考勤表的制作

L羅樂 2016-12-07

展開全文

（圖合伙人noah）

告別11月，迎來了2016年的最后一個月——12月您好！

今天是2016年12月的第二天，讓我們一起來看看合伙人成員在干嘛呢？

時間：2016年12月2日

地點：EXCEL中國合伙人駐廣州市白云區(qū)辦公室

人物：無敵大師姐『Coffee』

不會妹『仰望~星空』

拍攝：花花女神

星空：大師姐，可以向你請教個問題嗎？我在制作考勤表，怎樣能根據(jù)年月快速得到當(dāng)月的日與星期呢？

Coffee：可以通過控件來快速實現(xiàn)日與星期的同步更新。

星空：不懂。

Coffee：你看我這有個考勤表，我只要移動滾動條，就會出現(xiàn)對應(yīng)年份月份的日與星期。

星空：哇塞，師姐可以教教我嗎？

Coffee：當(dāng)然可以，先教你做控件吧！

Coffee：插入滾動條后，右鍵-設(shè)置控件格式

Coffee：從哪一年到哪一年你可以根據(jù)自己的需要去設(shè)置，年份的控件做好后，月份的控件也是同樣的方法，月份是從1月至12月，那么最小值為1，最大值為12，注意要填寫單元格鏈接，后面日與星期的數(shù)據(jù)都會隨著這個鏈接單元格年月的變動而改變。

星空：好的，師姐，真沒想到一個小小的控件有這么大作用。

Coffee：再來看看這個標(biāo)題的制作吧。

公式：A2=TEXT(DATE(C1,F1,1),'yyyy年m月員工考勤表')

Coffee：知道這個公式什么意思嗎？

星空：不知道。

Coffee：DATE返回日期時間代碼中代表日期的數(shù)字，DATE的使用格式為DATE(年,月,日),年份在單元格C1，月份在單元格F1，把他們作為DATE的第一第二參數(shù)，當(dāng)移動滾動條時相關(guān)日期會隨之改變，這個日你寫當(dāng)月的某日都行。TEXT根據(jù)指定的數(shù)值格式將數(shù)字轉(zhuǎn)成文本，注意要加英文狀態(tài)下的雙引號。

星空：公式在A2單元格，為啥內(nèi)容跑那么遠(yuǎn)了呢？

Coffee：這是設(shè)置了單元格對齊方式。

Coffee：如果想知道今天具體是哪一天、星期幾，你可以像這表右上角這樣設(shè)置。

公式：=TEXT(TODAY(),'今天是:yyyy年m月d日 aaaa')

Coffee：TODAY()返回日期格式的當(dāng)前日期，該函數(shù)不需要參數(shù)的。

同樣用TEXT函數(shù)轉(zhuǎn)換格式，4個a是將日期轉(zhuǎn)成星期幾的形式。

Coffee：現(xiàn)在來看看對應(yīng)的日與星期到底怎么實現(xiàn)的。

公式：C3=TEXT(IF(MONTH(DATE($C1,$F1,COLUMN(A1)))=$F1,DATE($C1,$F1,COLUMN(A1)),''),'d')，向右填充。

星空：這么復(fù)雜啊，這公式看得我頭都暈了。

Coffee：理清思路就不復(fù)雜了，DATE($C1,$F1,COLUMN(A1))部分容易理解吧，單元格C1是年份，單元格F1是月份，那COLUMN(A1)就是日了，因為公式右拉時要得到1,2,3…等這樣的序號，用COLUMN函數(shù)構(gòu)造，比如COLUMN(A1)等于1，右拉時變成COLUMN(B1)，相當(dāng)于等于2。函數(shù)MONTH是返回月份，IF部分就是如果MONTH(DATE($C1,$F1,COLUMN(A1)))部分得到的月份等于F1單元格的月份，就返回DATE($C1,$F1,COLUMN(A1))這個日期，否則為空。最后用TEXT函數(shù)轉(zhuǎn)換格式，只要日的話用'd'。