AGGREGAT und TEILERGEBNIS im Zusammenspiel 6

Noch mehr AGGREGAT
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.



Avatar-Foto

Über Martin Weiß

Er ist das Gesicht hinter dem Blog "Der Tabellenexperte". Seit 2013 veröffentlicht er hier Beiträge zu seinem Lieblingsprogramm: Microsoft Excel. Martin Weiß ist zertifizierter Microsoft Excel Expert und verdient sein Geld als selbständiger Excel-Berater, -Entwickler und -Trainer.

Schreibe einen Kommentar zu Martin Weiß Antworten abbrechen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert

6 Gedanken zu “AGGREGAT und TEILERGEBNIS im Zusammenspiel

  • Avatar-Foto
    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

    • Avatar-Foto
      Martin Weiß

      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

  • Avatar-Foto
    Janice Uhlig

    Hallo,
    Ich habe folgendes Problem,
    Ich würde gern eine durchgehende Nummerierung machen,allerdings soll diese zwischendrin unter gewissen Bedingungen wieder bei 1 starten.
    Wie in Ihrem Beispiel oben aber extra noch in Bezug auf die Region.
    Also die Region Nord durchgehend nummerierten und wenn Region Süd kommt wieder durchgehend nummerierten Ber bei 1 beginnend.
    Ist dies möglich?
    Wenn ja haben sie mir eine Idee wie ich dies umsetzen kann?
    DANKE
    Grüße Janice

    • Avatar-Foto
      Martin Weiß

      Hallo Janice,

      das wäre beispielsweise über eine WENN-Funktion zu lösen:

      =WENN(A3<>A2;1;B2+1)
      Dynamischer Zähler

      Schöne Grüße,
      Martin

      • Avatar-Foto
        Janice Uhlig

        Hallo, danke schonmal
        Das hat gut geklappt leider tut sich jetzt ein neues Problem.
        Bsp
        SpalteA Spalte B Spalte C
        Region Name Zählen
        Süd Max 1
        Süd Moritz 2
        Süd
        Nord Susi 1
        Nord Strolch 2
        Nord

        Es soll nur zählen wenn bei der Region auch ein Name in Spalte B steht, wenn kein Name eingetragen ist soll es leer bleiben
        Danke für die Hilfe
        Viele Grüße

        • Avatar-Foto
          Martin Weiß

          Hallo Janice,

          in diesem Fall muss eine weitere WENN-Abfrage eingebaut werden, die prüft, ob der Name leer ist:
          =WENN(B3=““;““;WENN(A3<>A2;1;C2+1))

          Schöne Grüße,
          Martin