2023-01-02_23-34-42.jpg 文章集

網友問到 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,陣列公式)

【延伸閱讀】

Excel-指定日期區間計算平均值

Excel-依日期區間小計數值(SUMPRODUCT)

Excel-計算日期區間裡每天的人數(SUMPRODUCT)

Excel-根據日期時間區間計算特定時段內的人數(SUMPRODUCT)

Excel-列出區間範圍包含指定數值的清單(INDEX,OFFSET,ROW,IFERROR)

Excel-利用SUMPRODUCT、WEEKDAY、INDIRECT計算今天起的日期區間中特定星期幾的數量

Google試算表-從日期和時間清單中計算一週各天裡秒數區間的次數(SUMPRODUCT,WEEKDAY)

Excel-解析OFFSET函數

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

arrow
arrow

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