《Excel数据处理与可视化》教案22-日期、时间范围统计（一）

《Excel数据处理与可视化》课程教案授课题目日期、时间范围统计(一)授课类型理实一体教学目标1、掌握常用的日期、时间范围统计方法2、会根据应用场景选择合适的函数解决具体问题重点1、常用日期、时间范围统计的使用2、根据场景灵活选用函数难点1、根据场景灵活选用函数教学过程一制作按年、月自动变化的考勤表表头在C6单元格中输入公式“=IF(MONTH(DATE($B$3,$E$3,COLUMN(A6)))=$E$3,DATE($B$3,$E$3,COLUMN(A6)),"")”，按Enter键执行计算在C5单元格中输入公式“=TEXT(C6,"AAA")”，按Enter键执行计算，再将公式向右填充，即可得自动变化的星期二、设置考勤表周六、周日列自动变色，且自动统计工作日与非工作日加班时长选中整个考勤表区域，单击“开始”→“条件格式”→“新建格式规则”，选择“使用公式确定要设置格式的单元格”命令，在“为符合此公式的值设置格式”文本框中输入公式“=OR(C$5="六",C$5="日")”，此公式的含义是：条件为第五行单元格数据如果是“六”或者“日”，单击“格式”按钮，选择一种填充颜色，即可实现周六、周日列自动变色三制作日期竖排的考勤表表头（1）设置单元格格式。将B6单元格设置自定义格式为只显示“年月日”中的“日”。日期竖排的考勤表只显示“年月日”中的“日”（2）输入公式计算日期。$E$3,ROW(A1)),"")”，按Enter键执行计算，即得第一个日期，再将公式向下填充可得所有日期所示。实现自动变化的竖排日期（3）输入公式计算星期。在A6单元格中输入公式“=TEXT(B6,"AAA")),"")”，按Enter键执行计算，即把第一个日期转换为星期几，再将公式向下填充可将所有日期转换为星期几。日期转星期→公式的值设置格式”文本框中输入公式“=OR($A6="六",$A6="日")”，并单击“格式”按钮设置背景色，所有的周六、周日便会用不同的颜色显示出来。四日期与时间分离的3种方法1）分列选中数据，单击“数据”→“分列”，在弹出的“文本分列向导”对话框中选择“固定列宽”项，单击“下一步”按钮，直至完成2）INT函数在B2单元格中输入公式“=INT(A2)”，按Enter键执行计算，再将公式向下填充，可得日期，如图2161所示。结果采用“日期+0:00”的格式，可以进行日期型数据的计算，也可以设置单元格格式为年月日，隐去0:00。在C2单元格中输入公式“=A2-B2”，按Enter键执行计算，再将公式向下填充，单元格设置为时间格式，即得分离后的时间，如图2162所示。3）TEXT函数在B2单元格中输入公式“=TEXT(A2,"yyyy/m/d")”，按Enter键执行计算，再将公式向下填充，结果如图2163所示。在C2单元格中输入公式“=A2-B2”，按Enter键执行计算，单元格设置为时间格式，即得时间五按Enter键执行计算，即可得结果所示。六利用LOOKUP+DATEDIF函数组合计算账龄=LOOKUP(DATEDIF(B2,TODAY(),"M"),{0,6,12,24},{"6个月内","6～12个月","1～2年","超过2年"})按Enter键执行计算。七根据出生日期制作员工生日提醒公式““=TEXT(7-DATEDIF(B2-7,TODAY(),"YD"),"0天后生日;;今天生日")”，按Enter键执行计算，即可统计出首位为4的商品销售总量。作业日期、时间范围统计练习教学小结本节知识：制作按年、月自动变化的考勤表表头设置考勤表周六、周日列自动变色，且自动统计工作日与非工作日加班时长234制作日期竖排的考勤表表