Excel Formel SVERWEIS?
Hallo Leute,
schaut Euch mal bitte die Formel in meiner Tabelle an. Wieso wird denn fast überall ein falsches Ergebnis ausgegeben? Wo habe ich denn grad den Denkfehler?
In U6 zB müsste doch jetzt 3,85 ausgegeben werden. 3,75 gibt mir Excel aber zurück. Einige sind richtig, viele aber falsch... :-/
3 Antworten
@gfntom hat hier schon die "perfekte" Antwort geliefert, ich möchte dir hier nur "schnell begründen" warum du hier zu einem falschen Ergebnis kommst.
leider ist das nicht öffentlich dokumentiert, daher muss ich auch ein wenig raten, aber ich kann dir einen ungefähren Ansatz liefern.
Bei dem Parameter WAHR / 1 als letzten Parameter erwartet Excel eine aufsteigend sortierte Liste, die du hier nur teilweise hast. Aber Excel sucht nicht wie man denken könnte Zeile für Zeile sondern eine Binärsuche, laut meiner Recherche scheint der erste Suchwert ca. in der Mitte der Tabelle zu liegen. Welche Berechnungsgrundlage für die Sprung/Schrittweite zu Grunde liegt, wird dir wohl nur Microsoft verraten können.
Schauen wir uns mal exemplarisch Saarbrücken (Zeile 2) als Suchwert an.
Hier sucht Excel als erstes in der Zeile 9 ca. Mitte der Tabelle findet dort aber Hannover.
Hannover ist kleiner als Saarbrücken, also springt es weiter hoch
Zeile 15 -> dort findet es Verl , Verl ist größer als Saarbücken, also springt es wieder zurück z.B. Zeile 12
Zeile 12 -> dort findet Excel Stuttgart Stuttgart ist ebenfalls größer.
Da bereits Zeile 9 durchsucht wurde, kann es nur in 11 oder 10 springen. Scheinbar wählt Excel hier 10 und gibt dies aus, warum das passiert und wie hie die genaue Ermittlung erfolgt kann ich wie gesagt nicht genau sagen nur so ist die Suche in Excel, vielleicht bekommt man das über die OpenSource Varianten von LibreOffice besser heraus, denn da kann ja jeder in den Quellcode schauen.
Ich hoffe dir damit geholfen zu haben.
Grundsätzlich würde ich aber wie gfntom vom SVERWEIS gänzlich abraten, von den VERWEIS Funktionen ist es leider die schlechteste.
Die bessere Alternative ist INDEX+VERGLEICH oder das Optimum XVERGLEICH ist aber eben erst ab Excel 2021 verfügbar Index + Vergleich hingegen schon eine halbe Ewigkeit wenn ich raten müsste, würde ich sagen Excel 1997.
Ich verwende SVERWEIS aus verschiedenen Gründen nicht, weil einfach zu unflexibel.
Aber:
Versuche als letzten Parameter von SVERWEIS den Wert FALSCH zu ergänzen. (also ...;5; FALSCH))
Wenn du WAHR (oder nichts) eingibst, "begnügt" sich SVERWEIS mit "ähnlichen" Werten.
Hm, sonderbar.
Häufige Fehler bei SVERWEIS:
- Ein Suchbegriff ist an einer oder der anderen Stelle anders geschrieben, z.B. mit einem führenden oder nachstehenden Leerzeichen. Daher solltest Du in der Formel nach der 5 noch einen Parameter FALSCH einfügen, also" ...;5;FALSCH)". Dann findet er nur absolut identische Übereinstimmung.
- Der Suchbereich ist nicht festgezurrt. Du gibst doch bestimmt oben die Formel ein, fixierst dann in der Formel durch Dollarzeichen die jeweiligen Spalten und/oder Zeilen, und ziehst die Formel dann herunter. Warum ist bei Dir ohne Fixierungen erst in der 5. Zeile die gewünschte Formel drin?
@tevau
Tatsache, mit dem "FALSCH" passt es dann. Aber trotzdem kurios, da ja die Suchparameter eigentlich in der Tabelle drin stehen und dementsprechend auch bei "WAHR" die korrekten Werte ausgegeben werden müssten.
Die $ habe ich nacträglich weg genommen weil ich die Formel an anderer Stelle noch kopiere und dort dann der Suchbereich ein anderer ist. ;)