Supportnet Computer
Planet of Tech

Supportnet / Forum / Tabellenkalkulation

Verkettetes Suchkriterium beim SVERWEIS





Frage

Hallo zusammen, als Neuling in diesem Forum bitte ich um Hilfe bei einem Problem, bei dem ich nicht weiterkomme! Zur vereinfachten Erklärung des Problems habe ich 2 kleine Tabellen in einem Arbeitsblatt definiert: Tabelle 1 enthält eine Zeile mit 4 Spalten mit folgenden Werten: [code]Spalte A = 19.02 Spalte B = 1 Spalte C = 2 Spalte D = einzusetzender Betrag aus Tabelle 2[/code] Tabelle 2 enthält ebenfalls nur eine Zeile mit 2 Spalten mit folgenden Werten: [code]Spalte A = 190212 Spalte B = 75,00[/code] Der Hintergrund ist; ich möchte in Abhängigkeit von einem Datum (Tabelle 1/ Spalte A) und weiteren Kriterien in Spalte B bzw. C einen Betrag aus Tabelle 2, Spalte B in die Tabelle 1 Spalte D übernehmen. Dazu habe ich in der Zelle D1 der Tabelle 1 folgenden SVERWEIS definiert: [code]=SVERWEIS("=TEIL(A1;1;2)&=TEIL(A1;4;2)&B1&C1))";Tabelle2!$A$1:$B$1;2;WAHR) [/code] Als Ergebnis bekomme ich in der Spalte D der Tabelle 1 den Fehlercode [code]#NV[/code] angezeigt und nicht den erwarteten Betrag von 75,00 €. Wäre nett, wenn mir jemand bei der Lösung des Problems helfen könnte. Gruss hanswerner

Antwort 1 von M.O.

Hallo Hans Werner,

wenn du mit TEIL deine Zahl zusammensetzt, wird diese als Text formatiert und daher von SVERWEIS nicht als Zahl erkannt. Außerdem sind die Gleichheitszeichen in der Formel überflüssig.
Versuch es mal mit folgender Formel:
=SVERWEIS(WERT(TEIL(A1;1;2)&TEIL(A1;4;2)&B1&C1);Tabelle2!A1:B1;2;WAHR)

Gruß

M.O.

Antwort 2 von hanswerner

Hallo M.O.,
danke für die schnelle Antwort.
Hab den SVERWEIS geändert, allerdings ohne durchschlagenden Erfolg!

=SVERWEIS(WERT(TEIL(A1;1;2)&TEIL(A1;4;2)&B1&C1);Tabelle2!A1:B14;2;WAHR)


Ich kapiere die Logik nicht, die dahinter steckt?
Ich bekomme zwar Beträge angezeigt, aber nicht die, die ich eigentlich erwartet habe. Die Tabelle 2 ist natürlich größer wie in meinem Beispiel beschrieben.
Ich hab sie etwas vergrößert um zu zeigen was nach meinen Eingaben in die Zellen A, B und C in der Zelle D der Tabelle 1angezeigt wird:

Tabelle 1

-----A ---B----C----D(Ergebnis IST)---(Ergebnis Soll)
09.04---2-----4---------#NV-----------------85,00
01.01---11---1----------#NV---------------123,00
12.04---11----1---------83,00-------------112,00
22.02---11----1-------102,00-------------105,00



Tabelle 2
---A--------------B------
090424 85,00
0101111 123,00
0101112 108,00
0101113 108,00
0101114 83,00
140491 102,00
1204111 112,00
1204112 95,00
1204113 95,00
1204114 75,00
2202111 105,00
2202112 90,00
2202113 90,00
2202114 75,00

Gibt es in Excel eine Möglichkeit sich die Auflösung der Verkettung im SVERWEIS anzeigen zu lassen?
Vielleicht kapiere ich es, wenn ich sehe mit welchem Suchkriterium in der Tabelle 2 gesucht wird?


Gruss hanswerner

Antwort 3 von M.O.

Hallo hanswerner,

