☰

Excel 求和公式使用嵌套 SUMIFS 返回 0

tag-icon • tag-icon microsoft-excel worksheet-function
Excel 求和公式使用嵌套 SUMIFS 返回 0

我有一张包含交易的表格：

invoice | date     | desc    | amount    | acct1  | acct2
11112   | 1/1/2020 | test a1 | 100.00    | 1001   | 4001
11113   | 1/1/2020 | test a2 | -50.00    | 1001   | 5001
11114   | 2/1/2020 | test b1 | 200.00    | 1001   | 4001

我正在尝试构建一个基于日期和帐户进行总计的公式。因此，在另一张工作表上，我有以下网格。顶行是第 10 行。

[A]|[B]|[C]|[D]|[E]|[F]       |[G]
xxxxxx             | 1/1/2020 | 2/1/2020 
1000               |          |
1001               |   [f12]  |  [g12]  
1100               |          |
2001               |          |
4001               |          |
5001               |          |

单元格“[f12]”和“[g12]”应该具有相同的公式，我在这里：

=ROUND(SUM(SUMIFS(TRANSACT[AMOUNT],TRANSACT[ACCT1],ACCOUNTS!$A12,TRANSACT[DATE], ">="&ACCOUNTS!F$10,TRANSACT[DATE],"<"&ACCOUNTS!G$10),-SUMIFS(TRANSACT[AMOUNT],TRANSACT[ACCT2],ACCOUNTS!$A12,TRANSACT[DATE], ">="&ACCOUNTS!F$10,TRANSACT[DATE],"<"&ACCOUNTS!G$10)),2)

单元格 [f12] 的正确答案应为“200.00”，但公式​​返回“0”。当我拆分或“取消嵌套”公式时，第一个 SUMIF 返回“200.00”，第二个 SUMIFS 返回“0”。

但当我去求和时，我得到的是“0”。事实上，如果我对 2 个单元格求和，我得到的是“0”。对我来说最疯狂的是，当我点击“插入公式”查看公式时，它返回正确的金额，200.00！在此处输入图片描述 我附上了一张图片。

我迫不及待地希望有人能指出我所忽略的明显错误。

编辑：为了清晰起见，我添加了另一张图片。我的问题是，公式似乎可以分段工作，但当我对它进行求和时，结果却不正确。请指教。

在此处输入图片描述

答案1

在这里设置公式后，我发现提供的公式存在三个 (3) 个问题：

  1. 首先，Table公式中的单元格地址： AMOUNT、ACCT1和与ACCT2您提供的表格中的标题不匹配。进行适当的更改以修复...
  2. 其次，您的cells in row 10引用显然必须查看输入表的标题......好吧，我很确定您自己在这里没有问题，因为事实证明您不能，[e12]大多数[f12]人会认为您的意思是（单元格E12和F12），所以第 10 行可能是正确的，但如果是第 11 行，正如人们首先从 [e12] amd [f12] 引用中怀疑的那样，它们需要修复。
  3. 您需要一些ACCOUNTS!F$10参考资料。否则您也会得到 $0。（请记住，我仍然不确定您的输出表在哪里……刚才提到的单元格似乎是2/1/2020输出表中标题后面的一个单元格，因此无论什么地址都适用。

修复两个确定的问题（第 1 和第 3 个），如果第 2 个确实是个问题，那么就修复成功了：它给了我正确的 200 美元结果。对于 [e12] 和 [f12] 都是如此。

鉴于您的输入表是一个表，我想知道您是否将输出表视为表。如果是这样，特别是如果您实际上已经这样做了，而不仅仅是考虑过，为什么不将这些ACCOUNT!cell地址（“C$11”类型，而不是“$C11”类型）替换为表头引用？如果这只是临时工作，那当然是一个不打扰的好理由，但如果是永久性的，为什么不呢？

答案2

我没有昨天的工作，但是我在答案中提到的单元格可以更好地描述为位于“[f12]”列右侧一列的标题行中的单元格，因此它会出现cell G10在更新的布局中。

这片刻我将日期“3/1/20”输入单元格 G10，公式给出了正确的结果。

这是因为，对于其中一个SUMIFS()，您正在比较标题单元格 F10 中的日期和单元格 G10 中的日期。但是，如果 G10 为空，则其值为 0，因此公式无法为该部分提供正确的结果，从而导致整个过程失败。就此而言，公式要求 G10 为较晚的日期，即使只晚一天（实际上，如果输入时它有一个“时间”部分，而不仅仅是日期，那么即使在当天较晚的 2/1/20 也可以，但必须填写并且必须更晚）。

您可以通过多种方式解决这个问题：始终让下一列以下一个适当的日期为标题；如果愿意，可以将其设置为未来一百年的某个日期；创建一个命名范围来引用并为其指定一个日期，例如一百年后的日期，或者甚至创建一个公式，该公式查找最右边的标题，将其视为日期，然后向其添加 1；在附加部分进行编辑，使其显示为(&ACCOUNTS!F$10+1))（非常包含额外的括号很重要！）因此公式中的值总是比使用的最后一列的标题晚一天，但您不必使用一个奇怪的列标题，而其下方的列中没有任何内容。

还有许多其他想法。我最喜欢最后一个，因为如果你向右扩展表格，它就不需要“维护”了……公式本身就包含解决方案。

顺便说一句，在这种情况下（但不是这种情况）常见的问题是，当以某种方式查找的值（就像某种情况一样SUMIFS()）是要查找的源中的文本，但不在查找的位置，或者相反。幸运的是，Excel 发现了一个微妙的差别，即如果“查找”的本质涉及的可能是字符串而不仅仅是数字，Excel 会强制执行格式并且经常找不到任何匹配项。但是，如果执行“查找”的函数是预期进行算术运算的函数，它会将任何看起来像数字的东西视为数字，而不管格式如何。同样，这不是问题，但这正是我提到它的原因：一个人可能会花几个小时试图弄清楚它，认为它是格式不匹配，但这并不能解决问题，但它必须是它，但它并没有解决问题......在站点上循环，甚至没有走上解决问题的正确道路。所以这种微妙的差异会对故障排除产生影响。但不是在这里！

（抱歉，与回复评论相比，答案有点多。我无法发表评论。）

相关内容