Wie zählt man eindeutige Werte in Excel? 5

Nur eindeutige Werte zählen klappt in Excel mit einem kleinen Trick.
 

Excel bietet allerhand Funktionen, um Werte in Tabellen zu zählen: ANZAHL, ANZAHL2, ZÄHLENWENN, ZÄHLENWENNS. Damit werden schon sehr viele Szenarien abgedeckt.

Was machst du aber, wenn in deiner Tabelle Werte mehrfach vorkommen, du aber jeden Wert nur einmal zählen möchtest? Eine vermeintlich triviale Aufgabe, für die es jedoch in Excel leider keine Standardfunktion gibt.

Im heutigen Artikel zeige ich dir, wie man es mit einem kleinen Trick doch hinbekommt.

Und so geht’s:

Zur Demonstration habe ich folgende kleine Beispieltabelle vorbereitet, die du dir bei Bedarf hier herunterladen kannst:

Beispieltabelle mit mehrfachen Werten

Beispieltabelle mit mehrfachen Werten

Die Funktion ANZAHL liefert – wie der Name schon sagt – die Anzahl aller Werte im genannten Bereich zurück:

Anzahl aller Werte

Anzahl aller Werte

Wie man allerdings unschwer erkennt, sind auch einige Werte doppelt enthalten. Diese wollen wir bei unserer Zählung jedoch ignorieren. Mit Hilfe der folgenden Array-Formel erreichen wir genau dieses Ziel:

{=SUMME(1/ZÄHLENWENN(A1:A10;A1:A10))}

Anzahl eindeutiger Werte

Anzahl eindeutiger Werte

Wichtig:
Die geschweiften Klammern dürfen in dieser Formel nicht von Hand eingetippt werden. Stattdessen schließt man die Eingabe mit der Tastenkombination STRG+Umschalt+Enter ab!

Was passiert hier?

Die ZÄHLENWENN-Funktion

Sehen wir uns zuerst den inneren Teil der Formel an. Die ZÄHLENWENN-Funktion zählt die nichtleeren Elemente in einem bestimmten Bereich abhängig von dem angegebenen Suchkriterium:

=ZÄHLENWENN(Bereich; Suchkriterium)

Wenn wir unsere Beispieltabelle in eine Liste umwandeln, wird das Prinzip deutlicher. In Zelle F3 kommt die ZÄHLENWENN-Funktion zum Einsatz. Als Bereich wird die komplette Liste von E1:E31 angegeben, als Suchkriterium dient Zelle E1. Da der Wert 9 insgesamt 3x in der Liste vorkommt, liefert die Funktion den Wert 3 zurück:

Hilfstabelle: ZÄHLENWENN

Hilfstabelle: ZÄHLENWENN

Kopieren wir die Formel nach unten, ergibt sich folgendes Bild:

Die Anzahl jedes Elements

Die Anzahl jedes Elements

Für jeden Wert in der Liste wissen wir nun, wie oft er vorkommt.

Der Kehrwert

Im nächsten Schritt nehmen wir von jedem berechneten Wert den Kehrwert:

Kehrwerte bilden

Kehrwerte bilden

Für die ersten drei Beispiele habe ich farbige Markierungen verwendet, damit man sieht, was damit erreicht wird:

Die Summe der Kehrwerte ergibt 1

Die Summe der Kehrwerte ergibt 1

Da die Zahl 9 insgesamt dreimal vorkommt, ist der Kehrwert jeweils 1/3 bzw. 0,33. Zählt man diese drei Werte zusammen, ergibt das 1. Die 11 kommt nur einmal vor, daher ist auch der Kehrwert 1. Die 15 gibt es zweimal, was einen Kehrwert von 1/2 bzw. 0,5 ergibt. Auch hier kommt als Summe der beiden logischerweise wieder 1 heraus.

Addieren wir nun in Zelle G32 sämtliche Kehrwerte, erhalten wir dadurch die Anzahl aller eindeutigen Werte:

Die Array-Funktion zum Vergleich

Die Array-Funktion zum Vergleich

Die Array-Formel in G33 (und B14) macht nichts anderes, als die vorhergehenden Schritte in eine einzige Formel zu verpacken und kommt daher zum gleichen Ergebnis, nur eben bedeutend eleganter und ohne die ganzen Hilfsberechnungen.

Ich weiß, dass viele Excel-Anwender noch nie etwas von Array-Formeln gehört haben. Und vielen anderen sind sie einfach nicht ganz geheuer. Aber an dem heutigen Beispiel erkennt man ganz gut, wie nützlich sie sein können. Es kann also nicht schaden, sich hin und wieder einmal mit etwas “gruseligen” Formeln zu beschäftigen 🙂

 

Das könnte dich auch interessieren:

Und immer daran denken: Excel beißt nicht!

P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)

P.P.S. Das Problem sitzt meistens vor dem Computer.



Kommentar erstellen

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.

5 Gedanken zu “Wie zählt man eindeutige Werte in Excel?

  • Michael Gerstel

    Hallo Martin,

    das war mal wieder ein richtig cooles Beispiel der Anwendung der Matrix- bzw. Arrayfunktion. Leider vergisst man in der Praxis oft diese auch mal wieder anzuwenden. Das werde ich für meine Userschulungen mit aufnehmen. Mit dem Beispiel kann man die Vorteile wirklich recht einfach erklären…..
    Danke dir! Echt top!

    • Martin Weiß Beitragsautor

      Hallo Michael,

      vielen Dank für das schöne Feedback! Freut mich sehr, wenn die Tipps praktische Anwendung finden.

      Schöne Grüße,
      Martin

  • François Derron

    Hallo Martin

    Vielen Dank für die tolle Hilfestellung! Ich bin auf der Suche nach einer Lösung für das einmalige Zählen von mehrfach vorkommenden Werten in einer Spalte auf deine Seite aufmerksam geworden. Deine Lösung zum zählen funktioniert in meiner Tabelle einwandfrei bis auf den Fall, wo ich einen Filter setze. Der gezählte Wert bleibt immer der gleiche. Gibt es vielleicht eine Möglichkeit mit Einbezug von Filtern?

    Gruss
    François

    • Martin Weiß Beitragsautor

      Hallo François,

      mit Filtern wird es richtig kniffelig. Die TEILERGEBNIS-Funktion berücksichtigt grundsätzlich gefilterte Werte, bietet aber keine Möglichkeit, nur eindeutige Werte zu zählen. Man muss also die oben im Artikel vorgestellte Lösung mit der TEILERGEBNIS-Funktion kombinieren. Ich habe ein wenig herumprobiert und kann – ohne jegliche Gewähr – folgende Lösung anbieten:

      =AUFRUNDEN(SUMMENPRODUKT((TEILERGEBNIS(3;INDIREKT(“A”&ZEILE(A2:A12)))=1)*(1/ZÄHLENWENN(A2:A12;A2:A12)));0)

      Ob das wirklich zuverlässig ist, kann ich nicht sagen. Einfach mal ausprobieren, vielleicht funktioniert’s ja.

      Schöne Grüße,
      Martin

      • François Derron

        Hallo Martin,

        vielen Dank! Es hat bei mir leider nicht funktioniert, ich bin über den Bezug INDIREKT(“A”&ZEILE(… gestolpert? Auch die Formelauswertung hat mir nicht wirklich weitergeholfen. Macht aber gar nichts, alleine schon dein Trick =SUMME(1/ZÄHLENWENN(C5:C540;C5:C540)) hat mir sehr geholfen. Nochmals ganz herzlichen Dank.

        Herzliche Grüsse aus der CH
        François