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

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

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