求Excel 2021中相似第一列資料的平均值

Question 1

鑑於您確實僅以圖片形式提供數據，我在範圍內創建了一些隨機數據，如下所示A1:E17：

A	乙	C	D	乙
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 的範圍，例如在您的 column 中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)
)

假設公式仍在 cell 中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()語句中，它允許您動態存取資料範圍 via，INDEX()因為它可以儲存中間結果。

這種方法將達到您的要求。然而，需要注意的是，它不一定能夠很好地擴展到許多列，因為現在它需要您對每一列重複公式。從理論上講，應該可以使這個維度更加動態，但它超出了這裡擴展的範圍。

Answer