Zählen mit Tabellen-Kalkulation


Die Funktion ANZAHL() und einige verwandte Funktionen sind die einfachsten dieser Kategorie: Sie zählen, wieviele Zellen des untersuchten Bereichs eine bestimmte Bedingung erfüllen.

Auf dieser Seite werden einige Beispiele von Zähler-Funktionen vorgestellt. Sie lassen sich leicht auf andere Aggregat-Funktionen übertragen, weil alle Funktionen dieser Gruppe ähnlich angewendet werden.


Verwandte Themen auf anderen Seiten


Zähl-Funktionen der Tabellen-Kalkulation


Es gibt fertig programmierte Funktionen für häufig benötigte Bedingungen, zusätzlich mehrere Möglichkeiten zum Zählen unter selbst programmierrten Bedingungen.


Funktion  ANZAHL2()

Gibt an, wieviele Nicht-leere Zellen in einem Bereich enthalten sind.
Die Funktion erwartet einen Bereich als Argument, z.B.
=ANZAHL2(A1:B10)
Es werden sowohl Zahlenwerte als auch Text-Werte (Strings) gezählt.


Anzahl der Zellen

Derzeit gibt es offenbar keine Funktion, welche die Anzahl der Zellen eines Bereichs ohne Bedingung zurückgibt. Dazu kann man eine Kombination mehrerer Funktionen benutzen, z.B.
=ANZAHL2(A1:B10)+ANZAHLLEEREZELLEN(A1:B10)
=ZEILEN(A1:B10)*SPALTEN(A1:B10)
Alternativ kann man eine ↓ Basic-Funktion verwenden:
=Anzahl_Zellen(A1:B10)


Datenbank-Funktionen

Diese Funktionen haben mit einer Datenbank wenig gemeinsam, aber sie sind gut geeignet, um in einer Tabelle nach verschiedenen selbst programmierten Bedingungen zu suchen und zu zählen.

Alle Funktionen dieser Kategorie arbeiten nach dem gleichen Muster, das im Kapitel ↓ Datenbank-Funktionen vorgestellt wird.

Als typischer Vertreter dieser Kategorie wird die Funktion DBANZAHL() vorgestellt: Sie zählt die Anzahl der Treffer nach einer oder mehreren Bedingungen (Such-Kriterien). Die Funktionen dieser Kategorie erlauben auch die Verknüpfung mehrerer Bedingungen, z.B. mit logisch UND oder ODER


Frei programmierte Zähler

Alternativ kann man einen Zähl-Vorgang nach beliebigen Bedingungen selbst programmieren.

Zur 'Bedingten Verzweigung' verwendet man eine der Funktionen WENN(), WENNFEHLER(), WNNNV() zusammen mit einer frei programmierten Bedingung.

Zum Formulieren der Bedingung werden meistens die Vergleichs-Operatoren < = > <= >= <> verwendet.
Gut geeignet sind auch die 'Informations-Funktionen', z.B. ISTBEZUG(), ISTFEHLER(), ISTFORMEL(), ISTLEER(), ISTTEXT(), ISTZAHL()

Zur Verknüpfung mehrerer Bedingungen verwendet man die 'Logischen Funktionen' NICHT(), ODER(), UND(), XODER()

Standard-Auswertung

Anwendung der oft gebrauchten Aggregat-Funktionen wie ANZAHL(), SUMME(), MITTELWERT(), MIN(), MAX() auf einen Bereich von auszuwertenden Zahlen-Daten.

Zähler mit vor-programmierten Bedingungen

Auswertung des links gezeigten Werte-Bereichs mit einigen einfachen Funktionen:
 AB
1Zahlen=ANZAHL(A15:A34)
2Werte =ANZAHL2(A15:A34)
3Leere Zellen=ANZAHLLEEREZELLEN(A15:A34)
4Zellen=B2+B3
5=ZEILEN(A15:B34)*SPALTEN(A15:A34)
6=Anzahl_Zellen(A15:A34)

Funktion ANZAHL() zählt nur Zahlen, keine Texte (Strings) oder leeren Zellen.
Funktion ANZAHL2() zählt nur Zellen mit Werten, keine leeren Zellen.
Alle angeführten Funktionen arbeiten auch mit mehrspaltigen Bereichen (z.B. A15:C34)
Der Werte-Bereich wurde hier zur besseren Übersicht einspaltig und kurz gehalten.

