Excel – Urlaubsliste (1-2)

Normalerweise würde ich Urlaubs- oder Anwesenheitslisten nach diesem Muster machen: alle Tage nebeneinander, damit ein Jahr zusammen dargestellt wird. Bedingte Formate oder das Zählen von Urlaubstagen ist so einfacher.

Aber in vielen  Firmen sind Urlaubslisten in anderer Form seit langem gebräuchlich. Die Monate stehen untereinander.

 

15.01.2018_ 0000

 

Das macht alles etwas umständlicher – aber auch hier gibt es Tricks, um sich das Leben zu erleichtern.

Achtung! Ich versuche in diesem Beispiel so wenig “komplizierte” Funktionen wie möglich zu verwenden – das Beispiel ist nicht gerade für Anfänger gedacht, aber auch ohne tiefschürfende Excel-Kenntnisse sollten Sie das Beispiel nachbauen können.

Beginnen Sie mit der ersten Zeile für den Januar. In A1 schreiben Sie 01.01.2018 (bzw. den 1. Januar des aktuellen Jahres). Klicken Sie mit der rechten Maustaste auf A1 und wählen Sie “Zellen formatieren”. Die Kategorie “Benutzerdefiniert” anklicken und dann das Format MMMM eintragen – damit wird der Monatsname ausgeschrieben.

In B1 schreiben Sie =A1 – jetzt steht auch hier “Januar”. Rufen Sie erneut das benutzerdefinierte Zahlenformat auf und formatieren Sie mit TT – Sie bekommen jetzt 01 für den 01.  Januar.

In C1 schreiben Sie =B1+1 und füllen diese Formel nach rechts aus, bis Sie beim 31. angekommen sind.

Markieren Sie die Spalten B bis AF und machen Sie sie schmaler. Jetzt sieht Ihre Tabelle so aus:

 

15.01.2018_ 0001

 

Schreiben Sie ab A2 ein paar Namen untereinander. Sie brauchen zum Kopieren einen einigermaßen repräsentativen Block mit Namen.

Markieren Sie B1 bis AF1 – jetzt sollen die Wochenenden automatisch formatiert werden. Ein Wochenende sind Samstag und Sonntag; in Deutschland beginnt die Woche mit dem Montag – das ist Tag 1. Freitag ist der Tag 5, Samstag und Sonntag haben die Nummern 6 und 7. Wir werden alles grau markieren, was größer als Wochentag 5 ist. Die Funktion lautet =WOCHENTAG(b1;11). Die Ziffer “11” steht für das System mit Montag als erstem Tag.

  • Klicken Sie auf “Start | Bedingte Formatierung” und wählen Sie “Neue Regel”.
  • Klicken Sie auf “Formel zur Ermittlung der zu formatierenden Zellen verwenden”.
  • Klicken Sie in die Zeile unter “Werte formatieren, für die diese Formel wahr ist”.
  • Tippen Sie die Formel ein =wochentag(B$1;11)>5
    Achten Sie unbedingt auf den gemischten Zellbezug! Die erste Zeile muss fixiert sein!
  • Klicken Sie auf “Formatieren” und suchen Sie sich unter “Ausfüllen” eine passende Füllfarbe.

Feiertage müssen Sie von Hand markieren; zum einen ist das Berechnen nicht so einfach, dass es hier in diesem Blog Platz hat. Das beschreibe ich mal zu einem anderen Zeitpunkt. Zum anderen sind die Feiertage für die Bundesländer unterschiedlich (hallo nach Berlin!).

Jetzt sieht die Tabelle so aus:

 

15.01.2018_ 0002

 

Sehen Sie in Spalte A meine Namen? Es sind Nach- und Vorname in einer Zelle – das mache ich, damit ich später nach diesen Namen suchen kann. Ich möchte schlussendlich alle Urlaubstage pro Mitarbeiter über alle Monate addieren.

Die bedingte Formatierung soll noch ausgefüllt werden für die Zeilen zwei bis fünf. Rufen Sie die bedingte Formatierung wieder auf: “Start | Bedingte Formatierung”. Klicken Sie auf “Regeln verwalten”. Markieren Sie Ihre Regel und erweitern Sie den Bezug bis AF5 (oder mehr, wenn Sie mehr Namen haben). Klicken Sie auf “Übernehmen” und kontrollieren Sie, ob alle Zeilen auch richtig formatiert werden:

 

15.01.2018_ 0003

Der erste Block ist jetzt fast fertig. Später sollen “u” eingetragen werden für Urlaubstage – diese “u” sollen in Spalte AG gezählt werden.

Klicken Sie in AG2 und tippen Sie
=zählenwenn(B2:AF2;”u”)

Füllen Sie diese Formel nach unten bis zum letzten Namen aus:

15.01.2018_ 0004

 

Markieren Sie A1 bis AG5, kopieren Sie die Zellen und fügen Sie sie unten wieder ein. Das sieht jetzt erst mal so aus:

15.01.2018_ 0005

 

Sie haben den Januar zweimal, lassen Sie den  Block markiert. Rufen Sie die  Formatierung auf, markieren Sie die Regel für diesen Bock und wählen Sie “Regel bearbeiten”. Ändern Sie B$1 in B$8.

Klicken Sie in A8 und tippen Sie 01.02.2018 – alles ändert sich für den Februar. Löschen Sie am Ende die Zellen, die über den 28.02. hinausgehen – löschen Sie Eintrag und Format mit “Start | Löschen | Alle löschen”.

Kopieren Sie jeweils den Januar für den nächsten Monat (er hat 31 Tage) und ändern Sie immer die Zelle mit dem Monatsnamen sowie die Zeile für die bedingte Formatierung. Löschen Sie überflüssige Tage am Ende (immer daran denken: Inhalt und Format löschen! Sie müssen auch die bedingten Formate loswerden).

Jetzt fehlt noch die Addition aller “u” für die Mitarbeiter – das zeige ich Ihnen am Montag. Und gleich noch einen Tipp, wie Sie diese Tabelle für das nächste Jahr sehr schnell anpassen können. Dann müssen Sie diese ganzen Schritten nicht zweimal machen.

Hinterlasse jetzt einen Kommentar

Kommentar hinterlassen

E-Mail Adresse wird nicht veröffentlicht.


*