Question to say "I can!"

用EXCEL的sumproduct函數做條件統計

2012-08-08

sumproduct函數在多條件統計中具有王者之風,是一個Excel高手必備的技能之一。

前面已經寫過一篇,這里再學習一下。

以下參考于:張志強——用EXCEL的sumproduct函數做條件統計

 

基本用法

sumproduct的正常用法是

= sumproduct(array1, array2, array3, ...)

它可以支持最高30個參數。但必須要注意,這里每個參數都必須為數值型,連邏輯型都不行。所以任何以下式子得到的結果都是0:

= sumproduct(A1:A100, B1:B100<>"a")        ' 錯誤用法,結果總是返回0!

解決方法之一是通過顯性或隱性的轉換將邏輯值轉為數值型:

= sumproduct(A1:A100, N(B1:B100<>"a"))      ' 顯性轉換?
= sumproduct(A1:A100, --(B1:B100<>"a"))     ' 隱性轉換,速度更快,推薦

另一個方法是使用連乘:

= sumproduct((A1:A100)*(B1:B100<>"a"))

使用連乘的技術原理可參考Excel區域計算的原理。它比上面的多參數形式運算速度要稍微慢一些,但也有以下兩個好處:

  • 連乘的表達式不需要轉換邏輯表達式,寫法更為簡單。
  • 連乘可突破最多30個參數式子的限制。

和sumifs的區別

office 2007引入了sumifs,可進行多條件求和,可以部分實現sumproduct的功能。但sumproduct有一個功能,sumifs無法做到。比如:

= sumproduct((A1:A100)*((B1:B100="a")+(B1:B100="b")))

即sumproduct可以實現嵌套的條件求和(包括and和or),而sumifs只能實現并列條件求和(即只能是and)。其實sumproduct的適用范圍要寬的多,比如以下條件求和,sumifs都無法實現:

= sumproduct((A1:A100)*((C1:C100+D1:D100)>0)) ?
                      ' sumproduct可對不同區域進行預算

= sumproduct((A1:A100)*(C1:C100<D1:D100)) ? ? ?
                      ' sumproduct可對不同區域進行比較

= sumproduct((A1:A100)*(LOG(C1:C100,2))) ? ? ? ? ?
                      ' sumproduct可使用excel內置函數

其它

數組公式

sumproduct的所有功能都可以用公式組實現。比如將sumproduct換成sum,然后按CTRL+SHIFT+ENTER確認,可得到一模一樣的結果

= sumproduct((A1:A100)*((B1:B100="a")+(B1:B100="b")))
{= sum((A1:A100)*((B1:B100="a")+(B1:B100="b")))}

但數組公式難以被理解,輸入速度慢(無法拖動等等),并且一般情況下數組公式比普通公式效率要低,所以能用普通函數實現的功能,建議直接使用普通函數。

加權平均值

value和weight分別為值和權重,那么可以通過以下方法

= sumproduct(value*weight) / sum(weight)

公式可包含數據

Excel支持直接輸入數據,所以我們可以使用下面這樣的表達式:

= sumproduct((A1:A4)*{1;2;3;4})

注意下面公式得到的結果是一樣的,但運算效率要低很多。從Excel區域計算的原理知道,下面的式子中sumproduct的參數被展開成一個4×4的方塊,所以運算速度要慢一個級別(線性 vs 平方):

= sumproduct((A1:A4)*{1,2,3,4})

這里區別在于";"號表示換行,而","號表示下一列,所以{1;2;3;4}是一個列向量,而{1, 2, 3, 4}是一個行向量。我們也可以將其結合,{1,2,3,4;5,6,7,8;9,10,11,12;}是一個3×4的矩陣。

作者:admin | Categories:軟件使用 | Tags:

一條評論

  1. Memorylunar說道:

    謝謝!請教: 隱性轉換的 --() 的原理。

發表評論

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

*

澳洲f1赛车b盘开奖套路 手机在线打麻将小游戏 湖人vs独行侠 北京期货配资网 甘肃十一选五开奖结 河北十一选五官网下 福彩29选7走势图 快播欧美av下载 长沙站街女地址 棒球比分app 扑克麻将怎么发牌 原千岁字中文在线迅雷 基金配资哪家好 2012年足球直播 黑龙36选7 东京热播巨雨女教师 广东好彩1