Excel–Summe, Teilergebnis und Aggregat

Eine Summe ist eine Summe? Nicht ganz. Beispielsweise kann man eine Summe mit oder ohne ausgeblendete Zeilen berechnen, man kann bei “ausgeblendet” unterscheiden, ob es durch Filter oder von Hand ausgeblendet wurde. Und was ist mit Fehlerwerten wie #NV?

Drei Funktionen stelle ich vor: SUMME, TEILERGEBNIS und AGGREGAT (letzteres gibt es seit Excel 2010).

07.02.2019_0000Schauen wir zuerst auf den Unterschied von SUMME und TEILERGEBNIS.

Addiert werden die Werte aus C2:C15, die Summe ist 2.500. Alle drei Formeln liefern dieses Ergebnis:

  • =SUMME(C2:C15)
  • =TEILERGENBIS(9;C2:C15)
  • =TEILERGENBIS(109;C2:C15)

 

 

 

 

Der erste Unterschied wird sichtbar, 07.02.2019_0001wenn ich einen Filter setze. Ich filtere auf das Gebiet “Nord”. Die Summe der nun sichtbaren Zeilen ist 1.400. Die SUMME bleibt bei dem Ergebnis 2.500, die beiden Teilergebnisse liefern 1.400.

Damit ist eines schon mal klar:

SUMME addiert immer alles im angegebenen Bereich, auch nicht sichtbare Zeilen.

TEILERGEBNIS sowohl mit der Funktion 9 als auch mit Funktion 109 rechnet nur mit den sichtbaren Zellen.

 

Was unterscheidet TEILERGEBNIS 9 und 109?

Das wird sichtbar, wenn ich nicht über einen Filter die Zeilen ausblende, sondern mit dem Befehl “Zeilen ausblenden”.

07.02.2019_0002SUMME (C2:C15) und TEILERGEBNIS(9;C2:C15) liefern jetzt das Gesamtergebnis 2.500 und berechnen alle Zellen im Bereich – auch die ausgeblendeten. Anders rechnet TEILERGENBIS(109;C2:C15)! Hier werden die von Hand ausgeblendeten Zeilen nicht berechnet.

Ganz genau muss es also heißen:

  • SUMME berechnet alles, auch über Filter oder von Hand ausgeblendete Zellen
  • TEILERGEBNIS(9;…) berechnet alles, aber nicht die durch einen Filter ausgeblendeten Zellen
  • TEILERGEBNIS(109;…) berechnet tatsächlich nur sichtbare Zellen

 

 

Zusätzlich gibt es seit Excel 2010 die Funktion AGGREGRAT, die genauso wie TEILERGEBNIS mehrere Funktionen anbietet.

=AGGREGRAT(Funktionsnr.;Option;Array)

Die Funktionsnummern 1 bis 19 werden mit den Funktionsnamen angezeigt, wenn Sie die Funktion in Excel tippen. Zum Beispiel steht die Ziffer 9 für die Summe, genauso wie bei TEILERGEBNIS. Andere Funktionen sind Mittelwert (1), Anzahl2 (2) oder Max(4).

Anschließend an die Funktionsnummer kommt eine Ziffer 0 bis 7 für die Option. Und hier gibt es mit der Ziffer 5 auch die Möglichkeit, ausgeblendete Zeilen zu ignorieren.

07.02.2019_0003

Die Funktion =AGGREGAT(9;5;C2:C15) liefert das gleiche Ergebnis wie TEILERGEBNIS(109;C2:C15). Alle ausgeblendeten Zeilen werden ignoriert, sowohl die gefilterten als auch die von Hand ausgeblendeten. Aber AGGREGAT kann noch mehr: z. B. Fehlerwerte ignorieren. Und daran scheitern SUMME und TEILERGEBNIS.

07.02.2019_0004

Sowohl =SUMME(C2:C21) als auch beide TEILERGEBNIS-Varianten liefern einen Fehlerwert. Aber AGGREGAT berechnet die restlichen Zellen.

In Zelle C27 steht:
=AGGREGAT(9;6;C2:C21)

9 – steht für die Funktion SUMME
6 – steht für “Fehlerwert ignorieren”

 

Wenn ich nicht nur Fehlerwerte, sondern auch ausgeblendete Zellen jedweder Art ausschließen will, dann sieht die Formel so aus:
=AGGREGAT(9;7;C2:C21)

Morgen zeige ich, wie man mit TEILERGEBNIS oder AGGREGAT Listen mit Zwischensummen addiert.

 

Die Tabelle von heute kann zum Ausprobieren hier: Aggregat heruntergeladen werden.

Werbeanzeigen

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google Foto

Du kommentierst mit Deinem Google-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s