Excel公式技巧31： 使用SUMPRODUCT函數巧妙處理多條件判斷

下面是要使用公式解決的問題：

如果值處于0%-25%，則返回0；處于16%-50%，則返回0.1；處于51%-75%，則返回0.2；處于76%-100%，則返回0.3；大于100%則返回0.4。

如下圖1所示，值為80%，返回0.3。

圖1

通常，我們會考慮使用IF函數的公式：

=IF(AND(B3>=0,B3<=0.25),0,IF(AND(B3>=0.26,B3<=0.5),0.1,IF(AND(B3>=0.51,B3<=0.75),0.2,IF(AND(B3>=0.76,B3<=1),0.3,0.4))))

太冗長了！如果條件更多，則需要增加更多的IF語句。

這里使用SUMPRODUCT函數編寫了一個簡潔的公式：

=SUMPRODUCT((B3>{0.25,0.5,0.75,1})*0.1)

我們來看看公式中的：

B3>{0.25,0.5,0.75,1}

用來將B3中的值進行分類，本例中的結果為：

{TRUE,TRUE,TRUE,FALSE}

將其與0.1相乘，得到：

{0.1,0.1,0.1,0}

將其傳遞給SUMPRODUCT函數：

=SUMPRODUCT((B3>{0.25,0.5,0.75,1})*0.1)

即：

=SUMPRODUCT({0.1,0.1,0.1,0})

由于SUMPRODUCT函數內只有一個數組，因此簡單地將該數組元素值相加，得到結果：

0.3

注意，SUMPRODUCT函數不會直接處理TRUE/FALSE值的數組，因此，如果使用公式：

=SUMPRODUCT((B9>{0.25,0.5,0.75,1}))*0.1

結果將是0。

要得到正確的結果，需要將上面的公式修改為：

=SUMPRODUCT(1*(B10>{0.25,0.5,0.75,1}))*0.1

或者：

=SUMPRODUCT(--(B10>{0.25,0.5,0.75,1}))*0.1