Sverweis: Reihenfolge entscheidend?

[Jörg Reutershan]

Habe folgendes Problem:

Mit Sverweis soll der aktuelle Tag mit einer Liste von Feiertagen u.ä.
verglichen werden. Bei Übereinstimmung mit einem Eintrag der Liste sollen
entsprechende Arbeitszeiten definiert werden. So weit so gut.

Ich habe jetzt in die Liste nachträglich zwei Tage eingefügt, und zwar
Beginn von Sommer- und Winterzeit. Wenn jetzt das Datum des
Sommerzeitanfangs vor Ostern liegt, der Eintrag in der Liste aber unter den
Osterfeiertagen liegt, wird der Beginn der Sommerzeit nicht erkannt.

Bsp:

29.03.2003 Beginn Sommerzeit
18.04.2003 Karfreitag
21.04.2003 Ostermontag

So wird der 29. korrekt erkannt. Wenn die Liste aber so aussieht:

18.04.2003 Karfreitag
21.04.2003 Ostermontag
29.03.2003 Beginn Sommerzeit,

wird die Sommerzeit nicht erkannt.

Offensichtlich muss diese Sverweis-Tabelle eine chronologische Reihenfolge
einhalten. Macht aber irgendwie für mich in diesem Fall keinen Sinn, da der
Beginn der Somerzeit ja auch mal nach Ostern sein könnte (oder?).

Hat das jetzt irgendwer verstanden und kann mir eine Erklärung geben?

Vielen Dank,

Jörg

[Philipp von Wartburg]

Hallo Jörg

Damit SVERWEIS funktioniert, muss die erste Spalte der Matrix in
aufsteigender Reihenfolge sortiert sein (vergleiche auch Online-Hilfe
Thema "SVERWEIS").

Wenn die erste Spalte aus irgend welchen Gründen nicht sortiert
ist bzw. sein darf, kann man eine Umgehungslösung mit INDEX
und VERGLEICH verwenden. Hier findest du die Anleitung
dazu:
"Durchführen einer Suche mit unsortierten Daten in Excel"
http://support.microsoft.com/?scid=kb;de;181212

Gruss
Philipp

Jörg Reutershan schrieb in Nachricht ...

[Thomas Ramel]

Grüezi Jörg

Jörg Reutershan schrieb:

>
> Mit Sverweis soll der aktuelle Tag mit einer Liste von Feiertagen u.ä.
> verglichen werden. Bei Übereinstimmung mit einem Eintrag der Liste sollen
> entsprechende Arbeitszeiten definiert werden. So weit so gut.
>
> Ich habe jetzt in die Liste nachträglich zwei Tage eingefügt, und zwar
> Beginn von Sommer- und Winterzeit. Wenn jetzt das Datum des
> Sommerzeitanfangs vor Ostern liegt, der Eintrag in der Liste aber unter den
> Osterfeiertagen liegt, wird der Beginn der Sommerzeit nicht erkannt.
>

> Offensichtlich muss diese Sverweis-Tabelle eine chronologische Reihenfolge
> einhalten. Macht aber irgendwie für mich in diesem Fall keinen Sinn, da der
> Beginn der Somerzeit ja auch mal nach Ostern sein könnte (oder?).
>
> Hat das jetzt irgendwer verstanden und kann mir eine Erklärung geben?

Du hat leider nicht die Funktion, wie du sie verwendet hat hier mit
angegeben. Ich vermute aber mal, dass der letzte 4.Parameter nicht gesetzt,
oder WAHR oder 1 ist.

Setzt ihn auf FALSCH oder 0 (Null), dann wird auf eine exakte
Überinstimmung gesucht und das Datum auch entsprechend gefunden.

--
Mit freundlichen Grüssen

Thomas Ramel
- MVP für Microsoft-Excel -

[Jörg Reutershan]

> Du hat leider nicht die Funktion, wie du sie verwendet hat hier mit
> angegeben. Ich vermute aber mal, dass der letzte 4.Parameter nicht
> gesetzt, oder WAHR oder 1 ist.

Stimmt.

> Setzt ihn auf FALSCH oder 0 (Null), dann wird auf eine exakte
> Überinstimmung gesucht und das Datum auch entsprechend gefunden.

Kann ich so nicht nachvollziehen. Wenn ich den Parameter setze, bekomme ich
für den 29.03.2003 die Heiligen Drei Könige angezeigt !?!?
Der 4. Wert hat doch nichts mit der Reihenfolge der Parameter zu tun, oder?

