2017年3月11日 星期六

Excel-計算相鄰欄/相鄰列的相乘積(SUMPRODUCT,MOD,ROW,COLUMN)

在 Excel 的工作表中,遇到的格式中,常會有左右相鄰兩欄要計算乘積和,或是上下相鄰兩列要計算乘積和,該如何處理。
在 Excel 中,若要計算『乘積和』,最常使用的函數是:SUMPRODUCT
1. 左右相鄰兩欄
Excel-計算相鄰欄/相鄰列的相乘積(SUMPRODUCT,MOD,ROW,COLUMN)
使用 SUM 函數也是可以輕鬆的計算得到結果:
儲存格N3:=SUM(B3*C3,D3*E3,H3*I3,F3*G3,J3*K3,L3*M3)
但是計算項目若很多時,將會造成輸入上的困擾。若改用 SUMPRODUCT 函數:
儲存格N3:=SUMPRODUCT(MOD(COLUMN(B3:L3)+1,2)*B3:L3*C3:M3)
複製儲存格N3,貼至儲存格N3:N6。
(1) MOD(COLUMN(B3:L3)+1,2)
公式:=MOD((2,3,4,5,6,7,8,9,10,11,12)+1,2)
公式:=MOD(3,4,5,6,7,8,9,10,11,13)
公式:=(1,0,1,0,1,0,1,0,1,0,1)
(2) SUMPRODUCT((1,0,1,0,1,0,1,0,1,0,1)*B3:L3*C3:M3)
公式:=SUMPRODUCT((B3,0,D3,0,F3,0,H3,0,J3,0,L3)*C3:M3)
公式:=B3*C3+0+D3*E3+0+F3*G3+0+H3*I3+0+J3*K3+0+L3*M3
公式:=B3*C3+D3*E3+F3*G3+H3*I3+J3*K3+L3*M3

2. 上下相鄰兩列
計算相鄰欄/相鄰列的相乘積(SUMPRODUCT,MOD,ROW,COLUMN)
通常你會使用以下的公式來計算乘積和:
儲存格C14:=C2*C3+C4*C5+C6*C7+C8*C9+C10*C11+C12*C13
但是計算項目若很多時,將會造成輸入上的困擾。若改用 SUMPRODUCT 函數:
儲存格C14:=SUMPRODUCT(MOD(ROW(C2:C12)+1,2)*C2:C12*C3:C13)
複製儲存格C14,貼至儲存格C14:F14。
(1) MOD(ROW(C2:C12)+1,2)
公式:=MOD((2,3,4,5,6,7,8,9,10,11,12)+1,2)
公式:=MOD(3,4,5,6,7,8,9,10,11,13)
公式:=(1,0,1,0,1,0,1,0,1,0,1)
(2) SUMPRODUCT((1,0,1,0,1,0,1,0,1,0,1)*C2:C12*C3:C13)
公式:=SUMPRODUCT((C2,0,C4,0,C6,0,C8,0,C10,0,C12)*C3:M3)
公式:=C2*C3+0+C4*C5+0+C6*C7+0+C8*C9+0+C10*C11+0+C12*C13
公式:=C2*C3+C4*C5+C6*C7+C8*C9+C10*C11+C12*C13

沒有留言:

張貼留言

檢視其他文章

好康東東