ein Problem mit deiner Zahlenreihe sind Werte mit führenden Nullen. Diese werden bei der Funktion WERT nicht angezeigt, deshalb gibt es bei den ersten beiden Werten die Fehlermeldung NV.
Falls die Werte in der Tabelle als Zahlen formatiert sind, müsste man entweder die führende Null weglassen, oder diese Zahlen als Text formatieren. Dann musst du in der SVERWEIS Formel allerdings auch den WERT weglassen.
Wenn du in der SVERWEIS - Formel mit WAHR arbeitest, müssen die Werte in der betreffenden Tabelle aufsteigend sortiert sein. Probier mal die Formel mit FALSCH.

Wenn du das Ergebnis der Verkettung sehen willst, kopierst du den Verkettungsteil am besten eine seperate Zelle. Eine andere Möglichkeit gibt es leider nicht.

Gruß

M.O.

Antwort 4 von hanswerner

Hallo,
zunächst einmal Dank an M.O. Die Hinweise haben mir sehr geholfen. Der SVERWEIS funktioniert nun so wie ich es mir vorgestellt habe!
Daraus ergibt sich nun für mich ein neues Problem.

Die Tabelle 2 enthält in der Spalte B EURO-Beträge, die ich durch das Suchkriterium in Spalte A über den SVERWEIS zuordne.

Tabelle 2
---A--------B------
12411-----119,00 
19212-----100,00 
999911-----88,00
999912-----65,00


Das funktioniert, wenn der Schlüssel in der Spalte A der Tabelle 2 gefunden wird. Im anderen Falle (Schlüssel nicht in Tabelle 2) möchte ich immer den Betrag erhalten, der unter 9999XX (wobei XX 11 oder 12 sein kann) gespeichert ist.

Der SVERWEIS soll nur ausgeführt werden, wenn in den Spalten A, B und C der Tabelle 1 Werte eingegeben werden (daraus bilde ich das Suchkriterium für den SVERWEIS)

Eingaben in Tabelle 1:

--A-----B----C----D(gewünschtes Ergebnis)
12.04---1----1-----119,00
19.02---1----2-----100,00
05.03---1----1------88,00
15.04---1----2------65,00


Folgende Funktion soll das bewirken:

