PDA

Vollständige Version anzeigen : Vergleichsdaten aus anderer Tabelle Ziehen - SVERWEIS verschachteln?!


MUELM99
22.07.2014, 09:58
Halli Hallo!

Ich sitze hier gerade auf Arbeit und verzweifle ein wenig an einer Aufgabe, die einem frisch als Student gestellt wird. Da ich noch nicht intensiv mit Excel gearbeitet habe, und nur Grundwissen mit einbringen kann, macht es mich schon fertig, wenn ein normaler SVERWEIS nicht mehr ausreicht...

Folgendes Problem:

Ich besitze 2 Excel Dateien, in der Unterschiedliche Informationen gespeichert sind. Hier mal die 2 Dateien als Beispiele, die nur die für uns relevanten Daten enthalten:

Datei 1:

http://s1.directupload.net/images/140722/r2sifgtv.jpg

Datei 2:

http://s14.directupload.net/images/140722/mw9969a6.jpg

Natürlich sind meine Tabellen weitaus komplexer, aber das ist das, worum es hauptsächlich geht

Problemstellung:

Ich muss die leere Spalte OrgEinheit in der Datei 1 mit den jeweiligen OrgEinheit Nummern aus Datei 2 füllen! Als Vergleichswert dient dafür die PlanstellenID aus Datei 1, die verglichen werden soll mit der Planstelle aus Datei 2! (Also die entsprechend selbe ID soll raus gesucht werden, und die zugehörige OrgEinheit dann in die erste Datei eingefüllt werden!)

Mein Bisheriger Anlauf:

=WENN(ISTNV(SVERWEIS(C2;'[Datei2.xlsx]Format'!$A$2:$A$7;2;FALSCH));"";SVERWEIS(C2;'[Datei2.xlsx]Format'!$A$2:$A$7;2;FALSCH))

Da kommt leider gar nichts raus, obwohl die ID natürlich existiert. Dann habe ich mir überlegt, dass ich da noch etwas hätte verschachteln müssen - jetzt bin ich jedoch total verwirrt.

Muss ich mit VBA ran? Wenn ja... wie würde eine VBA Lösung aussehen? In VBA kenne ich mich leider wenig bis gar nicht aus...

Danke im Vorraus für alle Antworten und schöne Mittagspause dann!

Marcel

herbearm
22.07.2014, 10:38
=INDEX([Datei2.xlsx]Tabelle1!$B:$B;VERGLEICH(C2;[Datei2.xlsx]Tabelle1!$A:$A))

Einfach nach unten kopieren oder aber mit Str+T den Bereich in eine Tabelle wandeln, dann wird die Formel immer automatisch fortgeschrieben falls Max und Peter noch Kollegen bekommen sollten :D

SVERWEIS hat halt immer des Problem, dass die Werte nach Größe geordnet sein müssen. Index bietet sich für solche Abfragen eigentlich auch immer an.
Der erste Wert ist der Wert der ausgegeben werden soll, dann folgt das Suchkriterium und der Bereich in dem im Index gesucht wird. Sobald es etwas komplexer wird ist Index eigentlich immer die feinere Variante.

Beste Grüße
Armin

Beverly
22.07.2014, 10:43
Hi,

benutze anstelle SVERWEIS() die Funktionen INDEX() und VERGLEICH()
<br/><b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:106px;" /><col style="width:106px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Planstelle</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">OrgEinheit</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">120</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Einheit 1</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">121</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">4</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">122</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">5</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">123</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">6</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">124</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">&nbsp;</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">7</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">125</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Einheit 2</td></tr></table><br/><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px;font-size:11pt; background-color:#ffffff; width:800px;padding-left:2pt; padding-right:2pt; "><tr style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td colspan="3" > verwendete Formeln </td><td> </td></tr><tr valign="top" style="background-color:#FFCC66; text-align:center; font-weight:bold; "><td> Zelle </td><td> Formel</td><td> Bereich </td> <td>N/A</td></tr><tr><td>B2:B7</td><td>=WENNFEHLER(INDEX([Mappe2]Tabelle1!$B:$B;VERGLEICH(A2;[Mappe2]Tabelle1!$C:$C;0));"")</td><td>&nbsp;</td><td>&nbsp;</td></tr></table><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.09 einschl. 64 Bit</td></tr></table><br/>

