Excel中进行数据分析的几个常用函数

职业教育

Excel 中进行数据分析的几个常用函数

赵磊

（河北机电职业技术学院）

（算数平MEAN AVERAGE 的功能是计算参数列表中数值的平均值

方便的解决，但是对于没有理工科背景的工作人员使用函数是有一定困难均值）。其语法形式为AVERAGE(number1，number2...) 其中num-的，本文介绍了几个EXCEL 中常用的进行数据分析的统计函数和数学，并介ber1，number2... 为要计算平均值的1～30个参数。这些参数可以是

语法及使用方法。绍了函数的功能、

数组或引用。如果参数是数组或单元数字，或者是包含数字的名称、

关键词：函数公式数据分析参数单元格

格引用，其中有文字、逻辑值或空单元格，则忽略其值，如果包含零值

Excel 是办公室工作中很常用的一款软件，很多企业都用Excel

则计算在内。

进行数据分析和管理。Excel 易学易用，并在数据和计算方面具有强

Excel 中还有一个函数AVERAGEA ，其函数名只比AVERAGE

大的功能，这其中的很多功能是通过函数实现的，在这里介绍几个进

多一个A ，两个函数的格式和功能及限定条件是一样的，其不同一处

行数据分析时经常用到的数学和统计函数

是AVERAGEA 会将包含文本的数组、单元格及空单元格和逻辑值

1函数概述

进行处理，计算时将逻辑真（TRUE ）按数值1计算，逻辑假（FALSE ）

Excel 中的函数其实是一些预定义的公式，用户可以直接用这

及文本和空单元格按数值0计算。

些公式对指定区域的数据进行指定的运算。例如使用SUM 函数可

TRIMMEAN 函数的功能是先从数据集的头部和尾部除去一定

MAX 函数可以在指定区域内找到其以对指定的区域进行求和运算，

百分比的数据点，然后再求平均值。语法形式为TRIMMEAN(array,

中的数据最大值。函数在使用时并不是只给出函数名就可以完成运

percent) ，其中Array 为需要进行筛选并求平均值的数组或数据区

算，用户要给出函数应用的单元格区域、条件等参数才能使用函数得

域。Percent 为计算时所要除去的数据点的比例。比如，在计算选手

到计算结果。

通常用去掉一个最高分，去掉一个最低分，再算选手的函数的表达形式一般是：函数名（参数1，参数2……），函数名平均分数时，

这种情况就可以使用此函数来计算，如果评委给出20个是已经定义好的在使用时不可改变，函数所需的参数写在括号里，多平均得分，

我们将percent 参数值设为0.1，在20个数据点的集合中，就个参数间用西文逗号分隔，多个参数的顺序也是定义好的，在使用时分数，

（20×0.1），头部除去1个，尾部除去1个。参数可以是数字、文本、要除去2个数据点要严格遵守，如果没有参数括号里可以是空。

2.2求最大值MAX 和最小值MIN MAX 和MIN 是用来求解逻辑值（TRUE 或FALSE ）、数组、单元格引用等，参数也可以是常量、

数据集的极值，即最大值和最小值的函数。语法形式为MAX 公式或其它函数。

number2... ）和MIN （number1，number2... ），其中的参数函数在使用时和公式的输入方法是一样的，先输入等于号再输（number1，

可以是数值、数组或单元格引用。入函数。在实际应用中则更多使用“插入函数”，在对话框选择使用所为1到30个，

2.3求中位数MEDIAN 和众数MODE 中位数是指在一组数需函数。在插入函数对话框中有关于函数的功能、格式和参数及运算

也就是说，在这组数据中，有一半的数据比它大，结果的提示，可以帮助用户完成函数的输入。观察“插入函数”对话框据中居于中间的数，

的选择函数列表，会发现在Excel 函数中会有这样一种情况，两个函有一半的数据比它小。众数是指在一组数据中出现频率最多的数。

MEDIAN 函数返回给定集合的中位数。语法形式为MEDIAN 数的差别只在最后一个字母即有一个函数最后多一个字母“A ”，这

