2017年4月13日 星期四

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

網友問到:在 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),"")}

沒有留言:

張貼留言

檢視其他文章

好康東東