Question to say "I can!"

SUMPRODUCT函數詳解

2014-10-22

前面,我們學習和運用了許多關于SUMPRODUCT函數的例子,但是,對SUMPRODUCT函數的了解可能還有些陌生,那么,今天我們來對SUMPRODUCT函數進行詳細的解釋。

本文資料來源于xlDynamic.com,供參考。

SUMPRODUCT函數的運作原理

理解SUMPRODUCT函數如何工作能夠幫助我們決定在什么情況下使用它,以及如何構建公式并擴展其用法。

下面圖片中是我們將要使用的示例數據。

圖中,A1:A12是商品,B1:B12是分類,C1:C12是銷售的數量。本例中,求賣了多少屬于類別A的商品Ford。

使用下面的公式可以得到結果:

=SUMPRODUCT((A1:A12="Ford")*(B1:B12="A")*(C1:C12))

該公式的第一部分(A1:A12="Ford")檢查值為Ford的商品,返回值為TRUE/FALSE的數組,本例中是:

{TRUE,FALSE,TRUE,TRUE, TRUE,TRUE, TRUE,FALSE,TRUE, TRUE, TRUE, TRUE}

同樣,使用(B1:B12="A")檢查值為A的分類,返回值為TRUE/FALSE的數組,本例中是:

{FALSE,FALSE,TRUE,TRUE,FALSE, TRUE, TRUE, TRUE,TRUE, TRUE, TRUE, TRUE}

最后,(C1:C12)返回含有一系列數值的數組:

{3,4,2,1,4,3,2,8,6,8,7,6}

現在我們獲得了3個數組,兩個含有TRUE/FALSE值,一個含有數值,如下圖所示:

SUMPRODUCT通常處理數值數組,然而我們的公式中既有數值數組,也有含有TRUE/FALSE值的數組。通過使用“*”(乘)運算符,能夠獲得可以求和的數字值。“*”運算符強制使這兩個含有TRUE/FALSE值的數組變為含有1/0值的單個數組。TRUE乘以TRUE將得到1(在工作表中試試,在單元格中輸入=TRUE*TRUE,查看結果),任何其它的組合的結果為0。因此,當兩個條件都滿足時,結果為1,當任一或者兩個條件都不滿足時,結果為0。第一個數組中的TRUE/FALSE值與第二個數組中的TRUE/FALSE值相乘,結果是一個含有1/0值的數組,即{0,0,1,1,0,1,1,0,1,1,1,1}。這個數組的值接著和銷售數量數組的值相乘,結果為滿足兩個測試條件的銷售數量的數組。接下來,SUMPRODUCT函數匯總該數組中的值,得到最終的結果。

上圖展示了“*”運算符處理前條件測試所分解的值。

下圖展示了TRUE/FALSE值等價于數字值1/0,并分別相乘后的結果。在這里,我們應該能夠看到SUMPRODUCT函數是如何運算直至獲得最終結果,即35。

下圖展示了沒有銷售數量列時等價的1/0值,即使用SUMPRODUCT函數統計滿足兩個條件的行數:

按照我們上面的解釋,在使用SUMPRODUCT函數時,“*”運算符解決了將多個數組轉換成單個的合成數組,剩下由SUMPRODUCT函數來簡單地對這個合成數組的成員求和。SUMPRODUCT函數能夠處理單個數組(例如,在單元格A1、A2、A3中分別輸入數字1、2、3,然后在一個單元格中插入公式=SUMPRODUCT(A1:A3),將返回結果6)。事實上,我們僅需要“*”運算符來強制對特定條件進行測試的數組進行轉換,而不需要它來對不是條件測試的數組進行操作。因此,對于上例,我們也可以使用下面的公式:

=SUMPRODUCT((A1:A12="Ford")*(B1:B12="A"),(C1:C12))

在使用SUMPRODUCT函數時,所有的數組都必須有相同的大小,這樣每個數組相對應的成員能夠彼此相乘。

在使用SUMPRODUCT函數時,數組不能為整列(例如A:A),數組必須是一列里的某個單元格區域。但是,可以使用整行(例如1:1)。

在SUMPRODUCT函數中,數組不能夠同時包含列和行區域,它們必須全是列,或者全是行。然而,行數據能夠使用函數轉置成列,參見后面的示例。

作者:admin | Categories:辦公能手 | Tags:

發表評論

電子郵件地址不會被公開。 必填項已用*標注

*

澳洲f1赛车b盘开奖套路 重庆百变王牌开奖直播现场 体彩七位数近30期走势图 郑州站街女多少钱 湖北十一选五规则 31选7走势图开奖2元网 极速赛车3分钟是骗局吗 小米股票代码 台湾麻将16张小游戏 湖北‖选五开奖结果 京香じゅりあ番号 福利彩票18选7 上海天天彩 全民麻将赢话费 极速赛车手电影 31选择7走势图 仁科百华番号 种子