AGGREGAT und TEILERGEBNIS im Zusammenspiel 2

Auf den ersten Blick nicht offensichtlich: AGGREGAT und TEILERGEBNIS können sich ergänzen.
 

Letzte Woche habe ich hier mit der AGGREGAT-Funktion ein wahres Multi-Talent vorgestellt.

In diesem Artikel zeige ich dir ein weiteres Einsatzgebiet für diese vielseitige Funktion. Im Zusammenspiel mit der TEILERGEBNIS-Funktion kann die AGGREGAT-Funktion nämlich weitere Stärken ausspielen.

Und so geht’s:

Teilergebnisse

Zu Beginn gleich ein Hinweis an alle, die den ersten Artikel noch nicht gelesen haben: Die AGGREGAT-Funktion ist erst ab Excel 2010 verfügbar.

Beginnen wir wieder mit einem Beispiel (die Datei kann hier heruntergeladen werden). In meiner kleinen Umsatzübersicht werden für die Verkaufsregionen Zwischenergebnisse berechnet, wie in folgendem Bild zu sehen ist:

Teilergebnisse je Region

Teilergebnisse je Region

Dazu habe ich die TEILERGEBNIS-Funktion verwendet, die ich in diesem Artikel schon mal näher beschrieben habe. Soweit so gut.

Problematisch wird es jedoch, wenn es für manche Verkäufer/Produkte/Regionen anstelle einer Umsatzzahl einen Fehlerwert gibt. Dies könnte z.B. dann der Fall sein, wenn die Werte über eine SVERWEIS-Funktion aus anderen Tabellen geholt werden und dort die gesuchte Kombination nicht vorhanden ist.

In diesem Fall scheitert die TEILERGEBNIS-Funktion kläglich:

Teilergebnisse mit Fehlern

Teilergebnisse mit Fehlern

Das ist wieder ein Fall für die AGGREGAT-Funktion, die wir auf unsere komplette Umsatztabelle (inkl. Teilergebnisse) anwenden. Dabei geben wir als Funktionsparameter den Wert „9“ (= Summe) und als Optionsparameter den Wert 3 an:

AGGREGAT im Einsatz

AGGREGAT im Einsatz

Und genau dieser Optionsparameter hat es in sich: Die durch die TEILERGEBNIS-Funktion berechneten Zwischensummen dürfen ja nicht mitgezählt werden, da es ansonsten zu einer Verdoppelung kommt. Außerdem sollen die enthaltenen Fehlerwerte auch ignoriert werden.

Und genau das macht diese Option „3“ und liefert somit trotz Fehler und Zwischensummen das korrekte Ergebnis:

AGGREGAT liefert das gewünschte Ergebnis

AGGREGAT liefert das gewünschte Ergebnis

Wenn man nun mit der Autofilter-Funktion arbeitet und die Region mit den Fehlerwerten ausblendet, kommt sowohl mit TEILERGEBNIS als auch mit AGGREGAT das korrekte Ergebnis heraus:

Gefilterte Liste

Gefilterte Liste

Dies liegt daran, dass ich in der TEILERGEBNIS-Funktion die Funktion „109“ (= Summe) angegeben habe. Diese berücksichtigt im Gegensatz zur Funktion „9“ (= ebenfalls Summe) keine per Filter ausgeblendeten Werte.

Für den Fall, dass jedoch kein Autofilter zum Einsatz kommt, sondern Zeilen manuell ausgeblendet werden, hat AGGREGAT auch keine Probleme. Auch hier werden die korrekten Werte ermittelt:

Manuell ausgeblendete Zeilen

Manuell ausgeblendete Zeilen

Wie man sieht, kommt es wirklich auf den Einzelfall an. Oftmals reicht die TEILERGEBNIS-Funktion völlig aus, aber es ist gut, wenn man die AGGREGAT-Funktion im Hinterkopf behält. Denn manchmal führt nur sie zum gewünschten Ergebnis.

Wenn dir der Artikel gefallen hat: Bitte weitersagen!

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht.

2 Gedanken zu “AGGREGAT und TEILERGEBNIS im Zusammenspiel

  • Bruno

    Hallo Martin,
    diesen Teilergebnis/Aggregat-Beitrag habe ich mit großem Interesse gelesen. Allerdings hat er mir bei einem (Luxus-)Problem noch nicht geholfen:
    Bei einer Tabelle, die durch eine Filterfunktion entstanden ist möchte ich jede 2. Zeile schattieren. Als Beispieldatei habe ich die B253_Filter.xlsx und dort Blatt „Neue_Lösung“ genommen. Dabei habe ich in der bedingten Formatierung die verbreitete Formel:
    =REST(TEILERGEBNIS(3;$E$4:$E5);2)=0 bei gewähltem Bereich $E4:$F55 eingesetzt.
    Bei China (42 Zeilen) hört die Schattierung wie gewünscht am Ende bei Zeile 45 auf aber bei anderen Ländern geht die Schattierung bis zum Bereichsende weiter, manchmal 2-zeilig und manchmal durchgehend.
    Da kann ich mir keinen Reim drauf machen und frage mich ob man durch eine geschickte Kombination mit der Aggregat-Funktion das gewünschte Ergebnis „Ende der Schattierung bei der letzten Filterzeile“ erreichen kann?
    Beste Grüße von dem TabellenExperten-Fan Bruno

    • Martin Weiß Autor des Beitrags

      Hallo Bruno,

      die Formel hat einen kleinen Fehler. Statt:
      =REST(TEILERGEBNIS(3;$E$4:$E5);2)=0)
      müsste es heißen:
      =REST(TEILERGEBNIS(3;$E$4:$E4);2)=0)

      Und damit sie nur auf gefüllte Zeilen wirkt, könntest du sie noch erweitern:
      =UND(NICHT(ISTLEER($E4));REST(TEILERGEBNIS(3;$E$4:$E4);2)=0)

      Dann sollte es klappen.

      Schöne Grüße,
      Martin