根據 Excel 資料表中(參考下左)的資料清單,想要篩選出合於類別並且和指定數量接近的資料,該如何處理?

本例要依兩個條件:類別、數量,來篩選資料。以指定數量-99~+99為接近值。

Excel-多條件篩選資料(OFFSET,SMALL,ROW)

 

【公式設計與解析】

選取儲存格A1:E100,按 Ctrl+Shift+F3 鍵,勾選「頂端列」,定義名稱:項目、類別、編號、數量、狀態。

儲存格H2:{=IFERROR(OFFSET($A$1,SMALL(IF((類別=$G$2)*(數量<=$G$4+99)*
(數量>=$G$4-99),ROW(編號),""),ROW(1:1))-1,0),"")}

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

複製儲存格H2,貼至儲存格H2:H15。

(1) IF((類別=$G$2)*(數量<=$G$4+99)*(數量>=$G$4-99),ROW(編號),"")

條件一:(類別=$G$2),

因為指定數量-100~+100為接近值,所以:

條件二:(數量<=$G$4+99)*(數量>=$G$4-99)

其中的「*」運算相當於執行邏輯 AND 運算。

在陣列公式中,當合於二個條件者,會傳回對應的儲存格列號,否則傳回空字串。

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

利用 SMALL 函數根據第(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 函數轉換為空字串。

同理,

儲存格I2:{=IFERROR(OFFSET($C$1,SMALL(IF((類別=$G$2)*(數量<=$G$4+99)*
(數量>=$G$4-99),ROW(編號),""),ROW(1:1))-1,0),"")}

儲存格J2:{=IFERROR(OFFSET($D$1,SMALL(IF((類別=$G$2)*(數量<=$G$4+99)*
(數量>=$G$4-99),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
    文章標籤
    Excel OFFSET SMALL ROW
    全站熱搜

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