☰

如果在水平范围内找到条件，则对行中的值进行求和

tag-icon • tag-icon microsoft-excel worksheet-function
如果在水平范围内找到条件，则对行中的值进行求和

我有一个电子表格，其中包含多条包含country/value对的行记录（A2：C11）和行记录表示水平regions列出那些（countries14:16)：

在此处输入图片描述

我想要做的是汇总每个地区的所有国家价值：

在此处输入图片描述

从概念上来说，我知道我需要做什么，例如对于北美（B19)：

  • 筛选B2：B11范围乙在行中找到14
  • 总结来自C2：11对应过滤B2：B11范围

在这种情况下，这将10 (C2) + 11 (C5) + 12 (C10) = 33

我认为我需要的功能是查找和引用函数，但我只是不知道从哪里开始：

有人可以给我一个 B19 的例子（或者至少是我应该使用的函数列表）吗？

PS：我不想使用 VBA，只想使用公式

答案1

尝试B19复制此公式

=SUMPRODUCT(SUMIF(B$2:B$11,INDEX(B$14:C$16,MATCH(A19,A$14:A$16,0),0),C$2:C$11))

  • MATCH给出表二中的正确行
  • 然后INDEX给出该行中的所有国家。
  • 然后，该INDEX/MATCH部分形成了的条件参数SUMIF，并且由于条件是值的范围，因此SUMIF返回一个数组（每个国家/地区的总和）......因此SUMPRODUCT用于对数组求和，从而避免“数组输入”

答案2

好的，由于每个地区有多个国家，因此您需要一个相当长的公式。我建议同时使用SUMIF()和VLOOKUP()。

=SUMIF($B$2:$B$11,VLOOKUP(A19,$A$14:$C$16,2,FALSE),$C$2:$C$11)+
 SUMIF($B$2:$B$11,VLOOKUP(A19,$A$14:$C$16,3,FALSE),$C$2:$C$11)

第一个SUMIF()是只计算加拿大的总和，第二个是只计算美国的总和。将两者相加即可得出该地区的总和。

VLOOKUP()从地区名称中检索国家名称，您会注意到 中的不同之处VLOOKUP()，其中第一个是2，第二个是3（就在 false 之前）。当然，您可以替换FALSE以0输出相同的结果。

另外，如果您想要一个更短的数组公式，那么这个也可以使用：

=SUM(SUMIF($B$2:$B$11,IF(A19=$A$14:$A$16,$B$14:$C$16),$C$2:$C$11))

但是，由于这是一个数组公式，因此您必须使用Ctrl++ShiftEnter

答案3

您可以尝试以下操作：

=SUMPRODUCT($C$2:$C$11,--(IFERROR(MATCH($B$2:$B$11,$B14:$C14,0),0)<>0))

在单元格 B19 处将其向下拖动。如果每个地区有超过 2 个国家，则需要$B14:$C14相应地扩展。

注意：这是一个数组公式，因此您将其复制到单元格并按 Ctrl+Shift+Enter 即可使其起作用。

  • IFERROR(MATCH(...,0))返回一个数组，其元素显示第一个表中每个条目在第二个表中相应行中的位置。
  • 逻辑条件将所有零转换为FALSE，将所有其他数字转换为TRUE
  • 该--部分将TRUE和FALSE值分别转换为1和0
  • SUMPRODUCT对两个数组进行操作：第一个是原始范围，第二个是0/1包含我们之前输入的条件的数组。

这里有一篇很好的文章，解释了这些公式背后的逻辑： http://www.excelhero.com/blog/2010/01/the-venerable-sumproduct.html

相关内容