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

Excel-依指定日期查詢休假名冊(使用INDEX,MATCH,OFFSET函數)

 

【公式設計與解析】

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

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 函數將傳回錯誤訊息者以空字串表示。

學不完.教不停.用不盡文章列表

arrow
arrow
    文章標籤
    Excel INDEX MATCH OFFSET
    全站熱搜

    vincent 發表在 痞客邦 留言(2) 人氣()