學不完．教不停．用不盡

延續前一篇文章：Excel-將整年的資料清單分配到12個月的資料表中(OFFSET,MATCH)

如果你的原始資料清單中不是一個月中的每一天都有資料，而且呈現的時候只想要呈現有日期的資料，該如何處理？

以下是三個月的原始資料，有些日期沒有資料。

 

【公式設計與解析】

先選取[全年]工作表中的所有日期欄位儲存格，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：日期。

1. 列出一月份清單

儲存格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. 列出二月份清單

儲存格C2：{=IFERROR(OFFSET(全年!C$1,SMALL(IF(MONTH(日期)=2,ROW(日期),
""),ROW(1:1))-1,0),"")}

這是陣列公式，輸入完成要按 Ctrl+Shfit+Enter 鍵，Excel 會自動加上「｛｝」。

複製儲存格C2，貼至儲存格C2:G31。

 

3. 列出三月份清單

儲存格C2：{=IFERROR(OFFSET(全年!C$1,SMALL(IF(MONTH(日期)=3,ROW(日期),
""),ROW(1:1))-1,0),"")}

這是陣列公式，輸入完成要按 Ctrl+Shfit+Enter 鍵，Excel 會自動加上「｛｝」。

複製儲存格C2，貼至儲存格C2:G31。