论文部分内容阅读
摘要:Excel具有强大的数据分析和管理功能,很多问题通过函数可以方便的解决,但是对于没有理工科背景的工作人员使用函数是有一定困难的,本文介绍了几个EXCEL中常用的进行数据分析的统计函数和数学,并介绍了函数的功能、语法及使用方法。
关键词:函数 公式 数据分析 参数 单元格
Excel是办公室工作中很常用的一款软件,很多企业都用Excel进行数据分析和管理。Excel易学易用,并在数据和计算方面具有强大的功能,这其中的很多功能是通过函数实现的,在这里介绍几个进行数据分析时经常用到的数学和统计函数
1 函数概述
Excel中的函数其实是一些预定义的公式,用户可以直接用这些公式对指定区域的数据进行指定的运算。例如使用SUM函数可以对指定的区域进行求和运算,MAX函数可以在指定区域内找到其中的数据最大值。函数在使用时并不是只给出函数名就可以完成运算,用户要给出函数应用的单元格区域、条件等参数才能使用函数得到计算结果。
函数的表达形式一般是:函数名(参数1,参数2……),函数名是已经定义好的在使用时不可改变,函数所需的参数写在括号里,多个参数间用西文逗号分隔,多个参数的顺序也是定义好的,在使用时要严格遵守,如果没有参数括号里可以是空。参数可以是数字、文本、逻辑值(TRUE或FALSE)、数组、单元格引用等,参数也可以是常量、公式或其它函数。
函数在使用时和公式的输入方法是一样的,先输入等于号再输入函数。在实际应用中则更多使用“插入函数”,在对话框选择使用所需函数。在插入函数对话框中有关于函数的功能、格式和参数及运算结果的提示,可以帮助用户完成函数的输入。观察“插入函数”对话框的选择函数列表,会发现在Excel函数中会有这样一种情况,两个函数的差别只在最后一个字母即有一个函数最后多一个字母“A”,这样的函数功能是基本相同的,区别是在运算时带A的函数都会对文本数据和逻辑值进行处理,而不带A的函数将忽略文本、逻辑值和空单元格。
2 进行数据分析常用的统计函数
2.1 求平均值函数AVERAGE和求修剪平均值函数TRIMMEAN AVERAGE的功能是计算参数列表中数值的平均值(算数平均值)。其语法形式为AVERAGE(number1,number2...)其中number1,number2...为要计算平均值的1～30个参数。这些参数可以是数字,或者是包含数字的名称、数组或引用。如果参数是数组或单元格引用,其中有文字、逻辑值或空单元格,则忽略其值,如果包含零值则计算在内。
Excel中还有一个函数AVERAGEA,其函数名只比AVERAGE多一个A,两个函数的格式和功能及限定条件是一样的,其不同一处是AVERAGEA会将包含文本的数组、单元格及空单元格和逻辑值进行处理,计算时将逻辑真(TRUE)按数值1计算,逻辑假(FALSE)及文本和空单元格按数值0计算。
TRIMMEAN函数的功能是先从数据集的头部和尾部除去一定百分比的数据点,然后再求平均值。语法形式为TRIMMEAN(array,percent),其中Array为需要进行筛选并求平均值的数组或数据区域。Percent为计算时所要除去的数据点的比例。比如,在计算选手平均分数时,通常用去掉一个最高分,去掉一个最低分,再算选手的平均得分,这种情况就可以使用此函数来计算,如果评委给出20个分数,我们将percent参数值设为0.1,在 20 个数据点的集合中,就要除去2个数据点(20×0.1),头部除去1个,尾部除去1个。
2.2 求最大值MAX和最小值MIN MAX和MIN是用来求解数据集的极值,即最大值和最小值的函数。语法形式为MAX(number1,number2...)和MIN(number1,number2...),其中的参数为1到30个,可以是数值、数组或单元格引用。
2.3 求中位数MEDIAN和众数MODE 中位数是指在一组数据中居于中间的数,也就是说,在这组数据中,有一半的数据比它大,有一半的数据比它小。众数是指在一组数据中出现频率最多的数。
MEDIAN函数返回给定集合的中位数。语法形式为MEDIAN(number1,number2...)其中的参数形式与上面的函数相同。MEDIAN在求解中位数时会先按从小到大将数据排序,再找到位置在中间的数并返回其值,如果参数集合中包含有偶数个数字,函数将返回位于中间的两个数的平均值。
MODE函数用来求众数。语法形式为MODE(number1,number2...),此函数会返回指定的数据、数组或数据区域中出现的次数最多的数值。
2.4 求单元格个数的统计函数COUNT和COUNTIF COUNT函数的功能是统计单元格的个数,函数的语法形式为COUNT(value1,value2...),其中value1,value2...为包含或引用各种类型数据的参数(1～30个),但只有数字类型的数据才被计数。COUNT函数在计数时,将把数字、零值及日期计算进去;但是错误值或其他无法转化成数字的文字则被忽略。如果要统计文本型数据就要使用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,如果省略,函数PERCENTRANK保留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函数不仅仅在分类汇总时可以使用,在没有分类汇总情况下也可以使用,在对筛选结果进行统计时也可以使用。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]Microsoft Excel帮助.
关键词:函数 公式 数据分析 参数 单元格
Excel是办公室工作中很常用的一款软件,很多企业都用Excel进行数据分析和管理。Excel易学易用,并在数据和计算方面具有强大的功能,这其中的很多功能是通过函数实现的,在这里介绍几个进行数据分析时经常用到的数学和统计函数
1 函数概述
Excel中的函数其实是一些预定义的公式,用户可以直接用这些公式对指定区域的数据进行指定的运算。例如使用SUM函数可以对指定的区域进行求和运算,MAX函数可以在指定区域内找到其中的数据最大值。函数在使用时并不是只给出函数名就可以完成运算,用户要给出函数应用的单元格区域、条件等参数才能使用函数得到计算结果。
函数的表达形式一般是:函数名(参数1,参数2……),函数名是已经定义好的在使用时不可改变,函数所需的参数写在括号里,多个参数间用西文逗号分隔,多个参数的顺序也是定义好的,在使用时要严格遵守,如果没有参数括号里可以是空。参数可以是数字、文本、逻辑值(TRUE或FALSE)、数组、单元格引用等,参数也可以是常量、公式或其它函数。
函数在使用时和公式的输入方法是一样的,先输入等于号再输入函数。在实际应用中则更多使用“插入函数”,在对话框选择使用所需函数。在插入函数对话框中有关于函数的功能、格式和参数及运算结果的提示,可以帮助用户完成函数的输入。观察“插入函数”对话框的选择函数列表,会发现在Excel函数中会有这样一种情况,两个函数的差别只在最后一个字母即有一个函数最后多一个字母“A”,这样的函数功能是基本相同的,区别是在运算时带A的函数都会对文本数据和逻辑值进行处理,而不带A的函数将忽略文本、逻辑值和空单元格。
2 进行数据分析常用的统计函数
2.1 求平均值函数AVERAGE和求修剪平均值函数TRIMMEAN AVERAGE的功能是计算参数列表中数值的平均值(算数平均值)。其语法形式为AVERAGE(number1,number2...)其中number1,number2...为要计算平均值的1～30个参数。这些参数可以是数字,或者是包含数字的名称、数组或引用。如果参数是数组或单元格引用,其中有文字、逻辑值或空单元格,则忽略其值,如果包含零值则计算在内。
Excel中还有一个函数AVERAGEA,其函数名只比AVERAGE多一个A,两个函数的格式和功能及限定条件是一样的,其不同一处是AVERAGEA会将包含文本的数组、单元格及空单元格和逻辑值进行处理,计算时将逻辑真(TRUE)按数值1计算,逻辑假(FALSE)及文本和空单元格按数值0计算。
TRIMMEAN函数的功能是先从数据集的头部和尾部除去一定百分比的数据点,然后再求平均值。语法形式为TRIMMEAN(array,percent),其中Array为需要进行筛选并求平均值的数组或数据区域。Percent为计算时所要除去的数据点的比例。比如,在计算选手平均分数时,通常用去掉一个最高分,去掉一个最低分,再算选手的平均得分,这种情况就可以使用此函数来计算,如果评委给出20个分数,我们将percent参数值设为0.1,在 20 个数据点的集合中,就要除去2个数据点(20×0.1),头部除去1个,尾部除去1个。
2.2 求最大值MAX和最小值MIN MAX和MIN是用来求解数据集的极值,即最大值和最小值的函数。语法形式为MAX(number1,number2...)和MIN(number1,number2...),其中的参数为1到30个,可以是数值、数组或单元格引用。
2.3 求中位数MEDIAN和众数MODE 中位数是指在一组数据中居于中间的数,也就是说,在这组数据中,有一半的数据比它大,有一半的数据比它小。众数是指在一组数据中出现频率最多的数。
MEDIAN函数返回给定集合的中位数。语法形式为MEDIAN(number1,number2...)其中的参数形式与上面的函数相同。MEDIAN在求解中位数时会先按从小到大将数据排序,再找到位置在中间的数并返回其值,如果参数集合中包含有偶数个数字,函数将返回位于中间的两个数的平均值。
MODE函数用来求众数。语法形式为MODE(number1,number2...),此函数会返回指定的数据、数组或数据区域中出现的次数最多的数值。
2.4 求单元格个数的统计函数COUNT和COUNTIF COUNT函数的功能是统计单元格的个数,函数的语法形式为COUNT(value1,value2...),其中value1,value2...为包含或引用各种类型数据的参数(1～30个),但只有数字类型的数据才被计数。COUNT函数在计数时,将把数字、零值及日期计算进去;但是错误值或其他无法转化成数字的文字则被忽略。如果要统计文本型数据就要使用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,如果省略,函数PERCENTRANK保留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函数不仅仅在分类汇总时可以使用,在没有分类汇总情况下也可以使用,在对筛选结果进行统计时也可以使用。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]Microsoft Excel帮助.