我有一张 data
,有多个尺寸,如下所示:
A B C D E
1 COUNTRY FLAVOUR SIZE DATE SALES ($)
2 Japan Strawberry 100ml 10/12/14 100
3 Japan Banana 100ml 10/03/15 100
4 China Orange 200ml 14/04/15 30
5 France Strawberry 200ml 11/04/15 400
6 UK 200ml 23/04/15 250
7 ....
我希望在日期范围内聚合此数据,其中 summary
工作表具有每个维度(国家/地区和风味),如果我没有指定维度,则它会对该维度的所有行进行求和 .
A B C
1 COUNTRY FLAVOUR SALES TOTAL
2 Japan Strawberry 100
3 Japan 200
4 Strawberry 500
如果使用SUMPRODUCT或SUMIFS存在所有尺寸(即上面的第2行),我可以这样做:
=SUMPRODUCT((data!A$2:A$100=A1)*(data!B$2:B$100=B1)*(data!D$2:D$100>[start_date]*(data!D$2:D$100<[end_date])*(data!E$2:E$100))
但是,如果输入单元格为空(例如上面的第3行),我无法弄清楚如何包含维度的所有行 . 我试过了:
-
在条件中添加IF语句或OR语句(例如
OR(data!A$2:A$100=A1,isblank(A1))
) . -
在SUMPRODUCT中使用
+
作为OR语句,(根据此答案https://stackoverflow.com/a/27536131/1450420)
一种解决方案是根据存在的概要维度来具有公式的不同分支,但是如果我将这种相同的行为扩展到诸如 Size
之类的更多维度,那么这将快速失控 .
任何帮助赞赏!
(我正在运行Excel Mac 2011) .
EDIT
Per @ BrakNicku的评论我试过的一个公式是 =SUMPRODUCT(((data!A$2:A$100=A2)+ISBLANK(A2))*((data!B$2:B$100=B2)+ISBLANK(B2))*(data!E$2:E$100))
这不起作用的原因是有时我的数据有空白属性(上面编辑过) . 由于某种原因,此公式对存在的属性匹配的行(例如 data!A6
)进行双重计数,但缺少另一个属性(例如 data!B6
) .
EDIT 2
我可以看到为什么会发生这种重复计算,因为 +
正在对匹配进行求和,因为 data!A$2:A$100=A2
(它们匹配因为它们都是空白的)和匹配因为 ISBLANK(A2)
(它确实是空白的) . 问题仍然是如何在不重复计算的情况下实现这一目标 . 如果需要,解决方法可能是使用一些占位符值填充数据上的所有空白单元格 .
2 回答
试试这个(我只包括前两个,我把日期留了出来):
![
重复计算值的原因如下:
如果A列中的单元格为空白,则此总和的两个部分均相等 . 要解决此问题,可以将其更改为: