項(xiàng)目10：制作動(dòng)態(tài)考勤表

項(xiàng)目10：制作動(dòng)態(tài)考勤表教師：電話：輔導(dǎo)時(shí)間：郵箱：第一部分Word2016高級(jí)應(yīng)用案例知識(shí)技能點(diǎn)用WEEK和DAY函數(shù)自動(dòng)顯示星期用DAY和DATE函數(shù)自動(dòng)計(jì)算每月的天數(shù)設(shè)置單元格下拉列表選項(xiàng)使用COUNTIF函數(shù)計(jì)數(shù)10.1項(xiàng)目背景小王在幫助人力資源經(jīng)理錄入數(shù)據(jù)的過程中，人力資源經(jīng)理有意讓小王針對(duì)員工考勤登記和統(tǒng)計(jì)。由于考勤系統(tǒng)暫時(shí)沒有到位，希望在這段時(shí)間人工考勤過程中，能充分運(yùn)用Excel強(qiáng)大的自動(dòng)化功能，盡量減少錄入工作量，能做到自動(dòng)統(tǒng)計(jì)。10.2.項(xiàng)目簡介本項(xiàng)目利用時(shí)間函數(shù)、計(jì)數(shù)函數(shù)、判斷函數(shù)、序列填充等多種函數(shù)，自動(dòng)顯示星期、自動(dòng)計(jì)算當(dāng)月考勤的天數(shù)、根據(jù)設(shè)計(jì)的考勤標(biāo)志自動(dòng)計(jì)算考勤統(tǒng)計(jì)等功能。10.3.項(xiàng)目制作10.3.1.制作基本表格新建Excel文檔，命名為“員工考勤表.xlsx”，基本樣式如圖10-1所示。由于后續(xù)部分要根據(jù)年份和月份計(jì)算星期，所以將“2017”和“10”分別放置在一個(gè)單元格內(nèi)（或合并單元格內(nèi)）。圖10-1考勤表基本表格10.3.2.自動(dòng)顯示星期根據(jù)“年”、“月”和“日期”中的數(shù)據(jù)，自動(dòng)計(jì)算“星期”行中的數(shù)據(jù)。步驟1：在D2單元格中輸入公式“=IF((WEEKDAY(DATE($M$1,$Q$1,D3),2))=7,"日",(WEEKDAY(DATE($M$1,$Q$1,D3),2)))”（不包含引號(hào)）。公式首先通過DATE函數(shù)，將“$M$1”單元格數(shù)據(jù)即2017、“$Q$1”數(shù)據(jù)即10和D3單元格數(shù)據(jù)即1拼接為日期形式，再通過WEEKDAY(****,2)函數(shù)，計(jì)算選定時(shí)間是星期幾，由于WEEKDAY(****,2)函數(shù)計(jì)算結(jié)果為1,2,3……7的形式，根據(jù)通常寫法，當(dāng)?shù)扔?的時(shí)候，一般寫為“星期日”，因此通過IF函數(shù)判斷，當(dāng)WEEKDAY(****,2)函數(shù)計(jì)算結(jié)果等于7的時(shí)候，將替換為“日”，否則用數(shù)字顯示當(dāng)前的星期日期，如圖10-2所示?！拘≠N士】由于下一步向右填充是，M1單元格和Q1單元格內(nèi)容不應(yīng)該自動(dòng)變化，因此，在引用該單元格內(nèi)容時(shí)，應(yīng)選擇絕對(duì)引用“$M$1,$Q$1”。WEEKDAY函數(shù)的用法：語法：WEEKDAY(serial_number,[return_type])WEEKDAY函數(shù)語法具有下列參數(shù)：Serial_number

必需。一個(gè)序列號(hào)，代表嘗試查找的那一天的日期。應(yīng)使用DATE函數(shù)輸入日期，或者將日期作為其他公式或函數(shù)的結(jié)果輸入。例如，使用函數(shù)DATE(2008,5,23)輸入2008年5月23日。如果日期以文本形式輸入，則會(huì)出現(xiàn)問題。圖10-2用公式確定星期Return_type

