Im Excel Schulwochen unter der berücksichtigung von Schulferien ausrechnen?
Hallo liebe Excel Spezialisten :)
Ich möchte im Excel eine Formel eintragen, wo ich ausrechnen kann, wie viele Wochen eine Lehrperson Unterricht gibt. Das habe ich bereits mit DATEDIF herausgefunden. Nur ist das Problem, dass es in der Schule ja Ferien gibt und die Lehrpersonen dann nicht unterrichten.
Untenstehend habe ich mal einen Entwurf gemacht. Kann mir jemand die Formel verraten?
Oder gibt es eine Möglichkeit vielleicht sogar nur mit einer Angabe von 2 Daten z.B. 21.02.24 - 12.05.24 die Schulwochen unter berücksichtigung der Schulferien zu ermitteln?
Vielen Dank im Voraus!
5 Antworten
So etwas ähnliches habe ich mal in VB in Excel gemacht.
Im wesentlichen habe ich eine Tabelle genommen in welcher die Freien Tage bzw Zeiträume gelistet waren und habe diese geladen. An den Anfang hab ich nun den Suchzeitraum gestellt und ans Ende das Enddatum und dann eben diese Liste chronologisch sortiert.
Für ein Anfangs und Enddatum hab ich dann eben die Arbeitstage berechnet bis zum nächsten Datum welches Frei war. Dann diese Zeit übersprungen und danach weiter bis zum nächsten Datum usw.
Das lässt sich dort eigentlich recht einfach umsetzen.
Also ich muss leider sagen das ich halb gescheitert bin.
Ich hab zwar eine Lösung aber sie gefällt mir nicht.
Soweit mir bekannt ist hat Excel keine Funktion für Schulferien, sofern die aber Online Verfügbar im passenden Zahlenformat sind könntest du dir diese quasi live beziehen. Oder in einer Extra Tabelle eintragen händisch eintragen ich habe mich letzteres entschieden da die üblichen verdächtigen Seiten die selten strukturiert darstellen.
Zur Berechnung würde ich mit Netto Arbeitstagen rechnen. Diese erlaubt dir Tage abzuziehen, die du zuvor in einer Liste zusammenfasst.
Erklärt in aller Kürze NETTOARBEITSTAGE zählt die Tage exklusive Sa/So. Und abzüglich jeder Tage die M2# das "#" Zeichen steht dafür alle Zellen bis ans Ende der dynamischen Liste.
Die FILTER Funktion ermittelt über Sequenz die Anzahl der Tage zwischen den Start und Enddatum und gibt diese Anzahl an Zeilen aus, Startwert ist das Startdatum und der Wert wird immer um 1 erhöht.
Anschließend werden über die Filterbedingung ermittelt welche Tage Bestandteil dieser Liste M2 sind und herausgefiltert. Außerdem werden nur jene Daten drin behalten deren Wochentage Mo bis Fr lauten. Und schon ist die Liste fertig.
Und nun zu dem Teil der mir nicht gefällt, ich hab es leider nicht hinbekommen in der Liste in M2 alle Daten dynamisch zu Stapeln ich musste für jeden Datumbsbereich eine eigene Sequenzfunktion anlegen. Ich hatte eigentlich gehofft es darüber lösen zu können:
=nachzeile(F2:G7;lambda(z;sequenz(Spaltenwahl(z;2)-Spaltenwahl(z;1);1;Spaltenwahl(z);1)))
Doch leider funktioniert das nicht und gibt nur nur jeweils die 7 Startdaten aus.
Ich hoffe dir dennoch weitergeholfen zu haben und würde mich über Rückmeldung freuen.
Hier noch die folge Screenshot der Datenbereiche für Ferien etc.
Hallo,
ausgehend von deiner Frage:
Oder gibt es eine Möglichkeit vielleicht sogar nur mit einer Angabe von 2 Daten z.B. 21.02.24 - 12.05.24 die Schulwochen unter berücksichtigung der Schulferien zu ermitteln?
würde ich es so machen:
Zur Info: Der Bereich Suchbereich ist im Namensmanager als der Bereich A2:C53 definiert. Dort, in Zeile 53, findet sich die letzte Kalenderwoche des Schuljahres:
Die blau hinterlegte Spalte C ist nur eine Hilfsspalte für die BEREICH.VERSCHIEBEN-Funktion, die dann in H5 gebraucht wird. Diese Spalte kannst du ausblenden, wenn sie dich stört. Dasselbe gilt für den blauen Bereich mit den Angaben zu Zeilen und Höhe (E7 bis F8). Das sind ebenfalls Hilfsberechnungen für die BEREICH.VERSCHIEBEN-Funktion. Diese Berechnungen könntest du auch woanders in der Tabelle durchführen lassen und dann ausblenden.
Jetzt musst du nur noch bei Start (E2) und Ende (F2) ein Datum eingeben und Excel gibt dir in Zelle H5 die Unterrichtswochen aus.
Gruß, BerchGerch
PS: Wenn du wissen willst, wie die BEREICH.VERSCHIEBEN-Funktion arbeitet, kannst du dir mal dieses Video anschauen:
z.B. so
Dazu ist der Ferienkalender zu Grunde zu legen und für die Daten Beginn und Ende die Kalenderwoche festzulegen.
E2
- 1. Variante liefert Wahr / Falsch
- 2. Variante liefert ja / nein
Schulwochen: =Zählenwenn(E:E;"Falsch") oder =Zählenwenn(E:E;"nein")
Solch eine Formel gibt es meines Wissens nach nicht, zumal es ja 16 solcher Formeln bräuchte.
Bei deiner manuellen Auflistung kannst du dann statt einfach die "NEIN"s durchzuzählen, Exel diese zusammenzählen lassen: Mit der Formel =ZÄHLENWENN(Bereich;Suchkriterium) erfragst du wie oft sich "NEIN" in Spalte E findet.
Mit einer Formel bekommst du es ganz sicher nicht hin außer mit Let aber mir mehreren.
Von KW 37 bis KW 41 sind doch nur 5 Wochen und davon 2 Unterrichtswochen?