Excel-用公式篩選資料(陣列公式) - 學不完.教不停.用不盡
文章推薦指數: 80 %
有網友問到,在Excel 中取得一個資料表(如下圖左),如果想要在一個儲存格中選取某一個類別,即能篩選出所有該類別的資料(如下圖右)。
學不完.教不停.用不盡
跳到主文
分享個人電腦教學和回答網友提問解決資料處理與設計問題。
部落格全站分類:數位生活
相簿
部落格
留言
名片
贊助廠商
Oct29Sat201110:25
Excel-用公式篩選資料(陣列公式)
有網友問到,在Excel中取得一個資料表(如下圖左),如果想要在一個儲存格中選取某一個類別,即能篩選出所有該類別的資料(如下圖右)。
你可以試著使用「自動篩選」即可方便篩選出想要類別的資料,但是如果想要以公式形式呈現,方便改變類別後,即能馬上得到結果,該如何處理?
【準備工作】
本例需要使用陣列公式,為了方便說明,先定義各個資料名稱。
1.選取儲存格A1:D21,按一下Ctrl+Shift+F3鍵,勾選「頂端列」選項。
定義名稱:「項目、類別、編號、數量」
2.選取儲存格A1:D21,由[公式/已定義名稱/名稱管理員]中定義名稱為:「資料」。
3.選取儲存格F2,在[資料/資料工具/資料驗證]中設定儲存格內允許:清單,來源:「A,B,C,D,E」。
【輸入公式】
儲存格G2:{=IFERROR(INDEX(資料,SMALL(IF(類別=$F$2,項目,FALSE),ROW(1:1))+1,3),"")}
這是陣列公式,輸入完成請按Ctrl+Shift+Enter鍵。
儲存格H2:{=IFERROR(INDEX(資料,SMALL(IF(類別=$F$2,項目,FALSE),ROW(1:1))+1,4),"")}
這是陣列公式,輸入完成請按Ctrl+Shift+Enter鍵。
複儲存格G2:H2,往下各列貼上即可。
【公式說明】
以儲存格G2為例。
儲存格G2:{=IFERROR(INDEX(資料,SMALL(IF(類別=$F$2,項目,FALSE),ROW(1:1))+1,3),"")}
IF(類別=$F$2,項目,FALSE)
將「類別」中符合儲存格F2內容者對應「項目」,否則給予Fasle。
以本例可得{Fasle,Fasle,2,3,Fasle,Fasle,7,…}陣列。
SMALL(IF(類別=$F$2,項目,FALSE),ROW(1:1))
取得前述陣列中的第1個、第2個、…最小值,本例為2,3,7,…。
INDEX(資料,SMALL(IF(類別=$F$2,項目,FALSE),ROW(1:1))+1,3)
前一項所得的各個最小值,以INDEX函數在「資料」陣列中求得第3欄的資料。
公式中的「+1」乃因為「資料」中的欄名也占掉一列。
IFERROR(INDEX(資料,SMALL(IF(類別=$F$2,項目,FALSE),ROW(1:1))+1,3),"")
利用IFERROR函數將INDEX查表後所產生的錯誤值以空白顯示。
儲存格H2的公式原理相同,只要將查表INDEX函式中設定為查詢第4欄即可。
全站熱搜
創作者介紹
vincent
學不完.教不停.用不盡
vincent發表在痞客邦留言(11)人氣()
全站分類:數位生活個人分類:講義資料此分類上一篇:Excel-設計二層的下拉式選單
此分類下一篇:Excel-計算日期數列中各星期幾的數量(Weekday+陣列)
上一篇:Excel-設計二層的下拉式選單
下一篇:Excel-計算日期數列中各星期幾的數量(Weekday+陣列)
歷史上的今天
2018:將Google表單產生的統計圖表放到Word文件中使用
2017:Excel-輸入時間不輸入「:」(MID,TIME)
2017:PreziNEXT快速鍵整理
2016:Excel-用公式篩選符合條件者(OFFSET,ROW,陣列公式)
2015:Excel-依日期判斷給予警示色彩(設定格式化的條件)
2012:Windows8-使用子母畫面
2011:Excel-計算日期數列中各星期幾的數量(Weekday+陣列)
2010:PDF轉電子書-FlippingBookPDFPublisher
▲top
留言列表
發表留言
到站人數
本日人氣:
累積人氣:
文章關鍵字搜尋
回到頁首
回到主文
免費註冊
客服中心
痞客邦首頁
©2003-2022PIXNET
關閉視窗
PIXNET
Facebook
Yahoo!
Google
MSN
{{guestName}}
(登出)
您尚未登入,將以訪客身份留言。
亦可以上方服務帳號登入留言
請輸入暱稱(最多顯示6個中文字元)
請輸入標題(最多顯示9個中文字元)
請輸入內容(最多140個中文字元)
請輸入左方認證碼:
看不懂,換張圖
請輸入驗證碼
送出留言
延伸文章資訊
- 1小技巧】使用Excel篩選出符合雙條件的資料範圍-進階篩選
最近剛好有同事在詢問如何從Excel資料表中篩選出符合雙條件(且、或)的資料範圍,這邊談到的篩選有兩種情況,第一種情況「且」,也就是篩選出兩個條件 ...
- 2Excel教學技巧/EXCEL進階篩選:自訂多個條件 - T客邦
Excel教學技巧/EXCEL進階篩選:自訂多個條件,找到符合條件的資料 · 步驟1.開啟要進行進階篩選的資料表。 · 步驟2.在篩選的欄目下方,建立條件,此例為「 ...
- 3Excel成本計算:篩選平均值及Subtotal條件計算平均數 - 贊贊小屋
Excel進階函數陣列公式
- 4Excel 小教室– 篩選你會用,但「進階」篩選你用過了嗎?
excel格式一般輸入數字,下公式可令其自動加總,但要如何不予計數,而是計次。例如1格輸入數字1,另一格輸入數字3,輸入公式加總會=4,但我希望是變成2( ...
- 52587Excel用函數篩選資料| 錦子老師 - - 點部落
Excel用函數篩選資料. ... 劉飄飄:「錦子老師您好,請教如果我要將公司所有年資1年的資料篩選出來,如下圖所示: ... 錦子老師:「首先在K5輸入公式:.