excelperfect 导语:本案例是《Excel公式练习60:求相邻单元格两两相乘之积的和》的扩展。 本次的练习是:需要求一行中列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键查看中间结果,来评估公式是不是按我们的意愿在工作。 |
|
来自: hercules028 > 《excel》