學不完．教不停．用不盡

根據前二篇文章：

Excel-在日期區間數列中小計分年分月的人數(SUMPRODUCT)
Excel-在日期區間數列中小計分年分月的人數2(SUMPRODUCT)

本篇要延伸列出每個月通過認證的名單，本例僅以 2014 年1 ~ 12 月為例，參考下圖。

【準備工作】

選取欄A和欄B中所有資料的儲存格，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：成員、期間。

【輸入公式】

儲存格D2：｛=IFERROR(OFFSET($A$1,SMALL(IF((DATE(MID(期間,1,4),
MID(期間,6,2),1)<=DATE(2014,COLUMN(A:A)+1,0))*(DATE(MID(期間,9,4),
MID(期間,14,2),1)>=DATE(2014,COLUMN(A:A),1)),ROW(期間),FALSE),
ROW(1:1))-1,,,),"")｝

這是陣列公式，輸入完畢要按 Ctrl+Shfit+Enter 鍵，Excel 自動產生「{  }」。

複製儲存格D2，貼至儲存格D2:O27。

(1)

IF((DATE(MID(期間,1,4),MID(期間,6,2),1)<=DATE(2014,COLUMN(A:A)+1,0))*
(DATE(MID(期間,9,4),MID(期間,14,2),1)>=DATE(2014,COLUMN(A:A),1)),
ROW(期間),FALSE)

該公式乃要判斷期間陣列中頭尾月份中是否包含1月(COLUMN(A:A)=1)，若是則傳回儲存格在期間中的第幾個，否則傳回 False。

本例傳回陣列：{FALSE,FALSE,FALSE,FALSE,FALSE,...,20,FALSE,...}，其中有19個FALSE，接著20，接著6個FASLE。

(2)

SMALL(上述(1)公式,ROW(1:1))：在陣列公式中利用 SMALL 函數找出符合條件的第一個最小值位置。本例傳回：20。

(3)

OFFSET($A$1,SMALL(上述(1)公式,ROW(期間),FALSE),ROW(1:1))-1,,,)

將上述 (2) 公式所得的數值(符合條件的第一個最小值位置)代入 OFFSET 函數，傳回以儲存格A1為起點的相對位置。本例傳回：儲存格A20。

(4)

IFERROR(OFFSET($A$1,SMALL(上述(1)公式,ROW(期間),FALSE),ROW(1:1))-1,,,),"")

藉由 IFERROR 函數將傳回錯誤值的結果以空白顯示。