hallo Zusammen, wer kann mir die Formel erweitern bzw. korrigieren? Es geht darum das ich per Dropdownauswahl der Statusanzeige mir die monatliche Einträge (Tage)anzeigen lassen will. Jetzt weiss ich nur noch nicht,ob diese Formel auch die Feiertage bzw. auch Sa/So. berücksichtigt wird. Das kann ich erst feststellen,wenn die Formel funktioniert. Eventuell ist jemand bereit,die Formel so zu bauen,das die Feiertage/Sa.So. mit eingebunden wird.
Formel in D5 auf dem Tabellenblatt "T_M_Jahresübersicht": =SUMMENPRODUKT((INDEX(Urlaubsplaner!$L$8:$NS$37;VERGLEICH($B5;Urlaubsplaner!$C$8:$C$37;0);0)=$R$5)*(MONAT(Urlaubsplaner!$L$7:$NS$7)=SPALTE(A$1)))
Siehe angehängte Beispieldatei
Die Formel berücksichtigt alle Tage des entsprechenden Monates, also auch Sa./So. und Feiertage
hallo Fred, danke,sieht gut aus. ABER!ist mein Fehler,das ich nicht den Begriff "berücksichtigen der Sa/So+Feiertage" erklärt habe. Es solllen beim anzeigen der einzelnen Ergebnisse,die Sa./So+Feiertage abgezogen werden. Denn Sa./So.+Feiertage werden ja beim Urlaub nicht mitgezählt. Ich denke,ein kleines Problem wird es bei der Formel geben,wenn die Kranktage bei Feiertage nur von (Mo.-Fr.) mit gezählt werden sollen. Also so in etwa:deine Formel minus Wochentage<6, minus Feiertage,plus Kranktage bei Feiertage.*denkichmal*
also dass die Wochenenden nicht mitgezählt werden, könnte man noch mit der Ergänzung "*(WOCHENTAG(Urlaubsplaner!$L$7:$NS$7;2)<6)" innerhalb der SUMMENPRODUKT-Formel lösen.
Bei den Feiertagen klappt das allerdings so nicht, denn woher soll Excel auf dem Tabellenblatt "Urlaubsplaner" wissen welcher Tag ein Feiertag ist ? Die Feiertage hast du zwar schön per bedingter Formatierung gekennzeichnet, aber in Formeln kann das Format nicht abgefragt/ausgewertet weden. Man bräuchte dafür auf dem Tabellenblatt "Urlaubsplaner" im Bereich der Kopfzeilen eine zusätzliche Hilfszeile, in der die Feiertage gekennzeichnet sind (analog deiner bedingten Formatierung für die Feiertage) - und diese Hilfszeile dann in der SUMMENPRODUKT-Formel mit abfragen.
Aber wieso trägst du auf dem Tabellenblatt "Urlaubsplaung" an den Wochenenden und Feiertagen überhaupt ein "k" ein (und machst es dann mit der bedingten Formatierung wieder unsichtbar….) - wenn es sowieso nicht mitgezählt werden soll ? Das "U" für Urlaub hast du ja schliesslich auch nicht an den Wochenenden und Feiertagen eingetragen…
Mein Vorschlagalso: Da wo das "k" nicht mitgezählt werden soll (Wochenende/Feiertage) trägst du auf dem Tabellenblatt "Urlaubsplaner" auch kein "k" ein. Dann wird es in der Tages-/Monatsauswertung auch nicht mitgezählt und die bisherige Formel liefert das korrekte Ergebnis. (alles andere macht das ganze nur unnötig kompliziert)
also dass die Wochenenden nicht mitgezählt werden, könnte man noch mit der Ergänzung "*(WOCHENTAG(Urlaubsplaner!$L$7:$NS$7;2)<6)" innerhalb der SUMMENPRODUKT-Formel lösen.
Mit der Ergänzungsformel funktioniert das schon mal gut
Zitat:Bei den Feiertagen klappt das allerdings so nicht, denn woher soll Excel auf dem Tabellenblatt "Urlaubsplaner" wissen welcher Tag ein Feiertag ist ? Die Feiertage hast du zwar schön per bedingter Formatierung gekennzeichnet, aber in Formeln kann das Format nicht abgefragt/ausgewertet weden. Man bräuchte dafür auf dem Tabellenblatt "Urlaubsplaner" im Bereich der Kopfzeilen eine zusätzliche Hilfszeile, in der die Feiertage gekennzeichnet sind (analog deiner bedingten Formatierung für die Feiertage) - und diese Hilfszeile dann in der SUMMENPRODUKT-Formel mit abfragen.
Auch 'ne gute Idee.Aber irgendwie funktioniert die Formel nicht.
Zitat:Aber wieso trägst du auf dem Tabellenblatt "Urlaubsplaung" an den Wochenenden und Feiertagen überhaupt ein "k" ein (und machst es dann mit der bedingten Formatierung wieder unsichtbar….) - wenn es sowieso nicht mitgezählt werden soll ? Es ist einfacher die "Buchstaben" über dass WE zu ziehen.Und geht auch schneller ;) Das "U" für Urlaub hast du ja schliesslich auch nicht an den Wochenenden und Feiertagen eingetragen…
doch,habe sie nur den vertikalen WEspalten farbig angepasst.
Zitat:Mein Vorschlagalso: Da wo das "k" nicht mitgezählt werden soll (Wochenende/Feiertage) trägst du auf dem Tabellenblatt "Urlaubsplaner" auch kein "k" ein. Dann wird es in der Tages-/Monatsauswertung auch nicht mitgezählt und die bisherige Formel liefert das korrekte Ergebnis. (alles andere macht das ganze nur unnötig kompliziert)
Hab die Krankansicht erst mal auf ein seperates Blatt erstellt.Mit deiner Formel+Ergänzungsformel passt es.
ok,ich habe mir im Blatt "Urlaubsplaner" die Formel angelegt : =WENNFEHLER(SVERWEIS(L7;'nicht löschen!'!$F$4:$I$17;4;0);"") und im Blatt " nicht löschen" diese Formel angelegt: =WENN(WOCHENTAG(F5;2)>5;"";WENN(ZÄHLENWENN(Feiertage1;B5);"";"F")) Damit sind nun die Feiertage im "Urlaubsplaner" gekennzeichnet.Und nun zur Gretchenfrage. Wie kann ich die Fs aus der Hilfszeile für die Berechnung mit dieser Formel : =SUMMENPRODUKT((INDEX(Urlaubsplaner!$L$8:$NS$37;VERGLEICH($B5;Urlaubsplaner!$C$8:$C$37;0);0)=$Q$5)*(WOCHENTAG(Urlaubsplaner!$L$7:$NS$7;2)<6)*(MONAT(Urlaubsplaner!$L$7:$NS$7)=SPALTE(A$1))) einfließen lassen?
Deine Formel in Zeile 2 des Tabellenblatt "Urlaubsplaner" ist schonmal nicht schlecht.
Aber: Sie zeigt dir jetzt in Zeile 2 alle Feiertage aus dem Blatt "nicht löschen" an. Aber du möchtest in deiner Auswertung der Urlaubs- / Kran-Tage nur diejenigen Feiertage berücksichtigen, die du auf dem Blatt "nicht löschen" mit "F" gekennzeichnet hast. Du hast hier zwar wieder schön mit bedingter Formatierung gearbeitet - aber wie bereits oben erwähnt: Das Format kann man in Formeln nicht auswerten.
Lösung: Anpassung deiner Formrel in Zeile2 auf dem Tabellenblatt "Urlaubsplaner": =WENNFEHLER(SVERWEIS(L7;'nicht löschen!'!$F$5:$I$17;4;0);"")
So werden (nur) die Fs aus dem Tabellnblatt "nicht löschen" in die Zeile 2 auf dem Tabellenblatt "Urlaubsplaner" übertragen.
Und das kann man dann mit der SUMMENPRODUKT-Formel auswerten mit der Ergänzung *(Urlaubsplaner!$L$2:$NS$2<>"F")
moin, Danke für dein Einsatz. Stimmt.Statt =WENNFEHLER(SVERWEIS(L7;'nicht löschen!'!$F$5:$I$17;4;0);"") hatte ich $F$4.... eingetragen. Und nun funkt. es mit deiner Ergänzung. Nochmals,Danke
