《Excel数据处理与可视化》教案21-数据统计（二）

《Excel数据处理与可视化》课程教案授课题目数据统计(二)授课类型理实一体教学目标1、掌握常用的数据统计方法2、会根据应用场景选择合适的函数解决具体问题重点1、常用数据统计的使用2、根据场景灵活选用函数难点1、根据场景灵活选用函数教学过程一同一单元格中有多个姓名，统计总人数入公式“=LEN(B2)-LEN(SUBSTITUTE(B2,"、",""))+1”，按Enter键执行计算二、员工姓名和业绩在同一单元格中，统计业绩最大值公式“=MAX((SUBSTITUTE(B2,ROW($1:$100),)<>B2)*ROW($1:$100))”，按Ctrl+Shift+Enter组合键执行计算三巧用ROW函数统计前N名数据求前3名业绩和的公式为“=SUM(LARGE(B2:B37,{1,2,3}))”。前10名业绩和的公式为“=SUM(LARGE(B2:B37,ROW(1:10)))”，按Ctrl+Shift+Enter组合键执行计算。前10名平均业绩的公式为“=AVERAGE(LARGE(B2:B37,ROW(1:10)))”，按Ctrl+Shift+Enter组合键执行计算。四排除重复项统计每月缺勤人数)-2))，按Ctrl+Shift+Enter组合键执行计算五巧用动态区域统计累计情况输入“=C2/B2”，按Enter键执行计算，再将公式向下填充，即可计算每个月的完成计划情况（2）累积计划。在E2单元格中输入公式“=SUM($B$2:B2)”，按Enter键执行计算，再将公式向下填充，即得本月及之前月的累积计划累积销量。累积完成计划情况。累积完成计划情况，方法同“当月完成计划情况”，在G2单元格中输入公式“=F2/E2”，按Enter键执行计算，再将公式向下填充，即得累积完成计划情况。六根据销售额分段提成标准计算累进提成（1）添加辅助列。增加“区间最低值”与“区间提成”两个辅助列，区间提成是用1000乘以提成率计算而来的。（2）公式计算。按Enter键执行计算，再将公式向下填充，可计算出所有销售额的累进提成，如图2129所示。七统计代码含有指定数字的商品销售总量公式“=SUMPRODUCT(ISNUMBER(SEARCH("4????",A2:A12))*B2:B12)”，按Enter键执行计算，即可统计出首位为4的商品销售总量。八利用LEN+SUBSTITUTE函数组合计算员工参与项目数公式“=(LEN(B2)-LEN(SUBSTITUTE(B2,"、",))+1)*(B2<>"")，按Enter键执行计算，得第1位员工参与项目数量，再将公式向下填充，得所有员工参与的项目数九按不同字体或背景颜色统计数值【实现方法】1）不用颜色数值统计（1）建立名称。单击“公式”→“定义名称”，在弹出的“新建名称”对话框的“名称”文本框中输入“ztcolor”，“引用位置”文本框中输入公式“=GET.CELL(24,B2)&T(NOW())”，单击“确定”按钮。（2）提取字体颜色。在C2单元格中输入公式“=ztcolor”，按Enter键执行计算，再将公式向下填充，即得B列数字颜色值。定义名称提取字体颜色把不同颜色的数字各取其一，粘贴到E列，同时在F2单元格中输入公式“=ztcolor”，按Enter键执行计算，再将公式向下填充，即得E列数字颜色值。取颜色值（3）统计。在G2单元格中输入公式“=SUMIF($C$2:$C$9,F2,$B$2:$B$9)”，按Enter键执行计算，公式向下填充，即得不同颜色数值的和；在H2单元格中输入公式“=AVERAGEIF($C$2:$C$9,F2,$B$2:$B$9)”，按Enter键执行计算，公式向下填充，即得不同颜色数值的平均值。不同颜色平均值2）不同背景数值统计（1）建立名称。单击“公式”→“定义名称”，在弹出的“新建名称”对话框的“名称”文本框中输入“bjcolor”，“引用位置”文本框中输入公式“=GET.CELL(63,B2)&T(NOW())”，单击“确定”按钮。（2）提取背景颜色。在C2单元格中输入公式“=bjcolor”，按Enter键执行计算，再将公式往下填充，即得B列背景颜色值。（3）统计。把不同背景颜色各取其一，粘贴到E列，同时在F2单元格中输入公式“=bjcolor”，按Enter键执行计算，再将公式向下填充，即得E列背景颜色值。在G2单元格中输入公式“=SUMIF($C$2:$C$9,F2,$B$2:$B$9)”，按Enter键执行计算，公式向下填充，即得不同背景颜色数据的和；在H2单元格中输入公式“=AVERAGEIF定义背景颜色名称得背景颜色值($C$2:$C$9,F2,$B$2:$B$9)”，按Enter键执行计算，再将公式向下填充，即得不同背景颜色数值的平均值。作业数据统计练习教学小结本节知识：同一单元格中有多个姓名，统计总人数员工姓名和业绩在同一单元格中，统计业绩最大值巧用ROW函数