还剩6页未读，继续阅读
文本内容:
《Excel数据处理与可视化》课程教案授课题目数据统计二授课类型理实一体、掌握常用的数据统计方法1教学目标、会根据应用场景选择合适的函数解决具体问题
2、常用数据统计的使用
1、根据场景灵活选用函数
2、根据场景灵活选用函数1一同一单元格中有多个姓名，统计总人数入公式“
二、,””+，按键执行计LENB2-LENSUBSTITUTEB2J1Enter算C2A=LENB2-LENSUBSTITUTEB2,\JR+l▼_A_1B C1线路人员名单人数2上海王
一、张
二、林
三、胡
四、吴五53广州章
六、陆
七、苏
八、韩
九、徐
一、项二64深圳贾
三、孙
四、姚五35北京周
六、金
七、赵
八、许九46西安陈
一、程
二、顾
三、祝
四、叶五57兰州杨
六、赖
七、石
八、郁
九、朱
一、百
二、时三7
二、员工姓名和业绩在同一单元格中，统计业绩最大值教学过程公式U=MAXSUBSTITUTEB2,ROW$1:$100,B2^ROW$1:$100”，按组合键执行计算Ctrl+Shift+EnterC2▼A{=MAXSUBSTITUTEB2,R0W$l:$100,oB2*R0W$l:$100}A BC D部门员工业绩最高业绩1销售部王一张二林三胡四吴五21196,90,85,9196销售部陆七苏八韩九徐一3276,85,71,9292销售部贾三孙四姚五4397,86,9697销售部周六金七赵八，许九5471,82,88,9595销售部陈一程二6564,9191三巧用函数统计前名数据ROW N求前名业绩和的公式为“二3SUMLARGEB2:B37,{1,2,3}”前名业绩和的公式为“二，按10SUMLARGEB2:B37,ROW1:10组合键执行计算Ctrl+Shift+Enter前名平均业绩的公式为“二，按10AVERAGELARGEB2:B37,ROW1:10”组合键执行计算Ctrl+Shift+Enter公式前名业绩和3205=SUMLARGEB2:B
37.{
1.
2.3}前名业绩和10621=SUMLARGEB2:B
37.ROW{1:10前名平均业绩
1062.1{=AVERAGELARGEB2:B37,ROW1:10四排除重复项统计每月缺勤人数入公式“二SUMPRODUCTMONTH$A$3:$A$30=D3*MATCHMONTH$A$3:$A$30=D3$B$3:$B$30,MONTH$A$3:$A$30D3$B$3:$B$30,0ROW$3:按组合键执行计算$30-2,Ctrl+Shift+EnterE3X/6{=SUMPRODUCTMONTH$A$3:$A$30=D3*MATCHMONTH$A$3:$A$30=D3$B$3:$B$30,MONTH$A$3:$A$30=D3$B$3:$B$30,0=ROW$3:$30-2A BC D E L年员工缺勒登记表年员工缺勒情况表根据月统计2009200912日期缺勤员工日期人数31fcoi■/王五1月3李四月4235越3月0月641李四月752李四月82009/2/1163甲一月92009/2/2271李四月102009/2/580并心月112009/2/1692李四月122009/4/6101张二月132009/5/7111张二月142009/5/101231R本A五巧用动态区域统计累计情况输入，按键执行计算，再将公式向下填充，即可计算每个月的完成JC2/B2Enter计划情况三三|三号•占百分比・M三隹亘至图.E、%，赞枭“淅方式G数字GD2▼X✓A:C2/B2A BC D|E月份计划销量实际销量当月完成计划惘况累枳计划累移122017年1月
10000953095.30%32017年2月
1000010235102.35%42017年3月
200001775388.77%52017年4月
1500019086127.24%62017年5月
10000856985.69%72017年6月
10000926592.65%82017年7月
1000011235112.35%92017年8月15000国102017年9月10000112017年10月10000累积计划2在单元格中输入公式“二，按键执行计算，再将公式向下填E2SUM$B$2:B2Enter充，即得本月及之前月的累积计划E2▼A=SUM$BS2B2A BC E F G累积计划月份计划销量实际销量当月完成计划情况累积储量累积完成计划书122017年1月
10000953095.30«10000132017年2月
1000010235102.35%2000042017年3月
200001775388.71%4000052017年4月
1500019086127.2a5500062017年5月
10000856985.69«6500072017年6月
10000926592.65%7500082017年7月
1000011235112.35X8500092017年8月15000100000102017年9月10000110000112017年10月10000120000累积销量方法同“累积计划”，只不过单元格区域变化了在单元格中输入公式F2，按键执行计算，再将公式向下填充，即得累积销量“=SUM$C$2:C2EnterF2▼[X✓A|=SUM$CS2^FA BC DF G月份计划销罩实际销晕当月完成计划情况累积计划累积销罩累枳完成计划情及122017年1月
10000953095.3M10000953032017年2月
1000010235102.35*200001976542017年3月
200001775388.11%4000037518厂52017年4月
1500019086127.24%SSOOO56604彳62017年5月
10000856985.69%6500065173彳72017年6月
10000926592.6SK7500074438r82017年7月
1000011235112.35%850008567392017年8月15000100000102017年9月10000110000112017年10月100001200002017年11月1220000140000累积完成计划情况累积完成计划情况，方法同“当月完成计划情况”，在单元格中输入公式G2，按键执行计算，再将公式向下填充，即得累积完成计划情况JF2/E2EnterG2-=F2/E2A BC1月份计划错量实际铺量当月完成计划恬况累枳计财~7累积销型累积完成计划恬况2017年1月
95.30X100009530—
95.3M1000095302017年2月
1000010235102.35X
200001976598.83X420门年3月
200001775388.71%
400003751893.80K52017年4月
127.24X
5500056604102.92X150001908662017年5月
85.6%
6500065173100.27X10000856972017年6月
10000926592.65H
750007443899.25182017年7月
112.
358500085673100.7第100001123592017年8月15000100000102017年9月10000110000112017年】月10000120000六根据销售额分段提成标准计算累进提成添加辅助列1增加“区间最低值”与“区间提成”两个辅助列，区间提成是用乘以提成率1000计算而来的E iF G H I J K提成标准销售额区间最低值提成率区间提成0-
99901.00%10第1个1000的提成.第个的提成1000-
199910001.20%
1221000.第个的提成2000〜
299920001.40%
1431000.第个的提成3000〜
399930001.60%16・41000■第个的提成4000—
499940001.80%
1851000.5000〜
599950002.00%20■第6个1000的提成第个的提成6000〜
699960002.20%2271000第个的提成7000-
799970002.40%
2481000.第个的提成8000〜
899980002.60%2691000第个的提成9000〜
999990002.80%28101000=
10000100003.00%30公式计算2在单元格中输入公式C3“=B3-LOOKUPB3,$G$3:$G$13*LOOKUPB3,$G$3:$G$13,$H$3:$H$13+IFB31000,0,SUMOFFSET$I$3„,MATCHB3,$G$3:$G$13,1-，按键执行计算，再将公式向下填充，可计算出所有销售额的累进提成，1,Enter如图所示2-129C3▼.6I=B3-LOOKUPB3,$G$3:$G$13*LOOKUPB3,$G$3:$G$13,$H$3:$H$13+IFB31000/0,SUMOFFSET$I$3„,MATCHB3,$G$3:$A BC DE F G HIJK L员工销售额与提成提成标准12姓名销售额累讲提成销售额区间最低值提成率区间提成3王一
475065.50-
99901.00%
10.第1个1000的提成4苏八
120012.41000-
199910001.20%
12.第2个1000的提成5周六30032000〜
299920001.40%14第3个1000的提成6祝四3500443000-
399930001.60%
16.第4个1000的提成7郁九
475065.54000-
499940001.80%
18.第5个1000的提成8邹七3500445000〜
599950002.00%20第6个1000的提成9张二
9335171.386000-
699960002.20%
22.第7个1000的提成10韩九3500447000-
799970002.40%24第8个1000的提成11金七
12589267.678000~
899980002.60%
26.第9个1000的提成12叶五
475065.59000〜
999990002.80%28第10个1000的提成13赵三7007=
10000100003.00%30七统计代码含有指定数字的商品销售总量公式二SUMPRODUCTISNUMBERSEARCH4”,A2:A12*B2:B12,\按D3▼A=SUMPRODUCTISNUMBERSEARCH4,,A2:A12*B2:B12/A B商品代码俏售金茶1代码首位为的商品储售总量25122330043234017353520445354575547219846代码首位为4且尾数为6的商品销售总量6445064781278一7247668518465248219528724081041606800113874084912550268311Q键执行计算，即可统计出首位为的商品销售总量Enter4八利用函数组合计算员工参与项目数LEN+SUBSTITUTE公式“
二、，按键执行LENB2-LENSUBSTITUTEB2,””,+l*B2”Enter计算，得第位员工参与项目数量，再将公式向下填充，得所有员工参与的项目数1C2▼fit=LENB2-LENSUBSTITUTEB2,\,+l*B2o,,nA BC DE1姓名参与项目参与项目数2王一项目
1、项目
6、项目1233张二项目
5、项目724,林三项目
2、项目
9、项目
10、项目1345胡四项目
1、项目
9、项目1036吴五项目
4、项目112项目、项目、项目7早八25738陆七09苏八项目
9、项目
10、项目
11、项目15410韩九项目
1、项目
5、项目73九按不同字体或背景颜色统计数值【实现方法】不用颜色数值统计1建立名称单击“公式”一“定义名称在弹出的“新建名称”对话框的1“名称”文本框中输入，“引用位置”文本框中输入公式“ztcolor”，单击“确定”按钮“=GET.CELL24,B2TNOW”提取字体颜色在单元格中输入公式“，按键执行计算，2C2=ztcolor”Enter▼［C2=ztcolor Iy八A BC D/产品数量门前自信12A4103J3B335104C81105D436E124437F438G206539H306r u.一再将公式向下填充，即得列数字颜色值B定义名称提取字体颜色把不同颜色的数字各取其一，粘贴到E歹U,同时在F2单元格中输入公式，按键执行计算，再将公式向下填充，即得列数字颜色值“=ztcolor EnterEF2▼A=ztcolor J/A BC IDE F GH1产品数量颜色值颜色颜色值和平均2A410341033B33510335104C8110222435D22243206536E12443国7F360438G206-539H3065310取颜色值统计在单元格中输入公式“二3G2SUMIF$C$2:$C$9,F2,$B$2:$B$9”,按键执行计算，公式向下填充，即得不同颜色数值的和；在单元格中输Enter H2入公式“二，按键执行计算，公AVERAGEIF$C$2:$C$9,F2,$B$2:$B$9”Enter式向下填充，即得不同颜色数值的平均值▼4A=AVERAGEIF$C$2:$C$9,F2,$B$2:$B$9H2/A BC DE1FGH1产品数量颜色值颜邑颜色值和平均2A410341034104103B10104162084C8110437062353335D43206535122566E437F438G206539H30653不同颜色平均值不同背景数值统计2建立名称单击“公式”一“定义名称”，在弹出的“新建名称”对话框1的“名称”文本框中输入，“引用位置”文本框中输入公式“bjcolor”单击“确定”按钮“=GET,CELL63,B2TNOW”,提取背景颜色在单元格中输入公式“，按键执行计算，2C2=bjcolor Enter再将公式往下填充，即得列背景颜色值B统计把不同背景颜色各取其一，粘贴到列，同时在单元格中输入公式3EF2，按键执行计算，再将公式向下填充，即得列背景颜色值“=bjcolor EnterE在单元格中输入公式“二按键执G2SUMIF$C$2:$C$9,F2,$B$2:$B$9M Enter行计算，公式向下填充，即得不同背景颜色数据的和；在单元格中输入公式二H2C2▼✓A=bjcolor/A BC D1产品量颜邑值2A1二33B1M10B4C1105D222436E124437F360438G539_H53■AVERAGEIF，按键执行计算，再将公式向下填充，即得不同背景$C$2:$C$9,F2,$B$2:$B$9”Enter页面布局开发工具颜色数值的平均值超逻与，@引用.数名称」数文本.惬数学和三解酸再侬用于公式，□日瞬］时间!■其他函数H2管超▼盘根据所选内容创x建y A=AVERAG日F$C$2:$C$9,F2,$B$2$B$9函数库/定义的名称A BC DEFGH1产品数量颜色值♦色颜色值和平均新建名称2A■一3—34104103B3351010416208名称N4C
811043706235.3333范围
⑤:5D22243__________535122566E12443备注@7F438G53■■引用位置⑻:9H531AI福定义背景颜色名称得背景颜色值作业数据统计练习本节知识同一单元格中有多个姓名，统计总人数员工姓名和业绩在同一单元格中，统计业绩最大值巧用函数统计前名数据ROW N排除重复项统计每月缺勤人数教学小结巧用动态区域统计累计情况根据销售额分段提成标准计算累进提成统计代码含有指定数字的商品销售总量利用函数组合计算员工参与项目数按不同字体或LEN+SUBSTITUTE背景颜色统计数值。
个人认证
优秀文档
获得点赞 0
