excel - 如何创建动态公式来查找给定向量的一组值的平均值

Question

我正在尝试创建一个公式，该公式根据关联的向量为我提供给定数据集中最后 12 个条目的平均值。

让我们举个例子：

我在 F2、G2、H2 和 I2 列中分别有 Company1、Company2 和 Company3。然后从第 3 行到第 33 行，我有从 2016 年 5 月开始的月份日期。

Date    Company1          Company2       Company3
May-16                   2,453,845  
Jun-16                   13,099,823     
Jul-16                   14,159,037     
Aug-16   38,589,050      8,866,101  
Sep-16   63,290,285      13,242,522     
Oct-16   94,005,364      14,841,793     
Nov-16   123,774,792     7,903,600      41,489,883 
Dec-16   93,355,037      12,449,604     69,117,105 
Jan-17   47,869,982      13,830,712     83,913,764 
Feb-17   77,109,905      10,361,555     68,176,643

目标是创建一个公式，当我将其向下拖动时，它可以正确计算给定公司最后 12 个值的平均值。

因此，例如，我会在表“B2：C5”中说：

Company1     76,856,345 
Company2     11,120,859 
Company3     65,674,349 

而且，如果将新的 Company4 添加到列表中，那么我只需将其拖到公式中，即可计算 Company4 过去 12 个月的平均值。

到目前为止，我想出了这个公式：

=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(G:G),ROW(G:G)),ROW(INDIRECT("1:"&MIN(12,COUNT(G:G))))),ROW(G:G),G:G ))

此公式正确计算给定列的平均值，仅考虑最后 12 个值。最后一步是提出一个包含所有列的公式，然后计算给定公司的平均值。

谢谢！

标签： excel

Answer 1

我建议您使用命名范围来定义 G:I 列中的数据。添加公司时，只需修改命名范围的规格。我使用了名称Target。当然，如果您愿意，可以将其替换为 $G:$I，但我宁愿建议减少范围内的行数，这样在命名时更易于管理。使用下面的公式将 Target 第一行中的公司名称提取到平均值表的第一列中。这是为了确保两个位置的名称拼写相同。

=INDEX(Target,1,ROW()-2)

数字 2 表示包含公式的行上方的行数。它是从单元格 M3 复制到此处的。在那里，ROW()-2 创建数字 1，并在复制公式时按顺序计数。现在我的单元格 N3 中有下面的公式并复制下来。

=SUM(INDEX(Target,0,MATCH($M3,INDEX(Target,1,0),0)))

该公式只是简单地将 G、H 和 I 列相加在 3 个连续的行中。在最后一步中，我将上面建立的范围定义（即不包括 SUM() 函数）插入到您现有的公式中。

=AVERAGE(LOOKUP(LARGE(IF(ISNUMBER(INDEX(Target,0,MATCH($M3,INDEX(Target,1,0),0))),ROW(INDEX(Target,0,MATCH($M3,INDEX(Target,1,0),0)))),ROW(INDIRECT("1:"&MIN(12,COUNT(INDEX(Target,0,MATCH($M3,INDEX(Target,1,0),0))))))),ROW(INDEX(Target,0,MATCH($M3,INDEX(Target,1,0),0))),INDEX(Target,0,MATCH($M3,INDEX(Target,1,0),0))))