Autor: Beate Schmitz  --- Aus Excel Standard - Gruppe: Funktionen

Aggregat - ab E 2010

Autor: Beate Schmitz - Erstellt: 2013-06-03      - Letzte Revision: 2013-06-20

Die Funktion AGGREGAT (ab Excel 2010)

Ab Excel 2010 gibt es diese neue Funktion, die die bisherige Funktion TEILERGEBNIS erweitert, indem sie mehr Funktionen gepaart mit besonderen Optionen bietet. Bei den Optionen kann man wählen, ob
  • Ausgeblendete Zeilen (interessant bei Einsatz Autofilter bzw. Spezialfilter) bzw. manuell ausgeblendete Zeilen
  • Fehlerwerte (interessant bei Nutzung der bedingten Formatierung)
  • geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen
berücksichtigt oder ignoriert werden sollen. Und dies in jedmöglicher Kombination.

Ich persönlich empfinde Fehlerwerte zumindest als Schönheitsfehler und würde sie ursächlich vermeiden. Guckst du: Fehlerwerte behandeln.

Beispiele für AGGREGAT gäbe es endlos, es kommt darauf an, welche Funktion Sie mit welcher Option kombinieren. Also: Hinschauen lohnt sich!

Auf folgende Themen wird nachfolgend detailliert eingegangen:


 Syntax:
Bezugsversion: Matrixversion:
=AGGREGAT(Funktion;Option;Bezug1;[Bezug2];…) =AGGREGAT(Funktion;Option;Array;[k])
    
Funktion:Ist die Zahl von 1-19 (s.u.), die die Zusammenfassungsfunktion für das Aggregat angibt.
Option:Ist die Zahl von 0-7 (s.u.), mit der die für das Aggregat zu ignorierenden Werde angegeben werden.
Bezug:Es können 1 bis 253 Bezüge eingegeben werden.
k:Zeigt die Position im Array an; bei den Funktionen KGRÖSSTE, KKLEINSTE, QUANTIL oder QUARTIL muss k angegeben werden, bei den Anderen nicht. Wird dies nicht berücksichtigt, ergibt sich der Fehlerwert #WERT!


ZahlFunktion
1MITTELWERT
2ANZAHL
3ANZAHL2
4MAX
5MIN
6PRODUKT
7STABW.S
8STABW.N
9SUMME
10VAR.S
11VAR.P
12MEDIAN
13MODUS.EINF
14KGRÖSSTE
15KKLEINSTE
16QUANTIL.INKL
17QUARTILE.INKL
18QUANTIL.EXKL
19QUARTILE.EXKL


ZahlOption
0 oder nicht angegebenGeschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren
1Ausgeblendete Zeilen, geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren
2Fehlerwerte, geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren
3Ausgeblendete Zeilen, Fehlerwerte, geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren
4Nichts ignorieren
5Ausgeblendete Zeilen ignorieren
6Fehlerwerte ignorieren
7Ausgeblendete Zeilen und Fehlerwerte ignorieren


Zum Blattanfang

Praktische Hilfe zur Formelerstellung

