☰

在 Excel 中计算标识符列满足条件的数值的平均值

tag-icon • tag-icon microsoft-excel-2007
在 Excel 中计算标识符列满足条件的数值的平均值

在 Excel 2007 中，我有一张表格，其中 F、G、H、I 和 J 列中有数字。B 列中有一个标识符。

我需要 F、G、H、I 和 J 列中所有数字的平均值，其中 B 列中的标识符 = A14（即 A14 中的值）。

我尝试了以下操作：

=AVERAGEIFS('Hours-Backup'!F3:J1048576,'Hours-Backup'!B:B,'Group Dashboard'!A14)

这导致#VALUE! 错误。

最终，我希望让函数忽略所有非数字或空白的单元格。换句话说，我不希望它为了求平均值而假设空白单元格为 0。

我不明白问题是什么。

答案1

两个问题。（或者说，一个问题有两个脑袋。）  AVERAGEIFS帮助说：

  • 与 AVERAGEIF 函数中的范围和条件参数不同，在 AVERAGEIFS 中，每个条件范围必须与总和范围具有相同的大小和形状。

嗯，这是个拼写错误：“sum_range” 应该是“average_range”。但无论如何，这是你的问题：你的标准范围是 1×∞（一整列），而你的平均范围是 5×1048574。因此，你可以通过将其更改B:B为 来解决其中一个问题B3:B1048576，使其成为 1×1048574 范围。

B您可以通过将 Column 复制到 Columns C、D、E和F，然后将其用作您的criteria_range来解决第二个问题B3:F1048576。但这太疯狂了。合理的方法是将辅助列（假设）定义为当前行中VColumns 的平均值：FJ

  • 设置，V3​AVERAGE(F3:J3)
  • 并向下拖动。

然后通过平均平均值来获得条件平均值：

=AVERAGEIFS('Hours-Backup'!V3:V1048576,'Hours-Backup'!B3:B1048576,'Group Dashboard'!A14)
(changes)                  ^  ^                        ^  ^^^^^^^

答案2

我对这种情况的处理方法是使用几个辅助列和一些数组公式。希望我以与您的相同方式设置了此玩具表：

Excel 片段

在这里，我设置了A16单元格，以设置要保留的过滤行。B是要过滤的值的列，C到是G可能缺少值的列。我计算列中每行的值的数量H，并对列中存在的值求和I。单元格H2和I2对相应的“计数”和“总和”列求和，然后在中计算结果平均值K2。请注意，列和中的公式H对于I表格中的文本和错误值都非常稳健。

我不确定SUM(IF(...))数组公式中的构造的版本兼容性。它显然适用于 Excel 2010，可能也适用于 Excel 2007 和 Excel 2013。

相关内容