Excel Anzahl eindeutiger einträge die bestimtmen Text enthalten?
Wie kann ich in Excel per Funktion die Anzahl eindeutiger Werte erhalten, welche einen bestimmten Text enthälten?
Hier zB. die Anzahl eindeutiger der Zellen, welche "ein" enthalten. Gesucht ist also die Zahl 3.
2 Antworten
Bei sowas empfehle ich stark ChatGPT zu schreiben. Er ist die optimale Programmierhilfe:
ChatGPT:
Angenommen, Sie haben Ihre Daten in Spalte A und möchten wissen, wie viele eindeutige Einträge den Text "ein" enthalten. Dazu können Sie die Funktionen UNIQUE, FILTER und COUNTA kombinieren, um dies zu erreichen.
Hier ist ein Beispiel, wie das aussehen könnte:
=COUNTA(UNIQUE(FILTER(A:A,ISNUMBER(SEARCH("ein", A:A)))))
Diese Funktion durchläuft jeden Schritt wie folgt:
SEARCH("ein", A:A)
- sucht in jeder Zelle der Spalte A nach dem Text "ein" und gibt die Position des ersten Vorkommens dieses Textes in jeder Zelle zurück oder liefert einen Fehler, wenn der Text nicht gefunden wird.
ISNUMBER(SEARCH("ein", A:A))
- wendet die Funktion ISNUMBER auf die Ergebnisse der SEARCH-Funktion an, um eine Array von Wahrheitswerten zu erzeugen, die angibt, ob jede Zelle in Spalte A den Text "ein" enthält.
FILTER(A:A,ISNUMBER(SEARCH("ein", A:A)))
- verwendet die ARRAY Funktion, um alle Zellen in Spalte A zu filtern, die den Text "ein" enthalten.
UNIQUE(FILTER(A:A,ISNUMBER(SEARCH("ein", A:A))))
- erzeugt eine Liste aller eindeutigen Werte, die aus dem vorherigen Schritt gefiltert wurden.
COUNTA(UNIQUE(FILTER(A:A,ISNUMBER(SEARCH("ein", A:A)))))
- zählt die Anzahl der eindeutigen Werte, die aus dem vorherigen Schritt gefiltert wurden.
Bitte beachten Sie, dass die Funktionen UNIQUE und FILTER erst ab Excel Version 2019 bzw. Excel 365 zur Verfügung stehen. Wenn Sie eine ältere Excel-Version verwenden, wäre die Implementierung dieser Aufgabe komplizierter und würde wahrscheinlich VBA oder eine Reihe von Hilfsspalten erfordern.
Dass coole ist ja, dass kann der auch. Ich schreibe ihm jetzt "Mein Excel ist auf deutsch." Und er antwortet:
Entschuldigen Sie die vorherige Verwirrung. Die Funktionsnamen werden in der lokalen Sprache angezeigt, wenn Excel in einer anderen Sprache als Englisch eingestellt ist. Die entsprechende Formel in deutschem Excel lautet:
=ZÄHLENWENN(EINMALIG(FILTER(A:A;ISTZAHL(FINDEN("ein"; A:A)))))
Die Funktionen in der Formel entsprechen:
FINDEN("ein"; A:A)- sucht in jeder Zelle der Spalte A nach dem Text "ein" und gibt die Position des ersten Auftretens dieses Textes in jeder Zelle zurück oder liefert einen Fehler, wenn der Text nicht gefunden wird.
ISTZAHL(FINDEN("ein"; A:A))- wendet die Funktion ISTZAHL auf die Ergebnisse der FINDEN-Funktion an, um ein Array von Wahrheitswerten zu erstellen, das angibt, ob jede Zelle in Spalte A den Text "ein" enthält.
FILTER(A:A;ISTZAHL(FINDEN("ein"; A:A)))- verwendet die Funktion FILTER, um alle Zellen in Spalte A auszuwählen, die den Text "ein" enthalten.
EINMALIG(FILTER(A:A;ISTZAHL(FINDEN("ein"; A:A))))- erzeugt eine Liste aller eindeutigen Werte, die aus dem vorherigen Schritt gefiltert wurden.
ZÄHLENWENN(EINMALIG(FILTER(A:A;ISTZAHL(FINDEN("ein"; A:A)))))- zählt die Anzahl der eindeutigen Werte, die aus dem vorherigen Schritt gefiltert wurden.
Bitte beachten Sie, dass die Funktionen EINMALIG und FILTER erst ab Excel Version 2019 bzw. Excel 365 zur Verfügung stehen. Wenn Sie eine ältere Excel-Version verwenden, wäre die Implementierung dieser Aufgabe komplizierter und würde wahrscheinlich VBA oder eine Reihe von Hilfsspalten erfordern.
Sie haben zu wenig Argumente für diese Funktion eingegeben.
Entschuldigen Sie meinen Fehler. In der deutschen Version von Excel, wird die
ZÄHLENWENNFunktion nicht in diesem Kontext funktionieren.
Stattdessen können Sie die
ANZAHL2Funktion verwenden, die die Anzahl der nicht leeren Zellen in einem Bereich zählt. Die korrigierte Formel lautet:
=ANZAHL2(EINMALIG(FILTER(A:A; ISTZAHL(FINDEN("ein"; A:A)); "")))
Dies sollte die Anzahl der eindeutigen Einträge in Spalte A zählen, die "ein" enthalten. Beachten Sie, dass die
FILTERFunktion einen dritten Parameter hat, der verwendet wird, wenn keine Übereinstimmungen gefunden werden. In diesem Fall wird ein leerer Text ("") zurückgegeben, um einen Fehler zu vermeiden.
Wieder einmal, bitte beachten Sie, dass die Funktionen
EINMALIGund
FILTERin älteren Versionen von Excel nicht verfügbar sind. Sie sind Teil der dynamischen Arrays Funktionen, die in Excel 365 und Excel 2019 eingeführt wurden.
=ANZAHL2(EINMALIG(FILTER(A:A; ISTZAHL(FINDEN("ein"; A:A)); "")))
Das gibt mir 1 als Ergebnis, nicht 3.
GPT ist und bleibt ein fehleranfälliger ASSISTENT.
(Gut dass wir Entwickler nicht jetzt schon obsolete sind :))
Ist es vielleicht wegen (ISTZAHL) ? Kein Plan, könnte sein er versucht die Wörter als Zahlen zu parsen? (Macht an der Stelle iwie keinen Sinn meiner Meinung nach)
Ich will ChatGPT nicht grundsätzlich schlecht reden, aber in diesem Fall war er nicht nur nicht hilfreich, er war mehrmals nicht hilfreich und er suggerierte ein richtiges Ergebnis. Hätte der Fragesteller nicht den Sollwert per Hand ausgerechnet, würde er jetzt annehmen 1 sei die richtige Lösung.
Wäre es eine Aufgabe einer Klassenarbeit, würde er 0 Punkte bekommen.
Ja, stimmt schon. Trotzdem finde ich GPT ein sinnvoller Assistent, auch für Anfänger, wenn Sie denn verstehen dass GPT nicht immer richtig ist.
Denn die Ansätze die GPT hat sind ja meistens richtig, auch wenn Sie manchmal sehr subtil, doch falsch sind, in der Gesamtlösung.
Sprich, ein digitales zweites Paar Augen kann hilfreich sein für Inspiration, Ideen und Blockaden
Zur Ergänzung: Die Formel auf deutsch ist
- =ANZAHL2(EINDEUTIG(FILTER(A:A;ISTZAHL(SUCHEN("ein"; A:A)))))
Zudem wirft sie fälschlicherweise 1 aus wenn der gesuchte Teil 0 mal vorhanden ist, da Anzahl2() eben auch den resultierenden Fehlerwert zählt. Daher müsste man den Fall noch abfangen z.B mit
- =WENN(ZÄHLENWENN(A:A;"*"&"ein"&"*");ANZAHL2(EINDEUTIG(FILTER(A:A;ISTZAHL(SUCHEN("ein";A:A)))));0)
Ohne Unique() und Filter() reicht eine Hilfspalte aus der man die Summe bildet:
- =WENN(UND(WENNFEHLER(SUCHEN($D$1;A1);0);ZÄHLENWENN($A$1:A1;A1)=1);1;0)
Ich würde es so lösen:
Angenommen das erste "eins" steht in A1, dann schreibst du in B1:
=ZÄHLENWENN(A2:A9999;A1)=0
Diese Formel kopierst du herunter.
In deine Ergebniszelle (zum Beispiel D1) schreibst du:
=ZÄHLENWENNS(A:A;"*ein*";B:B;WAHR)
Das funktioniert, vielen Dank. Hast du eine Idee, ob man das irgendwie ohne Hilfsspalter hinbekommt?
Siehe Kommentar von DanKirpan.
Vorausgesetzt du hast ein modernes Excel, kannst du mit dem Ansatz von ChatGPT arbeiten.
Statt wie von ChatGPT EINMALIG vorgeschlagen, musst du EINDEUTIG verwenden.
Dann hast du allerdings das Problem, dass eindeutig #KALK! bei Nichtfund zurück gibt und ANZAHL2 das als 1 wertet.
Ohne Hilfsspalte aber mit einer Hilfszelle könntest du es so lösen, dass du
EINDEUTIG(...) in einer Zelle (Bsp. D1) berechnest und in die andere schreibst du
=WENN(ISTFEHLER(D1);0;ANZAHL2(D:D))
Es ist schon erstaunlich, was ChatGPT kann, aber die meisten user hier verwenden ein deutsches Excel.