excel数据透视表的计算教程(不需再用数据透视表了)

excel数据透视表的计算教程(不需再用数据透视表了)(1)

如下图所示产品销售明细表

excel数据透视表的计算教程(不需再用数据透视表了)(2)

现需要根据单元格H1和H2的年月份,自动生成一个下面统计面板

excel数据透视表的计算教程(不需再用数据透视表了)(3)

分析:在统计需求中,很多是数据透视表无法或很难完成的。而且这么多数据,如果都用单独的数据透视表来完成,是很不方便的。而Sumproduct函数却可以完成统大部分统计。

1、本年累计公式

根据H1的年份计算本年累计销售额

=SUMPRODUCT((YEAR(A2:A182)=H1)*E2:E182)

公式说明:用year函数提取A列的年份和H1对比,然后用sumprodcut对符合条件的值进行求和

excel数据透视表的计算教程(不需再用数据透视表了)(4)

2、本月累计公式

根据H1的年份和H2的月份数计算本月累计销售额

=SUMPRODUCT((YEAR(A2:A182)=H1)*(MONTH(A2:A182)=H2)*E2:E182)

公式说明:原理同公式 1,只是增加了月的对比

excel数据透视表的计算教程(不需再用数据透视表了)(5)

3、本月已统计天数和本月平均公式

动态统计出本月统计了多少天

=SUMPRODUCT((YEAR(A2:A182)=H1)*(MONTH(A2:A182)=H2)*(B2:B182<>""))

公式说明:根据A列的年和月,统计出B列非空单元格个数。

本月平均

=H5/H6

excel数据透视表的计算教程(不需再用数据透视表了)(6)

4、最近一天公式

返回明细表中最后一天有数据的日期,以方便后面公式引用

=LOOKUP(1,0/(B2:B173<>""),A2:A173)

公式说明:这里使用了经典lookup(1,0/(条件),返回区域) ,返回最后一个符合条件的值。

excel数据透视表的计算教程(不需再用数据透视表了)(7)

5、最近30天销售合计和平均公式

统计最近30天的销售额之和

=SUMPRODUCT(((A2:A182)>=H9-30)*E2:E182)

公式说明:大于等30天前的日期(H9-30)作为条件,用sumprodcut统计出符合条件E列的和

最近30天平均

=H10/30

excel数据透视表的计算教程(不需再用数据透视表了)(8)

6、本日(最后一天)销售金额公式

返回最后一天销售金额,以备计算排名用

=LOOKUP(1,0/(D2:D173<>""),E2:E173)

公式说明:原理同计算最后一天日期

excel数据透视表的计算教程(不需再用数据透视表了)(9)

7、本日销售排名(本年)公式

本日销售在全年的排名

=SUMPRODUCT((YEAR(A2:A182)=YEAR(H9))*(E2:E182>H12)) 1

公式说明:用sumproduct函数统计出本年销售额大于今日销售额的天数, 1后就是本日排名

excel数据透视表的计算教程(不需再用数据透视表了)(10)

8、本日销售排名(本月)公式

本日销售在本月的排名

=SUMPRODUCT((YEAR(A2:A182)=YEAR(H9))*(MONTH(A2:A182)=MONTH(H9))*(E2:E182>H12)) 1

公式说明:原来同公式7,只是增加了月份判断

excel数据透视表的计算教程(不需再用数据透视表了)(11)

从本文可以看出,Suprouct函数无论是求和、计数,还是计算排名都非常方便。

如果觉得实用,请继续关注哦~

,

免责声明:本文仅代表文章作者的个人观点,与本站无关。其原创性、真实性以及文中陈述文字和内容未经本站证实,对本文以及其中全部或者部分内容文字的真实性、完整性和原创性本站不作任何保证或承诺,请读者仅作参考,并自行核实相关内容。文章投诉邮箱:anhduc.ph@yahoo.com

    分享
    投诉
    首页