☰

Excel 2013 AVERAGEIFS 使用 COLUMN() 引用

tag-icon • tag-icon microsoft-excel worksheet-function microsoft-excel-2013
Excel 2013 AVERAGEIFS 使用 COLUMN() 引用

我正在尝试在工作表中使用 AVERAGEIF 公式来计算基于当前月份的移动平均值。我有以下数据：

         A            B      C      D      E      F         G      H       I      J      K      L      M     N
      1 2017         Jan    Feb    Mar    Apr    May       Jun    Jul     Aug    Sep    Oct    Nov    Dec   Ave
      2 Cost/Req.    $430  $432     $365   $391  $1250        $-     $-      $-      $-      $-      $-      $-    ??

我想在 N2 中输入今天（5 月）1 月至 4 月的平均值以及 6 月 1 月至 5 月的平均值，依此类推。我尝试使用 COLUMN() 函数，但它不起作用。

AVERAGEIF(B2:M2,"COLUMN()<MONTH(NOW()")

任何想法？

谢谢！

答案1

我们来从几个角度分别来回答一下这个问题：

总结

=AVERAGE(INDIRECT("A2:"&ADDRESS(2,MONTH(NOW())-1)))

你的公式为什么不起作用？

该AVERAGEIF函数接受标准参数，但它们似乎不能做任何非常复杂的事。我还没有找到它们语法的真正解释，但在线文档不要做比 更深刻的事情">32"，而且你正在尝试使用整个公式。我还没有看到任何证据表明这种方法即使在理论上也是可行的。（我很想证明这是错的！）AVERAGEIF（或AVERAGEIFS）不是你想要的函数。

计算范围平均值

计算一系列值的平均数很容易。你显然已经知道如何做，甚至可以问出你的问题！假设值在 A2:L2 中（月份标题在顶部）：

=AVERAGE(A2:L2)

但等一下，你只想平均到并且不包括当前月份。所以我们需要一种方法来计算基本平均公式的范围。5 月份是A2:D2。我们如何计算呢？

计算范围的公式

好的，所以新的问题是如何AVERAGE从当前月份获取一个范围（函数的引用）。Lookup & Reference函数会在这里帮助你。可能有更简单的方法可以做到这一点，但我能想到的最好的方法是使用INDIRECT返回引用/范围，如果我们给它适当的文本，这是一个更简单的问题。现在我们需要的就是想出文本“A2:D2”（代表五月）。A2 很简单：应该是一个常数。可能有一种方法可以将 4（代表四月，您想要的最后一个月份）转换为 D（您想要的列），但您也可以使用函数ADDRESS计算D2行/列号。请注意，它从正常情况下“向后”思考。所以 A2 将输入为ADDRESS(2,1)。在你的例子中，你知道行 2，并且可以使用你想要的函数计算MONTH你想要的列，然后减去 1 回到上个月。所以我们可以用得到“D2” ADDRESS(2,MONTH(NOW())-1)。然后就是把它们放在一起的问题了。使用“与”符号将 A2 和 D2 结合起来。

="A2:"&(ADDRESS(2,MONTH(NOW())-1))

综合起来

现在我们有了要求平均值的区域的文本表示。该INDIRECT函数会将其转换为函数可以使用的实际范围AVERAGE。然后，最终函数会将其全部求平均值：

=AVERAGE(INDIRECT("A2:"&ADDRESS(2,MONTH(NOW())-1)))

一些注释

如果您的正确范围确实是 U192:AF192，请将公式中的 2 替换为 192，并将 20 添加到行参数（因为 U 位于 A 右侧 20 行）。

我还附上了各个中间步骤计算的屏幕截图。最终公式是第 8 行的公式。我还采取了下一步措施，只对过去几个月而不是全年进行平均，在第 12 行使用相同的想法。

请注意，整个方法将使你的平均值取决于电子表格的打开时间。如果你打开它下一个2 月份的平均值仅涵盖两个月，而不是整个 2017 年。1 月份将完全停滞。您可能需要想出一个更智能的公式来处理月份参数。

在此处输入图片描述

答案2

最简单的方法是AVERAGE在语句中创建一个公式IF，检查月份是否有值输入。将此公式放在 中U3，然后将其拖到行上AF3。

=IF(ISBLANK(U2), "", AVERAGE($U$2:U2))

此公式将检查单元格是否已填充。如果已填充TRUE，它将运行该函数。平均值函数具有锚定第一列的绝对引用。

当您将金额添加到 中的月份时row 2，公式将更新移动平均值以包含新数据。结果将是 1 月至该月的平均值。

在此处输入图片描述

答案3

这个问题很难理解。我的理解是

今天（五月），我想知道一月至四月的平均值。六月，我想知道一月至五月的平均值，等等。

这很容易处理为具有一个固定（锚定）端和一个浮动端的范围的平均值。在单元格中 V193（即在Feb列中，在成本/需求值 ($432) 下），输入

=AVERAGE($U192:U192)

在 中求值时V193，这相当于AVERAGE(U192:U192)，相当于AVERAGE(U192)，相当于U192，即 1 月份的值 430 美元。但现在我希望您将其拖拽/填充到右侧，即 Column  AF。在 中W193，这将更改为=AVERAGE($U192:V192)，因为$U192（ 的第一个参数AVERAGE）锚定到 Column  U，但U192（ 的第二个参数AVERAGE）浮动到当前列左侧的一列。依此类推，直到AF193，其中包含=AVERAGE($U192:AE192)。 电子表格

相关内容