Häufig wird eine Daten-Tabelle am unteren Ende durch weitere Daten ergänzt (hier z.B. in A35, A36 usw.).
Leider wird oft darauf vergessen, den Bereich in allen Auswertungs-Funktionen zu vergrößern. Wenn dieser Fehler unbemerkt bleibt, dann ist das Ergebnis der entsprechenden Funktionen falsch.
Das betrifft nicht nur die Zähl-Funktionen sondern auch alle anderen Aggregat-Funktionen wie SUMME(), MITTELWERT(), MIN(), MAX() usw.



In diesem Fall wird in den Auswertungs-Funktionen der gesamte frei gehaltene Bereich (hier A15:A1014) angegeben.
Die Aggregat-Funktionen rechnen weiterhin korrekt, weil alle zusätzlich eingetragenen Daten berücksichtigt, leere Zellen jedoch ignoriert werden.

Man muss durch geeignete Maßnahmen sicherstellen, dass in den frei gehaltenen Bereich keine fremden Formeln, Texte oder Zahlen eingetragen werden, und dass der Bereich nicht überschritten wird.

Beispiel: Man formatiert den gesamten Werte-Bereich als Kennzeichen für 'Eingabe' mit hellgrünem Hintergrund.
Die gesamte Zeile 1015 wird mit rotem Hintergrund formatiert und am besten gegen jede Eingabe gesperrt.


Variante der Auswertung

Alle Auswertungs-Funktionen verwenden nun genau den tatsächlich benutzten Werte-Bereich:
 AB
1Zahlen=ANZAHL(INDIREKT($B$10))
2Werte=ANZAHL(INDIREKT($B$10))
3Leere Zellen=ANZAHLLEEREZELLEN(INDIREKT($B$10))
4Zellen=B2+B3
5=ZEILEN(INDIREKT($B$10)) * SPALTEN(INDIREKT($B$10))
6=Anzahl_Zellen(INDIREKT($B$10))

In allen Auswertungs-(Aggregat)-Funktionen wird der Bereich durch die Funktion INDIREKT() und den jeweiligen Wert der Zelle A10 angegeben.
Die Funktion INDIREKT() ist für erfahrene AnwenderInnen ein mächtiges Werkzeug: Man kann die Adressen von Zellen und Bereichen als Text (String) programmieren und in beliebigen anderen Funktionen an Stelle einer 'echten' Adresse angeben.

Wenn man auch die Spalte des Eingabe-Bereichs variabel programmieren will, dann wird die Formel in Zelle B10 besser und allgemeiner formuliert:
=ADRESSSE(B11;SPALTE(A15);4) & ":" & ADRESSE(B12;SPALTE(A15);4)

'Datenbank'-Funktionen


Der Begriff 'Datenbank' ist kaum zutreffend, jedoch mittlerweile etabliert.
Die Funktionalität entspricht ungefähr jener einer einzelnen Datenbank-Tabelle, die mit SQL-Anweisungen (SELECT...) ausgewertet wird.


Such-Kriterien

Die Such-Kriterien (Filter, Such-Bedingungen) werden genau oberhalb der Daten-Tabelle angeordnet. Das ist nicht unbedingt notwendig, jedoch übersichtlich und leichter verständlich.
 
A
B
C
D
E
1
=A10
=B10
 
 
Anzahl
2
 
>5
 
 
=DBANZAHL($B$10:$B$36;B1;B1:B2)
3
 
 
 
 
 
In der 1. Zeile werden die Spalten-Überschriften der Daten-Tabelle wiederholt. Man verwendet dazu Formeln, damit die Spalten-Überschriften genau mit jenen der Daten-Tabelle übereinstimmen und allenfalls zusammen mit diesen geändert werden.

Unter den Spalten-Überschriften werden die Bedingungen eingetragen. Das Beispiel enthält die Bedingung TestZahl>5

Auswertung

Die Funktion DBANZAHL() erwartet 3 Argumente:
Datenbank-Bereich: Bereich der Daten-Tabelle inkl. (!) Spalten-Überschriften.
Name jener Spalte, deren Daten die Funktion auswerten (zählen, summieren,...) soll (hier: B1=B10="TestZahl")
Angabe des Such-Kriteriums (Spalten-Name und Bedingung).


Logische UND-Verknüpfung

 
B
C
1
=B10
=B10
2
>5
<9
Man kann auch in nebeneinander liegenden Zellen mehrere Such-Kriterien angeben. Zur Erfassung einer Daten-Zeile müssen alle angeführten UND-Bedingungen zutreffen.
Der Kriterien-Bereich der Auswertungs-Funktion muss die Spalten-Überschriften und alle Bedingungen umfassen, d.h. in diesem Fall:
=DBANZAHL($B$10:$B$36;$B$1;$B$1:$C$2)
Die Anzahl der Treffer ist bei UND-Verknüpfung maximal gleich groß wie jene der Treffer jeder einzelnen Bedingung.

