贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

有網友問到:在 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,陣列公式)

創作者介紹

學不完.教不停.用不盡

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


留言列表 (2)

發表留言
  • Pei Lu
  • 非常實用的方式
    對我現在想做升學檢索訊息很有用
    感謝...
  • 不用客氣!謝謝你光臨這個網站。

    vincent 於 2016/11/20 20:12 回覆

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