In diesem Beitrag zeige ich wie man eine Eindeutige Liste mit einer Formel erstellen kann.
Die Liste ist bewusst sehr kurz gehalten für das Beispiel, könnte aber beliebig lang sein. Das Ziel ist es aus der Liste in Spalte B eine eindeutige Liste zu erstellen in Spalte D in dem man nur Formeln benutzt.
Dies kann man machen in dem man eine Matrix Formel verwendet, so wie in Zelle D3:
{=INDEX(Liste;VERGLEICH(0;ZÄHLENWENN($D$2:D2;Liste);0);1)}
Die Formel Erklärt
Hier die Datei damit Ihr es gleich selber bei Euch in Excel anschauen könnt:Eindeutige List erstellen mit Formel
Drei obligatorische Hinweise:
- Dies ist eine Matrix Formel: die geschweiften Klammern werden nicht eingegeben sondern von Excel erstellt in dem man am Schluss CTRL+SHIFT+ENTER drückt.
{=INDEX(Liste;VERGLEICH(0;ZÄHLENWENN($D$2:D2;Liste);0);1)}
- Um Matrix Formeln zu verstehen hilft es ungemein wenn man diese Schritt für Schritt selber auswertet in Excel mit der F9 Taste!
- Die Lösung ist nicht eine Matrix Formel die sich über eine gesamte Spalte erstreckt, sondern eine Matrix Formel pro Zelle, die jeweils nach unten kopiert wird!
[Herzlichen Dank an Excelfreund der mir diesen wichtigen Hinweis, um da verständlicher zu machen, in den Kommentaren gegeben hat!!!]
Es werden insgesamt nur drei Formeln kombiniert: INDEX, VERGLEICH, und ZÄHLENWENN.
Die ZÄHLENWENN Formel ist der Kern dieser Matrix Formel. Hier wird die Matrix Formel zuerst ausgewertet.
ZÄHLENWENN($D$2:D2;Liste)
Speziell hervorzuheben ist hier dass wir den Bereich von ZÄHLENWENN in der Zelle oberhalb der Formel platzieren. Wir tun dies damit die Formel wenn sie nach unten kopiert wird anfängt die bereits erstellten Resultate zu vergleichen.
Mit der VERGLEICH Formel eliminieren wir die doppelten Werte und mit der ZÄHLENWENN Formel helfen wir die doppelten Werte zuerst zu finden.
VERGLEICH(0;ZÄHLENWENN($D$2:D2;Liste);0)
Da wir aus einer Liste Werte auslesen benötigen wir die INDEX Formel. Mit dieser können wir die gesuchten Werte aus der Ursprungsliste referenzieren und in der Eindeutigen Liste zurückgeben. Dank den ersten beiden Formeln haben wir bereits die korrekten Werte gefunden, nun werden Sie aufgelistet (wenn die Formel im Anschluss nach unten gezogen wird).
Die INDEX Formel hat drei Argumente: In Orange den Verweis auf die Liste. Das in gelb hervorgehobene ist die Zeile, und in grün die Spalte welche in unserem Fall immer 1 bleibt.
=INDEX(Liste;VERGLEICH(0;ZÄHLENWENN($D$2:D2;Liste);0);1)
Bitte um die Lösung:
In Spalte A mit 90 Zeilen mit Namen, Spalte B mit Zahlen.
In Spalte C Zeile 1 stelle ich den Vergleich mit Namen und das ergibt die Zeile (15)
In Spalte D Zeile 1 will ich dann die Zahl aus Spalte B derselben Zeile (15)
Mit welcher Formel kann man das machen.
Danke.
MfG
Siegfried Gruber
Sverweis heisst doe funktion. Allerdings bekommst du dann nur den ersten Wert von der ursprünglichen Tabelle
Überragend!!!
das funkt nicht, erscheint mir auch voll unlogisch.
Giebt bei mir immer nur 0 als wert zurueck
Ihr könnt Euch die Datei herunterladen und anschauen wie es funktioniert und auch DAS es funktioniert.
Ich dachte, ich kenne Excel und dann sowas! :D
Ja, es funktioniert, danke!
Das funktioniert super für eine Liste, aber geht das auch für eine Matrix (z.B. Daten in B3:C6)?
Hat sich erledigt, habe ein Workaround gefunden: Die Matrix in eine Liste transformieren, dann gehts. Danke für die elegante Formel!
Besteht auch die Möglichkeit, einen weiteren Filter zu setzen? Zum Beispiel sollen nur die eindeutigen Werte angezeigt werden, die mit A beginnen
Hallo Lukas,
ich habe x Spalten (T1, T2, T3,…). Unter diesen Spalten sind Werte aufgelistet S1 bis S20. Hier können mal nur 2 Werte stehen, manchmal aber auch 10. Innerhalb der Spalten gibt es keine doppelten Werte. Spaltenübergreifend aber schon. Ich benötige nun eine Auflistung aller Werte ohne Dopplungen mit einer möglichst verständlichen Excelformel. Kannst du mir hier weiterhelfen?
Leider bekomme ich es alleine nicht hin.
Viele Grüße
Lisa
Wahnsinn – fantastische Formel – auch wenn ich länger gebraucht habe, den Fehler auszumerzen –
Wie ist denn die korrekte Version ohne Fehler?
Ach, ich habe es auch eben gefunden. Danke!
Die Formel hat keinen Fehler; beim Nachmachen produziert man aber gerne einen Zirkelbezug
Gut gemacht, aber leider schlecht beschrieben!
Lukas, Du solltest darauf hinweisen, dass die Ergebnisliste keine Matrixformel ist, die sich über eine Spalte erstreckt, sondern eine Matrixformel (mit CTRL SHIFT abschließen), die dann einfach mit Copy/Paste untereinander kopiert werden muss.
Der Bezug auf sich selber muss eine Zelle oberhalb des Ergebnislistenanfangs verweisen.
Trotzdem danke für die schlau kombinierte Lösung!
Ich denke Du hast mit Deinem Kommentar absolut Recht! Guter Hinweis. Ich habe den Beitrag oben angepasst mit diesem wichtigen Hinweis der das ganze hoffentlich einfacher und verständlicher macht! Merci!
Matrixformeln erstrecken sich normalerweise über mehrere Zeilen oder Spalten, hier aber interessanterweise nicht!
Ich bekomme als Ergebnis auch immer nur 0 raus.
Ist deine “Liste” etwas besonderes? Oder speziell angelegt? Wenn ich alles genau so eingebe aber mit einer Spalte “Projektteil” und Projektteil statt Liste in der Formel eintrage bringt mir das garnichts. Ergebnis ist immer 0.
Hallo,
ich möchte aus Zeilen, die ca. 40 Einträge (= Sätzen von 5 Spalten) breit sind immer nur die maximal 25 nichtleeren Einträge in die eersten maximal 25 Einträge (ebenfalls je 5 Spalten, einer logischerweise neuen Liste) kopieren.
Mir scheint das dieser Ansatz hier dafür geeignet sein und wesentlich schlanker als jeweils per DBAUSZUG den Veränderlichen ‘Rest’ der Einträge ab dem zuletzt gefundenen und übernommenen wieder zu durchsuchen und zu finden etc…
Die Anwendung ist kommerziell und ein Beratungshonorar für zeitnahen zielführenden Hinweis steht zur Verfügung. (hoffe das ist hier ‘erlaubt’)
ich bin zu blöd dafür…. bei identischen Namen/Bezeichnern wie im Beispiel XLS bekomme ich als Resultat immer “#NAME?” zurück. Laut Excel ist in ZÄHLEWENN Liste nicht korrekt.
Was genau ist “Liste”? Ist das der Spaltenbezeichner für Spalte B? Oder ein Platzhalten für den Bereich B1:B9999 ?
Vielen Dank!
Unbrauchbare Formel solange man nicht die Iterative Berechnung aktiviert hat.
Hallo Lukas,
die Formel funktioniert super, vielen Dank dafür!
Ich habe noch folgendes Problem: Ich benötige eine eindeutige Liste aus vier Spalten, die jeweils auf einem eigenen Tabellenblatt stehen. Wie kann ich die Formel auf mehrere Spalten in unterschiedlichen Reitern beziehen?
Würde mich sehr freuen, wenn hierzu jemand eine Antwort hätte.
LG,
Peter