<br/><b><em>Tabelle1</em></b><table border="1" cellspacing="0" cellpadding="0" style="border-color:#000000; border-width: 1px; font-size:11pt; background-color:#ffffff; padding-left:2pt; padding-right:2pt; "><colgroup><col style="font-weight:bold; width:40px;" /><col style="width:106px;" /><col style="width:106px;" /><col style="width:106px;" /></colgroup><tr style="background-color:#99CCFF; text-align:center; font-weight:bold; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Name</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">OrgEinheit</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">PlanstellenID</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Max</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Einheit 1</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">120</td></tr><tr><td style="background-color:#99CCFF; text-align:center; font-weight:bold; ">3</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Peter</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:left; ">Einheit 2</td><td style="border-color:#000000; color:#000000; border-color:#000000; background-color:#FFFFFF ;; text-align:right; ">125</td></tr></table><table cellspacing="0" cellpadding="0"><tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href="http://excel-inn.de/dateien/vba_beispiele/tabellenanzeige_in_html_addin.zip" >Excel-Inn.de</a></td></tr> <tr style="text-align:left; font-weight:bold; " class="style21"><td style="text-align:left; font-size: xx-small" ><a href='http://Hajo-Excel.de/tools.htm' >Hajo-Excel.de</a></td></tr><tr style="text-align:left; font-weight:bold;" ><td style="text-align:left; font-size: xx-small" >XHTML-Tabelle zur Darstellung in Foren, einschl. der neuen Funktionen ab Version 2007</td></tr><tr style="text-align:left; font-weight:bold; " ><td style="text-align:left; font-size: xx-small" > Add-In-Version 18.09 einschl. 64 Bit</td></tr></table><br/>

<hr width="20%" align="left"><img src="http://excel-inn.de/images/grusz.gif" height=35" align="left" alt="Grußformel"><a href="http://excel-inn.de/"><img border="0" src="http://excel-inn.de/images/logo1.gif" height=35" align="middle" alt="Beverly's Excel - Inn"></a>

herbearm
22.07.2014, 10:51
Und Sverweis funktioniert doch auch einwandfrei?!

=SVERWEIS(Tabelle1!$C2;[Datei2.xlsx]Tabelle1!$A$2:$B$7;2)

Aber wie gesagt. Dies geht nur wenn die Argumente nach denen gesucht wird aufsteigend sortiert sind. Sonst kann SVERWEIS schnell mal falsche Werte liefern, wie ich schmerzlich erfahren musste ^^. Deswegen wie der Kollege schon meint. Bei solchen Abfragen besser mit Index arbeiten...

Bei deiner Formel oben hast du den Bereich in dem SVERWEIS sucht falsch definiert. Die Matrix in der gesucht wird erstreckt sich über A bis B also zwei Spalten, du hast nur Spalte A einbezogen, eine Matrix mit nur einer Spalte, ausgeben soll dir die Formel aber die zweite Spalte :D Das geht wohl nicht. Einfach den Bereich auf A+B erweitern und deine Formulierung sollte auch klappen....

MUELM99
22.07.2014, 11:10
Danke erstmal für die Antworten!

Ich habe es nun also mit der zuerst genannten Version probiert...

=INDEX('[Mitarbeiterliste 01.06.2014.xlsx]Format'!$R:$R;VERGLEICH(C$2;'[Mitarbeiterliste 01.06.2014.xlsx]Format'!$F:$F))

So sieht der Befehl jetzt letztendlich aus!
(Format heißt die Mappe in der Datei2, R Ist die Spalte in der die OrgEinheit steht, C die Spalte in Datei 1, in der die PlanstellenID der ersten Datei steht, und F die Spalte, in der die PlanstellenID der zweiten Datei steht).

