Office: VERWEIS Funktion: Fehler bei den Rückgabewerten

Ich suche Hilfe zu dem folgenden Problem :roll: :

Zu einem Wert in Spalte C soll in einer anderen Datei, wo der Wert in Spalte A vorkommen kann, der zugehörige Wert aus Spalte B übertragen werden. Wenn der Wert in der Datei nicht vorkommt, soll nichts übertragen werden.

Die Funktion sieht so aus:
=VERWEIS(C2;[Artenliste.xls]Arten!$A$2:$A$398;[Artenliste.xls]Arten!$B$2:$B$398)

De facto sieht es genau umgekehrt aus. Wenn der Wert aus C in der anderen Datei nicht gefunden wird, wird irgendein Wert übertragen. Wenn der Wert in der Datei gefunden wird, wird #NV übertragen.

Was mache ich falsch :?:

 

Hallo,

schaut man in der Excel-Hilfe zur VERWEIS-Funktion nach, liest man dort:

Also ist ganz und gar wichtig fürs Funktionieren der VERWEIS-Funktion, dass die Werte im Suchvektor aufsteigend sortiert sind. Sonst findet die Funktion entweder einen falschen Wert oder keinen (=#NV). Ferner darf man keinen kleineren Wert eingeben als denjenigen, der in der 1.Zelle von Suchvektor steht - sonst ist das Resultat #NV.

 

Hallo Exl121150 :-)

danke für Deine Antwort.

Sie erklärt, warum ich einen Wert übergeben bekomme, wenn mein gesuchter Wert nicht im Suchvektor der anderen Datei gefunden wird. Dann wird einfach der nächst gelegene höhere Wert zurückgegeben. Das ist allerdings sehr unschön und kann zu Fehlinterpretationen führen. Besser wäre, wenn in dem Fall eben <null> zurückgegeben würde.

Der zweite Fall ist aber weiter unklar. Der gesuchte Wert (Suchkriterium)ist definitiv im Suchvektor der anderen Datei enthalten. Trotzdem wird #NV zurückgegeben.

Was ist da falsch:?:

 

Hallo,

im letzten Posting habe ich ganz besonders darauf hingewiesen, dass der Suchvektor (in Spalte A der externen Datei) unbedingt aufsteigend sortiert sein muss.
Angenommen, die Werte in Spalte A (Suchvektor) haben folgende Abfolge:
33 | 55 | 332 | 333 | 11 | 222 | 444
a) Gibt man als Suchkriterium zB. 33 oder 55 oder 332 oder 333 ein, wird der korrekte Wert aus der 1.-4. Zeile aus dem Ergebnisvektor zurückgegeben.
b) Gibt man jedoch 22 ein, erhält man #NV, weil 22 kleiner als der 1.Wert (=33) ist.
c) Gibt man 11 als Suchkriterium ein, wird nicht etwa der 5. Wert aus Ergebnisvektor zurückgegeben, sondern ebenfalls wieder #NV, obwohl 11 doch in Suchvektor an 5. Stelle enthalten ist. Das rührt daher, dass 11 kleiner als der 1.Wert (=33) in Suchvektor ist.
d) Gibt man 57 als Suchkriterium ein, wird nicht etwa #NV (=nicht verfügbar) zurückgegeben, obwohl dieser Wert in Suchvektor nicht enthalten ist, sondern der 2.Wert aus Ergebnisvektor, da der nächstkleinere Wert in Suchvektor 55 ist - und der steht an 2.Stelle (-->Intervalleffekt).

Um diesem Schlamassel zu entgehen, gibt es 2 Möglichkeiten:

1) Die Werte in Suchvektor (Spalte A der externen Excel-Datei) müssen zwingend aufsteigend sortiert werden - und entsprechend müssen natürlich auch die Werte des Ergebnisvektors (Spalte B) mit umgereiht werden. Dann kann die VERWEIS-Funktion weiter verwendet werden und sie liefert dann korrekte Ergebnisse, falls es sich um Suchkriterien handelt, die im Suchvektor enthalten sind.

Aber auch dann hat die VERWEIS-Funktion 2 Problembereiche:
a) Gibt man ein Suchkriterium ein, das kleiner als der 1.Wert in Suchvektor ist, erhält man als Ergebnis #NV
b) Gibt man ein Suchkriterium ein, das nicht im Suchvektor enthalten ist, aber größer als der 1.Wert im Suchvektor ist, erhält man den Wert aus Ergebnisvektor, der dem zum Suchkriterium nächstkleineren Wert aus Suchvektor entspricht.
Es kommt also darauf an, ob man diesen Intervalleffekt fürs Suchkriterium haben möchte oder nicht.

