Wer mit Microsoft Excel zu tun hat, kommt an der Funktion SVerweis nicht herum.
Mit der Funktion SVerweis kann man ein Suchwort aus Tabelle 1 in Tabelle 2 finden und sich Werte aus der Zeile des gefundenen Suchwortes der Tabelle 2 ausgeben lassen.
Beispiel:
In Tabelle 1 sind Namen und Adressdaten und uns fehlen nur noch die eMailadresse, die in Tabelle 2 gespeichert sind. Dabei macht es keinen Unterschied, ob die Tabellen in einer Exceldatei in einem Tabellenblatt oder in einer Exceldatei in zwei Tabellenblätter oder in zwei unterschiedlichen Exceldateien vorhanden sind. Bei diesem Beispiel habe ich eine Exceldatei mit zwei verschiedenen Tabellenblättern.
Jetzt soll es darum gehen, in Spalte “F” der Tabelle 1, genau die Mailadressen aus Spalte “B” der Tabelle 2 zu bekommen, bei denen der Name beider Tabellen übereinstimmt.
Die SVerweis Formel kommt immer in die Tabelle, wo das Ergebnis erscheinen soll. Wir rufen also in der Zelle F3 der Tabelle 1 die SVerweis Formel auf, indem wir auf das “fx” neben der Bearbeitungszeile klicken.
Dann wählen wir bei “Kategorie auswählen” “Alles” aus, klicken einmal in den unteren Bereich “Funktion auswählen” rein. Dann ganz kurz hintereinander die Buchstaben “S” + “V” eingeben und schon landen wir bei unserer Funktion. Wenn wir SVerweis schon vorher einmal benutzt hätten, hätte Excel uns diese Funktion schon bei “Zuletzt verwendet” angeboten. Zum Schluss nur noch auf “OK” klicken.
Jetzt öffnet sich ein weiteres Fenster, der Formelassistent, der die einzelnen Argumente (so nennen sich die einzelnen Bestandteile einer Funktion) aufnimmt.
Wir klicken ins Feld “Suchkriterium” und anschließend in die Tabelle 1 in die Zelle “A3”. Die Zelladresse “A3” wird dann übernommen. Wir haben jetzt unser Suchwort bestimmt, mit dem Excel dann in der Tabelle suchen soll.
Damit Excel weiss, von wo bis wo es suchen soll, müssen wir ihm in unter “Matrix” den Bereich angeben. Dazu klicken wir erst einmal in den Formelassistent in das Feld Matrix. Dann anschließend in die Tabelle 2 und markieren mit gedrückter linken Maustaste den Bereich “A3” bis “B11”. Nicht erschrecken, wenn sich das Fenster des Formelassistenten verkleinert, es dient nur der Übersicht, sobald wir die Maustaste wieder loslassen, ist er wieder da.
Jetzt fehlt Excel nur noch die Angabe, aus der wie vielten Spalte er den Wert zurückgeben soll, der dem Suchwort entspricht. Hier tragen wir eine 2 ein, weil die Mailadressen in der zweiten Spalte vom Beginn des Matrixbereiches stehen.
Jetzt fehlt nur noch ein Eintrag aber der ist sehr wichtig. Man kann mit der Funktion SVerweis nicht nur genaue Übereinstimmungen erzielen, sondern auch mit Näherungswerten arbeiten. Dazu mehr an anderer Stelle. Wir müssen hier Excel mitteilen, dass wir an einer genauen Übereinstimmung zwischen dem Suchwort und der ersten Spalte in der Matrixtabelle (Tabelle 2) interessiert sind, also müssen wir unter “Bereich_Verweis” eine 0 (Null) eingeben.
Am Ende des Formelassistenten, kann man das Formelergebnis schon ablesen. Wenn wir alles richtig gemacht haben, sollte dort etwas stehen.
In der Bearbeitungsleiste sieht man auch den Aufbau der SVerweis Formel.
=SVERWEIS(A3;Tabelle2!A3:B11;2;0)
Nach klicken auf “OK”, wird der gefundene Wert in die Tabelle 1 übernommen.
Wer sich an dieser Stelle mit der Formel vertan hat und den Formelassistenten erneut benötigt, muss einfach nur auf das “fx” klicken aber nur dann, wenn die Zelle mit der Formel aktiv, d.h. angeklickt ist. Wer mehrere Formeln ineinander verschachtelt hat, muss dann noch vorher in der Bearbeitungsleiste auf den jeweilige Formelnamen klicken, damit der Formelassistent weiss, welche Formel gemeint ist.
Zurück zu unserem SVerweis. Excel benötigt für diesen SVerweis ein paar Voraussetzungen für den Aufbau des Matrixbereiches, also für den Bereich, in dem die Werte gefunden werden sollen.
- Die erste Spalte des Matrixbereiches, muss die Spalte sein, in dem Excel nach der Übereinstimmung des Suchwortes suchen soll
- Die Spalte, in der der Wert steckt, der ausgegeben werden soll, muss rechts von der “Suchwortspalte” liegen
Die Grundfunktion des SVerweis ist: Excel sucht senkrecht in einer Spalte nach Übereinstimmung und gibt nur Werte aus Spalten aus, die rechts von der Suchspalte liegen. Daher heißt es auch SVerweis, also Senkrechtverweis. Es gibt auch eine Formel, die WVerweis heißt. Hier sucht Excel waagerecht in einer Zeile und gibt Werte aus, die unter dieser Zeile liegen. Daher auch WVerweis, weil waagerecht gesucht wird. Viele Leute nennen den SVerweis auch Spaltenverweis, was von der Sache her richtig ist, jedoch das “S” beim SVerweis bedeutet senkrecht.
Wieder zurück zu unserem SVerweis.
Wie bekomme ich jetzt die anderen Zellen auch mit der SVerweis Formel bestückt, ohne diese erneut eingeben zu müssen (was aber einen guten Lerneffekt haben würde). Wir kopieren die Zelle “F3” aus Tabelle 1 und fügen die von “F4” bis “F6” wieder ein. Dabei muss man unbedingt aufpassen, wenn man Formeln, die Bereiche in sich bergen, innerhalb einer Exceldatei kopiert. Wir haben in “F3” folgendes zu stehen:
=SVERWEIS(A3;Tabelle2!A3:B11;2;0)
Das herunterkopieren der Formeln hat hier die Auswirkung, dass Excel auch den Matrixbereich anpasst und somit folgendes in der nächsten Zelle stehen würde:
=SVERWEIS(A4;Tabelle2!A4:B12;2;0)
Ihr seht, Excel hat zwar richtigerweise das Suchwort aus der Zelle “A4” übernommen, sucht aber in einem anderen Matrixbereich, als wir das ursprünglich vorgegeben hatten. Jetzt von A4 bis B12. Wenn sich das Suchwort aus “A4” der Tabelle 1 in der Zelle “A3” der Tabelle 2 befunden hätte, hätte Excel es mit der kopierten Formel nicht gefunden. Excel weiss an dieser Stelle nicht, ob er den Bereich fixieren muss oder nicht. Das müssen wir Excel vorgeben, bevor wir Formeln kopieren, die Bereiche in sich bergen.
Dazu löschen wir wieder die Formeln aus den Zellen, außer aus Zelle “F3”. Nun klicken wir in den ersten Teil des Matrixbereiches (Tabelle2!A3) in der Bearbeitungsleiste (dort sollte nun der Cursor sein) und klicken einmal auf die Funktionstaste “F4” unserer Tastatur und klicken sofort in den zweiten Bereich des Matrixbereiches (B11) und erneut auf die Funktionstaste “F4”. Die Formel sieht dann folgendermaßen aus:
=SVERWEIS(A3;Tabelle2!$A$3:$B$11;2;0)
Wenn wir diese Formel runterkopieren, weiss Excel, dass der Matrixbereich fixiert ist und dieser erscheint dann auch in allen anderen Formeln.
Eine Sache möchte ich noch erwähnen.
Die SVerweis Formel findet die ERSTE Übereinstimmung und gibt dann den entsprechenden Wert aus dem Matrixbereich zurück. Sollte es noch weitere Übereinstimmungen in der Tabelle 2 geben, so werden diese ignoriert, weil Excel die Suche nach dem ersten Treffer einstellt.
Daher wird auch die Mailadresse der ersten Marion ausgegeben und die der zweiten (in Zeile 11) ignoriert.
Anregungen, Kommentare und Kritik, gerne über die Kommentarfunktion. Wer Probleme mit seiner SVerweis Formel hat, kann gerne einen Kommentar schreiben und sein Problem formulieren. Wir finden eine Lösung 🙂
Hallo John Doe,
könnte ich das Ganze auch so gestalten, dass wenn ich eine zweite Tabelle mit Mailadresse hätte er zuerst Tabelle A prüft und wenn er dann kein Ergebnis findet in Tabelle B schaut ob da eines ist? Wenn er in Tabelle A fündig geworden ist, soll er dies als Ergebnis eintragen und wenn nicht dann halt in Tabelle B schauen. In Tabelle B würde dann z. B. für jeden ein Ergebnis gefunden werden.
Bei mir ginge es halt um Sonderpreise oder Listenpreise, wenn keine Sonderpreise vorhanden.
Wäre cool wenn du da eine Lösung hättest. DANKE!!!
Problem ist gelöst! Trotzdem Danke!
Lösung: =wenn(istnv(SVERWEIS_1);SVERWEIS_2;Sverweis_1)
Hallo,
das habt ihr Klasse selber gelöst, so machen wir das auch immer. So kann man auch die nervigen #NV abstellen, indem man anstelle SVERWEIS_2 eine “0” oder einen Text einträgt, der dann ausgegeben wird.
LG
John Doe
Hallo,
oben hattest Du geschrieben:
“die SVerweis Formel findet die ERSTE Übereinstimmung und gibt dann den entsprechenden Wert aus dem Matrixbereich zurück. Sollte es noch weitere Übereinstimmungen in der Tabelle 2 geben, so werden diese ignoriert, weil Excel die Suche nach dem ersten Treffer einstellt.
Daher wird auch die Mailadresse der ersten Marion ausgegeben und die der zweiten (in Zeile 11) ignoriert.”
Wie würde ich denn an die Werte der zweiten, dritten, usw. Übereinstimmung gelangen?
Gruß
Markus
Hallo Markus,
ich habe das Beispiel mit zwei “gleichen” Marions aber verschiedenen Mailadressen absichtlich gewählt, um aufzuzeigen, das Excel mit dem SVerweis genauso reagiert.
Es macht eigentlich keinen Sinn, einen SVerweis bei einer Tabelle anzuwenden, wo die Einträge nicht konsistent sind.
Normalerweise achtet man darauf, dass die Tabelle, in der ich suche einen eindeutigen Eintrag hat. In diesem Fall müsste ich die Tabelle 2, wo nur der Vorname und die Mailadresse vorhanden sind, entsprechend anpassen, also eine Marion1 und eine Marion2 bilden, die natürlich in Tabelle 1 genauso umbenannt werden müsste, damit man mit dem Suchwort “Marion1” auch nur die eine findet in Tabelle 2.
Hoffe ich konnte mich klar ausdrücken, ansonsten schreibe doch kurz auf, was du für ein Beispiel oder Problem hast.
LG
John Doe
Hallo John Doe,
mein Problem ist folgendes: Ich habe eine Liste mit verschiedenen Namen A, B, C, … Eine zweite Tabelle beinhaltet diese Namen, zu denen zu unterschiedlichen Zeiten unterschiedliche Werte zugeordnet sind. Jetzt wollte ich z.B. die ersten drei dieser Werte mir anzeigen lassen.
Als Beispiel kann man z.B. Aktienwerte, etc. nehmen.
LG
Markus
Hallo Markus,
grundsätzlich gilt beim SVerweis die Regel, dass nach dem ersten Finden, die Funktion aussteigt und keine weiteren Ergebnisse angezeigt werden (wo auch).
Wenn du in deiner ersten Tabelle Namen hast, z.B. die WKN Nummern verschiedener Aktien und du in deiner zweiten Tabelle, diese WKN Nummern und zusätzlich die Monate oder Tage (Datum), mit den jeweiligen Kursen hast, dann versuche doch, in deiner ersten Tabelle auch die Spalte Datum zu haben und bilde einen eindeutigen Schlüssel (das “verketten” von Werten, hier Zellen).
Den Schlüssel bildest du in deiner ersten Tabelle, indem du in Spalte “C” (wenn in Spalte “A” z.B. die WKN und in Spalte “B” das Datum steht) in Zelle C2 folgendes eingibst:
=A2&B2
Als Ergebnis würde dann in C2 z.B. stehen: A1C8111.1.2011 (Das Ergebnis aus WKN A1C811 und dem Datum 1.1.2011) Blödes Beispiel oder 🙂
A1C8111.1.2011 ist jetzt dein neues Suchkriterium für deinen SVerweis. In deiner zweiten Tabelle musst du ebenfalls diesen Schlüssel aus WKN und Datum bilden, damit der SVerweis die Werte findet (natürlich ganz weit links einbauen, weil der SVerweis nur nach rechts durchsucht ;-).
Schreibe mir, wenn irgendwas noch unklar ist.
LG
John Doe
Hallo John Doe
Könntest Du mir mit einer Formel helfen. Ich möchte den Kurs aus Tabelle B (bendepotaktiv)Spalte I in Spalte G der Tabelle A (depotaktiv) kopieren, sofern die Isin nummer von Tabelle B mit der Isinnummer der Tabelle A übereinstimmen. Beispiel
Spalte A Spalte G
depotaktiv (A) isin kurs
AU000000BHP4 46.05 empfangen
bendepotaktiv (B) Spalte D Spalte I
isin Kurs
AU000000BHP4 46.05 senden
diese Aktion für alle Werte in Tabelle A.
Kannst Du mir helfen und das Resultat auf mein emaillkonto mitteilen.
Danke Hans
Hallo Hans,
sehe bei deinen Spalten nicht ganz durch:
Kannst du bitte kurz notieren, was wo drin ist?
Tabelle A
Spalte A | …….| Spalte G
Tabelle B
Spalte A | ….| Spalte D |….| Spalte I
LG John Doe
Hallo John Doe, ich komme hier, weil ich bei Deiner Antwort keinen Button gefunden habe.
Also ich habe 2 exceldateien,
depotaktiv wkn spalte a, kurs spalte g
bendepotaktiv2 wkn spalte d, kurs spalte I
Ich habe foldende Formel eingegeben in g15
Suchbegriff a15. Matrix “bendepotaktiv2!a$2:i$100
spalte 9
falsch
Aber ich habe ein NV erhalten, dh er hat den Begriff nicht gefunden. Wo liegt der Fehler?
Du kannst mir auch Deine emailadresse angeben, dann sende ich Dir die beiden Dateien.
Danke Gruss Hans
Hallo Hans,
anbei deine beiden Dateien. Nicht aus der Mail heraus öffnen. Speicher sie bitte zusammen im gleichen Ordner ab und öffne dann beide.
Ich habe in deine depotaktiv Werte aus bendepoaktiv2 per Sverweis übertragen. Dazu habe ich die Zeile 24 kopiert und in Zeile 25 eingefügt, damit du die vergleichen kannst. In die Zelle G25 habe die SVerweis Formel.
Funktioniert soweit gut. Wo liegt das Problem?
LG John Doe
Hallo John Doe,
habe ein kleise Problem und hoffe Du kannst mir helfen, wäre sehr nett.
73075262 002500 15016723 ZYL-ROLLENLAGERSATZ SL182968 C3.2S BR
73075262 002600 15016724 ZYLINDERROLLENLAGER SL18 1840 C3 BR
73075262 002700 15016725 ZYLINDERROLLENLAGER SL18 2936 C3 BR
73075262 002800 15016726 ZYLINDERROLLENLAGER SL18 5044 C3 BR
73075262 002900 15016726 ZYLINDERROLLENLAGER SL18 5044 C3 BR
73075262 003000 15016729 AXIALZYLINDER-ROLLENLAGER 89324 M
73075262 003300 15016725 ZYLINDERROLLENLAGER SL18 2936 C3 BR
73083050 000100 20407128 METALASTIK-BUCHSE
73084423 000100 20612680 S-GEHAEUSE, GESCHLOSSEN WPR 58
73084423 000200 20612682 S-GEHAEUSE, GESCHLOSSEN WPR 58
73084423 000300 20612686 S-GEHAEUSE, GESCHLOSSEN WPR 58
73085951 000100 10041321 WECHSELVENTIL
73085951 000200 15016300 RUECKSCHLAGVENTIL
73085951 000300 15018687 TEMPERATURFUEHLER MTS 2000 F.SCHMELZE
73085951 000400 20402836 KUPPLUNGSHUELSE
73085951 000500 20000387 GETEILTER KEGELRING
73085951 000600 20007171 GEWINDERING LINKS M210X4
Ich möchte per sverweis auf einenm anderen Tabellenblatt einen Auftrag ausgeben mit den dazugehörigen Positionen.
Leider bekomme ich immer nur eine Pos. angezeigt, ich hoffe Sie haben dafüe auch eine Lösung
Gruß
Vitze
Hallo Vitze,
ich gehe davon aus, dass die ersten 8 Ziffern die Artikelnummer ist und die weiteren 6 Ziffern die Positionsnummer
Der SVerweis ist so konzipiert, dass er nach Auffinden des ersten Wertes seine Suche einstellt, daher nur ein Ergebnis, pro Artikelnummer.
Aber du kannst den SVerweis trotzdem nehmen, wenn du ihm als Suchkriterium einen eindeutigen Schlüssel gibst, indem du z.B. die Artikelnummer und die Positionsnummer miteinander verknüpfst.
Füge eine neue Spalte ein, am Besten gleich ganz vorne, als Spalte A
Dann hast du folgendes:
Spalte A: Schlüssel (aus B&C)
Spalte B: Artikelnummer
Spalte C: Positionsnummer
Nun bildest du in Spalte A den Schlüssel aus Artikelnummer & Positionsnummer
Beispiel:
Spalte A | Spalte B | Spalte C
=B2&C2 | 73075262| 002500
In Spalte A (in z.B. A2) steht dann 73075262002500
Das ist dein neues Suchkriterium für deine andere Tabelle.
Dort muss dann natürlich auch dieser Schlüssel gebildet werden. Somit gibt es dann in jeder Zeile einen eindeutigen Schlüssel, mit Hilfe dessen der SVerweis dann jedes Ergebnis genau ausgibt.
Solltest du mich nicht ganz verstanden haben, schicke doch einfach deine Tabellen an sverweis@pcnotfallhilfe.de
Dann schaue ich mir das mal an.
LG
John Doe
Hallo John,
vielen Dank für Deine Amntwort, habe Dir eine externe Mail zukommen lassen.
Gruß
Vitze
Hallo Vitze,
guck dir mal die Tabelle2 an.
Dort habe ich dir die ersten 4 mit dem SVerweis “verdrahtet”. Du brauchst nur die Spaltenzahl um eine weiter führen, also aus:
=IF(ISERROR(VLOOKUP(A3;Tabelle1!$A$3:$AE$3555;2;0));””;VLOOKUP(A3;Tabelle1!$A$3:$AE$3555;2;0))
in B2 wird
=IF(ISERROR(VLOOKUP(A3;Tabelle1!$A$3:$AE$3555;3;0));””;VLOOKUP(A3;Tabelle1!$A$3:$AE$3555;3;0))
in C2
usw.
Die Abfrage IF(ISERROR( habe ich drin, damit es nicht das nervige #NV gib.
Ich hoffe, du kommst damit klar.
John Doe
Hallo,
ich hoffe ich darf hier mal mein anliegen schreiben 🙂 habe diese mail auf pcnotfallhilfe.de gesehen und dort ging es um mein problem … nur bekomme ich keine lösung 🙁
ich will aus zwei verschiedenen exceltabellen (gleicher aufbau) daten übertragen. aus datenschutzgründen kann ich leider die tabellen nicht senden.
ich beschreibe mal den (wichtigen) aufbau:
spalten:
a: beabeitungsvermerk
i: vertragsnummer
name tabelle 1: Offene Auflagen Finanzierung Antrag Finanz 2012-11 (zeilen: 286 – 1 spaltenname)
name tabelle 2: Offene Auflagen Finanzierung Antrag Finanz 2012-12 (zeilen: 256 – 1 spaltenname)
ich möchte, dass sverweis die vertragsnummern von tabelle 2 spalte i mit den vertragsnummern von tabelle 1 spalte i abgleicht und bei übereinstimmung den bearbeitungsvermerk von tabelle 1 spalte a ind tabelle 2 spalte a kopiert.
ich bekomme es einfach nicht hin 🙁 bspw. bekomme ich dieses funktionsargument, wenn ich als matrix spalte i tabelle 1 markiere: ‘[Offene Auflagen Finanzierung Antrag Finanz 2012-11.xls]Antrag Finanz 2012-11’!$I$2
um himmelswillen, ich bin am verzweifeln … wie muss die formel denn aussehen? wo ist mein fehler?
vielen dank schonmal.
Tom
Hallo Tom,
entschuldige, dass ich mich erst jetzt melde.
Ich weiss wo dein Problem liegt.
Der SVerweis nimmt sich den Eintrag aus Tabelle1 aus Spalte i in Zeile 2 (das ist das Suchwort) und geht damit in die Tabelle2 und sucht in der Spalt i nach diesem
Suchwort. Soweit OK.
Wenn er dann das Suchwort (die Vertragsnummer) übereinstimmend in Tabelle2 gefunden hat, dann kann der SVerweis nur Daten zurückgeben, die RECHTS von Spalte i stehen.
Da aber die Bearbeitungsbemerke in Spalte A stehen, geht das nicht mit SVerweis.
Zwei Möglichkeiten:
1.
Die Tabellenstruktur ändern, d.h., grundsätzlich die Spalte i links von Spalte A führen. Dazu die Spalte i markieren, kopieren. Dann die Spalte A Markieren und mit der rechten Maustatste den Menüpunkt “kopierte Spalten (oder Zellen) einfügen. Danach den SVerweis noch einmal probieren.
2.
Eine andere Formel benutzen. Mit Index und Vergleich geht das auch.
Die Formel müsste dann so aussehen:
=INDEX(‘[Offene Auflagen Finanzierung Antrag Finanz 2012-11.xls]Antrag Finanz 2012-11′!$A:$A;VERGLEICH(I2;'[Offene Auflagen Finanzierung Antrag Finanz 2012-11.xls]Antrag Finanz 2012-11′!$I:$I;0))
Die Werte hinter INDEX ist die Ausgabespalte.
Ab Vergleich kommt als erstes das Suchwort aus i2, das dann in der Tabelle “Antrag Finanz 2012-11’!$I$2:$I$286” gefunden werden soll. Zum Schluß muss noch der Parameter “0” gesetzt werden, damit eine genaue Übereinstimmung angestoßen wird.
Die Index/Vergleich Formel ist ein bisschen komplizierter.
Viel Erfolg und wenn es klappt eventuell auch positives Feedback?
LG John Doe.
Hallo Tom,
anbei die beiden Dateien.
Ich habe dir die Index/Vergleich Formel eingegeben.
ich denke es ist immer problematisch, wenn man die eigene Tabellenstruktur anpassen muss.
Dann müssen eben die richtigen Formeln her . :-)))
Nochmal zum Verständnis:
=Index(die Spalte aus der das Ergebnis genommen wird;Vergleich(das Suchwort;die Spalte in der das Suchwort gefunden werden soll;0))
LG John Doe
Uff, Hausarbeit gerettet 🙂
Aber persönlich finde ich die Funktion einfach umständlich bzw. verwirrend benannt; wenn man schon mal bissl was mit VBA gemacht hat, ist der SVerweis und die Dokumentation einfach “umständlich formuliert” innerhalb von Excel :-/
Grüße
Hi Lelala,
kommt immer auf den Betrachtungspunkt an. Wenn man SVerweis als “senkrecht Verweis” oder “Spalten Verweis” übersetzt und immer davon ausgeht, dass Excel das erste Vorkommen nimmt, alle weiteren ignoriert und beim Treffer die Anzahl der angegebenen Spalten nach rechts geht, um den Wert zurückzugeben……….
Kann man das ganz kurz erklären 🙂
Was hat das mit den Hausaufgaben auf sich. Hattest du als Aufgabe etwas über den Sverweis zu schreiben oder den sogar anzuwenden?
LG John Doe
Hallo John
Deine Formel funktioniert ausgezeichnet mit den Dateien, die Du mir zurückgeschickt hast. Ich habe die in einem Ordner Jon Doe in Dokumente abgelegt. Dort geht das. Aber ich habe die Exceldateien auch im Ordner Depots auf dem Desktop. Auch wenn ich den Pfad eingebe funktioniert das nicht. Kann es daran liegen, dass die Dateien unter dem gleichen Namen in zwei verschiedenen Ordnern abgelegt sind. Wie soll ich vorgehen (kopieren oder so) oder kannst Du mir die Formel für depotaktiv und bendepotaktiv2 im Ordner depots mitteilen.
Sorry für die Frage. Gruss Hans
Hallo Hans,
da gibt es eine Möglichkeit.
Öffne beide Dateien aus dem Ordner “John Doe”.
Dann speicherst du die Datei, wo die SVerweis Formel NICHT enthalten ist (bendepotaktiv2.xls) unter dem anderen Ordner (da wo sie hin soll).
Jetzt schliesst du die bendepotaktiv2.xls. Die depotaktiv.xls Datei auch und wenn du gefragt wirst ob du die Datei depotaktiv.xls speichern willst, musst du auf JA klicken.
Jetzt hat die Sverweis-Formel den kompletten Pfad zur bendepotaktiv2.xls und du kannst die depotaktiv.xls verschieben wohin du willst.
LG John Doe
Hallo John
Auch das funktioniert prima. Da ich den Ordner John Doe nicht mehr benötige, möchte ich ihn löschen. Der Pfad unter desktop\depots\depotaktiv bezieht sich aber auf den Pfad John Doe. Wenn ich die Dateien in John Doe lösche, könnte ich mir vorstellen, dass die Formeln im neuen Depotaktiv nicht mehr funktionieren, weil ein Pfad angegeben wird, der nicht mehr existiert. Wie kann ich die Formel ändern, sodass sie sich auf bendepot2 im Ordner Depots bezieht?
Sorry für die lästigen Fragen, Du hast mir aber immer geholfen.
Gruss Hans
Hallo Hans,
Voraussetzung, beide Dateien sind geöffnet !!!!!!
Wenn du die Datei in der der SVerweis NICHT drin steht, in einen anderen Ordner abspeichert, dann ändert sich doch der Pfad in der Datei, wo der SVerweis drin ist entsprechend.
Beide Dateien schliessen (wenn Frage nach speichern kommt, mit Ja beantworten)
Anschliessend kannst du die Datei, wo der SVerweis drin ist, ist einen anderen Order verschieben. Somit müssten beide Dateien ausserhalb des Ordners “John Doe” liegen.
Öffne beide Dateien, aus den “richtigen” Ordnern heraus überprüfe das Ergebnis.
Danach kannst du den Ordner John Doe löschen.
LG John Doe
Hallo John
Ich getraue mich fast nicht mehr Dich um Rat zu bitten.
Vielleicht ein letztes
Wenn ich den Kurswert in bendepotaktiv2 ändere, wird es in depotaktiv nicht übernommen. Ich musste die Formel wieder einfügen und dann geht es wieder. Wenn ich also ein xls von der Bank mit neuen Kursen überschreibe geht das nicht. Was mache ich falsch?
Wenn ich zwei gleiche Aktien in depotaktiv habe, wird bei der zweiten Aktie der Wert NV ausgegeben. Wie kann ich das verhindern.
Ich hoffe ich Dich dann nicht mehr bemühen muss.
Danke Hans
Hallo Hans,
kein Problem. Solange ich Zeit habe geht das i.O.
Die SVerweis Formel musst du immer in der depoaktiv stehen lassen. Jede Kursänderung in der bendepoaktiv2 muss dann in der depoaktiv sichtbar werden.
Welche “xls von der Bank” bekommst du denn neu? Die bendepotaktiv2 oder die depotaktiv?
Zwei gleiche Aktien:
Wenn die zweite Aktie genau die gleiche Bezeichnung hat (der Abgleich mit der Spalte “Valor” aus der bedepoaktiv2), müsste auch das Ergebnis des SVerweis gleich sein.
Vergleich das noch mal und poste es hier.
hat das mit dem Speichern an einem anderen Ort funktioniert?
LG John Doe
Hallo John
Ich hab einfach mal einen Kurs in bendepotaktiv2 geändert und wollte sehen ob dieser Kurs in depotaktiv ersichtlich ist. Aber der alte Kurs blieb stehen obwohl ich die Formel nicht geändert habe. Ich beziehe meine Kurse UbsQuotes, die kann wird dann in einem Excel ausgeben. Dieses Excel speichere ich dann auf bendepotaktiv2 ab. Dann müssen die neuen Kurse beim öffnen von depotaktiv übernommenwerden. Das erleichtert mir die Arbeit, da ich sonst die Kurse händisch eingeben muss. Also ich habe nochmals kontrolliert. Es war tatsächlich so, dass ich die Valorennummer falsch eingegeben habe.es funktioniert super. Dies alles sind nur Testbeispiele. Ich versuche nun in der Bankinformation die echte Datei als excel auszugeben. Die wird dann als separates Fenster dargestellt. Wenn ich nun diese Datei zb als bendepotaktiv2 übertrage, kopiere, dann sollte mir die echte Datei die Kurse übernehmen. Ich werde das jetzt mit einer Kopie der Original datei versuchen. Wenn das geht wäre das Problem gelöst. Ferner werden die Kurse in verschiedenen Tabellen aufgelistet, sodass ich die Kurse von tabelle 2… in Tabelle 1 kopieren muss. Das ganze ist für einen Mittelklasse xls ler schon kompliziert.
Uebrigens das mit dem Speicher am andern Ort hat funktioniert und ich habe den Ordner John Doe gelöscht, sorry. Ich komme schrittweise weiter und bin glücklich, wenn ich das mal schaffe, meine Frau glaubt nicht daran, ich aber mit Deiner Hilfe schon.
Herzlichen Dank
Hans
Hallo John
Es ist alles gut gegangen, herzlichen Dank. Nun wird’s aber komplizierter und ich habe die Formel nicht richtig eingeben, dh ich kenne sie echt nicht. Ich habe beide Dateien beigefügt.
Depothanstest ist die empfangende Datei, Valor Spalte A, Kurs Spalte G
Depothansxls ist die abgebende Datei, Valor Spalte C, Kurs Spalte I (diese Datei kopiert von der Bank UBS)
Wie üblich den Kurs von Depothansxls Kurs speichern in Depothanstest.
Die Kurse in Depothansxls sind unter verschiedenen Tabellen gespeichert, Aktien, Obligationen, Fonds usw.
Wenn das funktioniert, möchte ich die Formeln in der Testdatei Depothanskopie abspeichern. Wenn das funktioniert kopiere ich die Formeln in die Datei ‚aktienbestehend‘ und wenn das funktioniert habe ich meine Aufgaben erledigt.
Wenn das zuviel ist, sage es mir einfach, dann gebe ich auf.
Ich freue mich auf Deine Antwort.
Gruss Hans
Hallo Hans,
anbei die beiden Dateien zurück.
In der Spalte “G” habe ich dir die SVerweisformel eingeben. Die ist dann nützlich, wenn man weiss, in welchem Tabellenblatt dein “Valor” steht (Aktien, Rohstoffe usw…)
In Spalte “H” habe ich dir eine SVerweisformel eingegeben, die solange alle Tabellenblätter durchgeht (müssen namentlich vorher bekannt sein), bis sie entweder den richtigen “Valor” trifft oder nach dem letzten Tabellenblatt ohne Fund “nicht im Portfolio” ausgibt.
Die Formal fragt auch vorher ab, ob die Zelle “Valor” leer ist. Das bedeutet, du kannst die Formel komplett runterziehen (so viele Zeilen wie du willst) und wenn du vorne einen dir bekannten “Valor” aus der depothansxls.xls eingibst, werden die Kurs in Spalte “H” angezeigt. Wenn du die Formel aus “H” in Spalte “G” haben willst, dann greife dir die Zelle und “verschiebe” sie nach “G”, nicht kopieren. Probier das mal aus, indem du mehr Aktien oder Rohstoffe in die depothansxls.xls eingibts und dann in der depothans.xls vorne den Valor eingibst.
Das mit den beiden Dateien im gleichen Ordner und dann nachträglich trennen, kennst du ja jetzt. Ich lege sie immer in einem Ordner ab, ist einfacher.
Hoffe du kommst klar und ich konnte dir helfen.
LG John Doe
Hallo John
Alles funktioniert. Mir gefällt die Formel in H besser, dann kann ich diese Formel für alle Positionen verwenden. Ich weiss allerdings noch nicht wie ich die Formel von Depothanstest in die Datei Depothanskopie überspielen kann. Kopieren geht nicht, ziehen auch nicht.
Wenn sie einmal dort ist, kann ich sie ja in alle Positionen kopieren.
Kannst Du mir helfen.
Gruss Hans
nB Du arbeitest ja auch an Sonntagen, danke
Hallo Hans,
na wenn es die zeit zuläßt. Und Excel ist für mich keine Arbeit 🙂
Du musst die Formel von der einen Datei aus Zelle H2 (oder H3, H4 usw…) in die andere Datei in die gleiche Zellenposition (H2, H3 oder H4) kopieren.
Dann kannst du sie in der neuen Datei verschieben.
LG John Doe
Hallo John
Ich habe von depothanstest h12 kopiert in depothanskopie auf h12 (leeres Feld) und den =#wert! erhalten. Kommentar Inkompabilität mit akt. Dateiformat nicht alle Dateien richtig eingefügt. überprüfe Eingabebereiche.
Was mache ich falsch?
Hans
Hallo Hans,
das kann ich dir aus der Ferne auch nicht sagen 🙂
Liegen beide Dateien im gleichen Verzeichnis?
Hat die Datei, wo die Formel NICHT drin steht auch alle Tabellenblätter, wie in den Formeln angegeben?
usw…
Schicke sie mir, dann kann ich mal schauen.
LG John Doe
Hallo John
Ich habs gefunden. Die Spalte G funktioniert, d.h. dort wo die Tabellen in der Formel sind, also die einfachere. Gibt bei Mutationen etwas mehr Arbeit, aber ich werds versuchen. Wenn alles optimal läuft melde ich mich.
Nochmals Dank
Hans
Hallo John
Nachdem es sich um einen Kopatilitäsfall handelt (nicht mit aktuellem Dateiformat)könnte ich mir vorstellen dass das Problem bei der Kompatibiltät liegt. Die Datei, die Du mir zurückgeschickt hast ‘depothanstest’ hat keinen Kompatibilitstäsmodus.Alle übrigen Dateien haben Kompatibiltätsmodus. Kann man den einschalten, geht es vielleicht. Ich versuche noch die Formel mit den einzelnen Tabellen.
Gruss Hans
Hallo John
Jetzt hat alles geklappt, war eine grosse Uebung. Ich würde Dich zu einem Festessen einladen, aber Du bist ja nicht in Zürich.
Gibst Du auch Auskunft für übrige PC-Probleme oder ist das jemand anders, für Excel oder andere Probleme?
Herzlichen Dank und viele Grüsse aus der verschneiten Schweiz. Zum Glück haben wir heute eine Silbermedaille an der WM geholt, wird wohl die einzige sein.
Gruss Hans
Hallo Hans,
freut mich, dass ich helfen konnte.
Wenn du PC Probleme haben solltest, schreib mir 🙂
LG John Doe
Hallo John
Ich habe Dir ein mail geschickt mit zwei Dateien und Dich ersucht mit die Formel für eine Kopie der Währungskurse anzugeben. Hast Du das mail erhalten?
Gruss Hans
Hallo Hans,
ja habe ich aber ich bin noch nicht dazu gekommen.
Kümmere mich die Tage drum.
LG John Doe
Hallo John
Noch eine Formel, in der ich durchschaue:
in depotaktiv (c\users\mm\desktop\depots\währungenxls
will ich in Zeile 2, Spalte F einen währungskurs einfügen. Suchbegriff Spalte F, Zeile 1 (zb usd/chf).
In währungenxls (auch unter depots) befindet sich der Kurs zum kopieren in Spalte C, der Suchbegriff in Spalte B. In Spalte D befinden sich die Einheiten, dh 1=vorhandener Kurs, 100 = Kurs für 100 Einheiten, dh das Resultat 100, muss noch durch 100 geteilt werden und mit 4 Kommastellen ausgegeben werden.
Beispiel:
depotaktiv F2 = Kurs einfügen, F1 = Suchbegriff zb usd/chf.
währungenxls B 41 Suchbegriff (usd/chf), c 41 = zu kopierender Kurs ,
wenn in Spalte D 100 steht, muss der Kurs noch durch 100 geteilt werden, bei 1 einfach kopieren.
Ich hoffe, dass ich mich klar ausgerückt habe, sonst sende ich Dir noch die beiden Dateien.
Erwarte gerne Deine Hilfe.
Gruss Hans
OK Hans,
wie schon geschrieben, bin ich die nächsten Tage dran.
LG John Doe
Meine Anfrage an John wurde blockiert, wieso?
Hi,
weiss nicht was du meinst.
OK John, sorry für die Bemerkung. Danke für die nächste Antwort. Ich bin gespannt, denn ich habe es nicht realisiert.
Gruss Hans
Hallo Hans,
anbei die Dateien wieder zurück.
Habe erst jetzt Zeit finden können.
Im Grunde ist das ein klassischer SVerweis aber hier müssen wir einen kleinen Trick anwenden. Ich frage einfach die 3 Spalte ab (die Einheit 1 oder 100). Abhängig vom Ergebnis (1 oder 100), liest er den Wert aus Spalte 2 aus oder teilt ihn noch durch 100.
=WENN(SVERWEIS(F1;[währungenxls.xls]Tabelle1!$B$1:$D$43;3;0)=1;SVERWEIS(F1;[währungenxls.xls]Tabelle1!$B$1:$D$43;2;0);SVERWEIS(F1;[währungenxls.xls]Tabelle1!$B$1:$D$43;2;0)/100)
Diese Vorgehensweise setzt voraus, dass jede Währungskombination, in der Währungsdatei vorkommt.
Ich hoffe dir geholfen zu haben.
LG John Doe
Vielen Dank für diese verständliche und nachvollziehbare Erklärung der SVERWEIS Funktion. Hat mir sehr geholfen!
Moin Moin,
also vllt wurde es ja schon beantwortet aber ich hab folgendes Problem.
Ich habe meine SVERWEIS-Formel genau so aufgeführt wie oben beschrieben und es klappt einfach nicht, vielleicht habe ich ja ein Fehler drinne den ich einfach nicht sehe.
1.)Mappe Kostenberechnung
2.)Mappe Einheitspreise
XY=SVERWEIS(D10;Einheitspreise!B4:C19;2;0)
Ich bekomme immer #NV
Ich will nur das wenn ich die Artikelnr. in D10 eintrage excel die Bezeichung automatisch übernimmt und es will einfach nicht funktionieren
wenn möglich auf WENN-Funktion verzichten
bitte helfen
Mit freundlichen Gruß
Hauke
Hallo Hauke,
schick mir doch mal deine beiden Dateien, dann schaue ich es mir an.
LG John
Lieber John,
ich möchte gerne einen SVERWEIS anlegen, der 3 Spalten einer Tabelle gleichzeitig anzeigt. Ich habe zudem ein Listenfeld eingefügt. Zur Veranschaulichung habe ich meine Excel-Datei auf folgender Webseite hochgeladen: (Link entfernt)
Leider habe ich einen Fehler in meiner Formel und komme einfach nicht weiter.
Könntest du mir bitte helfen?
Im Voraus vielen Dank. Falls du weitere Erläuterungen benötigst, melde dich gerne jederzeit!
Freundliche Grüße
Hallo John,
es hat sich schon erledigt. Ich habe die Antwort bekommen. Vielen Dank trotzdem, dass du ein solches Hilfeforum anbietest.
Liebe Grüße und ein schönes Wochenende!
Hallo,
wie würde die Formel im obigen Beispiel aussehen, wenn ich noch eine 3. Tabelle hätte, die durchsucht werden müsste?
Danke im Voraus für die Hilfe.
LG
Hallo,
wenn der SVerweis mit seinem Suchwort in einer Tabelle nichts gefunden hat, dann soll er in einer anderen Tabelle nachschauen?
Folgender Vorschlag mit der Annahme, dass die weitere Tabelle Tabelle3 heisst:
=WENN(ISTFEHLER(SVERWEIS(A3;Tabelle2!$A$3:$B$11;2;0)); SVERWEIS(A3;Tabelle3!$A$3:$B$11;2;0);”Leider in beiden Tabellen nichts gefunden”)
Das bedeutet folgendes:
Wenn es eine Fehlermeldung mit dem SVerweis für Tabelle2 gibt, dann einen erneuten SVerweis mit Tabelle3 machen, sollte auch hier nichts gefunden werden, dann wird der Text “Leider in beiden Tabellen nichts gefunden” ausgegeben.
Hoffe das hilft weiter.
LG John
Hallo ich möchte gerne ein Anmeldeformular erstellen. Das heißt in der ersten Spalte soll man die Kursnummer auswählen können (sprich man klickt auf den Pfeil, sieht alle Möglichkeiten und wählt das passende aus). Das ist einfach. Aber durch das auswählen dieser Nummer sollen sich die nächsten 2 Spalten (Name und Preis) automatisch ausfüllen. Ich bekomm es nicht hin und da ich absoluter Neuling bei Excel bin bräuchte ich jmd der mir das für Doofe erklärt 😉
Hi,
beschreibe doch bitte kurz, mit welchem Mittel du die Auswahl triffst? Combo Boy, Spin Button ……?
LG John
Mit SVERWEIS oder INDEX und VERGLEICH habe ich gearbeitet, klappt auch gut.
Ich suche eine Möglichkeit aus einer Tabelle mit einem Schlüssel mehrere Zeilen der Tabelle aufzurufen und auszugeben. Beispiel: Mitgliederliste von einem Verein.
Mit Eingabe des Familiennamens sollen alle Familienmitglieder, die jeweils einzeilig in der Tabelle gespeichert sind, in einem anderen Tabellenblatt ausgegeben werde.
Kennen Sie mit Excel eine Möglichkeit ?
Hallo,
eines der Grundprinzipien von SVerweis und INDEX VERGLEICH sind ja, dass diese Funktionen beim Finden des Suchkriteriums sofort aussteigen und keine weiteren oder andere Treffer vorweisen können.
Wie ich Sie verstehe, haben Sie in der einen Tabelle eine Auflistung von “Name”, “Vorname” usw.
In einer anderen Tabelle möchten Sie alle Vorkommen eines Nachnamens aufgelistet bekommen, sobald man einen Nachnamen eingibt. Aus meiner Sicht einfacher über eine VBA Programmierung zu lösen. Dann stellt sich mir die Frage, warum sie nicht die Filterfunktionen in der ersten Tabelle nutzen, um sich z.B. alle “Lehmanns” anzuschauen.
LG John
Hallo,
ich habe folgendes Problem. Ich habe eine Excel Liste mit Vertragspartnern einer bestimmten Kategorie. Darin stehen deren Vertragsnummern, SAP-Nummern, Namen usw. Nun habe ich eine andere Liste mit allen Vertragspartnern. Auch hier habe ich die Vertragsnummern, SAP-Nummern, Namen, Adressen.
Nun kann es sein, dass es einige Vertragspartner aus Liste 2 gibt, die zu welchen aus Liste 1 dazu gehören (verschiedene Filialen). Ich möchte dass in Liste 1 per Sverweis die Vertragspartner angezeigt werden die aus Liste 2 dazugehörig sein könnten. Jedoch möchte ich keine Doppelung (Die Vertragspartner aus Liste 1 könnten auch in Liste 2 stehen).
Die Vertragsnummern sind jeweils gleich, wenn die Filialen zusammen gehören. Das heißt, dass die zugehörigen aus Liste 2 die gleiche Vertragsnummer haben wie die aus Liste 1.
Hallo Thomas,
ein bisschen verwirrend.
In Liste 1 hast du Vertragspartner einer Kategorie
In Liste 2 hast du ALLE Vertragspartner. Das würde für mich bedeuten, dass dort auch die Vertragspartner (VP) aus Liste 1 enthalten sind.
Jetzt möchtest du in Liste 1 per SVerweis nur die aus Liste 2 angezeigt bekommen, die “dazugehörig sein könnten….ohne Doppelung”. Was bedeutet das?
Wenn du aus einer zweiten Liste nur die haben willst, die NICHT gleich derer aus Liste 2 sind, kommt hier der SVerweis in dieser Richtung nicht zum Tragen fürchte ich.
Was du machen kannst, um sicherzustellen, dass du eineindeutige Treffer hast ist, Schlüssel zu bilden. Ein Schlüssel (neue Spalte aus Vertragspartner&Nummer&whatever).
Diesen Schlüssel müsstest du auch in Liste 2 anlegen. Dann aber in Liste 2 den SVerweis über diesen Schlüssel machen und die, die NICHT übereinstimmen, erhalten die Fehlermeldung “#NV”.
Oder du schickst uns die beiden Listen und beschreibst noch mal anhand eines Beispiels, welche Treffer du haben willst.
LG John
Hallo John Doe,
ich habe ein, wohl kleines, Problem.
Ich habe eine Datenbank bestehend aus zwei Tabellenblätter.
In dem zweiten Blatt sind alle Artikel gelistet.
In das erste Blatt sollen nun die Artikel die den Lagerort “A” aufweisen.
Aufbau des zweiten Tabellenblatt:
Spalte 1: Lagerort
Spalte 2: Artikel Name oder Nummer
Jetzt soll Excel über Tabellenblatt eins folgendes durchführen:
Suche in Tabelle zwei den Lagerort A und füge die Artikel Nummer ein.
Über SVERWEIS kennen wir alle das Problem. Excel findet A fügt auch die Artikel Nummer ein und beendet die Suche.
Ich habe aber am Lagerort A mehrere Artikel liegen.
Wie muss ich vorgehen damit Excel alle Artikel mit Lagerort A in das erste Tabellenblatt untereinander einfügt und nicht nach dem ersten gefundenen stoppt.
Ich habe alles versucht was möglich ist, Internet durchstöbert. Nix gefunden.
Weißt Du einen Rat.
Gruß
HF
Hallo Harry,
du hast es ja schon erwähnt, dass der SVerweis beim ersten Vorkommen eines Treffers “aussteigt”. Für dein spezielles Problem hätte ich eine Lösung unter der Voraussetzung, dass die Tabelle 2 mit den Lagerorten, nach Lagerorten sortiert sein muss. Dann machst du dir einfach eine Eigenart von Excel zu nutze, die man eigentlich “abschaltet”, nämlich das NICHT-Setzen der “$”, um den Bereich zu fixieren. Setzt du diese NICHT, dann rutscht der SVerweis beim Runterziehen der Formeln immer um eine Zeile nach unten (startet also in der nächsten Zeile). Daher unbedingt nach Lagerorten sortieren.
Bitte antworte, damit ich erfahren kann, ob dein Problem gelöst ist.
LG John
Hallo John,
vielen Dank. Ist zwar etwas umständlich, vorher Ausdruck sortieren, aber so geht es. Vielleicht kann ich es austricksen mit anderen Funktionen. Mal schauen. Vorerst muss es so gehen, die Inventur steht an. 🙁
Gruß
Harry
Hallo Harry,
na da freue ich mich ja, dass du hier zumindest das gefunden hast, was dir erst einmal weiter hilft.
LG John
Hallo,
ich bekomme einfach die Formel nicht hin! Ich habe 2 Tabellen mit Datensätzen. Die DS aus der zweiten müssen aus der ersten raus, das heißt, ich muss sie irgendwie markieren nach übereinstimmenden E-Mail-Adressen. Der Aufbau der Dateien ist der gleiche. Kann mir jemand helfen?
Lieben Dank!
Hallo Tanja,
vielen Dank für deine Frage.
Ich muss aber nachfragen:
“Die DS aus der zweiten müssen aus der ersten raus” !!!!
Meinst du damit, dass du in der zweiten Tabelle den SVerweis hast und über die E-Mail-Adressen suchst?
Wenn ja, was muss markiert werden, die gefundenen? Eventuell über “bedingte Formatierung”?
Schreibe mal bitte etwas genauer, dann können wir bestimmt helfen.
LG John
Hallo,
ja, ich suche über die Mailadressen und muss doppelte oder fehlende angezeigt bekommen, das ist eigentlich egal.
Danke, Tanja
Hallo Tanja,
wenn du den SVerweis so einträgst, dass du mit der Mailadresse suchst und dir diese auch ausgeben lässt, dann hast du doch deine Übereinstimmung gefunden. Das heisst z.B.:
=SVERWEIS(A2;Tabelle1!A:A;1;0)
Der SVerweis kommt in Tabelle 2 rein und deine Mailadressen stehen z.B. in Spalte “A”, deswegen auch A2
In Tabelle 1 stehen deine Mailadressen auch in Spalte “A” und du fragst eben nur diese Spalte ab, deswegen “A:A” und die 1
Wenn eine Übereinstimmung gefunden wird, wird die entsprechende Mailadresse angezeigt, wenn nicht #NV.
Oder habe ich dich noch immer nicht verstanden? Dann bitte ein kleines Beispiel deinerseits.
LG John
Hallo John Doe,
Ich hab heute mal mit dem Sverweis gearbeitet und das klappt auch ganz gut,
nur habe ich jetzt festgestellt das wenn ich in tab3(artikelstamm) eine zeile einfüge die dann nicht in tab4(einkaufspreis) übernommen wird und auch nicht in tab5(UVP) das wäre aber dringend erforderlich.
Tab1 und 2 greifen über sverweis in kombi mit =wenn() auf die anderen Tab´s zu
zb. =WENN(C32=0;””;SVERWEIS(C32;Artikelstamm!$A$2:$D$268;2;FALSCH))
bzw. =WENN(C33=0;””;SVERWEIS(C33;Einkaufspreis!$A$2:$B$265;2;FALSCH))
bzw. =WENN(C34=0;””;SVERWEIS(C34;UVP_Hersteller!$A$2:$B$265;2;FALSCH))
tab 4 und 5 holen sich aber daten aus Tab 3
und somit hab ich da das problem
Wäre super wenn du da eine Lösung für mich hättest
Grus Ralf
Hallo Ralf,
vielen Dank für Deine Frage.
Im Moment “fragst” du in tab3(artikelstamm) 268 Zeilen, in tab4(einkaufspreis) und tab5(UVP) 265 Zeilen ab.
Sobald eine dieser Tabellen einen neuen Eintrag erhält (eine neue Zeile hinzukommt), wird die von den jeweiligen SVerweis Formeln nicht mehr “gesehen”. Das liegt an der “Bereichsbeschränkung” $A$2:$D$268 oder $A$2:$D$265
Damit teilst du dem SVerweis ja mit, in welchem Bereich er suchen soll.
Hier ist es besser, die ganzen Spalten anzugeben. Die SVerweisformel sollte dann folgendermaßen aussehen:
Du kannst das erreichen, indem du beim Erstellen der SVerweisformel, nicht nur den reinen “Zell-Bereich” mit der Maus markierst, sondern nur die Spalten (in deinem Fall markierst du die Spalten “A-D” aus tab3 und “A-B” aus tab4 und tab5)
LG John
Hallo John,
Also das hab ich jetzt mal gemacht klapt auch, eigentlich
Aber wenn ich in tab 3(artikelstamm) eine zeile einfüge, dann fügt excel nicht automatisch in tab4 und tab 5 auch eine zeile an der gleichen stelle ein
heist wenn ich im tab 3 zb zwischen zeile 90 und 91 eine einfüge und dann auf der neuen 91 etwas eintrage dann gibts die neue 91 nicht in tab 4 und tab5??? ich müsste also immer die anderen tabs 4/5 händisch mit Ändern?? gibt es da keine lösung?
Danke nochmal für deine schnelle Antwort
Grus Ralf
Hallo Ralf,
dann hatte ich dich falsch oder nur teilweise richtig verstanden.
Damit du in tab4 und tab5 die gleichen Einträge hast, wie in tab3, müsst du die einzelnen Zellen in tab4 und tab5 mit tab3 verknüpfen.
Dazu gehst du in tab4, z.B. in Zelle A265 und gibst ein “=”, dann klickst du in tab3 in die gewünschte Zelle A265 und klickst ENTER.
Damit erstellst du eine Verknüpfung von der Zelle A265 in tab4 zur Zelle A265 in tab3.
Sieht dann in tab4 in Zelle A265 so aus: =artikelstamm!A265
Dann ziehst du die Formel nach rechts in alle Spalten, die du möchtest (oder copy & paste).
Am Besten natürlich in A2 anfangen, dann runter ziehen, bis z.B. 500 oder wie viel Einträge du erwartest.
Mir ist jetzt aber nicht ganz klar, wofür du diese 1:1 Verknüpfung benötigst.
LG John
Hallo nochmal,
Diese Verknüpfung brauche ich deshalb weil im artikelstamm ja die artikelnummern und die artikelbezeichnung sowie auch die herstellerangaben und meine einkaufspreise bzw. die uvp des herstellers stehn und ich die in der tab 1(rechnung) ausgeben will
jetzt hab ich also eine tab erstellt in der die einkaufspreise und die artikelnummer stehn und eine wo uvp und artikelnummer stehn
immer in A2 die nummer und B2 Der Preis…
Bzw. die Artikelbeschreibung, oder der Hersteller?
Wascheinlich hab ich das alles zu kompliziert gemacht aber ich weis mir anders nicht zu helfen…
Gruß Ralf
Hallo Ralf,
grundsätzlich ist alles OK, sofern du dir zu helfen weisst und korrekte Angaben ermittelt werden. Das “Verbessern” oder Optimieren von Tabellen und Formeln kommt immer mit der Zeit und mit Geduld.
Bei dir stellt sich mir nur die Frage, warum du nicht in einer tab ALLE Angaben hast (Artikelnummer, -Bezeichnung, EK, UVP usw. und in der tab1 (rechnung höchstwahrscheinlich), alles über den SVerweis “rüberziehst”.
LG John
Hallo nochmal,
Dann hätte ich noch ein zweites problem und zwar bei der formel
=WENN(C32=0;””;SVERWEIS(C32;Artikelstamm!D:E;2;0))
wenn in der matrix kein wert steht schreibt mit excel eine 0 in das ausgabefeld, es soll aber dann nichts also ein lehres feld widergegebn werden, habe es mit istfehler probiert aber immer mit dem ergebnis das es ein fehler war…
Danke nochmal
Gruß Ralf
Hallo Ralf,
der SVerweis gibt immer dann eine Null zurück, wenn er das gesuchte gefunden aber in der auszulesenden Zelle nichts oder selber eine Null steht.
Um die Ausgabe der Null auf “Nichts” zu setzen, müsste der SVerweis folgendermaßen aussehen:
=WENN(C32=0;””;wenn(SVERWEIS(C32;Artikelstamm!D:E;2;0)=0;””;SVERWEIS(C32;Artikelstamm!D:E;2;0)))
Wenn der SVerweis den Wert Null zurückgibt, dann “”, ansonsten SVerweis.
Hoffe geholfen zu haben.
LG John
Hallo nochmal,
Habe das getestet und dann gibt mir excel als wert #nahme? an???
Gibt es keine möglichkeit das mit einem istfehler zu beheben?
Danke derweilen
Grus Ralf
Hallo Ralf,
wenn du die Formel hier aus dem Kommentar herauskopiert und in Excel eingefügt hattest, dann kann es sein, dass die beiden “” (Anführungsstriche) Excel stören.
Entferne die Anführungsstriche aus Excel und gib sie in der Formel erneut ein. Dann sollte funktionieren.
LG John
Hallo nochmal,
Also hab das jetzt auch getestet und jetzt kommt ein#NV zurück…
danke dir trotzdem für die mühe.
LG Ralf
Hallo Ralf,
kannst du bitte Deine Formel, die #NV produziert hier in den Kommentar kopieren?
Danke
LG John
Hallo John,
entschuldige das ich mich so lang nicht gerührt habe…
ich hatte die Formel wie angegeben eingetragen und die “” getascht und da war eben dieses #NV aber jetzt hab ichs nochmal getestet und es klappt, warum auch immer.
Ich danke dir für deine Hilfe und fals wir uns nicht mehr höhren wünsche ich ein schönes weihnachtsfest und einen guten rutsch nach 2015
LG Ralf
Hallo Ralf,
ganz lieb von Dir, dass dich nochmal gemeldet hast und ich freue mich, dass es doch noch geklappt hat.
Auch für Euch ein geruhsames Weihnachtsfest.
LG John
Hallo zusammen,
ich hätte eine Frage, bei der ich Hilfe benötige. Ich habe 2 Tabellenblätter, wobei im 1. Blatt die “Stammdaten” notiert sind und im 2. Blatt diverse Buchungen verschiedener Kunden. In Blatt 1 stehen sozusagen die Sollzahlungen. Die Istzahlungen möchte ich mir mittels der Formel aus dem 2. Blatt holen. Ich stelle mir das so vor. Bei dem Kunde Becker steht in Zelle P3 (Istzahlung) und P4 (Sollzahlung). Die Formel soll also in die Zelle P4. Und nun soll die Formel im 2. Blatt nach dem Name Becker suchen und mir dann den Wert aus der 4. Zelle nebenan (Zahlung) ins Blatt 1 Zelle P4 einfügen. Und dann entsprechend für die folgenden Kunden.
Vielen Dank für eure Hilfe!
Hallo Thomas,
Danke für deine Frage, das musste ich ein paar Mal lesen 🙂
Ich habe das so verstanden:
Blatt 1 = Stammdaten und Sollzahlungen
Blatte 2 = Kundenname und Istzahlungen
Du möchtest ins Blatt 1 die Istzahlungen aus Blatt 2 für den jeweiligen Kunden holen.
Dann setzt du den SVerweis im Blatt 1 in die Spalte “P”. Das Suchkriterium ist der Kundenname (der identisch mit den Eintragungen im Blatt 2 sein muss), also Kunde Becker muss im Blatt zwei auch Becker heißen und nicht Becker AG oder so.
Das sieht dann in etwa so aus: =SVERWEIS(A2;Tabelle2!P:P;16;0) / A2, wenn der Kundename in Spalte A steht / die 16 am Ende der Formel (;16;0) ist die Spalte “P” aus Blatt2, wenn dort auch der Kundennamen in Spalte A steht und du einen Wert aus Spalte “P” holen möchtest.
Den Rest deiner Beschreibung habe ich nicht nachvollziehen können.
Nachdem du die Istzahlen aus Blatt2 des Kunden ins Blatt1 geholt hast, willst du was noch machen?
LG John
Hallo,
ich hätte da auch eine Frage, stehe gerade auf dem Schlauch…
habe eine Spalte, in der verschieden kategorien von Produkten stehen und eine weitere Spalte in der ebenfalls die Kategorien, jedoch mit einer führenden Ziffer stehen (also z.B. Spalte 1 “Sports” Spalte 2 wäre dann “1 Sports”) jetzt soll per SVERWEIS der Wert aus Spalte 1 in der zweiten Spalte gesucht und in einer dritten Spalte ausgegeben werden. Ohne führende Ziffer kein Ding, mit hakts irgendwie….
Hi,
das schaft der SVerweis nicht. Andersherum hätte man es machen können, also mit Spalte 2 in Spalte 1 suchen und finden.
Möglichkeit: Wenn du die Zahlen in Spalte 2 durch “benutzerdefinierte Formatierungen” erzeugen könntest, dann wäre es möglich.
Beispiel: Zelle formatieren –> Benutzerdefiniert –> “1” @
Dann würde aus einem Eintrag “Sport” ein “1 Sport” werden. In der Zelle selber stünde dann nur das Wort “Sport” und kann dann per SVerweis gefunden werden.
LG John
Hallo.. ich weiß nicht mehr weiter… ich kann mit der Formel: =INDEX(C:C;ANZAHL2(C:C)+1) — die letzte gefüllte zelle in einer spalte anzeigen lassen…aber nur auf dem selben Arbeitsblatt.. ich möchte sie aber auch einem anderen Arbeitsblatt haben? kopieren oder so bringt mir nix, denn die anzahl der gefüllten zellen ändert sich immer…
bitte um hilfe.. danke im voraus
Hallo André,
vorab eine Frage: Bist du sicher mit deiner Aussage, dass du mit dieser Formel die letzte Zelle in der Spalte ermittelst?
Schau dir mal bitte den Screenshot an:
Du hast ANZAHL2 noch um +1 ergänzt, um eine eventuelle Überschrift zu berücksichtigen. Index gibt natürlich das aus, was über ANZAHL2 ermittelt wird, aber schaut “ab Anfang der Spalte” nach unten.
In meinem Beispiel ergibt Anzahl2 +1 = 92. Index gibt dann den Wert in Spalte “C” aus der Zeile 92 aus (weil von oben an gerechnet) und das ist in meinem Beispiel “87”. Ich habe aber Einträge bis “90” in der Spalte.
Vorschlag: =INDEX(C6:C500;ANZAHL2(C:C)-1) –> oder =INDEX(C6:C500;ANZAHL2(C6:C500))
Vorschlag 1: Hier wird ab C6 (Beginn deiner Daten) nach unten geschaut. Du brauchst dann noch eine “-1”, weil Anzahl2 die komplette Spalte (C:C) betrachtet und die Überschrift rausgenommen werden muss.
Vorschlag 2: Beide Bereiche mit C6:C500 (500 = fiktiver Wert, der entsprechend angepasst werden muss) angeben, dann brauchst du die “-1” nicht mehr.
*****************
Zu deiner ursprünglichen Frage, ob die Tabellen-Blatt-übergreifend die Formel verwenden kannst:
=INDEX(Tabelle1!C6:C500;ANZAHL2(Tabelle1!C6:C500))
Vor jedem Bereich, schreibst du den Tabellennamen mit einem abschliessenden Ausrufezeichen. In meinem Beispiel stehen die Werte in “Tabelle1” und die Formel in “Tabelle2”.
LG John
Hallo John,
Ich schaffe es einfach nicht… 🙁
Ich habe 2 Tabellen mit Preislisten.
Tabelle 1: mit 44669 Zeilen, in Spalte A steht die Artikelnummer, Spalte F=Einkaufspreis, Spalte L=Verkaufspreis.
Tabelle 2 mit 377 Zeilen nur eine kleine Auswahl der Artikel von Tab.1 (nicht in richtiger Reihenfolge), Spalte A Artikelnummer (identisch mit Tab. 1) Spalte E=Einkaufspreis, Spalte F=Verkaufspreis.
Ich möchte, dass die aktuellen Preise aus der langen Liste (Tab.1) in die entsprechenden Felder der Tab.2 eingefügt werden.
Folgende Formel habe ich nach der Anleitung oben erstellt und bekomme immer entweder #NV oder#BEZUG! …
=SVERWEIS(A3;Tabelle1!B4:B44669;6;0) alternativ:
=SVERWEIS(A3;Tabelle1!$B4:$B44669;6;0)
Wo liegt mein Fehler???
Danke.
Hallo Conny,
auf dem ersten Blick teste doch bitte, ob du in Tab.1 mit der richtigen Zeile startest. Suchkriterium aus A3 suchst du ab Zeile 4 (in Tab.1). Wenn du die Formel dann runterkopierst, dann setzt sich dieser Fehler fort.
Probiere bitte folgende Formel:=SVERWEIS(A3;Tabelle1!$B3:$B44669;6;0)
Das ist die Formel für die Einkaufspreise. Lass mich bitte wissen ob es das war oder du immer noch Probleme hast.
LG John
Hallo John,
ich bekomme noch immer #BEZUG! als Ergebnis angezeigt. Da muss noch was anderes falsch sein 🙁
Bisher hatte ich in beiden Tabellen unterschiedlichen Anzahl von Überschriftenzeilen. Daher hatte ich A3 … und $B4…
Ich habe jetzt beide Tabellen so geändert, dass beide Datensätze in Zeile 2 anfangen und beide Artikelnummern in Spalte A und beide Preisspalten in Spalte E (=5) sind.
Habe folgende Formel drin: =SVERWEIS(A2;Tabelle1!$A2:$A44668;5;0)
und bekomme #BEZUG als Ergebnis. Habe schon ganz viel ergebnislos rumprobiert…
Danke für deine erste schnelle Hilfe
VG Conny
Hallo Conny,
schick mir doch bitte die Datei mal an john.doe.pcn@gmail.com
LG
Hallo Conny,
sorry das hätte ich auch gleich sehen müssen.
Anstatt =SVERWEIS(A2;Tabelle1!$A2:$A44668;5;0) musst du schreiben: =SVERWEIS(A2;Tabelle1!$A2:$E44668;5;0)
Du musst doch von A:E schauen und dann Spalte 5 abgreifen..
LG John
Super. Heute früh die Formel eingegeben. Funktioniert 🙂 🙂 🙂
Vielen vielen Dank für deine Mühe und deine Hilfe.
Großes Lob an dich, dass du auf diese Weise deine Hilfe anbietest und vor allen Dingen so schnell und hilfreich antwortest.
Schön, dass es noch Menschen gibt, die uneigennützig helfen. Das ist in der heutigen Zeit nicht selbstverständlich.
Danke. Danke. Danke.
Viele Grüße
Conny
Hallo Conny,
Danke für die warmen Worte……… und schön das ich helfen konnte.
Der Start in die Woche geht ja super los :-)))))
LG John
Hi John,
ich hätte auch eine Frage, bin mir aber nicht sicher ob ich mit Sverweis hier weiterkomme. Spalte A Name, Spalte B Produktkategorie, Spalte C Kundennr, Spalte D Kostenerstattung, Ich würde jetzt gerne zur Vorbereitung eines Serienbriefes ab Spalte E die jeweilige Kostenerstattung pro Produktkategorie je Kunde einfügen, was aber dynamisch sein kann, weil ein Kunde durchaus auch Erstattungen aus verschiedenen Kategorien bekommen kann, wenn er Produkte aus mehreren Kategorien gekauft hat. D.h. der Kunde kann in Zeile 2 und Zeile 50 stehen, aber ich möchte seine Erstattungswerte nur in Zeile 2 angezeigt bekommen. Name und Kundennr hab ich über verketten schon als eindeutig identifiziert, aber jetzt steh ich etwas auf dem Schlauch wie weiter. Eine Idee?
Danke Rainer
Hallo Rainer,
soviel wie ich verstanden habe, möchtest du die Kostenerstattungen pro Kunde in jeder Produktkategorie ermitteln. Das Wort Addition schließt den SVerweis aus, da dieser bei jedem “Fund” aussteigen würde. Es gibt aber eine Menge anderer Funktionen, die du nutzen kannst. Z.B. SUMMEWENNS (achte auf das “S” am Ende).
Das ist jetzt nur mal ein Schnellschuss und sollte dir als Anregung dienen.
Damit werden zwar ALLE Namen, die mehrfach vorkommen auch mehrfach aufgeführt aber es wird jeder Name richtig zusammengerechnet, pro Produktkategorie.
LG John
Vielen herzlichen Dank. Damit konnte ich ein Problem lösen, was mich sonst Stunden gekostet hätte (automatische Beschriftung von ca. 10’000 Zeilen mit den Angaben aus einer anderen Datei).
Hallo Lukas,
lieben Dank für deinen Kommentar. Da freuen wir uns, dass wir helfen konnten.
LG John
hab in meiner Ergebinstabelle ein copy/paste Problem. Ich will in der Ergebnistabelle meines SVerweises folgende Struktur:
quelltable A1 kommt in zieltable A1
quelltable A2 kommt in zieltable A3
quelltable A3 kommt in zieltable A5
(sprich in der zieltable entsteht je eine leerzeile)
wenn ich nun den SVerweis nach unten kopiere oder ziehe dann erhalte ich
quelltable A1 kommt in zieltable A1
leerzeile
quelltable A3 kommt in zieltable A3
leerzeile
quelltable A5 kommt in zieltable A5
also fehlt jeder 2te Datensatz….
wie kann ich das umgehen?
besten Dank im voraus
Hallo Benjamin,
vielen Dank für deine Frage.
Dem SVerweis ist es eigentlich egal, ob es eine Leerzeile gibt oder nicht.
Ich frage mich bei deinem Beispiel, wenn du den SVerweis nutzt und beide male über Spalte A redest, wo dann dann Suchkriterium steht.
Der SVerweis in Zeile 3 (zieltable) müsste ja das Suchkriterium aus Zeile 2 der quelltable haben (es ist ja der zweite “Datensatz”, wenn es eine Zeile 1 gibt und eine Leerzeile).
Beschreibe doch mal ganz genau oder schick mir doch mal deine Datei, dann schaue ich mir das mal an.
LG John
HI John,
… es ist ein copy/paste Problem … der vlookup funzt…
werd mich da mal schlau machen.
trotzdem danke.
Benni
Hi Benjamin,
Danke für deine Rückmeldung. Probier mal drei Zellen untereinander (die Erste mit Werten, die Zweite ist die Leerzelle und die Dritte wieder mit Werten) zu markieren und dann mit ziehen (kleines schwarzes Kreuz, wenn du in die unterste der drei Zellen ganz rechts in die Ecke mit der Maus gehst) zu kopieren.
Wenn noch Probs bestehen, einfach hier nachfragen…
LG John
Hallo John,
hab mich hier mal durchgelesen und finde es super wie du allen hilfst und bin auch schon ne ganze Ecke weiter gekommen, hänge aber zur Zeit wieder fest…
Mein Problem:
Mein SUCHKRITERIUM befindet sich in einem Zellenbereich der sich über eine Makro sortieren lässt, (was unbedingt so muss) und auch das zu übertragende ERGEBNIS mit sich zieht, das ERGEBNIS hat also keine feste Zelle und ich kann als SPALTENINDEX kein Zellenbereich angeben, oder?
A B C D
1 abc/…/…/14
2 def/…/…/11
3 ghi/…/…/21
sortieren:
A B C D
1 ghi/…/…/21
2 abc/…/…/14
3 def/…/…/11
nun zur Frage: kann ich als ERGEBNIS eine Zelle beschreiben, die x zellen rechts vom KRITERIUM steht?
sagen wir ich suche auf Tabelle 1 “ghi” und möchte “21” auf Tabelle 2 auf einem anderen Blatt übertragen:
=SVERWEIS(“ghi”;Tabelle1!A1:D3;?????????????;0)
hoffe du verstehst was ich meine… ist sowas möglich? oder gibt es dafür eine andere Lösung?
wenn die sortierung nicht wäre, kein Problem, aber so komm ich nicht damit zurecht. Freue mich über jede Hilfe
mfg
Hallo Dennis,
schön das dir unsere Seite gefällt und Danke für die Worte.
Ich erkenne leider dein Problem nicht. Die Fragezeichen sind in dem Bereich, indem man angibt aus der wie vielten Spalte das Ergebnis zurückgegeben werden soll.
Entweder du schickst mir mal deine Tabelle und zeigst mir dein Problem darin oder du beschreibst es vielleicht noch einmal, nachdem du dir das hier durchgelesen hast.
Der SVerweis nimmt sich das Suchkriterium;geht in einen Bereich;und gibt den Wert aus der angegebenen Spalte aus;wenn das Suchkriterium exakt gefunden wird.
—-SVerweis(Suchkriterium—————;Bereich————–;Anzahl der Spalten nach rechts——————;exakte Übereinstimmung)
Dabei ist es vollkommen egal, welcher Bereich (dort wo das Suchkriterium oder das Ergebnis liegt) sortiert wird.
LG John
Hallo John Doe,
die Themen sind schon ein Weilchen her …:-)
Bin hierauf gestoßen, weil ich ähnlich Probleme habe.
Ich habe eine recht umfangreiche Preisliste erstellt (Position, Artikelname, EPreis, Stück, GPreis). Ich gebe die Stückzahl ein und GPreis wird ausgegeben.
Nun soll die gesamte Auswahl, quasi als Angebot, in einem anderen Tabellenblatt aufgelistet werden (Position, Stck, Artikelname, GPreis). Hier arbeite ich mit SVERWEIS. Das klappt auch soweit. Problem ist, dass die gewünschten Werte nicht untereinander geschrieben werden, sonder 1:1 in den Zeilen bleiben und somit Leerzeilen entstehen. Wie bekomme ich es hin, dass die Datenauswahl ohne Leerzeilen übernommen werden, also sauber untereinander gelistet werden???
Vielen lieben Dank bereits vorab.
LG Torsten
Hallo Torsten, Danke für deine Frage. Ich verstehe nur noch nicht ganz das Problem. Das Suchkriterium ist wahrscheinlich der Artikelname und wenn du den pro Zeile eingibst, dann hast du doch alles untereinander.
Kannst du bitte genauer erklären, was du machst? LG John
Hallo John Doe, danke für deine Antwort. Ich habe einen anderen Weg gefunden, dass Problemchen zu lösen – war ein wenig aufwendiger aber egal. Kam zum Ziel 🙂
LG Torsten
Hallo Torsten,
urlaubsbedingt die Antwort etwas später. Hauptsache es funktioniert. Lieben dank für dein Feedback. LG John
Hallo John Doe,
auch ich habe ein Problem, ich weiß nicht mal mit welcher Funktion ich mein Problem lösen kann.
ich habe im 1. Tabellenblatt einen Stundenplan mit Lernfeldnummern z.B. 1.1.2. pro Unterrichtseinheit d.h. pro Woche 7 UE senkrecht x 5 Tage waagerecht = 35 UE und im 2. Tabellenblatt eine Stundenbilanz mit allen Lernfeldnummern senkrecht aufgelistet, dahinter 12 Spalten mit jeweils 1 Stundenblock à 4 Wochen.
Die Unterrichtseinheit 1.1.2. auf dem Stundenplan Tabellenblatt 1. soll auf dem 2. Tabellenblatt in der Spalte Block 1 mit 1 eingetragen werden, so dass in diesem Tabellenblatt jeweils die Gesamtstunden ermittelt bzw. addiert werden können sowohl senkrecht pro Block wie auch waagerecht pro Lernfeldeinheit.
Also ich möchte, dass jede Unterrichtseinheit automatisch in der richtigen Zeile der Stundenbilanz mit 1 eingetragen wird, sobald der Stundenplan erstellt wird.
Ich hoffe, dass meine Beschreibung verständlich ist.
Vielen Dank im Voraus
Heike
Hallo Heike, kannst du mir die Datei mal per Mail zu kommen lassen? Bitte an
sverweis@pcnotfallhilfe.de
Ich antworte dir dann wieder hier….LG John
Hallo John Doe,
du hast den sverweis sehr schön erläutert! Allerdings komm ich bei meinem Problem nicht weiter.. ich habe zwei Tabellen. Die eine Tabelle ist sozusagen die Datenbank. Bei der anderen Tabelle will ich nur noch den Namen in die erste Spalte eingeben müssen, sodass daraufhin die restlichen Daten in der Zeile erscheinen. Also nicht nur ein Wert, sondern mehrere Werte.
Ich bin leider maßlos überfragt und habe keine Ahnung wie das gehen soll?!
Freu mich über jede Hilfe :))
LG Sunny
Hallo Sunny….dann starte doch als erstes mit einem Namen in Spalte A. Sobald der eingetragen ist, bindest du diesen Sverweis in eine Wenn Formel ein. =Wenn (A1=””;””;Sverweis………)
Dann alle weiteren Spalten nach diesem Schema ausfüllen. Es gibt übrigens einen guten Trick womit man mit nur einer Sverweis Formel alle Spalten gleichermaßen füllen kann. Wenn du daran interessiert bist antworte bitte. Auch wenn du noch Fragen haben solltest. LG John
Hallo,
ich hätte da auch ein Problem. Ich soll aus Tabelle 1 die mit ca. 500 Unterschiedlichen Projekten und Daten besteht nach 2 Kriterien das Datum
in Tabelle 2 mir anzeigen lassen! Suchkriterien sind Vorgangsnummer und Projektbezeichnung, wobei, bei der Vorgangsnummer ca 4 verschiedene je Projekt gibt und zu jeder Vorgangsnummer ein eigenes Datum.
Hallo Adam…kannst du das ein bisschen anschaulich darstellen in welcher Spalte was steht? LG John
Hallo John,
Spalte A Vorgangsnummer, Spalte B Projekt, Spalte D Früh.Startdat.
Nach demn inhalt aus Spalte A&B soll er mir in Tabelle 2 das Datum aus Spalte d anzeigen!
LG Adam
Hallo Adam, sorry hatte viel zu tun am WOE. Ich schaue mir das jetzt mal an und melde mich. LG John.
Hallo Adam, das Suchkriterium im SVerweis kann aus mehreren Spalten zusammengesetzt werden, Voraussetzung, das in der zu suchenden Tabelle, diese Kombination als “Key” zu finden ist.
Beispiel: VN = Vorgangsnummer PR = Projekt usw.
Tabelle 1 besteht aus VN&PR | VN | PR | DATUM
Tabelle 2 besteht aus VN | PR | DATUM (aus SVERWEIS)
In der Spalte “C” der Tabelle 2 kommt der SVERWEIS rein und sieht so aus:
Tabelle 1
Tabelle 2
Die Formel in C2 wäre= =SVERWEIS(A2&B2;Tabelle1!$A$2:$D$6;4;0)
Ich hoffe du verstehst meine Ausführungen. Sollte irgendetwas nicht klar verständlich sein, schreibe einfach einen weiteren Kommentar. LG John
Hallo John,
das habe ich verstanden. Jedoch befindet bei mir in der Tabelle 1 ca 120 Projekte mit je Projekt 4 unterschiedlichen Vorgangsnummern. Und mit der Formel aus Tabelle 2 soll er sich in Tabelle 1 das Datum aus den ca. 500 Zeilen raussuchen welche zum jeweiligen Projekt & Vorgangsnummer passt, denn die ganaue Zeile habe ich nicht ohne vorher selber zu schauen!
Besten Gruß
Adam
Hallo Adam, aber genau das macht doch der SVERWEIS. Mit der Kombination VN&PR geht er in die Tabelle 1, sucht in Spalte A nach dieser Kombination und wenn er fündig wird, gibt er aus Spalte 4 das Datum aus dieser Zeile aus.
Was passiert denn bei dir, wenn du die Formel so eingibst? LG John
Hallo John,
bei mir steht das Datum in der Spalte D. Ich habe eingegeben
=SVERWEIS(A4&B4;Tabelle2!A3:B10:$D4;6;0) dann steht bei mir der Fehler #WERT.
LG Adam
Hallo Adam,
einige Änderungen an deiner Formel müsstest du vornehmen. Aus deiner Formel entnehme ich, das du VN und PR in Spalten A und B hast.
OK bis hierhin =SVERWEIS(A4&B4;
Dann kommt als nächstes die Tabelle und der Bereich der Tabelle, in der du suchen willst:
=SVERWEIS(A4&B4;Tabelle1!A3:B10; ==> Tabelle1 anstatt Tabelle2 und der Bereich geht von A3 bis B10)
Dann kommt die Spalte, aus der du das Datum nehmen willst. Steht das Datum in Spalte F?
Dann ==> =SVERWEIS(A4&B4;Tabelle1!A3:B10;6;0)
Probiere mal aus LG John
Hallo John,
das Datum steht in Spalte D
LG Adam
Hi,
dann müsste dein Formel so aussehen:
=SVERWEIS(A4&B4;Tabelle1!A3:B10;4;0)
Du schaust dann aber nur in Tabelle1 bis Zeile 10. Du musst das hier auf die Anzahl der Zeilen deiner Projekte erweitern oder gleich über die ganze Spalte suchen.
Das würde dann so aussehen:
=SVERWEIS(A4&B4;Tabelle1!A:B;4;0)
LG John
Hi,
soweit alles richtig aber er zeigt mir dann #NV an!!!!
Hi,
kopiere doch bitte deinen SVerweis mal hier rein, der als Ergebnis #NV ausgibt. LG John.
=SVERWEIS(A4&B4;’Rohdaten ZP88′!A:B;4;0)
Hi Adam, sorry von meiner Seite, das muss so aussehen: =SVERWEIS(A4&B4;’Rohdaten ZP88′!A:D;4;0)
A:D (über 4 Spalten, damit er die letzte ausgeben kann)
Hi,
das mit dem Datum Markieren hat super geklappt. Aber zu dem ersten Problem bzw. deiner neuen formel kommt nach wie vor der Fehler #NV, trotz der A:D regel
Hi, kopiere doch bitte nochmal die Formel hier rein. Du nimmst die aus Zeile 4 ja? LG John
2500 RFUR0066 / TBUR130 / Dacia Rumänien
A4 B4
Hi, bist du dir sicher, das es diesen “Key” auch gibt? Der SVerweis gibt #NA oder #NV zurück, wenn es den Suchbegriff nicht findet. LG John
Hi,
die beiden Keys stehen in der Tabelle 2 oben mit den mir dort in der Tabelle bei den Belegen das Datum zu dem Projekt an zeigen soll.
LG Adam
Guten Morgen Adam,
Dein Suchkriterium (Key) setzt du doch zusammen aus dem Eintrag aus SpalteA und SpalteB der Tabelle2 und suchst in Tabelle1 oder?
Ist denn dieser Key in Tabelle1 vorhanden? LG John
Moin John,
ja der Key setzt sich so zusammen und ja der Key ist so auch in Tabelle 1 vorhanden. Ich gebe dir nochmal einen kleinen auszug
Vrg Projekt ArbPlatz Früh.Startdat.
2500 RFUR0065/130 TBUR/MC Syncro Enlog ESP SMM1 01.11.2017
2600 RFUR0065/130 TBUR/MC Syncro Enlog ESP SME 04.12.2017
2700 RFUR0065/130 TBUR/MC Syncro Enlog ESP SPR 22.01.2018
2800 RFUR0065/130 TBUR/MC Syncro Enlog ESP RAS 21.02.2018
2500 RFUR0066 / TBUR130 / Dacia Rumänien SMM1VP 19.02.2018
2600 RFUR0066 / TBUR130 / Dacia Rumänien SME 26.03.2018
2700 RFUR0066 / TBUR130 / Dacia Rumänien SPR 16.04.2018
2800 RFUR0066 / TBUR130 / Dacia Rumänien RAS 04.06.2018
A B C D
Nach den suchkriterien aus Tabelle 2, bestehend aus A 2500 & B RFUR0066 / TBUR130 / Dacia Rumänien, suche ich das dazugehörige Datum in Tabelle 1 Spalte D
LG Adam
Hallo Adam, kontrolliere bitte, ob es in der einen oder anderen Tabelle zusätzliche Leerzeichen gibt. Wenn nicht, kannst du mir die Tabelle schicken? Dann finde ich den Grund. LG John
PS: Bitte die Kommentare nicht doppelt schicken. Sie werden erst dann frei geschaltet, nachdem ich sie frei gebe aber ich kann nicht immer gleich sofort reagieren.
Hi John,
kein Problem. Wie kann ich dir die ganze Datei schicken?
LG Adam
Hi, Mailadresse aus dem Impressum. LG John
Hi John,
dann hätte ich da noch ein Problem!
Ich möchte mit Hilfe einer Formel in einer Tabelle ein bestimmtes Datum finden und die Zeile anschließend automatisch farblich kennzeichnen. Z.B ich suche 19.02.2018 und in der tabelle sind die Datums von z.b. 10.01.2018-30.03.2018!!!
Gibt es da was???
LG Adam
Hi, ja, da würde ich die “Bedingte Formatierung” nutzen. Die findest du im ersten Menüpunkt. Markiere dazu den kompletten Bereich, für den diese Formatierung gelten soll und gehe dann auf “Bedingte Formatierung” dann gleich der erste Unterpunkt und dann “zwischen”.
Als nächstes gib den Datumsbereich ein und klicke auf OK.
Ich habe hier leider nur die englische Excel Version.
LG John
Hallo John,
gibt es auch eine formel, das wenn ich die Tabelle 1 aktualisiert habe die Veränderung ob ein Projekt dazu gekommen ist, in Tabelle 2 anzeigt wird oder wenn sich eins erledigt hat das es dann nicht mehr auftaucht???
LG Adam
Hi, wenn du in der Tabelle2 die SVERWEIS Formel um einige Zeilen weiter runterziehst, dann würden dort die neuen Einträge aus Tabelle1 erscheinen. Du müsstest am Anfang der Formel nur reinschreiben, das der SVERWEIS nur dann aktiv werden soll, wenn es einen neuen Eintrag gibt.
Beispielformel aus Zeile 200: =WENN(A200=””;””;SVERWEIS(A4&B4;’Rohdaten ZP88′!A:D;4;0))
In A200 müsste dann eine Abfrage nach Tabelle1 Zeile A200 geben (=Tabelle1!A200)
Probiere mal…LG John
Hi John,
das habe ich verstanden aber neue Projekte haben andere/neue Namen als die vorhandenen Projekte. Und wenn ein Projekt abgeschlossen ist verschwindet es ja
Hi Adam, dann kommt es auf die Logik an, die du anwendest. Wenn du Projekte, die abgeschlossen sind aus der Tabelle1 raus nimmst, dann kommt es darauf an, wie du die entfernst. Löschst du die Zeile, hast du in Tabelle ein #REF Zeichen drin, weil die Referenzierung zum vorigen Eintrag fehlt. Wenn du jedoch nur den Inhalt löschst, dann musst du in Tabelle2 den Inhalt entsprechend dem neuen Projekt anpassen.
Oder du baust dir ein kleines VBA Makro und aktualisierst alle. LG John
Hi John,
es soll mir lediglich angezeigt werden ob sich was in der Tabelle geändert hat oder nicht. Hintergrund ist, das man dadurch nicht manuell suchen muss. Wenn die neue Tabelle ich sag mal Wöchendlich aus SAP aktualisiert wird und da drin abweichungen sind, soll er mir die Anzeigen!
LG Adam
Hi Adam, das geht ein bisschen über diesen Artikel (SVERWEIS) hinaus. LG John
Hi John,
das konnte ich mir schon denken das dass ganze ein bißchen komplxer wird. Aber gibt es da eine Möglichkeit???
LG Adam
Hi Adam, klar gibt es die. In Excel kannst du fast ALLES lösen. Dazu wäre ein kleines Programm im Hintergrund notwendig, dass immer dann durchläuft, wenn Änderungen an dieser einen Tabelle vorgenommen wird. LG John
Hi John,
welches wäre das?
LG Adam
Hi Adam, na das müsste man ja erst schreiben. LG John
Hi John,
ich hätte da mal wieder ein Problem.
Ich soll in einer Tabelle in der Spalte B ab Zeile 13 bis 920 die Datums die schon sortiert sind farblich hinterlegen nach 4 kriterien. Die Datums gehen von 16.01.2018-04.06.2018
1. Das erste datum vom 16.01 bis einschließlich 19.02 mit rot ausfüllen
2. Das zweite Datum 26.03 soll alles nach dem 19.02 bis einschließlich 26.03 mit Gelb ausfüllen
3. Das dritte Datum 16.04 soll alles nach dem 26.03 bis einschließlich 16.04. mit Grün ausfüllen
4. Das vierte Datum 04.06 soll alles nach dem 16.04 bis einschließlich 04.06 mit Blau ausfüllen
Das ganze soll in Bedingte Formation unter neue Regel stattfinden, denn die Stichdaten die sich auch verändern könne stehen in D4, D5, D6 & D7 und wenn die sich ändern soll sich auch die farbe in der tabelle ändern bzw. angepasst werden an das neue Datum.
LG Adam
Hallo John,
ich habe mal wieder ein Problem. Ich habe aus Tabelle 1 mir 3 wichtige Daten mit einer Formel raus kopiert und die dann in Tabelle 2 eingefügt. Das ist ein Automatist, sprich: Wenn Tabelle 1 in Spalte A3 ein “X” steht, gehen die 3 wichtigsten Daten in Tabelle 2. Wenn die dort ankommen, möchte ich das die, die in Tabelle 2 sind in Tabelle 1 gelöscht werden aber dann die ganze Zeile (ca. 20 Spalten). Gibt es dafür eine formel oder nur über Makros?
LG
Adam
Hallo John,
ich hätte da mal wieder ein Problem. Und zwar die Tabelle kennst du ja, wir haben jetztz neu in Spalte A Überprüft mit rein genommen und wenn es überprüft wurden ist mit ein X gekennzeichnet. Wenn sich in der Tabelle etwas ändert, soll es farblich angezeigt werden und kein X in der Spalte A sein. Sobald es Überprüft wurden ist soll auch die Farbliche markierung weg gehen.
LG Adam
Hallo John,
ich würde auch gerne mal deine Hilfe in Anspruch nehmen. Ich habe eine Prüfliste mit sagen wir mal ca. 1000 Einträgen. Nun erhalte ich eine andere Liste mit dem Nachweis darüber da ein Prüfling geprüft wurde. Allerdings unterscheiden sich einige Werte aus der Prüfliste mit denen der geprüften. Ein Beispiel, in Spalte G Zeile 7 steht das zu prüfende Objekt mit dem Wert 1234/1.
Nun erhalte ich die aber in der Nachweisliste in Spalte B Zeile 345 den Wert 1234 und in der Spalte C Zeile 345 steht 1.
Wie mache ich nun meiner Prüfliste klar das aus der Nachweisliste die Spalte B und C zusammen gehören. Vielen Dank schonmal
Hallo Kenny, sorry, habe ich erst jetzt gelesen. Was hältst du davon, in Spalte “D” (sofern sie leer ist) beide Spalten zusammen zu ziehen. Z.B so: = B345 & “/” & C345. Dann kannst du die beiden Listen vergleichen.
Wenn ich es noch nicht verstanden habe, dann bitte genauer beschreiben. Danke LG John
Hallo John Doe,
ich stehe leider auch vor einem Rätsel.
Ich habe 2 Listen.
1. Masterliste
Hierin sind Spalten von A-N.
In Spalte A steht meine Basis, also eine Kontonummer (ca 4400 Zeilen)
In Spalte O möchte ich dann den SVERWEIS einfügen.
In Tabellenblatt 2 Stehen ebenfalls Kontonummern, jedoch ca nur halb so viele (ca 2200 Zeilen)
In diesem Tabellenblatt 2 steht neben der Kontonummer auch eine weitere Zahl, die ich in dem ersten Tabellenblatt in Spalte “O” einfügen möchte.
Evt. muss ich auch vorher erst einmal prüfen, welche Kontonummern aus Tabellenblatt 2 in Tabellenblatt 1 enthalten sind?
Nun habe ich noch etwas vergessen.
Masterliste
Spalte A = Kontonummer (ab A2, wegen der Überschrift)
Spalte O= soll der Wert aus Tabellenblatt “2017 Spalte E” wiedergegeben werden
In dem Tabellenblatt 2017 steht folgendes:
Spalte D = Kontonummer (Ab D2, wegen der Überschrift)
Spalte E = Wert, der in Tabellenblatt Sheet1 hinterlegt werden soll.
Evt. hast du einen Denkanstoß für mich.
Meine Formel mit der es nicht geht ist: ==SVERWEIS(A2;’2017′!D2:E2218;2;FALSCH)
Ich komme leider nicht mehr weiter.
Vielen Lieben Dank!
Grüße Natali
Hi Natali, soweit sieht alles gut aus, bis auf die doppelten Gleichheitszeichen
oder war das ein Tippfehler hier im Kommentar?
Eine kleine Anmerkung vorab, bevor du meine anderen Hinweise liest. Wenn du die SVERWEIS Formel im ersten Blatt runterkopierst, verändert sich der Bereich aus dem Blatt ebenfalls.
Das ist ein Excel-Phänomen, das auftritt, wenn man den SVerweis innerhalb einer Datei ausführt.
Zwei Möglichkeiten:
Probiere das mal aus bevor du weiter unten weiter machst 😉
Wenn das nicht funktioniert, was gibt Excel denn für einen Wert aus? #N/A oder anderes?
Beim Rauskopieren deiner Formal ist mir gerade aufgefallen, dass die beiden Apostrophes nicht identisch sind aber das kann auch durch das Kopieren hier in den Blog entstanden sein.
Ich denke mal du gibst die Formel per Hand ein?
LG John
Hi John,
danke für deine schnelle Antwort!
Also, ich habe tatsächlich ein Kopierfehler gehabt.
=SVERWEIS(A2;’2017′!$D$1:$E$2217;2;FALSCH) so sieht die aktuelle Formel aus.
Welche Aporstophes meinst du? bei der Zahl 2017?
Genau, es kommt der Wert #NA raus.
Ich habe es so oft ausprobiert, ich kann gar nicht genau sagen ob ich die letzte Formel jetzt händisch eingegeben habe, oder über die Formel-Funktion.
Soll ich es mal über die Funktion versuchen?
Mein Problem ist halt auch, dass im Tabellenblatt 2017 nur ein Teil der Kontonummern enthalten sind, wie in der Masterliste.
Somit gibt es natürlich auch zusätzlich den Fehler, dass manche Kontonummern gar nicht gefunden werden.
LG Natali
Hi, ich habe in meiner ersten Antwort einen Fehler gemacht (A2; vergessen), das ändere ich gleich nach dieser Antwort.
Richtig ist das hier:
Ja die beiden Apostrophe, die die 2017 umgeben. ‘2017’
Aus dem Internet rauskopieren ist keine gute Sache, denn die Zeichensätze können verschieden sein.
Folge deinem Ansatz,den Formeleditor zu nutzen. Solltest du weiterhin keine Ergebnisse bekommen, dann liegt das höchstwahrscheinlich daran, dass die Formate der beiden Kontonummern auf beiden Blättern unterschiedlich sind.
Eines ist ein Text, das andere einen Zahl und eventuell hat die eine Kontonummer noch ein Leerzeichen am Ende usw….
Die müssen beide vom Inhalt der Zelle UND vom Format her komplett identisch sein.
Wenn du z.B. in deinem Blatt 2017 alle Kontonummern als Text und ohne Leerzeichen (vorne/hinten) eingetragen hast, dann kann dir diese Formel weiter helfen:
=SVERWEIS(GLÄTTEN(TEXT(A2;”#”));’2017′!E:F;2;0)
Das Suchkriterium wird damit so “angepasst”, dass es dem Format deiner Einträge aus dem Blatt 2017 entspricht.
Überprüfe doch bitte in welchem Format sich die beiden Spalten befinden und das es keine Leerzeichen gibt.
LG John
Hi,
leider kommt hier auch wieder eine Fehlermeldung 🙁
=SVERWEIS(A2;‚2017′!$D$1:$E$2217;2;falsch)
Das war jetzt meine Formel.
In der Mastertabelle hatten die Kontonummern tatsächlich eine andere Formatierung. Diese sind jetzt in beiden Listen als “Zahlen” hinterlegt.
Das eine Apostroph habe ich hier aus deinem Text kopiert. Da mit der Formelanwendung immer das obere Apostroph vor 2017 kam.
Welch Mammutaufgabe 🙁
Hallo Natali, wir kriegen das schon hin 🙂
Schau mal hier. Exakt so sollte die Formel aussehen. Und ändere mal bitte “Falsch” in 0 (Null)
Dann befolge doch bitte noch folgenden Hinweis. Du hast jetzt bestimmt die Spalten markiert und alle Zellen auf Zahl gesetzt.
Das würde noch nicht ganz reichen. Die Zellen müssen dieses Format erst einmal “annehmen”, also auch echt umwandeln. Das geschieht nur indem man die Zellen alle einzeln “bearbeitet”.
Das kann man ganz einfach machen. Schau dir bitte meinen Artikel hier an (wird im Extra Fenster geöffnet), da habe ich das alles formuliert. Das sieht jetzt nach viel Arbeit aus aber es muss sein. Danach musst du nur immer dafür sorgen, dass die Zellen auch wirklich immer nur Zahlen beinhalten !!
Bevor du startest, kannst du auch die ersten paar Zellen mit “F2” Enter neu berechnen lassen. Am Besten bei Kontonummern, wo du weisst, dass sie auf beiden Seiten vorhanden sind.
Solltes das alles nicht funktionieren, dann schicke mir die Datei doch einfach mal ohne den kompletten Inhalt, sondern nur 3-4 Datensätze.
LG John
ich habe es versucht, es kommt aber wieder das Ergebnis #NV.
Oh man, ich verstehe es einfach nicht 🙁
Wie kann ich dir denn einen Teil der Liste zukommen lassen?
LG Natali
Hi,
john.doe.pcn@gmail.com
Ich kann aber erst heute Abend nachschauen…OK?
LG John
Wow super!
ja ne, kein Thema. ich habe dir die Liste geschickt.
Wäre super, wenn du später mal drüber schauen könntest.
Vielen Dank!!
LG natali
Hi, Liste kommt per Mail zurück. Schreibe bitte hier ob alles ok ist. LG John
Hallo Natali, kannst du mir ein Feedback geben, ob die Datei so ok ist? LG John
Hi John,
ich habe drei Tabellen wovon zwei mit unterschiedlichen Währungen (CHF und EUR) sind. Des weiteren sind die Preie aus unterschiedlichen Jahren mit unterschiedlichen Artikelnummern. Nun meine Frage wie kann ich diese Tabellen in einer Tabelle mit allen den verlangten Kriterien darstellen.
Dürfte ich Dir die Excel-Daten mailen damit du dies mal betrachten kannst.?
Hallo Thomas, wenn alle 3 Tabellen EINE gemeinsame Komponente (Key) haben, dann kannst du doch in einer vierten Tabelle den SVerweis anwenden und diesen Key als Suchkriterium verwenden.
Du kannst mir die Datei auch gerne schicken. LG John
Hallo John, ich habe dir die Datei gesendet mit.
LG Thomas
Hallo Thomas, die Datei ist angekommen. Nicht ganz trivial ;-). Du möchtest die Preise aus verschiedenen Tabellen (Blätter) in eine Tabelle schreiben, in Abhängigkeit der Jahre. Ich schaue mir das heute Abend nach dem Eröffnungsspiel an. LG John
Hallo Thomas, heute hatte ich Zeit nach dem Spiel Portugal:Spanien (super Spiel). Zu Deinem Anliegen…Du wolltest die Informationen aus zwei Unterschiedlichen Arbeitsblättern in Abhängigkeit des Preisdatums (Jahr) in einer Gesamtübersicht haben. Zuerst habe ich die Spaltenüberschriften so formatiert, dass in der Zelle nur die Jahreszahl steht aber die Währung trotzdem angezeigt wird. Das sieht so aus:
Als nächstes frage ich mit dem SVerweis ab, ob das Bauteil in dem jeweiligen Arbeitsblatt vorkommt:
SVERWEIS(B3;’EUR-Einkaufpreis’!C:C;1;0) ==> wenn es um die EUR Spalte ging und bei der CHF Spalte entsprechend auf das CHF Arbeitsblatt:
SVERWEIS(B3;’CHF-Einkaufpreis’!C:C;1;0) ==> wenn das Bauteil nicht vorkommt, also der SVerweis einen Fehler (#NV) ausgibt, soll nichts angezeigt werden. Das geht folgendermaßen:
WENN(ISTFEHLER(SVERWEIS(B3;’EUR-Einkaufpreis’!C:C;1;0));””;”hier kommt noch was”).
Sollte das Bauteil vorkommen, geht es weiter…..Als nächstes kommt aber nicht der SVerweis zum Zuge, denn der SVerweis funktioniert nur für Ausgaben, die RECHTS von der Suchspalte stehen. Da das Datum aber in der ersten Spalte steht, links von der Suchspalte, müssen wir mit INDEX und VERGLEICH arbeiten. Eine genaue Beschreibung, wie INDEX VERGLEICH funktionieren steckt hinter diesem Link.
Mit INDEX(MATRIX;ZEILE;SPALTE) kann man sich auch Werte LINKS von der Suchspalte ausgeben lassen. MATRIX ist der Bereich, der die Suchspalte und die Ausgabespalte eingrenzt, ZEILE ermitteln wir mit Hilfe von VERGLEICH (das dem SVERWEIS ähnelt) und Spalte ist die Zahl der Spalte aus der MATRIX, also 1. Die Formel, um das Datum aus der Spalte zu dem jeweiligen Bauteil zu erhalten, sieht dann so aus:
INDEX(‘EUR-Einkaufpreis’!$A:$E;VERGLEICH(Summary!$B3;’EUR-Einkaufpreis’!$C:$C;0);1). VERGLEICH gibt bei einem Treffer die ZEILE (als Zahl) zurück.
Diese Formel gibt uns also das Datum des jeweiligen Bauteils zurück. Da wir aber nur das Jahr benötigen, umklammern wir diese Formel mit JAHR(Datum). Das sieht dann so aus:
JAHR(INDEX(‘EUR-Einkaufpreis’!$A:$E;VERGLEICH(Summary!$B3;’EUR-Einkaufpreis’!$C:$C;0);1)) ==> Das Ergebnis wäre dann entweder 2016, 2017 oder 2018. Jetzt wollen wir, dass es nur bei Übereinstimmung der Jahre weiter geht, also:
WENN(JAHR(INDEX(‘EUR-Einkaufpreis’!$A:$E;VERGLEICH(Summary!$B3;’EUR-Einkaufpreis’!$C:$C;0);1)) =$D$2;”mach weiter”;””). OK wir machen weiter…..Jetzt könnte der SVerweis zum Tragen kommen aber es geht auch mit INDEX und VERGLEICH:
INDEX(‘EUR-Einkaufpreis’!$A:$E;VERGLEICH(Summary!$B3;’EUR-Einkaufpreis’!$C:$C;0);4). Zusammengesetzt sieht die Formel dann so aus:
=WENN(ISTFEHLER(SVERWEIS(B3;’CHF-Einkaufpreis’!C:C;1;0));””;WENN(JAHR(INDEX(‘CHF-Einkaufpreis’!$A:$E;VERGLEICH(Summary!$B3;’CHF-Einkaufpreis’!$C:$C;0);1))=$G$2;INDEX(‘CHF-Einkaufpreis’!$A:$E;VERGLEICH(Summary!$B3;’CHF-Einkaufpreis’!$C:$C;0);4);””))
Ich habe dir die Datei zurückgeschickt und die Formeln bis Zeile 34 ausgefüllt (grau markiert). Du kannst dann selber noch ein bisschen probieren oder alles runter ziehen. Ich hoffe es ist das was du erwartet hast. LG John
Hello john,
Vielen Dank für diese tolle Bearbeitung, sowas hätte ich nie auf die Rolle gebracht.
Es funktioniert und konnte die Liste noch erweitern.
Du hast mir unbeschreiblich geholfen. :-), 🙂
Besten Dank und Gruss
Thomas
Hallo Thomas, Danke für dieses Feedback !! LG John
Hallo John,
mit einer MIN / MAX / MITTELWERT Formel =MIN(IF($A$3:$A$4005=A3;IF(C3:E40050;$C$3:$E$4005))) und =MAX(IF($A$3:$A$4005=A3;$C$3:$E$4005)) abgeschlossen mit ARRY eingabe erhalte ich die verlangten Werte. nun sind aber einige Zellen LEER und somit erhalte ich immer #DIV/O!
wie kann ich das beheben.?
Hoffe du kannst mir helfen.
Danke und Gruss
Thomas
Hallo,
ich glaube, dass ich für meinen Fall ebenfalls den SVerweis benötige, komme aber leider noch nicht wirklich klar, vielleicht kann mir ja hier jemand weiterhelfen.
Und zwar habe ich eine Spalte die Vor-und Zuname einer Person, sowie Ort und PLZ enthält. Ich möchte nun mittels einer Hilfsspalte diese Personen verschiedenen Gebieten zuordnen. Ich lege also eine A an in der ich alle vorkommenden Nachnamen eingebe, sowie eine Spalte B in der ich diesen Nachnamen eine Zahl 1-8 die für die Gebiete stehen soll gebe.
Wie bringe ich nun Excel dazu, mir aus der Ausgangsspalte mit den Vor-und Zunamen, sowie Ort und PLZ eine Zuordnung zu den Zahlen 1-8 für die Gebiete herzustellen?
Hi, lass mich das kurz rekapitulieren:
Du hast in deinem Fall eine Spalte, in der Vor- Zuname, Ort und PLZ enthalten sind? Also z.B. Zelle C2 = “Micky Maus 123456 Entenhausen”?
Dann erstellst du eine Spalte mit NUR dem Vor- Zunamen in “A”, also “Micky Maus” und eine Spalte “B” mit z.B. “1”?
Dann möchtest du der Ursprungsspalte (“Micky Maus 123456 Entenhausen”), die jeweiligen Zahlen zuordnen (in diesem Fall”1″)? Habe ich das so richtig verstanden?
Das kann funktionieren, wenn du das Suchkriterium entsprechend anpasst. Du musst nur darauf achten, dass Namen und Zunamen EXAKT identisch sind.
Ich müsste aber vorher genau wissen, wie die ersten Zeilen aussehen. Kannst du das hier posten? Wir finden schon einen Weg 🙂 LG John
Wow, das ging ja schnell mit der Antwort 🙂 Danke!
Ja genau, in der ersten Spalte steht dann zb: ‘Mickey Mouse / 12345 Entenhausen’, dazu habe ich dann eine Hilfsspalte angelegt in der nur der Nachname steht zB ‘Mouse’ (da ich einige sehr komplizierte zb Spanische Namen mit vielen Bestandteilen habe, hielt ich es für einfacher mich auf einen Nachnamen beschränken)und direkt daneben eine Spalte in der ich die zugeordnete Zahl also zB ‘1’ (insgesamt gibt es 8 verschiedene Kategorien) eingegeben habe.
Ist das nachvollziehbar? Oder soll soll ich dir die Datei irgendwie übermitteln?
Guten Morgen, das beste wäre, mir die zuzuschicken, dann kann ich dir in dieser Datei eventuell ein paar unterschiedliche Beispiele aufzeigen, die du dann eventuell verwenden könntest. Einfach an john.doe.pcn@gmail.com senden. Auswertung und weitere Kommentare dann bitte wieder hier über den Blog. LG John
Tausend Dank! Hab dir die Datei zugemailt.
LG
Stefanie
Hi Stefanie, ich habe dir in Spalte “G” den SVerweis eingebaut. Basis, damit der SVerweis etwas findet ist: Dass das Suchkriterium (der erste Parameter im SVerweis) exakt dem entspricht, was er finden soll. Das bedeutet in deinem Fall NAMENSGLEICHHEIT mit deiner Hilfstabelle. In Zelle “G2” (grün unterlegt), ist der komplette SVerweis, in ALLEN Zellen drunter, der “zergliederte” SVerweis, der die Spalten “H”, “I” und “J” benötigt.
Ich bin folgendermaßen vorgegangen:
Die komplette Formel OHNE die Hilfsspalten I-J lautet dann:
SVerweis(H3;Hilfstabelle!A:B;2;0)
Ohne “H” aber mit “I” und “J”:
SVerweis(TEIL(F2;FINDEN(” “;F2)+1;FINDEN(“/”;F2)-FINDEN(” “;F2)-2);Hilfstabelle!A:B;2;0)
Als ich die Formel dann runter gezogen hatte, habe ich gemerkt, dass die Namen aus deiner Hilfstabelle nicht identisch sind. In Zeile 9 hattest du den Anfangsbuchstaben des Vornamen mit einem Punkt hinzugenommen. Um das abzufangen, habe ich den SVerweis (die kurze Version) erweitert, um ISTFEHLER:
IF(ISTFEHLER(SVerweis(H9;Hilfstabelle!A:B;2;0));SVerweis(LINKS(F9;1) & “. ” & H9;Hilfstabelle!A:B;2;0);SVerweis(H9;Hilfstabelle!A:B;2;0))
Dann habe ich noch weitere “Namens-Ungleichheiten” entdeckt, die du aber dadurch beheben kannst, indem du die Hilfstabelle mit den Namen gleichsetzt, die du in Spalte “H” findest, denn das ist ja der Teil vom ERSTEN Leerzeichen, bis zum Schrägstrich aus der Hauptabelle.
Das liest sich jetzt bestimmt sehr aufwändig aber einfach mal in Ruhe durchlesen und anschauen. Die Datei schicke ich dir jetzt bearbeitet zurück.
Solltest du noch etwas benötigen, einfach posten. LG John
Wow, ich weiß gar nicht, weil ich dir danken soll. Da wäre ich nie im Leben drauf gekommen. Nach einer guten Stunde hab ich es jetzt tatsächlich einigermaßen verstanden und auch auf meine noch etwas größere Originaldatei anwenden können.
Aber ich glaube damit bin ich persönlich dann auch am Excel-Limit 😉
Wie gesagt, vielen lieben Dank nochmal für deine Mühe!
Gerne !!
Hallo,
ich habe folgendes Problem.
Tabelle 1 enthält in Spalte A unterschiedliche Datumsangaben (z.B. fünf zufällige Tage eines Jahres).
Tabelle 2 enthält in Spalte A alle Datumsangaben eines Jahres und in Spalte B einen dazugehörigen Wert (z.B. Wassertemperatur).
Mit Hilfe des SVerweises bekomme ich für jede zufällige Datumsangabe in Tabelle 1 die Wassertemperatur aus Tabelle 2. Ich möchte allerdings nicht nur die Wassertemperatur für diesen einen Tag, sondern auch die für die zehn vorherigen sowie die zehn nachfolgenden Tage. Am besten wäre es natürlich, wenn mir diese dann 21 Werte in Spalte B (Tabelle 1) angezeigt werden würden für die erste “zufällige Datumsangabe”, in Spalte C die 21 Werte für die zweite “zufällige Datumsangabe” usw.
Wäre schön wenn jemand eine Idee hat!
Vielen Dank schonmal!
Grüße Daniel
Hallo Daniel, sorry, ich habe deinen Kommentar erst jetzt gesehen. Ich schaue mir das mal die Tage an melde mich hier wieder. LG John
Hallo John,
mit SVerweis wird nur der erst gefundene Wert angegeben. Gibt es eine Möglichkeit / Formel wie auch die weiteren, falls vorhandenen Werte, angezeigt werden können.?
Danke für dein feedback
Gruss
Thomas
Hallo Thomas, sorry aber ich habe deine Kommentare erst heute gesehen. Ich habe deinen zweiten Kommentar auch gelesen (Min/Max/Mittelwert), den gebe ich auch jetzt frei und antworte dir dann sobald ich etwas habe für dich ok?
Kannst du mir vorab deine bisherige Formel schicken? Danke LG John
Hallo,
ich weiß nicht ob ich hier richtig bin. Ich habe folgendes Problem.
Ich habe eine Exceldatei mit vielen Registerblättern. Die Namen der Blätter sind die Zahlen der Autokennzeichen z.B. 327, 333, 1010 usw. In diesen Blättern erfasse ich die Tankrechnungen. Außerdem habe ich ein Blatt mit den Namen “Zusammenfassung” ( ohne Anführungszeichen ). In diesen Blatt möchte ich nun eine Auswertung nach Monat, Menge, Kosten, machen. Das geht über
=SUMMEWENNS(‘333′!$H$9:$H$999;’333′!$B$9:$B$999;”>31.01.2018″;’333’!$B$9:$B$999;”<1.3.2018")
und funktioniert einwandfrei.
In Spalte A lasse ich mir nun die Namen der einzelnen Registerblätter durch ein Makro auflisten.
In Zelle C3 habe ich ein Droptownfeld um ein Kennzeichen aus Spalte A auszuwählen.
Wie muß die Formel aussehen damit mir die Auswahl aus C3 in die Formel übernommen wird.
Ausgehend von der obengenannten Formel ist es der Teil ´333´ der dynamisch sein soll.
Im Internet gibt es zwar verwandte Themen aber ich habe nichts speziell zu meinen Problem gefunden.
Ich probiere schon seid 2 Wochen mit Indirekt herum und bekomme entweder die Meldung #Wert, #Bezug oder es ist ein Fehler In der Formel als Ergebnis.
Was mache ich falsch? Ich kapier es nicht.
Ich wäre sehr Dankbar wenn ich hier eine Antwort bekommen würde die mein Problem löst.
Freundliche Grüße
Hallo Frank, da müsste man genau sehen wie der Aufbau ist. Kannst du mir die Datei schicken? An john.doe.pcn@gmail.com
LG John
Hallo John,
ich werde die Datei entpersonalisieren und dann zusenden. Vielen Dank schon mal im voraus für die Hilfe.
LG Frank
Hi, OK…
Hallo John,
ein gesundes und erfolgreiches Jahr 2019 für dich.
Ich möchte ganz vorsichtig anfragen ob Du meine Datei, die mein Problem hoffentlich ersichtlich macht, erhalten hast?
LG Frank
Hallo Frank, danke, dir auch. Nein, ich habe keine Mail erhalten. Kannst du die bitte erneut senden an john.doe.pcn@gmail.com? Danke LG john
PS: Deinen zweiten Kommentar, mit der Richtigstellung deiner Mail habe ich erhalten, werde ihn aber nicht aktivieren.
Frank
16. November 2018 um 11:29
Hallo,
ich weiß nicht ob ich hier richtig bin. Ich habe folgendes Problem.
Ich habe eine Exceldatei mit vielen Registerblättern. Die Namen der Blätter sind die Zahlen der Autokennzeichen z.B. 327, 333, 1010 usw. In diesen Blättern erfasse ich die Tankrechnungen. Außerdem habe ich ein Blatt mit den Namen “Zusammenfassung” ( ohne Anführungszeichen ). In diesen Blatt möchte ich nun eine Auswertung nach Monat, Menge, Kosten, machen. Das geht über
=SUMMEWENNS(‘333′!$H$9:$H$999;’333′!$B$9:$B$999;”>31.01.2018″;’333’!$B$9:$B$999;”31.12.2017″;INDIREKT(“‘”&$C$3&”‘!”&”$B$9:$B$999”);”<1.2.2018")
Hallo Frank, das ist die eigentliche Anfrage, die du mir eigentlich per Mail hättest senden wollen? Was ist deine Frage? LG John
Hallo John Doe,
ich möchte folgendes Verknüpfen:
– Tabelle 1 beinhaltet folgende Spalten: Vorname, Name, Geldeingang, Betrag
– Tabelle 2 beinhaltet alle detaillierten Kontaktdaten zu den Namen
– Aus Tabelle 2 sollen Unternehmen, Straße, PLZ, Stadt zu den jeweiligen Namen in Tabelle 1 automatisch eingefügt werden
Wie kann ich zwei Kriterien für den SVerweis angeben? Die empfohlene Option von oben (=A1&B1) habe ich schon probiert, dadurch erhalte ich einen Formelfehler.
Vielen Dank für einen Tipp!
Hallo, sorry aber irgendwie funktioniert es nicht, das ich eine Mail erhalte, wenn ein neuer Kommentar gepostet wurde.
Du musst aus beiden Tabellen einen eindeutigen Key bilden, aus eben 2 oder mehreren Spalten. Das muss aber in BEIDEN Tabellen vorkommen.
Wenn Vorname&Name einen eindeutigen Key bilden, dann musst du in der Tabelle 2 eine neue Spalte vorne als erstes einfügen und in diese Spalte die beiden Spalten der Vornamen und Nachnamen aus Tabelle 2 genauso verknüpfen. Wenn die beiden in Tabelle2 auch in A&B vorkommen und du fügst eine neue Spalte ein, dann muss in dieser neuen Spalte B&C verknüft werden. Ich hoffe das war verständlich. Wenn nicht, einfach nochmal hier nachhaken. LG John
[…] Download Image Mehr @ http://www.pcnotfallhilfe.de […]
[…] Download Image Mehr @ http://www.pcnotfallhilfe.de […]
Wow – ich bedanke mich sooo arg für diesen Artikel ! ♥
Das ist genau das wonach ich seit gefühlten Ewigkeiten suche und diese Funktion (deren Nutzen ich ohne diese Erläuterungen hier nie verstanden hätte) wird mir in Zukunft so viel Arbeit und Zeit ersparen!
Danke, Danke, Danke, DANKE!!
Ich kann grad garnicht ausdrücken, wie sehr mir das hilft. 😀
🙂 Ein gesundes, glückliches und ideenreiches Jahr wünsche ich!
Liebe Grüße 🙂
Hi Jeanny, auch für dich alles Gute für das neue Jahr und danke für die warmen Worte….LG John
Hallo John,
ich habe bis jetzt noch keine Lösung ohne VBA gefunden, vielleicht kannst du mir hier ja helfen.
Ich habe 3 Tabellen in einer Arbeitsmappe:
Die ersten drei Spalten der Tabelle A werden automatisch aus Tabelle B entnommen.
Tabelle A
ID|Beschreibung|Abhängigkeit |Beschreibung Abhängigkeit
01|Simulation |Verarbeitung |TextA
15|Verlust |TranA, TranB, TranC |”Hier suche ich die Lösung”
Tabelle B
ID|Beschreibung|Abhängigkeit
01|Simulation |Verarbeitung
15|Verlust |TranA, TranB, TranC
Tabelle C
Abhängigkeit|Beschreibung Abhängigkeit
Verarbeitung|TextA
TranA |Hier
TranB |Hier suche
TranC |ich eine Lösung
Mein Problem besteht darin das ich aus einer Zelle (z.B. C3 Tabelle B) mehrere Wörter in Tabelle C Suche, dass die Ergebnisse sowie gleiche Wörter sollen zusammengefasst werden.
Danke dir für deine Hilfe.
Hi Daniel, ich habe mal eine Formel zusammengesetzt und ein Ergebnis erhalten. Das Ergebnis bedingt aber folgende Voraussetzungen:
Um TRANA aus Tabelle A Zelle C3 zu erhalten folgende Formel: =TEIL(C4;1;SUCHEN(“,”;C4)-1)
Um TRANB aus Tabelle A Zelle C3 zu erhalten folgende Formel: =TEIL(C4;SUCHEN(“,”;C4)+2;SUCHEN(“,”;C4)-1)
Um TRANC aus Tabelle A Zelle C3 zu erhalten folgende Formel: =TEIL(C4;SUCHEN(“,”;C4)+1+SUCHEN(“,”;C4)+2;SUCHEN(“,”;C4)-1)
Jedes einzelne stellt das Suchkriterium für einen SVerweis in D4 dar.
Für TRANA =SVERWEIS(TEIL(C4;1;SUCHEN(“,”;C4)-1);J:K;2;0)
Für TRANB =SVERWEIS(TEIL(C4;SUCHEN(“,”;C4)+2;SUCHEN(“,”;C4)-1);J:K;2;0)
Für TRANC =SVERWEIS(TEIL(C4;SUCHEN(“,”;C4)+1+SUCHEN(“,”;C4)+2;SUCHEN(“,”;C4)-1);J:K;2;0)
Zusammengesetzt und mit einem Leerzeichen getrennt, dann so:
=SVERWEIS(TEIL(C4;1;SUCHEN(“,”;C4)-1);J:K;2;0) & ” ” & SVERWEIS(TEIL(C4;SUCHEN(“,”;C4)+2;SUCHEN(“,”;C4)-1);J:K;2;0)& ” ” & SVERWEIS(TEIL(C4;SUCHEN(“,”;C4)+1+SUCHEN(“,”;C4)+2;SUCHEN(“,”;C4)-1);J:K;2;0)
Das Ergebnis siehst du hier im Bild:
Über ein kurzes Feedback würde ich mich freuen.
LG John
[…] Download Image Mehr @ http://www.pcnotfallhilfe.de […]
[…] SVerweis ist die mit am meisten verwendete Funktion in Microsoft Excel. Die Excel Funktion sieht folgendermaßen […]
[…] verschiedenen Beiträgen habe ich mich generell dem SVerweis (vlookup) gewidmet. Die meisten Beispiele sind dann natürlich […]