(Artikel überarbeitet am 26.10.2021) Show
Vor einiger Zeit hatte ich in einem Artikel beschrieben, wie man in einer Liste mit Duplikaten die Anzahl der eindeutigen Werte bestimmt (Wie zählt man eindeutige Werte in Excel?). Der heutige Artikel geht einen Schritt weiter: Und so geht’s: Ausgangslage und gewünschtes ErgebnisFür mein Beispiel verwende ich eine einfache Namensliste, welche manche Einträge mehrfach enthält (die Beispieldatei kannst du dir
hier herunterladen). Als Ergebnis soll die eindeutige Liste herauskommen, die im Bild in Spalte D gezeigt wird. Jeder Name also genau einmal.  Ausgangslage und gewünschtes Ergebnis Ich werde dazu zwei Lösungsvarianten vorstellen: Beginnen wir aber mit der universellen Variante, die wir nun in mehreren Schritten erarbeiten. Für Ungeduldige: die (fast) fertige LösungsformelUm dieses Ergebnis zu erreichen, verwende ich eine Matrix-Formel, die aus drei Funktionen besteht:
Wie bei jeder Matrix-Formel gilt auch hier: Die fertige Matrix-Formel Aus kosmetischen Gründen wird die finale Formel am Ende noch mit der WENNFEHLER-Funktion umschlossen. Zur besseren Übersicht lasse ich das in den nachfolgenden Erläuterungen aber weg. Schritt 1: ZÄHLENWENNMatrix-Formeln sind immer etwas schwer durchschaubar. Daher empfiehlt es sich, die Formel zu zerlegen und mit Hilfsspalten zu arbeiten. Da man verschachtelte Formeln am besten von innen
nach außen bearbeitet, beginnen wir mit der ZÄHLENWENN-Funktion. Erste Hilfsspalte mit der ZÄHLENWENN-Funktion Damit wird für jede einzelne Zeile geprüft, ob der enthaltene Name oberhalb der jeweiligen Zeile schon einmal vorgekommen ist. Wenn nein, liefert die Funktion den Wert 0 (Null), ansonsten eben 1, 2 oder wie oft auch immer der Name weitere oben schon aufgetaucht ist. Wichtig: Das erste Argument der ZÄHLENWENN-Funktion, also der zu durchsuchende Bereich, beginnt bereits in der
Überschriftenzeile. Wenn die Formel dann nach unten kopiert wird, beginnt damit der Bezug immer absolut in Zelle D4 und verlängert sich dann mit jeder Zeile nach unten. Wie man im Screenshot oben sieht, interessieren uns am Ende nur die Zeilen mit einem Null-Wert. Schritt 2: VERGLEICHDie nächste Schicht in der verschachtelten Formel ist die VERGLEICH-Funktion,
welche die ZÄHLENWENN-Funktion umschließt: Diese Formel wird in die erste Zeile als Matrix-Formel eingegeben und dann nach unten kopiert. Zweite Hilfsspalte mit der VERGLEICH-Funktion Wir suchen damit nach dem Wert 0 (= 1. Argument) innerhalb der Ergebnisse der ZÄHLENWENN-Funktion (= 2. Argument). Und dabei soll ein exakter Vergleich verwendet werden, daher im dritten Argument wieder der Wert 0. Als Ergebnis erhalten wir die relative Position aller 0-Werte in der Liste. Schritt 3: INDEXDie alles umfassende INDEX-Funktion nimmt am Ende die Positionen, die VERGLEICH ermittelt hat und liefert die entsprechenden Namen zurück: Das fertige Liste (inklusive WENNFEHLER) Die in Zelle D5 eingegebene Matrix-Formel wird dann bis ans Ende der
Tabelle nach unten kopiert. Da die eindeutige Liste erwartungsgemäß weniger Einträge umfasst, gibt es einige #NV-Fehler. Diese lassen sich aber mit einer zusätzlichen WENNFEHLER-Funktion elegant unterdrücken: Übrigens: Sonderfall: Leere ZellenMeine oben vorgestellte Lösung hat allerdings noch einen Schönheitsfehler. Wenn die Ausgangsliste nämlich leere Einträge enthält, wird dafür in der Zielliste eine Null
angezeigt: Leere Zellen ergeben einen Nullwert Der Grund dafür ist, dass auch ein leerer Eintrag erst einmal gezählt wird. Und wenn man mit einer Formel auf eine leere Zelle verweist, wird eben die Ziffer Null dargestellt. Um dieses Problem zu lösen, muss unsere Formel nochmal ein wenig angepasst und erweitert werden. Ich prüfe jetzt zusätzlich, ob die jeweilige Zelle in der Ausgangsliste leer ist: Als Ergebnis erhält man ein WAHR für jeden Eintrag, der nicht leer ist und ein FALSCH für jede leere Zelle. Um aus WAHR eine 1 und aus FALSCH eine 0 zu machen, setze ich die
Prüfung in Klammen und multipliziere das Ganze mit 1: Das heißt, für einen Eintrag, der nur einmal in der Liste vorkommt, ergibt die Zählung jetzt den Wert 1, für einen Namen, der zweimal vorkommt, 2 und so weiter. Leere Zellen hingegen ergeben den Wert 0. Und damit müssen wir jetzt in der VERGLEICH-Funktion nicht mehr nach dem Wert 0 suchen, sondern nach 1. Die gesamte neue Formel sieht für Zelle F5 also folgendermaßen aus: Angepasste Formel filtert Nullwerte aus Bitte auch hier unbedingt darauf achten, dass die Formel mit der Tastenkombination Strg+Umschalt+Eingabe abgeschlossen wird! Ich weiß, die Lösung ist nicht ganz trivial. Aber es funktioniert 😉 Deutlich bequemer mit Excel 365Wenn du das Glück hast, Excel aus Microsoft 365/Office 365 zu nutzen, dann habe ich für dich eine wesentlich komfortablere Lösung, als die oben beschriebenen Matrixformeln. Denn dann stehen dir auch die neuen dynamischen Array-Funktionen zur Verfügung, die vieles von dem auf den Kopf stellen, wie man in der Vergangenheit bestimmte Probleme in Excel lösen musste. Für unsere Zwecke sind das die Funktionen EINDEUTIG und FILTER. Wie der Name schon vermuten lässt, liefert
EINDEUTIG eine eindeutige Liste ohne Duplikate. Man gibt dabei lediglich die Ausgangsliste an und erhält sofort das Ergebnis. Da es sich um eine dynamische Funktion handelt, muss die Formel nur in die erste Zelle eingetippt werden. Sie läuft dann automatisch in soviele Zeile über, wie eben gerade notwendig. Nur in Microsoft 365: Die EINDEUTIG-Funktion Da in dieser einfachen Form leere Zellen ebenfalls als Nullwert angezeigt werden, brauchen wir
noch eine weitere Zutat, nämlich die ebenfalls neue FILTER-Funktion. Für sich genommen filtert sie eine Liste nach den angegebenen Kriterien. Um beispielsweise alle leeren Zellen auszufiltern, sieht die Funktion so aus: Nur in Microsoft 365: Die FILTER-Funktion Und wenn man jetzt die beiden Funktionen kombiniert, erhält man eine extrem einfache und höchst komfortable Lösung: EINDEUTIG kombiniert mit FILTER Wie gesagt, die Voraussetzung für diese Variante ist Microsoft 365/Office 365. Wenn du Excel 2019 oder älter einsetzt oder Dateien mit anderen Excel-Anwendern austauschen musst, die solche Versionen im Einsatz haben, bleibt dir leider nur die kompliziertere erste Formellösung. Das könnte dich auch interessieren:Melde Dich zum kostenlosen Newsletter an und verpasse keinen Excel-Tipp mehr! Und immer daran denken: Excel beißt nicht! P.S. Die Lösung ist immer
einfach. Man muss sie nur finden. P.P.S. Das Problem sitzt meistens vor dem Computer. Wann brauche ich Sverweis und wann die wennSo kombinieren Sie SVERWEIS und WENN-Funktion
Die Formel des SVERWEIS setzt sich dabei aus folgenden Argumenten zusammen: „=SVERWEIS(Suchkriterium; Matrix; Spaltenindex; [Bereich_Verweis])“. Die WENN-Funktion kennen Sie bestimmt aus dem wahren Leben. Sie bedeutet, dass eine Handlung eine Konsequenz hervorruft.
Was ist eine verschachtelte Funktion?Mit geschachtelten WENN-Funktionen, d. h. dass sich eine WENN-Funktion in einer anderen WENN-Funktion befindet, können Sie auf mehrere Kriterien prüfen und die Anzahl der möglichen Ergebnisse erhöhen.
Was kann die Funktion Vergleich?Mit der Funktion VERGLEICH wird in einem Bereich von Zellen nach einem angegebenen Element gesucht und anschließend die relative Position dieses Elements im Bereich zurückgegeben.
Was bedeutet #value Excel?#WERT! ist Excels Art des Mitteilens "Irgendetwas stimmt nicht mit der eingegebenen Formel. Oder es liegt irgendein Fehler mit den Zellen vor, auf die Sie verweisen." Der jeweilige Fehler ist sehr allgemein, und es kann schwierig sein, die genaue Ursache des Fehlers zu finden.
|
Welche Excel Funktion liefert einen Wert wenn die genannte Prüfung erfüllt ist sonst aber einen anderen Wert?
zusammenhängende Posts
Urheberrechte © © 2024 paraquee Inc.
