117.Excel教學-靠 vlookup 函數神器,自動請假更新排班表系統 @ Mr.Lazy愛偷懶先生

117. Excel教學-靠 vlookup 函數神器,自動請假更新排班表系統

Dear All,

設計過2022年自動化,月週排班表神器,自動顯示放假日期後，如果員工請事假、病假、特休等休假想要顯示在輪班表上的話，使用VLOOKUP函數就可以解決。

使用版本: Excel 2019。

使用到函數、功能:

分類	函數
邏輯函數	IF、IFS、IFERROR
查閱與參照函數	VLOOKUP
功能	資料驗證

完成圖:

VLOOKUP函數進階使用

讀者大多認知的VLOOKUP函數，是透過單一查閱值來查找「特定數值」，是一種「線性」方式。

如果VLOOKUP函數想執行「面性/表格式」模式的話，需透過兩數值合併成新的查閱值之後，再透過下文的提到的「判斷式」，作為特定數值連結執行。

教學步驟

Step 1: 建立請假清單表格

範例檔案，可點選連結下載。

Step 2: 公式設計/編號說明

編號1~2:

使用【資料驗證】功能，選擇「清單」，來建立下拉式選單。

位置: 資料→資料工具→資料驗證→選擇清單→輸入下拉選單內容。

編號3:

因為「請假事由」需要跑人事流程，不是申請後馬上通過。因此，需要依靠「流程狀況」來判斷是否申請完成，以G2儲存格為例:

G2儲存格公式:

=IFERROR(IFS(F2="獲准","完成",F2="申請中","申請中",F2="主管核准中","申請中",F2="退回","失敗"),"")

IFS函數	說明
F2="獲准","完成"	當F2=”獲准”時，顯示「完成」
F2="申請中","申請中"	當F2=”申請中”時，顯示「申請中」
F2="主管核准中","申請中"	當F2=”獲准”時，顯示「申請中」
F2="退回","失敗"	當F2=”退回”時，顯示「失敗」

關於G欄位 (編號3) 的顏色顯示:

透過格式化的條件→新增規則來新增。可參閱以下文章:

編號4:

「判斷式」是作為VLOOKUP函數，判斷是否要執行/顯示請假事由 (E欄位) 在【排班表】上的基準。

如上述 (編號3) 說到，請假流程必須通過核准後才會生效，因此必須依G欄位顯示結果，作為判斷依據。

另外，為了避免當E、F欄位空白時產生錯誤，所以將此情況加入公式中。以A2儲存格為例:

A2儲存格公式:

=IF(G2="申請中","",IF(G2="失敗","",IF(AND(E2="",F2=""),"",C2&D2)))

IF函數	說明
IF(G2="申請中","",	當G2=”申請中”時，顯示空白，否則執行。
IF(G2="失敗","",	否則執行: 當G2=”失敗”時，顯示空白，否則執行。
IF(AND(E2="",F2=""),"", C2&D2	否則執行: 當E2、F2儲存格皆等於空白時，顯示空白，否則執行C2和D2的數值串接。

Step 2: 【排班表】公式設計

C5儲存格公式:

=IFERROR(IFERROR(VLOOKUP($B5&C$3,請假清單!$A$1:$G$12,5,0),VLOOKUP(排班表!C$3,'2022年放假清單'!$A:$B,2,0)),"")

以上公式可拆解成:

◆ VLOOKUP($B5&C$3,請假清單!$A$1:$G$12,5,0)
◆ VLOOKUP(排班表!C$3,'2022年放假清單'!$A:$B,2,0)
◆ 和2個IFERROR函數。

◆ VLOOKUP($B5&C$3,請假清單!$A$1:$G$12,5,0)

VLOOKUP函數	公式拆解	結果
查閱值依據	$B5&C$3	透過「判斷式」判斷，核對顯示「請假事由」。
查閱範圍	請假清單!$A$1:$G$12
左至右欄位編號	5
是否符合	0

上文提到的查閱值依據，不再使用單一數值，而是使用數值串接數值的方式，作為「查閱範圍」的參照依據，此查閱值產生的數值洽等於「判斷式」的數值。

工作表	儲存格欄位	產生數值
排班表	$B5&C$3	雪靈44652
請假清單	判斷式A2儲存格	雪靈44652

關於鎖定$「搜尋值」欄位方式，以 $B5&C$3 為例:

◆ 當公式向右拉時，因為 B5 欄位不會移動；向下拉時，會移動，所以只鎖定 B，不鎖定5。(藍色箭頭)
◆ 當公式向右拉時，因為 C3 欄位會移動；向下拉時，不會移動，所以只不鎖定 C，鎖定3。(紅色箭頭)

◆ VLOOKUP(排班表!C$3,'2022年放假清單'!$A:$B,2,0)

VLOOKUP函數	公式拆解	結果
查閱值依據	排班表!C$3	透過C$3 (日期) 判斷，顯示「國定假日」。
查閱範圍	'2022年放假清單'!$A:$B
左至右欄位編號	2
是否符合	0

查閱值僅考慮日期欄位(排班表第3列)。關於查閱值鎖定，以排班表!C$3為例:

◆ 當公式向右拉時，因為 C3 欄位會移動；向下拉時，不會移動，所以只部鎖定C，鎖定3。

◆ 和2個IFERROR函數。

關於IFERROR應用&介紹，可參閱以下:

◆ 【排班表】公式合併說明:

開始執行	VLOOKUP($B5&C$3,請假清單!$A$1:$G$12,5,0)
如果失敗，再次執行
執行	VLOOKUP(排班表!C$3,'2022年放假清單'!$A:$B,2,0)
如果失敗，再次執行
執行	顯示空白。