1. 首頁 > 生活百科 > >

條件求和:sumif函數的數組用法

生活百科小知識

之前分享了sumif函數的基本用法，今天來分享下它的數組用法 。
-01-具體應用
1.求竹葉粽和豆沙粽的總數量 ?，F在是求2種粽子的總數量，你可能會想到先把”竹葉粽”的總數量求出來，然后再把”豆沙粽”的總數量求出來，最后把它們相加 。這樣是可以的，公式為=SUMIF(A2:A9,”竹葉粽”,B2:B9)+SUMIF(A2:A9,”豆沙粽”,B2:B9)，要用2次sumif，公式有點長 。
【條件求和:sumif函數的數組用法】

條件求和:sumif函數的數組用法


 
其實這里有2個條件，一個是”竹葉粽”，一個是”豆沙粽” ?？梢杂脭到M的方式來完成，公式為=SUM(SUMIF(A2:A9,{“竹葉粽”,”豆沙粽”},B2:B9)) 。
條件求和:sumif函數的數組用法


 
第2參數是一個數組，sumif也返回一個數組 ?？梢栽诠骄庉嫏谥羞x中sumif函數按F9查看，結果為{42,42} 。這里2個值剛好一樣，其中第1個42就是”竹葉粽”的總數量，第2個42是”豆沙粽”的總數量，可以和上圖的左表對比 。最后用sum函數對這2個值求和，這就是數組的用法 。
條件求和:sumif函數的數組用法


 
也可以用前面學的+來完成，盡管是求”竹葉粽”和”豆沙粽”的總數量，實際是多條件或的關系 。公式為=SUM(((A2:A9=”竹葉粽”)+(A2:A9=”豆沙粽”))*B2:B9)，按ctrl+shift+enter 。
條件求和:sumif函數的數組用法


 
2.求小于20和大于40的數量的總和 。這里也有2個條件，一個是小于20，一個是大于40，也是或的關系 ?？梢韵袂懊嬉粯佑?個sumif相加 ?，F在直接用數組公式=SUM(SUMIF(B12:B19,{“<20″,”>40″})) 。同樣地sumif返回一個數組{17,98}，17是小于20的總數量，98是大于40的總數量 。
條件求和:sumif函數的數組用法


 
用+來完成，公式為=SUM(((B12:B19<20)+(B12:B19>40))*B12:B19)，按ctrl+shift+enter 。
3.求20-40之間的數量的總和 。也就是求大于等于20且小于等于40的數量的總和 。也有2個條件，一個是大于等于20，一個是小于等于40，是與的關系 。現在該怎么求呢?如果直接用公式=SUM(SUMIF(B22:B29,{“>=20″,”<=40”}))，結果肯定不對 。在公式中查看sumif返回的結果為{270,104}，270是大于等于20的總數量，它把大于40的也包括進去了 。
條件求和:sumif函數的數組用法


 
正確的公式為=SUMIF(B22:B29,”>=20″)-SUMIF(B22:B29,”>40″)，用大于等于20的總數量減去大于40的總數量就是大于等于20且小于等于40的總數量 。
數組的公式為=SUM(SUMIF(B22:B29,{“>=20″,”>40″})*{1,-1})，sumif返回1個數組{270,185}，270是大于等于20的總數量，185是大于40的總數量，要用270減去185該怎么減呢?所以要乘以{1,-1}，這樣結果就為{270,-185}，用sum求和實際就是270-185 。
由于是多條件與的關系，還可以用*來完成，公式為=SUM((B22:B29>=20)*(B22:B29<=40)*B22:B29)，按ctrl+shift+enter 。

    猜你喜歡