☰

是否可以不使用多个 sumif 来对多个间隔求和？

tag-icon • tag-icon microsoft-excel worksheet-function
是否可以不使用多个 sumif 来对多个间隔求和？

我正在 Excel 中整理一份财务报表，其中我从试算表中获取数据，该试算表有一列包含帐号（名为“帐户”）和一列包含金额（名为“金额”）。

对于财务报表中的项目收入，我想要计算从 3199 到 3990 的账户中记账金额的总和。我使用以下公式：SUMIFS(Amount, Account, ">=3199"; Account; "<=3990")，这个公式非常有效。

但是，对于销售成本明细项目，我有几个帐户间隔。我想要帐户 7210-7299 和 7336-7394 的总和，等等。如果我使用 SUMIFS(Amount, Account, ">=7210", Account, "<=7299", Account, ">=7336", Account, "<=7394)，它将返回零，因为这些是重叠的标准。我当然可以将每个帐户间隔放在单独的 SUMIF 公式中。但是有没有更简单的方法可以做到这一点？有没有办法在同一个公式中单独/隔离地处理每个间隔？

答案1

简单SUMPRODUCT的多个标准解决了这个问题：

在此处输入图片描述

  • 单元格中的公式C48：

    =SUMPRODUCT(((B$33:B$47>=7210)*(B$33:B$47<=7299)+(B$33:B$47>=7336)*(B$33:B$47<=7394))*(C$33:C$47))

注意：

  • 同一列的多个条件需要+签名。

  • 根据需要调整公式中的单元格引用。

答案2

根据您的帐户结构，这里有一些可能缩短公式的技巧：

使用通配符：...帐户，“72*”...

使用数组（带总和包装）：sum(sumifs(..., Account, {"72*", "73*"})

希望这能给你一些想法。

- - 编辑 - -

此外，您可以创建一个具有帐户分组的辅助列，然后使用该辅助列进行 sumif 操作。

--- 编辑 2 --- 刚刚看到你的标题没有多个 sumif，所以将其移到这里供其他人查看

使用多个 sumif：sumifs(...)+sumifs(...)或sum(...)-sumifs(...)，等等。

答案3

将上限和下限对放入一对硬编码数组中，并对 SUMIFS 进行求和。

 =SUM(SUMIFS(B:B, A:A, ">="&{7210,7336}, A:A, "<="&{7299,7394}))
                             ^^^^ ^^^^              ^^^^ ^^^^
                         lower boundaries        upper boundaries

像这样添加另一组（例如 8000-8200），

 =SUM(SUMIFS(B:B, A:A, ">="&{7210,7336,8000}, A:A, "<="&{7299,7394,8200}))

答案4

公式

=SUMPRODUCT(SUMIF(Account_Number, okAccounts, Value))

在哪里

  • Account_Number是帐号列表
  • okAccounts是一个包含你想要包含在总数中的每个帐号的范围
  • Value是对应于的值的列表Account_Number

怎么运行的

由于okAccounts是一个范围，因此 SUMIF 会针对该范围内的每个单元格进行求值。它会返回 中每个单元格的值数组okAccounts。SUMPRODUCT()接受数组作为输入，并返回所有数组值的总和。如果您在公式上使用“求值公式”工具，就会看到这一点。

SUMPRODUCT SUMIF

这种方法的唯一缺点是您需要枚举每个有效的帐号，但我认为这比构建复杂的有效范围列表更容易。

我用了公式Chop编写此示例公式并生成此屏幕截图。（完整披露：我编写了 FormulaChop）。

编辑

我仔细思考了一下，发现如果有一个公式可以枚举某个范围内的所有值就好了。所以我写了一个：

=IF(Range_Start+(ROW() - ROW(First_Cell)) <= Range_End, Range_Start+(ROW() - ROW(First_Cell)), "")

在哪里

  • Range_Start是范围的开始（例如7210）
  • Range_End是范围的结束（例如7299）
  • First_Cell是枚举值列中第一个单元格的锁定地址

枚举值

相关内容