2017年3月15日 星期三

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

網友問到:在 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. 月份以數字來篩選」。

沒有留言:

張貼留言

好康東東