TEILERGEBNIS-Funktion – Beispiele in Excel, VBA und Google Sheets

In diesem Tutorial zeigen wir Ihnen, wie Sie die Funktion TEILERGEBNIS in Excel  und Google Sheets verwenden können, um statistische Zusammenfassungen zu erstellen.

 

Was ist die TEILERGEBNIS-Funktion?

Die TEILERGEBNIS-Funktion erstellt eine zusammenfassende Statistik für eine Datenreihe. Zu den verfügbaren Statistiken gehören unter anderem Mittelwert, Standardabweichung, Anzahl, Minimum und Maximum.

Die TEILERGEBNIS-Funktion ist eine der einzigartigen Funktionen in Tabellenkalkulationen, da sie den Unterschied zwischen ausgeblendeten und nicht ausgeblendeten Zellen erkennen kann. Dies kann sich als sehr hilfreich erweisen, wenn Sie mit gefilterten Bereichen arbeiten oder wenn Sie Berechnungen auf der Grundlage verschiedener Auswahlen des Benutzers erstellen müssen. Da die Funktion auch andere ZUSAMMENFASSUNGSFUNKTIONEN bei ihren Berechnungen ignorieren kann, können wir sie auch bei großen zusammengefassten Daten verwenden, ohne eine Doppelzählung befürchten zu müssen.

Grundlegende Zusammenfassung mit TEILERGEBNIS

Nehmen wir an, Sie hätten eine Tabelle mit sortierten Produktverkäufen und wollten Summen für jedes Produkt sowie eine Gesamtsumme erstellen. Sie könnten eine Pivot-Tabelle verwenden oder einige Formeln einfügen. Betrachten Sie dieses Layout:

Ich habe einige TEILERGEBNIS-Funktionen, die wie folgt aussehen, in die Zellen B5 und B8 eingefügt:

=TEILERGEBNIS(9; B2:B4)

Aus der Syntax geht hervor, dass Sie für das erste Argument eine Vielzahl von Zahlen verwenden können. In unserem speziellen Fall verwenden wir 9, um anzugeben, dass wir eine Summe bilden wollen.

Konzentrieren wir uns auf die Zelle B9. Sie enthält diese Formel, die den gesamten Datenbereich in Spalte B einschließt, aber nicht die anderen Zwischensummen.

=TEILERGEBNIS(9; B2:B8)

HINWEIS: Wenn Sie nicht alle Zusammenfassungsformeln selbst schreiben möchten, können Sie die Hauptregisterkarte Daten aufrufen und den Assistenten Gliederung -> Teilergebnis verwenden. Er wird Zeilen automatisch einfügen und die Formeln für Sie platzieren.

Ausgeblendete TEILERGEBNIS-Zeilen

Im ersten Beispiel haben wir eine 9 verwendet, um anzugeben, dass wir summieren wollen. Stattdessen können wir 109 für die Summe verwenden. Worin besteht der Unterschied? Wenn Sie die Bezeichnungen 1XX verwenden, schließt die Funktion keine Zeilen ein, die manuell ausgeblendet oder gefiltert wurden. Hier ist unsere Tabelle von vorher. Wir haben die Funktionen verschoben, damit wir den Unterschied zwischen den 9er und 109er Argumenten sehen können. Wenn alle sichtbar sind, sind die Ergebnisse die gleichen.

Wenn wir einen Filter anwenden, um den Wert „Banane“ in Spalte A herauszufiltern, bleiben die beiden Funktionen gleich.

Wenn wir die Zeilen manuell ausblenden, sehen wir den Unterschied. Die Funktion 109 war in der Lage, die ausgeblendete Zeile zu ignorieren, während die Funktion 9 dies hingegen nicht kann.

 

Die Mathematische Operation mit TEILERGEBNIS ändern

Vielleicht möchten Sie dem Benutzer manchmal die Möglichkeit geben, die Art der durchgeführten Berechnungen zu ändern. er  Möchte zum Beispiel die Summe oder den Mittelwert erhalten. Da TEILERGEBNIS die mathematische Operation durch ein Argument steuert, können Sie dies in einer einzigen Formel schreiben. Hier ist unser Aufbau:

Wir haben in D2 eine Auswahlliste, in der der Benutzer entweder „Summe“ oder „Mittelwert“ auswählen kann, erstellt. Die Formel in E2 lautet:

=TEILERGEBNIS(WENN(D2="Mittelwert";1;WENN(D2="Summe";9));B2:B4)

Hier bestimmt die WENN-Funktion, welches numerische Argument an TEILERGEBNIS weitergegeben werden soll. Wenn der Wert in A5 „Mittelwert“ entspricht, wird eine 1 ausgegeben und TEILERGEBNIS liefert den Mittelwert von B2:B4. Wenn der Wert in A5 jedoch „Summe“ entspricht, gibt die WENN-Funktion eine 9 aus und wir erhalten ein anderes Ergebnis.

Sie könnten diese Fähigkeit erweitern, indem Sie eine Nachschlagetabelle verwenden, um noch mehr Arten von Operationen, die Sie durchführen möchten, aufzulisten. Ihre Nachschlagetabelle könnte wie folgt aussehen:

