Office - Word, Excel und Co. 9.753 Themen, 41.597 Beiträge

Verfolgung starten Optionen

Excel 2002 - VLOOKUP basierend auf drop-down Liste - Problem

ainstein / 12 Antworten / Baumansicht Nickles

Hallo, ich habe ein kleines Problem...

Zwei Blätter, auf dem ersten ("work-time analysed") wird Arbeitszeit mit Stundenlohn zu Gehalt pro Tag verarbeitet.
Auf dem zweiten Blatt ("staff&rates") befinden sich Namen und Sundenlohn:

A B C D E F
2 20%
4 Ms Beth Ballard £6.50 =B4&" "&C4 =D4-D4*$D$2

Der Name [A4] in "work-time analysed" ist eine Verküpfung zum zweiten Blatt ['staff&rates'!E4], wo die Zellen [B4:C4] verkettet sind.
Dazu habe ich alle Namen [E4:E28] in eine Auswahlliste gesetzt, damit die richtige Rechtschreibung für das VLOOKUP gegeben ist.

Der Stundenlohn ['work-time analysed'!B4] soll neben dem Namen durch ein VLOOKUP von "staff&rates" aus den Zellen [G4:H28] eingesetzt werden.

A B C
4 Name =VLOOKUP(A4,'staff&rates'!$E$4:$F$28,2) ... rest der Gehaltsverrechning

[A4] (Name) = Data Validation drop-down list referring to ['staff&rates'!E4:E28]

Leider lässt sich das Problem sich nicht richtig beschreiben, ohne die Tabelle einzusehen. Teilweise funzt das VLOOKUP, aber dann eben nicht mehr, und in einem Fall resultiert es sogar in einem #N/A Fehler - mitten in der Liste, wee es danach wider funktioniert (?)

Ich hoffe, dass ich mit diesem Link (http://www.itcomes2u.co.uk/files/Staff_wages_dev.xls) die Tabelle eingebunden habe.

Vielen Dank im Voraus für jeglichen Vorschlag.

Gruß, Stefan

\"Eine Sache ist nur verloren, wenn man sie aufgibt.\" (Freiherr von Stein)
bei Antwort benachrichtigen
ainstein Nachtrag zu: „Excel 2002 - VLOOKUP basierend auf drop-down Liste - Problem“
Optionen

In case anyone has a similar problem, the issue is the 4th item in the formula, should say FALSE, i.e.:

=VLOOKUP(A4,'staff&rates'!$E$4:$F$28,2,FALSE)

For some reason it works very well then. Although I acan not see a reason, why it should not work otherwise.  Anyway, problem solved now.

Source of answer: http://www.tek-tips.com/viewthread.cfm?qid=1253790.

I still prefer to consult nickles.de, as it's a forum/board, where users provide constructive support.  Only recently found the above (www.tek-tips.com) for English speaking context.

Thanks again for your thoughts.

Regards,

Stefan

\"Eine Sache ist nur verloren, wenn man sie aufgibt.\" (Freiherr von Stein)
bei Antwort benachrichtigen
Olaf19 ainstein „In case anyone has a similar problem, the issue is the 4th ...“
Optionen

Hi Stefan + alle,

Nur für den Fall, dass Stefans englischsprachiges Posting oder auch schon das "VLOOKUP" im Eröffnungsposting für Stirnrunzeln oder Verwirrung gesorgt haben sollte:

VLOOKUP ist nicht mehr und nicht weniger als der gute alte SVERWEIS. Allerdings weicht die Syntax in der englischsprachigen Version ein wenig von unserer gewohnten ab:

VLOOKUP(A4,'staff&rates'!$E$4:$F$28,2)

...wäre im Deutschen: SVERWEIS(A4;'staff&rates'!$E$4:$F$28;2) - also mit Semikola statt Kommata! Hintergrund: im Deutschen taugt das Komma nicht zur Abgrenzung, weil man es mit dem Dezimalkomma in Fließkommazahlen verwechseln könnte. Deswegen steht bei uns ein Semikolon, wo im Englischen ein Komma steht. Da im angelsächsischen Sprachraum ein Punkt zur Trennung von Ganzzahl- und Nachkommastellen genutzt wird, kann das Komma hier zur Trennung von Parametern eingesetzt werden.

Ein Augenmerk ist auf das Ende der Formel zu richten: ,2) oder deutsch ;2) - wenn man es so schreibt, sucht der SVERWEIS resp. VLOOKUP nur nach ungefähren Übereinstimmungen. Manchmal ist das so gewollt - ich suche nach 22000 und bin mit 22222 zufrieden.

