學不完．教不停．用不盡

在 Excel 中有一個訂購清單（下圖左），如何轉換為如下圖右的格式？（原本直式的清單，轉變為表格式的清單）

 

【公式設計與解析】

選取儲存格A1:A25，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：訂購者。

儲存格E2：=IF(COLUMN(A:A)<=COUNTIF(訂購者,$D2),OFFSET($B$1,
MATCH($D2,訂購者,0)+COLUMN(A:A)-1,0),"")

選取儲存格E2，複製到儲存格E2:H9。

(1) MATCH($D2,訂購者,0)

找尋儲存格D2，在訂購者陣列中的第一個位置。（傳回一個數值）

(2) OFFSET($B$1,MATCH($D2,訂購者,0)+COLUMN(A:A)-1,0)

將第(1)式傳回的數值代入 OFFSET 函數傳回對應的儲存格內容。當公式向右複製時，COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→...。

(3) COLUMN(A:A)<=COUNTIF(訂購者,$D2)

判斷COLUMN(A:A)=1是否小於或等於儲存格D2在訂購者陣列中的數量。當公式向右複製時，COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→...。

(4) IF(COLUMN(A:A)<=COUNTIF(訂購者,$D2),第(2)式,"")

用以在顯示超過儲存格D2在訂購者陣列中的數量時，顯示空白。（否則，顯示第(2)式內容。）