在 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

arrow
arrow

    vincent 發表在 痞客邦 留言(1) 人氣()