PDA

Vollständige Version anzeigen : Alternative / How to do SVerweis


RUK
12.02.2006, 17:32
Hi Forum,

mit den Matrixfunktionalitäten bekomme ich das iregendwie nicht gebacken. Folgendes Problem:
1. In meinem Worksheet "Main" habe ich einen Wert in Spalte B, der aus einer Matrix des Worksheets "Slave" einen Wert "holen" soll.
2. Slave KANN NICHT geändert werden [aufsteigend geordnet werden].
3. Slave sieht verkürzt in etwa so aus:
[SpalteB] [SpalteC] [SpalteD]

Titel Nummer(n) Gesamt- Erhöhung in v. H.
______________________________________________
ZN99 66001801 0,00 %
WXN 66001853 6,90 %
NZN 66001803 2,59 %
NW 66000807 2,59 %

4. Ich möchte nun jeweils auf den Wert aus der Spalte D verweisen, wobei ich über die Spalte B zwischen "Main" und "Slave" verknüpfe.

Wenn ich mit SVerweis herangehe, bekomme ich entweder #NV- Werte oder falsche Werte.

Könnt Ihr mir einen Tipp geben, welche Funktion ich verwenden müsste und wie die Syntax aussieht ?

Vielen Dank & Gruß,
Rudolf

jinx
12.02.2006, 17:44
<font size="2" face="Century Gothic">Moin, Rudolf,

