Anzahl gleicher werte zählen excel

Sie haben eine Liste bekommen und müssen auf die Schnelle ermitteln, wie viele unterschiedliche Werte diese in einem Bereich enthält. Eine kleine Formel erledigt diese Berechnung.

Lösung: Eine Problemstellung, die immer wieder mal auftaucht: Man muss für einen bestimmten Bereich in Excel ermitteln, wie viele unterschiedliche Werte auftauchen, um ein Projekt weiter abarbeiten zu können. Die Funktion ZÄHLENWENN leistet da hilfreiche Dienste. Verpackt in eine Matrixfunktion, ermittelt sie die Anzahl der unterschiedlichen Werte.

Die Werte befinden sich in unserem Beispiel in dem Bereich Teilnehmer, wir haben den Bereich B2:B13 so benannt. Mit der Formel

{=ZÄHLENWENN(Teilnehmer;Teilnehmer)}

Diese Formel ist als Matrixfunktion einzugeben, denn es soll ja für jeden Wert gezählt werden, wie oft dieser vorkommt. Wenn Sie die Formel mit STRG + UMSCHALTTASTE + ENTER eingeben, fügt Excel automatisch die geschweiften Klammern hinzu und führt dies als Matrixfunktion aus. Dies ist auch für die nachfolgenden Formeln zu beachten.

1. Anzahl unterschiedlicher Werte ermitteln
   Auf einen Blick ist nur schwer zu erkennen, wie viele wirkliche verschiedene Teilnehmer angetreten sind.
2. Anzahl unterschiedlicher Werte ermitteln
   Diese Formel liefert die tatsächliche Anzahl der Teilnehmer zurück.

Das Element Bernhard kommt in unserem Beispiel fünf Mal vor, obige Formel liefert also "5" zurück. Da jeder Teilnehmer ja nur einmal gezählt werden soll, teilt man 1 durch die Anzahl. Wenn man jetzt das Ganze summiert, bekommt man die Anzahl der unterschiedlichen Werte im angegebenen Bereich mit:

{=SUMME(1/ZÄHLENWENN(Teilnehmer;Teilnehmer))}

Wer in der Liste mit leeren Zellen zu rechnen hat, kann diese eventuell noch durch eine WENN-Bedingung ausschließen, da die Formel sonst einen Fehler auswirft. Also etwa so:

{=SUMME(WENN(Teilnehmer=" ";0;1/ZÄHLENWENN(Teilnehmer;Teilnehmer)))}

Produkte: Dieser Tipp funktioniert mit Excel 2010 und Excel 2013. (mje)

Artikel als PDF kaufen

Die Rechte an diesem Artikel kaufen

×Close

Erwerben Sie die Rechte an diesem Artikel

Wenn Sie Artikel von CIO, Computerwoche, TecChannel oder Channelpartner für eine kommerzielle Vervielfältigung nutzen wollen, müssen Sie eine Lizenz erwerben.

Um in Excel die Anzahl von Übereinstimmungen von zwei Bereichen zu zählen, kommt die ZÄHLENWENN-Funktion zum Einsatz. Hier in dem Beispiel wollen wir einmal die Übereinstimmungen auf gleicher Zeile und einmal all Übereinstimmungen zählen.

Übereinstimmungen auf gleicher Zeile

=SUMME(--(A4:A10=C4:C10))

Zuerst wollen wir nur die Übereinstimmungen auf gleicher Zeile zählen. Hierfür vergleichen wir die beiden Bereiche A4:A10 und C4:10. Dabei bekommen wir als Ergebnis WAHR und FALSCH als Wahrheitswerte zurück. Das Ergebnisfeld (Array) im Beispiel sieht wie folgt aus  {WAHR;WAHR;FALSCH;FALSCH;FALSCH;FALSCH;FALSCH} 

