2016年8月16日 星期二

Excel-依據日期區間列出各月(跨年)人員清單(OFFSET,陣列公式)

網友問到一個 Excel 的問題:
根據前一篇文章:Excel-依據日期區間列出各月人員清單(OFFSET,ROW,COLUMN,陣列公式),如果日期有跨年時,該如何處理?
本例以2015年7月~12月、2016年1月~6為週期,來列出各月學員的清單。
Excel-依據日期區間列出各月(跨年)人員清單(OFFSET,ROW,COLUMN,陣列公式)

【公式設計與解析】
先選取儲存格A1:C23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:學員、開始、結束。
儲存格E3:
{=IFERROR(OFFSET($A$1,SMALL(IF((DATE(YEAR(開始),MONTH(開始),1)<=
DATE(2015,COLUMN(G:G),1))*(DATE(YEAR(結束),MONTH(結束),1)>=
DATE(2015,COLUMN(G:G),1)),ROW(學員),""),ROW(1:1))-1,0,1,1),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
條件一:DATE(YEAR(開始),MONTH(開始),1)<=DATE(2015,COLUMN(G:G),1)
DATE(YEAR(開始),MONTH(開始),1):利用 DATE 函數找出開始的日期陣列中各月的第1天。
DATE(2015,COLUMN(G:G),1):利用 DATE 函數找出 2015 年7月的第1天。
COLUMN(G:G)=7,向右複製公式時,COLUMN(G:G)=7→COLUMN(H:H)=8→...COLUMN(L:L)=12。
條件二:DATE(YEAR(結束),MONTH(結束),1)>=DATE(2015,COLUMN(G:G),1)
複製儲存格E3,貼至儲存格E3:J24。

儲存格K3:
{=IFERROR(OFFSET($A$1,SMALL(IF((DATE(YEAR(開始),MONTH(開始),1)<=
DATE(2016,COLUMN(A:A),1))*(DATE(YEAR(結束),MONTH(結束),1)>=
DATE(2016,COLUMN(A:A),1)),ROW(學員),""),ROW(1:1))-1,0,1,1),"")}
這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動產生「{}」。
條件一:DATE(YEAR(開始),MONTH(開始),1)<=DATE(2016,COLUMN(A:A),1)
DATE(2016,COLUMN(A:A),1):利用 DATE 函數找出 2016 年1月的第1天。
COLUMN(A:A)=1,向右複製公式時,COLUMN(A:A)=1→COLUMN(B:B)=2→...COLUMN(F:F)=6。
條件二:DATE(YEAR(結束),MONTH(結束),1)>=DATE(2016,COLUMN(A:A),1)
複製儲存格E3,貼至儲存格K3:P24。

沒有留言:

張貼留言

好康東東