Bedingungen mit AVERAGEIFS kombinieren

AVERAGEIFS berechnet den Mittelwert von Werten, die gleichzeitig mehrere Bedingungen erfüllen. Statt den Mittelwert aller Verkäufe zu berechnen, können Sie fragen: Wie hoch war der durchschnittliche Verkauf aus der Region Ost im Januar?

Damit ist das Trio vollständig: SUMIFS summiert, COUNTIFS zählt und AVERAGEIFS berechnet den Mittelwert — alle drei Funktionen verwenden denselben Stil mit mehreren Kriterien. Wenn Sie eine davon kennen, kennen Sie fast alle drei.

AVERAGEIFS entspricht SUMIFS genau. Die zu mittelnden Werte kommen zuerst, danach die Kriterienpaare.

• average_range — die zu mittelnden Zahlen
• criteria_range1, criteria1
• criteria_range2, criteria2

Intern summiert die Funktion die passenden Werte und teilt sie durch die Anzahl der Treffer – im Grunde also SUMIFS geteilt durch COUNTIFS, zusammengefasst in einer übersichtlichen Funktion.

Wenn Spalte A die Region, Spalte B den Monat und Spalte C den Betrag enthält, können Sie den durchschnittlichen Verkauf der Region East im Januar ermitteln.

Die Beträge in C:C werden gemittelt. Die beiden Kriterienpaare filtern die Zeilen zunächst auf East und Januar, bevor der Mittelwert berechnet wird.

Sie können die zu mittelnden Werte anhand ihrer eigenen Größe filtern. Angenommen, Sie möchten nur den Durchschnitt der großen Verkäufe in der Region East ermitteln – also der Verkäufe über 100.

Hier wird Spalte C sowohl als average_range als auch als criteria_range verwendet. AVERAGEIFS kann dieselbe Spalte problemlos für beide Aufgaben verwenden.

Wenn Benutzer einen Bericht steuern können sollen, verweisen Sie auf Zellen, statt Werte direkt einzugeben. Tragen Sie die Region in F1 und einen Mindestbetrag in F2 ein.

Für den Vergleich verketten Sie den Operator mit der Zelle: ">"&F2 bedeutet größer als der in F2 enthaltene Wert. Übereinstimmungen mit einfachem Text wie bei der Region benötigen keinen Operator, sondern nur den Zellbezug.

Die größte Stolperfalle bei AVERAGEIFS: Wenn keine Zeilen alle Ihre Bedingungen erfüllen, gibt es nichts zu mitteln. Daher gibt die Funktion einen #DIV/0!-Fehler zurück.

Das unterscheidet sich von SUMIFS (gibt 0 zurück) und COUNTIFS (gibt ebenfalls 0 zurück). Der Mittelwert von null Elementen ist nicht definiert. Deshalb gibt Excel einen Fehler aus, statt einen Wert zu erraten.

Schließen Sie die Formel in IFERROR ein, um einen verständlichen Wert anzuzeigen, wenn keine Treffer vorliegen. Statt eines unschönen #DIV/0! sieht der Benutzer einen Gedankenstrich oder eine Meldung.

So bleiben Dashboards übersichtlich, selbst wenn eine Filterkombination keine Daten liefert. Berücksichtigen Sie beim Mitteln gefilterter Daten immer auch den Fall einer leeren Ergebnismenge.

Eine wichtige Besonderheit: AVERAGEIFS ignoriert leere Zellen im average_range – sie werden weder zur Summe noch zum Divisor gezählt. Eine Zelle mit dem Wert 0 enthält dagegen eine echte Zahl und wird einbezogen.

Wenn Nullen als Platzhalter für fehlende Daten dienen, ziehen sie den Durchschnitt nach unten. Fügen Sie bei Bedarf ein Kriterium wie C:C, "<>0" hinzu, um sie auszuschließen.

Fügen Sie so viele Bedingungen hinzu, wie Sie benötigen. Wenn Spalte D den Verkäufer enthält, können Sie den durchschnittlichen Verkauf ermitteln, der aus der Region East stammt, im Januar erfolgte und von Maria getätigt wurde.

Jedes zusätzliche Paar schränkt den Filter weiter ein. Da AVERAGEIFS die UND-Logik verwendet, fließen nur Zeilen, die alle drei Bedingungen erfüllen, in den Mittelwert ein.

Behalten Sie die beiden Reihenfolgen der Argumente im Blick:

• AVERAGEIF: range, criteria, [average_range] — der Prüfbereich kommt zuerst, average_range ist optional und steht am Ende.
• AVERAGEIFS: average_range, range1, crit1, ... — average_range steht immer an erster Stelle.

Wie bei den anderen Funktionen erhalten Sie ein einheitliches Muster für SUM, COUNT und AVERAGE, wenn Sie standardmäßig die Pluralversion ...IFS verwenden.

AVERAGEIFS eignet sich für viele Gegenüberstellungen. Führen Sie die Regionsnamen in Spalte F auf und berechnen Sie den Durchschnittsverkauf jeder Region mit einer einzigen ausfüllbaren Formel, indem Sie die Kriterien-Spalte fixieren.

Mit $C:$C als average_range, $A:$A als criteria_range und $F2 als relativem Bezug auf die Region erhalten Sie durch Kopieren der Formel nach unten sofort einen Durchschnitt pro Region.

Rufen Sie sich in Erinnerung, wodurch sich AVERAGEIFS von den verwandten Funktionen unterscheidet.

Sie können jetzt Werte anhand mehrerer Bedingungen mitteln:

• AVERAGEIFS(average_range, range1, crit1, ...) — average_range steht an erster Stelle.
• Keine Treffer ergeben #DIV/0! — fangen Sie diesen Fall mit IFERROR ab.
• Leere Zellen werden übersprungen, aber Nullen werden einbezogen. Schließen Sie sie bei Bedarf mit "<>0" aus.
• Verwenden Sie ">"&F1 für dynamische numerische Grenzen.

Als Nächstes: Datumsbereiche in diesen Kriterienfunktionen verarbeiten.