Wie zählt man eindeutige Werte in Excel? 18

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.



Schreibe einen Kommentar

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

18 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ß Autor des Beitrags

      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ß Autor des Beitrags

      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

  • Sabine

    Hallo Martin,

    echt cooles Beispiel. Aber was mache ich denn, wenn ich leere Zellen in dem Bereich habe?
    Danke für deine Hilfe.

    Gruß
    Sabine

    • Martin Weiß Autor des Beitrags

      Hallo Sabine,

      da leere Zellen zu einem #DIV/0!-Fehler in der Formel führen, musst Du nur diesen Fehler abfangen:
      {=SUMME(WENNFEHLER(1/(ZÄHLENWENN(A1:C10;A1:C10));0))}

      Schöne Grüße,
      Martin

  • Rödenbeck

    Hallo Martin,

    das funktioniert mit Zahlen super.

    Ich habe allerdings in diesem Fall keine Zahlen, sondern Artikelnummern (z.B. 100100-200). Ich habe die Formel ausprobiert, habe aber als eindeutige Anzahl „0“ heraus bekommen. Kann man die eindeutige Anzahl von diesen Nummern, oder auch von Bezeichnungen auch mithilfe einer Formel heraus bekommen, oder gehe ich da den uneleganten Weg alle Duplikate raus zu schmeißen und mir die dadurch gewonnen Informationen zusammen zu flicken?

    Danke und viele Grüße

    Sarah

    • Martin Weiß Autor des Beitrags

      Hallo Sarah,

      dann muss sich irgendwo in deiner Formel der Wurm eingeschlichen haben. Denn die im Artikel gezeigte Matrixformel funktioniert nicht nur bei Zahlen, sondern auch bei Texten.

      Schöne Grüße,
      Martin

  • Tobias

    Hallo Martin,

    danke dir für die tolle Erklärung.

    Ich habe eine Tabelle vor mir, die in einer Spalte Artikelnummern hat.
    Nun möchte ich diese unterschiedlichen Artikelnummern zusammenzählen jedoch nur die aus dem Standort A und aus dem Jahr 2017.
    Sprich, ich möchte die Formel weiter Einschränken, aber wie ist das möglich. Ich habe es ein paar Mal mit =ZählenwennS probiert, klappt aber nicht.

    Danke dir im Voraus

    • Martin Weiß Autor des Beitrags

      Hallo Tobias,

      mit ZÄHLENWENNS bist du genau auf der richtigen Spur. Ich kenne jetzt den Aufbau deiner Tabelle nicht, daher folgende vereinfachte Annahme. Standort steht in Spalte B und das Jahr in Spalte C, dann lautet die Formel:
      =ZÄHLENWENNS(B1:B100;“A“;C1:C100;2017)

      In diesen beiden Artikeln ist die ZÄHLENWENNS-Funktion beschrieben:
      https://www.tabellenexperte.de/wieviele-sind-das-eigentlich/
      https://www.tabellenexperte.de/wenns-mal-wieder-ein-bisschen-mehr-excel-sein-darf/

      Schöne Grüße,
      Martin

      • Tobias

        Hallo Martin,

        ich probier das Problem noch einmal zu beschreiben.

        Insgesamt habe ich rund 40.000 Datensätze.

        In Spalte A habe ich das Jahr, in dem das Teil verkauft wurde
        In Spalte B habe ich den Standort, von dem es verkauft wurde (Standort Paris oder London)
        In Spalte C habe ich die dazugehörige Artikelnummer.

        Ich möchte nun herausfinden, wie viele unterschiedliche Artikelnummern Im Jahr 2016 vom Standort Paris verkauft wurden.
        Wie viele unterschiedliche Artikelnummern im Jahr 2017 vom Standort Paris verkauf wurden usw.

        Ich dachte da eher an eine Formel die in etwa so aussehen könnte, die natürlich nicht funktionier 🙁
        {=SUMME(1/ZÄHLENWENNS(A:A;“2016″;B:B;“Paris“;C:C;C:C))}

        Ich komme über Umwege zu meinem Ergebnis (mit Filtern und Dupplikaten enfernen, oder Hilfsspalten …) würde mich aber eben für die „elegantere“ Lösung interessieren.

        Danke dir!

        • Martin Weiß Autor des Beitrags

          Hallo Tobias,

          ok, der Knackpunkt ist hier tatsächlich, dass du nur die unterschiedlichen Artikelnummern brauchst. Wenn du Excel 2013 oder neuer einsetzt, geht das mit einer Pivot-Tabelle. Du musst nur beim Erstellen der Pivot-Tabelle das Häkchen „Zum Datenmodell hinzufügen“ auswählen. Danach gibt es in den Wertfeldeinstellungen nicht nur die Funktionen Anzahl, Summe etc., sondern auch diskrete Werte.

          Falls du noch Excel 2010 oder 2007 hast, geht das leider nicht. Dann musst du eine Hilfsspalte erstellen, die eindeutige Einträge zählt. Gib in die erste Zelle folgende Formel ein (und achte unbedingt auf die genaue Setzung der $-Zeichen!)
          =WENN(ZÄHLENWENNS($A$2:A2;A2;$B$2:B2;B2;$C$2:C2;C2)>1;0;1)

          Und dann die Formel nach unten kopieren. Damit wird immer nur beim ersten vorkommenden Artikel die 1 gesetzt, ansonsten die 0. Und das kannst du jetzt in einer „normalen“ Pivot-Tabelle auswerten.

          Schöne Grüße,
          Martin

  • Reinhard Hess

    Hallo Martin,
    diese Formel ist wirklich gut zu gebrauchen. Ich wollte sie gleich mal erweitern auf zwei (oder mehr) Spalten.
    Als Beispiel diene eine Tabelle mit zwei Spalten, Name (A:A) und Vorname (B:B). Die Formel soll nun alle Paare ohne Mehrfachwertung zählen: =SUMME(1/ZÄHLENWENN(A:A&B:B;A:A&B:B)), natürlich als Matrixformel eingegeben. Dabei bekomme ich eine Fehlermeldung. Wenn ich die Pärchen in einer Hilfsspalte erstelle, also Spalte C = A:A&B:B und dann auf die Hilfsspalte referenziere, also =SUMME(1/ZÄHLENWENN(C:C;C:C)), funktioniert es prima. Auch die Syntax =SUMME(1/ZÄHLENWENN(C:C;A:A&B:B)) funktioniert. Mache ich hier einen Denkfehler oder ist das mal wieder ein Microsoft-Feature?
    Die Formeln werden natürlich immer als Matrixformel eingegeben.
    Viele Grüße, Reinhard

    • Martin Weiß Autor des Beitrags

      Hallo Reinhard,

      eine sehr interessante Frage und ein in der Tat nicht so richtig nachvollziehbares Verhalten von Excel. Ich habe auch ein wenig herumprobiert, bin aber auch auf keine Lösung gekommen. Anscheinend akzeptiert die ZÄHLENWENN-Funktion wirklich nur für den zweiten Parameter (Suchkriterium) einen zusammengesetzten Ausdruck, nicht aber für den ersten Parameter. Warum das so ist, erschließt sich mir leider auch nicht.

      Schöne Grüße,
      Martin

      • Hess Reinhard

        Hallo Martin,
        dann sitzt das Problem wohl doch in Redmond und nicht vor dem Computer. Schade, das hätte gut gepaßt. Aber mit einer Hilfsspalte geht’s ja auch. Danke für die Hilfe.
        viele Grüße, Reinhard