《Excel数据处理与可视化》教案10-统计函数

《Excel数据处理与可视化》课程教案授课题目统计函数授课类型理实一体教学目标1、掌握MODE.MULT函数、COUNTIF函数、“IFS”结尾、COUNTIFS函数、TRIMMEAN函数等统计函数的用法；2、会灵活选用统计函数重点1、统计函数的选用常识2、多个函数的组合难点1、多个函数的组合教学过程一利用MODE.MULT函数统计出现最多的数字（一）公式“=MODE.MULT($A$2:$G$12)”，按Enter键执行计算，即可得出现次数最多的数值。二、利用MODE.MULT函数统计出现最多的数字（二）公式“=IFERROR(INDEX(MODE.MULT($A$1:$E$10),ROW(A1)),"")”，按Enter键执行计算，即得出现次数最多的数值，三、利用COUNTIF函数给众多班级中相同班级的学生编号公式“=COUNTIF($B$2:B2,B2)”，按Enter键执行计算，再将公式向下填充就得到所有班级的学生编号。四、5个常用的“IFS”结尾的多条件统计函数1）COUNTIFS函数：多条件计数语法：COUNTIFS(criteria_range1,criteria1,[criteria_range2,criteria2],…)中文语法：COUNTIFS(条件区域1,条件1,[条件区域2,条件2],…)●条件区域1：必需，在其中计算关联条件的第1个区域。●条件1：必需，条件的形式为数字、表达式、单元格引用或文本。它定义了要计数的单元格范围。例如，条件可以表示为32、">32"、B4、"apples"或"32"。●在本示例中，要统计市场1部业绩分高于10的女高级工程师人数，有以下4个条件对。●条件区域1：市场部；条件1：市场1部。●条件区域2：业绩分；条件2：高于10。●条件区域3：性别；条件3：女。●条件区域4：职称；条件4：高级工程师。所以，公式为“=COUNTIFS(A2:A21,"市场1部",E2:E21,">=10",C2:C21,"女",D2:D21,"高级工程师")”。2）SUMIFS函数：多条件统计和语法：SUMIFS(sum_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)中文语法：SUMIFS(统计和的数值区域,条件区域1,条件1,[条件区域2,条件2],…)●统计和的数值区域：必需，要计算和的一个或多个单元格，其中包含数字或包含数字的名称、数组、引用。●条件区域1、条件区域2等：条件区域1必需，后续条件区域是可选的。在其中计算关联条件的1～127个区域。●条件1、条件2等：条件1必需，后续条件是可选的。形式为数字、表达式、单元格引用或文本的1～127个条件，用来定义要统计和的单元格。例如，条件可以表示为32、"32"、">32"、"苹果"或B4。在本示例中，要统计市场1部女高级工程师业绩总分，有以下3个条件对。●条件区域1：市场部；条件1：市场1部。●条件区域2：性别；条件2：女。●条件区域3：职称；条件3：高级工程师。3）AVERAGEIFS函数：多条件统计平均值语法：AVERAGEIFS(average_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)中文语法：AVERAGEIFS(统计平均值区域,条件区域1,条件1,[条件区域2,条件2],…)●统计平均值区域：必需，要计算平均值的一个或多个单元格，其中包含数字或数字的名称、数组、引用。●条件区域1、条件区域2等：条件区域1必需，后续条件区域是可选的。在其中计算关联条件的1～127个区域。●条件1、条件2等：条件1必需，后续条件是可选的。形式为数字、表达式、单元格引用或文本的1～127个条件，用来定义要计算平均值的单元格。例如，条件可以表示为32、"32"、">32"、"苹果"或B4。在本示例中，要统计市场1部女高级工程师平均业绩分，有以下3个条件对。●条件区域1：市场部；条件1：市场1部。●条件区域2：性别；条件2：女。●条件区域3：职称；条件3：高级工程师。所以，公式为“=AVERAGEIFS(E2:E21,A2:A21,"市场1部",C2:C21,"女",D2:D21,"高级工程师")”。4）MAXIFS函数：多条件统计最大值语法：MAXIFS(max_range，criteria_range1，criteria1，[criteria_range2，criteria2],…)中文语法：MAXIFS(取最大值的单元格区域,条件区域1,条件1,[条件区域2,条件2],…)●取最大值的单元格区域：必需，要取最大值的一个或多个单元格，其中包含数字或数字的名称、数组、引用。●条件区域1、条件区域2等：条件区域1必需，后续条件区域是可选的。在其中计算关联条件的1～126个区域。●条件1、条件2等：条件1必需，后续条件是可选的。形式为数字、表达式、单元格引用或文本的1～126个条件，用来定义取最大值的单元格。例如，条件可以表示为32、"32"、">32"、"苹果"或B4。在本示例中，要统计市场1部女高级工程师最高业绩得分，有以下3个条件对。●条件区域1：市场部；条件1：市场1部。●条件区域2：性别；条件2：女。●条件区域3：职称；条件3：高级工程师。5）MINIFS函数：多条件统计最小值语法：MINIFS(min_range,criteria_range1,criteria1,[criteria_range2,criteria2],…)中文语法：MINIFS(取最小值的单元格区域,条件区域1,条件1,[条件区域2,条件2],…)●取最小值的单元格区域：必需，要取最小值的一个或多个单元格，包含数字或数字的名称、数组、引用。●条件区域1、条件区域2等：条件区域1必需，后续条件区域是可选的。在其中计算关联条件的1～126个区域。●条件1、条件2等：条件1必需，后续条件是可选的。形式为数字、表达式、单元格引用或文本的1～126个条件，用来定义取最小值的单元格。例如，条件可以表示为32、"32"、">32"、"苹果"或B4。在本示例中，要统计市场1部女高级工程师最低业绩得分，有以下3个条件对。●条件区域1：市场部；条件1：市场1部。●条件区域2：性别；条件2：女。●条件区域3：职称；条件3：高级工程师。五、利用SUM+COUNTIF函数组合统计不重复值的个数在E2单元格中输入公式“=SUM(1/COUNTIF(B2:B16,B2:B16))”，按Ctrl+Shift+Enter组合键执行计算，即可统计出销售的商品种数所示。六、利用FREQUENCY函数分段计数输入公式“=FREQUENCY(B2:B16,{60,70,80,90}-0.1)”，按Ctrl+Shift+Enter组合键执行计算，即可计算每个分数段的人数。七、利用COUNTIFS函数统计满足多个条件的单元格数量在F2单元格中输入公式“=COUNTIFS($A$2:$A$135,G$1,$C$2:$C$135,">="&$F2)”，按Enter键执行计算，再将公式向下、向右填充，即可得到每个班级各分数段的人数。八、利用TRIMMEAN函数计算去掉最高分和最低分后的平均分，左侧公式为“TRIMMEAN（B3:B8,2/9）”，表示在9个裁判员打分中，去掉一个最高分和一个最低分后计算平均分；右侧公式为“TRIMMEAN(F3:F8,4/9)”，表示在9个裁判员打分中，去掉两个最高分和两个最低分后计算平均分。作业练习统计函数的使用教学小结本节知识：利用MODE.MULT函数统计出现最多的数字（