贊助廠商

///本部落格所有文章列表///

搜尋本部落格文章資料

廣告贊助

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

文章標籤
創作者介紹

學不完.教不停.用不盡

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


留言列表 (1)

發表留言
  • 阿雅
  • 我要比較兩個數值的關係,可是同樣的數值,並設定IF(C2=D2,:"相等","不相等")而那兩筆資料是使用VLOOKUP彙過來的,但是好像其中一筆有問題,兩個相比跑出的E2會出現#VALUE! 用D2-C2會變成也會產生問題,請問是哪邊格式有錯??
找更多相關文章與討論

您尚未登入,將以訪客身份留言。亦可以上方服務帳號登入留言

請輸入暱稱 ( 最多顯示 6 個中文字元 )

請輸入標題 ( 最多顯示 9 個中文字元 )

請輸入內容 ( 最多 140 個中文字元 )

請輸入左方認證碼:

看不懂,換張圖

請輸入驗證碼