贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

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

文章標籤
創作者介紹

學不完.教不停.用不盡

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


留言列表 (1)

發表留言
  • 悄悄話
找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