☰

不符合条件的单元格的 ITSUMIF 标准

tag-icon • tag-icon microsoft-excel worksheet-function
不符合条件的单元格的 ITSUMIF 标准

我有一个如下所示的 Excel 电子表格：

# |A           |B
-----------------------------
1 |Expenses    |Category
2 |$5          |Food
3 |$10         |Entertainment
4 |$10         |Haircut

我想要创建一个列出每个类别总数的部分。组织方式如下：

# |H            |I
-----------------------------
1 |Category     |Total Expenses
2 |Food         |
3 |Entertainment|
4 |Other        |

总结食品和娱乐类别非常容易。我使用 SUMIF 函数检查 B“类别”列的范围与 H“类别”列中的单元格，如果匹配，则将值相加。例如，“I2”的公式如下：

=SUMIF(B$2:B$999, H2, A$2:A$99)

但是，我在设置“其他”类别时遇到了很多困难。我希望它显示任何具有未显示在 H 列中的类别的值（例如“理发”）。我尝试执行以下操作：

=SUMIF(B$2:B$999, NOT(OR(H2,H3)), A$2:A$99)

但这似乎没有捕获任何单元格。最终，我希望 SUMIF 的条件部分能够捕获不包含在其他单元格范围内的值的单元格。

编辑：这是为不太懂电脑的客户准备的电子表格，因此任何解决方案最好都应尽量减少最终用户的负担（例如，不需要他们了解宏是可以的）。此外，完整的电子表格包含日期列，因此无法按类别排序。

答案1

SUMIFS允许您指定多个条件（在 Excel 2007 或更高版本中），以便您可以使用此公式

=SUMIFS(A:A,B:B,"<>"&H2,B:B,"<>"&H3)

这将对 A 列求和，其中对应的 B 列值不等于 H2和不等于 H3

如果你有更长的项目列表需要排除，那么这种方法将会产生一个很长的公式，所以另一种排除范围的方法是使用SUMPRODUCT如下方法

=SUMPRODUCT(ISNA(MATCH(B2:B100,H2:H5,0))+0,A2:A100)

当相应的 B 列值与 H2:H5 中的任何值都不匹配时，将对 A2:A100 求和

答案2

您需要的是一个Pivot Table。

选择表格中的一个单元格，例如A1。通过单击“插入”>“数据透视表”创建数据透视表。单击“确定”。

此时，您将在新工作表中创建一个。要计算所需的内容，请将表格右侧框中的文件列表中的Pivot Table拖放到，然后将其拖放到。CategoryRow LabelsExpenses∑ Values

编辑

正如您在问题中问到的，不需要Pivot Table在单独的工作表中创建。当数据透视表提示对话框时，您可以指定要创建数据透视表的位置，而不是单击“确定”。

此外，请记住，每当修改表的内容时都要刷新表，方法是右键单击数据透视表，然后单击“刷新”。

编辑2

对不起，我完全误读了你的问题，你能够计算类别的总和，所以你不需要数据透视表。你需要的是将其他类别相加，我的建议是：

为什么不尝试对所有未求和的类别求和，而是对 A1:A999 中的总数求和并减去 I 列中的所有求和值？

假设你的Other字段是 H4：

=SUM(澳元1:澳元999)-SUM(澳元2:澳元3)

答案3

一个简单的方法是按类别对数据进行排序并使用 SUBTOTAL。您可以重复使用 SUBTOTAL 函数，也可以（更简单的方法）突出显示已排序的 A 列和 B 列，然后使用数据功能区中大纲中的 Subtotal（我忘记了它在早期版本的 Excel 中位于哪个菜单下）。这样，只要类别发生变化，就会自动插入小计，包括总计，并设置大纲处理，以便您可以折叠或展开小计。

小计为非常对于这种事情来说非常方便。它支持多层总计，因此您可以按部门、按部门类别等进行单独的小计。

答案4

如何设置I4为=SUM(A$2:A$99) - SUM(I$2:I3)–– 列 中的所有数据 B减去您已在 列 中累积的（子）总数 I。如果您需要能够以最少的麻烦和麻烦添加（和/或删除）类别，请查看

=SUM(A$2:A$99) - SUM(OFFSET(I$2,0,0,ROW()-2,1))

函数ROW()会计算出它在哪一行（即哪一行是“其他”行）。将行 1和当前行减去 2；例如，如果您有八个求和类别，在单元格中 H2:H9，则Other在中H10，并且您想要求和I2:I9，即八（10-2）行。

相关内容