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
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
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
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
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
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
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.
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.
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
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
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
Hallo Janice,
das wäre beispielsweise über eine WENN-Funktion zu lösen:
=WENN(A3<>A2;1;B2+1)
Schöne Grüße,
Martin
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
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