Jörg

[Anton Haumer]

Ist der 4.Wert (Bereich_Verweis) logisch false, wird eine genaue
Übereinstimmung auch bei nicht sortierten Daten gesucht;
wird keine genaue Übereinstimmung gefunden, wird #NV zurückgegeben.
Ist der 4.Wert (Bereich_Verweis) logisch true, wird der größte
Wert der kleiner oder gleich dem Suchkriterium ist zurückgegeben;
dazu muß die erste (durchsuchte) Spalte aufsteigend sortiert sein.

Sagt die Online-Hilfe - und es stimmt ;-)

LG - Toni

Jörg Reutershan schrieb:

[Thomas Ramel]

Grüezi Jörg

Jörg Reutershan schrieb:

>>Du hat leider nicht die Funktion, wie du sie verwendet hat hier mit
>>angegeben. Ich vermute aber mal, dass der letzte 4.Parameter nicht
>>gesetzt, oder WAHR oder 1 ist.
>
> Stimmt.

Fein, dann hat der Schuss ins Blaue ja ins Schwarze getroffen.

Obschon ich noch immernicht weiss, wie das Schwarze denn auschaut.
Bitte kopiere die von dir verwendete Formel hier in einen Beitrag, dann ist
es uns auch möglcih *konkret* etwas zu raten. Ansonsten müssen wir uns auf
allgemeine Aussagen beschränken. Das führt (bei genügend Geduld) zwar auch
zum ziel, dauert jedoch einiges länger ;-)

>>Setzt ihn auf FALSCH oder 0 (Null), dann wird auf eine exakte
>>Überinstimmung gesucht und das Datum auch entsprechend gefunden.
>
> Kann ich so nicht nachvollziehen. Wenn ich den Parameter setze, bekomme ich
> für den 29.03.2003 die Heiligen Drei Könige angezeigt !?!?
> Der 4. Wert hat doch nichts mit der Reihenfolge der Parameter zu tun, oder?

Im Prinzip eben doch. (Hmm, schon wieder eine allgemeine Aussage)
Anton hat das sehr gut wiedergegeben.

[Jörg Reutershan]

> Bitte kopiere die von dir verwendete Formel hier in einen Beitrag,
> dann ist es uns auch möglcih *konkret* etwas zu raten. Ansonsten

Tabelle mit dem akutellen Monat (Auszug)

A B
...
36 28.03.03
=WENN(A36<>"";WENN(A36=SVERWEIS(A36;Feiertage;1);SVERWEIS(A36;Feiertage;3);"
");"")
37 29.03.03 dto.
38 30.03.03 dto.
39 31.03.03 dto.

SVerweis - Tabelle (Auszug) "Feiertage"

A B C
31-Dez-02 Di Silvester Vorjahr
01-Jan-03 Mi Neujahr
05-Jan-03 So vor
06-Jan-03 Mo Heilige Drei Könige
29-Mrz-03 Sa Beginn Sommerzeit
17-Apr-03 Do vor
18-Apr-03 Fr Karfreitag
20-Apr-03 So vor
21-Apr-03 Mo Ostermontag

In dieser Konstellation funktioniert es nur, wenn die Spalte A der
Sverweis-Tabelle geordnet ist (so wie oben).

Wenn die Reihenfolge nicht stimmt (Sommerzeit nach Ostern), bekomme ich für
o.a. Formel "" , für
"=WENN(A36<>"";WENN(A36=SVERWEIS(A36;Feiertage;1;falsch);SVERWEIS(A36;Feiert
age;3);"");"")" bekomme ich "Heilige Drei Könige".

