☰

如何正确计算单独工作表中范围内的多个条件？

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2010
如何正确计算单独工作表中范围内的多个条件？

我在工作表中有一个主数据表（工作表名称为“数据”；表格范围称为“MasterData”）。其中包括有关会议参与者的数据（姓名、国籍等）以及每个人是否参加个人活动。

我正在尝试在同一个 Excel 工作簿中制作一个仪表板类型工作表（工作表名称“仪表板”），以汇总不同类型的数据，以便进行良好的概述和规划。

数据表：

Name    Country Breakfast   Dinner  Concert Happy Hour  Field Trip
Sam USA Yes Yes Yes Yes Yes
John    USA No  No  No  Yes No
Matt    Canada  Yes Yes Yes No  Yes
Phil    Canada  Yes Yes Yes No  Yes
Marco   Mexico  No  Yes No  No  No
Julio   Mexico  Yes Yes No  Yes No

仪表板事件摘要表：

Event   USA Canada  Mexico
Breakfast           
Dinner          
Concert         
Happy Hour          
Field Trip

举个例子，我在仪表板工作表中创建了一个表（表名为“事件”），其中我想汇总每个国家参加每个活动的人数，并从“数据”工作表的“主数据”表中提取数据。

我尝试使用 COUNTIFS 来计算工作表“数据”的“MasterData”表中的国家/地区是否等于美国并且特定事件等于“是”。但是，该公式似乎无法正确运行。见下文：

=COUNTIFS(MasterData,MasterData[[#Headers],[Country]]="USA",MasterData[[#All],[Breakfast]],"Yes")

我使用的方法正确吗COUNTIFS？我在 Excel 中输入的公式有什么问题？我该如何重写这个公式才能得到我想要的结果——创建一个总结主数据各方面的仪表板？

答案1

我刚刚尝试在 OP 中重现您的示例数据，并找到了一个使用COUNTIFS()函数的解决方案，但是它与您尝试过的那个解决方案并不相同，因为它使用INDEX()&函数根据类型MATCH()返回一系列，然后将与一起应用。YesEventsCOUNTIFS()Countries

在此处输入图片描述

• 单元格中使用的公式C3

=COUNTIFS(
 INDEX(MasterData[[Breakfast]:[Field Trip]],,
 MATCH($B3,MasterData[[#Headers],[Breakfast]:[Field Trip]],0)),"Yes",
 MasterData[Country],C$2)
  • INDEX(MasterData[[Breakfast]:[Field Trip]],,MATCH($B3,MasterData[[#Headers],[Breakfast]:[Field Trip]],0))--> 注意，公式将返回一系列值，即Yes基于No的类型Events。
  • Yes然后将上述公式用作标准范围，以与工作表Country中的范围和国家标准一起检查标准Dashboard。
  • 标准范围1-- 上述公式和criteria1 "Yes"
  • 标准范围2-MasterData[Country]和criteria2 C$2

还有更好的选择，那就是使用SUMPRODUCT()在这种情况下发挥作用。

在此处输入图片描述

• 单元格中使用的公式C3

=SUMPRODUCT((MasterData[Country]=C$2)*
 (MasterData[[#Headers],[Breakfast]:[Field Trip]]=$B3)*
 (MasterData[[Breakfast]:[Field Trip]]="Yes"))

答案2

你的countifs公式需要调整。

  1. 语法不同于sumfis
  2. 表命名有点偏离，您需要引用表数据而不是[#Header]，我也不认为您需要[#All]引用

它应该看起来更像这样：=COUNTIFS(MasterData[Breakfast],"Yes",MasterData[Country],"USA")

由于您的数据格式，您需要为仪表板中的每个单元格创建公式。或者，如果您删除NoMasterData 中的所有 '，则可以使用数据透视表，如下所示：

在此处输入图片描述

还有其他方法可以解决这个问题，但这会让你重新开始。

相关内容