sverweis() mit dynamischem Spaltenindex möglich?
Richard Ohl
Ich benötige ein wenig Hilfe. Ich habe eine Matrix, die per ODBC aus einer
Datenbank geliefert wird. Ich habe senkrecht Namen, waagerecht Daten zu
Namen. Diese Daten benötige ich in verschiedenen anderen Blättern. Die erste
Zeile der Matrix enthält Überschriften.
Bsp:
A B C
1 name first last
2 K. Meier Klaus Meier
3 H. Peter Hans Peter
Der aktuelle Sverweis auf Hans Peters Nachname sieht jetzt also so aus:
sverweis("H. Peter"; crewData; 3; falsch)
Das Problem ist, dass die Überschriften sich durch externe Änderung der
DB-Abfrage ändern können (müssen). In der dritten Spalte könnte dann also
"birhday" als Überschrift stehen, "last" wäre dann in der 4. Spalte. Gibt es
eine Möglichkeit, die Zahl 3 in meinem sverweis durch einen Bezug zu
ersetzen, den ich irgendwie bekomme? Ich habe es mit wverweis bereits so
probiert:
sverweis("H. Peter"; crewData; indirekt(WVERWEIS("last"; crewData; 1;
FALSCH); falsch) - das liefert aber #Bezug!
Kann mir jemand helfen? Vielen Dank!
Richard
--
Toleranz ist der Verdacht, daß der andere Recht hat. (Kurt Tucholsky)
Claus Busch
Am Fri, 1 Jun 2007 12:59:28 +0200 schrieb Richard Ohl:
> Ich benötige ein wenig Hilfe. Ich habe eine Matrix, die per ODBC aus einer
> Datenbank geliefert wird. Ich habe senkrecht Namen, waagerecht Daten zu
> Namen. Diese Daten benötige ich in verschiedenen anderen Blättern. Die erste
> Zeile der Matrix enthält Überschriften.
> Bsp:
>
> A B C
> 1 name first last
> 2 K. Meier Klaus Meier
> 3 H. Peter Hans Peter
>
> Der aktuelle Sverweis auf Hans Peters Nachname sieht jetzt also so aus:
> sverweis("H. Peter"; crewData; 3; falsch)
>
> Das Problem ist, dass die Überschriften sich durch externe Änderung der
> DB-Abfrage ändern können (müssen). In der dritten Spalte könnte dann also
> "birhday" als Überschrift stehen, "last" wäre dann in der 4. Spalte. Gibt es
> eine Möglichkeit, die Zahl 3 in meinem sverweis durch einen Bezug zu
> ersetzen, den ich irgendwie bekomme? Ich habe es mit wverweis bereits so
> probiert:
> sverweis("H. Peter"; crewData; indirekt(WVERWEIS("last"; crewData; 1;
> FALSCH); falsch) - das liefert aber #Bezug!
probiers mal so:
=SVERWEIS("H. Peter"; crewData; VERGLEICH("last";1:1;);)
--
Mit freundlichen Grüssen
Claus Busch
Win XP Prof SP2; Office 2000 SP3
Richard Ohl
> Richard Ohl:
>> [...] Gibt es
>> eine Möglichkeit, die Zahl 3 in meinem sverweis durch einen Bezug zu
>> ersetzen, den ich irgendwie bekomme?
>
> probiers mal so:
> =SVERWEIS("H. Peter"; crewData; VERGLEICH("last";1:1;);)
Hm, das funktioniert so noch nicht. Woher bekommt VERGLEICH denn die Info,
in der ersten Zeile von crewData zu suchen? Die Matrix befindet sich ja auf
einem anderen Blatt als die Ausgabe/Formel. Gibt es eine Möglichkeit, eine
einzelen Zeile einer Matrix zu referenzieren? Also etwa so:
=SVERWEIS(..;..; VERGLEICH("last"; crewData!1:1;);) ?
Danke für die Hilfe!!
Richard
--
Ein Experte ist jemand, der auf einem begrenzten Wissengebiet einfach schon
mal alle Fehler gemacht hat.
Claus Busch
Am Fri, 1 Jun 2007 13:37:13 +0200 schrieb Richard Ohl:
> Hm, das funktioniert so noch nicht. Woher bekommt VERGLEICH denn die Info,
> in der ersten Zeile von crewData zu suchen? Die Matrix befindet sich ja auf
> einem anderen Blatt als die Ausgabe/Formel. Gibt es eine Möglichkeit, eine
> einzelen Zeile einer Matrix zu referenzieren? Also etwa so:
> =SVERWEIS(..;..; VERGLEICH("last"; crewData!1:1;);) ?
ich dachte crewData wäre ein Bereichsname. Wenn dies aber ein Blattname
ist, würde es so gehen: (Bereiche noch entsprechend anpassen)
=SVERWEIS("H. Peter"; crewData!A1:H1000; VERGLEICH("last";crewData!1:1;);)
Richard Ohl
> ich dachte crewData wäre ein Bereichsname. Wenn dies aber ein Blattname
> ist, würde es so gehen: (Bereiche noch entsprechend anpassen)
> =SVERWEIS("H. Peter"; crewData!A1:H1000; VERGLEICH("last";crewData!1:1;);)
Ja, es ist ein Bereichsname. Er bezieht sich auf einen lokalen Bereich eines
Blattes. Aber crewData ist halt $A$1:$x$y (x, y sind dynamisch).
Ich habe es jetzt so gelöst:
- Namen crewHeader erstellt mit "Bezieht sich auf": crewData!$1$1
Formel:
=SVERWEIS("H. Peter"; crewData; VERGLEICH("last"; crewHeader; FALSCH);
FALSCH)
Natürlich ist das keine sooo schöne Lösung. Die Tipparbeit ist enorm, weil
ich für die Ausgabe noch ein
=WENN(sverweis(...) <> ""; sverweis(...); "n/a") tippen muss... Kann man
irgendwie eine Funktion definieren, die einem die Tipparbeit abnimmt und nur
den Parameter für INDEX erfordert? Die SVERWEIS Suchkriterien sind auch
fest, weil es ein definierter Name aus einer Liste aus. Das vollständige
Beispiel wäre also:
=WENN(SVERWEIS(crewMember; crewData; VERGLEICH("last"; crewHeader; FALSCH);
FALSCH)) <> ""; SVERWEIS(crewMember; crewData; VERGLEICH("last"; crewHeader;
FALSCH); FALSCH)); "---")
^ Das heißt, dass alles, was sich ändert, nur "last" ist. Kann man das also
in eine Funktion packen oder sowas? Ein kleines bisschen VBA kann ich, aber
nicht wirklich viel.
Vielen herzlichen Dank für alle Tips!
Richard
--
function getRandomNumber()
{
return 4; // choosen by fair dice roll. Guaranteed beeing random
}
Alexander Wolff
> Natürlich ist das keine sooo schöne Lösung. Die Tipparbeit ist enorm,
> weil ich für die Ausgabe noch ein
> =WENN(sverweis(...) <> ""; sverweis(...); "n/a") tippen muss... Kann
Wenn das Lookup-Feld garantiert mittels Daten Gültigkeit nur auf die
Lookup-Liste beschränkt wird, brauchst Du diese rechenzeitverdoppelnde
Fehlerbehandlung nicht.
> man irgendwie eine Funktion definieren, die einem die Tipparbeit
> abnimmt und nur den Parameter für INDEX erfordert? Die SVERWEIS
> Suchkriterien sind auch fest, weil es ein definierter Name aus einer
> Liste aus. Das vollständige Beispiel wäre also:
>
> =WENN(SVERWEIS(crewMember; crewData; VERGLEICH("last"; crewHeader;
> FALSCH); FALSCH)) <> ""; SVERWEIS(crewMember; crewData;
> VERGLEICH("last"; crewHeader; FALSCH); FALSCH)); "---")
>
> ^ Das heißt, dass alles, was sich ändert, nur "last" ist. Kann man
> das also in eine Funktion packen oder sowas? Ein kleines bisschen VBA
> kann ich, aber nicht wirklich viel.
VBA wäre genauso die Lösung.
Achte darauf, dass Du mehrfache SVERWEISe nur mit verschiedenen
Lookup-Feldern verwendest. Sollten die hingegen immer gleich sein, schalte
ein VERGLEICH dazwischen (in einer extra Zelle) und verwende das Ergebnis
daraus in einem INDEX. Ggü SVERWEIS wird Dein Blatt vor Erleichterung
aufschreien!
--
Moin+Gruss Alexander - MVP for MS Excel - www.xxcl.de - mso2000sp3 --7-2
Richard Ohl
> Die Tastatur von Richard Ohl wurde wie folgt gedrückt:
>> Natürlich ist das keine sooo schöne Lösung. Die Tipparbeit ist enorm,
>> weil ich für die Ausgabe noch ein
>> =WENN(sverweis(...) <> ""; sverweis(...); "n/a") tippen muss... Kann
>
> Wenn das Lookup-Feld garantiert mittels Daten Gültigkeit nur auf die
> Lookup-Liste beschränkt wird, brauchst Du diese rechenzeitverdoppelnde
> Fehlerbehandlung nicht.
Ähm, die Prüfung ist ja nur, um "n/a" anzuzeigen, wenn der Wert in der
Matrix leer ist.
>> Das heißt, dass alles, was sich ändert, nur "last" ist. Kann man
>> das also in eine Funktion packen oder sowas? Ein kleines bisschen VBA
>> kann ich, aber nicht wirklich viel.
>
> VBA wäre genauso die Lösung.
>
> Achte darauf, dass Du mehrfache SVERWEISe nur mit verschiedenen
> Lookup-Feldern verwendest. Sollten die hingegen immer gleich sein, schalte
> ein VERGLEICH dazwischen (in einer extra Zelle) und verwende das Ergebnis
> daraus in einem INDEX. Ggü SVERWEIS wird Dein Blatt vor Erleichterung
> aufschreien!
Hm, geschrien hat es nicht, es war nur langsam... ;-) Aber der
Vollständigkeit halber und für die Suchmaschinen will ich mal beschreiben,
wie ich es dank eurer Hilfe lösen konnte:
- Matrix "crew": cdata!getCrew (ist ein Name mit Referenz auf Bereich
getCrew/Blatt cdata)
- dann habe ich zusätzlich zum dynamischen Namen "crew" einen Namen cheader
erstellt, der sich auf den Bereich "=BEREICH.VERSCHIEBEN(crew; 0; 0; 1;)"
bezieht (horizontal).
- ^ ebenso einen benannten Bereich für die Namen in der Matrix (vertikal),
also Name crewName = "=BEREICH.VERSCHIEBEN(crew; 0; 0; ;1)"
- Auf der Auswertungsseite habe ich nun ein Feld mit dem Namen crewID
erstellt, mit der Formel =VERGLEICH(crewMember; crewName; FALSCH), die mir
nun den Zeilenindex der Ursprungsmatrix zurückgibt.
Wenn ich nun also die Telefonnummer des in der Liste ausgewählten
Crewmitglieds ausgeben will, benutze ich:
=INDEX(crew;crewID;VERGLEICH("name";cheader;FALSCH))
Ich mag gern zugeben, dass mir das schon sehr gut gefällt. Nun fehlt nur
noch, ein "n/a" auszugeben, wenn die Rückgabe von INDEX = "" ist. Kann ich
das ohne WENN bzw. zumindest so erreichen, dass ich die INDEX Formel nicht 2
mal schreiben muss?
Vielen Dank für eure Hilfe, wieder viel gelernt!
Richard
--
All truth passes through three stages. First, it is ridiculed.
Second, it is violently opposed. Third, it is accepted as being
self-evident. -- Arthur Schopenhauer
Richard Ohl
> Alexander Wolff tastaturte dies:
>> Die Tastatur von Richard Ohl wurde wie folgt gedrückt:
>>> Natürlich ist das keine sooo schöne Lösung. Die Tipparbeit ist enorm,
>>> weil ich für die Ausgabe noch ein
>>> =WENN(sverweis(...) <> ""; sverweis(...); "n/a") tippen muss... Kann
>> Wenn das Lookup-Feld garantiert mittels Daten Gültigkeit nur auf die
>> Lookup-Liste beschränkt wird, brauchst Du diese rechenzeitverdoppelnde
>> Fehlerbehandlung nicht.
> Ähm, die Prüfung ist ja nur, um "n/a" anzuzeigen, wenn der Wert in der
> Matrix leer ist.
Ich nochmal! Nun habe ich dank Alexander und Claus ja tüchtig Rechenzeit
gespart und die Arbeit immerhin vereinfacht. Gibt es jetzt vielleicht noch
eine kürzere Schreibweise der folgenden Formel?
=WENN(INDEX(crew; crewID; VERGLEICH("name"; cheader; 0)) <> ""; INDEX(crew;
crewID; VERGLEICH("name"; cheader; 0)); "n/a")
Mich ärgert die Wiederholung der beiden "Index" Formeln. Es geht ja nur
darum, "n/a" in das Feld zu schreiben, wenn der Wert aus der Matrix leer
ist. Kann noch mal jemand helfen? Oder gibt es gar eine Möglichkeit, die
Formel so in VBA unterzubringen, dass ich im Feld nur "=crewLookup("name")"
anzugeben bräuchte? Die restlichen Werte in der Formel sind definierte
Namen, die sich nicht ändern.
Danke für alle Tips!
Alexander Wolff
eine extra Zelle für die Berechnung (das mögen viele nicht, ist aber
zumindest schneller, als die Wiederholungsarie), oder Du formatierst die
Zelle mit Standard;Standard;"n/a" oder #.##0,00;-#.##0,00;"n/a"
--
Moin+Gruss Alexander - MVP für MS Excel - www.xxcl.de - mso2000sp3 --7-2
Richard Ohl
> [...] oder Du formatierst die
> Zelle mit Standard;Standard;"n/a" oder #.##0,00;-#.##0,00;"n/a"
Die Idee fand ich super, suche mich in der Hilfe nach den
benutzerdefinierten Formaten tot. Ich habe Standard;Standard;"n/a"
ausprobiert. Bei mir bleibt die Zelle, wenn der Inhalt leer ist, auch leer.
Ich habe dann einfach mal Standard;Standard;"n/a";"n/a" probiert, hier steht
dann "n/a" in der Zelle, wenn sie zumindest ein Leerzeichen enthält. Im
Grunde ist das aber exakt das, wonach ich suche - nur leider bekomme ich es
nicht hin... Magst nochmal helfen? Bei den Wiederholungen stimme ich dir
nämlich vorbehaltlos zu!
Danke und Gruß
Claus Busch
Am Sat, 2 Jun 2007 21:50:18 +0200 schrieb Richard Ohl:
> Die Idee fand ich super, suche mich in der Hilfe nach den
> benutzerdefinierten Formaten tot. Ich habe Standard;Standard;"n/a"
> ausprobiert. Bei mir bleibt die Zelle, wenn der Inhalt leer ist, auch leer.
> Ich habe dann einfach mal Standard;Standard;"n/a";"n/a" probiert, hier steht
> dann "n/a" in der Zelle, wenn sie zumindest ein Leerzeichen enthält. Im
> Grunde ist das aber exakt das, wonach ich suche - nur leider bekomme ich es
> nicht hin... Magst nochmal helfen? Bei den Wiederholungen stimme ich dir
> nämlich vorbehaltlos zu!
diese beschriebenen Formate sind Zahlenformate. Dein SVERWEIS liefert dir
aber doch Namen, soweit ich dies aus dem vorherigen Posting verstanden
habe. Es bleibt also eine Hilfsspalte oder die Wiederholung.
Du könntest aber einfach deine SVERWEIS-Formel eingeben. Wenn diese dann
keine Übereinstimmung liefert, bekommst du ein #NV. Und dies könntest du
doch über VBA in n/a umwandeln. Angenommen deine Daten stehen in der Spalte
A und deine Formeln kommen in Spalte B:F. Dann in ein Modul z.B.:
Sub Test()
Dim rngZelle As Range
For Each rngZelle In Range("B1:F500")
If IsError(rngZelle.Value) Then
rngZelle.Value = "n/a"
End If
Next
End Sub
und in das Codemodul deines Tabellenblattes:
Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Range("A:F")) Is Nothing _
Then Exit Sub
Test
End Sub
Die Bezüge müsstest du deinen Daten entsprechend anpassen.
Alexander Wolff
Falls Du xl2007 nutzst: Es gibt dort ein solches modifiziertes WENN, meine
ich gehört zu haben.
Und falls Du eine Vorgängerversion verwendest:
Function NAwennleer(Formel)
If Formel = "" Then Formel = "n/a"
NAWennleer = Formel
End Function
(ungetestet)
Thomas Ramel
Richard Ohl schrieb am 01.06.2007
> Ich nochmal! Nun habe ich dank Alexander und Claus ja tüchtig Rechenzeit
> gespart und die Arbeit immerhin vereinfacht. Gibt es jetzt vielleicht noch
> eine kürzere Schreibweise der folgenden Formel?
> =WENN(INDEX(crew; crewID; VERGLEICH("name"; cheader; 0)) <> ""; INDEX(crew;
> crewID; VERGLEICH("name"; cheader; 0)); "n/a")
>
> Mich ärgert die Wiederholung der beiden "Index" Formeln. Es geht ja nur
> darum, "n/a" in das Feld zu schreiben, wenn der Wert aus der Matrix leer
> ist. Kann noch mal jemand helfen?
Bis xl2003 gibt es keine (eingebaute) Alternative zu deiner Schreibweise.
Doch Du könntest die folgende Benutzerdefinierte Funktion verwenden, die
das doppelte Eingeben deiner Funktion unnötig macht und der du auch gleich
einen Rückgabewert im Falle eines Fehlers mitgeben kannst:
Function WennFehler(varFormel As Variant, _
Optional varFehler As Variant) As Variant
'© t.r...@mvps.org / 07.04.2005 / 03.06.2007
'Funktion zur Vermeindung doppelter Aufrufe von z.B. SVERWEIS()
If Not IsError(varFormel) Then
WennFehler = varFormel
Else
If IsMissing(varFehler) Then
WennFehler = varFormel
Else
WennFehler = varFehler
End If
End If
End Function
Wenn Du der Funktion keinen Fehlerwert mitgibst liefert sie den zurück, den
die Funktion selbst auslöst. Anwenden in der Tabelle dann wie folgt:
=WennFehler(DeineFormel;"Dein Fehlerwert")
für dich konkret also:
=WennFehler(INDEX(crew; crewID; VERGLEICH("name"; cheader; 0)); "n/a")
Mit freundlichen Grüssen
Thomas Ramel
--
- MVP für Microsoft-Excel -
[Win XP Pro SP-2 / xl2003 SP-2]
Microsoft Excel - Die ExpertenTipps