In Excel werden WAHR als 1 und FALSCH als 0 dargestellt. Daher können wie jetzt die Summe des Ergebnisfeldes berechnen. Damit jedoch die nächste Funktion die Wahrheitswerte als Zahlen erkennt, müssen diese in noch mit einem doppelten Minuszeichen von Wahrheitswerten in binäre Werte also 0 und 1 umgewandelt werden.

Übereinstimmungen gesamt

=SUMME(ZÄHLENWENN(A4:A10;C4:C8))

Um alle Übereinstimmungen zu zählen, kommt die ZÄHLENWENN-Funktion zum Einsatz. ZÄHLENWENN(Bereich; Suchkriterien)

Hier geben wir als Suchkriterien nicht nur einen Wert, sondern alle Werte aus dem 2. Bereich ein. Das führt dazu, dass die ZÄHLENWENN-Funktion mehrfach durchgeführt wird und ein Ergebnisfeld (Array) produziert. Das Ergebnisfeld (Array) im Beispiel sieht wie folgt aus {1;1;1;0;1}

Die Anzahl von Einträgen in einem Bereich zu zählen ist mit Excel kein Problem. Sie können dazu die Funktionen ANZAHL für Zahlenwerte bzw. ANZAHL2 für beliebige Werte verwenden. Diese Funktionen zählen jeden Eintrag in einem Bereich. Sind Duplikate darunter, werden sie doppelt gezählt.  Möchten Sie wissen, wie viele eindeutige Werte ein Bereich enthält, geht das mit einer Matrix-Funktion.

So funktioniert es:

In unserem Beispiel wollen wir zählen, wie viele unterschiedliche Produkte sich in den Lagern befinden. Wir bauen die dazu notwendige Vorgehensweise Schritt für Schritt auf.

Zuerst muss festgestellt werden, wie oft jedes einzelne Produkt vorkommt. Hierzu verwenden wir die Funktion ZÄHLENWENN. Diese Funktion zählt nur Einträge, für die eine bestimmte Bedingung erfüllt ist. Unsere erste Bedingung ist, dass das Produkt „Hammer“ sein soll, dann „Zange“, usw.

Die Funktion ZÄHLENWENN muss also den Bereich mit den Produkten mit sich selbst vergleichen. Hierfür ist es notwendig, die Funktion ZÄHLENWENN als Matrixfunktion einzugeben. Um dies zu tun, wird der gesamte Ergebnisbereich (D3:D16) markiert. Die Eingabe der Funktion wird dann mit der Tastenkombination STRG + UMSCHALT + ENTER abgeschlossen. Hierdurch wird der gesamte Bereich auf einmal ausgewertet. Dass es sich um eine Matrixfunktion handelt, wird von Excel durch die geschweiften Klammern um die Funktion kenntlich gemacht. Diese Klammern werden durch die oben genannte Tastenkombination erzeugt.

Das Ergebnis ist ein Bereich, der hinter jedem Produkt anzeigt, wie oft dieses Produkt insgesamt vorkommt. Diese Angabe steht an jeder Position des jeweiligen Produkts.

Um zu erreichen, dass ein Produkt, das beispielsweise dreimal vorkommt, nur einmal gezählt wird, teilen wir jetzt die Zahl „1“ durch die Anzahl des Produkts. Wenn ein Produkt dreimal vorkommt, erhalten wir dann also den Wert ⅓. Zählen wir dann die Werte dieses Produkt zusammen, so erhalten wir ⅓ + ⅓ + ⅓ = 1.

Um ohne diese Zwischenberechnungen sofort zu dem Ergebnis zu gelangen, werden die einzelnen Schritte jetzt verschachtelt.

Weitere Tipps zum Arbeiten mit Excel erhalten Sie in unseren Excel-Seminaren. Wir freuen uns auf Sie!

Wie zähle ich die Anzahl in Excel?

Wie oft ist ein Wert vorhanden Excel?

Wie kann man gleiche oder doppelte Werte nur einmal in einer Spalte Zahlen?

Wann benutze ich Anzahl und ANZAHL2?