AGGREGATE-Funktion in Excel
AGGREGATE Die 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-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 ist ein numerischer Wert oder Werte, für die wir die Berechnung durchführen möchten. Es sind mindestens zwei Argumente erforderlich. Restargumente sind optional.
Array: ist ein Array von Werten, mit denen wir arbeiten 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 wie LARGE, SMALL, PERCENTILE.EXC, QUARTILE.INC, PERCENTILE.INC oder QUARTILE.EXC in Excel verwendet wird.
Beispiele
Beispiel 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. Im Durchschnitt ist function_ num 1
In Spalte C werden alle Werte angegeben, und wir müssen keine Werte ignorieren, damit wir Option 4 auswählen (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 auf Lassen Sie den Wert von k weg.
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 eine Fehlermeldung angezeigt. Wenn jedoch eine geeignete Option verwendet wird, gibt das AGGREGATE in Excel den Durchschnitt der verbleibenden Werte an, wobei der Fehlerwert in E6 vernachlässigt wird.
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 verborgene 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 Kanal generiert wurde, wenden wir Filter in Excel für denselben, die Summenfunktion, an wird immer die Gesamtsumme geben
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 angewendet wird.
Wenn wir also die SUMME-Formel durch eine AGGREGATE-Funktion mit dem Optionscode 5 ersetzen (wobei die verborgenen Zeilen und Werte ignoriert werden), 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 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. Daher kann die AGGREGATE-Funktion dynamisch verwendet werden, um verschiedene Funktionen für verschiedene Bedingungen zu ersetzen, ohne die bedingte Formel zu verwenden.
Angenommen, für dieselbe Tabelle 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, wobei die Fehlerwerte ignoriert werden. Wenn wir den Filter anwenden, z. B. nach Kanalwert Direkt filtern, erhalten wir dieselbe Summe, wobei die Fehler ignoriert werden, aber gleichzeitig müssen wir auch die versteckten Werte ignorieren.
In diesem Fall verwenden wir also 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. In ähnlicher Weise identifiziert sie für die Optionsnummer keine Werte größer als 7 und kleiner als 1; Wenn wir andere Werte angeben, erhalten Sie einen #WERT! 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.
