Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Datum/Zeit

Beitrag: Anzahl unterschiedlicher Spalteneinträge

Aufgabe
Wie kann man die Anzahl unterschiedlicher Einträge in Spalten (also ohne Duplikate) ermitteln ?

       A       
1 9  
2 2  
3 4  
4 4  
5 8  
6 5  
7 8  
8 2  
9  
10 5 

Lösung
Spalte: A

1a) ohne Leerzellen:
{=SUMME(1/ZÄHLENWENN(A1:A99;A1:A99))}

1b) mit Leerzellen:
{=SUMME(WENN(A1:A99<>"";1/ZÄHLENWENN(A1:A99;A1:A99)))}
bzw.
=SUMMENPRODUKT((A1:A99<>"")/ZÄHLENWENN(A1:A99;A1:A99&""))

funktionieren auch für mehrere Spalten (Bereich z.B.: A1:D99)

2a)
{=SUMME(WENN(A1:A99<>"";VERGLEICH(A1:A99;A1:A99;0)=ZEILE(1:99))*1)}
bzw.
=SUMMENPRODUKT((A1:A99<>"")*(VERGLEICH(A1:A99&"";A1:A99&"";0)=ZEILE(1:99)))
(nur eine Spalte bzw. Kombi - ist bei langen Listen (> 500) aber viel schneller)
Vor allem aber werden Spaltenkombis und Teilauswertungen akzeptiert:
...VERGLEICH(LINKS(A1:A99;5);LINKS(A1:A99;5);0)... funktioniert
...ZÄHLENWENN(LINKS(A1:A99;5);LINKS(A1:A99;5))... NICHT!

2b) Variante, ohne Leerzellen explizit ausschließen zu müssen und Fehlermeldungen werden ignoriert:
{=ANZAHL(1/((VERGLEICH(A1:A99;A1:A99;0)=ZEILE(1:99))))}

3) Wenn es sich nur um Zahlen und nicht um Text handelt, geht es auch ohne Array: 
=SUMME((HÄUFIGKEIT(A:A;A:A)>0)*1)
Diese Formel ist (da kein Array) sehr schnell und akzeptiert komplette Spalten (A:D).

Obige Formeln unterscheiden nicht Groß- und Kleinschreibung und die Zeichen * ? ~ können zu falschen Ergebnissen führen. 
{=SUMME(1/MMULT(IDENTISCH(A1:A99;MTRANS(A1:A99))*1;ZEILE(1:99)/ZEILE(1:99)))-1}
hebt diese Einschränkungen auf.

Bei großem Datenbestand macht der Rechner mit obigen Arrayformeln schlapp.
Dann in B1 (Hilfsspalte) die Formel:
=1*(ZÄHLENWENN(A$1:A1;A1)=1)
und 65.000 Zellen runterkopieren
das Ergebnis ist dann =SUMME(B:B)

Seit xl365 gibt es die Funktion EINDEUTIG, die es sehr einfach macht:
=LET(e;EINDEUTIG(A1:A99);ANZAHL2(FILTER(e;e<>"")))
Der zusätzliche FILTER wird benötigt, wenn der Bereich Leerzellen enthält, da EINDEUTIG eine einzelne zusätzliche Null für alle Leerzellen liefert. Dies würde das Ergebnis um genau 1 verfälschen.
Ohne Leerzellen reicht: 
=ANZAHL2(EINDEUTIG(A1:A99))
Mit -(ANZAHLLEEREZELLEN(A1:A99)>0) hinten angehängt sind Leerzellen egal.
Erläuterung
Zu 1a) und 1b)
Von jedem Eintrag wird der Kehrwert seiner Häufigkeit im Bereich addiert.

Bei folgenden Einträgen (zur Verdeutlichung ein extremeres Beispiel als oben)
{1;2;3;3;5;5;5;5}
ergibt sich für jede einzelne Zahl eine Häufigkeit von
{1;1;2;2;4;4;4;4}
der jeweilige Kehrwert beträgt dann
{1;1;0,5;0,5;0,25;0,25;0,25;0,25}

ergibt in Summe: 4