網路城邦
上一篇 回創作列表 下一篇   字體:
【Excel範例】:好用的SUMPRODUCT (一)
2017/06/26 07:04:57瀏覽3737|回應2|推薦49

Excel的SUMPRODUCT函數功能強大,網上有許多文章介紹,此處不再贅述。以SUMPRODUCT來做小計的功能,網上未見相關資料,今介紹於下。

SUMPRODUCT是行列式函數,它的參數是行列式,可有三十個之多,基本用法如下:

=SUMPRODUCT({1,2,3},{4,5,6},{7,8,9})

這個函數展開來是:

=1X4X7+2X5X8+3X6X9=270

我見到這個函數,先在心中將之轉為直式,成了:

1 4 7
2 5 8
3 6 9

然後加上乘號,變成:

1X4X7
2X5X8
3X6X9

當然,用橫式也行:

1 2 3
4 5 6
7 8 9

乘號加於垂直方向,垂直相乘:

1 2 3
X X X
4 5 6
X X X
7 8 9

基本功能介紹完畢後,來看上圖。圖中貨物按打數排列,總打數等於箱數乘以打數。若要算甲乙的總共打數,用SUMPRODUCT很容易就算出來:

=SUMPRODUCT(B2:B6,C2:C6)=75

然若要算三打裝的共幾打、四打裝的共幾打,等等,就比較複雜。此時要加兩個篩子,把不要的資料篩掉。一要把不同打數的箱子篩掉,一要把同打數的箱子,但不是最後一箱的篩掉。

把不同打數的箱子篩掉,式子是:

--($B$2:$B$6=B2)

把這個行列式展開,得下式:

$B$2=B2    TRUE    1
$B$3=B2    FALSE    0
$B$4=B2    FALSE    0
$B$5=B2    FALSE    0
$B$6=B2    FALSE    0

對照上圖,得知除了第一式為真外,其餘皆為偽。前端的--把真偽換成數字1與0,真為1,偽為0。

把--($B$2:$B$6=B2)放入SUMPRODUCT中,得:

=SUMPRODUCT(--($B$2:$B$6=B2),$C$2:$C$6,($B$2:$B$6))

將這個行列式乘以其他的行列式,即是將打數不等於B2的全部濾掉,僅得最上面一列:

1X3X1
0X4X6
0X4X9
0X4X1
0X4X2

總和為3。

再來看下面一列,即第三列:

--($B$2:$B$6=B3)

把這個行列式展開,得下式:

$B$2=B3    FALSE    0
$B$3=B3    TRUE    1
$B$4=B3    TRUE    1
$B$5=B3    TRUE    1
$B$6=B3    TRUE    1

將這個行列式乘以其他的行列式,即是將打數不等於B3的全部濾掉:

0X3X1
1X4X6
1X4X9
1X4X1
1X4X2

得24+36+4+8,總和為72。第三至六列,結果完全一樣,都是72。

至此,H行的結果為:

3
72
72
72
72

這很接近欲得之結果,剩下來要把前三個72篩掉,僅留最後一個,式子是:

COUNTIF($B$2:$B$6,B2)=COUNTIF($B$2:B2,B2)

COUNTIF($B$2:$B$6,B2)算的是在$B$2:$B$6中,總共有幾個值等於B2,由上圖可知,三打裝的只有一種。

COUNTIF($B$2:B2,B2)算的是從$B$2到B2中,有幾個B2,這當然只有一個。這個式子看不出什麼名堂,下面幾式,便知分曉:

COUNTIF($B$2:$B$6,B3)=COUNTIF($B$2:$B$6,B4)=COUNTIF($B$2:$B$6,B5)=COUNTIF($B$2:$B$6,B6)=4
COUNTIF($B$2:B3,B3)=1
COUNTIF($B$2:B4,B4)=2
COUNTIF($B$2:B5,B5)=3
COUNTIF($B$2:B6,B6)=4

COUNTIF($B$2:$B$6,B2)=COUNTIF($B$2:B2,B2)    1=1    真
COUNTIF($B$2:$B$6,B3)=COUNTIF($B$2:B3,B3)    4=1    偽
COUNTIF($B$2:$B$6,B4)=COUNTIF($B$2:B4,B4)    4=2    偽
COUNTIF($B$2:$B$6,B5)=COUNTIF($B$2:B5,B5)    4=3    偽
COUNTIF($B$2:$B$6,B6)=COUNTIF($B$2:B6,B6)    4=4    真

如此一來,前三個72為偽,也被濾掉,只餘第一列三打裝及最後一列四打裝為真。這正是我們想要的結果。

把這個式子代入原式子,得:

=SUMPRODUCT(--($B$2:$B$6=B2),$C$2:$C$6,($B$2:$B$6)*(COUNTIF($B$2:$B$6,B2)=COUNTIF($B$2:B2,B2)))


此即為我們要的式子。

( 知識學習其他 )
回應 推薦文章 列印 加入我的文摘
上一篇 回創作列表 下一篇

引用
引用網址:http://classic-blog.udn.com/article/trackback.jsp?uid=junk200&aid=105158906

 回應文章

PJ Chuz
等級:8
留言加入好友
2017/06/29 02:42

點請教:

1.  "它的參數是行列式,可有三十個之多 是否: {1,2,3},{4,5,6}... 可以計算三十組?

2.  函列式中的「位值」如 B2, B3, … 若以「數值」直接列出如 3, 4, … 是否應該也可以?

 

SUMPRODUCT 中套了一個 COUNTIF 便可輕鬆操作一項篩檢的功能,高明的進階運用。

【無★言】家喻戶曉的中國人(junk200) 於 2017-06-29 08:37 回覆:

請教不敢當,我們相互切磋吧。


一、是的,可以有三十組。這是網上說的。我從沒用過那麼多組,也沒見人用那麼多組。


二、行列式位址無法替換,數值則可。以COUNTIF($B$2:B2,B2)為例,$B$2:B2是行列式的位址,這不能以數值替換,後面那個B2是數值,直接代入數值也可以。以上圖為例,B2之值為3,故此式可以改成COUNTIF($B$2:B2,3)。


然而,這會有個不便之處。複製方程式時,Excel會自動更換位址。舉個例子,把COUNTIF($B$2:B2,B2)複製到下一列時,它會自動變成COUNTIF($B$2:B3,B3)。若把B2以數值代之,則COUNTIF($B$2:B2,3)往下複製時,成了COUNTIF($B$2:B3,3),這不是想要的,B3的值為4,我們想要的是COUNTIF($B$2:B3,4)。此時,只能慢慢一個一個手改,效果不佳。


LJ
等級:8
留言加入好友
2017/06/27 16:19
於我而言實乃深奧的數學.....
【無★言】家喻戶曉的中國人(junk200) 於 2017-06-28 07:34 回覆:

其實這與數學無關喔!是如何運用Excel函數。

Excel函數有些實在很高深,看都看不懂,不知當初是怎麼想出來的。