excel函數求滿足條件的單元格兩兩相乘之積的和

excel函數求滿足條件的單元格兩兩相乘之積的和

需要求一行中列B至列V中的值兩兩相乘的結果之和，例如：

(B2*C2)+(E2*F2)+(H2*I2)+…+(T2*U2)

注意，每兩個列中的值相乘之間間隔了一列，也就是說，要求第1行中“A”和“B”對應的第2行中的值相乘的積之和。有沒有不需要列出該行中的每個單元格的更簡單的公式？

示例數據如下圖1所示。

圖1

先不看答案，自已動手試一試。

公式

如果數據在單元格區域B2:V2中，那么可以使用公式：

=SUMPRODUCT((B2:V2)*((B1:V1)=”a”),(C2:W2)*((C1:W1)=”b”))

公式解析

從公式中可以看到，SUMPRODUCT函數包含了2個數組：

數組1：(B2:V2)*((B1:V1)=”a”)

數組2：(C2:W2)*((C1:W1)=”b”)

兩個數組相似，只看一個數組就行了。我們看數組1，其中的B2:V2就是單元格區域B2:V2中的值，即：

{10,20,30,10,20,30,10,20,30,10,20,30,10,20,30,10,20,30,10,20,30}

另一個(B1:V1)=”a”，判斷單元格區域B1:V1中的值是否與“a”相等，返回TRUE/FALSE組成的數組：

{TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE}

上述兩個中間數組相乘，即：

{10,20,30,10,20,30,10,20,30,10,20,30,10,20,30,10,20,30,10,20,30}*{TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE,TRUE,FALSE,FALSE}

得到：

{10,0,0,10,0,0,10,0,0,10,0,0,10,0,0,10,0,0,10,0,0}

可以看出，除列中第一行的值是“a”對應的第二行中的值外，其余為0。

同樣，數組2：

(C2:W2)*((C1:W1)=”b”)

可得到：

{20,0,0,20,0,0,20,0,0,20,0,0,20,0,0,20,0,0,20,0,0}

即列中“B”對應的值。

將這兩個數組傳遞給SUMPRODUCT函數，實現對應元素相乘后求和：

=SUMPRODUCT({10,0,0,10,0,0,10,0,0,10,0,0,10,0,0,10,0,0,10,0,0},{20,0,0,20,0,0,20,0,0,20,0,0,20,0,0,20,0,0,20,0,0})

得到結果：

1400

公式調試技術小結

1.如果公式要處理的數據很多，可以在編寫公式時在少量的數據上測試，通過后再應用到要處理的數據中。

2.測試公式時，沒必要使用很大的數字，可以使用簡單的數字如1、10、20等。

3.在測試公式時，可以選取公式的某部分，按F9鍵查看中間結果，來評估公式是不是按我們的意愿在工作。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。