查找 Excel 2021 中相似第一列数据的平均值

Question 1

鉴于您确实仅以图片形式提供了数据，因此我在以下范围内创建了一些随机数据A1:E17：

A	乙	C	德	埃
142	0.02	0.19	0.83	0.14
142	0.65	0.61	0.38	0.82
142	0.60	0.34	0.36	0.37
141	0.98	0.95	0.23	0.97
141	0.83	0.24	0.50	0.67
141	0.02	0.14	0.33	0.23
140	0.33	0.14	0.85	0.64
140	0.78	0.09	0.17	0.06
139	0.08	0.70	0.26	0.26
139	0.25	0.16	0.35	0.67
138	0.52	0.44	0.18	0.44
138	0.21	0.93	0.04	0.23
138	0.66	0.33	0.72	0.39
138	0.91	0.91	0.51	0.30
137	0.39	0.60	0.28	0.46
137	0.43	0.81	0.41	0.14

现在，您分别输入例如单元格G2和H2以下公式；AVERAGEIF()可以对其他列重复该公式：

=SORT(UNIQUE($A$2:$A$17))
=AVERAGEIF($A$2:$A$17,$G$2#,B2:B17)

现在做一些额外的解释；该UNIQUE()函数可用于提取唯一 ID。此函数返回一个数组，然后可用于AVERAGEIF()自动溢出。该SORT()函数用于按升序排列 ID 值。对于该AVERAGEIF()函数，首先指定要查找 ID 的范围，例如，在您的列中A。接下来指定条件，即我们获得的数组UNIQUE()，最后指定要对其求平均值的列。

输出如下：

ID	平均
137	0.41
138	0.57
139	0.17
140	0.55
141	0.61
142	0.42

扩展以将所有平均值作为一个数组进行访问，并将所有公式放在一个单元格中：

根据用例，您可能希望将所有平均值作为一个数组来访问。为此，您可以将AVERAGEIF()函数包装在中HSTACK()。这样，公式会因为UNIQUE()函数而向下溢出，也会因为函数而向右溢出HSTACK()。这样，您还可以将所有公式放在一个单元格中，有时可能更容易维护：

=HSTACK(
AVERAGEIF($A$2:$A$17,$G$2#,B2:B17),
AVERAGEIF($A$2:$A$17,$G$2#,C2:C17),
AVERAGEIF($A$2:$A$17,$G$2#,D2:D17),
AVERAGEIF($A$2:$A$17,$G$2#,E2:E17)
)

假设公式仍在单元格中H2，您可以按如下方式访问完整数组：

=SUM(H2#)

根据评论进行扩展以包含动态范围：

为了实现这一点，您可以使用LET()如下语句：

=LET(
    lastCol, "E",
    data, INDEX(A:A,2):INDEX(INDIRECT(CONCATENATE(lastCol, ":", lastCol)),
    MAX(IF(A:A<>"", ROW(A:A)))),
    id, SORT(UNIQUE(INDEX(data,2,1):INDEX(data,ROWS(data),1))),
    b, AVERAGEIF(INDEX(data,,1), id, INDEX(data,,2)),
    c, AVERAGEIF(INDEX(data,,1), id, INDEX(data,,3)),
    d, AVERAGEIF(INDEX(data,,1), id, INDEX(data,,4)),
    e, AVERAGEIF(INDEX(data,,1), id, INDEX(data,,5)),
    HSTACK(id,b,c,d,e)
)

首先，指定包含数据的“最后一列”。行数是根据列自动计算的A，因此，您需要确保在要使用的数据下方没有任何数据。所有其他方面都与我们之前所做的类似，只是包装在一个LET()语句中，它允许您通过动态访问数据范围，INDEX()因为它可以存储中间结果。

这种方法可以实现您的要求。但需要注意的是，它不一定能很好地扩展到许多列，因为现在它要求您对每一列重复公式。理论上，应该可以使这个维度更加动态，但这超出了此处扩展的范围。

Answer