Aus irgend einem Grund liefert er mir jedoch nur die OrgEinheit 0000000, obwohl das eigentliche Ergebnis 30000264 sein sollte. (Habe die Planstellen ID in der zweiten Datei per Strg.+F gesucht, und die zugehörige OrgEinheits Nummer abgelesen. Doppelt kommt die Planstellen ID NICHT vor!!).

Was habe ich falsch gemacht? :boah:

EDIT: Ich habe mich vertan! In Datei 1 stand die ID in der Spalte D, habe C also auf D geändert. Jetzt sagt er mir als Ergebnis #NV ...

herbearm
22.07.2014, 11:40
Dollarzeichen nach dem C bzw. D erstmal entfernen?! Ansonsten Spalten bitte prüfen. So sollte es eigentlich funzen!?

Gruß

MUELM99
22.07.2014, 11:43
Dollarzeichen nach dem D!! ist jetzt weg. leider noch selbiges Ergebnis! :( Also #NV

herbearm
22.07.2014, 12:03
Mach mal ne array formel draus... geh in die Zelle und klicke nochmal, sodass du im Formelfeld landest. Drücke jetzt Strg +Shift + Enter. Dann macht er array formeln draus (siehst du an geschweiften Klammern, die jedoch nur über die Tastenkombi erzeugt werden)...

Anschließend mal die Formel nach unten kopieren. Hilft das vielleicht?

Gruß Armin

steve1da
22.07.2014, 12:31
Hola,

eine Array Formel macht an der Stelle keinen Sinn.

#NV bedeutet, dass er diesen Wert nicht in dieser Form in der anderen Datei findet - das kann auch schon an einem fehlerhaften Format liegen.

Wenn du den Suchwert mit einem vorhandenen Wert in der anderen Datei vergleichst, via:

=C$2='[Mitarbeiterliste 01.06.2014.xlsx]Format'!Zelle_mit_identischem_Wert

was kommt dabei raus, WAHR oder FALSCH?

@herbearm: das der Sverweis nur aufsteigend sortierte Listen braucht ist Quatsch - dafür gibt es den 4. Parameter FALSCH.

Gruß,
steve1da

MUELM99
22.07.2014, 14:38
Aloha!

Thema hat sich erledigt...
Eine gute Kollegin kam daher, hat einfach mal alle Werte mit 1 Multipliziert, den SVERWEIS nicht händisch geschrieben, sondern ein wenig hier und da rum geklickt und TADAAA... es geht.

Manchmal kommt es mir so vor, als denke ich ZU kompliziert... Visual Studio sei Dank.

Kann geclosed werden.^^

herbearm
23.07.2014, 07:42
Naja, die Daten in der ersten Spalte der Suchmatrix müssen aufsteigend sortiert sein, sonst wirft SVERWEIS gern mal falsche Werte zurück. Und das durfte ich schon am eigenen Leib spüren. Da nützt auch FALSCH nix ^^

steve1da
23.07.2014, 08:11
Hola,

dem ist eben nicht so!
Schreib doch mal in A1 bis A10 die Zahlen von 10-1 absteigend, in B1 bis B10 irgendwelche Werte. Wenn du jetzt in E1 z.B. 5 schreibst und den Sverweis mit FALSCH einsetzt, kommt das richtige Ergebnis raus:

=SVERWEIS(E1;A1:B10;2;0)

Gruß,
steve1da

Edit: siehe auch die Microsoft Hilfe dazu:

Wenn der Parameter FALSCH ist, sucht SVERWEIS eine genaue Entsprechung. In diesem Fall müssen die Werte in der ersten Spalte von Matrix nicht sortiert werden. Wenn zwei oder mehr Werte in der ersten Spalte von Matrix mit Suchkriterium übereinstimmen, wird der erste gefundene Wert verwendet. Wenn keine genaue Entsprechung gefunden wird, wird der Fehlerwert #NV zurückgegeben.