Excel中Sumproduct函数的强大功能。在多数Excel用户常用数据透视表进行统计，但数据透视表存在布局固定、部分统计难以完成的局限下，引出了Sumproduct函数。通过产品销售明细表的统计需求，详细阐述了Sumproduct函数在本年累计、本月累计、本月已统计天数和平均、最近一天、最近30天销售合计和平均、本日销售金额、本日销售排名（本年和本月）等方面的具体公式及原理。
从数据统计的角度来看，在众多Excel用户群体中，数据透视表往往是多数人的首选。这是因为它具有易学易用的特点，并且统计功能较为强大，能满足很多常规的数据统计需求。
然而，数据透视表并非是万能的存在。由于其布局相对固定，导致有许多统计任务很难借助它来完成。例如，在处理含有大量零值的行计算平均值时，数据透视表就显得力不从心。
不过，有一个堪称万能的统计函数——Sumproduct函数，它可以实现数据透视表的大部分功能。对于数据透视表无法完成的统计任务，它也能够轻松应对。
接下来，让我们通过一个具体的例子来深入了解Sumproduct函数的强大之处。如下图所示，这是一份产品销售明细表：
现在，我们需要根据单元格H1和H2中的年月份信息，自动生成一个如下的统计面板：
经过分析我们发现，在实际的统计需求中，有很多任务是数据透视表难以甚至无法完成的。而且，如果针对这么多的数据都单独使用数据透视表来进行统计，操作起来会非常不方便。而Sumproduct函数却能够完成大部分的统计工作。下面为大家详细介绍具体的公式及原理：
1、本年累计公式
该公式的作用是根据H1单元格中的年份来计算本年累计销售额，公式为：
=SUMPRODUCT((YEAR(A2:A182)=H1)*E2:E182)
公式说明：利用year函数提取A列数据中的年份，并将其与H1单元格中的年份进行对比。然后，使用sumprodcut函数对符合条件的值进行求和。具体示例如下图所示：
2、本月累计公式
此公式用于根据H1的年份和H2的月份数计算本月累计销售额，公式如下：
=SUMPRODUCT((YEAR(A2:A182)=H1)*(MONTH(A2:A182)=H2)*E2:E182)
公式说明：其原理与公式1类似，只是在判断条件中增加了月份的对比。具体情况见下图：
3、本月已统计天数和本月平均公式
该公式能够动态统计出本月已经统计的天数，公式为：
=SUMPRODUCT((YEAR(A2:A182)=H1)*(MONTH(A2:A182)=H2)*(B2:B182<>””))
公式说明：根据A列数据中的年和月信息，统计出B列中非空单元格的个数。计算出本月已统计天数后，本月平均销售额的计算公式为：
=H5/H6
具体示例见下图：
4、最近一天公式
该公式的作用是返回明细表中最后一天有数据的日期，方便后续公式引用，公式为：
=LOOKUP(1,0/(B2:B173<>””),A2:A173)
公式说明：这里运用了经典的lookup（1，0/（条件）,返回区域） 公式，用于返回最后一个符合条件的值。具体情况如下图所示：
5、最近30天销售合计和平均公式
统计最近30天的销售额之和的公式为：
=SUMPRODUCT(((A2:A182)>=H9-30)*E2:E182)
公式说明：以大于等于30天前的日期(H9 – 30)作为判断条件，使用sumprodcut函数统计出符合条件的E列数据之和。计算最近30天平均销售额的公式为：
=H10/30
具体示例见下图：
6、本日（最后一天）销售金额公式
该公式用于返回最后一天的销售金额，以便后续计算排名使用，公式为：
=LOOKUP(1,0/(D2:D173<>””),E2:E173)
公式说明：其原理与计算最后一天日期的公式相同。具体情况见下图：
7、本日销售排名（本年）公式
该公式用于计算本日销售在全年的排名，公式为：
=SUMPRODUCT((YEAR(A2:A182)=YEAR(H9))*(E2:E182>H12)) + 1
公式说明：使用sumproduct函数统计出本年销售额大于今日销售额的天数，在此基础上加1即为本日的排名。具体示例见下图：
8、本日销售排名（本月）公式
该公式用于计算本日销售在本月的排名，公式为：
=SUMPRODUCT((YEAR(A2:A182)=YEAR(H9))*(MONTH(A2:A182)=MONTH(H9))*(E2:E182>H12)) + 1
公式说明：原理与公式7相同，只是在判断条件中增加了月份的判断。
Excel中Sumproduct函数展开，先指出数据透视表在统计方面存在的局限性，进而引出Sumproduct函数。通过产品销售明细表的实例，详细介绍了Sumproduct函数在多种统计场景下的具体公式及原理，包括本年累计、本月累计、本月已统计天数和平均等八个方面。这表明Sumproduct函数在Excel数据统计中具有强大的功能和广泛的应用价值，能够解决数据透视表难以完成的统计任务。本文总结
原创文章，作者：modesty，如若转载，请注明出处：https://www.qihaozhang.com/archives/1400.html