number2...) 其中的参数形式与上面的函数相同。MEDI-样的函数功能是基本相同的，区别是在运算时带A 的函数都会对文(number1，本数据和逻辑值进行处理，而不带A 的函数将忽略文本、逻辑值和AN 在求解中位数时会先按从小到大将数据排序，再找到位置在中

间的数并返回其值，如果参数集合中包含有偶数个数字，函数将返回空单元格。

位于中间的两个数的平均值。2进行数据分析常用的统计函数

MODE 函数用来求众数。语法形式为MODE(number1，num-2.1求平均值函数AVERAGE 和求修剪平均值函数TRIM-（上接第213页）

导致余某脸部起火，造成面部小面积烧伤。虽然此例中致使另一学生如何防治结合地处理来自校外暴力袭击型的学生伤害事件呢？被烧伤的并非教师或实验管理员的个人行为，而是学生的操作不当一个基本的原则是“防治结合，以防为主”。所致，但是，教师或实验员对实验事项、安全警示应当尽到充分合理“防”可以从三个层次采取对策：一是思想意识上的防备。特别是的告知义务。事后，学校被追究赔偿责任。要对承担了学校保卫工作的部门领导人做好思想教育，令其时刻警

本文认为，要避免或防范此类学生伤害事故的发生，一个很有惕，防范校外暴力对学生的突然袭击；二是要加强门岗制度建设，实效的威慑机制即是设立责任状制度，责任到人，约束教师与管理人员行人不离岗，把好学校大门。实行严格的出入登记制度，谢绝无身份的个人行为。责任状相当于一份契约，主体一方是学校，另一方是教证件者入校。如果门岗违规放人出入，即使没有发生安全事故，也要师或管理人员，其主要内容是要求个人负责落实安全教育的目标责受到相应的经济处罚；三是加强学校电子监控设备的管理与投入使任状。如果发生了使学校卷入学生伤害赔偿纠纷中的事件，直接负责用。监控设备要安装在可能发生学生伤害的危险角落，24小时投入的教师或管理人员个人可能会被学校事后追偿。而且对教职工的考使用，并配备专人值守。

“零容忍”的评价指标，由此唤起教师和管理核环节，要引入安全事故“治”可以考虑从以下层面进行：一是整治学校周边治安环境，消

人员的责任心。除安全隐患，比如联合城市管理部门对摊贩、夜宵档、娱乐场所等可

3.3对来自校外暴力袭击型的学生伤害事件要防治结合正如能影响学校治安的危险因素予以清楚；二是对侵害学生安全利益的国务院温家宝总理所指出的那样，解决学生伤害事件要注意解决深袭击者果断采取措施予以制伏，奖励英勇捍卫学生生命的教职工；三层次矛盾。深层次的矛盾一般源于两个方向：一是学校与社会的关是要请求当地公安机关协助治理各种不安全状况，将学生伤害事故系；二是学校内部成员的关系。消除在萌芽阶段。

就前者而言，有的行凶者专门挑选学校作为目标下手，主要是对参考文献：社会的仇恨转嫁到无辜的学生身上。学生此时成为了泄愤者的牺牲[1]江涛. 对校园学生伤害事故的界定与预防[J].考试周刊，2009，(11).品。从更层次看，学校之所以会成为施暴者的目标是因为学校与社会[2]钱巧鲜. 校园体育活动伤害事故的预防[J].科教文汇(中旬刊，2009，的关系没有处理妥当，埋下了安全隐患。(07).

就后者而言，学校内部成员的关系基本分为三个层次：一是校方[3]周迎杰. 高校学生伤害事故多发的原因及对策[J].湖南人文科技学院代表（校长或董事长）与教职工之间的关系；二是学校（含校方代表和学报，2009，(01).教职工）与学生之间的关系；三是学生相互之间的关系。这三组关系均[4]唐宏瑛. 学校学生伤害事故若干问题分析[J].中国成人教育，2008，与学生利益密切关系，如果不当，可能会使学生沦为暴力侵害的对象。(22).

摘要：Excel 具有强大的数据分析和管理功能，很多问题通过函数可以

214

职业教育

ber2...) ，此函数会返回指定的数据、数组或数据区域中出现的次数最多的数值。

2.4求单元格个数的统计函数COUNT 和COUNTIF COUNT 函数的功能是统计单元格的个数，函数的语法形式为COUNT(val-ue1，value2...) ，其中value1，value2... 为包含或引用各种类型数据的

），但只有数字类型的数据才被计数。COUNT 函数在参数（1～30个

计数时，将把数字、零值及日期计算进去；但是错误值或其他无法转化成数字的文字则被忽略。如果要统计文本型数据就要使用COUNTA 函数。

COUNTIF 函数可以用来统计给定区域内满足特定条件的单元格的数目。比如统计职称是工程师的人数，统计女职工的人数等。语法形式为COUNTIF(range，criteria) 。其中Range 为需要计算其中满

其形足条件的单元格数目的单元格区域。Criteria 确定计数的条件，

式可以为数字、表达式或文本。例如，条件可以表示为“工程师”、“>2000”、60等。

2.5求数据的频率分布FREQUENCY FREQUENCY 可以计算在给定的分数范围内测验分数的个数，也就是说此函数可以统计给定数据集中各分段范围内数据的个数。语法形式为FREQUENCY (data_array,bins_array)，此函数以一列垂直数组返回各个区域中数

所以以数组据的频率分布，由于函数FREQUENCY 返回一个数组，

公式的形式输入。其中Data_array为要进行数据分段统计的一数组或对一组数值的引用，Bins_array为一数组或对数组区域的引用，设定对data_array进行分段频率计算的分段点。如果Bins_array中不包含任何数值，函数FREQUENCY 返回data_array元素的总个数。此函数非常有用，例如可以计算不同工资段的人员分布，职工年龄分布，学生成绩分布情况等。

此函数在使用时必须以数组公式的形式输入，具体操作是：先选定统计结果的单元格区域，然后点击“插入函数”图标按钮，选择FREQUENCY 函数，在“插入函数”对话框中输入要进行统计的数据区域和分段参数，如果分段参数不是数据区域而是具体分段数值则应当用大括号将各分段数值括起来，其中的各分段数值用逗号分隔。最后，在插入公式对话框输入完参数后，要按Ctrl+Shift同时再单击“确定”。这样就完成了数组公式的输入。

例如，要对学生成绩进行频率分布分析，数据区域在C3到C40，分段的条件是“0～59，60～69，70～79，80～89，90及以上”。如果分段参数59,69,79,89在E3到E6单元格，则输入的公式是：{=FREQUENCY(C3:C40,E3:E6)}；如果分段参数没有输入到数据区域，则公式输入应为：{=FREQUENCY(C3:C40,｛59,69,79,89｝)}。注意，公式是用大括号括起来的，这是数组公式的表示形式，在插入函数对话框的参数输入完毕，按下Ctrl+Shift同时再单击“确定”大括号就会自动添加到公式两端。

2.6用来排位的函数RANK 和PERCENTRANK RANK 函数的功能是返回指定数字在数据列表中的排位，数值的排位是与其他数值的相对大小比较后的排序位，如果数据已经排过序了，则数值的排位就是它当前的位置。语法形式为RANK(number，ref ，order) ，其中Number 为需要找到排位的数字；Ref 为一组数字的数组或引用，其中的非数值型数据被忽略。Order 为一个数字，用来指明排位的方

将按降序排列来确定指定数据的排位，式，如果order 为0或省略，

如果order 不为零，则按升序排列的数据列表进行排位。函数RANK 对重复数的排位相同。但重复数的存在将影响后续数值的排位。例如，在比赛排名里，如果两个选手并列第3次，则没有第4名，下一位选手的排名为第5名。RANK 函数对排名的处理方式与此相同。

PERCENTRANK 函数可用于查看指定数据在数据集中所处的百分比排位。语法形式为PERCENTRANK(array,x,significance)其中Array 为要对指定数值所在技术排位的数字数组或数字区域。X 为需要得到其排位的指定数值。Significance 为可选项，表示返回的百分数值的有效位数，其值应大于1，如果省略，函数PER-CENTRANK 保留3位小数。此函数的返回值为小数形式，如果需要

百分比形式则需要在单元格式中进行设置。

3用于数据分析的数学函数

3.1求和函数SUM 和SUMIF SUM 是求和函数，返回某一单元格区域中所有数字之和。语法形式为：SUM(number1，number2...) ，函数使用时直接键入到参数表中的数字、逻辑值（TRUE 为1，FALSE 为0）及数字的文本表达式将被计算，出现其它数据将提示出错。如果参数为数组或引用，只有其中的数字将被计算，数组或引用中的空白单元格、逻辑值、文本或错误值将被忽略，而不出现错误提示。

SUMIF 函数是对满足指定条件的单元格求和。语法形式为SUMIF(range，criteria ，sum_range) ，其中Range 为进行条件判断的单元格区域。Criteria 为进行求和的条件，其形式可以为数字、表达式或文本。Sum_range为需要求和的单元格区域。也就是说，只

与有Range 指定区域的单元格中数据满足Criteria 所指定条件时，

这些单元格相对应的Sum_range区域中的单元格才进行求和运算。其中的Sum_range如果忽略，则求和区域为Range 指定的区域。

3.2分类汇总函数SUBTOTAL SUBTOTAL 函数返回列表或数

“数据”菜单中的“分类汇总”命令对数据据库中的分类汇总。在使用

进行分类汇总之后，如果选定分类汇总结果的单元格，就会发现此处使用的就是SUBTOTAL 函数，如果想修改汇总方式，就可以通过编辑SUBTOTAL 函数进行修改。

函数的语法形式为：SUBTOTAL(function_num,ref1,ref2,...)，其中的参数Function_num为1到11（运算时包含隐藏值）或101到111（运算时忽略隐藏值）之间的数字，指定使用何种函数进行计算，见表1。Ref1,ref2,... 为要进行计算的1到29个区域或引用。SUBTOTAL 函数适用于数据列或垂直区域。不适用于数据行或水平区域。

SUBTOTAL 函数使用的函数及其代码表

Function_num（包含隐藏值）Function_num（忽略隐藏值）

[***********][***********]1011011111

表1

相应函数

AVERAGE COUNT COUNTA MAX MIN PRODUCT STDEV STDEVP SUM VAR VARP

SUBTOTAL 函数不仅仅在分类汇总时可以使用，在没有分类汇总情况下也可以使用，在对筛选结果进行统计时也可以使用。SUBTOTAL 函数中当function_num为从1到11的常数时，SUBTOTAL 函数将计算包括通过“隐藏”命令所隐藏的行中的值，当function_num为从101到111的常数时，SUBTOTAL 函数将忽略通过“隐藏”命令所隐藏的行中的值。但是对于“筛选”后被隐藏的数据行，不论function_num取何值，SUBTOTAL 函数都将忽略任何不包括在筛选结果中的行。例如，对C2到C40求平均值（应用AVERAGE 函数），如果计算包括“隐藏”命令隐藏的行中的值，则function_num取值为1，函数表示为：SUBTOTAL(1，C2:C40)，如果计算不包括“隐藏”命令隐藏的行中的值，则function_num取值为101，函数表示为：SUBTOTAL(101，C2:C40)。

在EXCEL 中有很多功能强大的函数，例如各种概念分布统计函数、各种数学运算函数、各种财务函数等等。在工作中根据工作性质的不同会用到不同的函数，我们在使用时可以通过查阅EXCEL 帮助来学习函数的使用，通过EXCEL 中的函数可以帮助我们轻松实现数据分析和管理。

参考文献：

[1]周国彬.Excel 函数应用之统计函数，百度文库. [2]MicrosoftExcel 帮助.

215