Ich versuche mich seit Stunden an einer Auswertung. Ausgewertet werden soll auf Basis der Kalenderwoche. Hierbei soll überprüft werden, ob pro KW mindestens 36 Stunden durchgängig freie Stunden vorhanden waren bzw. sind. In der Beispieldatei ist manuell aufgeführt worden wie in etwa das Ergebnis aussehen sollte. Dabei ist der Weg zum Ergebnis egal (bspw. Formelgebunden, Pivot usw.) Zur Vereinfachung oder besseren Überblick habe ich farbige Beispielmarkierungen gesetzt.
Ich würde mich über Hilfe freuen, da ich hier schon seit Stunden über die Datei brüte.
Hallo
folgende Formel in E4 zum runter kopieren, Zellformat in [hh]:mm oder Ergebnis mit 24 multiplizieren
=WENNFEHLER(WENN(C4="frei";1-C3+B5+1;WENN(B4="frei";1-C3+1;WENN(C3="frei";B4+2-C2;B4+1-C3)));"")
leider funktioniert die Formel nicht immer. Sie orientiert sich dabei nach der oberen und unteren Kalenderwoche. Sie sollte, wenn möglich, isoliert pro Kalenderwoche schauen (als Beispiel in der Datei blaue Klammern, neu ansetzen der Formel erst bei der nächsten Klammer). Vielleicht müsste hierzu in die Formel noch die Kalenderwoche miteinbezogen werden. So weit geht meine Kenntnis dann doch nicht. Es kann sein, dass dies formelgebunden nicht funktioniert, weil keine Regelmäßigkeit vorhanden ist und die Formel sich nicht immer nach unten orientieren darf, sondern nur innerhalb der gleichen KW. Ich habe dies genau aus diesem Grund immer manuell nachgerechnet.
Ich habe noch mal die Beispieldatei hochgeladen mit deiner Formel. Teilweise ergeben sich Unterschiede zur manuellen Überprüfung KW 45 (falls ich mich nicht verrechnet habe). Ob noch weitere Unterschiede sind, kann ich nicht sagen, da ich nicht alles durchgerechnet habe.
Ich wollte mich bezüglich der Problemstellung noch einmal melden. Für mein Dafürhalten ist das wahrscheinlich nur noch über VBA zu bewerkstelligen. Problematik des Ganzen, dass immer wieder unterschiedlich auf die Zahlen zugegriffen werden muss, weil die freien Tage in der KW nicht immer an der gleichen Stelle stehen.
Man möge mich korrigieren, falls ich falsch liege.
Gibt es vielleicht ein Tool für Excel oder ein Programm für den Rechner was das vielleicht kann?
Vielleicht hat irgendeiner dahingehend eine Idee, muss ja nicht immer sofort die Komplettlösung sein.
Die übersetzte Form der Formel habe ich genutzt. Ausgetauscht habe ich frei gegen 0:00 Spalte B und Spalte C 23:59. Als Ergebnis hatte ich raus in der KW 45 109,5. Gesucht sind aber die Freizeitstunden pro Tag wie ich es in der Beispieldaten_2 im schwarzen Kasten ganz links aufgeführt hatte.
Eine Formel, die immer unterschiedlich auf die Zellen zugreift, um die gewünschten Ergebnisse pro Kalenderwoche zu erhalten, gibt es da vielleicht nicht. Die Daten folgen keinem regelmäßigen Muster. Zumindest ist mir nicht bekannt was für eine Formel die unterschiedlichen unregelmäßigen Konstellationen abfangen könnte und dabei in der gleichen KW noch runterkopierbar wäre. Im Anfang war ich der Überzeugung dies mit einer Formel erschlagen zu können. Da ich nicht so formelfest wie die Helfenden im Forum bin, habe ich vor der Forumsfrage unterschiedliche WENN Konstrukte mit Hilfsspalten versucht zu basteln. Wenn es dann endlich mal bei einer KW passte, konnte die gleiche Formel bei anderen KW’s nicht eingesetzt werden. Erst danach habe ich hier die Frage gestellt.
Die Schwierigkeit hierbei meiner Meinung nach, sind die freien Tage, die dann zusätzlich noch auf unterschiedliche Wochentage fallen.
Insofern würde ich mich über Antworten freuen, die meinen Eindruck evtl. bestätigen. Inwiefern und ob das mit VBA zu erschlagen wäre, kann ich durch fehlende Kenntnisse nicht beurteilen.
05.02.2020, 21:21 (Dieser Beitrag wurde zuletzt bearbeitet: 05.02.2020, 21:27 von Umstaende_halber.)
@snb
Einfach? Naja, nicht jeder ist ein Profi.
Aaaaber, ich habe es endlich selbst hinbekommen. Der Tipp kam mehr oder weniger sogar von Dir. Statt frei und 23:59 h bzw. 0:00 h habe ich jeweils in meinem Formelkonstrukt bei beiden Spalten 0:00 h nehmen können. Der Schlüssel war nicht die doch sehr verschachtelte WENN Funktion, auch nicht das Summenprodukt von dir, sondern die WENNS Funktion. Sie hat den großen Vorteil, nicht verschachtelt zu sein und ist dadurch sehr übersichtlich. So ist die Formel viel besser nachvollziehbar bzw. lesbarer. Klar, wenn die WENNS funktioniert, funktioniert auch die WENN Funktion. Allerdings ist für mich zumindest die Übersichtlichkeit das Argument für die WENNS Funktion und letztendlich auch die Lösung bei mir gewesen.
Hierzu war es für mich wichtig, von Profis wie du einer sein wirst, erklärt zu bekommen, dass dies mit Formeln durchaus funktioniert.
Also, Lösung durch das Anstupsen der richtigen Tipps selbst erzeugt. Aber das hat einige graue Haare lang gedauert.
Mein Problem war schon sehr spezifisch, ich weiß daher nicht ob es ein Interesse an meiner Formel gibt bzw. gegeben hat. Um den Sinn des Forums nachzukommen poste ich sie der Vollständigkeit halber.
![[-]](https://www.clever-excel-forum.de/images/collapse.png)
