Dieses Forum nutzt Cookies
Dieses Forum verwendet Cookies, um deine Login-Informationen zu speichern, wenn du registriert bist, und deinen letzten Besuch, wenn du es nicht bist. Cookies sind kleine Textdokumente, die auf deinem Computer gespeichert werden. Die von diesem Forum gesetzten Cookies werden nur auf dieser Website verwendet und stellen kein Sicherheitsrisiko dar. Cookies aus diesem Forum speichern auch die spezifischen Themen, die du gelesen hast und wann du zum letzten Mal gelesen hast. Bitte bestätige, ob du diese Cookies akzeptierst oder ablehnst.

Ein Cookie wird in deinem Browser unabhängig von der Wahl gespeichert, um zu verhindern, dass dir diese Frage erneut gestellt wird. Du kannst deine Cookie-Einstellungen jederzeit über den Link in der Fußzeile ändern.

Wenn(und( mit Summenprodukt?
#1

.xlsx   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
Antwortento top
#2
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


Angehängte Dateien
.xlsx   Mappe1 - Kalendar.xlsx (Größe: 31,23 KB / Downloads: 5)
[-] Folgende(r) 1 Benutzer sagt Danke an Rudi'S für diesen Beitrag:
  • ari-2001
Antwortento top
#3
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
Antwortento top
#4
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


Angehängte Dateien
.xlsx   Mappe2 - Kalendar.xlsx (Größe: 34,9 KB / Downloads: 3)
[-] Folgende(r) 1 Benutzer sagt Danke an Rudi'S für diesen Beitrag:
  • ari-2001
Antwortento top
#5
Rudi, super!

Vielen Dank!

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

Ohne Sa/So Ferien wäre es perfekt!

Viele Grüße
Andreas
Antwortento top
#6
Hoooray, Rudi, ich habe es Smile

=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
Antwortento top
#7
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
[-] Folgende(r) 1 Benutzer sagt Danke an Elex für diesen Beitrag:
  • ari-2001
Antwortento top
#8
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
[-] Folgende(r) 1 Benutzer sagt Danke an Rudi'S für diesen Beitrag:
  • ari-2001
Antwortento top
#9
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
Antwortento top
#10
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.
Antwortento top


Gehe zu:


Benutzer, die gerade dieses Thema anschauen: 1 Gast/Gäste