Sommerakademie – Lösung 2: Excel

Die Aufgabe 2 vom 19. August 2019 bestand darin, einen automatischen Kalender zu erstellen. Ausgehend von dem Startdatum in A2 soll in Spalte A der Monat bis zum Monatsende ausgefüllt werden.

Gebraucht wird die Funktion MONATSENDE. Die Funktion erfordert zwei Argumente: das Datum und die Anzahl der Monate, die weitergerechnet werden soll. Da ich vom gleichen Monat das Ende haben will, ist die Anzahl Null (0):

=M04.04.2019_0002ONATSENDE(A2;0)

ergibt für den 01.04.2019 den 30.04.2019 – probieren Sie das erst einmal in der Zelle daneben aus. Wenn Sie jetzt 43585 als Ergebnis bekommen, müssen Sie die Zelle noch als Datum formatieren.

Mit dieser Funktion muss ich jetzt rechnen. Zuerst einmal will ich Spalte A immer einen Tag weiterzählen. Also in A3 würde =A2+1 stehen – das gibt den 02.04.2019. Das soll allerdings nicht endlos lange so gehen, sondern nur, solange das Ergebnis kleiner oder gleich dem Monatsende von A2 ist.

Für A3 die erste Formel in Alltagssprache: Wenn A2 plus 1 kleiner oder gleich dem Monatsende von A2 ist, dann rechne A2 plus 1, sonst tue nichts.

In Excel-Sprache sieht das so aus:

=WENN(A2+1<=MONATSENDE(A2;0);A2+1;””)

04.04.2019_0003

Halt! Noch nicht nach unten ausfüllen. Wir sind noch nicht fertig.

Das erste Problem ist der relative Bezug auf A2 beim  Monatsende. Excel soll immer auf A2 verweisen, das muss also absolut gesetzt werden:

=WENN(A2+1<=MONATSENDE($A$2;0);A2+1;””)

Ich bin immer noch nicht glücklich. Wenn Sie diese Formel von A2 bis – ja bis wohin ziehen? A30, A31? Die Überlegung ist, wie viele Tage ein Monat maximal haben kann. Das sind 31. Wir starten in Zeile 2, plus 31, dann lande ich im höchsten Fall in Zeile 32. Ich muss die Formel also bis A32 ziehen. Ziehe ich also die Formel so weit nach unten, kriege ich für die letzten Zeilen in kurzen Monaten einen #WERT-Fehler.

Fehler fängt man ab mit WENNFEHLER. Das wird um die gesamte bestehende  Funktion herum gebaut:

=WENNFEHLER(WENN(A2+1<=MONATSENDE($A$2;0);A2+1;””);””)

Jetzt die ganze Formel bis in A32 (oder von mir aus auch weiter) ausfüllen. Probieren Sie es aus und tragen Sie in A2 den 01.02.2019 ein oder den 01.03.2019. Kontrollieren Sie das Ende in den Zeilen 31, 32. Alles ok?

Daneben kommt jetzt der Wochentag. Zuerst muss in B2 der Wert aus A2 übernommen  werden. Das geht mit =A2. Diese Zelle muss formatiert werden als Tag. Das benutzerdefnierte Zahlenformat dafür heißt TTTT – vier große T schreiben den Tagesnamen aus.

  • Markieren Sie B2
  • Rufen Sie das Zahlenformat auf – z.  B. mit rechter Maustaste und “Zellen formatieren”
  • Klicken Sie auf “Benutzerdefiniert” in der linken Liste
  • Tragen Sie bei Typ ein: TTTT
  • Bestätigen Sie mit OK und ziehen Sie die Formel bis B32

Probieren Sie wieder aus, indem Sie andere Monatsanfänge in A2 eintragen – passt alles?

Zum Schluss die bedingte Formatierung für das Wochenende.

Zuerst wieder die Vorarbeit. Wir müssen herausfinden, welche Wochentagsnummer das Datum hat. Denn mit Sonntag oder Dienstag kann das bedingte Format leider nichts anfangen. Dafür brauchen wir die Nummerierung: Montag ist die 1, Dienstag die 2, Sonntag die 7. Dafür gibt es die Funktion =WOCHENTAG(Datum;Typ).

Der Typ ist in jedem Land anders, weil manche Länder den Sonntag als Tag 1 nehmen, andere haben den Samstag als ersten Tag. Für Deutschland wäre als Typ 2 richtig. Die Funktion lautet also: =WOCHENTAG(A2;2)

04.04.2019_0004

Probehalber fülle ich das in Spalte C – die brauchen wir aber nicht. Sie sollen nur sehen, was als Ergebnis herauskommt. Das ist das Ergebnis für die ersten Zeilen: Samstag ist die Zahl 6, Sonntag die Zahl 7.

04.04.2019_0005

Damit kann  die bedingte Formatierung arbeiten. Spalte C kann man jetzt oder später wieder löschen. Und wer WOCHENTAG sowieso schon kannte, braucht die Spalte gar nicht.

Die bedingte Formatierung geht von A2 bis B32 – markieren Sie den Bereich. Wählen Sie “Start | Bedingte Formatierung | Neue Regel | Formel zur Ermittlung der zu formatierenden Zellen verwenden”.

Die Formel lautet:04.04.2019_0006

=WOCHENTAG($A2;11)>5

Achten Sie unbedingt auf das Dollarzeichen vor dem A! Es darf nicht alles absolut gesetzt werden, nur die Spalte.

Klicken Sie auf “Formatieren” und weisen Sie eine Füllfarbe auf dem  Register “Ausfüllen” zu. Alles mit OK bestätigen.

Und wieder ausprobieren, indem  Sie einen anderen Startwert in A2 eintragen. Klappt alles?

 

 

Die Datei zum Kontrollieren  und Nachgucken können Sie hier herunterladen: Sommerakademie Lösung 2

Werbeanzeigen

Kommentar verfassen

Trage deine Daten unten ein oder klicke ein Icon um dich einzuloggen:

WordPress.com-Logo

Du kommentierst mit Deinem WordPress.com-Konto. Abmelden /  Ändern )

Google Foto

Du kommentierst mit Deinem Google-Konto. Abmelden /  Ändern )

Twitter-Bild

Du kommentierst mit Deinem Twitter-Konto. Abmelden /  Ändern )

Facebook-Foto

Du kommentierst mit Deinem Facebook-Konto. Abmelden /  Ändern )

Verbinde mit %s