Excel-TippAnzahl der Daten in einem Intervall ermitteln
Eine häufige Anforderung in der Praxis ist, dass Sie Zellen in einer Liste zählen müssen, deren Wert zwischen zwei Zahlen (Intervall) liegt. Hierfür können Sie die Funktion ZÄHLENWENNS() in Excel einsetzen.
Beispiel: Anzahl Einträge zählen, die in einem Intervall liegen
In der folgenden Abbildung sehen Sie eine Liste mit Daten in Spalten A und B. Darin sind Personen und die Punkte einer Prüfung pro Person dargestellt.
Im Bereich D1:F5 sollen nun die entsprechenden Punktintervalle aus der Liste gezählt werden. In der Zelle F2 soll die Anzahl der Prüflinge ermittelt werden, die eine Punktzahl von 61 bis 70 Punkte haben.
Für diese Aufgabe können Sie die Funktion ZÄHLENWENNS() einsetzen. Erfassen Sie für das Beispiel in Zelle F2 die folgende Formel:
=ZÄHLENWENNS($B$2:$B$13;">="&D2;$B$2:$B$13;"<="&E2)
Erfassen Sie den Listenbereich B2:B13 in der Formal als absoluten Bezug (mit $-Zeichen), damit Sie die Formel ohne Probleme in die Zellen F3 bis F5 kopieren können.
Tipp: Wenn Sie in der Bearbeitungsleiste mit der linken Maustaste auf einen Bezug klicken und anschließend die F4-Taste drücken, dann können Sie mit jedem Drücken der F4-Taste die Bezugsarten zwischen absoluten und relativen Bezügen automatisch wechseln.
Beachten Sie: Festlegen der Intervallgrenzen
Die Intervallgrenzen in den Spalten D und E müssen eindeutig sein. Die Intervalle oder Zahlenbereiche dürfen sich nicht überschneiden. Ansonsten werden Einträge in der Liste möglicherweise doppelt gezählt.
Gleichzeitig müssen Sie darauf achten, dass mit der Intervalleingabe alle möglichen Listeneinträge erfasst werden. Käme beispielsweise die Punktzahl 70,5 vor, würde dieser Eintrag nicht berücksichtigt.
Sie können dies vermeiden, indem Sie die Formel ein wenig ändern und die Obergrenze des vorhergehenden Intervalls als Untergrenze des nächsten Intervalls nutzen. Die Formel in Zelle F3 lautet dann:
=ZÄHLENWENNS($B$2:$B$13;">"&E2;$B$2:$B$13;"<="&E3)
Und in den Zellen F4 und F5 entsprechend.
Wie geht ZÄHLENWENNS() bei der Intervallauswertung vor?
Die Funktion ZÄHLENWENNS() akzeptiert Kriterien in Bereichs- oder Kriterienpaaren. Im Gegensatz zu ZÄHLENWENN() können Sie mehrere Kriterien definieren.
Mit dem ersten Bereichs-/ Kriterienpaar suchen und zählen Sie die Werte in Spalte B (Punktzahl), die größer oder gleich (>=) dem „von“-Wert in Spalte D sind:
=ZÄHLENWENNS($B$2:$B$13;">="&D2;$B$2:$B$13;"<="&E2)
Das zweite Bereichs-/ Kriterienpaar sucht und zählt die Einträge, die kleiner oder gleich (<=) dem „bis“-Wert in Spalte E sind:
=ZÄHLENWENNS($B$2:$B$13;">="&D2;$B$2:$B$13;"<="&E2)
Die Zellen, bei denen beide Kriterien zutreffen, werden gezählt und als Wert in die Zelle F2 zurückgegeben.
Alternative Funktion SUMMENPRODUKT()
Eine Alternative zu ZÄHLENWENNS() ist die Funktion SUMMENPRODUKT(), mit der Sie diese Aufgabe ebenfalls meistern können. Erfassen Sie hierzu die folgende Formel in die Zelle F2:
=SUMMENPRODUKT(($B$2:$B$13>=D2)*($B$2:$B$13<=E2))
Erfassen Sie auch hier den Listenbereich, in welchem die Punkte aufgelistet sind in absoluten Bezügen, damit Sie die Formel nach unten kopieren können.
Wie rechnet die Formel SUMMENPRODUKT()?
Im ersten Teil der Formel =SUMMENPRODUKT(($B$2:$B$13>=D2)*($B$2:$B$13<=E2)) wird jede Zelle im Bereich B2:B13 überprüft, ob der Wert >=D2 ist. Ist die Bedingung wahr, dann wird eine 1 sonst eine 0 als Ergebnis zurückgegeben.
Das Gleiche passiert mit dem zweiten Teil der Formel. Hier wird durch =SUMMENPRODUKT(($B$2:$B$13>=D2)*($B$2:$B$13<=E2)) jede einzelne Zelle im Bereich B2:B13 überprüft, ob diese <= dem Wert in der Zelle E2 ist. Ist die Bedingung wahr, dann wird eine 1 sonst eine 0 als Ergebnis zurückgegeben.
Anschließende werden die Ergebnisse der beiden Überprüfungen miteinander multipliziert und das Ergebnis summiert.
Nur bei den Paaren, bei denen bei beiden eine 1 als Prüfergebnis zurückgegeben wurde (beide Bedingungen sind erfüllt), ergibt sich nach der Multiplikation eine 1 (=1*1) als Wert, der anschließend in die Summierung eingeht.
