So verwenden sie subtotal mit averageif in excel

Sie können die folgende Formel verwenden, um die Funktionen ZWISCHENSUMME und MITTELWERT in Excel zu kombinieren:

 =AVERAGE(IF(SUBTOTAL(2,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),0)),IF( B2:B11 ="Guard", C2:C11 )))

Mit dieser speziellen Formel können Sie den Durchschnitt der Werte im Bereich C2:C11 berechnen, wobei der entsprechende Wert im Bereich B2:B11 gleich „Behalten“ ist, selbst nachdem dieser Zellbereich auf die eine oder andere Weise gefiltert wurde. noch einer.

Das folgende Beispiel zeigt, wie diese Formel in der Praxis angewendet wird.

Beispiel: Verwendung von SUBTOTAL mit AVERAGEIF in Excel

Angenommen, wir haben den folgenden Datensatz mit Informationen über verschiedene Basketballspieler:

Als Nächstes filtern wir die Daten, um nur die Zeilen anzuzeigen, in denen sich die Spieler der Western Conference befinden.

Markieren Sie dazu den Zellbereich A1:C11 . Klicken Sie dann im oberen Menüband auf die Registerkarte „Daten“ und dann auf die Schaltfläche „Filter“ .

Klicken Sie dann auf den Dropdown-Pfeil neben „ Konferenz “ und stellen Sie sicher, dass nur das Kontrollkästchen neben „ West“ aktiviert ist. Klicken Sie dann auf „OK“ :

Die Daten werden automatisch gefiltert, um nur Zeilen anzuzeigen, in denen die Spalte „Konferenz“ gleich „West“ ist:

Wenn wir versuchen, die Funktion AVERAGEIF() zu verwenden, um den Durchschnitt der Punktespaltenwerte zu berechnen, wobei der Positionsspaltenwert gleich „Guard“ ist, wird tatsächlich der Durchschnitt der Punkte für Zeilen zurückgegeben, die gleich „Guard“ sind. im Originaldatensatz:

Stattdessen müssen wir die folgende Formel verwenden:

 =AVERAGE(IF(SUBTOTAL(2,OFFSET( C2 ,ROW( C2:C11 )-ROW( C2 ),0)),IF( B2:B11 ="Guard", C2:C11 )))

Der folgende Screenshot zeigt, wie diese Formel in der Praxis angewendet wird:

Diese Formel gibt den korrekten Durchschnitt von 21,25 zurück.

Wir können bestätigen, dass dies korrekt ist, indem wir die Punktwerte für Zeilen, in denen die Positionsspalte den Wert „Guard“ hat, manuell mitteln:

Durchschnittliche Schutzpunkte: (12 + 28 + 30 + 15) / 4 = 21,25 .

Zusätzliche Ressourcen

In den folgenden Tutorials wird erläutert, wie Sie andere gängige Vorgänge in Excel ausführen:

So löschen Sie gefilterte Zeilen in Excel
So zählen Sie gefilterte Zeilen in Excel
So fügen Sie gefilterte Zeilen in Excel hinzu