學不完．教不停．用不盡

網友問到：在 Excel 中有一個學員受訓的記錄表，如何根據下圖左的日期區間，轉換至下圖中各月的清單報表？

 

【公式設計與解析】

先選取儲存格A1:C23，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：學員、開始、結束。

儲存格E2：

{=IFERROR(OFFSET($A$1,SMALL(IF((MONTH(開始)<=COLUMN(A:A))*(MONTH
(結束)>=COLUMN(A:A)),ROW(學員),""),ROW(1:1))-1,0,1,1),"")}

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

(1) (MONTH(開始)<=COLUMN(A:A))*(MONTH(結束)>=COLUMN(A:A))

條件一：(MONTH(開始)<=COLUMN(A:A))

判斷開始日期的月份是否小於或等於1月，COLUMN(A:A)向右複製時，COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→ ... 。

修件二：(MONTH(結束)>=COLUMN(A:A))

判斷結束日期的月份是否大或等於1月。

兩個條件之間的運算子『*』，相當於執行邏輯 AND 運算，並且會將 TRUE／FALSE 陣列轉換為 1／0 陣列。

 

(2) IF(第(1)式,ROW(學員),"")

如果第(1)的條件成立，則傳回成員的列號陣列，否則傳回空白(空字串)

 

(3) SMALL(第(2)式,ROW(1:1))

在 SMALL 函數中利用第(2)式，找出傳回的列號中最小值的第 1, 2, 3, ... 個。ROW(1:1)向下複製時，會產生 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→ ... 。

 

(4) OFFSET($A$1,第(3)式-1,0,1,1)

根據第(3)傳回的列號代入 OFFSET 函數取得儲存格內容。(注意公式中的『-1』)

 

(5) IFERROR(第(4)式,"")

當公式傳回錯誤訊息時，利用 IFERROR 函數使其顯示空白(空字串)。