《Excel數(shù)據(jù)處理與可視化》教案22-日期、時(shí)間范圍統(tǒng)計(jì)（一）

《Excel數(shù)據(jù)處理與可視化》課程教案授課題目日期、時(shí)間范圍統(tǒng)計(jì)(一)授課類型理實(shí)一體教學(xué)目標(biāo)1、掌握常用的日期、時(shí)間范圍統(tǒng)計(jì)方法2、會根據(jù)應(yīng)用場景選擇合適的函數(shù)解決具體問題重點(diǎn)1、常用日期、時(shí)間范圍統(tǒng)計(jì)的使用2、根據(jù)場景靈活選用函數(shù)難點(diǎn)1、根據(jù)場景靈活選用函數(shù)教學(xué)過程一制作按年、月自動變化的考勤表表頭在C6單元格中輸入公式“=IF(MONTH(DATE($B$3,$E$3,COLUMN(A6)))=$E$3,DATE($B$3,$E$3,COLUMN(A6)),"")”，按Enter鍵執(zhí)行計(jì)算在C5單元格中輸入公式“=TEXT(C6,"AAA")”，按Enter鍵執(zhí)行計(jì)算，再將公式向右填充，即可得自動變化的星期二、設(shè)置考勤表周六、周日列自動變色，且自動統(tǒng)計(jì)工作日與非工作日加班時(shí)長選中整個(gè)考勤表區(qū)域，單擊“開始”→“條件格式”→“新建格式規(guī)則”，選擇“使用公式確定要設(shè)置格式的單元格”命令，在“為符合此公式的值設(shè)置格式”文本框中輸入公式“=OR(C$5="六",C$5="日")”，此公式的含義是：條件為第五行單元格數(shù)據(jù)如果是“六”或者“日”，單擊“格式”按鈕，選擇一種填充顏色，即可實(shí)現(xiàn)周六、周日列自動變色三制作日期豎排的考勤表表頭（1）設(shè)置單元格格式。將B6單元格設(shè)置自定義格式為只顯示“年月日”中的“日”。日期豎排的考勤表只顯示“年月日”中的“日”（2）輸入公式計(jì)算日期。$E$3,ROW(A1)),"")”，按Enter鍵執(zhí)行計(jì)算，即得第一個(gè)日期，再將公式向下填充可得所有日期所示。實(shí)現(xiàn)自動變化的豎排日期（3）輸入公式計(jì)算星期。在A6單元格中輸入公式“=TEXT(B6,"AAA")),"")”，按Enter鍵執(zhí)行計(jì)算，即把第一個(gè)日期轉(zhuǎn)換為星期幾，再將公式向下填充可將所有日期轉(zhuǎn)換為星期幾。日期轉(zhuǎn)星期→公式的值設(shè)置格式”文本框中輸入公式“=OR($A6="六",$A6="日")”，并單擊“格式”按鈕設(shè)置背景色，所有的周六、周日便會用不同的顏色顯示出來。四日期與時(shí)間分離的3種方法1）分列選中數(shù)據(jù)，單擊“數(shù)據(jù)”→“分列”，在彈出的“文本分列向?qū)А睂υ捒蛑羞x擇“固定列寬”項(xiàng)，單擊“下一步”按鈕，直至完成2）INT函數(shù)在B2單元格中輸入公式“=INT(A2)”，按Enter鍵執(zhí)行計(jì)算，再將公式向下填充，可得日期，如圖2161所示。結(jié)果采用“日期+0:00”的格式，可以進(jìn)行日期型數(shù)據(jù)的計(jì)算，也可以設(shè)置單元格格式為年月日，隱去0:00。在C2單元格中輸入公式“=A2-B2”，按Enter鍵執(zhí)行計(jì)算，再將公式向下填充，單元格設(shè)置為時(shí)間格式，即得分離后的時(shí)間，如圖2162所示。3）TEXT函數(shù)在B2單元格中輸入公式“=TEXT(A2,"yyyy/m/d")”，按Enter鍵執(zhí)行計(jì)算，再將公式向下填充，結(jié)果如圖2163所示。在C2單元格中輸入公式“=A2-B2”，按Enter鍵執(zhí)行計(jì)算，單元格設(shè)置為時(shí)間格式，即得時(shí)間五按Enter鍵執(zhí)行計(jì)算，即可得結(jié)果所示。六利用LOOKUP+DATEDIF函數(shù)組合計(jì)算賬齡=LOOKUP(DATEDIF(B2,TODAY(),"M"),{0,6,12,24},{"6個(gè)月內(nèi)","6～12個(gè)月","1～2年","超過2年"})按Enter鍵執(zhí)行計(jì)算。七根據(jù)出生日期制作員工生日提醒公式““=TEXT(7-DATEDIF(B2-7,TODAY(),"YD"),"0天后生日;;今天生日")”，按Enter鍵執(zhí)行計(jì)算，即可統(tǒng)計(jì)出首位為4的商品銷售總量。作業(yè)日期、時(shí)間范圍統(tǒng)計(jì)練習(xí)教學(xué)小結(jié)本節(jié)知識：制作按年、月自動變化的考勤表表頭設(shè)置考勤表周六、周日列自動變色，且自動統(tǒng)計(jì)工作日與非工作日加班時(shí)長234制作日期豎排的考勤表表