Warum funktioniert Excel-WENN-Formel mit SVERWEIS in zwei Zellen und dann nicht mehr?

Computer: Software & Programmierung Office Software
#1

Hallo,

ich habe ein Problem in Excel, wo ich mich zugegebenermaßen nur begrenzt auskenne. Ich habe eine Tabelle angelegt, in der die Terminplanung für Wahlen automatisch errechnet werden soll. Das heißt, ausgehend vom Datum des Wahltags werden die Termine für verschiedene Aktionen berechnet. Dabei sollen Wochenenden und Feiertage mit berücksichtigt werden. Die Feiertage habe ich in ein Extra-Tabellenblatt gepackt und dort die Spalte mit den Terminen als „feiertag“ benannt.

Die Formel zur Terminberechnung sieht jetzt so aus:
=WENN(WOCHENTAG($B$8-50)=1; $B$8-52; WENN(WOCHENTAG($B$8-50)=7; $B$8-51;WENN(SVERWEIS($B$8-50;feiertag;1;0); WENN(WOCHENTAG(2);$B$8-53; WENN(WOCHENTAG(1);$B$8-52;$B$8-51)); $B$8-50)))

Dabei ist B8 das Wahldatum, in dieser Formel wird der 50. Tag vor der Wahl berechnet und wenn das Datum auf einen Wochenend- oder Feiertag fällt, wird es entsprechend vorverlegt. Das funktioniert auch so. In einer zweiten Zelle ebenfalls, in allen anderen Zellen mit anderen Abständen zum Wahltag nicht mehr, da kommt dann die Meldung #NV. Ich verstehe nicht warum, weil die Formel an sich ja identisch ist.

Hier ein Beispiel für eine Zelle mit Fehlermeldung (36. Tag vor der Wahl):
=WENN(WOCHENTAG($B$8-36)=1; $B$8-38;WENN(WOCHENTAG($B$8-36)=7; $B$8-37; WENN(SVERWEIS($B$8-36;feiertag;1;0); WENN(WOCHENTAG(2); $B$8-39; WENN(WOCHENTAG(1); $B$8-38; $B$8-37));$B$8-36)))

Hat jemand eine Idee dazu?

Danke schonmal und viele Grüße
Henriette

#2

hey,

ich bin auch kein Experte - hatte jedoch mal mit dem SVerweis ein Problem das mich Tage gekostet hat.
Ohne deine Formel jetzt genau betrachtet zu haben, möchte ich dir einen „Tipp“ geben, VIELLEICHT hilft es dir:

die Matrix im Sverweis, also =SVERWEIS(Suchkriterium;MATRIX;Spaltenindex) muss alphabetisch sortiert sein!

vielleicht liegt da „der Hund begraben“,… denn der Sverweis funktioniert nur so lange, so lange die Liste der Matrix alphabetisch sortiert ist…

wenn wir bspw. von Namen in der Matrix ausgehen würden:

Anton
Berta
Caeser
Dora
Emil
Friedrich
Paula
Gustav
Heinrich
…

dann würde der SVERWEIS nur bis „Friedrich“ funktionieren, alle unterhalb davon nicht mehr…

Beste Grüße

#3

Hallo
Ich glaube, daran liegt es nicht. Henriette setzt ja in der VERWEIS()-Funktion den Wert 0 ein. Dann muss die Matrix nicht alphabetisch sortiert sein.
Grüsse Niclaus

1 Like
#4

Grüezi Henriette

