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

第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+(D2C2)-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})”