☰

Excel：使用同一列单元格中某列的计算平均值

tag-icon • tag-icon microsoft-excel worksheet-function
Excel：使用同一列单元格中某列的计算平均值

我有以下数据：

    Column B    Column C
11  21 Oct      0.87%
12  22 Oct      1.38%
13  23 Oct      0.04%
14  24 Oct      0%
15  25 Oct      0%

对于每个日期都是如此，所有尚未发生的日期均为 0%。我还有一个单元格 (C2)，其中包含以下公式：

{=AVERAGE( IF(B5:B373 < TODAY(); C5:C373))}

计算 C 列中所有值的平均值，包括当天的值（B 列），因此所有 0% 值均不包含在计算中。假设今天是 10 月 23 日，则 C2 的值将是 0.76%。

每天，我都会在相应的单元格中输入该日期的百分比，因此最终所有 0% 的单元格都将被适当的值替换。

我不想使用 0%，而是希望根据迄今为止的平均百分比运行一些预测计算。因此，基本上，我希望所有现在包含 0% 的单元格（因为我尚未输入实际值）都包含 C2 中的值，使其看起来像这样：

    Column B    Column C
11  21 Oct      0.87%  < literal value
12  22 Oct      1.38%  < literal value
13  23 Oct      0.04%  < literal value
14  24 Oct      0.76%  < =C2
15  25 Oct      0,76%  < =C2

但是，这不起作用，因为它会在单元格中的公式和 C2 中的公式之间创建循环引用。

所以我需要一个使用范围的 C2 公式截至今天。我现在的公式使用整个范围然后使用 IF 排除未来的值，这意味着我无法在列中的其他单元格中使用结果。如果我有一个公式将范围限制为截至今天的所有单元格，那么我就能够在该范围之外的所有单元格中使用结果。我一直在尝试使用 INDEX 和 MATCH，但搞不懂。我尝试做的事情是否可行？

答案1

您可以使用 MATCH 来查找今天的日期在日期列中的第几位。

=MATCH(TODAY(),B5:B373,0)

您可以将该数字与 OFFSET 结合使用，从 C 列中获取仅包含今天的值的范围。

=OFFSET(C5,0,0,MATCH(TODAY(),B5:B373,0))

最后，您将获得该范围内值的平均值。在单元格 C2 中输入以下内容：

=AVERAGE(OFFSET(C5,0,0,MATCH(TODAY(),B5:B373,0)))

您现在可以输入=C$2每个未来日期的值，而不会产生循环引用。

相关内容