Formel erweitern - Summenprodukt | HERBERS Excel Forum

Beiträge zum Forumthread

Beiträge zu diesem Forumthread

Betreff

Datum

Anwender

Hallo Werner,
ja genau, die Feiertage sind je nach Bundesland unterschiedlich.
In dem Kalender tragen die Mitarbeiter deren Urlaub und sonstige Abwesenheiten ein - ungeachtet, ob es Wochenende oder Feiertag ist. Für die Auswertung soll es keine Rolle spielen, ob ein Feiertag leer bleibt oder mit einer Abwesenheitsart versehen wird, das gleiche gilt für ein WE.
In der Spalte V werden WE-Tage eben nicht berücksichtigt und zwar durch die Formel "WOCHENTAG('Verfügbarkeit-Planung'!D$6:BL$6;2) Natürlich kann man entweder nur Spalte V oder W ermitteln und dann die andere Spalte durch eine Differenz von der Spalte U machen, aber ich wollte erstmal den direkten Weg über die Formel probieren.
Mein Ziel ist es, zu ermitteln, wieviele Arbeitstage ein Mitarbeiter im Monat verfügbar ist. Dazu vergleiche ich, was in der Planung steht.
Kriterium 1: Name des Mitarbeiters
Kriterium 2: Monat
Kriterium 3: nur Arbeitstage
Kriterium 4: kein Feiertag Mo-Fr => hier Abgleich Bundesland / Tabellenblatt Feiertage 2019 (Spalte A, D und E)
Es darf nur nicht passieren, dass Feiertag und Abwesenheitsart doppelt abgezogen werden. Wenn ein Feiertag für den Mitarbeiter greift, dann soll dieser für die Ermittlung "anwesend" abgezogen werden und wenn die Zelle mit Feiertag doch mit "U" versehen wird, dann soll nicht nochmal abgezogen werden. Ich hoffe, es ist einigermaßen verständlich, was ich meine.
Wenn ich die Ermittlung etwas manueller machen müsste, dann würde ich alle Spalten mit Feiertagen leer machen und dann die Formel in W6 durch diesen Ausdruck erweitern: "-T6". Diese Lösung möchte aber meiden, da ich ungerne etwas man Einträgen von Mitarbeitern verändern möchte.
Wahrscheinlich müsste man für die Lösung auch index, vergelich und zählenwenn kombinieren, ich weiß nur nicht wie.
Schade, dass ich dich verpasst habe, würde mich über deine Antwort dennoch freuen nach deiner Rückkehr.
VG, Kisska

…Kisska;
allerdings gilt für mich ab Sonntag das Gleiche wie für Werner (Gute Reise, Werner!). ;-)
Zuerst ein paar Hinweise:
Das 3.Blatt muss überarbeitet wdn, denn es enthält Fehler. Die 2.Tabelle nach Feiertagen und BLn zeigt unter Xl14/2010 leider nicht den realen Stand, weil es sich eigentl um singulare MatrixFmln handelt. Das mag unter deiner Version anders aussehen, trotzdem erhebt sich die Frage, warum in der 1.Tabelle dort einige allgemeine Feiertage nur BB zugeordnet wdn?
Auch erfüllt Werners Fml nicht deine Keine-Planung-Korrektur-aber-trotzdem-richtig-Wünsche. Das wäre ggf so möglich:
V6[:V8]: =WENN(P6="";"";SUMMENPRODUKT((Planung!$A$5:$A$6=M6)*(MONAT(Planung!$D$3:$BL$3)=MONAT(P6))*(WOCHENTAG(Planung!D$3:BL$3;2)<6)*ISTNV(VERGLEICH(Planung!D$3:BL$3;'Feiertage 2019'!$A$4:$A$22*('Feiertage 2019'!$D$4:$D$22="nein")*('Feiertage 2019'!$G$4:$G$22="ja");0))*(Planung!$D$5:$BL$6<>""))+SUMMENPRODUKT(('Feiertage 2019'!$D$4:$D$22="nein")*('Feiertage 2019'!$G$4:$G$22="ja")*(MONAT('Feiertage 2019'!$A$4:$A$22)=MONAT(P6))))
W6[:W8]: =WENN(P6="";"";SUMMENPRODUKT((Planung!$A$5:$A$6=M6)*(MONAT(Planung!$D$3:$BL$3)=MONAT(P6))*(WOCHENTAG(Planung!D$3:BL$3;2)<6)*ISTNV(VERGLEICH(Planung!D$3:BL$3;'Feiertage 2019'!$A$4:$A$22*('Feiertage 2019'!$D$4:$D$22="nein")*('Feiertage 2019'!$G$4:$G$22="ja");0))*(Planung!$D$5:$BL$6="")))
Ohne die wg der Merkwürdigkeiten zusätzlichen Teile wären die V-Fmln sogar kürzer als Werners, obwohl sicher nicht schneller.
Allerdings war mir nicht einsichtig, warum du einen Vgl der Planung-Eintragung mit der Legende willst, wenn doch nur relevant ist, dass eine Eintragung vorliegt. Sollen hier so unzulässige Eintragungen ignoriert wdn? Eine Auswertung ist keine Prüfliste! Beide Arten zu koppeln, kann nur zu schlechter Handhabbarkeit und Fehlern führen. Man kann ja auch das Original bewahren und für die Auswertung eine Korrektur verwenden. Das wurde früher generell so gemacht (Großrechner waren teuer genug), ist heute aber wohl in Vergessenheit geraten. Das hätte jedenfalls die Fmln vereinfacht.
Xl kann übrigens so rechnen wie du es gern gehabt hättest, aber dann wird's 3dimensional und die Xl-Fktt können mit solchen Datenfeldern eingabeseitig nichts anfangen und sie schon gar nicht aufs Blatt abbilden. Ich verwende für so etwas eigene UDFs (Stichwort: TensorProdukt).
Allseits schönen Urlaub bzw Gut Schwitz daheim!
Morhn, Luc :-?
Der beste Beweis für intelligentes Leben im Universum ist, dass noch niemand versucht hat, Kontakt mit uns aufzunehmen. H.Lesch, 2018, Sonneberg
Deshalb Intelligenz steigern mit

Beliebteste Forumthreads (12 Monate)

Formel erweitern - Summenprodukt

17 Beiträge zum Forumthread Beiträge zu diesem Forumthread

VBA Beispiele zu verwandten Themen

Beliebteste Forumthreads (12 Monate)

Beliebteste Forumthreads (12 Monate)

17
Beiträge zum Forumthread

Beiträge zu diesem Forumthread