Supportnet / Forum / Tabellenkalkulation
Tabellen verknüpfen SVERWEIS
Frage
Moin:
Ich melde mich nochmal mit demselben Problem wie vor 4 Tagen an Euch, werde es aber anders erklären!
Ich habe 2 Tabellen:
Tabelle 1 enthält 2 Spalten mit dem Autotypen und der PS-Zahl:
Audi A2, 50 PS
Audi A3, 70 PS
Audi A4, 100 PS
Die Tabelle 2 enthält wieder Autotypen und den Hubraum:
Audi A2, 1,6 ltr
Audi A6, 2,5 ltr
Audi A8, 3,0 ltr
Nun enthalten beide Tabellen gleiche, aber auch verschiedene Autotypen, wie bastel ich die Tabellen mit SVERWEIS zusammen, so daß am Ende in der Tabelle folgendes steht:
Audi A2, 50 PS, 1,6 ltr
Audi A3, 70 PS, ---
Audi A4, 100 PS, ---
Audi A6, ------, 2,5 ltr
Audi A8, ------, 3,0 ltr
Danke für die Hilfe.
Antwort 1 von Coolpix
hallo haitha,
aaalssooo...
in Tabelle1 steht z.B. in Saplte "A" der Fahzeugtyp
und in Spalte "B" die PS-Zahl
in Tabelle2 steht wieder in Saplte "A" der Fahzeugtyp
und in Spalte "B" der Hubraum
ein SVERWEIS baut sich wie folgt auf
=SVERWEIS(Suchkriterium;Suchmatrix;Ausgabespalte;Bereich_Verweis)
d.h.
wenn bei dir in Tabelle1 in Zelle A3 "Audi A3" und in Zelle B3 "70 PS" steht müßtest du in C3 folgenden SVERWEIS schreiben:
=SVERWEIS(A3;Tabelle2!$A$1:$B$30;2;falsch)
in deutsch heißt das:
suche den Wert aus Tabelle1 A3 in der Tabelle2 in der ersten Spalte des festen Bereiches A1 bis B30 (die $-Zeichen fixieren Spalten und Zeilen damit man die Formel kopieren kann) und gebe den Wert aus , der im fixierten Bereich in Spalte 2 steht.
Das "falsch" schreibt man hin um einen 100% Wertevergleich machen zu können ohne die Suchmatrix sortieren zu müssen.
ich hoffe das ist verständlich ausgedrückt...
noch'n Tipp:
versuch's mal mit dem Funktionsassistenten "fx"
der führt eigentl. ganz gut durch sowas.
Greetings ;-)
aaalssooo...
in Tabelle1 steht z.B. in Saplte "A" der Fahzeugtyp
und in Spalte "B" die PS-Zahl
in Tabelle2 steht wieder in Saplte "A" der Fahzeugtyp
und in Spalte "B" der Hubraum
ein SVERWEIS baut sich wie folgt auf
=SVERWEIS(Suchkriterium;Suchmatrix;Ausgabespalte;Bereich_Verweis)
d.h.
wenn bei dir in Tabelle1 in Zelle A3 "Audi A3" und in Zelle B3 "70 PS" steht müßtest du in C3 folgenden SVERWEIS schreiben:
=SVERWEIS(A3;Tabelle2!$A$1:$B$30;2;falsch)
in deutsch heißt das:
suche den Wert aus Tabelle1 A3 in der Tabelle2 in der ersten Spalte des festen Bereiches A1 bis B30 (die $-Zeichen fixieren Spalten und Zeilen damit man die Formel kopieren kann) und gebe den Wert aus , der im fixierten Bereich in Spalte 2 steht.
Das "falsch" schreibt man hin um einen 100% Wertevergleich machen zu können ohne die Suchmatrix sortieren zu müssen.
ich hoffe das ist verständlich ausgedrückt...
noch'n Tipp:
versuch's mal mit dem Funktionsassistenten "fx"
der führt eigentl. ganz gut durch sowas.
Greetings ;-)
Antwort 2 von Haitha
Gut, danke Coolpix, SVERWEIS verstanden.
In die SVERWEIS-Spalte (Tabelle 1, Spalte C) wird mir den Hubraum von Audi A2 hingeschrieben!!!
Audi A3 und Audi A4 findet der SVERWEIS in der Tabelle 2 nicht, weil die beiden Typen in Tabelle 2 ja nicht auftauchen. Deswegen wird in Tabelle 1 #NV ausgegeben. Gut! Verstanden!
Aber Audi A6 und Audi A8 werden natürlich nicht in Tabelle 1 übertragen! Diese Datensätze gehen mir so natürlich durch die Lappen.
Wie kann ich verhindern, daß mir die durch die Lappen gehen?
Eine weitere Frage noch:
Warum A1:B30 als Matrix, A1:B3 tut es doch auch?!?!
Danke
Haitha
Antwort 3 von Arnim
Hallo Haitha,
so wie ich es verstehe, hast du 2 lange Listen mit den Autotypen. Um diese zu "vereinen", lege eine 3.Tabelle an, also Tabelle3.
Jetzt könnte man die ganze Prozedur auch mit Formeln bewältigen, was aber sehr aufwendig wäre.
Daher mein Vorschlag:
Angenommen, die Typenbezeichnungen sind alle in Spalte A.
1) Kopiere die Spalte A aus Tabelle1 und füge sie in Soalte A derTabelle3 ein.
2) Kopiere jetzt die Typen aus Spalte A der Tablle2 (z.B. ab A2) und füge sie unterhalb der letzten belegten Zelle der Spalte A in Tabelle3 ein.
3) Jetzt markiere die Spalte A (ab der Überschrift A1) in Tabelle3 und wähle "Daten"-"Filter"-Spezialfilter" ,setze dann das Häkchen bei "ohne Duplikate" - und "OK".
4) Schreibe in B2 Tabelle3 diese Formel:
=WENN(ISTNV(SVERWEIS(A2;Tabelle1!A$2:B$100;2;0));"";SVERWEIS(A2;Tabelle1!A$2:B$100;2;0))
und kopiere sie nach unten.
5) In C2 Tablee3 diese Formel:
=WENN(ISTNV(SVERWEIS(A2;Tabelle2!A$2:B$100;2;0));"";SVERWEIS(A2;Tabelle2!A$2:B$100;2;0))
ebenfalls nach unten kopieren.
Gruß Arnim
so wie ich es verstehe, hast du 2 lange Listen mit den Autotypen. Um diese zu "vereinen", lege eine 3.Tabelle an, also Tabelle3.
Jetzt könnte man die ganze Prozedur auch mit Formeln bewältigen, was aber sehr aufwendig wäre.
Daher mein Vorschlag:
Angenommen, die Typenbezeichnungen sind alle in Spalte A.
1) Kopiere die Spalte A aus Tabelle1 und füge sie in Soalte A derTabelle3 ein.
2) Kopiere jetzt die Typen aus Spalte A der Tablle2 (z.B. ab A2) und füge sie unterhalb der letzten belegten Zelle der Spalte A in Tabelle3 ein.
3) Jetzt markiere die Spalte A (ab der Überschrift A1) in Tabelle3 und wähle "Daten"-"Filter"-Spezialfilter" ,setze dann das Häkchen bei "ohne Duplikate" - und "OK".
4) Schreibe in B2 Tabelle3 diese Formel:
=WENN(ISTNV(SVERWEIS(A2;Tabelle1!A$2:B$100;2;0));"";SVERWEIS(A2;Tabelle1!A$2:B$100;2;0))
und kopiere sie nach unten.
5) In C2 Tablee3 diese Formel:
=WENN(ISTNV(SVERWEIS(A2;Tabelle2!A$2:B$100;2;0));"";SVERWEIS(A2;Tabelle2!A$2:B$100;2;0))
ebenfalls nach unten kopieren.
Gruß Arnim
Antwort 4 von Arnim
Hallo,
noch ein Zusatz:
Wenn Du danach die Tabellen 1 und 2 nicht mehr benötigen solltest und löschen willst, markiere vorher die mit Formeln übertragenen Zahlen in Spalten B und C der Tabelle3 und wähle "Kopieren" und danach "Inhalte einfügen"-"Werte". Sonst sind nämlich die Werte futsch!
Gruß Arnim
noch ein Zusatz:
Wenn Du danach die Tabellen 1 und 2 nicht mehr benötigen solltest und löschen willst, markiere vorher die mit Formeln übertragenen Zahlen in Spalten B und C der Tabelle3 und wähle "Kopieren" und danach "Inhalte einfügen"-"Werte". Sonst sind nämlich die Werte futsch!
Gruß Arnim