回答網友提問:如何在 Excel 中,由原始清單中挑出符合指定內容的清單?

如下圖,如何由原始清單中(如下圖左),挑出符合指定內容的清單(如下圖左)?

Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET).xlsx

 

【公式設計與解析】

如果不想大費周章的寫公式,篩選工具是你很好的選擇,而且很容易即可完成。本篇是要以撰寫公式來完成這個工作。

Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET)

儲存格F4:{=IFERROR(OFFSET($D$1,SMALL(IF($B$2:$B$16=$F$2,
ROW($B$2:$B$16),""),ROW(1:1))-1,0),"")}

這是陣列公式,輸入完成要按 Ctrl+Shift+Enter 鍵,Excel 會自動加上「{}」。

複製儲存格F4,貼至儲存格F4:F16。

(1) IF($B$2:$B$16=$F$2,ROW($B$2:$B$16),"")

在陣列公式中判斷儲存格F2的內容是否和儲存格B2:B16中的每一個儲存格的內容相符,若是,則傳回該儲存格的列號;否則傳回空字串。在陣列公式中,ROW($B$2:$B$16)={2,3,4, ... , 14,15,16}。 

(2) SMALL(第(1)式,ROW(1:1))

利用 SMALL 函數找出第(1)式傳回的列號之最小值。其中,ROW(1:1)向下複製時,ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→....。可以分別取出第 1,2,3,… 較小值。

(3) OFFSET($D$1,第(2)式-1,0)

將第(2)式依序取出的列號,置入 OFFSET 函數中,得到對應的儲存格內容。

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

因為第(3)式當公式向下複製時,有可能傳回錯誤訊,所以利用 IFERROR 函數將錯誤訊息以空字串顯示。

所以,當儲存格F2改變時,可以顯示對應的清單。

Excel-由原始清單中挑出符合指定內容的清單(ROW,SMALL,OFFSET).xlsx

  文章集

【延伸閱讀: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
    文章標籤
    Excel ROW SMALL OFFSET
    全站熱搜

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