相關函數：INDEX() / SMALL() / ROW()
要從一大堆資料當中篩選出符合條件的資料，最簡單的方法就是〔資料〕〔篩選〕〔自動篩選〕，然後選擇你要的條件或公式。但是，這樣會有一些不方便的地方。
- 第一，進入篩選模式時，不方便輸入新的資料。
- 第二，即使使用的公式都一樣，進入篩選模式時，每次都要重覆輸入篩選公式。
- 第三，不能只篩選部份欄位，所有欄位都會全部顯示。
##CONTINUE##=INDEX(B:B,SMALL(IF($C$2:$C$10<60,row($c$2:$c$10),""),ROW(C1)))
公式說明：
- IF(...): 設定篩選條件，如果 C 欄分數小於 60，就傳回列數，否則傳回空白，傳回值形成一個陣列。
- SMALL(IF(...),ROW(...)): 依序從陣列中傳回第一小，第二小...的列數。因為不符合條件者傳回空白，在這裡會傳回不合理的列數，導致結果為 0 或 #NUM!。
- INDEX(顯示資料欄位，SMALL(...)): 從 B 欄中依序篩選出列數所對應的值。
=INDEX(C:C,SMALL(IF($C$2:$C$10<60,row($c$2:$c$10),""),ROW(C1)))
再直接往下複製即可。這是一個陣列公式，記得用 CTRL+SHIFT+ENTER 來完成輸入。
這樣，我們可以任意在 A:D 欄輸入資料，F:G 欄會馬上自動顯示出最新的篩選結果，而且可以任意指定要顯示那些欄位。
如果想一次篩選多個條件，例如，想找國文，數學兩科都不及格的人，只要適當修改篩選條件就可以了。如 I2 輸入公式
=INDEX(A:A,SMALL(
IF((($C$2:$C$10<60)+($d$2:$d$10<60))=2,ROW($C$2:$C$10),""),
ROW(C1)))
22 意見:
嗯！這樣做真的很方便。
拍拍手，
放煙火。
呵呵，能得到愛文大大的鼓勵，真是榮幸啊！
請教您, 如果有兩個人分數相同, 要如何顯示出兩人姓名? 謝謝!
您好! 抱歉~ 我上面的問題, 好像不完全適用本文. 但我目前遇到的困擾是: 用 LARGE 可以看出分數最高前三名, 但是該如何對應出這前三名的姓名? 另外, 我試著將您本文提供的資料鍵入, 但是儲存格 F3, G3 跑不出孫三的名字. 不知問題出在哪?
1.文中的公式可以顯示兩人同分的狀況。
2.如果只能跑出第一筆資料，無法跑出第二筆資料，可能是你沒有設定成陣列公式。請記得用 CTRL+SHIFT+ENTER 來完成輸入。
大大~ 謝謝您的指導! 敝人已了解到陣列公式的重要性! 但是我的問題仍未解決 ~_~
我只是單純要判別最高分的排名, 然後帶出對應的姓名, 可是試到現在都是帶出同分的第一位姓名, 同分的第二位該如何帶出來呢?
非常感謝您的幫忙!!
要排序資料，直接用［資料］［排序］是最簡單的方法。如果這樣不適用，一定要用公式，可以參考這一篇新文章：
[EXCEL] 用公式做資料排序
http://whitefox-blog.blogspot.com/2008/04/excel.html
想請教一個問題:
如果有1000多題單選題,前面都有正解,再來試題目,最後是選項(偶4個選1個),該如何用函數或公式只選擇出正確選項,其他非必要之錯誤選項可刪除或消失
範例如下:
52.（2）機關委託廠商辦理專案管理，得視工程性質及實際需要，將下列何者一併委託辦理：（1）工程施工；（2）施工監造；（3）統包；（4）細部設計。
假設正解在 B 欄，如 [2], 四個選項在 D:G 欄，如 [2]xxxx，則 H1 公式，
=INDEX(D1:G1,IF(ISNA(MATCH(B1,D1:G1,1)),0,MATCH(B1,D1:G1,1))+1)
往下複製，可以得到正解。
然後，複製 H 欄，選擇性貼上 "值" 到 I欄，刪除 D:H 欄，留下 I 欄即為答案。
先生：
你好！我有一個銷售資料清單，格式如下：
A欄：公司名稱、B欄：幣種、C欄：銷售金額
欄名放在列1，列2至列100為資料
我想在儲存格：C101(USD)、C102(EUR)、C103(HKD)利用SUMIF 及 SUBTOTAL 這兩個函數去篩選出某公司不同幣種（一併顯示）的銷售金額。請問如何設計？
如果不用SUMIF 及 SUBTOTAL，有其他辦法嗎？
有些問題，好像改用樞紐分析表來做也行吔...
繼續睡覺....
如果是要加總某種幣值，SUMIF() 是最簡單快速的方法。其他拐彎抺角的方法當然也有，但是有必要那樣做嗎？
愛文大大，如果要指定結果的顯示位置 (C101:C103)，用樞紐分析表好像比較麻煩喔？
請問如果回傳值無法顯示0,出現#NUM,如何讓他顯示為空白?謝謝
試試 =IF(ISERROR(原公式),"",原公式)
whitefox大大,你的方法很有效,謝了,感恩
不客氣，很高興有幫上忙。
請問...如果使用SMALL的位置，我希望不是篩選數字小於一個指定數字的條件，而是要篩選出所有符合一字串(EX：CR20100417001)的資料，並列出此字串後的列資料呢?
我使用您的範例改了很久，都還是沒辦法有答案，請幫幫我~
您好，為了更清楚表達我想請教的問題,貼上圖片說明連結：希望對您瞭解問題更有幫助，感謝您～
http://cid-6af76479c784308c.skydrive.live.com/self.aspx/%e7%b6%b2%e8%b7%af%e6%b5%81%e5%82%b3%e7%9a%84%e5%9c%96%e7%89%87/Q.jpg
狐大..請問用公式篩選後的資料..想要再依大小排序..要怎樣在修改公式??謝謝
如果已將資料進行篩選了..
但篩選下拉選項並沒有出現所有欄位的值..
請問是那裡出了問題??
大大~SMALL的部份會一直要我輸入K值，請問K值是什麼意思呢?我一直VALUE錯誤><麻煩幫幫忙，我的有一行資料，依據下拉式選單去參考，DATABASE的資料擷取前三碼若跟下拉式符合的就都傳回，請問該怎麼做呢?
請問如果我有AB欄，但只想抽出A欄中非空白格(有1)所對應B欄的數字，或者A欄非空白格所對應的數值總數，應該怎麼輸入formula？例子如下，，
A ☞ B
1 ☞ 25
空白格 ☞2
空白格 ☞6
1 ☞88
1 ☞2
張貼留言