6
SVERWEIS zu externer Quelle funktioniert nicht
Guten morgen allesamt,
ich habe folgendes kleines Problem. Ich habe einen Sverweis, in der das Suchkriterium aus einer anderen Excel Tabelle ist. Das zu suchende Wort gibt es auf jeden Fall in der zweiten Spalte der Matrix. Als Ergebnis wird mir aber nicht die Zelle der ersten Spalte zurück gegeben, sondern nichts, die Zelle bleibt leer. (Wenn in der ersten Spalte #NV Werte stehen, werden diese hingegen angegeben, wenn ein passender Treffer in der zweiten Spalte ist.)
Wo liegt das Problem?
ich habe folgendes kleines Problem. Ich habe einen Sverweis, in der das Suchkriterium aus einer anderen Excel Tabelle ist. Das zu suchende Wort gibt es auf jeden Fall in der zweiten Spalte der Matrix. Als Ergebnis wird mir aber nicht die Zelle der ersten Spalte zurück gegeben, sondern nichts, die Zelle bleibt leer. (Wenn in der ersten Spalte #NV Werte stehen, werden diese hingegen angegeben, wenn ein passender Treffer in der zweiten Spalte ist.)
=SVERWEIS([import.xls]Applications!$E5;'CI Server Hardware'!$A$8:$B$200;1) Wo liegt das Problem?
Share on Facebook
Share on Twitter
Share on Reddit
Share on LinkedinPlease also mark the comments that contributed to the solution of the article
Content-Key: 54497
Url: https://administrator.de/contentid/54497
Printed on: April 26, 2024 at 02:04 o'clock
6 Comments
Latest comment
Hallo Shaggy84!
Was Du vorhast, kann eigentlich nicht funktionieren, da der SVERWEIS nur in der ersten Spalte der angegebenen Matrix sucht - daher müsstest Du die Reihenfolge der Spalten in der externen Quelle ändern oder alternativ mit VERWEIS arbeiten - dort kannst Du die zweite Spalte als "Suchvektor" und die erste Spalte als "Ergebnisvektor" angeben. Dabei ist zu beachten, dass der "Suchvektor" aufsteigend sortiert sein muss, da die Suche nach Bereichen erfolgt (gefunden wird, wie beim SVERWEIS in dessen Dafault-Variante, die letzte Zeile, deren Eintrag <= dem Suchbegriff ist).
Wenn die zweite Spalte nicht sortiert ist oder Du nach genauer Übereinstimmung suchen musst, könntest Du VERGLEICH verwenden. Beispiel (Spalte 1 = B2:B10, Spalte 2 = C2:C10, Suchbegriff in A2):
Das letzte Argument im Vergleich muss 0 sein, damit exakte Übereinstimmung verlangt wird.
Grüße
bastla
Was Du vorhast, kann eigentlich nicht funktionieren, da der SVERWEIS nur in der ersten Spalte der angegebenen Matrix sucht - daher müsstest Du die Reihenfolge der Spalten in der externen Quelle ändern oder alternativ mit VERWEIS arbeiten - dort kannst Du die zweite Spalte als "Suchvektor" und die erste Spalte als "Ergebnisvektor" angeben. Dabei ist zu beachten, dass der "Suchvektor" aufsteigend sortiert sein muss, da die Suche nach Bereichen erfolgt (gefunden wird, wie beim SVERWEIS in dessen Dafault-Variante, die letzte Zeile, deren Eintrag <= dem Suchbegriff ist).
Wenn die zweite Spalte nicht sortiert ist oder Du nach genauer Übereinstimmung suchen musst, könntest Du VERGLEICH verwenden. Beispiel (Spalte 1 = B2:B10, Spalte 2 = C2:C10, Suchbegriff in A2):
=INDEX(B2:B10;VERGLEICH(A2;C2:C10;0))Grüße
bastla
Hallo bastla,
man man man, du bist ja immer superschnell mit den Lösungen, und die funktionieren auch immer bestens
Großes Danke!
Habe noch eine weitere Frage. Stell dir vor, mein Suchkriterium heisst nicht nur "12345" sondern einige auch "12345, 98765".
Könnte man ein Makro bauen, dass in so einem Fall automatisch eine leere Zelle über der mit der Formel baut und beide Abfragen trennt in "12345" und "98765".
(Hört sich ziemlich kompliziert an, so etwas zu trennen und dann auch noch die Formeln zu kopieren.)
Gruß, Shaggy
man man man, du bist ja immer superschnell mit den Lösungen, und die funktionieren auch immer bestens
Großes Danke!Habe noch eine weitere Frage. Stell dir vor, mein Suchkriterium heisst nicht nur "12345" sondern einige auch "12345, 98765".
Könnte man ein Makro bauen, dass in so einem Fall automatisch eine leere Zelle über der mit der Formel baut und beide Abfragen trennt in "12345" und "98765".
(Hört sich ziemlich kompliziert an, so etwas zu trennen und dann auch noch die Formeln zu kopieren.)
Gruß, Shaggy
Hallo Shaggy84!
Sofern ich Dich richtig verstehe, willst Du nach mehreren Kriterien suchen, diese aber alle in einer Zelle (durch "," getrennt) eingeben.
Falls ja, lässt sich vorweg festlegen, wie viele Kriterien das maximal sein werden? In diesem Fall könntest Du die entsprechende Anzahl von Ergebniszellen bereits einplanen, ohne irgendwelche Verschiebungen berücksichtigen zu müssen.
Bei nur 2 möglichen Kriterien ließe sich das noch bequem mit einzelnen Excel-Formeln umsetzen - Beispiel dazu (analog zu oben): Dein zusammengesetzter Suchbegriff steht in A2, die Spalte mit den Suchbegriffen in C2:C10, die Spalte mit den Suchergebnissen in B2:B10. Für die Ergebnisse sind die Zellen F2 und F3 vorgesehen:
Die Formeln sind ein wenig länger, weil ich auf die Verwendung von Hilfszellen / Zwischenergebnissen verzichte und Leerzeichen rund um das "," ausfiltere.
Ab 3 Kriterien ginge das Ganze nicht mehr so einfach, sodass ich dann tatsächlich zu VBA greifen würde.
Frage allerdings: Warum sollen eigentlich die Kriterien in einer Zelle zusammengefasst werden - wenn sie auf einzelne Zellen verteilt wären, müsstest Du nur auf das Vorhandensein eines Eintrages in diesen Zellen prüfen und die ganze Zerlegerei entfiele. Beispiel für 4 Kriterien (im Vergleich zu oben stehen die einzelnen Kriterien in den Zellen A2:A5):
Diese Formel funktioniert auch in F3 bis F5 (einfach nach unten kopieren).
Grüße
bastla
Sofern ich Dich richtig verstehe, willst Du nach mehreren Kriterien suchen, diese aber alle in einer Zelle (durch "," getrennt) eingeben.
Falls ja, lässt sich vorweg festlegen, wie viele Kriterien das maximal sein werden? In diesem Fall könntest Du die entsprechende Anzahl von Ergebniszellen bereits einplanen, ohne irgendwelche Verschiebungen berücksichtigen zu müssen.
Bei nur 2 möglichen Kriterien ließe sich das noch bequem mit einzelnen Excel-Formeln umsetzen - Beispiel dazu (analog zu oben): Dein zusammengesetzter Suchbegriff steht in A2, die Spalte mit den Suchbegriffen in C2:C10, die Spalte mit den Suchergebnissen in B2:B10. Für die Ergebnisse sind die Zellen F2 und F3 vorgesehen:
<i>In F2:</i> =WENN(ISTFEHLER(SUCHEN(",";A2));INDEX($B$2:$B$10;VERGLEICH(A2;$C$2:$C$10;0));INDEX($B$2:$B$10;VERGLEICH(GLÄTTEN(LINKS(A2;SUCHEN(",";A2)-1));$C$2:$C$10)))
<i>In F3:</i> =WENN(ISTFEHLER(SUCHEN(",";A2));"";INDEX($B$2:$B$10;VERGLEICH(GLÄTTEN(RECHTS(A2;LÄNGE(A2)-SUCHEN(",";A2)));$C$2:$C$10;0))) Ab 3 Kriterien ginge das Ganze nicht mehr so einfach, sodass ich dann tatsächlich zu VBA greifen würde.
Frage allerdings: Warum sollen eigentlich die Kriterien in einer Zelle zusammengefasst werden - wenn sie auf einzelne Zellen verteilt wären, müsstest Du nur auf das Vorhandensein eines Eintrages in diesen Zellen prüfen und die ganze Zerlegerei entfiele. Beispiel für 4 Kriterien (im Vergleich zu oben stehen die einzelnen Kriterien in den Zellen A2:A5):
<i>In F2:</i> =WENN(A2="";"";INDEX($B$2:$B$10;VERGLEICH(A2;$C$2:$C$10;0))) Grüße
bastla
Hallo bastla,
wenn ich deinen Code richtig verstehe, müsste ich ja schon vorher wissen, ob in dem Suchkriterium ein Komma drin steht oder nicht.
Leider habe ich die Information nicht. Ich weiss nur, dass es "12345" oder "12345, 67890" oder "12345, 67890, 54321" usw. sein könnte.
Deswegen kommt natürlich mit der alten Formel ein #NV wenn mehrere Suchkriterien in einer Zelle stehen.
So sollte er dementsprechend automatisch neue Zeilen einfügen, also bei drei Suchkriterien zwei neue Zeilen oben drüber um jedem Kriterium eine Zeile geben zu können. (ist das überhaupt möglich?)
Gruß, Shaggy
wenn ich deinen Code richtig verstehe, müsste ich ja schon vorher wissen, ob in dem Suchkriterium ein Komma drin steht oder nicht.
Leider habe ich die Information nicht. Ich weiss nur, dass es "12345" oder "12345, 67890" oder "12345, 67890, 54321" usw. sein könnte.
Deswegen kommt natürlich mit der alten Formel ein #NV wenn mehrere Suchkriterien in einer Zelle stehen.
So sollte er dementsprechend automatisch neue Zeilen einfügen, also bei drei Suchkriterien zwei neue Zeilen oben drüber um jedem Kriterium eine Zeile geben zu können. (ist das überhaupt möglich?)
Gruß, Shaggy
Hallo Shaggy84!
Wenn also unbedingt mehrere Kriterien in eine Zelle sollen, könnte ich mir folgende Vorgangsweise vorstellen:
Grüße
bastla
müsste ich ja schon vorher wissen, ob in dem Suchkriterium ein Komma drin steht oder nicht.
Nein; dazu wird das SUCHEN() verwendet, das einen Fehler ergibt, wenn kein Komma enthalten ist; anhand des Fehlers kann dann weiter vorgegangen werden.Wenn also unbedingt mehrere Kriterien in eine Zelle sollen, könnte ich mir folgende Vorgangsweise vorstellen:
- Zelle mit Suchkriterien markieren, zB A2
- Makro per Tastenkombination oder Schaltfläche starten
- Per Makro:
- Zellinhalt aufteilen; benötigte Anzahl von neuen Zeilen einfügen (also zB Kriterien dann in A2 bis A5)
- Formeln einfügen (an fixierter relativer Position, zB in F2 bis F5)
- Zusätzliche Voraussetzung: Für Such- und Ergebnisspalte müssen Bereichsnamen vergeben worden sein.
Grüße
bastla
Hallo Shaggy84!
Nach etwas genauerer Beschäftigung mit Deinem Wunsch halte ich die Umsetzung nicht für sinnvoll.
Wenn es tatsächlich eine Makro-Lösung werden sollte, wäre diese vergleichsweise unflexibel - als Minimum müssten die Positionen der Such- und Ergebnisspalten dem Makro bekannt sein (am ehesten noch, wie schon angesprochen, über in der Tabelle vergebene Namen) und es müsste gleichfalls im Makro festgelegt werden, wieviele Spalten weiter rechts die Ergebnisse abzulegen wären.
Das Makro könnte zwar auch dynamisch auf eine Änderung der Zelle mit den Suchkriterien reagieren, müsste dann aber noch zusätzlich berücksichtigen, ob noch aus dem früheren Inhalt resultierende Reste vorhanden und noch aktuell oder bereits veraltet (und damit wieder zu entfernen?) wären, etc.
Ein Staten "von Hand" würde zwar ev diese Entscheidung an den Benutzer delegieren, aber auch so müsste dann zunächst einmal festgestellt werden, welche Zeilen denn im Hinblick auf die neuen Kriterien noch brauchbar oder vielleicht auch noch gar nicht vorhanden wären.
Welcher Einwand besteht denn tatsächlich gegen eine Aufteilung der Suchbegriffe auf mehrere Zellen (für den Benutzer sollte es doch relativ egal sein, ob er die Kriterien durch Kommata oder die Eingabetaste voneinander trennt)?
Wenn Du näher beschreibst, welche Zielsetzung die ganze Aktion eigentlich verfolgt, könnten wir vielleicht eine brauchbare Alternative zum bisherigen Ansatz finden.
Grüße
bastla
Nach etwas genauerer Beschäftigung mit Deinem Wunsch halte ich die Umsetzung nicht für sinnvoll.
Wenn es tatsächlich eine Makro-Lösung werden sollte, wäre diese vergleichsweise unflexibel - als Minimum müssten die Positionen der Such- und Ergebnisspalten dem Makro bekannt sein (am ehesten noch, wie schon angesprochen, über in der Tabelle vergebene Namen) und es müsste gleichfalls im Makro festgelegt werden, wieviele Spalten weiter rechts die Ergebnisse abzulegen wären.
Das Makro könnte zwar auch dynamisch auf eine Änderung der Zelle mit den Suchkriterien reagieren, müsste dann aber noch zusätzlich berücksichtigen, ob noch aus dem früheren Inhalt resultierende Reste vorhanden und noch aktuell oder bereits veraltet (und damit wieder zu entfernen?) wären, etc.
Ein Staten "von Hand" würde zwar ev diese Entscheidung an den Benutzer delegieren, aber auch so müsste dann zunächst einmal festgestellt werden, welche Zeilen denn im Hinblick auf die neuen Kriterien noch brauchbar oder vielleicht auch noch gar nicht vorhanden wären.
Welcher Einwand besteht denn tatsächlich gegen eine Aufteilung der Suchbegriffe auf mehrere Zellen (für den Benutzer sollte es doch relativ egal sein, ob er die Kriterien durch Kommata oder die Eingabetaste voneinander trennt)?
Wenn Du näher beschreibst, welche Zielsetzung die ganze Aktion eigentlich verfolgt, könnten wir vielleicht eine brauchbare Alternative zum bisherigen Ansatz finden.
Grüße
bastla
