作者JieJuen (David)
看板Office
標題Re: [問題] EXCEL 搜尋而且比較大小
時間Thu Mar 13 06:33:50 2008
※ 引述《furukawa (furukawa)》之銘言：
: (若是和其他不同軟體互動之問題 請記得一併填寫)
: 您所使用的軟體為:
: EXCEL 2003
: 問題:
: 我有一個類似下面的資料表。
: B欄的編號定期會變動，而且可能出現兩筆末兩碼相同
: 第一碼不同的資料。我希望能做到在B11格輸入末兩碼
下面公式是寫B12 以B12為準
: 號碼，把條件符合的資料按大小分別放到A14和B14格
: 去：
: A | B
: 1 品名 編號
: 2 蘋果 103
: 3 柳丁 509
: 4 橘子 213
: 5 香蕉 009
: 6 芭樂 022
: 7 鳳梨 112
: 8 葡萄 513
: 9
: 10
: 11輸入 13
: 12
: 13大的 小的
: 14葡萄 橘子
: 目前研究結果似乎可以用如下的公式來完成，但函數ROW()
: 並不能比較兩組號碼的大小：
: {=INDEX(A2:A8,LARGE(IF(RIGHT(B2:B8,2)
: =RIGHT(B12,2),ROW(B2:B8)-1,""),1),0)}
: 想請教各位先進能建議我比較好的函數或是公式嗎？
: 感恩不盡！ <(_ _)>
參照應該要固定的 不過就很長 先不固定 以免眼花 均陣列公式
以下假設編號都不重覆
=INDEX(A2:A8,MATCH(LARGE(IF(RIGHT(B2:B8,2)=RIGHT(B12,2),B2:B8),COLUMN()),
B2:B8,0))
以下假設都是正數(都是負數....也可以啦 一樣就好)
=INDEX(A2:A8,MATCH(LARGE(IF(MOD(B2:B8-B12,100),,B2:B8),COLUMN()),B2:B8,0))
換用OFFSET寫
=OFFSET(A1,MATCH(LARGE(IF(MOD(B2:B8-B12,100),,B2:B8),COLUMN()),B2:B8,0),)
用VLOOKUP寫
=VLOOKUP(LARGE(IF(MOD(B2:B8-B12,100),,B2:B8),COLUMN()),IF({1,0},B2:B8,A2:A8)
,2,0)
固定後
=INDEX($A$2:$A$8,MATCH(LARGE(IF(RIGHT($B$2:$B$8,2)=RIGHT($B$12,2),
$B$2:$B$8),COLUMN()),$B$2:$B$8,0))
=INDEX($A$2:$A$8,MATCH(LARGE(IF(MOD($B$2:$B$8-$B$12,100),,$B$2:$B$8),
COLUMN()),$B$2:$B$8,0))
=OFFSET($A$1,MATCH(LARGE(IF(MOD($B$2:$B$8-$B$12,100),,$B$2:$B$8),
COLUMN()),$B$2:$B$8,0),)
=VLOOKUP(LARGE(IF(MOD($B$2:$B$8-$B$12,100),,$B$2:$B$8),COLUMN()),
IF({1,0},$B$2:$B$8,$A$2:$A$8),2,0)
如果資料將編號放在左邊 VLOOKUP可以更方便
=VLOOKUP(LARGE(IF(MOD($A$2:$A$8-$B$12,100),,$A$2:$A$8),COLUMN()),$A$2:$B$8,2,0)
另外 篩選與樞紐分析在大量時效率高
只是配置上有些限制
--
※ 發信站: 批踢踢實業坊(ptt.cc)
◆ From: 218.164.51.188
1F：推 furukawa:謝謝大大詳細的解答！我會研究看看 03/13 09:32
2F：→ furukawa:只是想不到看似簡單的想法，卻要動用到這麼大陣仗...@_@ 03/13 09:33
3F：→ JieJuen:XD 方法很多 越寫越多 03/13 16:02