Wissensstand: Level 2 ⇒ Solides Basiswissen in Excel, etwas Erfahrung in PQ
Alle Jahre wieder stellt sich in Schulsekretariaten, kommunalen Verwaltungen, etc. die Frage, in welchem Jahr ein Kind eingeschult werden soll. Prinzipiell gilt folgende Regel: Wird ein Kind vor dem 1. August 6 Jahre alt, dann soll es (vorbehaltlich der schulischen Reife) in dem Jahr des 6. Geburtstags eingeschult werden, sonst im kommenden Jahr.
Zur Berechnung des Einschulungsjahrs (der Schulpflicht) mit Excel führen (natürlich) mehrere Wege nach Rom, also zum Ziel. Den einen oder anderen werde ich Ihnen hier vorstellen. In Plain Excel als auch in Power Query. Um meine Vorschläge nachvollziehen zu können, laden Sie diese Datei mit unterschiedlichsten Geburtstagsdaten von unserem Server herunter.
Plain Excel
Für diese als auch für die weiteren Lösungen gilt, dass in der Ergebnis-Spalte ausschließlich das Jahr der Einschulung ausgegeben werden soll. Als Excel-Formel mit einer eher klassischen Ausprägung bietet sich in B2 an:
=JAHR(A2) + 6 + N(MONAT(A2) >7)
JAHR(A2) gibt das Jahr der Geburt zurück. Dieses ist eine ganz normale Zahl, wo 6 (Jahre) addiert werden. Die Funktion N() sorgt dafür, dass ein Wahrheitswert 0 bzw. 1 zurückgegeben wird, wobei 0 für FALSCH und 1 für WAHR steht. Als Argument innerhalb der Klammern steht die Behauptung, dass die Monatszahl des Geburtstags größer als 7 ist. Ab August ist das Ergebnis dieser Funktion wahr, also 1 und dieser Wert wird dann entsprechend noch einmal zum Jahren der Einschulung dazu gezählt. Dass Sie die Formel so weit wie in der Nachbarzelle Daten stehen nach unten ziehen, versteht sich …
Eine weitere Möglichkeit bietet sich bei Excel-Versionen ab (mindestens) 2007. Die Formel ist recht kurz und prägnant:
=WENNFEHLER(JAHR(EDATUM(A2; 6))+6;"")
wobei Sie sogar auch noch die Funktion WENNFEHLER() weglassen könnten, wenn mit Sicherheit in Spalte A ein gültiges Datum steht: =JAHR(EDATUM(A2; 6))+6. Durch die Funktion EDATUM() wird dem Geburtstag der Zeitraum von 6 Monaten addiert und aus diesem neu berechneten Datum anschließend die Jahreszahl extrahiert.
Haben Sie jetzt das wirklich ganz klassische WENN()-Konstrukt vermisst? Ja natürlich, damit ist es auch möglich. Aber beim besten Willen, das mag ich Ihnen hier nicht antun …
Mit Power Query
Da die Geburtstagsdaten ja bereits in Form einer intelligenten Tabelle vorliegen, ist der Import in den Power Query-Editor problemlos mit nur einem Mausklick möglich. Ich räume ein, dass für vielleicht 100 oder 200 neue Schüler der Aufwand über Power Query nicht unbedingt erforderlich ist. Hier in diesem Beispiel stellen Sie sich einfach vor, dass beispielsweise in einer Behörde große Mengen von Daten verarbeitet werden müssen oder aber Sie wollen das des Lernens, des besseren Umgangs mit Power Query wegen tun.
Die Geburtstage sind in PQ importiert und im ersten Schritt wechseln Sie in das Register Transformieren | Datums- & Uhrzeitspalte | Nur Datum; die Darstellung der Uhrzeit 0:00 stört doch etwas die Optik. 😎 Wechseln Sie nun zu Spalte hinzufügen | Benutzerdefinierte Spalte und geben Sie im Dialog bei Neuer Spaltenname beispielsweise Einschulung Jahr ein. Im großen Kasten darunter Benutzerdefinierte Spaltenformel tragen Sie nach dem vorgegebenen Gleichheitszeichen diese Formel ein:
= if Date.Month([Geburtsdatum]) <8
then Date.Year([Geburtsdatum]) + 6
else Date.Year([Geburtsdatum]) + 7
Wenn Sie auch nur geringe Kenntnisse in Sachen Programmierung haben werden Sie sofort erkennen, dass dieses im Prinzip jene WENN()-Entscheidung ist, die ich Ihnen im ersten Abschnitt „vorenthalten” habe. Sie können diesen Code nacheinander in einer einzigen logischen Zeile schreiben oder hier der Übersichtlichkeit wegen in drei einzelnen Zeilen (wie oben gezeigt). Nach dem OK filtern Sie das Geburtsdatum beginnen mit dem 1. Januar des frühestmöglichen Datums (beispielsweise 2011) und das Ergebnis ist gegeben.
Idealerweise geben Sie dieser Abfrage einen sinnvollen Namen, beispielsweise Einschulung (klassisch). Schließen & laden oder Schließen & laden in…, um die Abfrage an eine definierte Position zu schreiben. Als Alternative erstellen Sie aus den gleichen Excel-Daten wie eben eine weitere Abfrage. Auch hier werden Sie sinnvollerweise das Datum aus den Datum/Uhrzeit–Wert extrahieren oder den Datentyp zu Datum wechseln. Und Sie werden sich denken können, dass auch hier eine Benutzerdefinierte Spalte zum Zuge kommt. Als Neuer Spaltenname verwenden Sie Einschulung (Jahr), die Spaltenformel sieht so aus:
= Date.Year([Geburtsdatum]) + 6 +
(if Date.Month([Geburtsdatum])>7 then 1 else 0)
Ja, das ist auch ein if-Konstrukt, eine WENN-Formel. Nur etwas kompakter, kürzer. Und die Klammern um den if-Teil sind wichtig, sonst kommt es zu einem Fehler. Apropos Fehler: Bedenken Sie, dass in der hier verwendeten Sprache M die Groß- Kleinschreibung der Anweisungen wichtig ist! Und auch diese Daten werden Sie filtern; als Start-Geburtsdatum werden Sie heute (im Jahr 2019) den 1.1.2011 wählen. Anschließend wieder Schließen & laden und auch diese Runde ist beendet.
Last but not least habe ich die die beiden Abfragen zusammengefügt. Mit ging es darum, die beiden Ergebnisse zu vergleichen. Schauen Sie sich gerne die Vorgehensweise intensiv an, es sollte nachvollziehbar sein. Sie wissen, dass im rechten Seitenfenster die einzelnen Schritte automatisch dokumentiert werden und Sie durch einen Klick auf das Zahnradsymbol rechts (sofern vorhanden) den Dialog öffnen.
Das war’s von meiner Seite. Zugegeben, nur ein kleiner Ausschnitt der Möglichkeiten, denn auch In Excel führen viele Wege zum Ziel. Die Lösungsdatei (die Sie vielleicht eben schon vermisst haben) finden Sie hier im Blog.