學不完．教不停．用不盡

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

【公式設計與解析】

選取儲存格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」。

(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欄中找出對應的位置，即為所求。