Noch ein Problem mit dem 4. Parameter: Für den Fall, dass keine
Übereinstimmung in der Tabelle gefunden wird (also kein Feiertag), wird #NV
ausgegeben. Es soll aber """ ausgegeben werden.

Jörg

[Thomas Ramel]

Grüezi Rolf

Jörg Reutershan schrieb:

>>Bitte kopiere die von dir verwendete Formel hier in einen Beitrag,
>>dann ist es uns auch möglcih *konkret* etwas zu raten. Ansonsten

So; nun kommt Licht isn Dunkel ;-)

> A B
> ...
> 36 28.03.03
> =WENN(A36<>"";WENN(A36=SVERWEIS(A36;Feiertage;1);SVERWEIS(A36;Feiertage;3);"
> ");"")
> 37 29.03.03 dto.
> 38 30.03.03 dto.
> 39 31.03.03 dto.
>
>
> SVerweis - Tabelle (Auszug) "Feiertage"
>
> A B C
> 31-Dez-02 Di Silvester Vorjahr
> 01-Jan-03 Mi Neujahr
> 05-Jan-03 So vor
> 06-Jan-03 Mo Heilige Drei Könige
> 29-Mrz-03 Sa Beginn Sommerzeit
> 17-Apr-03 Do vor
> 18-Apr-03 Fr Karfreitag
> 20-Apr-03 So vor
> 21-Apr-03 Mo Ostermontag
>
> In dieser Konstellation funktioniert es nur, wenn die Spalte A der
> Sverweis-Tabelle geordnet ist (so wie oben).

Ja; das ist richtig; der 4.Parameter ist nicht gesetzt, wird daher als
WAHR angenommen und die Matrix *muss* aufsteigend sortiert sein.

> Wenn die Reihenfolge nicht stimmt (Sommerzeit nach Ostern), bekomme ich für
> o.a. Formel "" , für
> "=WENN(A36<>"";WENN(A36=SVERWEIS(A36;Feiertage;1;falsch);SVERWEIS(A36;Feiert
> age;3);"");"")" bekomme ich "Heilige Drei Könige".
>
> Noch ein Problem mit dem 4. Parameter: Für den Fall, dass keine
> Übereinstimmung in der Tabelle gefunden wird (also kein Feiertag), wird #NV
> ausgegeben. Es soll aber """ ausgegeben werden.

Ja; ich kann beide Fälle bestätigen.
Die Ursache liegt im Aufbau deiner Formel: Du hast zwei SVERWEIS()-Abfragen
drin und *nur die erste* mit dem 4.Parameter angepasst. Daher reagiert der
erste Teil der WENN()-Abfrage richtig; im zweiten (wo Du erst den Wert
holst) dann aber falsch :-(
Es ist sinnvoll, zweimal dieselbe Syntax zu verwenden; die Fehlersuche
fällt dann etwas leichter.
Ich habe die Formel ein wenig umgestrickt; versuche es damit:

=WENN(ISTFEHLER(SVERWEIS(A36;Feiertage;3;0));"";SVERWEIS(A36;Feiertage;3;0))

[Jörg Reutershan]

Thomas Ramel wrote:

> Ich habe die Formel ein wenig umgestrickt; versuche es damit:
>
>
=WENN(ISTFEHLER(SVERWEIS(A36;Feiertage;3;0));"";SVERWEIS(A36;Feiertage;3;0))

Ja, funktioniert perfekt. Vielen Dank!

Eine Frage doch noch: Du verweist in Deiner Formel immer auf die dritte
Spalte, nie auf die erste, in der ja die Daten stehen. Wie funktioniert es
trotzdem?

Jörg

[Thomas Ramel]

Grüezi Jörg

Jörg Reutershan schrieb:
>
>

> =WENN(ISTFEHLER(SVERWEIS(A36;Feiertage;3;0));"";SVERWEIS(A36;Feiertage;3;0))
>
> Ja, funktioniert perfekt. Vielen Dank!

Sag ich doch! ;-)

> Eine Frage doch noch: Du verweist in Deiner Formel immer auf die dritte
> Spalte, nie auf die erste, in der ja die Daten stehen. Wie funktioniert es
> trotzdem?

Hmmm, du hast dir die Infos zur Funkton in der Online-Hilfe zu Gemüte
geführt? So wie es ausschaut nicht ;-)

SVERWEIS() sucht *immer* nach einer Übereinstimmung in der ersten Spalte
der Matrix. Wenn die Übereinstimmung gefunden wird, liefert die Sunktion
den Rückgabewert aus der definierten Spalte.
Es ist also nicht notwendig in Spalte 1 zu suchen und wenn gefunden Spalte
3 zurückzugeben - das tut SVERWEIS() ganz von alleine.
Die WENN()-Formel hier dient nur dem Abfangen des Fehlers, wenn *keine*
Übereistimmung in Spalte 1 gefunden wird.

Weiters und Näheres wie geasgt in der Ohnile-Hilfe (soooo schlecht wie sie
oft gemacht wird, ist sich gar nicht - wenn man weiss wonach suchen).