5
SVerweis - Daten aus 2 Tabellen konsolidieren
Ich bekomme das einfach nicht in Stein gehauen .... 
Ich habe 2 Dateien.
Datei 1 (Info.xls) hat eine Tabelle1 mit folgendem Inhalt
Spalte A Spalte B
Namen Alter
Willi 43
Erna
Hans 32
Kurt
Fritz 19
Maus 19
Datei (Neu.xls) sieht so aus
Spalte A Spalte B
Namen Alter
Hans 32
Kurt 32
Hanna 21
Fritz 22
Maus 18
Erna 42
Wie bekomme ich nun einen Abgleich zwischen beiden Dateien hin.
Ich möchte in einer neuen Tabelle oder neuen Datei
- alle Namen aus Datei 1 und 2 mit den dazugehörigen Alter haben.
- WENN das Alter schon eingetragen ist, dann ignoriere einen neuen Wert (oder, wenn möglich, markiere den Datensatz im Original)
Viiiiielen Dank
Fred Feuerstein
Ich habe 2 Dateien.
Datei 1 (Info.xls) hat eine Tabelle1 mit folgendem Inhalt
Spalte A Spalte B
Namen Alter
Willi 43
Erna
Hans 32
Kurt
Fritz 19
Maus 19
Datei (Neu.xls) sieht so aus
Spalte A Spalte B
Namen Alter
Hans 32
Kurt 32
Hanna 21
Fritz 22
Maus 18
Erna 42
Wie bekomme ich nun einen Abgleich zwischen beiden Dateien hin.
Ich möchte in einer neuen Tabelle oder neuen Datei
- alle Namen aus Datei 1 und 2 mit den dazugehörigen Alter haben.
- WENN das Alter schon eingetragen ist, dann ignoriere einen neuen Wert (oder, wenn möglich, markiere den Datensatz im Original)
Viiiiielen Dank
Fred Feuerstein
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 98291
Url: https://administrator.de/contentid/98291
Printed on: April 20, 2024 at 04:04 o'clock
5 Comments
Latest comment
Hallo Feuerstein09!
Soferne es sich um eine einmalige Aktion handelt, könntest Du etwa so vorgehen (Annahme: Die Überschriften ("Namen" und "Alter") sind jeweils ab A1 eingetragen, sodass die Daten ab A2 beginnen):
In der Datei "Info.xls" in C2 die Formel
und in D2 die Formel
eintragen und jeweils nach unten kopieren (die Bereiche aus der Datei "Neu.xls" sind natürlich entsprechend anzupassen).
In "Neu.xls" in Zelle C2
und in D2
verwenden und (wiederum nach Anpassung an die Bereiche aus "Info.xls") nach unten kopieren.
Als Zwischenergebnis sollten nun in beiden Dateien in den Spalten C und D jeweils Paare von Namen und Altersangaben oder leere Zellen stehen. Jetzt sind nur noch diese Daten (die "Lücken" dazwischen stören nicht - siehe unten) zu kopieren und in einer neuen Tabelle über "Bearbeiten / Inhalte einfügen... / Werte" einzufügen und so zusammenzufassen.
Nach dem Markieren der beiden Spalten, in welche die Daten eingefügt wurden, können diese Spalten (nach dem Namen) sortiert werden, wodurch alle nicht-leeren Zellen zu einem Block zusammengefasst werden, was eine vernünftige Weiterverarbeitung (ggf nach dem Entfernen des oberhalb stehenden zweiten Blockes mit leeren Zielen oder einem nochmaligen Kopieren in eine andere Tabelle) möglich machen sollte.
Im Endergebnis sind alle vollständigen Daten aus der Datei "Info.xls" und alle zusätzlichen (und vollständigen, also ebenfalls mit zum Namen eingetragenem Alter) Daten aus "Neu.xls" enthalten.
Grüße
bastla
Soferne es sich um eine einmalige Aktion handelt, könntest Du etwa so vorgehen (Annahme: Die Überschriften ("Namen" und "Alter") sind jeweils ab A1 eingetragen, sodass die Daten ab A2 beginnen):
In der Datei "Info.xls" in C2 die Formel
=WENN(B2<>"";A2; WENN(ISTFEHLER(SVERWEIS(A2;[Neu.xls]Tabelle1!$A$2:$B$7;2;0));""; WENN(SVERWEIS(A2;[Neu.xls]Tabelle1!$A$2:$B$7;2;0)<>"";A2))) =WENN(B2<>"";B2;WENN(C2<>"";SVERWEIS(C2;[Neu.xls]Tabelle1!$A$2:$B$7;2;0))) In "Neu.xls" in Zelle C2
=WENN(ISTFEHLER(SVERWEIS(A2;[Info.xls]Tabelle1!$C$2:$C$7;1;0));WENN(B2<>"";A2;"");"") =WENN(C2<>"";B2;"") Als Zwischenergebnis sollten nun in beiden Dateien in den Spalten C und D jeweils Paare von Namen und Altersangaben oder leere Zellen stehen. Jetzt sind nur noch diese Daten (die "Lücken" dazwischen stören nicht - siehe unten) zu kopieren und in einer neuen Tabelle über "Bearbeiten / Inhalte einfügen... / Werte" einzufügen und so zusammenzufassen.
Nach dem Markieren der beiden Spalten, in welche die Daten eingefügt wurden, können diese Spalten (nach dem Namen) sortiert werden, wodurch alle nicht-leeren Zellen zu einem Block zusammengefasst werden, was eine vernünftige Weiterverarbeitung (ggf nach dem Entfernen des oberhalb stehenden zweiten Blockes mit leeren Zielen oder einem nochmaligen Kopieren in eine andere Tabelle) möglich machen sollte.
Im Endergebnis sind alle vollständigen Daten aus der Datei "Info.xls" und alle zusätzlichen (und vollständigen, also ebenfalls mit zum Namen eingetragenem Alter) Daten aus "Neu.xls" enthalten.
Grüße
bastla
werde ich sofort ausprobieren ... und feedback geben.
Danke soweit!
Danke soweit!
Hallo bastla,
klappt einwandfrei!
Danke!
Kannst du mir nun noch (for dummies) erklären, wie ich die Parameter beim SVERWEIS zu verstehen habe?
Konkret: Laut Hilfe hat SVERWEIS folgende Parameter:
Suchkriterium: Ok, hier wird der Wert eingetragen (oder darauf verwiesen, nach dem gesucht werden soll.
Matrix: Das ist der Bereich in dem gesucht werden soll, richtig?
Spaltenindex: Das ist die Spalte, deren Wert ist in abhängigkeit des gesuchten Werts zurück geliefert haben möchte?? Also in meinem Beispiel wäre das Spalte B weil die Matrix von Ax bis Bx geht???
Bereich_Verweis: Was genau haben hier die Werte -1, 0 und 1 zu bedeuten?
Kann man (gerade die letzten 2 Parameter) Anhand meines Beispiel näher erläutern?
Wäre echt klasse (ich will ja gerne dazulernen!) !!!
)
klappt einwandfrei!
Danke!
Kannst du mir nun noch (for dummies) erklären, wie ich die Parameter beim SVERWEIS zu verstehen habe?
Konkret: Laut Hilfe hat SVERWEIS folgende Parameter:
Suchkriterium: Ok, hier wird der Wert eingetragen (oder darauf verwiesen, nach dem gesucht werden soll.
Matrix: Das ist der Bereich in dem gesucht werden soll, richtig?
Spaltenindex: Das ist die Spalte, deren Wert ist in abhängigkeit des gesuchten Werts zurück geliefert haben möchte?? Also in meinem Beispiel wäre das Spalte B weil die Matrix von Ax bis Bx geht???
Bereich_Verweis: Was genau haben hier die Werte -1, 0 und 1 zu bedeuten?
Kann man (gerade die letzten 2 Parameter) Anhand meines Beispiel näher erläutern?
Wäre echt klasse (ich will ja gerne dazulernen!) !!!
)
Hallo Feuerstein09!
Du hat es weitgehend schon selbst beschrieben
...
Der SVERWEIS sucht (bei der ersten Zelle der angegebenen "Matrix " beginnend senkrecht) immer in der ersten Spalte nach dem "Suchkriterium", und sobald die richtige Zeile gefunden wurde, wird von der Fundstelle aus nach rechts die gesuchte Zelle angesteuert - wie weit es nach rechts geht, legt der "Spaltenindex" fest - dieser ist also eine "relative" Spaltennummer (wenn die Matrix in Spalte D beginnt, wird eben diese Spalte D mit Spaltenindex 1, Spalte E mit 2, etc nummeriert).
Mit dem Parameter "Bereich_Verweis" lässt sich die Art der Suche steuern - der von mir verwendete Wert 0 (=FALSCH) bedeutet: Suche nicht nach einem Bereich, sondern nach einer exakten Übereinstimmung mit dem Suchkriterium (anderenfalls - also für die Werte 1 oder -1, welche beide für WAHR stehen, oder auch bei Weglassen des Parameters - wird nur versucht, einen passenden Bereich zu finden, was zB sinnvoll ist bei PLZ-Bereichen, Rabattstaffelungen, etc).
Grüße
bastla
Du hat es weitgehend schon selbst beschrieben
...Der SVERWEIS sucht (bei der ersten Zelle der angegebenen "Matrix " beginnend senkrecht) immer in der ersten Spalte nach dem "Suchkriterium", und sobald die richtige Zeile gefunden wurde, wird von der Fundstelle aus nach rechts die gesuchte Zelle angesteuert - wie weit es nach rechts geht, legt der "Spaltenindex" fest - dieser ist also eine "relative" Spaltennummer (wenn die Matrix in Spalte D beginnt, wird eben diese Spalte D mit Spaltenindex 1, Spalte E mit 2, etc nummeriert).
Mit dem Parameter "Bereich_Verweis" lässt sich die Art der Suche steuern - der von mir verwendete Wert 0 (=FALSCH) bedeutet: Suche nicht nach einem Bereich, sondern nach einer exakten Übereinstimmung mit dem Suchkriterium (anderenfalls - also für die Werte 1 oder -1, welche beide für WAHR stehen, oder auch bei Weglassen des Parameters - wird nur versucht, einen passenden Bereich zu finden, was zB sinnvoll ist bei PLZ-Bereichen, Rabattstaffelungen, etc).
Grüße
bastla
Ich sehe Licht im Dunkel der Parameter
Einleuchtende und gute Erklärung.
Damit kann ich jetzt (hoffentlich) selbst die nächsten Tabellen durchsuchen.
Danke bastla!
Einleuchtende und gute Erklärung.
Damit kann ich jetzt (hoffentlich) selbst die nächsten Tabellen durchsuchen.
Danke bastla!
