Ermitteln des Durchschnittswerts der Daten in Excel 2021 für die ähnlichen Daten der ersten Spalte

Question

Ich habe riesige Datenmengen und das folgende Bild ist ein Teil davon.

Die erste Spalte ist immer eine natürliche Zahl (nicht unbedingt fortlaufend) und für die Zeilen, die die gleichen Daten in der ersten Spalte haben, möchte ich den Durchschnitt der Daten der nächsten Spalten ermitteln. Beispielsweise sind die ersten drei Zellen in der ersten Spalte 142 und ich möchte den Durchschnitt der nächsten drei Zellen der nächsten Spalten ermitteln. Da ich viele Daten habe, möchte ich diesen Prozess automatisieren. Ich habe die Formel „AVERAGEIF“ ausprobiert, aber ohne Ergebnis. Können Sie mir bitte helfen?

Answer 1

Da Sie Ihre Daten nur als Bild bereitgestellt haben, habe ich einige zufällige Daten im folgenden Bereich erstellt A1:E17:

A	B	C	D	E
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

Nun setzt man beispielsweise in Zelle G2und H2jeweils folgende Formel ein, die AVERAGEIF()Formel kann dann für die weiteren Spalten wiederholt werden:

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

Nun noch eine zusätzliche Erklärung: Die UNIQUE()Funktion kann verwendet werden, um die eindeutigen IDs zu extrahieren. Diese Funktion gibt ein Array zurück, das dann für die Funktion verwendet werden kann, AVERAGEIF()sodass es automatisch überläuft. Die SORT()Funktion wird verwendet, um die ID-Werte in aufsteigender Reihenfolge zu sortieren. Für die AVERAGEIF()Funktion geben Sie zuerst den Bereich an, in dem Sie die ID nachschlagen möchten, z. B. in Ihrer Spalte A. Als Nächstes geben Sie die Kriterien an, also Ihr Array, das wir mit erhalten haben, UNIQUE()und zuletzt geben Sie die Spalte an, über die Sie den Durchschnitt berechnen möchten.

Die Ausgabe sieht wie folgt aus:

AUSWEIS	Durchschnitt.
137	0,41
138	0,57
139	0,17
140	0,55
141	0,61
142	0,42

---

Erweiterung für den Zugriff auf alle Durchschnittswerte als ein Array und mit allen Formeln in einer Zelle:

Je nach Anwendungsfall möchten Sie möglicherweise auf alle Durchschnittswerte als ein Array zugreifen. Um dies zu erreichen, können Sie Ihre AVERAGEIF()Funktionen in ein einschließen HSTACK(). Auf diese Weise läuft die Formel aufgrund der UNIQUE()Funktion nach unten und aufgrund der Funktion nach rechts HSTACK(). Auf diese Weise haben Sie auch alle Formeln in einer einzigen Zelle, was manchmal auch einfacher zu verwalten sein kann:

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

Vorausgesetzt, die Formel steht noch in Zelle H2, können Sie wie folgt auf das komplette Array zugreifen:

=SUM(H2#)

---

Erweiterung basierend auf Kommentar zur Einbeziehung des Dynamikbereichs:

Um dies zu erreichen, könnten Sie eine LET()Anweisung wie folgt verwenden:

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

Zuerst geben Sie Ihre „letzte“ Spalte mit Daten an. Die Anzahl der Zeilen wird automatisch basierend auf der Spalte berechnet. ASie müssen daher sicherstellen, dass Sie keine Daten unterhalb der Daten haben, die Sie verwenden möchten. Alle anderen Aspekte ähneln denen, die wir zuvor hatten, sind jedoch in eine LET()Anweisung verpackt, die Ihnen einen dynamischen Zugriff auf den Datenbereich ermöglicht, INDEX()da dieser Zwischenergebnisse speichern kann.

Mit diesem Ansatz erreichen Sie, was Sie gefordert haben. Allerdings ist er nicht unbedingt sehr gut auf viele Spalten skalierbar, da Sie die Formel derzeit für jede Spalte wiederholen müssen. Theoretisch sollte es möglich sein, auch diese Dimension dynamischer zu gestalten, aber das geht über den Rahmen dieser Erweiterung hier hinaus.

Answer 2

In F2:=AVERAGE(IF($A$2:$A$17=$A2,B$2:B$17))

In G2:=AVERAGE(IF($A$2:$A$17=$A2,C$2:C$17))

Im zweiten Halbjahr:=AVERAGE(IF($A$2:$A$17=$A2,D$2:D$17))

In I2:=AVERAGE(IF($A$2:$A$17=$A2,E$2:E$17))

Hinweis: Dies ist eine Matrixformel. In früheren Excel-Versionen müssen Sie drücken, CTRL+SHIFT+ENTERdamit die Formel funktioniert. In der aktuellen Excel-Version können Sie einfach drücken Enter.