=WENN(UND(A1>"";B1>"";C1>"")ISTFEHLER(SVERWEIS("9999"&B1&C1;Tabelle2!$A$1:$B$4;2;WAHR;SVERWEIS(WERT(TEIL(A1;1;2)&TEIL(A1;4;2)&B1&C1);Tabelle2!$A$1:$B$4;2;WAHR)))


das klappt aber nicht! EXCEL sagt, die Formel enthält einen Fehler, warum?
Kann man eine Funktion eigentlich so aufbauen?

Vielen Dank für jede Hilfe.

hanswerner

Antwort 5 von M.O.

Hallo hanswerner,

mit folgender Formel sollte es funktionieren:

=WENN(UND(A1<>"";B1<>"";C1<>"");WENN(ISTFEHLER(SVERWEIS(WERT(TEIL(A1;1;2)&TEIL(A1;4;2)&B1&C1);Tabelle2!$A$1:$B$4;2;FALSCH));SVERWEIS(WERT("9999"&B1&C1);Tabelle2!$A$1:$B$4;2;FALSCH);SVERWEIS(WERT(TEIL(A1;1;2)&TEIL(A1;4;2)&B1&C1);Tabelle2!$A$1:$B$4;2;FALSCH));"")

Gruß

M.O.

Antwort 6 von hanswerner

Hallo M.O.
die Formel funktioniert!
Allerdings nur, wenn das Datum in der 1. Stelle keine 0 enthält.

Beispiel:

-----Anzeige Zelle A1
---(Format Benutzerdefiniert )
-----(Typ 00\:00--------------------Inhalt der Zelle A1:

 --------14.04----------------------------1404
 --------03.04----------------------------304


Die Formel
...(WERT(TEIL(A1;1;2)&TEIL(A1;4;2).....

liefert im 1. Fall den Wert "144" und im 2. Fall den Wert "30".
Wenn ich das Suchargument für die über SVERWEIS verbundene Tabelle bilde, gibt es im 2. Fall keine Übereinstimmung!

Das Problem ist die unterschiedliche Länge des Zelleninhaltes. Meine Überlegung ist nun, das irgendwie mit der Funktion =LÄNGE in den Griff zu bekommen. (Prüfen wie lang der Zelleninhalt ist und in Abhängigkeit davon das Suchargument zusammensetzen.
Das würde die Formel aber noch unübersichtlicher machen.
Gibt es da evtl. eine einfachere Lösung?
Die Formatierung der Zellen in Spalte A sollte nicht geändert werden!

Gruss hanswerner

Antwort 7 von M.O.

Hallo hanswerner,

die einzige Möglichkeit, die mir einfällt, wäre eine Hilfsspalte (die man ja ggf. ausblenden kann) in der Tabelle 2.

Nach der Spalte A der Tabelle 2 könnte man eine Spalte B einfügen und dort die Formel =WERT(A1) einfügen:
A--------------B---------C
012411---12411---119
...
999912--999912----85

Die Formel in der Tabelle1 müsste entsprechend angepasst werden und dann so aussehen:
=WENN(UND(A1<>"";B1<>"";C1<>"");WENN(ISTFEHLER(SVERWEIS(WERT(TEIL(A1;1;2)&TEIL(A1;4;2)&B1&C1);Tabelle2!$B$1:$C$5;2;FALSCH));SVERWEIS(WERT("9999"&B1&C1);Tabelle2!$B$1:$C$5;2;FALSCH);SVERWEIS(WERT(TEIL(A1;1;2)&TEIL(A1;4;2)&B1&C1);Tabelle2!$B$1:$C$5;2;FALSCH));"")

Gruß

M.O.

Antwort 8 von Aliba

Hi,

ich glaube wir haben hier erstmal das Problem, daß es sich auch in Spalte A der Tabelle2 zum Teil nicht um Werte handelt.
z.B. 090204
kann ja eigentlich nur Text sein.
Zum 2. haben wir das Problem, daß durch die kombinierte WERT und TEIL-Formel eben aus z.B.
09.02 eben 92 und nicht wie gewünscht 902 bzw. eigentlicht ja 0902
Nun weiss man natürlich nicht, wie in Tabelle2 in Spalte A die Eintragungen, die nicht mit 0 beginnen, interpretiert sind, als Text oder als Zahl.
Einen ersten Test würde ich mal anstelle von TEIL und WERT mit WECHSELN machen.

=SVERWEIS(WECHSELN(A1;".";"")&B1&C1;Tabelle2!A:B;2;0)

Jetzt mal prüfen, ob die Verweise richtig sind. Wenn es teilweise funktioniert (zu erwarten ist, daß es bei führenden Nullen funktioniert und bei allen anderen Werten nicht), dann müsste man die erzeugten Texte wieder als Ganzes in einen Wert wandeln und aber auch die Spalte A von Tabelle2 in Werte wandeln.

Also erstmal prüfen und dann die Ergebnisse melden.

CU Aliba

Antwort 9 von hanswerner

Hallo ,
Danke an Alle, die auf meine Fragen hier geantwortet haben.
Ich hatte nicht erwartet, dass mir so schnell geholfen werden würde.
Das Problem mit den führenden Nullen in meiner Datumsspalte habe ich anders gelöst.
Zunächst einmal habe ich die Zellen in Spalte A der Tabelle 1
als Datum formatiert. Der Nachteil ist, dass der Punkt zwischen Tag und Monat mit eingegeben werden muß, was ich durch die Formatierung: Benutzerdefiniert 00\.00) vermeiden wollte.
Da EXCEL Daten mit der Formatierung = Datum intern mit einer fortlaufenden ganzen Zahl speichert (ausgehend vom 1.1.1900 gleich 1 entspricht der 14.12.2005 dem Wert 38700) habe ich diese Zahl bei der Verkettung des Suchkriteriums für die Tabelle 2 verwendet. In der Spalte A der Tabelle 2 (Format: Standard) habe ich ebenfalls diese fortlaufende Zahl als Schlüsselargument gespeichert. Dadurch war die Übereinstimmung beim SVERWEIS in allen Fällen gegeben und das Problem mit den führenden Nullen war keines mehr.

Gruss hanswerner