Wie zählt man eindeutige Werte in Excel? 32

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 🙂

 

Wenn dir der Artikel gefallen hat: Bitte weitersagen!

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 zu Daniela Antworten abbrechen

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

32 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

    • Andreas Neumann

      oder ohne Matrixformel:
      =SUMMENPRODUKT(1/(ZÄHLENWENN(A1:C10;A1:C10))*(A1:C10<>““))

      Übrigens funktioniert diese Formel grundsätzlich auch dann, wenn keine Leerzeichen vorhanden sind. Der Vorteil von dieser Lösung ist: Es ist keine Matrixformel. Aber der Kern der Idee ist derselbe. Die Idee mit dem Kehrwert finde ich genial (habe gerade dieses Problem gehabt und natürlich hier wieder mal eine geniale Lösung gefunden).

      • Andreas Neumann

        Hinweis: HTML schein zugeschlagen zu haben, denn die Formel ist nicht sauber. Es muss am Ende wie folgt heißen:

        (A1:C10 ungleich „“)

        Ungleich sollte eigentlich in Kombination sein. Das scheint aber nicht zu funktionieren.

        • Martin Weiß Autor des Beitrags

          Hallo Andreas,

          vielen Dank für diese schöne Alternative. Es stimmt, das HTML spielt einem hier leider immer wieder einen Streich. Ich habe die Formel in deinem Kommentar so angepasst, dass sie jetzt lesbar ist.

          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

          • Franz Rumplmayr

            Hallo Martin,

            absolut Top deine Erklärung und genau das, wonach ich schon lange suche. Der Tipp mit der Pivot ist echt genial, weil ich hauptsächlich mit Pivots arbeite.
            Aber jetzt nochmal zurück zum Thema über die Formel. Ich habe die Anforderung, dass ich 2 Parameter habe. Sagen wir Kunden mit eindeutigen Produkten. Eindeutige Produkte über alle Kunde schaffe ich mit oben angeführter Formel. Sehe ich das richtig, dass es tatsächlich mit 2 Parametern nicht funktioniert?! Kann ich die oben angeführte Formel mit Zählenwenns nicht so erweitern, dass er mir JE Kunde die verschiedenen Produkte ohne Doppelzählung auswirft?
            So wie es Tobias beschrieben hat. Hast du eine Idee warum das nicht funktioniert?

            Lg Franz

          • Martin Weiß Autor des Beitrags

            Hallo Franz,

            ich habe auch ziemlich lange mit einer Formel herumprobiert und ehrlich gesagt: Ich weiß es nicht 🙁

            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

  • Peter Kalkenings

    Hallo Martin,
    danke das funktioniert sehr gut, leere Zellen in dem Spaltenbereich muss man aber mit „1“ vorbesetzen sonst bekommt man kein Ergebnis. Da muss man dann nochmal „1“ subtrahieren um den richtigen wert zu bekommen.

    Was ist aber wenn ich die3se Formel mit dem Teilergebnis verküpfen will weil ich durch einen Filter nur einen Teilbereich der Matrix betrachte?

    • Martin Weiß Autor des Beitrags

      Hallo Peter,

      ja, bei leeren Zellen liefert die Formel einen Fehler. Aber ich verstehe deine Frage mit dem Teilergebnis nicht, das müsstest du etwas näher erläutern.

      Schöne Grüße,
      Martin

  • Daniela

    Hallo,
    wenn ich Zählen ohne Duplikate mit Bedingungen verknüpfen möchte.
    Wäre das z.B. so möglich =SUMME(1/ZÄHLENWENN(A26:A223;A26:A223))-ZÄHLENWENNS(C$26:C$223;“> 0,4″)
    In Spalte A stehen Namen (teils auch doppelt) in Spalte C stehen Werte die ich auf verschiedene Weise auswerten möchte.
    Also kleiner als 0,4 oder auch gößer als 0,4 und kleiner-gleich 0,8 usw.

  • Martin Winkler

    Hallo Martin,

    vielen Dank für diesen Artikel und diese Formel, sie hat mir bereits weiter geholfen.

    Ein Problem hat sich mir dennoch aufgetan.
    Ich möchte einem bestimmten Kriterium zugeordnete eindeutige Werte herauslesen und zählen.
    Diese befinden sich jedoch in einer separaten Tabelle.

    Sobald beide Tabellen geöffnet sind, funktioniert die Lösung mit der Formel =ZÄHLENWENN.
    Sobald die Tabelle mit den „Rohdaten“ jedoch geschlossen ist, liefert mir die Formel keinen Wert.
    SUMMENPRODUKT kommt leider nicht in Frage, da die eindeutigen Werte nicht gezählt werden.
    Gibt es dafür eine einfache Lösung?

    Viele Grüße
    Martin

    • Martin Weiß Autor des Beitrags

      Hallo Martin,

      nein, dafür gibt es keine einfache Lösung. Formeln, die auf andere Dateien verweisen, liefern nur verlässliche Ergebnisse, wenn die Dateien geöffnet sind. Abgesehen davon rate ich von dateiübergreifenden Formelkonstrukten unbedingt ab, wenn es irgendwie vermeidbar ist. Die machen nur Ärger.

      Schöne Grüße,
      Martin

  • Mario Rüger

    Hallo Martin,

    danke zunächst für deine Geduld mit uns Excel „Nichtfachleuten“. 🙂
    Ich habe deine Formel für das Zählen nichtleerer Zellen ohne Dopplungen ausprobiert. Leider scheint sich Excel aufgrund der schieren Mengen an Zeilen (über 850.000 Zeilen) immer wieder aufzuhängen, weil ich als Ergebnis die Zahl „0“ ausgewiesen bekomme und Excel danach nicht mehr funktioniert. Wat nu? Hast du hierzu einen Rat?

    • Martin Weiß Autor des Beitrags

      Hallo Mario,

      kein Problem, wir alle sind auf allen möglichen Gebieten ja Nichtfachleute und wollen etwas lernen 😉
      Zu Deiner Frage:
      Ja, bei dieser Menge an Daten wirst du mit einer Formellösung immer an Grenzen stoßen, dafür ist das Ganze einfach nicht ausgelegt. Wenn es sich um eine Liste handelt, in der alle relevanten Werte in einer einzigen Spalte stehen, würde ich auf die Schnelle eine Pivot-Tabelle verwenden. Die Liste sollte eine Überschrift haben, dann einfach alles markieren, Menü „Einfügen| PivotTable“. Und dann das Feld mit den Werten in den Zeilen-Bereich ziehen. Damit bekommst du eine Liste aller eindeutigen Werte.

      Schöne Grüße,
      Martin