Wird aber die genaue Übereinstimmung gesucht, muss die Formel vor der letzten Klammer wiederum mit einem Trennzeichen abgeschlossen werden, also ,2,) oder deutsch ;2;) - jetzt führt ein Wert von 22222 nicht zum Erfolg, wenn man nach 22000 gesucht hat.- Oder man ergänzt nach dem letzten Komma/Semikolon noch das Binärflag:

  • TRUE bzw. WAHR (suche nach ungefährer Übereinstimmung) oder
  • FALSE bzw. FALSCH (suche nach genauer Übereinstimmung).

Leider ist das nocht nicht die Fehlerursache... ich melde mich noch einmal, falls ich etwas finde. Mysteriös, denn der Name, die Schreibweise und der SVERWEIS / VLOOKUP für Mitarbeiter Andy unterscheidet sich zunächst in nichts von den anderen.

CU
Olaf

Die Welt ist ein Jammertal ohne Musik. Doch zum Glueck gab es Bach, Beethoven, Haendel und Goethe (Helge Schneider)
bei Antwort benachrichtigen
Olaf19 Nachtrag zu: „Hi Stefan alle, Nur für den Fall, dass Stefans ...“
Optionen

Anscheinend hat es doch etwas mit dem Fehler zu tun!

Ich habe einmal spaßeshalber Fantasienamen wie "Olaf Neunzehn" eingegeben - obwohl die nicht in der Liste auftauchen, wird ein Stundenlohn ausgegeben, in "meinem" Fall 4 £.

Schlimmer noch: Gibt man den vollständigen Namen von Kim W. ein, in der Liste zwei Positionen unterhalb von Andy, so erhält man einen falschen Wert, nämlich 5,60 £ anstelle von 5,20 £.

Kleine Schwierigkeit: ich sitze hier vor einer deutschen OpenOffice-Version, du hingegen hast die Tabelle mit einer englischen Microsoft Office-Version erstellt. Deswegen komme ich an dieser Stelle nicht so recht weiter.

Ich kann dir daher vorläufig nur den Tipp geben, den VLOOKUP noch einmal neu aufzubauen und diesmal den Parameter ganz am Schluss vor der letzten Klammer – ungefähre Übereinstimmung = TRUE, genaue Übereinstimmung = FALSE – in deine Überlegungen mit einzubeziehen. Bei Personennamen kommt es immer auf absolute Genauigkeit an!

Wenn du also einen John Covington und einen John Doodle in deiner Liste hast, dann darf Excel keinesfalls zu dem Schluss kommen: och, der Vorname passt ja, dann wird es mit dem Stundenlohn schon stimmen. Hier ist Genauigkeit gefragt! Der letzte Parameter muss also immer auf FALSE stehen, oder du schließt einfach vor der letzten Klammer mit einem Komma ab (bzw. in der deutschen Version mit einem Semikolon).

CU
Olaf

Die Welt ist ein Jammertal ohne Musik. Doch zum Glueck gab es Bach, Beethoven, Haendel und Goethe (Helge Schneider)
bei Antwort benachrichtigen
ainstein Olaf19 „Hi Stefan alle, Nur für den Fall, dass Stefans ...“
Optionen

Hallo Olaf,

Danke, das mit dem Komma und Strichpunkt war mir bekannt.  Ich dachte halt weil das Programm sonst immer alles vom Englischen übernimmt, dass VLOOKUP auch im Deutschen als solches gehandhabt wird.

Ich habe leider keine Zugriffsmöglichkeit auf ein deutsches Excel, so dass ich die entsprechenden Befehle nicht übersetzen kann. (tippe aber auf einem deutsche Keyboard)