Da man sich kaum alle Zahlen der Funktionen und Optionen merken kann, empfiehlt sich folgende Vorgehensweise bei der Formelerfassung:
  • Schreiben sie in die Zelle: =Aggregat(. Es öffnet sich ein Listenfeld mit allen Funktionen, durch Doppelklick können Sie die gewünschte Funktion als Zahl in Ihre Formel übernemen
  • Geben Sie in der Bearbeitungsleiste hinter der Funktionszahl ein Semikolon ein, automatisch springt dann ein Listenfeld mit allen Optionen auf, auch hier können Sie die Auswahl per Doppelklick treffen
  • Geben Sie in der Bearbeitungsleiste hinter der Optionszahl ein Semikolon ein und markieren Sie den/die auszuwertenden Bereich(e) mit der Maus. Liegen die Bereiche nicht am Stück, müssen sie dabei die STRG-Taste gedrückt halten - automatisch fügt Excel die Strichpunkte ein
  • Ist die Eingabe von k erforderlich (Rang des Elements einer Matrix oder eines Zellbereichs, dessen Wert zurückgegeben werden soll), müssen Sie nochmal ein Semikolon setzen und die gewünschte Zahl eingeben
  • Zuletzt schließen Sie die Formel mit einer runden Klammer und Enter ab


Zum Blattanfang

Beispiel: Autofilter - ausgeblendete Zeilen ignorieren

In diesem Beispiel ersetzt die Funktion AGGREGAT die bekannte Funktion TEILERGEBNIS. Die Option Ausgeblendete Zeilen ignorieren wirkt sich ausdrücklich nur auf ausgeblendete Zeilen, nicht auf ausgeblendete Spalten aus! Im Beispiel sollen die sichtbaren Zellen im Bereich B4:B11 summiert werden:

 AB
1gefilterte Summe mit TEILERGEBNIS:1.541,48 €
2gefilterte Summe mit AGGREGAT:1.541,48 €
3KundeUmsatz
4Kunde1662,37 €
6Kunde3657,87 €
8Kunde1221,24 €

Formeln der Tabelle
ZelleFormel
B1=TEILERGEBNIS(9;B4:B11)
B2=AGGREGAT(9;5;B4:B11)


Ein Unterschied in der Funktionsweise wird sichtbar, wenn Fehlerwerte im Bereich vorkommen, da die Funktion TEILERGEBNIS damit nicht umgehen kann. Wenn wir dies mit der Funktion AGGREGAT erreichen wollen, müssen wir aber auch die Optionszahl auf 7 ändern:

 AB
1gefilterte Summe mit TEILERGEBNIS:#NV
2gefilterte Summe mit AGGREGAT:883,61 €
3KundeUmsatz
4Kunde1662,37 €
6Kunde3#NV
8Kunde1221,24 €

Formeln der Tabelle
ZelleFormel
B1=TEILERGEBNIS(9;B4:B11)
B2=AGGREGAT(9;7;B4:B11)


Zum Blattanfang

Beispiel: Manuell ausgeblendete Zeilen ignorieren

Im Gegensatz zu TEILERGEBNIS kann man mit der AGGREGAT-Funktion erreichen, dass auch manuell ausgeblendete Zeilen nicht im Ergbebnis berücksichtigt werden. Und zwar, indem man je nach Bedarf eine der Optionen 1, 3, 5 oder 7 wählt.


Zum Blattanfang

Beispiel: Bedingte Formatierung - Fehlerwerte ignorieren

Es gibt Funktionen, die nicht nutzbar sind im Rahmen der Bedingten Formatierung, insofern Fehlerwerte im auszuwertenden Bereich auftreten. Durch den Einsatz der Funktion AGGREGAT können wir dieses Manko nun umgehen.

In nachfolgendem Beispiel wollte ich den 4-kleinsten Wert mit KKLEINSTE orange hinterlegen. Da der Bereich einen Fehlerwert enthält, bleibt die gesetzte Bedingung aber wirkungslos. Mit der Funktion AGGREGAT habe ich dagegen den 5-kleinsten Wert blau färben können. Hierfür habe ich die Funktionszahl 15 und die Optionszahl 6 genutzt und k mit 5 festgelegt:

 ABC
17214
2889845
3867695
4666843
575#NV52
676233
7557589

Bedingte Formatierungen der Tabelle
ZelleNr.: / BedingungFormat
A11. / Formel ist =A1=AGGREGAT(15;6;$A$1:$C$7;5)Abc
A12. / Formel ist =A1=KKLEINSTE($A$1:$C$7;4)Abc


Auf manchen Erklärungsseiten sieht man dieses Beispiel auch mit MAX/MIN-Werten, aber das ist überflüssig, weil man ja den Erst-Größten bzw. Erst-Kleinsten Wert aussuchen könnte und das kann Excel 2010, weil es auch Fehlerwerte ignoriert.


Zum Blattanfang

Beispiel: TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren

Hier geht es um die Vermeidung von Doppeladditionen. Was hier gezeigt wird, hat Teilergebnis Menü schon immer gekonnt. Außer, wenn die Liste Fehlerwerte enthält.

Wir operieren in diesem Beispiel mit folgender Ausgangsliste, die nach Spalte A aufsteigend sortiert ist und einen Fehlerwert enthält:

 AB
1  
2KundeUmsatz
3Kunde1662,37 €
4Kunde1815,79 €
5Kunde2566,58 €
6Kunde2221,24 €
7Kunde3#NV
8Kunde3797,03 €
9Kunde3391,41 €
10Kunde4610,66 €


Diese habe ich nun über Menü>Daten>Gliederung>Teilergebnis ausgewertet, was folgendes Bild ergibt. In Zelle B1 habe ich die AGGREGAT-Funktion eingefügt, welche trotz Fehlerwerten in der Tabelle im Gegensatz zur Zelle B15 ein sinnvolles Ergebnis liefert, weil ich die Funktionszahl 9 in Kombination mit der Optionszahl 3 gewählt habe:

 AB
1AGGREGAT:4.065,08 €
2KundeUmsatz
3Kunde1662,37 €
4Kunde1815,79 €
5Kunde1 Ergebnis1.478,16 €
6Kunde2566,58 €
7Kunde2221,24 €
8Kunde2 Ergebnis787,82 €
9Kunde3#NV
10Kunde3797,03 €
11Kunde3391,41 €
12Kunde3 Ergebnis#NV
13Kunde4610,66 €
14Kunde4 Ergebnis610,66 €
15Gesamtergebnis#NV

Formeln der Tabelle
ZelleFormel
B1=AGGREGAT(9;3;B3:B13)
B5=TEILERGEBNIS(9;B3:B4)
B8=TEILERGEBNIS(9;B6:B7)
B12=TEILERGEBNIS(9;B9:B11)
B14=TEILERGEBNIS(9;B13:B13)
B15=TEILERGEBNIS(9;B3:B13)



Weitere Artikel der Gruppe: Funktionen Aus Excel Standard
Nach oben
ToDo
Google Werbung