2017年3月21日 星期二

Excel-將整年的資料清單分配到12個月的資料表中(OFFSET,MATCH)2

如果你的原始資料清單中不是一個月中的每一天都有資料,而且呈現的時候只想要呈現有日期的資料,該如何處理?
以下是三個月的原始資料,有些日期沒有資料。
Excel-將整年的資料清單分配到12個月的資料表中(OFFSET,MATCH)

【公式設計與解析】
先選取[全年]工作表中的所有日期欄位儲存格,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。
1. 列出一月份清單
Excel-將整年的資料清單分配到12個月的資料表中(OFFSET,MATCH)
儲存格C2:{=IFERROR(OFFSET(全年!C$1,SMALL(IF(MONTH(日期)=1,ROW(日期),
""),ROW(1:1))-1,0),"")}
這是陣列公式,輸入完成要按 Ctrl+Shfit+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格C2,貼至儲存格C2:G31。
(1) IF(MONTH(日期)=1,ROW(日期),"")
在日期陣列公式中判斷是否月份為1(1月份),若是,則傳回列號,若否,則傳空字串。其中 MONTH 函數用以傳回日期的月份,ROW 函數傳回儲存格列號。
(2) SMALL(第(1)式,ROW(1:1))
利用 SMALL 函數依序由小到大取出列號的最小值。當公式向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。
(3) OFFSET(全年!C$1,第(2)式-1,0)
將第(2)式代入 OFFSET 函數,根據第(2)式傳回的列號取得對應儲存格的內容。
(4) IFERROR(第(3)式,"")
在執行第(2)式時可能傳回錯誤訊息,藉 IFERROR 函數將錯誤訊息顯示為空字串(空白)。

2. 列出二月份清單
Excel-將整年的資料清單分配到12個月的資料表中(OFFSET,MATCH)
儲存格C2:{=IFERROR(OFFSET(全年!C$1,SMALL(IF(MONTH(日期)=2,ROW(日期),
""),ROW(1:1))-1,0),"")}
這是陣列公式,輸入完成要按 Ctrl+Shfit+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格C2,貼至儲存格C2:G31。

3. 列出三月份清單
Excel-將整年的資料清單分配到12個月的資料表中(OFFSET,MATCH)
儲存格C2:{=IFERROR(OFFSET(全年!C$1,SMALL(IF(MONTH(日期)=3,ROW(日期),
""),ROW(1:1))-1,0),"")}
這是陣列公式,輸入完成要按 Ctrl+Shfit+Enter 鍵,Excel 會自動加上「{}」。
複製儲存格C2,貼至儲存格C2:G31。

沒有留言:

張貼留言

檢視其他文章

好康東東