網友問到:在 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 函數將錯誤訊息轉換為空白(空字串)。

文章標籤
創作者介紹

學不完.教不停.用不盡

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