Question

excel统计问题

日期 姓名 产品 收入 最近7天平均
1号 王二 A 10
1号 张三 B 15
...............
2号 王二 B 11
2号 王二 A 15
2号 张三 B 12
.............
求：
姓名,产品相同的:最近7天平均收入。该表为累计数据，每天更新，只求最近7天。

Answer 1

给你一个参考图：

图中，给出三种日期前的前7天内的平均值，需要说明的是，均按此日期内实际发生日期的天数统计（即：有二天记录，按二天平均，三天的按三天平均，以此类推）

E3公式：

=IF(OR(A3<MAX(A:A)-6,AND(IF(ISERROR(MATCH(SUMIFS(D:D,A:A,">="&MAX(A:A)-6,A:A,"<="&MAX(A:A),B:B,B3,C:C,C3)/7,F$2:F2,0)),FALSE(),IF(MATCH(SUMIFS(D:D,A:A,">="&MAX(A:A)-6,A:A,"<="&MAX(A:A),B:B,B3,C:C,C3)/COUNTIFS(A:A,">="&MAX(A:A)-6,A:A,"<="&MAX(A:A),B:B,B3,C:C,C3),F$2:F2,0)<>ROW(),TRUE(),FALSE())),A3>=MAX(A:A)-6,A3<=MAX(A:A))),"",SUMIFS(D:D,A:A,">="&MAX(A:A)-6,A:A,"<="&MAX(A:A),B:B,B3,C:C,C3)/COUNTIFS(A:A,">="&MAX(A:A)-6,A:A,"<="&MAX(A:A),B:B,B3,C:C,C3))

F3的公式：

=IF(OR(A3<TODAY()-7,A3>TODAY()-1,AND(IF(ISERROR(MATCH(SUMIFS(D:D,A:A,">="&TODAY()-7,A:A,"<="&TODAY()-1,B:B,B3,C:C,C3)/COUNTIFS(A:A,">="&TODAY()-7,A:A,"<="&TODAY()-1,B:B,B3,C:C,C3),F$2:F2,0)),FALSE(),IF(MATCH(SUMIFS(D:D,A:A,">="&TODAY()-7,A:A,"<="&TODAY()-1,B:B,B3,C:C,C3)/COUNTIFS(A:A,">="&TODAY()-7,A:A,"<="&TODAY()-1,B:B,B3,C:C,C3),F$2:F2,0)<>ROW(),TRUE(),FALSE())),A3>=TODAY()-7,A3<=TODAY()-1)),"",SUMIFS(D:D,A:A,">="&TODAY()-7,A:A,"<="&TODAY()-1,B:B,B3,C:C,C3)/COUNTIFS(A:A,">="&TODAY()-7,A:A,"<="&TODAY()-1,B:B,B3,C:C,C3))

G3的公式：

=IF(OR(A3<H3-6,A3>H3,AND(IF(ISERROR(MATCH(SUMIFS(D:D,A:A,">="&H3-6,A:A,"<="&H3,B:B,B3,C:C,C3)/COUNTIFS(A:A,">="&H3-6,A:A,"<="&H3,B:B,B3,C:C,C3),G$1:G2,0)),FALSE(),IF(MATCH(SUMIFS(D:D,A:A,">="&H3-6,A:A,"<="&H3,B:B,B3,C:C,C3)/COUNTIFS(A:A,">="&H3-6,A:A,"<="&H3,B:B,B3,C:C,C3),G$1:G2,0)<>ROW(),TRUE(),FALSE())),A3>=H3-6,A3<=H3)),"",SUMIFS(D:D,A:A,">="&H3-6,A:A,"<="&H3,B:B,B3,C:C,C3)/COUNTIFS(A:A,">="&H3-6,A:A,"<="&H3,B:B,B3,C:C,C3))

式中：

OR(A3<H3-6,A3>H3,AND(IF(ISERROR(MATCH(SUMIFS(D:D,A:A,">="&H3-6,A:A,"<="&H3,B:B,B3,C:C,C3)/COUNTIFS(A:A,">="&H3-6,A:A,"<="&H3,B:B,B3,C:C,C3),G$1:G2,0)),FALSE(),IF(MATCH(SUMIFS(D:D,A:A,">="&H3-6,A:A,"<="&H3,B:B,B3,C:C,C3)/COUNTIFS(A:A,">="&H3-6,A:A,"<="&H3,B:B,B3,C:C,C3),G$1:G2,0)<>ROW(),TRUE(),FALSE())),A3>=H3-6,A3<=H3))

用于判断条件外的记录、条件内非首条记录，使之显示为空，使界面整洁，可省略；

SUMIFS(D:D,A:A,">="&H3-6,A:A,"<="&H3,B:B,B3,C:C,C3)/COUNTIFS(A:A,">="&H3-6,A:A,"<="&H3,B:B,B3,C:C,C3)   用于计算平均数

其中： 

  COUNTIFS(A:A,">="&H3-6,A:A,"<="&H3,B:B,B3,C:C,C3)    计算符合条件的次数(即天数)；

  SUMIFS(D:D,A:A,">="&H3-6,A:A,"<="&H3,B:B,B3,C:C,C3)  计算符合条件的总和

公式可适用于wps表格/excel，已验证。

Answer 2

如果你的日期是类似2013-3-14的标准日期格式，公式如下
=SUMPRODUCT(($A$2:$A$999>=TODAY()-6)*($A$2:$A$999<=TODAY())*($B$2:$B$999="王二")*($C$2:$C$999="A")*$D$2:$D$999)/7
如果是2007及以上版本，还可以用如下公式
=SUMIFS(D:D,A:A,">="&TODAY()-6,A:A,"<="&TODAY(),B:B,"王二",C:C,"A")/7
上述公式表示 （当前天数-6天）到当前天数这7天“王二”销售“A”产品每天的销量。
其它公式依此类推。

Answer 3

用数据透视表，日期作为行数据显示，显示最大值为7，数据统计项为平均值。OK