Microsoft Excel

Das Portal zum Thema Excel-Formeln

Gruppe: Zählfunktionen

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)


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