|
提到求和，大家想到的肯定是Sum、Sumif、Sumifs等函数，而计数则想到的肯定是Count、Counta、Countif、Countifs等函数，排名则用Rank函数，但是，在Excel中，有一个函数它既能求和、计数、还会权重计算和排名等功能，它就是Sumproduct函数。 一、Sumproduct函数简介。 功能：返回相应的数组区域乘积的和。 语法：=Sumproduct(数组或单元格引用1，数组或单元格引用2……数组或单元格引用N）。 注意事项： 1、如果SUMPRODUCT函数具有多个参数数组，这些数组之间必需具有相同的维数，否则SUMPRODUCT将返回#VALUE!错误值REF！ 2、函数Sumproduct将非数据类型的元素作为0处理。 示例： 方法： 在目标单元格中输入公式：=SUMPRODUCT(C3:C9,D3:D9)。 解读： 公式=SUMPRODUCT(C3:C9,D3:D9)可以理解为：C3*D3+C4*D4+……C9*D9，暨对应的同一行单元格乘积的和。 二、Sumproduct：求和。 1、单条件求和。 方法： 在目标单元格中输入公式：=SUMPRODUCT((E3:E9=H3)*C3:C9)、=SUMPRODUCT((E3:E9=H3)*C3:C9*D3:D9)。 解读： 1、首先判断E3:E9=H3条件是否成立，如果成立，则返回True，暨1，否则返回False,，暨0。 2、以“上海”地区的为例：公式=SUMPRODUCT((E3:E9=H3)*C3:C9)的计算过程就是：1*66+0*88+0*67+0*56+0*33+1*57+0*20=123。公式：=SUMPRODUCT((E3:E9=H3)*C3:C9*D3:D9)的计算过程就是：1*66*39+0*88*58+0*67*61+0*56*53+0*33*42+1*57*84+0*20*82=7362。 2、多条件求和。 方法： 在目标单元格中输入公式：=SUMPRODUCT((E3:E9=H3)*(C3:C9>=50),C3:C9)、=SUMPRODUCT((E3:E9=H3)*(C3:C9>=50),C3:C9*D3:D9)。 解读： 1、其实计算过程和单条件的类似，只是多了一个条件判断而已，具体请参阅单条件的计算过程。 2、为了是公式更具有条理性，我们可以将条件用*（乘号）连接在一起，将数值用*（乘号）连接在一起，条件和数值之间用，（逗号）分隔，但，（逗号）的作用还是乘。 3、连接符，（逗号）和*（乘号）的区别在于：如果数据源中包含文本，必须用，（逗号）连接，而不能用*（乘号）连接。故*（乘号）只能用于连接数据类型的值。 3、隔列求和。 方法： 在目标单元格中输入公式：=SUMPRODUCT(($C$3:$J$3=K$3)*$C4:$J4)。 解读： 1、隔列求和也很好理解，需要注意的就是相对引用和绝对引用的使用，如条件区域C3:J3不变，随意采用绝对引用，而条件值的列会发生变化，随意采用相对和绝对引用相结合的方式。 2、数据区域也是采用绝对和相对引用相结合的方式。 三、Sumproduct：计数。 1、单条件计数。 方法： 在目标单元格中输入公式：=SUMPRODUCT(N(E3:E9=H3))。 解读： 1、公式中用到了Excel中最短函数N，其主要作用为将非数值的值转换为数值。 2、首先判断E3:E9=H3是否成立，如果成立，返回True，否则返回False，然后用N函数将对应的值转换为1和0。 3、当Sumproduct函数只有一个数据区域时，对区域中的值进行求和处理。以“上海”为例：判断和转换后的结果为{1;0;0;0;0;1;0},求和的结果为2。达到了计数的目的。 2、多条件计数。 方法： 在目标单元格中输入公式：=SUMPRODUCT(N(E3:E9=H3)*(C3:C9>=50))。 解读： 1、以“上海”为例，公式可以理解为：=Sumproduct（{1;0;0;0;0;1;0},{1;0;0;0;0;1;0}），所以返回的结果为2。 四、Sumproduct：多权重计算。 方法： 在目标单元格中输入公式：=SUMPRODUCT(C$3:E$3,C4:E4)。 解读： 1、从表中我们可以看出要计算考生的最终成绩，其中体能占20%，笔试占30%，面试占50%。 2、各项所占的比是固定不变的，所以行绝对引用，不可以随着单元格的变动而变动，而每个人的成绩要随着人员的变化而变化，所以才用相对引用的形式。 3、如果增加“名次”列，则可以快速的对考生的成绩进行排序。排序请继续阅读。 五、Sumproduct：中国式排名。 方法： 在目标单元格中输入公式：=SUMPRODUCT(($F$4:$F$10>F4)/COUNTIF($F$4:$F$10,$F$4:$F$10))+1。 解读： 1、首先判断$F$4:$F$10>F4条件是否成立，如果成立，则返回True，暨1，否则返回False，暨0。 2、=COUNTIF($F$4:$F$10,$F$4:$F$10)的作用就是形成一个7个元素为1的数组。 3、第一步和第二步对应的数组元素进行除法运算，形成一个新的数组，而Sumproduct函数当数组区域为单一区域时，对区域中的值进行求和运算。 4、公式中的1为附加值。 5、以93.8分的名次为例，公式=SUMPRODUCT(($F$4:$F$10>F4)/COUNTIF($F$4:$F$10,$F$4:$F$10))就转化为=Sumproduct（{0;0;0;1;0;0;0}/{1;1;1;1;1;1;1}）,然后对应位置的数组相除得到新的数组=Sumproduct({0;0;0;1;0;0;0})，对其进行求和：0+0+0+1+0+0+0=1，再加上附加值1，最终结果为2。 结束语： 本文主要学习了万能函数Sumproduct，其不仅能够求和、计数、还能够计算权重和排名，功能非常的强大，这难倒是要抢“饭碗”的节奏？…… 各位亲在学习的过程中如果有不明白的地方，欢迎在留言区留言讨论哦！ |
|
|