Ein sehr komplexe Formel! Ich habe sie nur mal angeschaut und dabei ist mir aufgefallen:
Nach dem VERWEIS-Teil schreibst Du:
WENN(WOCHENTAG(2) und etwas später
WENN(WOCHENTAG(1)
Das kann nicht richtig sein, denn zu WENN gehört eine Prüfung! Also z. B.
WENN(WOCHENTAG($B$8)=2);$B$8-53;

Auf den ersten Blick hätte ich folgenden Vorschlag für Deine Formel:

=WENN(WOCHENTAG($B$8-50)=1; $B$8-52;
WENN(WOCHENTAG($B$8-50)=7; $B$8-51;
WENN(ISTFEHLER(SVERWEIS($B$8-50;feiertag;1;0));$B$8-50;
WENN(WOCHENTAG(SVERWEIS($B$8-50;feiertag;1;0))=2;$B$8-53;
WENN(WOCHENTAG(SVERWEIS($B$8-50;feiertag;1;0))=1;$B$8-52;
WENN(WOCHENTAG(SVERWEIS($B$8-50;feiertag;1;0))>2;$B$8-51))))))

Aber auch das wird möglicherweise nicht immer stimmen: z. B. bei den Weihnachtsfeiertagen!

Ich würde in Deine feiertage eine zweite Spalte einfügen: Zu jedem Feiertage würde ich dort den Alternativ-Arbeitstag einfügen. Dann könnte man die obige Formel viel einfacher gestalten.

Grüsse Niclaus

#5

Hallo Niclaus,

danke für Deine ausführliche Antwort. Wenn das Problem wäre, dass die Prüfung nicht gegeben ist, dürfte ja die erste Formel (die Du dann abgeändert hast) nicht funktionieren - tut sie aber. Und sie tut es auch noch an einer zweiten Stelle mit veränderten Zahlen. Danach aber dann nicht mehr, z. B. in der zweiten Variante, die ich weiter unten eingestellt hatte.

Ich werde gleich mal versuchen, wie es mit den vielen SVerweis-Nennungen geht, vielleicht funktioniert das ja, dann wäre mir schon sehr geholfen.

Eine Zusatzspalte mit Alternativarbeitstagen ist nicht praktikabel, weil die Tabelle auch für weitere Jahre funktionieren soll (die Feiertage werden auch über Formeln berechnet), das würde dann unnötig kompliziert werden, dort auch noch wieder Wenn-Dann-Formeln einzufügen.

Danke auf jeden Fall schomal fürs Mitdenken!

Viele Grüße
Henriette

#6

Genau, und außerdem handelt es sich um Daten.

Trotzdem danke.

1 Like
#7

Okay, jetzt habe ich es mit der neuen Formel von Dir, Niclaus, probiert, und es funktioniert überall. Ganz herzlichen Dank dafür!

Viele Grüße
Henriette

#8

Grüezi Henriette

Wenn das Problem wäre, dass die Prüfung nicht gegeben ist, dürfte ja die erste Formel (die Du dann abgeändert hast) nicht funktionieren - tut sie aber. Und sie tut es auch noch an einer zweiten Stelle mit veränderten Zahlen. Danach aber dann nicht mehr, z. B. in der zweiten Variante, die ich weiter unten eingestellt hatte.

Ich kann den Fehler ohne Deine konkreten Daten nicht nachvollziehen. Ich glaube aber, die Formel funktioniert, solange Wochentag() = 1 oder 7 ergibt. Dann gerät sie ins Schleudern.

Zu meinem Vorschlag zu einer erweiterten Matrix meinst Du:

Eine Zusatzspalte mit Alternativarbeitstagen ist nicht praktikabel, weil die Tabelle auch für weitere Jahre funktionieren soll (die Feiertage werden auch über Formeln berechnet), das würde dann unnötig kompliziert werden, dort auch noch wieder Wenn-Dann-Formeln einzufügen.

Ich glaube, Du kommst nicht darum herum, Zusatzspalten in die Feiertagsliste einzufügen. Ich denke z. B. an Ostern: Bei uns sind Karfreitag und Ostermontag Feiertage.

Angenommen, Wahltag ist der 6. Juni 2017. Du müsstest also -54 rechnen, damit Du auf Do 13.4.2017 kommst.

Oder wenn der Wahltag der 20. Juni 2017 ist: Du müsstest dann mit -53 rechnen, damit der Aktionstag nicht auf den 1. Mai fällt. Aber am 20. Juni 2018 nur mit -51.

Wenn der Wahltag der 14.2.2018 wäre, müsstest Du mit -54 rechnen. – Bei uns sind der 25. und der 26. Dezember Feiertage. Am 14.2.2019 aber nur mit -52. Oder vielleicht sogar mit -55, weil bei uns der 24. Dezember in vielen Betrieben als arbeitsfreier Brückentag gilt, wenn er auf einen Montag fällt. - Ein ähnlicher Brückentag ist der Freitag nach Auffahrt.

Alle diese Feiertage für x Jahre in eine einzige Formel zu kriegen, ist recht umständlich. - Sie in einer zweispaltigen Matrix zu berechnen oder von Hand einzugeben, ist gar nicht so aufwändig.

Die Formel, die ich oben aufgeführt habe, musst Du vergessen. Die schaut zwar, ob ein Aktionstag auf einen Samstag oder Sonntag fallen würde – dann soll der vorangehende Freitag aktiviert werden. Aber ob dieser Freitag ein Feiertag ist, das wird nicht kontrolliert.

Ein spannendes Thema ist es! Ich habe für mich die Matrix „feiertag“ auf zwei Spalten erweitert: links der Feiertag – rechts (z. T. von Hand berechnet) der entsprechende Arbeitstag davor:

Osterfeiertage 2017:
14.04.2017	Do 13.04.2017
17.04.2017	Do 13.04.2017
Weihnachtsfeiertage 2017:
25.12.2017	Fr 22.12.2017
26.12.2017	Fr 22.12.2017

Mit dieser erweiterten Matrix komme ich dann zu folgender „Aktionstag“-Formel:

=WENNFEHLER(SVERWEIS((A8-50+WENN(WOCHENTAG(A8-50;2)>=6;5-WOCHENTAG(A8-50;2);0));
feiertag;2;0);A8-50+WENN(WOCHENTAG(A8-50;2)>=6;5-WOCHENTAG(A8-50;2);0))

Ich habe sie für einige Daten kontrolliert; ich glaube, sie haut hin.
Grüsse Niclaus

#9

Hallo Henriette
Du meinst meine Formel von gestern:

Ich bin mir nicht so sicher, ob die wirklich überall stimmt. - Siehe meinen Beitrag von 12:03 Uhr. Er hat sich mit Deinem Beitrag gekreuzt.
Grüsse Niclaus

#10

Ja, leider hast Du Recht, sie funktioniert tatsächlich nicht überall, denn ich hatte jetzt testweise mal ein Datum in 2018 eingegeben, bei dem dann fälschlicherweise nicht der korrekte Termin ausgeworfen wurde, sondern ein Tag später (war eine Formel, die vorwärts- statt rückwärtsgerechnet hat), der dann ein Feiertag war. Wahrscheinlich lag das am ISTFEHLER… Der eigentlich korrekte Termin wäre kein Feiertag und auch kein Wochenende gewesen.

Was Deinen Vorschlag mit Alternativarbeitstagen angeht, ist das schwierig. Ich erstelle die Datei für eine Kollegin, die so gut wie gar keine Ahnung von Excel hat. Sie muss nun das Jahr und den Jahresersten eintragen. Die Feiertage und alle Termine sollen von dort aus automatisch berechnet werden. Wenn man jetzt zu den Feiertagen noch Alternativ-Arbeitstage automatisch berechnen wollte, würde das wahrscheinlich wieder lange Wenn-Dann-Formeln ergeben. Die einzig denkbare Variante dazu wäre, dass ich jedes Jahr eine neue Tabelle erstelle mit errechneten Alternativterminen, aber ich werde ja vielleicht nicht immer da sein. Deine „Aktionstag-Formel“ verstehe ich leider nicht so richtig - wo wäre die denn einzufügen? Müsste ich das bei jedem Termin machen (sind insgesamt 24)?

Einfacher wäre wahrscheinlich, die gestern von Dir genannte Formel nochmal zu erweitern, damit sie Feiertage mit umfasst. Echt kompliziert das Ganze, ich sitze da schon (mit Unterbrechungen) eine Woche dran.

Wenn es Dich reizt, kann ich Dir gern die Tabelle mal zukommen lassen (hier lässt sich, glaube ich, nichts hochladen), aber ich will auch nicht zu viel von Deiner Zeit in Anspruch nehmen.

Viele Grüße
Henriette

#11

Entschuldige, ich habe die Formel ohne zu Ueberlegen aus meiner Testdatei kopiert. In dieser Formel müsstest Du A8 überall ersetzen durch $B$8. - Gemäss Deinen Vorgaben heisst sie dann:

=WENNFEHLER(SVERWEIS(($B$8-50+WENN(WOCHENTAG($B$8-50;2)>=6;5-WOCHENTAG($B$8-50;2);0));
feiertag;2;0);$B$8-50+WENN(WOCHENTAG($B$8-50;2)>=6;5-WOCHENTAG($B$8-50;2);0))

Die Erklärung der Formel ist kompliziert! Wichtig für mich ist, mit WOCHENTAG(DATUM, 2) zu arbeiten: also Samstag = 6, Sonntag = 7. Das vereinfacht das Ganze. Und dann nehme ich WENNFEHLER(SVERWEIS …): d. h. wenn das Datum in feiertag nicht vorkommt: Ich prüfe also zuerst, ob es sich um einen Feiertag handelt, erst dann, ob es Samstag oder Sonntag ist.

Grüsse Niclaus