《Excel数据处理与可视化》（第二版） 课件 韩春玲 第3部分 数据统计分析

第3部分 数据统计分析----3.1数据查询和匹配3.1数据查询和匹配3.1.1查找得票最多的姓名公式“=IFERROR(INDEX(B$2:B$16,SMALL(MODE.MULT(MATCH(B$2:B$16,B$2:B$16,)),ROW(A1))),"")”3.1数据查询和匹配3.1.2查找开奖号码对应的数字公式“=IF(COUNT(FIND(C$1,$A2)),C$1,"")”3.1数据查询和匹配3.1.3何提取前3名销量数据重复公式“=LARGE(IF(FREQUENCY($B$2:$B$13,$B$2:$B$13),$B$2:$B$13),ROW(A1))”数据不重复：3.1数据查询和匹配3.1.5去掉最后一个特殊符号及以后内容第1种方法：快速填充。第2种方法：公式法公式“=MID(A2,1,LOOKUP(9^9,FIND("-",A2,ROW($1:$30)))-1)”3.1数据查询和匹配3.1.4提取订货量对应的订货型号公式“=INDIRECT(ADDRESS(2,MATCH(LARGE(D4:G4,1),4:4,0)))”3.1数据查询和匹配3.1.6两列商品型号排序不一，对应数量如何相减公式“=B2-IFERROR(VLOOKUP(A2,$E$2:$F$9,2,0),0)3.1数据查询和匹配3.1.7利用VLOOKUP函数实现多表数据合并查询公式“=VLOOKUP($B2,INDIRECT($A2&"!a:d"),COLUMN(B1),0)”3.1数据查询和匹配3.1.8VLOOKUP+MATCH，轻松查找数据公式“=VLOOKUP(A13,A1:E10,MATCH(B12,A1:E1,0),0)”3.1数据查询和匹配3.1.9满足条件的数据自动“跑”到其它工作表公式“=INDEX(全部!A:A,SMALL(IF(全部!$F:$F="已对",ROW(全部!A:A),ROWS(A:A)),ROW(A1)))&""”3.1数据查询和匹配3.1.10INDEX+MATCH函数组合应用——查找业绩前几名员工姓名公式“=INDEX(A2:A10,MATCH(MAX(B2:B10),B2:B10,0))”3.1数据查询和匹配3.1.11INDEX+MATCH函数组合应用——提取行列交叉单元格数值公式“=INDEX(A1:E10,MATCH(A13,A1:A10,0),MATCH(B13,A1:E1,0))”3.1数据查询和匹配3.1.12INDEX+MATCH函数组合应用——提取整行整列数据公式“=INDEX(B2:E10,MATCH(A13,A2:A10,0),0)”，按Ctrl+Shift+Enter组合建执行计算3.1数据查询和匹配3.1.13利用OFFSET函数在大量数据中查找指定数据公式“=IF(COUNTIF(OFFSET(D5,0,0,500,500),A4)<>0,"有","无")”3.1数据查询和匹配3.1.14利用OFFSET函数在动态区域中查找指定数据公式“=IF(COUNTIF(OFFSET($D$6,0,0,COUNTA($D:$D),COUNTA($6:$6)),A4)<>0,"有","无")”3.1数据查询和匹配3.1.15跨表查询指定顾客的购买记录公式“=INDEX(购买记录表!B:B,SMALL(IF(购买记录表!$B$2:$B$12=购买查询!$B$1,ROW(购买记录表!$B$2:$B$12),ROWS(购买记录表!B:B)),ROW(A1)))&""”，按Ctrl+Shift+Enter组合建执行计算3.1数据查询和匹配3.1.16在大量信息中快速查找哪些员工信息不完整第1种方法：定位法。第2种方法：公式法。3.1数据查询和匹配3.1.17利用OFFSET函数提取最大销量整列信息公式“=OFFSET($A$2,ROW(A1)-1,MATCH(MAX($B$5:$K$5),$B$5:$K$5,0))”3.1数据查询和匹配3.1.18利用OFFSET函数提取前三位销量整列信息公式“=OFFSET($A$2,ROW($A1)-1,MATCH(LARGE($B$5:$K$5,COLUMN(A1)),$B$5:$K$5,0))”3.1数据查询和匹配3.1.19提取销售量最大的月份公式“=OFFSET($A$1,,MATCH(MAX(B2:M2),B2:M2,0))”3.1数据查询和匹配3.1.20提取销售量第一、二、三位的月份公式“=OFFSET($A$1,,MATCH(LARGE($B2:$M2,COLUMN(A$1)),$B2:$M2,0))”3.1数据查询和匹配3.1.21如何给相同姓名添加相同编号1．相同姓名有序排列。第1步，在A2单元格中输入编号1。第1步，在A3单元格中输入公式“=IF(B3=B2,A2,A2+1)”，按Enter键执行计算，再将公式向下填充，即得结果3.1数据查询和匹配3.1.21如何给相同姓名添加相同编号2．姓名无序排列。公式“=IFERROR(VLOOKUP(G2,IF({1,0},G$1:G1,F$1:F1),2,0),N(F1)+1)”3.1数据查询和匹配3.1.22INDEX+SMALL函数组合完成一对多查找公式“=INDEX(B:B,SMALL(IF(A$2:A$13=E$2,ROW(A$2:A$13),ROWS(B:B)),ROW(A1)))&""”，按Ctrl+Shift+Enter组合建执行计算3.1数据查询和匹配3.1.23VLOOKUP与IFERROR函数组合屏蔽查找错误值公式“=IFERROR(VLOOKUP(A2,Sheet2!$A$2:$B$12,2,0),"")”3.1数据查询和匹配3.1.24如何查找主单号对应的子单号，且子单号同行显示方法1：辅助列+VLOOKUP函数。3.1数据查询和匹配3.1.24如何查找主单号对应的子单号，且子单号同行显示方法2：公式“=IFERROR(VLOOKUP($D3&COLUMN(A$1),IF({1,0},$A$2:$A$13&COUNTIF(INDIRECT("A2:A"&ROW($2:$13)),$D3),$B$2:$B$13),2,0),"")”3.1数据查询和匹配3.1.25利用公式从地址中取省级行政区公式“=LEFT(A2,MIN(FIND({"省","市","区"},A2&"省市区")))”3.1数据查询和匹配3.1.26Vlookup+IF函数、Vlookup+CHOOSE函数实现逆向查询1．VLOOKUP+IF函数实现逆向查询3.1数据查询和匹配3.1.26Vlookup+IF函数、Vlookup+CHOOSE函数实现逆向查询2．VLOOKUP+CHOOSE函数实现逆向查询3.1数据查询和匹配3.1.27IF、VLOOKUP、LOOKUP、CHOOSE、INDEX都可完成等级评定IF函数：公式“=IF(B2<60,"不合格",IF(B2<70,"合格",IF(B2<85,"良好","优秀")))”3.1数据查询和匹配3.1.27IF、VLOOKUP、LOOKUP、CHOOSE、INDEX都可完成等级评定VLOOKUP函数：公式“=VLOOKUP(B2,{0,"不合格";60,"合格";70,"良好";85,"优秀"},2)”3.1数据查询和匹配3.1.27IF、VLOOKUP、LOOKUP、CHOOSE、INDEX都可完成等级评定LOOKUP函数：公式“=LOOKUP(B2,{0,60,70,85},{"不合格";"合格";"良好";"优秀"})”3.1数据查询和匹配3.1.27IF、VLOOKUP、LOOKUP、CHOOSE、INDEX都可完成等级评定CHOOSE+MATCH函数：公式“=CHOOSE(MATCH(B2,{0,60,70,85},1),"不合格","合格","良好","优秀")”3.1数据查询和匹配3.1.27IF、VLOOKUP、LOOKUP、CHOOSE、INDEX都可完成等级评定INDEX+MATCH函数：公式“=INDEX({"不合格";"合格";"良好";"优秀"},MATCH(B2,{0,60,70,80},1))”3.1数据查询和匹配3.1.28利用VLOOKUP函数查询同一部门多个员工信息第1步：建立辅助列。第2步：公式实现。3.1数据查询和匹配3.1.29利用VLOOKUP函数查询一种产品多次进货量公式“=IFERROR(VLOOKUP($B$12&ROW(B1),IF({1,0},$B$2:$B$9&COUNTIF(INDIRECT("b2:b"&ROW($2:$9)),$B$12),$C$2:$C$9),2,0),"")”，按Ctrl+Shift+Enter三键组合3.1数据查询和匹配3.1.30六个多条件查询的函数第3部分 数据统计分析----3.2数据统计3.2数据统计3.2.1根据等级计算总成绩公式“=SUM(SUMIF($I$2:$I$5,B2:F2,$J$2:$J$5))”，按Ctrl+Shift+Enter组合键执行计算3.2数据统计3.2.2依据评分标准，折算男女同学体育分数公式“=IF(G3="男",LOOKUP(H3,$B$4:$B$17,$A$4:$A$17),LOOKUP(H3,$D$4:$D$17,$C$4:$C$17))”3.2数据统计3.2.3统计除请假以外参与考核的部门人数公式“=SUMPRODUCT((B2:B15=E2)*ISNUMBER(C2:C15))”3.2数据统计3.2.4根据规定好的占比划分成绩等级公式“=LOOKUP(PERCENTRANK.INC(C:C,C2),{0,10,40,80}%,{"D","C","B","A"})”3.2数据统计3.2.4根据规定好的占比划分成绩等级公式“=LOOKUP(PERCENTRANK.INC(C:C,C2),{0,10,40,80}%,{"D","C","B","A"})”成绩由高到低划分A、B、C、D四个等级，而且规定好了各等级所占的人数百分比：等级A占20%，等级B占40%，等级C占30%，等级D占10%3.2数据统计3.2.5利用公式填写金额收据公式“=LEFT(RIGHT("¥"&ROUND($A2,2)*100,12-COLUMN(A:A)))”3.2数据统计3.2.6正值负值分别求和，盈亏情况一目了然负值求和正值求和3.2数据统计3.2.7算算每户有几口人公式“=IF(A2="户主",COUNTA(B2:B15)-SUM(C3:C15),"")”3.2数据统计3.2.8依据收费标准，计算不同地区不同重量快递费用公式“=SUMPRODUCT(INDEX($B$2:$C$8,MATCH("*"&LEFT(F2,2)&"*",$A$2:$A$8,),)*IF({1,0},1,INT(G2-0.01)))”3.2数据统计3.2.9多人分组完成多个项目，统计每个人参与了哪些项目公式“=IFERROR(INDEX($A$1:$A$7,SMALL(($G2<>$B$2:$D$7)*100+ROW($B$2:$D$7),COLUMN(A$1))),"")，按Ctrl+Shift+Enter组合建执行计算3.2数据统计3.2.10同一单元格中多个姓名，如何统计总人数公式“=LEN(B2)-LEN(SUBSTITUTE(B2,"、",""))+1”3.2数据统计3.2.11员工姓名和业绩挤在一个单元格中，怎样统计业绩最大值公式“=MAX((SUBSTITUTE(B2,ROW($1:$100),)<>B2)*ROW($1:$100))”,按Ctrl+Shift+Enter组合建执行计算3.2数据统计3.2.12巧用ROW函数统计前N名数据前三名业绩和：=SUM(LARGE(B2:B37,{1,2,3}))前十名业绩和：=SUM(LARGE(B2:B37,ROW(1:10)))，按Ctrl+Shift+Enter组合建执行计算。前十名平均业绩：=AVERAGE(LARGE(B2:B37,ROW(1:10)))，按Ctrl+Shift+Enter组合建执行计算。3.2数据统计3.2.13全年缺勤表，排除重复项统计出每月缺勤人数公式“=SUMPRODUCT((MONTH($A$3:$A$30)=D3)*(MATCH((MONTH($A$3:$A$30)=D3)&$B$3:$B$30,(MONTH($A$3:$A$30)=D3)&$B$3:$B$30,0)=ROW($3:$30)-2))，按Ctrl+Shift+Enter组合建执行计算3.2数据统计3.2.14巧用动态区域统计累计情况1．当月完成计划情况。2．累积计划。3.2数据统计3.2.14巧用动态区域统计累计情况3．累积销量。4．累积完成计划情况。3.2数据统计3.2.15如何根据销售额分段提成标准计算累进提成1．添加辅助列。2．公式计算。3.2数据统计3.2.16统计代码含有指定数字的商品销量总量1．首位为4的商品销售总量。2．首位为4尾数为6的商品销售总量。3.2数据统计3.2.17利用LEN+SUBSTITUTE函数，计算员工参与项目数公式“=(LEN(B2)-LEN(SUBSTITUTE(B2,”、“,))+1)*(B2<>”“)”3.2数据统计3.2.18按不同字体或是背景颜色统计数值第2步：提取字体颜色：第1步：建立名称。第3步：统计。1．不用颜色数值统计。3.2数据统计3.2.18按不同字体或是背景颜色统计数值第2步：提取背景颜色。第1步：建立名称。第3步：统计。2．不同背景数值统计。第3部分 数据统计分析----3.3日期、时间范围统计3.3 日期、时间范围统计3.3.1制作按年月自动变化的考勤表表头公式“=IF(MONTH(DATE($B$3,$E$3,COLUMN(A6)))=$E$3,DATE($B$3,$E$3,COLUMN(A6)),"")”3.3 日期、时间范围统计3.3.2设置考勤表周六周日列自动变色且自动统计工作日与周末加班时长1．周六周日列自动变色2．自动统计工作日与周末加班时长3.3 日期、时间范围统计3.3.3制作日期竖排的考勤表表头第1步：设置单元格格式。第2步：输入公式计算日期。第3步：输入公式计算星期。第4步：条件格式突出周末。3.3 日期、时间范围统计3.3.4日期与时间分离的三种方法方法1：分列。方法2：INT函数。方法3：TEXT函数。3.3 日期、时间范围统计3.3.5利用SUM+OFFSET函数查询产品指定月份期间的销量合计公式“=SUM(OFFSET(A1,MATCH(A13,A2:A9,0),B13,1,C13-B13+1))”,3.3 日期、时间范围统计3.3.6利用LOOKUP+DATEDIF函数计算账龄公式“=LOOKUP(DATEDIF(B2,TODAY(),"M"),{0,6,12,24},{"6个月内","6-12个月","1-2年","超过2年"})”3.3 日期、时间范围统计3.3.7根据出生日期制作员工生日提醒公式“=TEXT(7-DATEDIF(B2-7,TODAY(),"YD"),"0天后生日;;今天生日")”3.3 日期、时间范围统计3.3.82018年的2月有29日吗公式“=IF(OR(AND(MOD(D2,4)=0,MOD(D2,100)<>0),MOD(D2,400)=0),"闰年","平年")”3.3 日期、时间范围统计3.3.9判断会员是否可以升级到VIP公式“=IF(AND(TODAY()-B2>=365,C2>=500),"VIP会员","普通会员")”3.3 日期、时间范围统计3.3.10白班中班跨日夜班，如何用公式计算工作时长公式“=D2+(D2<C2)-C2”3.3 日期、时间范围统计3.3.11计算加班时长与补助工资公式“=D2-C2”“自定义”设置为“h"小时"mm"分钟"”公式“=HOUR(E2)*100+INT(MINUTE(E2)/15)*25”3.3 日期、时间范围统计3.3.12SUMIFS函数按月统计产品销量第1步：TEXT函数建立辅助列。第2步：利用SUMIFS函数完成统计。3.3 日期、时间范围统计3.3.13计算指定年份与月份的销售总额公式“=SUMPRODUCT((YEAR($A$2:$A$15)=D2)*(MONTH($A$2:$A$15)=E2)*($B$2:$B$15))”3.3 日期、时间范围统计3.3.14根据入职时间计算带薪年假天数公式““=IF(DATEDIF(A2,TODAY(),"y")<1,0,IF(DATEDIF(A2,TODAY(),"y")<10,5,IF(DATEDIF(A2,TODAY(),"y")<20,10,15)))”第1种方法：IF函数3.3 日期、时间范围统计3.3.14根据入职时间计算带薪年假天数第2种方法：LOOKUP函数。公式“=LOOKUP(DATEDIF(A2,TODAY(),"y"),{0,1,10,20},{0,5,10,15})”第3部分 数据统计分析----3.4数据透视表3.4 数据透视表3.4.1数据透视表八项基本应用1．更改汇总方式。3.4 数据透视表3.4.1数据透视表八项基本应用2．数值排序3.4 数据透视表3.4.1数据透视表八项基本应用3．分组统计3.4 数据透视表3.4.1数据透视表八项基本应用4．按年季月汇总3.4 数据透视表3.4.1数据透视表八项基本应用5．筛选3.4 数据透视表3.4.1数据透视表八项基本应用6．筛选前几项3.4 数据透视表3.4.1数据透视表八项基本应用7．添加列字段3.4 数据透视表3.4.1数据透视表八项基本应用8．生成相应的数据透视图3.4 数据透视表3.4.2巧用数据透视表批量生成合并单元格第1步：添加辅助列。第2步：生成并设置数据透视表。第3步：格式刷刷出合并单元格3.4 数据透视表3.4.3利用OFFSET函数定义名称，实现数据透视表动态更新第1步：定义名称第2步：插入数据透视表3.4 数据透视表3.4.4利用数据透视表对数据进行分段统计第1步：建立数据透视表。3.4 数据透视表3.4.4利用数据透视表对数据进行分段统计第2步：分数值分组。3.4 数据透视表3.4.4利用数据透视表对数据进行分段统计第3步：添加分数段比例。3.4 数据透视表3.4.5巧用数据透视表快速拆分工作表第1步：插入数据透视表。3.4 数据透视表3.4.5巧用数据透视表快速拆分工作表第2步：设计数据透视表。第3部分 数据统计分析----3.5数学专业分析3.5数学专业分析3.5.1新个税计算的三个公式第1种方法：IF函数。公式“=IF((B12-5000)<0,0,IF((B12-5000)<=3000,(B12-5000)*0.03,IF((B12-5000)<=12000,(B12-5000)*0.1-210,IF((B12-5000)<=25000,(B12-5000)*0.2-1420,IF((B12-5000)<=35000,(B12-5000)*0.25-2660,IF((B12-5000)<=55000