2016年9月24日 星期六

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

網友問到的 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欄中找出對應的位置,即為所求。

沒有留言:

張貼留言

檢視其他文章

好康東東