《Excel数据处理与可视化》（第二版） 课件 3.3日期、时间范围统计

第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根据入职时间计算带薪年假天数第