Zählen mit Bedingung


Das ist in manchen Fällen die einfachste Form der bedingten Zählung.


Direkt eingetragene Bedingung

Diese Formel zählt, wieviele Zellen des untersuchten Bereichs (hier: A11:A30) eine Zahl >5 enthalten:
=ZÄHLENWENN($A$11:$A$30;">5")

Die Bedingung ist als Text (String) direkt in die Funktion eingetragen: Das ist einfach, aber wenig flexibel.



Mit einigem Aufwand kann man auch den Operator (<, =, >, <=, <>, >=) programmieren, z.B. mit Hilfe von Options-Schaltern (RadioButtons) oder einer Auswahl-(Aufklapp)-Liste

Der untersuchte Bereich ist nicht an eine einzelne Zeile oder Spalte gebunden. Man kann z.B. auch den Bereich A11:J110 (1000 Zellen) mit einer einzigen Formel zählen.

Mit dem Operator = kann man nicht nur Zahlen sondern auch Strings (Texte) vergleichen und damit zählen.
Beispiel: → Zufalls-Texte

Freie Programmierung


Diese Methode erfordert mehr Aufwand und fortgeschrittene Kenntnisse, bringt jedoch unbegrenzte Flexibilität und erlaubt die genaue Kontrolle jedes Details.


Zählung von Daten > Vorgabe-Wert

 
B
5
Treffer
6
=SUMME(B11:B36)
7
Zahl
8
5
9
Bedingung
10
=">"&B8
11
=WENN($A11>B$8;1;0)
 
⇓⇓
36
=WENN($A36>B$8;1;0)
Der Zähler in Zelle B6 gibt an, wieviele Zellen des Daten-Bereichs einen Zahlenwert > B8 enthalten.


Logische ODER-Verknüpfung

 
D
5
Treffer
6
=SUMME(D11:D36)
7
 
8
 
9
Bedingung
10
="ODER("&B10&";"&C10&")"
11
=ODER(B11;C11)
 
⇓⇓
36
=ODER(B36;C36)
Der Zähler in Zelle D6 gibt an, auf wieviele Zellen des Daten-Bereichs mindestens eine der beiden Bedingungen von Spalte B oder C zutrifft.


Logische UND-Verknüpfung

 
F
5
Treffer
6
=SUMME(F11:F36)
7
 
8
 
9
Bedingung
10
="UND("&B10&";"&E10&")"
11
=UND(B11;E11)
 
⇓⇓
36
=UND(B36;E36)
Der Zähler in Zelle F6 gibt an, auf wieviele Zellen des Daten-Bereichs die beiden Bedingungen von Spalte B und E zutrifft.

Intervall-Zähler


Dieses Beispiel berechnet die Verteilung jener Ergebnisse, die man beim wiederholten Werfen mit je 2 Würfeln erzielt. Ganze Zahlen lassen sich besonders leicht zählen: Dazu wird hier die Funktion ZÄHLENWENN() verwendet.



 
A
 
J
14
*
 
*
 
113
*
 
*
Jeder Würfel kann eine Augen-Zahl zwischen 1 und 6 ergeben.
In diesem einfachen Beispiel wird die Häufigkeit jeder Ergebnis-Zahl einzeln gezählt.
Die Adresse des Daten-Bereichs wird in B1 berechnet und in allen Auswertungs-Formeln indirekt verwendet.

Auswertung

 
A
B
C
D
E
1
Bereich
=ADRESSE(ZEILE(A14);SPALTE(A14);4) & ":" & ADRESSE(ZEILE(J113);SPALTE(J113);4)
 
Wurf
Anzahl
2
Zellen
=ANZAHL(INDIREKT(B1))
 
2
=ZÄHLENWENN(INDIREKT($B$1);"="&D2)
3
 
 
 
=D2+1
=ZÄHLENWENN(INDIREKT($B$1);"="&D3)
4
Maximum
=MAX(INDIREKT(B1))
 