Dann könnten Sie die Formel in E2 wie folgt ändern:

=TEILERGEBNIS(SVERWEIS(A5; Nachschlagetabelle; 2; 0); B2:B4)

 

Bedingte Formeln mit TEILERGEBNIS

TEILERGEBNIS kann zwar viele Operationen ausführen, aber keine Kriterien allein überprüfen. Sie kann jedoch in einer Hilfsspalte verwendet werden, um diese Operation durchzuführen. Wenn Sie eine Datenspalte haben, von der Sie wissen, dass sie immer einen Teil der Daten enthält, können Sie die Fähigkeit von TEILERGEBNIS nutzen, um ausgeblendete Zeilen zu erkennen.

Das ist die Tabelle, mit der wir in diesem Beispiel arbeiten werden. Schließlich möchten wir die Werte für „Apfel“ summieren können, aber auch dem Benutzer die Möglichkeit geben, die Spalte „Menge“ zu filtern.

Erstellen Sie zunächst eine Hilfsspalte, die die TEILERGEBNIS-Funktion aufnehmen soll. In C2 lautet die Formel:

=TEILERGEBNIS(103; A2)

Denken Sie daran, dass 103 bedeutet, dass wir eine ZÄHLUNG durchführen wollen. Ich empfehle die Verwendung von ANZAHL2, weil Sie dann Ihre Referenzzelle A2 entweder mit Zahlen oder mit Text füllen können. Die Tabelle sieht nun wie folgt aus:

Auf den ersten Blick erscheint dies nicht hilfreich, da alle Werte nur 1 entsprechen. Wenn wir jedoch Zeile 3 ausblenden, wird die „1“ in C3 zu einer 0, da diese auf eine ausgeblendete Zeile verweist. Es ist zwar unmöglich, ein Bild zu haben, das den Wert einer bestimmten ausgeblendeten Zelle zeigt, aber man kann es überprüfen, indem man die Zeile ausblendet und dann eine einfache Formel wie die folgende schreibt, um sie zu überprüfen.

=C3

Da wir nun eine Spalte haben, deren Wert sich ändert, je nachdem ob sie ausgeblendet ist oder nicht, können wir die endgültige Gleichung schreiben. Unsere SUMMEWENNS wird wie folgt aussehen:

In dieser Formel werden wir nur die Werte aus Spalte B summieren, wenn Spalte A gleich „Apfel“ ist und der Wert in Spalte C gleich 1 ist (d. h. die Zeile ist nicht ausgeblendet). Nehmen wir an, unser Benutzer möchte den Wert 600 herausfiltern, weil er ihm ungewöhnlich hoch erscheint. Wir können sehen, dass unsere Formel das richtige Ergebnis liefert.

Mit dieser Fähigkeit können Sie eine Prüfung auf ZÄHLENWENNS, SUMMEWENNS oder sogar SUMMENPRODUKT anwenden. Sie Fügen die Fähigkeit hinzu, Ihre Benutzer einige Datenschnitte kontrollieren zu lassen und Sie sind bereit, ein großartiges Dashboard zu erstellen.

 

TEILERGEBNIS in Google Sheets

Die Funktion SUBTOTAL funktioniert in Google Sheets genau so wie in Excel:

 

TEILERGEBNIS-Beispiele in VBA

Sie können die TEILERGEBNIS-Funktion auch in VBA verwenden. Geben Sie Folgendes ein:

Application.WorksheetFunction.Subtotal(Funktion, Bezug1)

Ausführen der folgenden VBA-Anweisungen:

Range("C7") = Application.WorksheetFunction.Subtotal(1, Range("C2:C5"))
Range("C8") = Application.WorksheetFunction.Subtotal(2, Range("C2:C5"))
Range("C9") = Application.WorksheetFunction.Subtotal(4, Range("C2:C5"))
Range("C10") = Application.WorksheetFunction.Subtotal(5, Range("C2:C5"))
Range("C11") = Application.WorksheetFunction.Subtotal(9, Range("C2:CE5"))

Range("D7") = Application.WorksheetFunction.Subtotal(1, Range("D2:D5"))
Range("D8") = Application.WorksheetFunction.Subtotal(2, Range("D2:D5"))
Range("D9") = Application.WorksheetFunction.Subtotal(4, Range("D2:D5"))
Range("D10") = Application.WorksheetFunction.Subtotal(5, Range("D2:D5"))
Range("D11") = Application.WorksheetFunction.Subtotal(9, Range("D2:D5"))

Range("E7") = Application.WorksheetFunction.Subtotal(1, Range("E2:E5"))
Range("E8") = Application.WorksheetFunction.Subtotal(2, Range("E2:E5"))
Range("E9") = Application.WorksheetFunction.Subtotal(4, Range("E2:E5"))
Range("E10") = Application.WorksheetFunction.Subtotal(5, Range("E2:E5"))
Range("E11") = Application.WorksheetFunction.Subtotal(9, Range("E2:E5"))

wird die folgenden Ergebnisse liefern

Die Funktionsargumente (Funktion usw.) können Sie entweder direkt in die Funktion eingeben oder Variablen definieren, die Sie stattdessen verwenden.