Wenn(und( mit Summenprodukt?

[ari-2001]

  Mappe1 - Kalendar.xlsx (Größe: 37,71 KB / Downloads: 10) Hallo,
ich habe einen Kalender und in Spalte "D" gibt er mir die Tage raus.
Dabei schaut er nach, ob das Datum in definierten Bereichen (Feiertage,Ferien NRW) vorkommt.
Wenn Ja, wird ein "F" für Ferien, wenn nicht, wird ein "S" für Schultage in die Zelle geschrieben.
Ist es ein ´Feiertag, wird ein Feiertag in die Zelle geschrieben.

Ist es ein Samstag kommt ein "Sa" dahin, ist es ein Sonntag, kommt ein So dahin.

Soweit, so gut, hier die Formel dazu:

=WENN(ZÄHLENWENN($J$2:$J$15;A2);SVERWEIS(A2;$J$2:$L$15;3);WENN(B2="Sa";"Sa";WENN(B2="So";"So";WENN(SUMMENPRODUKT(($J$20:$J$29<=A2)*($K$20:$K$29>=A2));"F";"S"))))

Ich schaffe es nicht, bei den Samstagen und Sonntagen die Formel so anzupassen, dass wenn die Samstage bzw. Sonntage in den Ferien stattfinden, diese mit "Sa Ferien" bzw. "So Ferien" zu benennen, ansonsten sollen die Samstage einfach "Sa" und "So" heißen.


Tabelle ist beigefügt, in Spalte D die Formel geht, bis auf das Manko mit den Samstagen und Sonntagen in den Ferien.

Hat jemand einen Tipp?

Vielen Dank im Voraus

Andreas

[Rudi'S]

Hallo Andreas,

ist nur eine kleine Formelerweiterung notwendig.

=WENN(ZÄHLENWENN($J$2:$J$15;A5);SVERWEIS(A5;$J$2:$L$15;3);WENN(UND(SUMMENPRODUKT(($J$20:$J$29<=A5)*($K$20:$K$29>=A5));B5="Sa");"Sa Ferien";WENN(UND(SUMMENPRODUKT(($J$20:$J$29<=A5)*($K$20:$K$29>=A5));B5="So");"So Ferien";WENN(SUMMENPRODUKT(($J$20:$J$29<=A5)*($K$20:$K$29>=A5));"F";WENN(B5="Sa";"Sa";WENN(B5="So";"So";"S"))))))

Gibt auch hierzu bestimmt einfachere Lösungen, aber es funktioniert.

Gruß Rudi

[ari-2001]

Hallo Rudi,

ja, das klappt soweit.

Ich habe mich allerdings vertan, der Datumsbereich für die Samstage und Sonntage soll nicht aus den Ferien der Schultage entnommen werden, sondern der
Datumsbereich der Samstage steht in N2(01.05.2020) und o2(21.06.2020).(Also vom 01.05-21.06.2020)

Und es soll Sa Som bzw. So Som da stehen, wenn die Samstage in diesen Bereich fallen.

Wie kann man das den mit der Formel bewerkstelligen?
Umgebaut bekomme ich die Formel grade nicht.

Hast Du noch einmal so einen tollen Tipp wie vorhin?

Gruß
Andreas

[Rudi'S]

Hallo Andreas,

habe in den angegeben Zellen nichts gefunden, meintest du den Bezug zum Sommer in den Zellen J/K 45?
Egal wie, habe deine Angaben  jedenfalls dort jetzt eingetragen und du kannst in den Spalten N und O noch zwei weitere Termine anfügen oder auch die Daten dort ändern.

Die Sa/So in den Ferien sind erst mal weiterhin in der Auswertung geblieben.
Es werden weiterhin nur die Ferientermine von NRW berücksichtigt.

Gruß Rudi

[ari-2001]

Rudi, super!

Vielen Dank!

Wie nehme ich den jetzt die Sa/So Ferien aus der Auswertung raus?
Ich blicke die Formel nicht wirklich

Ohne Sa/So Ferien wäre es perfekt!

Viele Grüße
Andreas

[ari-2001]

Hoooray, Rudi, ich habe es

=WENN(ZÄHLENWENN($J$2:$J$16;A2);SVERWEIS(A2;$J$2:$L$16;3);WENN(UND(SUMMENPRODUKT(($N$2:$N$2<=A2)*($O$2:$O$2>=A2));B2="Sa");"Sa Som";WENN(UND(SUMMENPRODUKT(($N$2:$N$2<=A2)*($O$2:$O$2>=A2));B2="So");"So Som";WENN(B2="Sa";"Sa Win";WENN(B2="So";"So Win";WENN(SUMMENPRODUKT(($J$20:$J$29<=A2)*($K$20:$K$29>=A2));"F";"S"))))))


Puhhhhh, ohne deine grandiose Vorarbeit wäre ich da niemals drauf gekommen!

Vielen lieben Dank!

Viele Grüße
Andreas

[Elex]

Hi

Selber Versuchen = richtiger Weg
 
Nur als Inspiration.
Da dein Wunschergebnis nun offen liegt, kannst du deine Formel noch etwas zusammenfassen.

Code:

=WENNFEHLER(SVERWEIS(A2;$J$2:$L$15;3;0);WENN(WOCHENTAG(A2;2)>5;B2&WAHL(1+(A2>=$N$2)*(A2<=$O$2);" Win";" Som");WENN(SUMMENPRODUKT((A2>=$J$20:$J$29)*(A2<=$K$20:$K$29));"F";"S")))

Gruß Elex

[Rudi'S]

Hallo Elex,

danke! Ich wusste, dass es kürzer und besser geht. Ganz bin ich darauf nicht gekommen, deswegen mein 08/15 Weg, den auch jeder ohne viel Wissen nachvollziehen kann. Aber so habe ich den letzten Schritt auch noch gelernt.

Gruß Rudi

[ari-2001]

Hallo Rudi,
hallo Elex,

vielen vielen Dank für Eure Hilfe, ohne Euch wäre ich niemals auf die Lösung gekommen.

Aber es freut mich, dass ich den letzten Rest auch noch hinbekommen habe, aber ohne Rudi würde ich da nächste Woche noch dran rumbasteln.

Ich schaue mir nachher mal deine Zusammenfassung der Formel an, ob ich die verstehe, aber ich werde mir größte Mühe geben.

Vielen Dank noch einmal an euch beide!

Andreas

[ari-2001]

Hallo Elex,

ich habe meine Formel jetzt so weit,
dass mir wirklich alle vorkommenden 35 Betriebstagsarten mit EINER Formel berechnet werden.

Wärst Du so freundlich und würdest Du mal schauen, ob Du die (wie die andere) etwas einkürzen könntest?

Hier ist das Prachtstück:

=WENN(UND(A2=DATUM($N$1;12;24);WOCHENTAG(A2;2)=7);"Heil So";WENN(UND(A2=DATUM($N$1;12;31);WOCHENTAG(A2;2)=7);"Sil So";WENN(WOCHENTAG(A2;2)=7;B2&WAHL(1+(A2>=$O$53)*(A2<=$P$53);" Win";"");WENN(UND(F2="";DATUM(JAHR(A2);MONAT(A2);1)-WOCHENTAG(DATUM(JAHR(A2);MONAT(A2);1)-5;3)+6=A2);"1. Fr"&WENN(SUMMENPRODUKT((A2>=$O$32:$O$41)*(A2<=$P$32:$P$41));" F/"&WENN(SUMMENPRODUKT((A2>=$O$42:$O$52)*(A2<=$P$42:$P$52));"F";"S");" S/"&WENN(SUMMENPRODUKT((A2>=$O$42:$O$52)*(A2<=$P$42:$P$52));"F";"S"));WENNFEHLER(SVERWEIS(A2;$O$2:$Q$15;3;0);WENN(WOCHENTAG(A2;2)>5;B2&WAHL(1+(A2>=$O$53)*(A2<=$P$53);" Win";"");WENN(SUMMENPRODUKT((A2>=$O$32:$O$41)*(A2<=$P$32:$P$41));B2&" F/"&WENN(SUMMENPRODUKT((A2>=$O$42:$O$52)*(A2<=$P$42:$P$52));"F";"S");B2&" S/"&WENN(SUMMENPRODUKT((A2>=$O$42:$O$52)*(A2<=$P$42:$P$52));"F";"S"))))))))


Das hat mal richtig Spaß gemacht, das zusammenzubasteln!

Vielen Dank im Voraus, falls Du zeit dazu finden solltest.