ECEL中统计与计算类函数

商务数据分析基础EXCEL中统计与计算类函数3.2.1统计类函数平均值函数1算术平均值：返回参数的平均值AVERAGE(number1,number2,...)2几何平均值：返回正数数组或数值区域的几何平均数GEOMEAN(number1,[number2],...)3调和平均值：返回一组正数的调和平均数HARMEAN(number1,[number2],...)1．平均值函数平均值是表示一组数据集中趋势的量数，是反映数据集中趋势的一项指标3.2.1统计类函数1．平均值函数【例3-1】某企业2018年上半年每个月的成本如图3-11所示，现要求计算出该企业2018年上半年的平均值、几何平均值和调和平均值，操作步骤如下。图3-11某企业2018年上半年每个月成本明细3.2.1统计类函数1．平均值函数光标定位于D4单元格，鼠标单击“插入函数”按钮，弹出如图3-12所示的“插入函数”对话框，选择类别“统计”—“GEOMEAN”函数，单击“确定”按钮，弹出如图3-13所示的“函数参数”对话框。光标定位于number1右侧的输入框中，按住鼠标左键选取EXCEL表中的B2:B7区域，再单击“确定”按钮，即计算得出“上半年成本几何平均值”。同理，分别将光标定位于D2单元格和D6单元格计算“上半年成本平均值”和“上半年成本调和平均值”图3-12“插入函数”对话框图3-13“函数参数”对话框3.2.1统计类函数2．计数函数在数据分析过程中，经常需要统计选定区域内数值型单元格的数目、空白单元格的数目、非空单元格的数目以及满足某条件的单元格的数目03020104COUNT功能：返回包含数字以及包含参数列表中的数字的单元格的个数COUNT(value1,value2,...)COUNTA功能：计算范围中不为空的单元格的个数COUNTA(value1,[value2],...)COUNTBLANK功能:用于计算单元格区域中的空单元格的个数COUNTBLANK（range）COUNTIF功能：计算区域中满足给定条件的单元格的个数COUNTIF(range,criteria)3.2.1统计类函数2．计数函数【例3-2】请根据如图3-14所示的学生成绩明细，统计学生人数、语文科目参加考试人数及数学科目参加考试人数。操作步骤如下。图3-14学生成绩明细数据表3.2.1统计类函数2．计数函数光标定位于F4单元格，鼠标单击“插入函数”按钮，弹出如图315所示的“插入函数”对话框，选择类别“统计”—“COUNTA”函数，单击“确定”按钮，弹出如图316所示的“函数参数”对话框。光标定位于value1右侧的输入框中，鼠标选取A2:A11单元格区域，单击“确定”按钮即可。光标定位于F5单元格，鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“统计”—“COUNT”函数，单击“确定”按钮，弹出“函数参数”对话框，光标定位于value1右侧的输入框中，鼠标选取B2:B11单元格区域，单击“确定”按钮即可。图3-15“插入函数”对话框图3-16“函数参数”对话框3.2.1统计类函数2．计数函数【例3-3】如图3-17所示是会员数据，一般数据库中的数据均有一个主键，即不允许重复的键，计算主键的重复次数如果大于1即为重复。操作步骤如下。图3-17会员数据明细3.2.1统计类函数2．计数函数在A列与B列中插入一列，名称为“重复次数”。将光标定位于B2单元格，鼠标单击“插入函数”按钮，弹出如图3-18所示的“插入函数”对话框，选择类别“统计”—“COUNTIF”函数，单击“确定”按钮，弹出如图3-19示的“函数参数”对话框。光标定位于range右侧的输入框中，鼠标选取A2:A11单元格区域，由于此区域在公式复制过程中不变，故行号后添加$符号，将相对地址改为混合地址。将光标定位于Criteria右侧的输入框中，选取A2单元格，单击“确定”按钮，B2单元格中的数值显示3。单击B2单元格，将鼠标移动到B2单元格右下角，此时光标变为一个+，双击+，后续的单元格自动按此公式进行计算。图3-18“插入函数”对话框图3-19“函数参数”对话框3.2.1统计类函数2．计数函数【例3-4】如图3-20所示是某单位2018年1月的销售明细数据，现要求统计教学部男员工的销售记录个数。操作步骤如下。图3-20某单位2018年1月销售明细数据3.2.1统计类函数2．计数函数光标定位于工作表的空白单元格，鼠标单击“插入函数”按钮，弹出如图3-21所示的“插入函数”对话框，选择类别“统计”—“COUNTIFS”函数，单击“确定”按钮，弹出如图3-22所示的“函数参数”对话框。光标定位于criteria_range1右侧的输入框中，鼠标选取第一个条件区域C3:C14，光标定位于criteria1右侧的输入框中，输入第一个条件“教学部”，光标定位于criteria_range2右侧的输入框中，鼠标选取第二个条件区域B3:B14，光标定位于criteria2，输入第二个条件“男”，单击“确定”按钮即可。图3-21“插入函数”对话框图3-22“函数参数”对话框3.2.1统计类函数3．其他统计类函数其他统计函数0102030405最大值函数最小值函数Frequency函数Rank函数MEDIAN函数MAX(number1,number2,...)功能：返回一组值中的最大值MIN(number1,number2,...)功能：返回一组值中的最小值MEDIAN(number1,number2,...)功能：返回给定数值集合的中值.中值是在一组数据中居于中间的数,即在这组数据中，有一半的数据比它大,有一半的数据比它小RANK(number,ref,order)功能：返回一个数字在数字列表中的排位。数字的排位是其大小与列表中其他值的比值（如果列表已排过序，则数字的排位就是它当前的位置）Frequency(data_array,bins_array)功能：以一列垂直数组返回一组数据的频率分布3.2.1统计类函数3．其他统计类函数【例3-5】计算如图3-17所示会员明细数据表中，购买金额的最大值与最小值。操作步骤如下。图3-17会员数据明细3.2.1统计类函数3．其他统计类函数鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“统计”—“MAX”函数，单击“确定”按钮，弹出“函数参数”对话框。光标定位于number1右侧的输入框中，鼠标选取G2:G11单元格区域，单击“确定”按钮即可求出购买金额的最大值。【例3-6】计算如图3-17所示会员明细数据表中，购买金额的中位数。操作步骤如下。鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“统计”—“MEDIAN”函数，单击“确定”按钮，弹出“函数参数”对话框。光标定位于number1右侧的输入框中，鼠标选取G2:G11单元格区域，单击“确定”按钮即可求出购买金额的中位数。3.2.1统计类函数3．其他统计类函数【例3-7】计算如图3-23所示学生成绩明细表中每位同学成绩在年级中的排名。操作步骤如下。图3-23学生成绩明细表3.2.1统计类函数3．其他统计类函数光标定位于工作表的E2单元格，鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“统计”—“RANK”函数，单击“确定”按钮，弹出图324“函数参数”对话框。光标定位于number右侧的输入框中，鼠标选取B2单元格。光标定位于Ref右侧的输入框中，按住Ctrl的同时，鼠标选取B2:B11单元格区域与D2:D11单元格区域，为保证复制公式时，此区域地址不变化，需要使用绝对地址，因此在行列号前加上$，并在区域外添加英文状态下的括号。光标定位于Order右侧的输入框中，输入0或省略，默认情况下为降序。鼠标单击“确定”按钮即可。图3-24RANK“函数参数”对话框3.2.1统计类函数3．其他统计类函数【例3-8】请分别统计图3-23所示学生成绩明细表中学生成绩的分段人数，结果如图3-26所示。操作步骤如下。图3-23学生成绩明细表图3-26分段统计结果3.2.1统计类函数3．其他统计类函数鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“统计”—“FREQUENCY”函数，单击“确定”按钮，弹出如图3-25所示“函数参数”对话框。光标定位于Data_array右侧的输入框中，按住Ctrl键的同时，鼠标选取B2:B11单元格区域和D2:D11单元格区域。为保证复制公式时，此区域地址不变化，需要使用绝对地址，因此在行列号前加上$，并在区域外添加英文状态下的括号。光标定位于Bins_array右侧的输入框中，鼠标选取C14:C17单元格区域，接收区间的结束单元格C17在复制公式时不能变化，需要使用混合地址，因此在C17行号前加上$。鼠标单击“确定”按钮即可。图3-25Frequency“函数参数”对话框3.2.2数学计算类函数1．求和函数在数据分析过程中，有时需要对数值型数据进行求和或根据指定条件求和等03020104SUMSUM(number1,number2,...)功能：返回某一单元格区域中所有数字之和。参数说明：Number1,number2,...为1到30个需要求和的参数,可以是一个具体的数值、一个单元格或单元格区域SUMIFSSUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],...)功能：用于计算其满足多个条件的全部参数的总量。SUMIFSUMIF(range,criteria,sum_range)功能：对范围中符合指定条件的数值求和。SUMPRODUCTSUMPRODUCT(array1,[array2],[array3],...)功能：在给定的几组数组中,将数组间对应的元素相乘,并返回乘积之和3.2.2数学计算类函数1．求和函数【例3-9】要求计算如图3-27所示的信息表中指定部门的总成绩。操作步骤如下。图3-27成绩信息表3.2.1统计类函数1．求和函数光标定位于工作表的B14单元格，鼠标单击“插入函数”按钮，弹出如图3-28所示的“插入函数”对话框，选择类别“数学与三角函数”—“SUMIF”函数，单击“确定”按钮，弹出如图3-29所示的“函数参数”对话框。光标定位于range右侧的输入框中，鼠标选取条件区域A2:A10，为确保向下复制公式时，条件区域永远定位于A2:A10，因此需要将该地址改为混合地址A$2:A$10，光标定位于criteria右侧的输入框中，选取条件单元格A14，光标定位于Sum_range右侧的输入框中，鼠标选取求各区域B3:B14，同样，需要将该区域地址修改为混合地址B$3:B$14，单击“确定”按钮即可。图3-28“插入函数”对话框图3-29SUMIF“函数参数”对话框3.2.2数学计算类函数1．求和函数【例3-10】要求计算如图3-30所示的信息表中各部门男女员工的销售总额。操作步骤如下。图3-30各部门员工销售明细表3.2.1统计类函数1．求和函数光标定位于工作表的D18单元格，鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“数学与三角函数”—“SUMIFS”函数，单击“确定”按钮，弹出“函数参数”对话框。光标定位于Sum_range右侧的输入框中，鼠标选取计算区域D3:D14，为确保向下复制公式时，条件区域永远定位于D3:D14，因此需要将该地址改为绝对地址$D$3:$D$14。光标定位于criteria_Range1右侧的输入框中，鼠标选取计算区域C3:C14，为确保向下复制公式时，条件区域永远定位于C3:C14，因此需要将该地址改为绝对地址$C$3:$C$14，光标定位于criteria1右侧的输入框中，鼠标选取条件单元格B18。光标定位于criteria_Range2右侧的输入框中，鼠标选取计算区域B3:B14，为确保向下复制公式时，条件区域永远定位于B3:B14，因此需要将该地址改为绝对地址$B$3:$B$14，光标定位于criteria2右侧的输入框中，鼠标选取条件单元格C18，单击“确定”按钮即可。3.2.2数学计算类函数1．求和函数【例3-11】要求使用SUMPRODUCT计算如图330所示的信息表中各部门男女员工的人数及销售总额。操作步骤如下。图3-30各部门员工销售明细表3.2.1统计类函数1．求和函数光标定位于工作表的D18单元格，鼠标单击“插入函数”按钮，弹出“插入函数”对话框，选择类别“数学与三角函数”—“SUMPRODUCT”函数，单击“确定”按钮，弹出如图3-32所示的“函数参数”对话框。光标定位于array1右侧的输入框中，输入公式($C$3:$C$14=D18)*($B$3:$B$14=C18)光标定位于array2右侧的输入框中，鼠标选取计算区域$D$3:$D$14，单击“确定”按钮即可计算各部门男女员工的销售总额计算各部门男女员工的人数，只需要在array1右侧的输入框中输入公式($C$3:$C$14=D18)*($B$3:$B$14=C18)，单击“确定”按钮即可。图3-32SUMPRODUCT“函数参数”对话框3.2.2数学计算类函数2．其他数学函数（1）INT(number)功能：将number数值向下舍入为最接近