Liebe Excel-Freunde,
ich stehe vor folgender Herausforderung.
Ich möchte aus meiner Dienstplantabelle herausfiltern, wie viele Wochenenden im Monat jemand arbeitet.
Mit der Summe aus verschiedenen WENN und ZÄHLENWENN-Funktionen erhalte ich die richtige Lösung (s. angehängte Tabelle C39). Die Bereiche muss ich aber per Hand in jedem Monat neu definieren.
Ich suche nach einer Formel, die mir erspart, jeden Monat die Wochenenden per Hand neu zu definieren. Bekomme ich Excel dazu, automatisch Freitag, Samstag und Sonntag als Wochenende zu erkennen und dann noch zu trennen, dass es unterschiedliche Wochenenden sind, damit ich zählen kann, an wie vielen Wochenenden jemand arbeitet? (Dabei ist es unerheblich, wie viele Tage am Wochenende die Person eingesetzt ist --> ob nur Samstag oder Freitag bis Sonntag, es ist immer 1 Wochenende.)
Vielen Dank
Hallo, so..:
PHP-Code:
=SUMMENPRODUKT((WOCHENTAG(B4:B34;2)>5)*(D4:G34=C37))
Das klappt nur bei Meier - zufällig.
Okay, dann ..:
PHP-Code:
=SUMMENPRODUKT((WOCHENTAG(B4:B34;2)>5)*(INDEX(D4:G34;;)=C37))
Edith:
Aber hier werden die Tage gezählt... und nicht die WE's...
Nur neues Excel (
auch kostenlos!):
C39:
=SUMME(--TEIL(WECHSELN(WECHSELN(TEXTVERKETTEN(;;((D4:D34=C37)+(E4:E34=C37)+(F4:F34=C37)+(G4:G34=C37)>0)*(REST(B4:B34+1;7)<3));111;11);11;1)&WIEDERHOLEN(0;31);SEQUENZ(31);1))
(Hinweis: SEQUENZ lässt sich ersetzen, TEXTVERKETTEN aber nicht)
Zu zählen, wie viel einzelne Wochenendtage es sind, habe ich dank der Wochentagsfunktion auch geschafft, aber danke für den Verweis.
Leider arbeite ich mit Excel 2010, d.h. TEXTVERKETTEN ist nicht drin.
Aber krasse Formel, ich brauch wahrscheinlich erstmal eine Woche um zu verstehen, welche Operationen dahinter verborgen sind.
VBA übersteigt meine Kenntnisse.
Ich habe jetzt folgendes versucht:
=WENN((B4-1-WOCHENTAG(B4-5;3)+7*1)*(ZÄHLENWENN(D4:G34;C37));1;0)
Damit wollte ich zunächst erreichen, dass am ersten Freitag im Monat >>>(B4-1-WOCHENTAG(B4-5;3)+7*1)<<< gezählt wird.
Sozusagen wenn Bedingung 1 erster Freitag im Monat UND Bedingung 2 Name aus C37 taucht irgendwo in Spalte D bis G auf gleichzeitig erfüllt sind, dann soll 1 gezählt werden.
Jetzt macht meine Formel das dummerweise nicht, sondern egal wo ich den Namen einfüge, wird 1 gezählt und die Bedingung 1 ignoriert.
--> wie kriege ich es hin, dass nur gezählt wird, dass beide Bedingungen erfüllt sind?
Zweite Frage, die ich nicht beantworten kann, solange ich keine Lösung für erstes Problem habe:
in meiner Wochentagsformel =(B4-1-WOCHENTAG(B4-5;3)+7*1) steht die 5 für Freitag. Funktioniert die Formel wenn ich statt 5 >>>ODER(5;6;7)<<< nutze sprich, überprüft die Formel dann automatisch den ersten Freitag und Samstag und Sonntag
Und da es meine zweite Frage warum auch immer drastisch verkleinert hat: In meiner Wochentagsformel (B4-1-WOCHENTAG(B4-5;3)+7*1) steht die 5 für Freitag.
Lässt sich das ggf. durch ODER(5;6;7) ersetzen und prüft den ersten Freitag/Samstag/Sonntag?
Moin
Für das aktuelle Excel hätte ich auch noch einen:
Code:
=ANZAHL(EINDEUTIG(MMULT(ISOKALENDERWOCHE($B$4:$B$34)*(WOCHENTAG($B$4:$B$34;11)>=5)*($D$4:$G$34=$C$37);{1;1;1;1})))-1
Schöne Lösung, shift-del! Bei mir war noch ein unbeachteter Fall: Schicht am Fr und So. (101); kommt zwar wohl nicht vor, aber wäre doppelt gezählt worden:
=SUMME(--TEIL(WECHSELN(WECHSELN(WECHSELN(TEXTVERKETTEN(;;((D4:D34=C37)+(E4:E34=C37)+(F4:F34=C37)+(G4:G34=C37)>0)*(REST(B4:B34+1;7)<3));101;1);111;1);11;1)&WIEDERHOLEN(0;31);SEQUENZ(31);1))