Microsoft Excel

Herbers Excel/VBA-Archiv

Mit welcher Formel machbar ?

Betrifft: Mit welcher Formel machbar ? von: Michael
Geschrieben am: 30.09.2020 19:00:41

Hallo zusammen,

stehe vor einer Aufgabe , die ich ohne Euer Hilfe nicht hinbekomme.
Es handelt sich um Mitarbeiter Einsatzplan. Soweit mit der Layout der Tabelle bin zufrieden aber ich denke nicht optimal dafür was ich vorhabe.

im TB "Auswertung" würde ich gerne aus dem Blatt " Einsatzplan " aktuelle und durchschnittliche ( pro KW ) Mitarbeiterzahlen darstellen z.B. Urlaubs- und Krankheitstage

Besten Dank für Eure Unterstützung

https://www.herber.de/bbs/user/140554.xlsx

Betrifft: AW: Mit welcher Formel machbar ?
von: Werner B
Geschrieben am: 30.09.2020 22:12:29

Hallo Michael,

meinst du so etwas?
https://www.herber.de/bbs/user/140556.xlsx

Gruß Werner B.

Betrifft: AW: Mit welcher Formel machbar ?
von: Michael
Geschrieben am: 01.10.2020 17:39:47

Hallo Werner,

Perfekt ! genauso habe ich mir vorgestellt.

So eine Lösung hatte ich nicht erwartet. Ich habe mit Summeprodukt , Sverweis und ähnlichen probiert. Könntest Du mir kurz erklären die Formel, die Du dafür verwendet hast ?

Die Formel Summe in dieser Variante kenne ich gar nicht. Dies hat nur Bezug zum Datum und KW !
Wäre nie darauf gekommen.

Besten Dank für deine Unterstützung

Gruß
Michael

Betrifft: AW: Mit welcher Formel machbar ?
von: Werner B
Geschrieben am: 01.10.2020 19:13:45

Hallo Michael,

die Matrix-Summenformel ist im Prinzip die gleiche wie Summenprodukt.
Diese Array-Formel wird im Gegensatz zur Summenprodukt-Formel immer mit der Tastenkombination STRG+Umschalt+Eingabe abgeschlossen.

Nun kurz zum Vorgehen.
Im Tabellenblatt "Einsatzplan" habe ich zwei Zeilen eingefügt.
Zeile 69 mit den entsprechenden Tagesdaten, in Zeile 70 die KW mit Hilfe der Funktion KALENDERWOCHE.
Mit der Arrayformel, hier im Tabellenblatt "Auswertung", habe ich die Wertebereiche der Zeile 69 (=Tagesdaten) mit den Wertebereich (Array) der Tagessummen zum Schnittpunkt gebracht.
Ebenso mit der KW.
Hier steht ja die auszuwertende KW in der Zelle "C6".
In der Zeile 68 stehen ja die jeweiligen Summen der KW für die MA/Tag
Diese wird dann als Referenzwert in die Arrayformel mit dem Wertebereich zum Schnittpunkt gebracht.
{=SUMME(($C$6=Einsatzplan!$E$70:$MK$70)*(Einsatzplan!$E$68:$MK$68))}

Die Arrayformel erkennt für jeden Wert einen Schnittpunkt (=TRUE) oder kein Schnittpunkt (=FALSE).
Aufgrund dieser Tatsache können auch so die Treffer gezählt werden, indem die TRUE-Werte mit "1" zusammengezählt werden:
Zelle "C11"
{=SUMME(($C$6=Einsatzplan!$E$70:$MK$70)*1)}
Es können auch gleich beide Arrayformeln direkt miteinander kombiniert werden, wenn keine Hilfszelle benutzt werden soll.
Dann würde diese so aussehen:
{=RUNDEN(SUMME(($C$6=Einsatzplan!$E$70:$MK$70)*(Einsatzplan!$E$68:$MK$68)) /SUMME(($C$6=Einsatzplan!$E$70:$MK$70)*1);0) }
Da dieses Beispiel jedoch nur bis Monat Oktober geht, müüsstest du diese Formeln dann entsprechend anpassen.
Daran denken, die Arrayformeln dann mit STRG+Umschalt+Eingabe abschließen.

Ich hoffe das einigermaßen verständlich erläutert zu haben.

Gruß Werner B.

Betrifft: AW: Mit welcher Formel machbar ?
von: Michael
Geschrieben am: 03.10.2020 08:34:42

Danke Dir, jetzt verstehe ich es

VG

Michael

Betrifft: AW: Mit welcher Formel machbar ?
von: Herbert_Grom
Geschrieben am: 01.10.2020 10:34:47

Hallo Michael,

du solltest aber deine Daten mal überprüfen, denn mal gibst du z. B. das "U" von Urlaub mit ("U.") und mal ohne Punkt ein!

Servus

Betrifft: AW: Mit welcher Formel machbar ?
von: Michael
Geschrieben am: 01.10.2020 17:41:12

Hallo Herbert,

Danke für den Hinweis

Gruß

Michael

Betrifft: AW: Gern geschehen! Danke für die Rückmeldung! owt
von: Herbert_Grom
Geschrieben am: 01.10.2020 17:43:02

,,,