Wie kann man die Anzahl unterschiedlicher Einträge in Spalten (also ohne Duplikate) ermitteln ?
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