網友問到:在 Excel 中有一個資料表(如下圖),如何篩選出各個欄位指定的項目?

在下圖左中,資料有四個欄位(零件編號、外形、尺寸、售價),在儲存格G1中輸入篩選條件,要在下圖右中自動列出合於條件的資料清單(欄位:零件編號、售價)。

Excel-以公式執行篩選資料(OFFSET,SMALL,ROW,陣列公式)

 

【公式設計與解析】

1. 篩選售價高於700者

如果你使用自動篩選的功能,則可以自訂篩選條件:

Excel-以公式執行篩選資料(OFFSET,SMALL,ROW,陣列公式)

得到篩選結果:

Excel-以公式執行篩選資料(OFFSET,SMALL,ROW,陣列公式)

 

如果你想要以公式來設計,參考以下的做法:

選取儲存格A1:D19,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:零件編號、外形、尺寸、售價。

Excel-以公式執行篩選資料(OFFSET,SMALL,ROW,陣列公式)

列出合於條件的零件編號:

儲存格F2:{=IFERROR(OFFSET($A$1,SMALL(IF(售價>=$G$1,ROW(售價),""),
ROW(1:1))-1,0),"")}

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

複製儲存格F2,往下各列貼上。

(1) IF(售價>=$G$1,ROW(售價),"")

在售價陣列中列出合於條件的列號(不合條件者傳回空字串)。

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

利用 SMALL 函數由小至至依序找出最小值。ROW(1:1)向下複製公式:ROW(1:1)=1→ROW(2:2)=2→ROW(3:3)=3→…。

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

將第(2)式傳回的列號代入 OFFSET 函數取得對應的儲存格內容。

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

利用 IFERROR 函數將錯誤訊息轉換為顯示空字串(空白)。

同理:

儲存格G2:{=IFERROR(OFFSET($D$1,SMALL(IF(售價>=$G$1,ROW(售價),""),
ROW(1:1))-1,0),"")}

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

複製儲存格G2,往下各列貼上。

 

2. 篩選外形為「圓」

Excel-以公式執行篩選資料(OFFSET,SMALL,ROW,陣列公式)

自行練習:

儲存格F2:{=IFERROR(OFFSET($A$1,SMALL(IF(外形=$G$1,ROW(外形),""),
ROW(1:1))-1,0),"")}

儲存格G2:{=IFERROR(OFFSET($D$1,SMALL(IF(外形=$G$1,ROW(外形),""),
ROW(1:1))-1,0),"")}

 

3. 篩選尺寸為「中」

Excel-以公式執行篩選資料(OFFSET,SMALL,ROW,陣列公式)

自行練習:

儲存格F2:{=IFERROR(OFFSET($A$1,SMALL(IF(尺寸=$G$1,ROW(外形),""),
ROW(1:1))-1,0),"")}

儲存格G2:{=IFERROR(OFFSET($D$1,SMALL(IF(尺寸=$G$1,ROW(外形),""),
ROW(1:1))-1,0),"")}

 

【延伸閱讀-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 發表在 痞客邦 留言(3) 人氣()