Excel 平均值和 if 数组 - 简化输入

Question

我有一个跨多个公司和类别汇总的数据集。我需要将不同的公司汇总在一起并计算各个类别的平均值。我知道解决方案是使用平均值数组公式，但我想知道是否有办法让事情变得更快一些。这是我正在处理的一个（非常简化的）示例。

假设我需要分别对 A 和 B 公司的每个分类进行平均。我知道公式如下：

{=AVERAGE(如果(($A$5:$A$13={"A","B"})*($B$5:$B$13="H"),$C$5:$C$13))}

但随后我需要针对每个分类运行该程序（实际上可能会有多个公司/分类组合需要更改），因此可能需要更新 5-10 个公式。然后，我需要汇总不同的公司，例如 B、C 和 D。我知道这看起来像：

{=AVERAGE(IF(($A$5:$A$13={"B","C","D"}) * ($B$5:$B$13="H"),$C$5:$C$13))} {=AVERAGE(IF(($A$5:$A$13={"B","C","D"})*($B$5:$B$13="N"),$C$5:$C$13))}, 等等

我的问题是，有什么方法可以将 {“B”、“C”、“D”} 部分放入另一个单元格，以便我可以快速更改，然后引用该单元格，这样就可以避免每次手动更改所有内容？

Answer 1

但是，您可能需要使用简单的用户定义函数将逗号分隔的公司列表拆分为数组。在此示例中，A1:A5 列出公司。B1:B5 作为“H”，C1:C5 根据您的公式计算平均值。F2 返回平均值，公司列表在 H1 中提及（逗号分隔）

按ALT+F11访问 VBA 编辑器并插入模块从插入菜单中。打开模块并将以下代码粘贴到其中。

Public Function MyArray(strTest As String)
   MyArray = Split(strTest, ",")
End Function

现在在 F2 中您有使用此 MyArray 函数的公式。

=AVERAGE(IF((A1:A5=MyArray(H1))*(B1:B5="H"),C1:C5))

按CTRL+ SHIFT+ENTER创建数组公式，您将获得所需的预期结果。

Answer 2

您可以将所需的查找放在连续的范围内并使用：

=AVERAGE(AVERAGEIFS(C:C,B:B,"H",A:A,E1:INDEX(E:E,MATCH("zzz",E:E))))

退出编辑模式时使用 Ctrl-Shift-Enter 而不是 Enter。如果操作正确，Excel 会将{}公式置于“Around”位置。

然后，您可以从 E1 开始放置所需的查找。