Excel 數列對照的問題, 要去掉0與空白

Question

如附件:
http://www.funp.net/128449

有ABC三欄，分別為名稱,數值,計算結果，
我想在另一處寫函數，將這三欄的數列直接對照，
但要將計算結果為0或空白者均踢除，
得到如EFG三欄的結果

這個結果使用VBA的方式我是會做，
但我這個表格不想再使用VBA，
所以要直接做函數的對照

請各位高手幫幫忙！

piny · Accepted Answer

I2公式為
=OFFSET(A$1,SMALL(IF(ISERROR(1/($B$2:$B$21*$C$2:$C$21)),"",ROW($B$2:$B$21)-1),ROW(1:1)),)
此公式為陣列
偵錯還在想 ^^

2008-06-07 07:53:25 補充：
呼！想出來了

I2公式改為
=IF(SUM(--NOT(ISERROR(1/($B$2:$B$21*$C$2:$C$21))))

2008-06-07 07:54:40 補充：
如附件(SeriesRef)
http://www.funp.net/948150

2008-06-07 07:55:23 補充：
公式被截掉了

I2
=IF(SUM(--NOT(ISERROR(1/($B$2:$B$21*$C$2:$C$21))))＜ROW(1:1),"",OFFSET(A$1,SMALL(IF(ISERROR(1/($B$2:$B$21*$C$2:$C$21)),"",ROW($B$2:$B$21)-1),ROW(1:1)),))

2008-06-07 11:51:26 補充：
若只判斷C欄

I2
=IF(SUM(--NOT(ISERROR(1/($C$2:$C$21))))＜ROW(1:1),"",OFFSET(A$1,SMALL(IF(ISERROR(1/($C$2:$C$21)),"",ROW($C$2:$C$21)-1),ROW(1:1)),))

2008-06-07 11:52:17 補充：
重新上傳附件
http://www.funp.net/498230

2008-06-07 17:32:02 補充：
排序值相同時，列號大的先排序
M2
=IF(SUM(--NOT(ISERROR(1/($C$2:$C$21))))＜ROW(1:1),"",OFFSET(A$1,IF(ISERROR(1/($C$2:$C$21)),"",MATCH(LARGE($C$2:$C$21＋ROW($C$2:$C$21)/10000,ROW(1:1)),$C$2:$C$21＋ROW($C$2:$C$21)/10000,)),))

2008-06-07 17:32:21 補充：
列號小的先排序
Q2
=IF(SUM(--NOT(ISERROR(1/($C$2:$C$21))))＜ROW(1:1),"",OFFSET(A$1,IF(ISERROR(1/($C$2:$C$21)),"",MATCH(LARGE($C$2:$C$21－ROW($C$2:$C$21)/10000,ROW(1:1)),$C$2:$C$21－ROW($C$2:$C$21)/10000,)),))

2008-06-07 17:33:12 補充：
重新上傳附件
http://www.funp.net/985748

2008-06-08 01:05:09 補充：
您好，
先回答簡單的 ^^

應該有把「小於」符號及「加號」及「減號」改為半形吧

而ROW($C$2:$C$21)/10000的用法主要是考量同結果時之排序問題，如您例中之23，則僅用large排序原函數，會有部分rank值佚失而影響match函數判斷

故將每個值都加上列號，則縱使同值，也因列號不同而可分別，再加上不想影響原始排序，故將列號除以10000(若不夠，則除以100000000000)

2008-06-08 01:19:34 補充：
小問題

請問G18到G65有Rand函數嗎

2008-06-08 07:20:04 補充：
改善起始值為0或空白

M2
=IF(SUM(--NOT(ISERROR(1/($C$2:$C$21))))＜ROW(1:1),"",OFFSET(A$1,SMALL(IF(ISERROR(1/($C$2:$C$21)),"",MATCH(LARGE($C$2:$C$21＋ROW($C$2:$C$21)/10000,ROW(1:1)),$C$2:$C$21＋ROW($C$2:$C$21)/10000,)),ROW(1:1)),))

重新上傳附件
http://www.funp.net/923736

2008-06-08 07:21:25 補充：
經測試，僅有Sum函數應該可以跑出來

G18到G65的公式應該都類似吧，可以將G18的公式寫出來嗎？

好讓小弟方便判斷　^^

2008-06-08 23:48:11 補充：
不好意思，您兩個補充的問題，經小弟一整個下午測試也想不出所以然

初步看來，小弟的公式僅適用於DEF無公式，且G欄無負值情況，

沒能幫上忙！

看有無其他大師樂意相助 ^^

2008-06-12 23:20:59 補充：
嗯 funp網站似乎當機中

這是用另一網站上傳的檔案(SeriesRef4-1)
http://web2.go2upload.com/file/22916

主要問題是
1.sheet2之D到F欄若無公式，則K欄可顯示結果
2.G欄加總值若為負值，會影響正確結果

就是這兩點讓小弟束手無策 懇請大大撥冗賜教囉 ^^

2008-06-13 00:20:05 補充：
哇 還在嘗試瞭解applerot大大提供的解法

如果僅零值不出現，綜合小弟公式前段的判讀公式即可

=IF(SUM(--NOT(ISERROR(1/$G$18:$G$65)))

2008-06-13 00:20:29 補充：
=IF(SUM(--NOT(ISERROR(1/$G$18:$G$65)))＜ROW(1:1),"",INDEX(C:C,SMALL(IF(LARGE($G$18:$G$65,ROW(A1))=$G$18:$G$65,ROW($G$18:$G$65),""),MOD(ROW(),COUNTIF($G$18:$G$65,LARGE($G$18:$G$65,ROW(A1))))+1)))

2008-06-13 00:24:11 補充：
applerot大大的解法比較強　佩服

顯栓 · Answer

依經驗所得 INDIRECT 和 OFFSET 處理不好會相沖不知是不是我的電腦有問題以上的 http://www.funp.net/XXXX 都沒法下載有空也幫不上忙! 2008-06-13 00:05:47 補充：公式貼不完整檔案: http://tco.cseintltd.com.tw/applerot/pic/SeriesRef4-1.xls 自己研究 piny 可算大師級應該不難重點我猜對了 INDIRECT 和 OFFSET 不相容 2008-06-13 00:10:24 補充：試貼 K18=IF(SUM(--($G$18:$G$65<>""))＜ROW(1:1),"",INDEX(C:C,SMALL(IF(LARGE($G$18:$G$65,ROW(A1))=$G$18:$G$65,ROW($G$18:$G$65),""),MOD(ROW(),COUNTIF($G$18:$G$65,LARGE($G$18:$G$65,ROW(A1))))+1))) 2008-06-13 00:20:27 補充：最後修訂版,檔案也修改過 K18=IF(SUM(--($G$18:$G$65<>0))＜ROW(1:1),"",INDEX(C:C,SMALL(IF(LARGE(IF($G$18:$G$65,$G$18:$G$65,""),ROW(A1))=$G$18:$G$65,ROW($G$18:$G$65),""),MOD(ROW(),COUNTIF($G$18:$G$65,LARGE($G$18:$G$65,ROW(A1))))+1))) 有問題明天再研究! 晚安! 2008-06-13 10:57:15 補充：修訂完成，列數小的在上面、0 、負應該都解決了 http://tco.cseintltd.com.tw/applerot/pic/061301.xls 2008-06-13 12:07:26 補充： SUM(--NOT(ISERROR(1/$G$18:$G$65)))=SUM(--($G$18:$G$65<>0)) 兩個結果一樣! IF($G$18:$G$65,$G$18:$G$65,"") 將陣列中的0消除

Excel 數列對照的問題, 要去掉0與空白

2 個解答