Vielen Dank nochmal.

Regards,

Stefan

\"Eine Sache ist nur verloren, wenn man sie aufgibt.\" (Freiherr von Stein)
bei Antwort benachrichtigen
Olaf19 ainstein „Hallo Olaf, Danke, das mit dem Komma und Strichpunkt war mir ...“
Optionen
Ich dachte halt weil das Programm sonst immer alles vom Englischen übernimmt, dass VLOOKUP auch im Deutschen als solches gehandhabt wird.

Oh, durchaus nicht. Z.B. ROUND, ROUNDUP, ROUNDDOWN heißt im Deutschen: RUNDEN, AUFRUNDEN, ABRUNDEN ;-)

Hast du dich einmal mit dem Binärflag vor der End-Klammer beschäftigt? Macht es einen Unterschied, ob du das auf TRUE oder FALSE setzt?

See youOlaf

Die Welt ist ein Jammertal ohne Musik. Doch zum Glueck gab es Bach, Beethoven, Haendel und Goethe (Helge Schneider)
bei Antwort benachrichtigen
gelöscht_211816 Olaf19 „Oh, durchaus nicht. Z.B. ROUND, ROUNDUP, ROUNDDOWN heißt ...“
Optionen

Übrigens übersetzt Office die Funktionsnamen engl.Deutsch und umgekehrt. Das Beispiel ist halt bei mir sofort mit den deutschen Funktionsnamen angezeigt.
Wenn ich mal etwas in OpenOffice mache, suche ich den entsprechenden englischen  Funtionsnamen auf einer Internetseite - wenn die Tabelle grad nicht kopiert werden kann.

Gruß Annegret

bei Antwort benachrichtigen
Olaf19 gelöscht_211816 „Übrigens übersetzt Office die Funktionsnamen engl.Deutsch ...“
Optionen
Wenn ich mal etwas in OpenOffice mache, suche ich den entsprechenden englischen  Funtionsnamen auf einer Internetseite - wenn die Tabelle grad nicht kopiert werden kann.

Wobei OpenOffice ja in jedem Fall das englische Sprachpaket mit an Bord hat, so dass du jederzeit zwischen beiden wechseln kannst.

Wenn du in deinem deutsch lokalisierten Office eine Datei öffnest, die mit einem englischsprachigen Office erstellst wurde, so werden dir alle Befehle auf deutsch angezeigt. Office-intern sind die ja identisch codiert, der Unterschied besteht nur darin, was dir angezeigt wird, abhängig von der Lokalisation.

CU
Olaf

Die Welt ist ein Jammertal ohne Musik. Doch zum Glueck gab es Bach, Beethoven, Haendel und Goethe (Helge Schneider)
bei Antwort benachrichtigen
ainstein Olaf19 „Oh, durchaus nicht. Z.B. ROUND, ROUNDUP, ROUNDDOWN heißt ...“
Optionen

Ja, das hat geklappt.  Ich habe es auf FALSE gesetzt und jetzt funktioniert alles bestens.

Dank' schön noch mal.

Gruß,

Stefan

\"Eine Sache ist nur verloren, wenn man sie aufgibt.\" (Freiherr von Stein)
bei Antwort benachrichtigen
Olaf19 ainstein „Ja, das hat geklappt. Ich habe es auf FALSE gesetzt und ...“
Optionen

Hi Ainstein, danke für deine Rückmeldung.

Auch ich habe etwas dabei gelernt: nämlich dass dieser "Toleranz-Parameter", das TRUE / FALSE für die Suche nach ungefährer/genauer Übereinstimmung, dazu führen kann, wenn man ihn auf TRUE setzt, dass ein Wert nicht gefunden wird. Das finde ich erstaunlich. Erwartet hätte ich, dass wenn ein Wert exakt vorhanden ist, dass er dann auch bei Suche nach nur ungefährer Übereinstimmung erst recht gefunden werden muss. Das ist anscheinend nicht der Fall.

Das bestärkt mich nur darin, auch in Zukunft immer nur mit FALSE zu arbeiten - also den Parameter am Schluss einfach wegzulassen, indem ich mit ";)" (Semikolon vor letzter Klammer) Ende.

