☰

Excel 2021で類似の最初の列のデータの平均を見つける

tag-icon • tag-icon microsoft-excel
Excel 2021で類似の最初の列のデータの平均を見つける

私は膨大なデータを持っており、次の画像はそのデータの一部です。 [1]: https://i.stack.imgur.com/l5M0S.png

最初の列は常に自然数 (連続している必要はありません) であり、最初の列のデータが同じ行については、次の列のそれぞれのデータの平均を求めます。たとえば、最初の列の最初の 3 つのセルが 142 で、次の各列の次の 3 つのセルの平均を求めます。大量のデータがあるため、このプロセスを自動化したいので、「AVERAGEIF」数式を試しましたが、結果は得られませんでした。助けていただけませんか?

答え1

データを画像のみで提供されたことを考慮して、次の範囲内でランダムなデータを作成しましたA1:E17。

あ B Ｃ だ え
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

すべての平均に 1 つの配列としてアクセスし、すべての数式を 1 つのセルに含める拡張機能:

ユースケースによっては、すべての平均値を 1 つの配列としてアクセスしたい場合があります。これを実現するには、AVERAGEIF()関数を で囲みますHSTACK()。その場合、数式は関数によって下にUNIQUE()、関数によって右に広がりHSTACK()ます。その場合、すべての数式が 1 つのセルに収まるため、メンテナンスが簡単になることもあります。

=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()。

このアプローチは、あなたが求めていたものを実現します。ただし、注意点として、現時点では各列に対して数式を繰り返す必要があるため、多くの列に必ずしもうまく拡張できるわけではありません。理論的には、このディメンションもより動的にできるはずですが、それはここでの拡張機能の範囲を超えています。

答え2

F2の場合:=AVERAGE(IF($A$2:$A$17=$A2,B$2:B$17))

G2の場合:=AVERAGE(IF($A$2:$A$17=$A2,C$2:C$17))

H2:=AVERAGE(IF($A$2:$A$17=$A2,D$2:D$17))

I2の場合:=AVERAGE(IF($A$2:$A$17=$A2,E$2:E$17))

ここに画像の説明を入力してください

注: これは配列数式です。以前のバージョンの Excel では、CTRL+SHIFT+ENTER数式を機能させるには を押す必要があります。現在のバージョンの Excel では、 を押すだけで済みますEnter。

関連情報