可選，用于確定返回值類型的數(shù)字，每個(gè)不同的返回值，代表返回不同的星期格式，具體如下表所示。Return_type返回的數(shù)字1或省略數(shù)字1（星期日）到7（星期六）。2數(shù)字1（星期一）到7（星期日）。3數(shù)字0（星期一）到6（星期日）。11數(shù)字1（星期一）到7（星期日）。12數(shù)字1（星期二）到數(shù)字7（星期一）。13數(shù)字1（星期三）到數(shù)字7（星期二）。14數(shù)字1（星期四）到數(shù)字7（星期三）。15數(shù)字1（星期五）到數(shù)字7（星期四）。16數(shù)字1（星期六）到數(shù)字7（星期五）。17數(shù)字1（星期日）到7（星期六）。步驟2：設(shè)置D2單元格格式。根據(jù)中國習(xí)慣，“星期1”一般應(yīng)寫為“星期一”、“星期2”一般應(yīng)寫為“星期二”，依次類推，需要設(shè)置D2單元格的數(shù)據(jù)格式，顯示為中文的“一、二”等。右鍵點(diǎn)擊D2單元格，選擇【設(shè)置單元格格式】，在彈出的對(duì)話框選擇【數(shù)字】選項(xiàng)卡，在【分類】中選擇【特殊】，在類型列表選擇【中文小寫數(shù)字】，點(diǎn)擊【確定】按鈕即可，如圖10-3所示圖10-3設(shè)置單元格格式第三步：選中D2單元格，拖動(dòng)右下角的“填充柄”向右填充，直到AH3單元格。效果如圖10-4所示。圖10-4設(shè)置星期后的效果當(dāng)確定年和月之后，需要AH1單元格自動(dòng)顯示當(dāng)月的天數(shù)。在AH1單元格中輸入公式“=DAY(DATE(M1,Q1+1,1)-1)”，公式先獲取M1單元格的年份，此處為“2017”，以及Q1單元格月份（此處為10）的下一個(gè)月“Q1+1”，即11月，結(jié)合DATE第三個(gè)參數(shù)“1”，獲取2017年11月1日的日期，再通過“DATE(M1,Q1+1,1)-1”，獲取11月1日之前一天的日期，本例為2017年10月31日，最后用DAY函數(shù)獲取2017年10月31日的天數(shù)，即31，填入到AH1單元格。如圖10-5所示。圖10-5獲取當(dāng)前月的天數(shù)為統(tǒng)一考勤標(biāo)志，需要設(shè)定統(tǒng)一的考勤符號(hào)，當(dāng)然也可以設(shè)定用漢字或者其他自己認(rèn)可方便的符號(hào)均可，也可以根據(jù)實(shí)際情況確定更多考勤類別。本例假設(shè)有如下的符號(hào)規(guī)定。為方便錄入，采用下拉列表選擇的方法來設(shè)置，每次錄入考勤的時(shí)候，僅需從下拉列表中選擇即可。符號(hào)考勤含義√正?！?xí)绻ぁ鞴佟钏郊龠x擇需要考勤的所有單元格，選擇【數(shù)據(jù)】選項(xiàng)卡->【數(shù)據(jù)驗(yàn)證】->【數(shù)據(jù)驗(yàn)證】，彈出數(shù)據(jù)驗(yàn)證對(duì)話框，在【允許】下拉列表中選擇【序列】，在【來源】部分選中單元格折疊按鈕，然后選擇$B$37:$B$40區(qū)域，點(diǎn)擊【確定】按鈕，如圖10-6所示。其中$B$37:$B$40是考勤符號(hào)標(biāo)志區(qū)域。圖10-6用列表設(shè)置考勤符號(hào)一旦設(shè)置完畢，每個(gè)人的考勤，就可以從下拉列表中選擇了。錄入考勤效果如圖10-7所示。圖10-7錄入考勤10.3.5.自動(dòng)考勤統(tǒng)計(jì)在表格中每個(gè)人的最后，劃出一塊區(qū)域，用來統(tǒng)計(jì)每個(gè)人的出勤天數(shù)，如圖10-8所示。使用COUNTIF函數(shù)統(tǒng)計(jì)各種考勤符號(hào)出現(xiàn)的次數(shù)，由于每天的上午和下午都需要考勤，但統(tǒng)計(jì)是以天為單位的，因此在用COUNTIF函數(shù)統(tǒng)計(jì)之后，需要將考勤符號(hào)出現(xiàn)的次數(shù)再除以2，得到最終的以天為單位的考勤統(tǒng)計(jì)。圖10-8考勤統(tǒng)計(jì)區(qū)域在統(tǒng)計(jì)區(qū)域中相應(yīng)單元格中，輸入公式“=COUNTIF(D4:AH5,AM$2)/2”（不包含引號(hào)），表示在D4:AH5單元格范圍內(nèi)，統(tǒng)計(jì)AM$2（即符號(hào)√）出現(xiàn)的次數(shù)，并用該數(shù)據(jù)除以2，得到正常出勤的以天為單位的數(shù)據(jù)，在該單元格右下方，拖動(dòng)“填充柄”，向下填充，即可得到所有人的正常出勤天數(shù)。注意在此處對(duì)單元格AM$2的引用方法。在“曠工”單元格的下一個(gè)單元格，輸入公式“=COUNTIF(D4:AH5,AM$3)/2”，表示在D4:AH5單元格范圍內(nèi)，統(tǒng)計(jì)