2017年1月11日 星期三

Excel-在日期清單中依月份列出名冊(OFFSET,SMALL,ROW,MONTH,陣列公式)

網友問到:在 Excel 的工作表中有一個日期清單,如何依月份列出名冊?(參考下圖)
Excel-在日期清單中依月份列出名冊(OFFSET,SMALL,ROW,MONTH,陣列公式)

【公式設計與解析】
儲存格D2:{=IFERROR(OFFSET($B$1,SMALL(IF(MONTH($A$2:$A$44)=
COLUMN(A:A),ROW($A$2:$A$44),""),ROW(1:1))-1,0),"")}
這是陣列公式,輸入完成按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格D2,貼至儲存格D2:H13。

(1) IF(MONTH($A$2:$A$44)=COLUMN(A:A),ROW($A$2:$A$44),"")
在陣列公式中找出每個日期符合各欄位月份的列號。(參考以下示意圖)
Excel-在日期清單中依月份列出名冊(OFFSET,SMALL,ROW,MONTH,陣列公式)

(2) SMALL(第(1)式,ROW(1:1))
利用 SMALL 函數由小至大依序取出列號。ROW(1:1)向下複製公式時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→...。
(在 SMALL 函數中可能出現錯誤訊息(#NUM!))
image

(3) OFFSET($B$1,第(2)式-1,0)
依據第(2)式中的列號代入 OFFSET 函數,可以取得對應儲存格的內容。
Excel-在日期清單中依月份列出名冊(OFFSET,SMALL,ROW,MONTH,陣列公式)

(4) IFERROR(第(3)式,"")
利用 IFERROR 函數將錯誤訊息轉換為空白(空字串)。

沒有留言:

張貼留言

檢視其他文章

好康東東