網友問到:在 Excel 中有一個上百筆資料的日期清單,如何依所挑選的月份,用公式篩選出符合的清單。

當然,在 Excel 中,你可以使用「篩選」工具來手動挑出想要月份的日期清單。本篇文章是要利用公式來自動篩選想要月份的日期清單。以下分成二個部分來說明。

1. 月份以數字來篩選

月份以「1, 2, …, 12」的數字在下拉式清單中選取。(下拉式清單可以藉由「資料驗證」來製作)

Excel-用公式依月份篩選資料(陣列公式,OFFSET,MATCH)

選取日期的所有儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。

儲存格G2:{=IFERROR(OFFSET(A$1,SMALL(IF(MONTH(日期)=$E$2,ROW(日期),
""),ROW(1:1))-1,0),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格G2,貼至儲存格G2:I17。

(1) IF(MONTH(日期)=$E$2,ROW(日期),"")

在日期陣列中比對是否和儲存格E2中的日期相符,若是,則傳回該日期的列號,若否,則傳回空字串。ROW 函數可以取得儲存格的列號。

(2) SMALL(第(1)式,ROW(1:1))

利用 SMALL 函數取得第(1)式中所傳回日期中依序由小至大的最小值。

當公式向下複製時,ROW(1:1)=1→ROW(2:2)=1→ROW(3:3)=3→…。

(3)OFFSET(A$1,第(2)式-1,0)

將第(2)式傳回的列號代入 OFFSET 函數查詢對應的儲存格內容。

(4) IFERROR(第(3)式,"")

因為查詢結果可能會有錯誤訊息,所以利用 IFERROR 將錯誤訊息顯示為空白(空字串)。

當你由下拉式清單中挑選一個月份數字時,公式即會帶出該月份的資料清單。


2. 月份以文字來篩選

月份以「一月, 二月, …, 十二月」的文字在下拉式清單中選取。

Excel-用公式依月份篩選資料(陣列公式,OFFSET,MATCH)

為了簡化公式,先定義一個名稱:月份。

參照公式:=MATCH(工作表3!$E$2,{"一月","二月","三月","四月","五月","六月",
"七月","八月","九月","十月","十一月","十二月"},0)

利用 MATCH 函數將儲存格E2中的內容和一月~十二月比對,傳回代表位置的數字(一月~十二月對應為 1~12)。

Excel-用公式依月份篩選資料(陣列公式,OFFSET,MATCH)

儲存格G2:{=IFERROR(OFFSET(A$1,SMALL(IF(MONTH(日期)=月份,ROW(日期),
""),ROW(1:1))-1,0),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格G2,貼至儲存格G2:I17。

特別注意:MONTH(日期)=月份,其中的「月份」是已經定義好的名稱,並且內容是公式,所以月份會傳回一個數字。

其餘公式的相關原理,同「1. 月份以數字來篩選」。

文章標籤
創作者介紹

學不完.教不停.用不盡

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