贊助廠商

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

搜尋本部落格文章資料

根據前二篇文章:

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

創作者介紹

學不完.教不停.用不盡

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


留言列表 (3)

發表留言
  • 訪客
  • 您好 請問若是細分到日期呢?
    我想知道四月每一天每一科有幾個住院病人
    請問要如何設定
    謝謝

    入院日期 出院日期 科別
    104/03/21  104/04/11  一般外科 
    104/03/22  104/04/18  一般外科 
    104/03/22  104/04/25  骨科 
    104/03/23  104/04/30  胸腔外科 
    104/03/24  104/04/02  胸腔外科 
    104/03/24  104/04/07  直腸外科 
    104/03/24  104/04/03  骨科 
    104/03/25  104/04/03  骨科 
    104/03/25  104/04/01  胸腔外科 
    104/03/25  104/04/18  一般外科 
    104/03/26  104/04/01  一般外科 
    104/03/26  104/04/04  骨科 
    104/03/26  104/04/01  一般外科 
    104/03/26  104/04/14  骨科 
    104/03/26  104/04/03  骨科 
    104/03/27  104/04/08  直腸外科 
    104/03/27  104/04/14  一般外科 
  • 請參考:http://isvincent.pixnet.net/blog/post/44954951

    vincent 於 2015/06/12 21:40 回覆

  • 訪客
  • 您好~請問如果欲進行人員每月訓練時數之統計,應如何設定?
    受訓人員 訓練日期 時數
    A 01/05 6
    B 01/06-01/07 12
    C 02/05-02/06 4
    B 02/18-02/20 18
    A 03/05-03/06 10
  • 可參考:http://isvincent.pixnet.net/blog/post/45774469

    vincent 於 2016/01/14 23:05 回覆

  • 訪客_小靜
  • 您好,閱讀您此篇文受益良多,目前我也在整理工作資料,資料類型與您本篇文章舉例相同,只有"期間",我分成"開始日期"與"結束日期",但是"名單"仍呈現不出來。

    以下此我的公式:
    {=IFERROR(OFFSET($A$1,SMALL(IF((DATE(YEAR(受訓開始日),MONTH(受訓開始日),1)<=DATE(2016,COLUMN(A:A)+1,0)*(DATE(YEAR(受訓結束日),MONTH(受訓結束日),1)>=DATE(2016,COLUMN(A:A),1)),ROW(受訓開始日),FALSE),ROW(1:1))-1,,,),"")}

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

    vincent 於 2016/08/13 15:11 回覆

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