Excel INDEX(區域,SMALL(IF(條件,行號數組,4^8),ROW(A1)))區域大小及IF第三參數的選擇問題

關於模式化公式INDEX(區域，SMALL(IF(條件,行號數組，4^8),ROW(A1)))區域大小及IF第三參數的選擇問題如上，這個公式用來取得滿足條件的多個值已成為模式化了，被各位板油廣泛運用，但我在使用中以及看到各位新板友在使用該類型的過程中，發現如下問題，貼出來供大家思考，也請各位老師指導：假設，有公式:

1. =INDEX($A$1:$A$20,SMALL(IF($B$1:$B$20=C$1,ROW($1:$20),4^8),ROW(A1)))&""

複製代碼，各位板友發現什麼問題沒有，就我看來有如下問題：此公式的兩個地方沒有發揮應有的作用：1.IF第三個參數4^82.&""此公式沒有容錯，還要加上IF(ISERROR來取得不顯示錯誤值的效果，不如省略IF第三參數和&""，和上面的效果一樣的話，不如寫成下式更簡潔：

1. =INDEX($A$1:$A$20,SMALL(IF($B$1:$B$20=C$1,ROW($1:$20)),ROW(A1)))

複製代碼但以上寫法均難取得滿意的效果，正確的寫法是：假設源數據放在$A$1:$A$20，那寫成：

1. =INDEX($A$1:$A$21,SMALL(IF($B$1:$B$20=C$1,ROW($1:$20),21),ROW(A1)))&""

複製代碼減少嵌套層數不用IF的寫法是：

1. =INDEX($A$1:$A$21,SMALL(($B$1:$B$20<>C$1)*(21-ROW($1:$20))+ROW($1:$20),ROW(A1)))&""

複製代碼

1. =INDEX($A$1:$A$21,SMALL(($B$1:$B$20=C$1)*(ROW($1:$20)-21)+21,ROW(A1)))&""

複製代碼用TEXT來代替IF的寫法是：

1. =INDEX($A$1:$A$21,SMALL(--TEXT(($B$1:$B$20=C$1)*ROW($1:$20),"[=]21"),ROW(A1)))&""

複製代碼套用4^8的寫法那就是：

1. =INDEX($A:$A,SMALL(IF($B$1:$B$20=C$1,ROW($1:$20),4^8),ROW(A1)))&""

複製代碼或者：

1. =INDEX($A:$A,SMALL(($B$1:$B$20<>C$1)/1%+ROW($1:$20),ROW(A1)))&""

複製代碼4^8的用法在2003里也就整列引用時才能使用，否則就是多此一舉了。上面的公式的ROW(A1)常用ROW(1:1)代替，前者得到常數1，後者得到常量數組{1}，下拉都能取得相同的效果。前者少1字元，後者多一字元。但後者安全性更高一點，前者在刪除A1單元格或包含A1的單元格區域時都會發生錯誤，後者在刪除第一行整行時發生錯誤。總結上面的寫法，也就是查找區域比源數據區域大，且IF第三參數的選擇比數據區域所在的最大行號要大並且小於等於查找區域的行數時，加上&""，才能取得下拉N行後，當單元格數量超過實際查詢得到的結果數量時，公式顯示空文本而不是0零值的效果，且不會出現錯誤值。關於&"",若INDEX需要返回的結果是文本，才適合加上&"".若返回的結果是數值類型，加上&""，將會導致你後面的運算要加上類似於減負的運算符號才能正常計算，因此在不加&""時在工具選項里取消勾選零值顯示也不失為最好的方案。舉的簡單例子在10樓。
推薦閱讀：