字體：小 中 大 |
|
|
|
| 2017/06/26 07:04:57瀏覽5482｜回應2｜推薦51 | |
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))) 此即為我們要的式子。 |
|
| ( 知識學習｜其他 ) |
