01
日期和星期的填充
製作考勤表說容易也容易,隨便畫個表格,輸入年份日期、星期,然後再手工劃勾就可以用了。但要介紹的是更智能的考勤表,如修改考勤表月份,下面的日期和星期都會自動變化:
實現日期和星期自動更新有很多種方法,大多是用複雜的函數公式。許多同學看到長公式就頭暈。所以先介紹一個簡單公式+技巧的方法。
第1步 設置可以自動變化的日期
在D3中輸入公式=A2, E3中輸入公式= D3+1,然後複製E3公式到最右。
財務工作中,你是否遇到焦頭爛額不知如何解決的問題呢?
再選中日期行,打開單元格設置窗口 - 數字 - 自定義,右側框中輸入字母 d ( Day的縮寫,日期的自定義代碼,代表天數)
第2步 設置星期
在D5中輸入公式=D3,向右複製公式。然後輸入自定義格式代碼 AAA (日期顯示為星期的自定義代碼)
第3步:屏蔽下月的日期
當本月天數小於31天時,最後幾列為顯示下月的日期。
屏蔽方法:選取後3列,條件格式 - 新建規則 - 使用公式... - 輸入公式
=MONTH(AF3)>MONTH(AC3)
公式說明:用最後三列的月份(month函數可以取得日期所在月份)和前面3列日期的月份進行對比作為條件。
再點【格式】按鈕打開單元格格式設置窗口 - 數字 - 自定義 - 在右側框中輸入3個分號 ;;; (英文狀態)
可以自動更新的日期和星期設置完成。
考勤表的自動變色功能
如果遇到節假日,顏色能否自動顯示,就如日曆中看到的:
於是,蘭色就對條件格式進行了調整,結果顯示帥極了:不但可以變色,而且可以在日期左上角顯示「休」和「班」字。
是不是很想知道是怎麼實現的,下面就蘭色一起做吧。
製作步驟:
1、在考勤表旁輸入節假日和加班日期
2、在考勤表添加一空行,並輸入公式,然後把字體設置為紅色。
=IFERROR(VLOOKUP(B5,$AK:$AL,2,),"")
2、選取考勤表,開始 - 條件格式 - 新建規則 - 添加公式:
=OR(B$4="休",AND(WEEKDAY(B$5,2)>5,B$4<>"班"))
公式原理:根據第4行的文字和用weekday提取的星期數,判斷是否為休息日。
完成!
03
考勤表的查詢功能
先看查詢效果:根據選擇的月份不同,生成對應月份的考勤表:
其實有很多Excel用戶都想實現這樣的查詢功能,只要變換查詢的關鍵信息,就可以生成對應的表格。
做這樣的表是不是很複雜?需要用到很高深的Excel功能,難道是傳說中的VBA功能?
你想多了,做這樣的查詢表其實只需要一個公式。比如今天的考勤表,它的查詢公式為:
=INDIRECT(TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN()))&""
雖然只是一個公式,但看起來有些複雜,大部分新手估計看不太懂。所以蘭色有必要剖析一下這個它。
我們要想根據G3單元格的日期從對應月份的工作表中返回考勤信息,就需要把日期和工作表名關聯起來。所以公式用Text函數從G3中提取年月(G3中看似是年月格式,其實是包含日的),以和工作表名稱保持一致。
=TEXT($F$3,"yyyy年m月")
工作表名有了,接下來生成單元格地址。由於所有考勤表格式完成一致,所以總表的單元格(如A7)要提取的也是各個表A7的內容。也就是說接下來要自動生成公式所在單元格的地址(如A7中生成地址A7),所以蘭色用了:
=ADDRESS(ROW(),COLUMN())
row()和Column()分別返回公式所在單元格的行、列數,然後用Address(行數,列數)生成單元格地址。
它和已生成的工作表名連在一起,正好生成了完成的引用「字符串」
=TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN())
公式生成的字符串只是「字符串」,並不能從對應表中提取數據,所以用Indirect函數把它轉換為可以提取值的引用。
=INDIRECT(TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN()))
好象公式設置好了,但當向下複製公式時,你就會發現當被提取的值為空時顯示0,這顯示不是我們想要的。
其實我們用Vlookup函數提取時也遇到這樣的問題。怎麼把0值轉換為空白,高手們是這樣做的,在公式後面添加 &"",即:
=INDIRECT(TEXT($F$3,"yyyy年m月")&"!"&ADDRESS(ROW(),COLUMN()))&""
到此,公式設置完成。
來源:作者:趙志東、李傑
在這裡相信有許多想要學習會計的同學,大家可以關注小編頭條號,
下方評論區留言:想要學習,並收藏本文;私信小編:學習
即可領取一整套系統的會計學習資料!還可以免費試學會計課程15天!
因領取人數太多,不能及時回復,請大家耐心等待。。。。
文章來源: https://twgreatdaily.com/zh/MF4sOXEBnkjnB-0zaZWB.html