Excel考勤表太牛了！有自動變色和查詢功能！

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天！

因領取人數太多，不能及時回復，請大家耐心等待。。。。