Galerie
Arbeitszeiterfassung mit Microsoft Excel
Zeiterfassung mit Excel ist in vielen Betrieben gang und gäbe, aber die dazu eingesetzten Tabellen sind häufig echte Zeitfresser. In diesem Workshop stellen wir Ihnen eine echte Alternative vor, die Sie sofort nutzen können.
Arbeitszeiterfassung mit Microsoft Excel
TIPP 1: Eingabe per Auswahllisten
Um die Eingabe für den Anwender so bequem wie möglich zu gestalten, sind in dem Arbeitsblatt verschiedene Auswahllisten hinterlegt. Diese Listen werden automatisch aktiviert, sobald der Benutzer eine der betreffenden Zellen selektiert. Das Anlegen solcher Listen funktioniert wie folgt.
Geben Sie die gewünschten Listeneinträge am besten auf einem separaten Blatt der Arbeitsmappe ein. Markieren Sie dann die fertige Liste und klicken Sie in der Registerkarte "Formeln" auf "Namen definieren". Es erscheint ein kleines Dialogfeld, in dem Sie einen Namen für die Liste eintippen müssen. Anschließend markieren Sie die Zelle, die die Auswahlliste erhalten soll und klicken in der Registerkarte "Daten" auf "Datenüberprüfung".
Im nun angezeigten Dialog stellen Sie auf der Registerkarte "Einstellungen" das Feld "Zulassen" auf "Liste" und tippen dann im Feld "Quelle" ein Gleichheitszeichen und den Namen der Liste ein. Anschließend können Sie noch auf den beiden übrigen Registerkarten entscheiden, ob die Zelle einen Ausfüllhinweis anzeigen soll und ob die Eingabe auf die Listenwerte beschränkt werden soll.
Die Highlights der Tabelle
Wir zeigen in unserem Beispiel:
- Eingabe von Jahr, Monat und Zeitangaben über Listenfelder
- Automatische Erkennung der Monatslänge
- Automatische Hervorhebung der Wochenenden
- Rechnen mit Zeitangaben (auch über Mitternacht)
- Löschfunktion per Makro mit grafischer Schaltfläche
- Schutz vor ungewollten Änderungen
So sieht die Bedingung und die zugehörige Formatierung für die Darstellung des Samstags aus.
TIPP 2: Kennzeichnen der Wochenenden
Für die farbliche Hinterlegung der Samstage und Sonntage kommt die bedingte Formatierung von Excel zum Einsatz. Dabei steuern Sie die Formatierung der Zellen über eine oder auch mehrere Bedingungen. In diesem Fall ist der Wochentag eines Datums das entscheidende Kriterium für die Farbe der betreffenden Tabellenzeile. Diese Information lässt sich leicht über die Funktion WOCHENTAG() bestimmen, die einen Wert zwischen 1 (Sonntag) und 7 (Samstag) liefert. Da in diesem Fall nicht nur einzelne Zellen, sondern mehrere Tabellenzeilen eingefärbt werden sollen, müssen Sie zunächst den betreffenden Bereich der Tabelle markieren (hier: B6:J36).
Anschließend rufen Sie den Befehl "Start/Bedingte Formatierung/Regeln verwalten" auf. Im angezeigten Dialog klicken Sie auf "Neue Regel" und wählen im nächsten Fenster die Option "Formel zur Ermittlung der zu formatierenden Zellen verwenden". Bei der Formulierung der Bedingung müssen Sie darauf achten, dass sich die Zelle, deren Wert die Formatierung steuern soll, in der ersten Spalte des markierten Bereichs befindet. Andernfalls kann Excel die Formatierung nicht korrekt auf die Nachbarzellen übertragen.
Anschließend stellen Sie noch die gewünschte Formatierung ein und beenden das Dialogfeld mit "OK". Nun erstellen Sie nach dem gleichen Muster weitere Regeln für die Sonntage, die Werktage und die leeren Anschließend stellen Sie noch die gewünschte Formatierung ein und beenden das Dialogfeld mit "OK". Nun erstellen Sie nach dem gleichen Muster weitere Regeln für die Sonntage, die Werktage und die leeren Zeilen am Tabellenende von Monaten mit weniger als 31 Tagen. Das ist zugegebenermaßen nicht ganz einfach, aber das Ergebnis ist die Mühe wert.
Arbeitszeiterfassung mit Microsoft Excel
TIPP 3: Monatsende bestimmen
Wenn Sie in der Zelle C3 einen Monat auswählen, werden Sie feststellen, dass in der Tabelle automatisch die richtige Anzahl an Tagen eingetragen wird. Hinter diesem Service steckt im Wesentlichen die Funktion MONATSENDE(), die zu einem gegebenen Datum das Datum des dazugehörigen Monatsletzten liefert. Zum Beispiel liefert die Formel "MONATSENDE("1.5.2010";0)" den Datumswert 31.5.2010.
Der zweite Parameter dient dabei zur Auswahl des gewünschten Monats und steuert, wie viele Monate Excel in die Vergangenheit (negative Werte) oder in die Zukunft (positive Werte) gehen soll. Aus dem so gewonnenen Datum lässt sich dann mithilfe der Funktion TAG() die Anzahl der Tage eines Monats bestimmen (hier: 31). Dieser Wert steuert dann letztendlich, ob die Tage vom 29. bis zum 31. angezeigt werden oder nicht. Dazu wird er mit einer fortlaufenden Nummerierung in der Spalte A verglichen, die wir durch eine weiße Textfarbe "versteckt" haben.
Hinweis: Falls Sie mit einer älteren Version von Excel arbeiten, müssen Sie diese Funktionen erst aktivieren. Wählen Sie dazu den Befehl "Extras/Add-Ins" und schalten Sie im angezeigten Dialog die Option "Analyse- Funktionen" ein. Anschließend müssen Sie die aktuelle Arbeitsmappe noch schließen und wieder öffnen, damit Excel das Add-in lädt.
Falls Sie eine Arbeitsmappe, die Analyse-Funktionen enthält, an andere Benutzer weitergeben, müssen diese das Add-in ebenfalls aktivieren, bevor sie Ihre Arbeitsmappe nutzen können. Andernfalls zeigt Excel in den entsprechenden Zellen einen Fehler an.
So sieht der Stundenzettel unseres Beispiels aus.
TIPP 4: Mit Uhrzeiten rechnen
Uhrzeiten werden in Excel als Zahl zwischen 0 und 1 gespeichert, wobei die 1 für 24:00 Uhr steht. Ein Wert von 0,5 entspricht also 12:00, 0,25 steht für 6:00 usw. Genau genommen speichert Excel in einer Zelle sogar immer ein Datum und eine Uhrzeit: Das Datum wird dabei durch die Vorkommastellen und die Uhrzeit durch die Nachkommastellen repräsentiert.
Dieses Prinzip lässt sich leicht nachvollziehen, indem Sie in einer Zelle eine beliebige Zahl zwischen 0 und 1 eingeben und diese dann mit dem Format "Zeit" belegen (Registerkarte "Start", Gruppe "Zahl"). Allerdings zeigt Excel den Wert dabei sekundengenau an, was für unsere Zwecke eher störend ist. Für die hier gewünschte Zeiterfassung sollten die Ausgabe der Sekunden besser unterdrückt werden.
Dazu klicken Sie die Zelle mit der rechten Maustaste an, wählen im Kontextmenü "Zellen formatieren" und stellen dann im angezeigten Dialog auf der Registerkarte "Zahlen" die gewünschte Uhrzeit-Variante ein. Durch diese Art der Darstellungen können Sie Uhrzeiten in Excel einfach addieren. Um zum Beispiel einer bestimmten Uhrzeit eine Stunde hinzuzufügen, addieren Sie 1/24, also zum Beispiel "=JETZT()+1/24". Natürlich müssen Sie dann auch die Ergebniszelle so formatieren, dass Excel den berechneten Wert als Uhrzeit anzeigt.
Stunden addieren und multiplizieren
TIPP 4a:
5 SCHRITTE: Stunden addieren und multiplizieren
Beim Erfassen und Auswerten von Arbeitszeiten müssen Sie nicht nur die Differenz zweier Zeitangaben berechnen, sondern diese Ergebnisse auch summieren und eventuell mit einem Stundenlohn multiplizieren.
Stunden addieren und multiplizieren
TIPP 4b:
Geben Sie die Uhrzeiten im Format "hh:mm" ein. Die tägliche Arbeitsdauer lässt sich dann durch eine einfache Subtraktion berechnen (siehe Zelle D3). Die in D8 ermittelte Gesamtsumme scheint jedoch falsch zu sein.
Stunden addieren und multiplizieren
TIPP 4c:
Das ist nur ein Formatproblem. Klicken Sie auf "Start" und in der Gruppe "Zahl" auf die Schaltfläche in der rechten unteren Ecke. Im angezeigten Dialog wählen Sie "Benutzerdefiniert" und tragen dann im Feld "Typ" [h]:mm ein.
Stunden addieren und multiplizieren
TIPP 4d:
Um aus einer Uhrzeit 00:15 eine Stundenanzahl zu erhalten, multiplizieren Sie die Uhrzeit einfach mit 24. Eine Viertelstunde, intern als 0,0104167 dargestellt, liefert nach der Multiplikation mit 24 zum Beispiel den Wert 0,25.
Stunden addieren und multiplizieren
TIPP 4e:
Diese Zeitangaben (Spalte E) lassen sich jetzt problemlos mit dem Stundensatz "C10" multiplizieren und ergeben den Tageslohn. In der Tabelle finden Sie die entsprechenden Werte in der Spalte F.
Hier können Sie detailliert einstellen, welche Daten oder Eigenschaften vor Veränderungen geschützt werden sollen.
TIPP 5: Formeln schützen
Damit unbedarfte Benutzer die (oft mühselig erstellten) Formeln eines Arbeitsblatts nicht versehentlich löschen bzw. ändern, lassen sich die betreffenden Zellen des Blattes gezielt vor Veränderungen schützen. Bei Bedarf können Sie sogar verhindern, dass sich die hinterlegten Formeln anzeigen lassen. Standardmäßig sind in Excel alle Zellen eines Arbeitsblattes so eingestellt, dass sie bei aktiviertem Blattschutz gesperrt sind.
Diese Voreinstellung ist durchaus sinnvoll, da auf einem Tabellenblatt in der Regel nur einige wenige Zellen freigegeben werden müssen und der große Rest gesperrt werden kann. Allerdings lassen sich die Formeln auch bei gesperrtem Arbeitsblatt noch anzeigen (z.B. per "Strg+#"). Um bei einem Arbeitsblatt das Anzeigen der Formeln generell zu verhindern und eine Eingabe lediglich für einige ausgewählte Zellen zuzulassen, gehen Sie folgendermaßen vor: Markieren Sie das gesamte Arbeitsblatt mit Strg+A und klicken Sie die Markierung mit der rechten Maustaste an. Wählen Sie im Kontextmenü "Zellen formatieren" und aktivieren Sie im Dialog auf der Registerkarte "Schutz" die Optionen "Gesperrt" und "Ausgeblendet". Anschließend beenden Sie den Dialog mit "OK".
Jetzt markieren Sie die Zellen, die der Anwender noch bearbeiten können soll. Halten Sie dabei die Strg-Taste gedrückt, damit Sie die Zellen auf einen Rutsch freigeben können. Klicken Sie dazu die Markierung wieder mit der rechten Maustaste an, wählen Sie "Zellen formatieren", und schalten Sie auf der Registerkarte "Schutz" die Option "Gesperrt" aus.
Nun können Sie den Schutzmechanismus via "Überprüfen/ Blatt schützen" aktivieren. Im Dialogfeld des Befehls lässt sich genau festlegen, welche Aktionen der Benutzer in den ungesperrten Zellen noch vornehmen kann. Bei Bedarf vergeben Sie noch ein Kennwort, dass dann zum Aufheben des Schutzes eingegeben werden muss.
Arbeitszeiterfassung mit Microsoft Excel
TIPP 6: Einträge per Makro löschen
Als kleines Extra dieser Excel-Lösung lassen sich die eingegebenen Arbeitszeiten bequem über eine Schaltfläche löschen, damit das Anlegen eines neuen Monats möglichst komfortabel wird. Hinter dieser Funktionalität verbirgt sich ein kleines Makro, das vor dem irreversiblen Löschvorgang noch eine Sicherheitsabfrage anzeigt. So ein Makro ist auch ohne Programmierkenntnisse schnell erstellt.
Klicken Sie einfach unten links in der Statuszeile die Schaltfläche des Makrorekorders an und geben Sie im angezeigten Dialog einen Namen für das zu erstellende Makro ein. Dann markieren Sie den Tabellenbereich, indem Sie auf der Registerkarte "Start" den Befehl "Suchen und Auswählen/Gehe zu" aufrufen und dort den Eintrag "Eingabebereich" auswählen. Dadurch werden alle Eingabefelder der Tabelle markiert.
Jetzt lassen sich die Felder mit "Entf" auf einen Schlag löschen. Anschließend beenden Sie die Aufzeichnung des Makrorekorders mit einem Klick auf seinen Stop-Button (unten links) und sind fertig.
Zum Aufruf des Löschmakros soll hier keine simple Standard-Schaltfläche benutzt werden, sondern eine kleine Grafik. Dazu fügen Sie einfach eine geeignete Grafik bzw. ClipArt ein, die Sie bei Bedarf noch formatieren können, um sie optisch besser zu integrieren. In unserem Beispiel haben wir ihr einen farblich passenden Rahmen und einen Schatteneffekt spendiert.
Nach diesen Vorarbeiten klicken Sie die Grafik mit der rechten Maustaste an und rufen in ihrem Kontextmenü den Befehl "Makro zuweisen" auf. Im angezeigten Dialog markieren Sie das zuvor erstellte Makro und schließen das Fenster mit "OK".
Wenn Sie nun die Grafik anklicken, führt Excel das Makro ohne Nachfrage aus. Da das in der Praxis nicht ganz ungefährlich ist, haben wir das Makro noch um eine Sicherheitsabfrage erweitert.
