SUMPRODUCT 函数简介
在今天的文章中，我们来讨论一下 SUMPRODUCT 函数的几种用法。SUMPRODUCT 是数据分析人员最喜爱的函数之一，特别是当需要解决在指定条件下计算诸如“多少”、“多久”、求和、求平均值此类的问题时。首先我们简单介绍一下 SUMPRODUCT，然后再举例说明其用法。
语法：
SUMPRODUCT 函数只有第一个参数是必须的，其它的均是可选参数。SUMPRODUCT 以具有相同大小的数组为参数，将其相乘，并返回结果数组的和。数组大小必须一致，否则会报错。其语法如下：
SUMPRODUCT ( array1, [array2], [array3], ...)
以上语法来自微软的帮助文件，可以看吃上只有第一个数组是必须的，而其余的则是可选的（可选参数以方括号表示）。参数最多可达255个。
数据
我们用示例中的销售数据来说明。示例数据共有6个字段，分别命名为 Item, Date, Qtty, Sales Person ID, Paid through, Sales Amt。现在我们来说明 SUMPRODUCT 的各种用法。
示例：
使用 SUMPRODUCT 对某项进行求和：
对指定区域进行求和可能是 SUMPRODUCT 最简单的用法。比如在上图所示的数据中，我们可以用下面的公式对铅笔的销售数量进行求和：
=SUMPRODUCT((A2:A20="Pencil")*(C2:C20))
公式首先检查 A2:A20 中的铅笔项，形成由真值、假值（即1和0）组成的数组，然后分别与销售数量对应的值相乘，再对最终数组进行求和即可得到铅笔总的销售数量。
小技巧：可以将公式中的固定引用替换如下所示的单元格引用。
=SUMPRODUCT((A2:A20=H3)*(C2:C20))
此处我们用单元格 H3 来替换 "Pencil"。
使用 SUMPRODUCT 对客户代表的销售数据进行求和
在示例数据中共有三个客户代表，其 ID 分别为1，2，3. 我们可以用 SUMPRODUCT 对销售数据进行汇总。所用公式如下：
=SUMPRODUCT((D2:D20=H4)*(C2:C20))
公式中第一对括号部分用于将客户代表的 ID 与我们想要对其销售数据汇总的客户代表 ID 进行比较，这样就得到一个 ID=3 布尔型的数组，然后将其与销售数量的各个值分别相乘，最后再用 SUMPRODUCT 函数对其进行汇总。
在 SUMPRODUCT 中使用比较运算符（大于/小于/小于比较）
Excel 中的比较运算符如下表所示：
例如如果我们要求某个客户代表销售数量小于等于4的次数时，可以使用如下公式：
=SUMPRODUCT((D2:D20=H6)*(C2:C20<=4)*1)
此公式首先对 D2:D20 中的客户代表 ID 进行检查，符合条件时返回 TRUE，然后将销售数量与用户设定的条件进行比较（此处是小于等于4），小于等于的将返回 TRUE，再将这两个数组分别相乘得到另一个布尔型数组，最后乘以1并求和后得到最终答案。
在 SUMPRODUCT 中使用比较运算符（不等于比较）
我们有时可能需要统计某个客户代表除了铅笔外其它项目的销售数量，这时就要使用不等于运算符了（"<>"）。比如要统计客户代表 1 除了削笔器外其它项目的销售数量，就可以使用一下公式：
=SUMPRODUCT((A2:A20<>H8)*(D2:D20=1)*(C2:C20))
在公式的第一对括号中，我们使用了不等于运算符。它保证了只有当不等于所给条件（削笔器）的项目才返回 TRUE。第二个括号中的比较运算是确保客户代表的 ID 为1，这两个数组的乘积与第三个数组中的数量相乘并求和得到所需结果。本例中，客户代表 1 除了削笔器外其它项目的销售数量是 44.
使用 SUMPRODUCT 查询使用现金或者信用卡支付的次数
相似的公式可用于查询交易是使用信用卡还是现金支付的。要用 SUMPRODUCT 查询支付方式，需要对包含支付方式的单元格区域进行检查。公式如下：
=SUMPRODUCT((E2:E20=H11)*(F2:F20))
公式中的第一对括号中用于检查支付方式是否是信用卡，然后返回一个由 TRUE/FALSE 构成的数组，此数组与由销售金额组成的数组中的对应值分别相乘得到另一个数组，对此数组求和得到最终结果。
利用 SUMPRODUCT 对某个月内的销售数据进行统计
可以使用 Excel 中的 DAY 和 MONTH 函数对指定月份内的交易进行统计，当然也可以通过给定开始、结束日期的方式来统计。假设我们给定月份的开始日期，通过公式可以计算出此月的结束日期。
单元格 I15 中是月份的起始日期，在下个单元格中通过 EOMONTH() 计算出此月的结束日期。
=EOMONTH(I15,0)
在下个单元格中通过下列公式计算给定月份（起始、结束日期）的总交易金额：
=SUMPRODUCT((B2:B20>=I15)*(B2:B20<=I16)*(F2:F20))
公式中第一对括号用于计算大于当月起始日的区域，第二对括号用于计算小于或等于当月结束日期的区域，然后与销售金额区域相乘即得到最终答案。
总结：
SUMPRODUCT 函数还有许多其它用法，请下载 示例 文件进行学习。
网友评论