學不完．教不停．用不盡

回答網友提問：在下圖中有一個排班休假的報表，如何依指定日期列出休假者的清冊。

【公式設計與解析】

為了簡化公式的長度，也為了方便理解與說明，所以使用了一個輔助欄位。

1. (輔助欄位)找出指定日期那一欄的內容

儲存格M2： =OFFSET($A$1,ROW(A1),MATCH($K$2,$B$1:$I$1))

複製儲存格M2，貼至儲存格M2:M23。

(1) MATCH($K$2,$B$1:$I$1)

找出儲存格K2內容在儲存格B1:I1中的位置，傳回一個數字。

(2) OFFSET($A$1,ROW(A1),MATCH($K$2,$B$1:$I$1))

將第(1)式的結果代入 OFFSET 函數，找出以儲存格A1為起始，列編號2的儲存格內容。(OFFSET 函數以編號 0 為第 1 列)

ROW 函數會傳回儲存格的列號，向下複製公式時，ROW(A1)=1→ROW(A2)=2→ROW(A3)=3→…。

2. (輔助欄位)找出指定日期那一欄有「休」的列號

儲存格N2：{=SMALL(IF($M$2:$M$23="休",ROW($I$2:$I$23),""),ROW(A1))}

這是陣列公式，輸入完成要 Ctrl+Shift+Enter 鍵，Excel 會自動加上「｛｝」。

複製儲存格N2，貼至儲存格N2:N23。

(1) IF($M$2:$M$23="休",ROW($I$2:$I$23),"")

在陣列公式中，判斷儲存格M2:M23裡內容為「休」者，傳回其列號。

(2) SMALL(IF($M$2:$M$23="休",ROW($I$2:$I$23),""),ROW(A1))}

根據第(1)式傳回的列號，利用 SMALL 函數，由小至大依序取出列號數值。

3. 找出指定日期那一欄有「休」對應的姓名

儲存格L2：=IFERROR(INDEX($A$2:$A$23,N2-1),"")

複製儲存格L2，貼至儲存格L2:L23。

(1) INDEX($A$2:$A$23,N2-1)

根據N欄傳回值，代入 INDEX 函數查詢對應的儲存格內容。N2-1是因為傳回值N2是絶對位置，而查詢資料A2:A23時使用相對的位置，這二者差 1。

(2) IFERROR(INDEX($A$2:$A$23,N2-1),"")

利用 IFERROR 函數將傳回錯誤訊息者以空字串表示。