Alle Werte summieren
| A | |
|---|---|
| 1 | Value |
| 2 | 2 |
| 3 | 4 |
| 4 | 6 |
| 5 | 8 |
Formel: C2: =AGGREGATE(9,4,A2:A5)
Ergebnis: 20
Funktion 9 ist SUM; Option 4 ignoriert nichts.
Mathematik
AGGREGAT (AGGREGATE) in Excel
AGGREGAT in Excel nutzen: Fehler und ausgeblendete Zeilen ignorieren, Funktions- und Optionscodes wählen sowie k korrekt einsetzen.
Syntax
AGGREGATE(function_num, options, ref, [k])Beispiel: =AGGREGATE(9,6,C2:C100)
Verfügbar in: Excel 2010+
AGGREGAT (AGGREGATE) führt eine Aggregation aus und kann ausgewählte Zeilen oder Fehler ignorieren. Die Syntax lautet =AGGREGATE(function_num, options, array, [k]).
Wann Sie die Funktion einsetzen
- Eine Spalte mit #N/A- oder #DIV/0!-Zellen summieren, ohne sie vorher zu bereinigen: =AGGREGATE(9,6,C2:C100).
- Den k-größten oder k-kleinsten Wert unter Ignorieren von Fehlern ermitteln, etwa mit =AGGREGATE(14,6,A2:A100,3).
- SUBTOTAL ersetzen, wenn Sie zusätzlich Funktionen wie MEDIAN oder PERCENTILE nur auf sichtbare Zeilen anwenden müssen.
Argumente
- function_num. Ein Code zur Auswahl der Aggregationsoperation.
- options. Ein Code, der festlegt, ob verschachtelte Summen, ausgeblendete Zeilen und Fehler ignoriert werden.
- array. Der zu berechnende Bereich oder das Array.
- k. Für Operationen wie LARGE, SMALL und Perzentile erforderlich.
Erklärte Beispiele
Einen Fehler überspringen
| A | |
|---|---|
| 1 | Amount |
| 2 | 3 |
| 3 | =1/0 |
| 4 | 4 |
Formel: C2: =AGGREGATE(9,6,A2:A4)
Ergebnis: 7
Option 6 ignoriert die #DIV/0!-Zelle. SUM(A2:A4) würde #DIV/0! zurückgeben.
Zweitgrößter Wert
| A | |
|---|---|
| 1 | Value |
| 2 | 2 |
| 3 | 4 |
| 4 | 6 |
| 5 | 8 |
Formel: C2: =AGGREGATE(14,4,A2:A5,2)
Ergebnis: 6
Funktion 14 ist LARGE, und k=2 verlangt den zweitgrößten Wert.
Jetzt ausprobieren
Ändern Sie eine Formel oder einen Wert in der Beispieltabelle und prüfen Sie das Ergebnis.
Formel in F2: =AGGREGATE(9,6,C2:C7)
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Region | Product | Units | Price | Result | |
| 2 | East | Pen | 120 | 2.5 | 350 | |
| 3 | West | Pen | 80 | 2.5 | ||
| 4 | East | Book | 45 | 12 | ||
| 5 | North | Book | 60 | 12 | ||
| 6 | West | Lamp | 30 | 25 | ||
| 7 | East | Lamp | 15 | 25 |
Ergebnis in F2: 350
Fehler und Prüfungen
- Falsche Funktionsnummer. Die Zahlen 1 bis 19 wählen die Funktion: 1 AVERAGE, 4 MAX, 9 SUM, 12 MEDIAN, 14 LARGE. Eine Zahl außerhalb der Liste wie 99 gibt #VALUE! zurück.
- k fehlt. Die Funktionen 14 bis 19 (LARGE, SMALL, Perzentile und Quartile) benötigen das vierte Argument; ohne dieses akzeptiert Excel die Formel nicht.
- #NUM!, wenn k zu groß ist. AGGREGATE(14,6,A2:A4,5) gibt #NUM! zurück, wenn nach dem Ignorieren der Fehler weniger als fünf Werte verbleiben.
- Fehler werden weiterhin angezeigt. Option 4 ignoriert nichts. Verwenden Sie 6 für Fehlerwerte, 5 für ausgeblendete Zeilen und 7 für beides.
Verwandte Funktionen
Häufige Fragen
- Wie ignoriere ich Fehler in einer Summe?
- Verwenden Sie =AGGREGATE(9,6,range). Option 6 überspringt Zellen, die Fehler enthalten.
- Was ist der Unterschied zwischen AGGREGATE und SUBTOTAL?
- AGGREGATE kann Fehler ignorieren und unterstützt 19 Funktionen einschließlich MEDIAN und LARGE. SUBTOTAL besitzt 11 Funktionen und kann Fehler nicht überspringen.
- Was bedeuten die Optionen?
- 0 oder ausgelassen ignoriert verschachtelte Teilergebnisse; 1 zusätzlich ausgeblendete Zeilen; 2 zusätzlich Fehler; 3 beides; 4 nichts; 5 ausgeblendete Zeilen; 6 Fehler; 7 ausgeblendete Zeilen und Fehler.