網友問到一個 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。

詳細說明,可參考前一篇文章:Excel-依據日期區間列出各月人員清單(OFFSET,ROW,COLUMN,陣列公式)

arrow
arrow
    全站熱搜
    創作者介紹
    創作者 vincent 的頭像
    vincent

    學不完.教不停.用不盡

    vincent 發表在 痞客邦 留言(4) 人氣()