计算最后 5 个值的平均值，同时忽略空白值并根据名称条件进行匹配

Question

我正在尝试生成一个 Excel 公式，对 G 列中的最后 5 个数字值求平均值并与 B 列中的名称匹配，同时忽略任何空白单元格来计算具有数字的单元格的最后 5 个值。

例如，数据格式如下：

John Smith     100
Adam Pratt      95
Michael Keen    86
John Smith      56
John Smith      78
John Smith      92
John Smith      88

我希望能够计算“John Smith”的最后一个值的平均值。

任何帮助都值得感激！

Answer 1

假设没有版本限制，如您的标签所示，您可以使用：

=AVERAGE(TAKE(FILTER(B1:B100,A1:A100="John Smith"),-5))

• FILTER仅返回 Col B 中与 John Smith 匹配的项目
• TAKE 使用-5rows 参数返回最后五项
• AVERAGE=> 这五项的平均值

由于您没有指明如果项目少于五项时您希望发生什么，因此此公式将对现有项目取平均值。如果您希望发生其他情况，则需要更改公式。

如果您想动态创建一个包含每个名称及其相应平均值的输出表（最多最后五个元素），那么您可以在单个单元格中使用此公式，结果将溢出到输出范围：

=LET(
    x, UNIQUE(
        FILTER($A$1:$A$100, A1:$A$100 <> "")
    ),
    y, BYROW(
        x,
        LAMBDA(arr,
            AVERAGE(
                TAKE(
                    FILTER(
                        $B$1:$B$100,
                        $A$1:$A$100 = arr
                    ),
                    -5
                )
            )
        )
    ),
    HSTACK(x, y)
)

Answer 2

根据您的描述，我在A列和B列创建了类似的样本。

我建议您使用公式来获取 A 列中“John Smith”的第一个单元格。

="A"&LARGE(IF($A$1:$A$8=$A$8,IF(ISNUMBER($B$1:$B$8),ROW($A$1:$A$8)-ROW($A$1)+1)),5)

B列同样如此。

="B"&LARGE(IF($A$1:$A$8=$A$8,IF(ISNUMBER($B$1:$B$8),ROW($A$1:$A$8)-ROW($A$1)+1)),5)

然后平均值可以用=SUMIF(INDIRECT(E1):A8,A8,INDIRECT(E2):B8)/5公式来计算。

Answer 3

• 我假设你的数据在 中A2:B8。

• 您可以使用适用于所有 Excel 版本的此公式。

  =IF(COUNTIFS(A2:A8,"John Smith",B2:B8,"<>")>=5,AVERAGE(IF((A2:A8="John Smith")*(B2:B8<>""),LARGE(B2:B8*(A2:A8="John Smith"),{1,2,3,4,5}))), "")
  

• 你得到答案了82.80。

注意：根据需要调整单元格引用。