856多條件篩選資料-使用IFERROR、OFFSET、SMALL、ROW函數 @ 錦子老師

856	多條件篩選資料-使用IFERROR、OFFSET、SMALL、ROW函數
示範檔	無	範例檔	856.XLSX	結果檔	856F.XLSX

如果我們要在庫存明細表(如下圖所示)中找尋某一類別(C)，庫存數量為550差距50間的所有產品。

在前一單位錦子老師介紹過用進階篩選來完成，現在則是介紹使用多個函數一同作業來完成。

步驟1：在工作表中輸入如下圖準則資料。

步驟2：框選A欄到D欄，點取「公式 > 己定義之名稱 > 從選取範圍建立」圖示，如下圖所示。

步驟3：在【以選取範圍建立名稱】對話方塊，點取「頂端列」核取方塊，使其打勾，其他核取方塊為空白，如下圖所示。

步驟4：點取「確定」鈕，則建立好四個範圍名稱，如下圖所示。

步驟5：點取I2儲存格，輸入公式「=IFERROR(OFFSET($A$1,SMALL(IF((類別=$F$2)*(數量<=$G$2+50)*(數量>=G2-50),ROW(編號),""),ROW(1:1))-1,0),"")」後，按Ctrl+Shift+Enter鍵完成輸入。

【公式解析】

IF((類別=$F$2)*(數量<=$G$2+50)*(數量>=G2-50),ROW(編號),"") 是在判斷類別欄(B)是否有符合F2儲存格內容且數量欄(D)大於等於G2儲存格值加50及減50的資料，若有傳回其所在列的列號，否則傳回空白。

SMALL(IF((類別=$F$2)*(數量<=$G$2+50)*(數量>=G2-50),ROW(編號),""),ROW(1:1)) 抓取陣列傳回來最小的列號值(ROW(1:1))，向下複製依序為第2小的值、第3小的值依此類推。

OFFSET($A$1,SMALL(IF((類別=$F$2)*(數量<=$G$2+50)*(數量>=G2-50),ROW(編號),""),ROW(1:1))-1,0) 傳回從A1儲存格向下移動多少列(SMALL函數算出的列值再減1)，欄位不動為0。

=IFERROR(OFFSET($A$1,SMALL(IF((類別=$F$2)*(數量<=$G$2+50)*(數量>=G2-50),ROW(編號),""),ROW(1:1))-1,0),"") 如果傳回來的值是錯誤訊息，則將其變成空白。

步驟6：點取J2儲存格，輸入公式「=IFERROR(OFFSET($A$1,SMALL(IF((類別=$F$2)*(數量<=$G$2+50)*(數量>=G2-50),ROW(編號),""),ROW(1:1))-1,1),"")」後，按Ctrl+Shift+Enter鍵完成輸入。

步驟7：點取K2儲存格，輸入公式「=IFERROR(OFFSET($A$1,SMALL(IF((類別=$F$2)*(數量<=$G$2+50)*(數量>=G2-50),ROW(編號),""),ROW(1:1))-1,2),"")」後，按Ctrl+Shift+Enter鍵完成輸入。

步驟8：點取L2儲存格，輸入公式「=IFERROR(OFFSET($A$1,SMALL(IF((類別=$F$2)*(數量<=$G$2+50)*(數量>=G2-50),ROW(編號),""),ROW(1:1))-1,3),"")」後，按Ctrl+Shift+Enter鍵完成輸入。