2017年2月14日 星期二

Excel-列出日期區間內的資料(OFFSET,SMALL,ROW,陣列公式)

網友問到 Excel 的問題:如何在含有日期清單的資料中,列出指定日期區間內的資料?
如下圖左,有一個「日期、項目、數值」組成的資料清單,想要找出指定的兩個日期之間的資料(參考下圖右)。以下提供三種方式來練習。
Excel-列出日期區間內的資料(OFFSET,SMALL,ROW,陣列公式)

1. 手動:使用篩選工具
(1) 啟用自動篩選,篩選「日期」欄位。
(2) 選取[日期篩選/自訂篩選]選項。
Excel-列出日期區間內的資料(OFFSET,SMALL,ROW,陣列公式)
(3) 設定「之後」的日期為起始日期-1;設定「之前」的日期為終止日期+1。
image
最後結果即為該日期區間的資料。
image

2. 自動:設計公式
先選『日期』欄位中有資料的所有儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。
儲存格F2:{=IFERROR(OFFSET(A$1,SMALL(IF((日期>=$E$2)*(日期<=$E$4),
ROW(日期),""),ROW(1:1))-1,0),"")}
這是陣列公式,輸入完成要 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
複製儲存格F2,貼至儲存格F2:H13。
(1) IF((日期>=$E$2)*(日期<=$E$4),ROW(日期),"")
(日期>=$E$2)*(日期<=$E$4):在 IF 函數中使用雙條件「日期>=$E$2 和 日期<=$E$4」,其中『*』運算子相當於執行邏輯 AND 運算。若雙條件均成立時,傳回對應的日期列號陣列。
(2) SMALL(第(1)式,ROW(1:1))
利用 SMALL 函數找出符合條件的日期所對應由小到大的列號。其中公式向下複製時,會產生 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
(3) OFFSET(A$1,第(2)式-1,0)
利用第(2)式傳回的列號,代入 OFFSET 函數找出對應的儲存格。
(4) IFERROR(第(3)式,"")
若第(3)式傳錯誤訊息時,利用 IFERROR 函數改以空白顯示。
Excel-列出日期區間內的資料(OFFSET,SMALL,ROW,陣列公式)
換個方式寫公式:
儲存格F2:{=IFERROR(OFFSET(A$1,LARGE((日期>=$E$2)*(日期<=$E$4)*
ROW(日期),ROW(1:1))-1,0),"")}
這是陣列公式,輸入完成要 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
複製儲存格F2,貼至儲存格F2:H13。
其結果會和上式的結果順序相反。
Excel-列出日期區間內的資料(OFFSET,SMALL,ROW,陣列公式)

沒有留言:

張貼留言

檢視其他文章

好康東東