參考下圖,有網友想要知道在一個 Excel 的項目清單中,如何根據每一個項目,在同列中列出相關的內容?

【準備工作】

選取儲存格A1:B16,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、內容。

【輸入公式】

儲存格E2:{=IFERROR(OFFSET($B$1,SMALL(IF($D2=項目,ROW(內容),FALSE),
COLUMN(A:A))-1,,,),"")}

這是陣列公式,輸入完成要按 Ctrl+Shfit+F3 鍵,Excel 自動產生「{  }」。

複製儲存格E2,貼至儲存格E2:J5。

(1) IF($D2=項目,ROW(內容),FALSE)

在陣列公式中判斷儲存格D2的內容和「項目」陣列中的每一個項目是否相同,若是,則傳回該儲存格的列號;若否,則傳回 FALSE

本例傳回陣列:{2; 3; FALSE; FALSE; FALSE; FALSE; 8; ... }

(2) SMALL(IF($D2=項目,ROW(內容),FALSE),COLUMN(A:A))

根據 (1) 的結果代入 (2) 式中,當 COLUMN(A:A)=1,往右複製時會產生COLUMN(B:B)=2、COLUMN(C:C)=3、...。

SMALL 函數中,本例會傳回結果 2(第一個最小值)。往右複製後,會傳回 2, 3, 8, ...。

(3) OFFSET($B$1,SMALL(IF($D2=項目,ROW(內容),FALSE),COLUMN(A:A))-1,,,)

將 (2) 的結果代入 OFFSET 函數,在「內容」陣列中取得對應的內容。

(4) 最後藉由 IFERROR 函數將儲存格傳回錯誤訊息時(查詢不到對應值時),改設定為空白。

 

【延伸閱讀-FILTER函數篩選應用】

Excel-下拉式清單選取月份列出該月日期

圖片1 Excel-根據單條件和雙條件篩選資料(FILTER)

圖片1 Excel-列出指定星期幾的日期

圖片1 Excel-單一欄位篩選與跨欄位篩選

圖片1 Excel-計算分組最大值

圖片1 Excel-FILTER和OFFSET的動態陣列

圖片1 Excel-篩選資料並轉置資料

圖片1 Excel-2021版新增函數進行篩選、查詢、排序之綜合練習

圖片1 Excel-由資料清單中篩選一組

圖片1 Excel-利用FILTER函數模糊篩選

圖片1 Excel-從日期清單中區別平日和假日計算總和

圖片1 Excel-列出非空白項目的清單(比較篩選函數和以陣列公式模擬篩選)

圖片1 Excel-使用傳統陣列和動態陣列公式列出模糊搜尋清單

圖片1 Excel-FILTER函數與進階篩選

圖片1 Excel-2021版新增函數的使用

arrow
arrow
    全站熱搜

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