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

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

這個是典型的多條件取數(shù),階梯條件,你首先要重新整理條件為標準格式,按我下面方法操作即可

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

實例如下

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

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

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

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

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

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

2、抽出A1條件和X對應結(jié)果 簡單得出以下數(shù)列，

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

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

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

說了一大堆理論 估計很多人看不懂 我表達能力實在有限啊 看不明白的就略過吧

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

純數(shù)字版的：

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

別看這覺得嚇人 其實你幾個括號的值和上面的數(shù)列一對比 就很簡單了

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

00.15000.220000.350000.420000

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

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

這里 當你的條件更多時 一樣可以套用這個公式 只要把條件區(qū)域做相應改變即可

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

實例截圖如下

SUMPRODUCT實乃excel函數(shù)中大神 經(jīng)常可以用它來完成一些不可思議的任務

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