Tutorial

Excel-Trick: Eindeutige Werte in einem Bereich zählen mit ZÄHLENWENN | dptutorials

Melden

Hier ist ein ausführliches Tutorial im Stil von dptutorials, das dir zeigt, wie du die Anzahl eindeutiger Werte in Excel mithilfe der ZÄHLENWENN-Funktion (COUNTIF) ermittelst.

In vielen Situationen musst du wissen, wie viele einzigartige (eindeutige) Einträge sich in einer Liste befinden – zum Beispiel bei einer Kundenliste mit Doppelnennungen oder einer Liste von Produktnummern. In diesem Tutorial lernst du die klassische Formel-Kombination kennen, die in jeder Excel-Version funktioniert.

Die Herausforderung

Die Standardfunktion ANZAHL zählt alle Werte, aber sie unterscheidet nicht zwischen Duplikaten und einmaligen Werten. Wenn "Apfel" dreimal in deiner Liste vorkommt, soll er für die Zählung der eindeutigen Werte nur als 1 zählen.

Die Lösung: Die universelle Formel

Markiere die Zelle, in der das Ergebnis erscheinen soll, und verwende folgende Formel (angenommen, deine Daten stehen im Bereich A2:A10):

=SUMME(1/ZÄHLENWENN(A2:A10; A2:A10))

Wichtiger Hinweis für ältere Excel-Versionen: Wenn du eine Excel-Version vor Office 365 oder Excel 2021 verwendest, musst du diese Formel als Array-Formel abschließen. Drücke dafür nicht einfach Enter, sondern Strg + Umschalt + Enter. Excel setzt dann automatisch geschweifte Klammern { } um die Formel.

Schritt-für-Schritt-Erklärung: Wie funktioniert das?

Die Formel wirkt auf den ersten Blick etwas seltsam. Hier ist die Logik dahinter:

  1. ZÄHLENWENN(A2:A10; A2:A10): Dieser Teil zählt für jeden einzelnen Wert im Bereich, wie oft er vorkommt. Wenn "Apfel" 3-mal vorkommt, erstellt Excel intern ein Array wie: {3; 3; 3; ...}.
  2. 1 / ...: Hier passiert die Magie. Jeder Wert wird durch seine Häufigkeit geteilt.
    • Wenn "Apfel" 3-mal vorkommt, ergibt 1/3 für jedes Vorkommen 0,333.
    • Wenn du diese drei Werte (0,333 + 0,333 + 0,333) addierst, ergibt das genau 1.
  3. SUMME(...): Die Summenfunktion addiert all diese Brüche auf. Jedes Duplikat trägt so nur einen Bruchteil bei, der in der Summe für jede Gruppe genau 1 ergibt. Das Ergebnis ist die Gesamtzahl der eindeutigen Werte.

Profi-Tipp: Fehler bei leeren Zellen vermeiden

Wenn dein Bereich leere Zellen enthält, gibt die obige Formel den Fehler #DIV/0! aus. Um das zu verhindern, kannst du die Formel mit einer Textverkettung (&"") erweitern:

=SUMME(1/ZÄHLENWENN(A2:A10; A2:A10 & "")) - WENN(ANZAHLLEEREZELLEN(A2:A10)>0; 1; 0)

Hierbei wird verhindert, dass durch Null geteilt wird, und die leere Zelle selbst wird am Ende abgezogen, falls sie vorhanden war.

Die moderne Alternative (Excel 365 & Excel 2021)

Wenn du eine aktuelle Excel-Version nutzt, gibt es einen wesentlich einfacheren Weg ohne den "Divisionstrick":

=ANZAHL2(EINDEUTIG(A2:A10))
  • EINDEUTIG erstellt eine Liste, in der jedes Element nur einmal vorkommt.
  • ANZAHL2 zählt, wie viele Elemente in dieser Liste stehen.

Zusammenfassung

Die Kombination aus SUMME und 1/ZÄHLENWENN ist ein mächtiger Excel-Trick, der besonders wertvoll ist, wenn du an älteren Rechnern arbeitest oder Dateien teilst, die mit allen Excel-Versionen kompatibel sein müssen.

Hat dir dieser dptutorials-Trick geholfen? Probiere es direkt in deiner nächsten Tabelle aus!

0