=D3+1
=ZÄHLENWENN(INDIREKT($B$1);"="&D4
5
Mittelwert
=MITTELWERT(INDIREKT(B1))
 
=D4+1
=ZÄHLENWENN(INDIREKT($B$1);"="&D5)
6
Minimum
=MIN(INDIREKT(B1))
 
=D5+1
=ZÄHLENWENN(INDIREKT($B$1);"="&D6)
7
 
 
 
=D6+1
=ZÄHLENWENN(INDIREKT($B$1);"="&D7)
 
 
 
 
⇓⇓
12
 
 
 
=D11+1
=ZÄHLENWENN(INDIREKT($B$1);"="&D12)


Jede der 1000 Daten-Zellen kann einen Wert im Bereich 0.0...20.0 enthalten. In diesem Fall gibt es sehr viele unterschiedliche Werte: Man kann sie daher nicht einzeln zählen, sondern muss die Daten in sinnvolle Intervalle (von...bis) zusammenfassen.


Auswertung

 
D
E
F
G
1
von
bis
Anzahl(<bis)
Anzahl(von-bis)
2
0
=D2+4
=ZÄHLENWENN($A$14:$J$113;">"&D2)
=F2-F3
3
=E2
=D3+4
=ZÄHLENWENN($A$14:$J$113;">"&D3)
=F3-F4
 
⇓⇓
⇓⇓
5
=E4
=D5+4
=ZÄHLENWENN($A$14:$J$113;">"&D5)
=F5-F6
6
=E5
=D6+4
=ZÄHLENWENN($A$14:$J$113;">"&D6)
=F6
7
 
 
Anzahl(gesamt)
=SUMME(G2:G6)
Die Berechnung von Minimum, Mittelwert, Maximum und Bereich wurde hier zur Vereinfachung weglelassen.
In jeder Zeile 2...6 wird 1 Intervall gezählt. Ein Vergleich ist nur möglich, wenn alle Intervalle gleich breit sind: In diesem Fall ist eine Breite von 4 fix vorgegeben.
In jeder Zeile werden zunächst die Grenzen (von...bis) des jeweiligen Intervall-Zählers berechnet.
Danach wird mit Funktion ZäHLENWENN() kumulativ gezählt, d.h. von der Untergrenze des jeweiligen Intervalls bis zur Gesamt-Obergrenze.
Zuletzt wird von jedem kumulativen Ergebnis das nachfolgende kumulative Ergebnis abgezogen: Das ergibt die Anzahl der Treffer im jeweiligen Intervall.


Matrix-Funktion  HÄUFIGKEIT()

Eine Matrix-Funktion wird anders als eine 'einfache' Funktion eingegeben:
Markieren sie den gesamten Bereich, in den die Matrix-Funktion eingetragen werden soll (im Beispiel G2:G8)
Geben sie die Formel ein. Die Funktion erwartet als Argumente den Daten-Bereich, danach den Bereich der Intervall-Grenzen.
Bestätigen sie nicht mit Enter oder Return, sondern mit den Tasten Ctrl-Shift-Enter
Die Formel wird in {} Klammern angezeigt.


Auswertung mit Basic-Hilfsfunktion

Noch einfacher ist die Zählung von Intervallen, wenn man eine 'Benutzer-definierte' Hilfs-Funktion verwendet, z.B. die im nächsten ↓ Kapitel vorgestellte Funktion Anzahl_Intervall()

In diesem Fall kann man die kumulative Zählung weglassen und die Grenzen der Intervalle (von,bis) als Argumente an die Funktion übergeben.

Basic-Funktionen


Download des Basic-Moduls Random.bas mit diesen und anderen Funktionen.

Die (gleichnamigen) Basic-Funktionen dieses Moduls sind ungekürzt: Sie sind umfangreicher, besser gegen Sonderfälle abgesichert, jedoch weniger übersichtlich.


Basic-Quelltext (hier: abgeschaltet): Für MS-Excel muss man diese Option mit einem führenden Single-Quote Zeichen abschalten:
' Option VBASupport 1

Für LibreOffice oder OpenOffice muss man diese Option einschalten:
Option VBASupport 1



Basic-Quelltext:
Function Anzahl_Zellen(Bereich As Range) As Integer
Anzahl_Zellen = Bereich.Count
End Function


Basic-Quelltext:
Function Adresse_Zelle(Zelle As Range) As String
Dim s0, s1 As Integer
Dim a1, a2 As String
s0 = Zelle.Column - 1
s1 = Int(s0 / 26)
If s1 = 0 Then
a1 = ""
Else
a1 = Chr(s1 + 64)
End If
a2 = Chr((s0 Mod 26) + 65)
Adresse_Zelle = a1 & a2 & Zelle.Row
End Function


Basic-Quelltext:
Function Anzahl_Intervall(Bereich As Range, _
Untergrenze As Double, Obergrenze As Double) _
As Integer
Dim i, treffer As Integer
Dim v As Double
treffer = 0
For i = 1 To Bereich.Count
v = Bereich(i).Value
If (v > Untergrenze) And (v <= Obergrenze) Then
treffer = treffer + 1
End If
Next
Anzahl_Intervall = treffer
End Function