贊助廠商

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

搜尋本部落格文章資料

廣告贊助

網友問到的 Excel 問題:如下圖左有一個人員的排班基本表,其中每個日期分為二列,分別是早班和晚班的人員輪值名單,現在要轉換為下圖右的人員和日期的彙整表,該如何處理?對照

Excel-資料表重組(SUMPRODUCT,OFFSET,MATCH)

 

【公式設計與解析】

選取儲存格A1:A21,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:日期。

儲存格I2:=OFFSET($B$1,SUMPRODUCT((OFFSET($C$1,MATCH($H2,
日期,0),0,2,4)=I$1)*ROW(OFFSET($C$1,MATCH($H2,日期,0),0,2,4)))-1,0)

複製儲存格I2,貼至儲存格I2:P11。

(1) MATCH($H2,日期,0)

查詢儲存格H2在日期陣列中的位置(列號),因為每一個日期都有二個符合,但是只會傳回第1個(列號較小者)。本例傳回「2」。

Excel-資料表重組(SUMPRODUCT,OFFSET,MATCH)

(2) OFFSET($C$1,第(1)式,0,2,4)

利用第(1)式的傳回值找出符合該日期的儲存格範圍,例如:儲存格H2為『9/1』,會傳回儲存格C2:F3。

(3) ROW(第(2)式)

利用第(2)式的儲存格範圍,藉由 ROW 函數傳回每個儲存格的列號陣列。以儲存格C2:F3為例,在 SUMPRODUCT 函數中會傳回 {2,3}。

(4) SUMPRODUCT((第(2)式=I$1)*ROW(第(2)式)

SUMPRODUCT 函數中的條件:第(2)式=I$1,因為本例只會有一個符合,所以乘以ROW(第(2)式後,即可得符合者的列號。公式=SUMPRODUCT({0,0,0,0,0,0,0,3},最後傳回『3』,表示『甲』在『第3列』。

(5) OFFSET($B$1,第(4)式-1,0)

根據第(4)式的傳回值,代入 OFFSET 函數於B欄中找出對應的位置,即為所求。

創作者介紹

學不完.教不停.用不盡

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


留言列表 (1)

發表留言
  • 訪客
  • 版大請教,日期及班別是沒有規律,公式要怎麼改?
    感謝不吝賜教!

    例如:
    日期 │班別│A員│B員│C員│D員│
    09/01│早班│ 乙 │ 丙 │ 丁 │ 戊 │
    09/02│早班│ 戊 │ 己 │ 庚 │ 辛 │
    09/02│晚班│ 甲 │ 乙 │ 丙 │ 丁 │
    09/03│早班│ 甲 │ 乙 │ 丙 │ 丁 │
    09/03│晚班│ 戊 │ 己 │ 庚 │ 辛 │
    09/04│晚班│ 辛 │ 甲 │ 乙 │ 丙 │
    09/05│早班│ 庚 │ 辛 │ 甲 │ 乙 │
    09/05│晚班│ 丙 │ 丁 │ 戊 │ 己 │
    ===================================
    日期 │ 甲 │ 乙 │ 丙 │ 丁 │ 戊 │ 己 │ 庚 │ 辛 │
    09/01│  │早班│早班│早班│早班│  │  │  │
    09/02│晚班│晚班│晚班│晚班│早班│早班│早班│早班│
    09/03│早班│早班│早班│早班│晚班│晚班│晚班│晚班│
    09/04│晚班│晚班│晚班│  │  │  │  │晚班│
    09/05│早班│早班│晚班│晚班│晚班│晚班│早班│早班│
  • 請參考:http://isvincent.pixnet.net/blog/post/47099854

    vincent 於 2017/03/09 22:24 回覆

找更多相關文章與討論

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

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

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

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

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