Autor: Beate Schmitz --- Aus Excel Standard - Gruppe: Funktionen
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
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:
Weitere Artikel der Gruppe: Funktionen Aus Excel Standard
Nach oben
ToDo
Aggregat - ab E 2010
Autor: Beate Schmitz - Erstellt: 2013-06-03 - Letzte Revision: 2013-06-20Die 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
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:
- Erläuterung der Syntax
- Zahlen der Funktionen
- Zahlen der Optionen
- Praktische Hilfe zur Formelerstellung
- Beispiel: Autofilter bzw. Spezialfilter - ausgeblendete Zeilen ignorieren
- Beispiel: Manuell ausgeblendete Zeilen ignorieren
- Beispiel: Bedingte Formatierung - Fehlerwerte ignorieren
- Beispiel: TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren
| 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! | ||
| Zahl | Funktion |
| 1 | MITTELWERT |
| 2 | ANZAHL |
| 3 | ANZAHL2 |
| 4 | MAX |
| 5 | MIN |
| 6 | PRODUKT |
| 7 | STABW.S |
| 8 | STABW.N |
| 9 | SUMME |
| 10 | VAR.S |
| 11 | VAR.P |
| 12 | MEDIAN |
| 13 | MODUS.EINF |
| 14 | KGRÖSSTE |
| 15 | KKLEINSTE |
| 16 | QUANTIL.INKL |
| 17 | QUARTILE.INKL |
| 18 | QUANTIL.EXKL |
| 19 | QUARTILE.EXKL |
| Zahl | Option |
| 0 oder nicht angegeben | Geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren |
| 1 | Ausgeblendete Zeilen, geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren |
| 2 | Fehlerwerte, geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren |
| 3 | Ausgeblendete Zeilen, Fehlerwerte, geschachtelte TEILERGEBNIS- und AGGREGAT-Funktionen ignorieren |
| 4 | Nichts ignorieren |
| 5 | Ausgeblendete Zeilen ignorieren |
| 6 | Fehlerwerte ignorieren |
| 7 | Ausgeblendete 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:
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:
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:
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:
| A | B | |
| 1 | gefilterte Summe mit TEILERGEBNIS: | 1.541,48 € |
| 2 | gefilterte Summe mit AGGREGAT: | 1.541,48 € |
| 3 | Kunde | Umsatz |
| 4 | Kunde1 | 662,37 € |
| 6 | Kunde3 | 657,87 € |
| 8 | Kunde1 | 221,24 € |
| Formeln der Tabelle | ||||||
|
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:
| A | B | |
| 1 | gefilterte Summe mit TEILERGEBNIS: | #NV |
| 2 | gefilterte Summe mit AGGREGAT: | 883,61 € |
| 3 | Kunde | Umsatz |
| 4 | Kunde1 | 662,37 € |
| 6 | Kunde3 | #NV |
| 8 | Kunde1 | 221,24 € |
| Formeln der Tabelle | ||||||
|
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.
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:
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.
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:
| A | B | C | |
| 1 | 7 | 21 | 4 |
| 2 | 88 | 98 | 45 |
| 3 | 86 | 76 | 95 |
| 4 | 66 | 68 | 43 |
| 5 | 75 | #NV | 52 |
| 6 | 76 | 2 | 33 |
| 7 | 55 | 75 | 89 |
| Bedingte Formatierungen der Tabelle | |||||||||
|
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:
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:
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:
| A | B | |
| 1 | ||
| 2 | Kunde | Umsatz |
| 3 | Kunde1 | 662,37 € |
| 4 | Kunde1 | 815,79 € |
| 5 | Kunde2 | 566,58 € |
| 6 | Kunde2 | 221,24 € |
| 7 | Kunde3 | #NV |
| 8 | Kunde3 | 797,03 € |
| 9 | Kunde3 | 391,41 € |
| 10 | Kunde4 | 610,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:
| A | B | |
| 1 | AGGREGAT: | 4.065,08 € |
| 2 | Kunde | Umsatz |
| 3 | Kunde1 | 662,37 € |
| 4 | Kunde1 | 815,79 € |
| 5 | Kunde1 Ergebnis | 1.478,16 € |
| 6 | Kunde2 | 566,58 € |
| 7 | Kunde2 | 221,24 € |
| 8 | Kunde2 Ergebnis | 787,82 € |
| 9 | Kunde3 | #NV |
| 10 | Kunde3 | 797,03 € |
| 11 | Kunde3 | 391,41 € |
| 12 | Kunde3 Ergebnis | #NV |
| 13 | Kunde4 | 610,66 € |
| 14 | Kunde4 Ergebnis | 610,66 € |
| 15 | Gesamtergebnis | #NV |
| Formeln der Tabelle | ||||||||||||||
|
Weitere Artikel der Gruppe: Funktionen Aus Excel Standard
Nach oben
Google Werbung
