Dieses Tutorial zeigt, wie Sie die Excel-Funktionen AVERAGEIF und AVERAGEIFS in Excel und Google Sheets verwenden, um Daten zu mitteln, die bestimmte Kriterien erfüllen.
AVERAGEIF Funktionsübersicht
Sie können die AVERAGEIF-Funktion in Excel verwenden, um Zellen zu zählen, die einen bestimmten Wert enthalten, Zellen zu zählen, die größer oder gleich einem Wert sind usw.
Um die AVERAGEIF Excel-Arbeitsblattfunktion zu verwenden, wählen Sie eine Zelle aus und geben Sie Folgendes ein:
(Beachten Sie, wie die Formeleingaben angezeigt werden)
AVERAGEIF-Funktionssyntax und Argumente:
=AVERAGEIF (Bereich, Kriterien, [average_range])
Bereich - Der Bereich der zu zählenden Zellen.
Kriterien - Die Kriterien, die steuern, welche Zellen gezählt werden sollen.
durchschnittliche Reichweite - [optional] Die zu mittelnden Zellen. Wenn es weggelassen wird, wird der Bereich verwendet.
Was ist die AVERAGEIF-Funktion?
Die Funktion MITTELWERTWENN ist eine der älteren Funktionen, die in Tabellenkalkulationen verwendet werden. Es wird verwendet, um einen Bereich von Zellen zu durchsuchen, um nach einem bestimmten Kriterium zu suchen, und dann den Durchschnitt (auch bekannt als das mathematische Mittel) anzugeben, wenn die Werte in einem Bereich liegen, der diesen Werten entspricht. Die ursprüngliche AVERAGEIF-Funktion war auf nur ein Kriterium beschränkt. Nach 2007 wurde die AVERAGEIFS-Funktion geschaffen, die eine Vielzahl von Kriterien zulässt. Der Großteil der allgemeinen Verwendung bleibt zwischen den beiden gleich, aber es gibt einige kritische Unterschiede in der Syntax, die wir in diesem Artikel besprechen werden.
Falls noch nicht geschehen, können Sie viele ähnliche Strukturen und Beispiele im Artikel ZÄHLENWENN nachlesen.
Einfaches Beispiel
Betrachten wir diese Liste der aufgezeichneten Verkäufe, und wir möchten das durchschnittliche Einkommen kennen.
Da wir eine Ausgabe hatten, den negativen Wert, können wir nicht einfach einen einfachen Durchschnitt erstellen. Stattdessen möchten wir nur die Werte mitteln, die größer als 0 sind. „größer als 0“ ist unser Kriterium in einer AVERAGEIF-Funktion. Unsere Formel, um dies zu sagen, ist
=MITTELWERTWENN(A2:A7, ">0")
Zweispaltiges Beispiel
Während die ursprüngliche AVERAGEIF-Funktion dazu gedacht war, ein Kriterium auf den zu summierenden Zahlenbereich anzuwenden, müssen Sie meistens ein oder mehrere Kriterien auf andere Spalten anwenden. Betrachten wir diese Tabelle:
Wenn wir nun die ursprüngliche AVERAGEIF-Funktion verwenden, um herauszufinden, wie viele Bananen wir im Durchschnitt haben. Wir geben unsere Kriterien in Zelle D1 ein und müssen den gewünschten Bereich angeben Durchschnitt als letztes Argument, und so wäre unsere Formel
=MITTELWERTWENN(A2:A7, D1, B2:B7)
Als die Programmierer jedoch schließlich erkannten, dass Benutzer mehr als ein Kriterium angeben wollten, wurde die Funktion MITTELWERTIFS erstellt. Um eine Struktur zu erstellen, die für eine beliebige Anzahl von Kriterien geeignet ist, erfordert der AVERAGEIFS, dass der Summenbereich zuerst aufgeführt wird. In unserem Beispiel bedeutet dies, dass die Formel
=MITTELWERTWENN(B2:B7, A2:A7, D1)
HINWEIS: Diese beiden Formeln liefern dasselbe Ergebnis und können ähnlich aussehen. Achten Sie daher genau darauf, welche Funktion verwendet wird, um sicherzustellen, dass Sie alle Argumente in der richtigen Reihenfolge auflisten.
Arbeiten mit Datumsangaben, mehreren Kriterien
Beim Arbeiten mit Datumsangaben in einer Tabellenkalkulation ist es zwar möglich, das Datum direkt in die Formel einzugeben, es empfiehlt sich jedoch, das Datum in einer Zelle zu haben, damit Sie einfach auf die Zelle in einer Formel verweisen können. Dies hilft dem Computer beispielsweise zu wissen, dass Sie das Datum 27.05.2020 und nicht die Zahl 5 geteilt durch 27 geteilt durch 2022 verwenden möchten.
Schauen wir uns unsere nächste Tabelle an, in der alle zwei Wochen die Anzahl der Besucher einer Website aufgezeichnet wird.
Wir können den Start- und Endpunkt des Bereichs angeben, den wir in D2 und E2 betrachten möchten. Unsere Formel, um den Durchschnitt der Besucherzahl in diesem Bereich zu ermitteln, könnte dann lauten:
=MITTELWERTWENN(B2:B7, A2:A7, ">="&D2, A2:A7, "<="&E2)
Beachten Sie, wie wir die Vergleiche von „=“ mit den Zellbezügen verketten konnten, um die Kriterien zu erstellen. Auch wenn beide Kriterien auf denselben Zellbereich (A2:A7) angewendet wurden, müssen Sie den Bereich zweimal schreiben, einmal pro Kriterium.
Mehrere Spalten
Wenn Sie mehrere Kriterien verwenden, können Sie sie auf denselben Bereich anwenden, wie wir es im vorherigen Beispiel getan haben, oder Sie können sie auf verschiedene Bereiche anwenden. Lassen Sie uns unsere Beispieldaten in dieser Tabelle kombinieren:
Wir haben einige Zellen eingerichtet, damit der Benutzer in die Zellen E2 bis G2 eingeben kann, wonach er suchen möchte. Wir brauchen also eine Formel, die die Gesamtzahl der im Februar gepflückten Äpfel addiert. Unsere Formel sieht so aus:
=MITTELWERTWENN(C2:C7, B2:B7, ">="&F2, B2:B7, "<="&G2, A2:A7, E2)
AVERAGEIFS mit OR-Logik
Bis zu diesem Punkt waren die von uns verwendeten Beispiele alle UND-basierte Vergleiche, bei denen wir nach Zeilen suchen, die alle unsere Kriterien erfüllen. Nun betrachten wir den Fall, wenn Sie nach der Möglichkeit suchen möchten, dass eine Zeile das eine oder andere Kriterium erfüllt.
Schauen wir uns diese Verkaufsliste an:
Wir möchten die durchschnittlichen Verkäufe von Adam und Bob addieren. Zuerst eine kurze Diskussion über das Nehmen von Durchschnittswerten. Wenn Sie eine ungerade Anzahl von Dingen haben, z. B. 3 Einträge für Adam und 2 für Bob, können Sie nicht einfach den Durchschnitt der Verkäufe jeder Person nehmen. Dies wird als Durchschnitt der Durchschnittswerte bezeichnet, und Sie geben dem Artikel mit wenigen Einträgen eine unfaire Gewichtung. Wenn dies bei Ihren Daten der Fall ist, müssen Sie den Durchschnitt „manuell“ berechnen: Nehmen Sie die Summe aller Ihrer Artikel geteilt durch die Anzahl Ihrer Artikel. Um zu erfahren, wie das geht, können Sie die Artikel hier lesen:
Wenn die Anzahl der Einträge gleich ist, wie in unserer Tabelle, haben Sie mehrere Möglichkeiten. Am einfachsten ist es, zwei AVERAGEIFS zusammenzuzählen und dann durch 2 zu dividieren (die Anzahl der Elemente in unserer Liste).
=(MITTELWERTWENN(B2:B7, A2:A7, "Adam")+MITTELWERTWENN(B2:B7, A2:A7, "Bob"))/2
Hier haben wir den Computer unsere individuellen Punktzahlen berechnen lassen und dann addieren wir sie.
Unsere nächste Option ist gut, wenn Sie mehr Kriterienbereiche haben, sodass Sie nicht die gesamte Formel wiederholt neu schreiben müssen. In der vorherigen Formel haben wir den Computer manuell angewiesen, zwei verschiedene AVERAGEIFS zusammenzuzählen. Sie können dies jedoch auch tun, indem Sie Ihre Kriterien wie folgt in ein Array schreiben:
= MITTELWERT(MITTELWERTWENN(B2:B7, A2:A7, {"Adam", "Bob"}))
Sehen Sie sich an, wie das Array innerhalb der geschweiften Klammern aufgebaut ist. Wenn der Computer diese Formel auswertet, weiß er, dass wir für jedes Element in unserem Array eine AVERAGEIFS-Funktion berechnen möchten, wodurch ein Array von Zahlen erstellt wird. Die äußere AVERAGE-Funktion nimmt dann dieses Zahlenarray und wandelt es in eine einzelne Zahl um. Wenn Sie die Formelauswertung durchgehen, würde sie so aussehen:
=DURCHSCHNITT(DURCHSCHNITTWENN(B2:B7, A2:A7, {"Adam", "Bob"})) =MITTEL(13701, 21735) =17718
Wir erhalten das gleiche Ergebnis, aber wir konnten die Formel etwas prägnanter formulieren.
Umgang mit Leerzeichen
Manchmal enthält Ihr Datensatz leere Zellen, die Sie entweder finden oder vermeiden müssen. Das Einrichten der Kriterien dafür kann etwas schwierig sein, also schauen wir uns ein anderes Beispiel an.
Beachten Sie, dass Zelle A3 wirklich leer ist, während Zelle A5 eine Formel enthält, die eine Null-Länge-Zeichenfolge von "" zurückgibt. Wenn wir den Gesamtdurchschnitt von wirklich leere Zellen, würden wir ein Kriterium von „=“ verwenden und unsere Formel würde so aussehen:
=MITTELWERTWENN(B2:B7,A2:A7,"=")
Auf der anderen Seite, wenn wir den Durchschnitt für alle Zellen erhalten möchten, die visuell leer aussehen, ändern wir das Kriterium auf „“, und die Formel sieht so aus
=MITTELWERTWENN(B2:B7,A2:A7,"")
Drehen wir es um: Was ist, wenn Sie den Durchschnitt der nicht leeren Zellen ermitteln möchten? Leider lässt Sie das aktuelle Design die Null-Länge-Zeichenfolge nicht vermeiden. Sie können ein Kriterium von „“ verwenden, aber wie Sie im Beispiel sehen können, enthält es immer noch den Wert aus Zeile 5.
=MITTELWERTWENN(B2:B7,A2:A7,"")
Wenn Sie keine Zellen zählen müssen, die Zeichenfolgen der Länge Null enthalten, sollten Sie die LEN-Funktion in einem SUMPRODUCT verwenden
MITTELWERTWENN in Google Tabellen
Die AVERAGEIF-Funktion funktioniert in Google Sheets genauso wie in Excel:
