2016年11月19日 星期六

Excel-清單資料轉換為表格資料(OFFSET,陣列公式)

有網友問到:在 Excel 中有一個資料清單(如下圖左),如何轉換為表格形式(如下圖右)?
在下圖左的資料清單是由類別和項目組成,在下圖右的表格中將相同的類別的項目集合在一起,該如何設計公式?
Excel-清單資料轉換為表格資料(OFFSET,陣列公式)

【公式設計與解析】
為了幫助公式理解,請先選取儲存格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 函數將錯誤訊息轉換為空白。

在下圖中,如果類別是不規則的排列(試和上圖比較),原公式仍可以得到想要的結果(項目內容的排序不同而已)。
Excel-清單資料轉換為表格資料(OFFSET,陣列公式)

沒有留言:

張貼留言

好康東東