2) Da der Suchvektor in Spalte A steht und der Ergebnisvektor in Spalte B der externen Excel-Datei, könnte man auch die SVERWEIS-Funktion einsetzen. Diese hat den Vorteil, dass man in ihrem 4. Argument angeben kann, ob man nur genaue Funde aus der 1.Spalte der Matrix haben haben will (=FALSCH oder 0) oder ob sie sich im Prinzip so verhalten soll, wie die VERWEIS-Funktion (=WAHR oder 1).

a) Syntax: SVERWEIS(Suchkriterium;Matrix;Spaltenindex;[Bereich_Verweis])

b) Der "Suchvektor" muss sich dabei in der 1.Spalte von 'Matrix' befinden

c) Das 3.Argument 'Spaltenindex' gibt die Nummer der Spalte aus 'Matrix' an, in der sich der "Ergebnisvektor" befindet; dieser darf sich nicht links von "Suchvektor" befinden (also kein negativer 'Spaltenindex' möglich).

d) Damit man die SVERWEIS-Funktion bei einer externen Datei (2.Argument: Matrix) einsetzen kann, muss man zwingend einen Namen für diesen externen Zellbereich anlegen und diesen Namen in der SVERWEIS-Funktion als 2.Argument (für 'Matrix') verwenden.
Befindet sich zB. die externe Excel-Datei (Artenliste.xls) im selben Verzeichnis als die aktulle Excel-Datei und ist ferner der Bereich 'Matrix' im Arbeitsblatt 'Arten' im Bereich $A$2:$B$398 (Suchvektor wäre dann $A$2:$A$398 und Ergebnisvektor $B$2:$B$398 bzw. Spaltenindex=2), müsste man für den Namen 'Matrix' im Namensmanager folgenden Ausdruck eingeben:
='[Artenliste.xls]Arten'!$A$2:$B$398
ansonsten ist zu Beginn noch ein relativer oder absoluter Pfad mit anzugeben, zB:
='C:\Excel\Tests\[Artenliste.xls]Arten'!$A$2:$B$398

e) Falls Du die Variante, dass nur genaue Funde des Suchkriteriums in der 1.Spalte von Matrix einen Wert aus der 2.Spalte liefern sollen, wünschst, ist folgende Formel einzugeben anstatt VERWEIS(...):
=SVERWEIS(C2;Matrix;2;0)
Dann braucht auch die 1.Spalte von 'Matrix' nicht aufsteigend sortiert zu sein. Werte aus C2, die nicht in der 1.Spalte von 'Matrix' enthalten sind, liefern den Wert #NV (=nicht verfügbar).

f) Falls Du die Variante, dass sich die SVERWEIS-Funktion so verhalten solle wie die VERWEIS-Funktion, haben möchtest, ist folgende Formel einzugeben anstatt VERWEIS(..):
=SVERWEIS(C2;Matrix;2;1)
oder
=SVERWEIS(C2;Matrix;2)

 

Hallo Exl121150 :-)

danke für die ausführlichen Beschreibungen. Die habe ich auch verstanden - aber SVERWEIS reagiert nicht wie beschrieben.

Z.B. bei der Formel =SVERWEIS(C2;Matrix;2;0) liefert es das Ergebnis #Wert, obwohl zu dem Suchriterium in der Datei in der neben der Matrix befindlichen Spalte ein Ergebnis steht. Ich verstehe das nicht.
:-? (wenn ich statt dessen =SVERWEIS(C2;Matrix;1;0) verwende, gibt mir die Funktion das Suchkriterrium richtig zurück)

Ich bin daher auf die Funktion VERWEIS ausgewichen und habe in der Verweisdatei jetzt sichergestellt, das es zu jedem Suchkriterium auch einen Wert gibt. In der Spalte der Rückgabewerte, wo zu dem Suchwert nichts eingetragen ist wird mir "0" zurückgegeben. Damit kann ich leben.

Schöne Grüße
Thomas

 

Hallo,

hast Du tatsächlich im Namensmanager für den Namen 'Matrix' (oder welchen Namen Du auch immer verwendest) einen mindestens zweispaltigen Bereich festgelegt?

Denn wenn Du die Werte der Kriteriumsspalte richtig zurückerhältst, habe ich den starken Verdacht, dass Du nur einen einspaltigen Bereich festgelegt hast (und dann erhält man den Fehlerwert #WERT, falls der Spaltenindex außerhalb dieses Bereichs zu liegen kommt - Excel2007 überprüft das sehr genau!!).

 

Hallo Exl121150 :-D

Deine Vermutung ist richtig; ich habe die "Matrix" nur einspaltig definiert.
Das war der Fehler. Jetzt funktioniert es :!:

Nochmals vielen Dank für die Hilfe
Thomas