excelperfect

導(dǎo)語：本案例是《Excel公式練習(xí)60：求相鄰單元格兩兩相乘之積的和》的擴展。

本次的練習(xí)是：需要求一行中列B至列V中的值兩兩相乘的結(jié)果之和，例如：

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

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

示例數(shù)據(jù)如下圖1所示。

圖1

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

公式

如果數(shù)據(jù)在單元格區(qū)域B2:V2中，那么可以使用公式：

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

公式解析

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

數(shù)組1：(B2:V2)*((B1:V1)='a')

數(shù)組2：(C2:W2)*((C1:W1)='b')

兩個數(shù)組相似，只看一個數(shù)組就行了。我們看數(shù)組1，其中的B2:V2就是單元格區(qū)域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'，判斷單元格區(qū)域B1:V1中的值是否與“a”相等，返回TRUE/FALSE組成的數(shù)組：

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

上述兩個中間數(shù)組相乘，即：

{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”對應(yīng)的第二行中的值外，其余為0。

同樣，數(shù)組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”對應(yīng)的值。

將這兩個數(shù)組傳遞給SUMPRODUCT函數(shù)，實現(xiàn)對應(yīng)元素相乘后求和：

=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})

得到結(jié)果：

1400

公式調(diào)試技術(shù)小結(jié)

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

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

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