2017年8月29日 星期二

Excel-依日期區間列出符合的清單(SMALL,OFFSET,ROW,陣列公式)

參考下圖,網友問到如何根據人員的回訓欄位,找出符合日期區間的資料列成清單?
Excel-依日期區間列出符合的清單(SMALL,OFFSET,ROW,陣列公式)

【公式設計與解析】
1. 為儲存格範圍命名
選取儲存格C1:C27,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:回訓。

2. 輸入公式
儲存格G2:{=IFERROR(OFFSET($A$1,SMALL(IF((回訓>=$E$2)*(回訓<=$E$3),
ROW(回訓),""),ROW(1:1))-1,0),"")}
儲存格H2:{=IFERROR(OFFSET($C$1,SMALL(IF((回訓>=$E$2)*(回訓<=$E$3),
ROW(回訓),""),ROW(1:1))-1,0),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格G2:H2,貼至儲存格G2:H13。
(1) IF((回訓>=$E$2)*(回訓<=$E$3),ROW(回訓),"")
在陣列公式中使用雙條件:(回訓>=$E$2)*(回訓<=$E$3),當儲存格內容大於或等於儲存格E2且小於或等於儲存格E3者,傳回其列號,否則傳回空字串。(其中『*』相當於執行邏輯 AND 運算)

(2) SMALL(第(2)式,ROW(1:1))
在陣列公式中利用 SMALL 函數由小至大依序取出列號。當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。

(3) OFFSET($A$1,第(2)式-1,0)
利用 OFFSET 函數,將第(2)式傳回的列號代入,取得一個由儲存格A1起始的對應儲存格內容。

(4) IFERROR(第(3)式,"")
因為第(2)式中公式向下複製時,可能傳回錯誤訊息,利用 IFERROR 函數將其顯示為空字串。

沒有留言:

張貼留言

檢視其他文章

好康東東