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

arrow
arrow
    全站熱搜

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