2014年9月26日 星期五

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

根據前二篇文章:

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 函數將傳回錯誤值的結果以空白顯示。

沒有留言:

張貼留言

檢視其他文章

好康東東