贊助廠商

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

搜尋本部落格文章資料

網友問到:在 Excel 中有一個學員受訓的記錄表,如何根據下圖左的日期區間,轉換至下圖中各月的清單報表?

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


【公式設計與解析】

先選取儲存格A1:C23,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:學員、開始、結束。

儲存格E2:

{=IFERROR(OFFSET($A$1,SMALL(IF((MONTH(開始)<=COLUMN(A:A))*(MONTH
(結束)>=COLUMN(A:A)),ROW(學員),""),ROW(1:1))-1,0,1,1),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 自動輸入「{}」。

(1) (MONTH(開始)<=COLUMN(A:A))*(MONTH(結束)>=COLUMN(A:A))

條件一:(MONTH(開始)<=COLUMN(A:A))

判斷開始日期的月份是否小於或等於1月,COLUMN(A:A)向右複製時,COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→ ... 。

修件二:(MONTH(結束)>=COLUMN(A:A))

判斷結束日期的月份是否大或等於1月。

兩個條件之間的運算子『*』,相當於執行邏輯 AND 運算,並且會將 TRUE/FALSE 陣列轉換為 1/0 陣列。


(2) IF(第(1)式,ROW(學員),"")

如果第(1)的條件成立,則傳回成員的列號陣列,否則傳回空白(空字串)


(3) SMALL(第(2)式,ROW(1:1))

SMALL 函數中利用第(2)式,找出傳回的列號中最小值的第 1, 2, 3, ... 個。ROW(1:1)向下複製時,會產生 ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→ ... 。


(4) OFFSET($A$1,第(3)式-1,0,1,1)

根據第(3)傳回的列號代入 OFFSET 函數取得儲存格內容。(注意公式中的『-1』)


(5) IFERROR(第(4)式,"")

當公式傳回錯誤訊息時,利用 IFERROR 函數使其顯示空白(空字串)。

創作者介紹

學不完.教不停.用不盡

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


留言列表 (3)

發表留言
  • 小靜
  • 非常感謝您的解答,已成功跑出資料了。您的分享與指導,實在是受益良多!!
    您在部落格分享的excel技巧,在工作上非常非常有幫助 (按100個讚)
  • 不客氣!謝謝你常常光臨我的部落格。

    vincent 於 2016/08/15 17:07 回覆

  • Chloe Shih
  • 您好:
    看到此篇的說明,讓我受益良多,另想請教您:若起訖日資料(如文中圖左)為跨年份的資料,是否也能以年份分別呈現出像文中圖右的清單報表?

    例如:
    [學員]   [開始]     [結束]
    [A]  [2016/03/02]  [2017/01/05]
    [B]  [2016/05/06]  [2017/05/06]
    [C]  [2017/01/20]  [2017/06/30]

    待您方便之時,再給予我回覆與指教,非常感謝您!!
  • 請參考:http://isvincent.pixnet.net/blog/post/46542223

    vincent 於 2016/08/16 23:32 回覆

  • Tina
  • 您好! 謝謝您的分享,有兩個問題想請教您
    1請問若將您範例中藍底部分和綠底部分分成兩個工作頁(sheet)也可以嗎?儲存格公式要怎麼打呢?
    2若想要依照日期區間(如2017年第11週)來列出人員清單,是否也可以呢?

    謝謝您!
找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