Na dann noch viel Spaß mit den netten Engländern und Engländerinnen, auf dass sie alle immer rechtzeitig ihre Pfunde bekommen :-)

Cheers
Olaf

Die Welt ist ein Jammertal ohne Musik. Doch zum Glueck gab es Bach, Beethoven, Haendel und Goethe (Helge Schneider)
bei Antwort benachrichtigen
neanderix Olaf19 „Hi Ainstein, danke für deine Rückmeldung. Auch ich habe ...“
Optionen
Das bestärkt mich nur darin, auch in Zukunft immer nur mit FALSE zu arbeiten - also den Parameter am Schluss einfach wegzulassen, indem ich mit ";)" (Semikolon vor letzter Klammer) Ende.

Ich habe mir angewöhnt, auch Default-parameter grundsätzlich explizit anzugeben.

Das macht Formeln/Sourcecode lesbarer, besonders, wenn man längere zeit nicht daran gearbeitet hat und dann wieder reinguckt.

Computers are like airconditioners - they stop working properly when you open Windows Ich bin unschuldig, ich habe sie nicht gewählt!
bei Antwort benachrichtigen
Olaf19 neanderix „Ich habe mir angewöhnt, auch Default-parameter ...“
Optionen
Ich habe mir angewöhnt, auch Default-parameter grundsätzlich explizit anzugeben.

Mir ist es mittlerweile derart vertraut, dass meine SVERWEISE immer mit einem Smiley enden – ;) – dass ich mir kaum noch vorstellen kann, das FALSE einmal voll auszuschreiben. Rein formal wäre das sicherlich korrekter, wobei:

Das macht Formeln/Sourcecode lesbarer, besonders, wenn man längere zeit nicht daran gearbeitet hat und dann wieder reinguckt.

Das klingt jetzt aber schon "schwer" nach Programmierung (VBA?). Wenn man richtig am Coden ist, muss man natürlich schon sehr auf gute Strukturierung und Lesbarkeit achten. Diese irrwitzig verschachtelten Einzeiler-Befehle sind aber von Natur aus eh nie richtig lesbar, sozusagen broken by design, da kommt es auf den unfreiwilligen Smiley am Ende IMHO auch nicht mehr an.

CU
Olaf

Die Welt ist ein Jammertal ohne Musik. Doch zum Glueck gab es Bach, Beethoven, Haendel und Goethe (Helge Schneider)
bei Antwort benachrichtigen
gelöscht_211816 ainstein „Hallo Olaf, Danke, das mit dem Komma und Strichpunkt war mir ...“
Optionen

Hallo ainstein,
in EXCEL 2013 funktioniert des VLOOKUP (bzw. SVERWEIS auf Deutsch) in der Tabelle ganz einwandfrei, wenn die Formel wie oben von Olaf (12.11.2014, 13:5) bearbeitet wird.

  • TRUE bzw. WAHR (suche nach ungefährer Übereinstimmung) oder
  • FALSE bzw. FALSCH (suche nach genauer Übereinstimmung).

also

S=VLOOKUP(A4,'staff&rates'!$E$4:$F$28,2,FALSE)

Steht so in Ihrem 2., enlischen Posting, aber fehlt in der Tabelle 'work-time analysed' Spalte B!

Allerdings gibt es den Fehler #N/A bei ''Claire Moon"  am Ende in dem Tabellenblatt 'work-time analysed' - die Dame gibt es nicht. (Claire Mundy?)

Für Fehlerbehandlung kann man ISNA(..) benutzen (deutsch ISTNV). Die ist ein bißchen lang, aber sehr nützlich, um mehr Informationen zu geben oder einen Kommentar einzufügen.

Um Funktionen zu verwenden, die einem nicht so geläufig sind, läßt sich ein Klick auf das Funktionssymbol  in der Zeile Oben mit der Formel gut nutzen. So kann jedes Argument eingegeben oder ausgewählt werden.

Viel Erfolg! wünscht Annegret

bei Antwort benachrichtigen