學不完．教不停．用不盡

有網友問到：在 Excel 中有一個資料清單(如下圖左)，如何轉換為表格形式(如下圖右)？

在下圖左的資料清單是由類別和項目組成，在下圖右的表格中將相同的類別的項目集合在一起，該如何設計公式？

【公式設計與解析】

為了幫助公式理解，請先選取儲存格A1:B25，按 Ctrl+Shift+F3 鍵，勾選「頂端列」，定義名稱：類別、項目。

儲存格E2：{=IFERROR(OFFSET($B$1,SMALL(IF(類別=$D2,ROW(類別),""),
COLUMN(A:A))-1,0),"")}

這是陣列公式，輸入完成要按 Ctrl+Shift+Enter 鍵，Excel 自動加上「｛｝」。

(1) IF(類別=$D2,ROW(類別),"")

在陣列公式中，如果類別陣列的內容和儲存格D2相同者，傳回其列號，若不相同，則傳回空字串(『""』)(其目的是為了製造一個錯誤訊息)。

(2) SMALL(IF(類別=$D2,ROW(類別),""),COLUMN(A:A))

利用 SMALL 函數傳回第(1)式中的列號最小值(COLUMN(A:A)=1)，當公式向右複製時，COLUMN(A:A)=1→COLUMN(B:B)=2→COLUMN(C:C)=3→...，可以分別取出第1, 2, 3, ... 的最小值。

(3) OFFSET($B$1,SMALL(IF(類別=$D2,ROW(類別),""),COLUMN(A:A))-1,0)

將第(2)式傳回的列號代入 OFFSET 函數可以找到對應的『項目』陣列內容。若是代入 OFFSET 函數的是空字串，則會傳回一個錯誤訊息。

(4) IFERROR(第(3)式,"")

利用 IFERROR 函數將錯誤訊息轉換為空白。

在下圖中，如果類別是不規則的排列(試和上圖比較)，原公式仍可以得到想要的結果(項目內容的排序不同而已)。