網(wǎng)友解答: 大家好,我是@EXCEL實(shí)例視頻網(wǎng)站長(zhǎng)@歡迎關(guān)注我的酷米,每天都會(huì)分享Excel實(shí)戰(zhàn)技巧!這個(gè)是典型的多條件取數(shù),階梯條件,你首先要重新整理?xiàng)l件為標(biāo)準(zhǔn)格式,按我下面方法操作即

大家好,我是@EXCEL實(shí)例視頻網(wǎng)站長(zhǎng)@歡迎關(guān)注我的酷米,每天都會(huì)分享Excel實(shí)戰(zhàn)技巧!

這個(gè)是典型的多條件取數(shù),階梯條件,你首先要重新整理?xiàng)l件為標(biāo)準(zhǔn)格式,按我下面方法操作即可

上次介紹了一個(gè)lookup進(jìn)行多條件判斷取值的解法，不過(guò)今天突然發(fā)現(xiàn)那個(gè)解法有個(gè)嚴(yán)重的缺陷，就是只能解a<=A<b,就是當(dāng)條件都是大于等于較小值且小于較大值的情況，今天又有人問(wèn)了一個(gè)a<A<=b 及大于較小值而小于等于較大值的多條件 那個(gè)公式在節(jié)點(diǎn)上就判斷不準(zhǔn)了，我又實(shí)在不想用if函數(shù)去做，嵌套一大堆得if 看著都累，先后試驗(yàn)了vlookup，Hlookup，match，發(fā)現(xiàn)它們的內(nèi)在原理都是一樣的 沒(méi)辦法只好請(qǐng)出SUMPRODUCT大神了

實(shí)例如下

輸入500=a10 的數(shù)的時(shí)候，B1顯示為A1*0.1

輸入2000=A1500 B1顯示為A1*0.2

輸入5000=A12000 B1顯示為A1*0.3

輸入20000=A15000 B1顯示為A1*0.4

有人會(huì)說(shuō) 這還不簡(jiǎn)單那 幾個(gè)if就可以了 恭喜你 答對(duì)了！ 但是我現(xiàn)在要做的是 如果有N個(gè)以上這樣的條件 你還能if么，那公式寫出來(lái)只怕累死你 而且if只能嵌套7層的，其實(shí)我最想做的是將公式簡(jiǎn)化 簡(jiǎn)化成容易理解容易使用的形式 廢話少說(shuō) 開(kāi)始分析

1、本問(wèn)題核心是A1*X，X的取值根據(jù)A1的范圍變化，好了 核心就是求X

2、抽出A1條件和X對(duì)應(yīng)結(jié)果 簡(jiǎn)單得出以下數(shù)列，

1230<=5000.1500<=20000.22000<=50000.35000<=200000.4

到現(xiàn)在 事情就變得比較簡(jiǎn)單的 這個(gè)問(wèn)題抽象成數(shù)學(xué)邏輯就變成了

對(duì)1，2列數(shù)據(jù)進(jìn)行邏輯與運(yùn)算 其結(jié)果再乘以3列數(shù)據(jù) 由于只有一行的數(shù)據(jù)會(huì)符合要求 而其他行注定會(huì)等于0 所以對(duì)每行的運(yùn)算結(jié)果求和 就是最終的X取值了

說(shuō)了一大堆理論 估計(jì)很多人看不懂 我表達(dá)能力實(shí)在有限啊 看不明白的就略過(guò)吧

上面的結(jié)論 最適合的公式就是SUMPRODUCT了 這個(gè)函數(shù)其本質(zhì)就是數(shù)組求和 實(shí)際上可以分解成sum函數(shù)的數(shù)組公式 但是它的優(yōu)點(diǎn)就是用普通公式完成了數(shù)組公式的效果 在這里它最大的作用就是進(jìn)行邏輯與運(yùn)算和數(shù)組求和 公式其實(shí)挺簡(jiǎn)單

純數(shù)字版的：

=SUMPRODUCT((A1{0,500,2000,5000})*(A1<={500,2000,5000,20000})*{0.1,0.2,0.3,0.4})

別看這覺(jué)得嚇人 其實(shí)你幾個(gè)括號(hào)的值和上面的數(shù)列一對(duì)比 就很簡(jiǎn)單了

實(shí)際使用的時(shí)候 可以用單元格區(qū)域代替{}例如

00.15000.220000.350000.420000

這是F9：G13的單元格區(qū)域 公式就相應(yīng)改為

=SUMPRODUCT((A1$F$9:$F$12)*(A1<=$F$10:$F$13)*($G$9:$G$12))

這里 當(dāng)你的條件更多時(shí) 一樣可以套用這個(gè)公式 只要把條件區(qū)域做相應(yīng)改變即可

到此為止 這就算出了對(duì)應(yīng)X的值 剩下的就是在公式前面加上A1*即可了

實(shí)例截圖如下

SUMPRODUCT實(shí)乃excel函數(shù)中大神 經(jīng)?？梢杂盟鼇?lái)完成一些不可思議的任務(wù)

不知道怎么圖被吃了一張…