AGGREGATE-Funktion in Excel
Die AGGREGATE-Funktion in Excel gibt das Aggregat einer bestimmten Datentabelle oder Datenlisten zurück. Diese Funktion hat auch das erste Argument als Funktionsnummer und weitere Argumente gelten für einen Bereich der Datensätze. Die Funktionsnummer sollte gespeichert werden, um zu wissen, welche Funktion verwendet werden soll .
Syntax
Es gibt zwei Syntaxen für die AGGREGATE-Formel:
- Referenzsyntax
= AGGREGATE (Funktionsnummer, Optionen, Ref1, Ref2, Ref [3],…)
- Array-Syntax
= AGGREGATE (Funktionsnummer, Optionen, Array, [k])
Function_num ist eine Zahl, die eine bestimmte Funktion angibt, die wir verwenden möchten. Es ist eine Zahl von 1 bis 19
Option: Es ist auch ein numerischer Wert zwischen 0 und 7 und bestimmt, welche Werte bei Berechnungen ignoriert werden sollen
Ref1, ref2, ref [3]: Ist das Argument bei Verwendung der Referenzsyntax. Es handelt sich um einen numerischen Wert oder Werte, für die die Berechnung durchgeführt werden soll. Mindestens zwei Argumente sind erforderlich. Restargumente sind optional.
Array: ist ein Array von Werten, für die wir die Operation ausführen möchten. Es wird in der Array-Syntax der AGGREGATE-Funktion in Excel verwendet
K: ist ein optionales Argument und ein numerischer Wert. Es wird verwendet, wenn die Funktion LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC oder QUARTILE.EXC in Excel verwendet wird.
Beispiele
Sie können diese AGGREGATE-Funktions-Excel-Vorlage hier herunterladen - AGGREGATE-Funktions-Excel-VorlageBeispiel 1
Angenommen, wir haben eine Liste von Zahlen und berechnen den Durchschnitt, die Anzahl der Zellen, die einen Wert enthalten, die Anzahl der nicht leeren Zellen, das Maximum, das Minimum, das Produkt und die Summe der angegebenen numerischen Werte. Die Werte sind unten in der Tabelle angegeben:
Berechnen wir zunächst den Durchschnitt in Zeile 9 für alle angegebenen Werte. Für den Durchschnitt ist die Funktion_Nummer
In Spalte C sind alle Werte angegeben und wir müssen keine Werte ignorieren, also wählen wir die Option 4 (nichts ignorieren)
Wählen Sie den Wertebereich C1: C8 als Array numerischer Werte aus
Da ' k' ein optionales Argument ist und verwendet wird, wenn eine Funktion wie LARGE, SMALL in Excel, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC oder QUARTILE.EXC verwendet wird, berechnen wir in diesem Fall den Durchschnitt wird den Wert von k weglassen.
Der Durchschnittswert ist also
In ähnlicher Weise wählen wir für den Bereich D1: D8 erneut Option 4 aus.
Für den Bereich E1: E8 enthält eine Zelle E6 einen Fehlerwert. Wenn wir dieselbe AGGREGATE-Formel verwenden, wird ein Fehler angezeigt. Wenn jedoch eine geeignete Option verwendet wird, gibt AGGREGATE in Excel den Durchschnitt der verbleibenden Werte an, wobei der Fehler vernachlässigt wird Wert in E6.
Um die Fehlerwerte zu ignorieren, haben wir Option 6.
In ähnlicher Weise verwenden wir für den Bereich G1: G8 die Option 6 (ignorieren Sie die Fehlerwerte).
Wenn wir für den Bereich H3 einen Wert 64 eingeben und die dritte Zeile ausblenden und Option 5 verwenden, um die ausgeblendete Zeile AGGREGATE in Excel zu ignorieren, geben wir den Durchschnittswert nur für sichtbare numerische Werte an.
Ausgabe ohne Zeile 3 auszublenden
Ausgabe nach Ausblenden von Zeile 3
Wir haben die AGGREGATE-Formel für andere Operationen angewendet
Beispiel - # 2
Angenommen, wir haben eine Tabelle für die Einnahmen, die an verschiedenen Daten aus den verschiedenen Kanälen erzielt wurden (siehe unten)
Jetzt wollen wir die Einnahmen überprüfen, die für verschiedene Kanäle generiert wurden. Wenn wir also die Summenfunktion anwenden, erhalten wir den generierten Gesamtumsatz. Wenn wir jedoch den Umsatz überprüfen möchten, der für den organischen Kanal oder den direkten Kanal oder einen anderen generiert wurde, wird die Summenfunktion immer angewendet, wenn wir Filter in Excel für denselben Kanal anwenden Geben Sie die Gesamtsumme an
Wir möchten, dass wir beim Filtern des Kanals die Summe der sichtbaren Werte erhalten. Anstatt die SUMME-Funktion zu verwenden, verwenden wir die AGGREGATE-Funktion, um die Summe der Werte zu erhalten, die sichtbar sind, wenn ein Filter sichtbar ist angewendet.
Wenn wir also die SUMME-Formel durch eine AGGREGATE-Funktion mit dem Optionscode 5 ersetzen (wobei wir die versteckten Zeilen und Werte ignorieren), haben wir:
Wenn wir nun den Filter für verschiedene Kanäle anwenden, werden die Einnahmen für diesen Kanal nur angezeigt, wenn der Rest der Zeilen ausgeblendet wird.
Gesamtumsatz für Direct Channel:
Gesamtumsatz für Organic Channel:
Gesamtumsatz für bezahlten Kanal:
Wir können also sehen, dass die AGGREGATE-Funktion die verschiedenen Summenwerte für die Einnahmen berechnet, die für verschiedene Kanäle generiert wurden, sobald sie gefiltert wurden. Die AGGREGATE-Funktion kann also dynamisch zum Ersetzen verschiedener Funktionen für verschiedene Bedingungen verwendet werden, ohne die bedingte Formel zu verwenden.
Angenommen, für denselben Tabellenkanal und denselben Umsatz enthalten einige unserer Umsatzwerte einen Fehler. Jetzt müssen wir die Fehler ignorieren. Wenn wir einen Filter anwenden möchten, sollte die AGGREGATE-Funktion gleichzeitig auch die ausgeblendeten Zeilenwerte ignorieren.
Wenn wir Option 5 verwenden, erhalten wir den Fehler für die Summe der Gesamteinnahmen. Um die Fehler zu ignorieren, müssen wir Option 6 verwenden
Mit Option 6 erhalten wir die Summe, die die Fehlerwerte ignoriert, aber wenn wir den Filter anwenden, z. B. nach Kanalwert Direct filtern, erhalten wir dieselbe Summe, die die Fehler ignoriert, aber gleichzeitig müssen wir auch die versteckten Werte ignorieren.
In diesem Fall verwenden wir also die Option 7, bei der die Fehlerwerte und gleichzeitig die ausgeblendeten Zeilen ignoriert werden
Dinge, an die man sich erinnern sollte
- Die AGGREGATE-Funktion erkennt den Wert der Funktion _ num größer als 19 oder kleiner als 1 nicht und erkennt in ähnlicher Weise für die Optionsnummer keine Werte größer als 7 und kleiner als 1, wenn wir andere Werte angeben, die einen #WERT ergeben ! Error
- Es akzeptiert immer den numerischen Wert und gibt immer einen numerischen Wert als Ausgabe zurück
- Das AGGREGATE in Excel hat eine Einschränkung. Es ignoriert nur die ausgeblendeten Zeilen, ignoriert jedoch nicht die ausgeblendeten Spalten.