auf Basis von Sverweisfunktion nach links (http://www.excelformeln.de/formeln.html?welcher=24) oder per SVERWEIS (at least as long as the items are unique):<table><tr style="vertical-align:top; text-align:center; "><tr><td>Master</td></tr><tr><td><table border=1 cellspacing=0 cellpadding=0 style="font-family:Arial,Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "> <style type = "text/css"> th {font-weight:normal} </style> <colgroup><col width=30 style="font-weight:bold; "><col width=79.999998 ><col width=79.999998 ><col width=79.999998 ></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="text-align:right; ">66001803</td><td style="text-align:right; ">0,0259</td><td style="text-align:right; ">0,0259</td></tr></table><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#FFFCF9; "><tr><td><b>Formeln der Tabelle</b></td></tr><tr><td><table style="font-family:Arial; font-size:10pt;">B1 : =INDEX(Slave!C:C;VERGLEICH<span style=' color:008000; '>(A1;Slave!B:B;0)</span>)<br>C1 : =SVERWEIS(A1;Slave!B:C;2)<br></table></td></tr></table></td></tr><tr><td>&nbsp;</td></tr><tr><td>Slave</td></tr><tr><td><table border=1 cellspacing=0 cellpadding=0 style="font-family:Arial,Arial; font-size:10pt; padding-left:2pt; padding-right:2pt; "> <style type = "text/css"> th {font-weight:normal} </style> <colgroup><col width=30 style="font-weight:bold; "><col width=79.999998 ><col width=79.999998 ><col width=79.999998 ></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td>&nbsp;</td><td>A</td><td>B</td><td>C</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="">ZN99</td><td style="text-align:right; ">66001801</td><td style="text-align:right; ">0%</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td style="">WXN</td><td style="text-align:right; ">66001853</td><td style="text-align:right; ">6,90%</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="">NZN</td><td style="text-align:right; ">66001803</td><td style="text-align:right; ">2,59%</td></tr><tr height=17 ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="">NW</td><td style="text-align:right; ">66000807</td><td style="text-align:right; ">2,59%</td></tr></table></td></tr><tr><td>&nbsp;</td></tr></tr></table></font>

RUK
12.02.2006, 17:54
Moin JINX,

wow; sogar am Sonntag "mailwendend" Hilfe im Forum. GE- NI- AL !!

VIELEN DANK für den Tipp. Werde ich gleich morgen früh testen !
[unser Server fährt leider gleich [18:00] wieder herunter :( ]

Gruß aus Hamburg,
Rudolf

raiho
12.02.2006, 18:16
@jinx
beim sverweis müsste doch, nachdem die matrix nicht sortiert ist, noch eine 0 (falsch)angehängt werden:
=sverweis(A1;Slave!B:C;2;0)

jinx
13.02.2006, 06:27
<font size="2" face="Century Gothic">Moin, Rainer,

die Einlassung stimmt - vielleicht sollte ich mir so langsam mal wieder den SVERWEIS in der Online-Hilfe ansehen... :rolleyes:

Für INDEX/VERGLEICH hingegen ist die Reihenfolge der Daten meines Wissens unerheblich.</font>

RUK
13.02.2006, 10:29
Moin JINX, moin Rainer,

danke für Eure Tipps. Als DIREKT- Eingabe funzt die Index- Variante prima. Mit SVerweis hakt's irgendwie [anyway; da die erste Variante läuft, habe ich mich auf diese konzentriert].

Wenn ich die Formel jetzt aus VB heraus in die Zellen schreibe, kommt es zur "Nr. 1004 - objektdefinierten Fehlermeldung".

Auszug aus meinem Coding:

strText = "INDEX('" & Trim(strDokName) & Str(strJahr) & "'!E:E; " _
& "VERGLEICH(E15;'" & Trim(strDokName) & Str(strJahr) & "'!D:D;0))"
.Cells(j, intAktSpalt + 2).Value = strText
.Cells(j, intAktSpalt + 2).Formula = "=" & .Cells(j, intAktSpalt + 2).Value

wobei ich über
- strText die Zell- Formel übergebe [ich gehe diesen Weg, um mir die Formel ggf. per MsgBox anzeigen lassen zu können]
- j mein interner Zähler für eine for- Next Schleife ist,
- intAktSpalt mein Merker für die aktuelle Spalte ist.

Wenn ich auf das Gleichheitszeichen verzichte, den Zelleninhalt nur mit strText befülle und nachträglich das Gleichheitszeichen ergänze, klappt die Chause wieder. Ich vermute, das liegt - wieder einmal - an der englischen Syntaktik respektive an der "RC- Problematik".

... Nachträgliche Ergänzung ...

Nach einigen TryNErrors bin ich dann zu folgendem Resultat gekommen:

strText = "=INDEX('" & Trim(strDokName) & Str(strJahr) & "'!C[-14], " _
& "MATCH(RC[-" & "14],'" & Trim(strDokName) & Str(strJahr) _
& "'!C[-15],0))"
.Cells(j, intAktSpalt + 2).FormulaR1C1 = strText

und es funktioniert :dance: [wobei sich mir - ehrlich gesagt - die Syntax nicht so wirklich erschließt; insbesondere der C-14 und RC14 Part , sowie der Sachverhalt, dass TATSÄCHLICH auf die Zeile verwiesen wird, die ich <dynamisch> vergleichen möchte :rolleyes:]


Vielen Dank & Gruß
Rudolf

jinx
13.02.2006, 16:38
<font size="2" face="Century Gothic">Moin, Rudolf,

es gibt meines Wissens 5 Möglichkeiten, eine Formel in Zellen zu bekommen: eine falsche (das hast Du ja auch rausgefunden ;)) sowie die Verwendung von Formula, FormulaR1C1, FormulaLocal und FormulaLocalR1C1 (zweiter und vierter Vorschlag arbeiten mit A1-Schreibweise)...</font>

RUK
20.02.2006, 15:10
Moin JINX,

die erste Variante ist meine Spezialität :grinange: . Wenngleich ich mit dem Ergebnis das Klassenziel erreicht habe, ist für mich die Sache noch nicht so wirklich rund [vermutlich hat mich der vor Urzeiten von einem Ausbilder mir mit auf den Weg gegebene Satz "Entweder Sie wissen an einem Computer WAS Sie machen, oder Sie lassen es" geprägt ;)]. Ich hatte jetzt im Forum etwas gestöbert, aber nicht die für mich läuternden Threads gefunden.

Was löst dieses "Minuszeichen" hinter C und RC aus; ist das evtl. der Hinweis auf eine dynamische Zelle ?

Gruß & THX
RUK

jinx
20.02.2006, 16:53
<font size="2" face="Century Gothic">Moin, Rudolf,

die Verschiebung zeilen- oder spaltenweise in die angegebene Richtung (- also nach links oder nach oben, + nach rechts oder nach unten) - jeweils bezogen auf die aktive Zelle:

Aktive Zelle R10C10, Versatz R[-4]C[-4] --> R6C6 (oder, in A1: J10, Offset(-4, -4) --> F6)...</font>

RUK
20.02.2006, 17:32
Moin Jinx,

danke für die Aufschlüsselung. Damit bestätigt sich dann [leider], dass ich schlicht eine "Dusel- Lösung" hatte, weil es mit den auf diese Art gebildeten Formeln eben gerade so hinkam. Also werde ich da dann noch einmal nachlegen.

Gruß & THX,
RUK