贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

網友問到一個 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,陣列公式)

創作者介紹

學不完.教不停.用不盡

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


留言列表 (1)

發表留言
  • Chloe Shih
  • 感謝您快速地回覆,我已將您指導的內容學習並運用於工作之中了,真的很謝謝您!
  • 不客氣!

    vincent 於 2016/08/17 21:11 回覆

找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