excelperfect

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

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

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

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

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

圖1

先不看答案，自已動(dòng)手試一試。

公式

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

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

公式解析

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

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

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

兩個(gè)數(shù)組相似，只看一個(gè)數(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}

另一個(gè)(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}

上述兩個(gè)中間數(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”對(duì)應(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”對(duì)應(yīng)的值。

將這兩個(gè)數(shù)組傳遞給SUMPRODUCT函數(shù)，實(shí)現(xiàn)對(duì)應(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í)在少量的數(shù)據(jù)上測(cè)試，通過后再應(yīng)用到要處理的數(shù)據(jù)中。

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

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