Möglicherweise sind Sie bereits mit der Standard-AVERAGE-Funktion in Excel vertraut. Es gibt jedoch Fälle, in denen Sie Bedingungen auf diese Durchschnittsberechnung anwenden müssen. Genau hier kommt die AVERAGEIF-Funktion ins Spiel und ermöglicht es Ihnen, bestimmte Kriterien nahtlos in Ihre Durchschnittsberechnungen einzubeziehen. In diesem umfassenden Leitfaden erfahren Sie, wie Sie die Excel-Funktion AVERAGEIF effektiv einsetzen und Ihre Datenanalyse auf die nächste Stufe heben. Nutzen Sie die leistungsstarken Formeln in Excel, um Ihre Tabellenkalkulationen zu optimieren.
Was ist die AVERAGEIF-Funktion in Excel und wie wird sie verwendet?
Die AVERAGEIF-Funktion in Excel erleichtert Benutzern die Berechnung des Durchschnitts eines bestimmten Zellbereichs, der eine spezifische Bedingung erfüllt. Dies ist besonders nützlich, wenn Sie nicht alle Datenpunkte in eine Durchschnittsberechnung einbeziehen möchten, sondern nur jene, die bestimmte Kriterien erfüllen. Die Syntax für die Implementierung der Excel-Funktion AVERAGEIF besteht aus drei Argumenten und lautet wie folgt:
Syntax: AVERAGEIF(Bereich, Kriterien, [Mittelwertbereich])
Argumente:
- Bereich: Dies ist der Zellbereich, den Sie anhand eines bestimmten Kriteriums analysieren möchten. Die Funktion prüft jede Zelle in diesem Bereich gegen die definierten Kriterien.
- Kriterien: Dies beschreibt die Bedingung, die Zellen in einem bestimmten Bereich erfüllen müssen, um in die Berechnung des Durchschnitts einbezogen zu werden. Dies kann ein Wert, ein Textausdruck, eine Formel oder ein Zellbezug sein.
- Mittelwertbereich (optional): Dies ist der Zellbereich, dessen Durchschnitt Sie tatsächlich berechnen möchten. Wenn dieses Argument weggelassen wird, verwendet die Funktion den
Bereich-Parameter auch als Mittelwertbereich.
Die ersten beiden Argumente (Bereich und Kriterien) sind obligatorisch, während das dritte (Mittelwertbereich) optional ist.
Um ein tieferes Verständnis der Excel-Funktion AVERAGEIF zu erlangen, demonstrieren wir ein praktisches Beispiel. Stellen Sie sich vor, Sie haben in Spalte A einen Datensatz über mehrere Verkaufsjahre eines bestimmten Automodells und in Spalte B die entsprechende Anzahl verkaufter Einheiten.
Mit diesen Daten möchten wir die durchschnittliche Anzahl der pro Jahr verkauften Autos ermitteln. Während die einfache Excel-Durchschnittsfunktion den grundlegenden Durchschnitt liefern kann, was wäre, wenn wir nur die Jahre berücksichtigen möchten, in denen die Anzahl der verkauften Einheiten 10 überstieg? Genau hier kommt die Excel-Funktion AVERAGEIF ins Spiel. Durch das Anwenden von Bedingungen auf unsere Durchschnittsberechnung können wir tiefere Einblicke in unsere Daten gewinnen.
Schritt 1: Klicken Sie auf eine leere Zelle, in der der Durchschnitt berechnet werden soll (z. B. B10).
Schritt 2: Geben Sie als Nächstes die Excel-AVERAGEIF-Formel in die leere Zelle B10 ein. Stellen Sie sicher, dass Sie die Formel mit einem Gleichheitszeichen beginnen.
Schritt 3: Wählen Sie mit dem Cursor den Bereich für die AVERAGEIF-Funktion aus; d. h. B2:B9. Dieser Bereich enthält die Werte, die anhand der Kriterien bewertet werden.
Schritt 4: Wir möchten, dass unser Durchschnitt nur die Jahre umfasst, in denen die Anzahl der verkauften Einheiten 10 übersteigt. Das Kriterium für unsere Funktion wird daher ">10" sein. Achten Sie auf die Anführungszeichen um den Vergleichsoperator und den Wert.
Schritt 5: Schließen Sie die Klammern und drücken Sie “Eingabe”, damit Excel die durchschnittliche Anzahl der pro Jahr verkauften Einheiten berechnen kann, die das Kriterium erfüllen.
Erweiterte Tipps zur Verwendung der AVERAGEIF-Funktion
Bei der Nutzung der AVERAGEIF-Funktion gibt es einige wichtige Aspekte, die Sie beachten sollten, um Fehler zu vermeiden und präzise Ergebnisse zu erzielen. Hier ein kurzer Überblick über diese Überlegungen:
TRUE- und FALSE-Werte: Die Funktion berücksichtigt bei der Berechnung nur numerische Werte und ignoriert daher alle logischen Werte (WAHR oder FALSCH) im Bereich. Diese werden einfach übersprungen.
Leere Zellen: Die Funktion berücksichtigt bei der Berechnung des Durchschnitts keine leeren Zellen im Bereich, sodass diese keinen Einfluss auf Ihr Durchschnittsergebnis haben. Sie werden stillschweigend ignoriert.
#DIV/0! Fehler: Die Funktion gibt einen
#DIV/0!-Fehler zurück, wenn keine der alsBereichausgewählten Zellen die definiertenKriterienerfüllt. Dies bedeutet, dass keine Werte vorhanden sind, die gemittelt werden könnten, und eine Division durch Null die Folge ist.Spezifischer Bereich: Die Funktion benötigt einen bestimmten Zellbereich als Argument; ein Array kann stattdessen nicht direkt ersetzt werden.
Mittelwertbereich-Berechnung: Mit dieser Funktion können Sie einen anderen Zellbereich für die Berechnung des Durchschnitts auswählen, der auch größer sein kann als der für die Kriterien verwendete Hauptbereich. Die Berechnung beginnt in der oberen linken Zelle des
Mittelwertbereichs, und nur die übereinstimmenden Zellen (basierend auf denKriterien) aus beiden Bereichen werden gemittelt. Nicht übereinstimmende Zellen werden bei der Durchschnittsberechnung ignoriert.Platzhalter und Groß-/Kleinschreibung beachten: Die Funktion unterstützt die Verwendung von Platzhaltern (
*für eine beliebige Zeichenfolge und?für ein einzelnes Zeichen) für den flexiblen Abgleich und behandelt Groß- und Kleinbuchstaben bei der Auswertung von Kriterien als gleich (nicht zwischen Groß- und Kleinschreibung unterscheiden). Diese wichtige Excel-Funktionen erweitern Ihre Möglichkeiten bei der Datenanalyse erheblich.
Beispiele für die Verwendung der AVERAGEIF-Funktion in Excel
Wenn Sie wissen, wie logische Operatoren (>, <, >=, <=, =, <>) und Platzhalter (*, ?) in den Kriterien verwendet werden, können Sie komplexere und flexiblere bedingte Mittelung Operationen durchführen.
Hier ist eine Tabelle zur Veranschaulichung der Kriterien für die Verwendung der AVERAGEIF-Funktion:
| Ziel | Kriterien |
|---|---|
| Zellen gleich hundert | 100 oder "100" |
| Zellen größer als 75 | ">75" |
| Zellen, die nicht leer sind | "<>" |
| Zellen, die leer sind | "" (leere Anführungszeichen) |
| Zellen ungleich „Rot“ | "< >rot" |
| Zellen gleich „Rot“ | "Rot" |
| Zellen kleiner oder gleich 100 | "< =100" |
| Zellen, die mit „X“ beginnen | "X*" |
| Zellen kleiner als der Wert in Zelle A1 | "<"&A1 |
| Zellen kleiner als das heutige Datum | "<"&HEUTE() |
Betrachten wir einige weitere Beispiele für ein umfassendes Verständnis der AVERAGEIF-Funktion in verschiedenen Szenarien:
Verwendung der AVERAGEIF-Funktion mit Textkriterien
Hier ist ein einfaches Beispiel für die Verwendung der Excel AVERAGEIF-Funktion mit Textkriterien. Nehmen wir an, Sie haben eine Liste von Schülern und deren entsprechenden Testergebnisse:
Wenn wir nun die durchschnittliche Punktzahl aller Schüler berechnen möchten, die über 80 Punkte erzielt haben, verwenden wir die Excel AVERAGEIF-Funktion mit ">80" als Textkriterium.
Schritt 1: Klicken Sie auf eine leere Zelle, z. B. B7, und geben Sie die AVERAGEIF-Formel ein.
Schritt 2: Wir wählen die Prüfungsergebnisse der 5 Schüler als unseren Bereich aus (B2:B6).
Schritt 3: Als unsere Kriterien geben wir ">80" ein, da wir möchten, dass die AVERAGEIF-Funktion ein Testergebnis über 80 einbezieht. Stellen Sie sicher, dass die Kriterien in Anführungszeichen eingegeben werden.
Schritt 4: Drücken Sie “Eingabe” nach dem Schließen der Klammern. Excel zeigt die durchschnittliche Punktzahl der Studenten ohne Noten unter 80 an; Marys Punktzahl von 78 wird von den Berechnungen ausgeschlossen.
Verwendung von AVERAGEIF mit Datumsangaben
Die AVERAGEIF-Funktion kann auch effektiv mit Datumsangaben verwendet werden, um zeitbasierte Durchschnittswerte zu berechnen. Nehmen wir an, Sie haben eine Liste mit Verkaufsdaten, die das Datum und die entsprechenden Verkaufsbeträge enthalten:
Wir können die Excel AVERAGEIF-Funktion nutzen, um die durchschnittlichen Verkäufe von einem bestimmten Datum (z. B. 1. Februar 2023) bis zum aktuellen Datum zu berechnen. Unser Kriterium wird ">01.02.2023" sein.
Schritt 1: Wählen Sie eine leere Zelle aus, um die Durchschnittsberechnung durchzuführen, und geben Sie die AVERAGEIF-Formel ein.
Schritt 2: Wir werden die Datumsspalte als unseren Bereich für die AVERAGEIF-Funktion auswählen (A2:A6). Da unsere Kriterien auf dem Datum basieren, wird die Datumsspalte zu unserem Zielbereich für die Bewertung.
Schritt 3: Für unser Kriterien-Argument geben wir ">01.02.2023" ein. Stellen Sie sicher, dass das Datumsformat korrekt befolgt wird (MM/TT/JJJJ oder TT.MM.JJJJ, je nach Ihren Excel-Einstellungen).
Schritt 4: Für unser letztes Argument, den Mittelwertbereich, wählen wir die Spalte “Verkaufsmenge” aus (B2:B6). Dadurch können wir den Durchschnitt basierend auf den Werten in der Spalte „Umsätze“ berechnen, aber nur für die Daten, die unsere Kriterien erfüllen.
Schritt 5: Drücken Sie “Eingabe”, um die Ergebnisse anzuzeigen.
Der durchschnittliche Umsatz von Februar bis zum aktuellen Datum wird nun angezeigt.
Verwendung von AVERAGEIF mit Kriterien „größer als 0“
In vielen Datensätzen sind Nullen oder leere Zellen für Durchschnittsberechnungen irrelevant. Betrachten wir einen Datensatz mit den folgenden Verkaufszahlen für verschiedene Produkte, um zu verstehen, wie die Excel-Funktion AVERAGEIF mit Kriterien „größer als 0“ verwendet werden kann:
Wir möchten die durchschnittliche Verkaufsmenge für Produkte ermitteln, deren Umsätze größer als 0 sind. Dazu können wir die Funktion AVERAGEIF mit dem Kriterium ">0" verwenden.
Schritt 1: Wählen Sie eine leere Zelle, wo Sie die Durchschnittsberechnung durchführen möchten.
**