Excel für Personal und HR: drei Aufgaben aus dem Personalalltag

Kurz gesagt: Für Excel im Personalbereich brauchst du vor allem Zählen mit Bedingung, Rechnen mit Grenzen und feste Bezüge. Damit lassen sich Headcount, Überstunden und Resturlaub zuverlässig auswerten, auch wenn das HR-System solche Berichte nicht direkt liefert.

Welche Excel-Kenntnisse Personaler brauchen

Viele HR-Daten liegen in einem Personal- oder Zeiterfassungssystem. Sobald aber eine Frage kommt, für die es keinen fertigen Bericht gibt, landen sie in Excel. Diese Werkzeuge brauchst du dafür am häufigsten:

• ZÄHLENWENN und ZÄHLENWENNS für Köpfe je Abteilung, Standort oder Vertragsart.
• WENN für Regeln wie Überstunden, Probezeit oder Zuschläge.
• Absolute Bezüge, damit Sollstunden oder Urlaubsanspruch zentral in einer Zelle stehen.
• Datumsfunktionen für Eintrittsdatum, Betriebszugehörigkeit und Fristen.
• SVERWEIS, um Abteilung oder Kostenstelle zur Personalnummer zu holen.
• Textfunktionen, um Namen und Kennungen aus Exporten zu bereinigen.
• MITTELWERT für Durchschnittswerte wie Fehltage oder Stunden.
• PivotTables für Übersichten nach Abteilung und Monat.

Die Praxisaufgabe: Monatsauswertung in der Personalabteilung

Du arbeitest in der Personalabteilung eines Unternehmens mit mehreren Abteilungen. Am Monatsende kommen drei Anfragen: Die Geschäftsführung will den Headcount je Abteilung, die Lohnbuchhaltung braucht die Überstunden, und die Teamleitungen wollen wissen, wie viele Urlaubstage ihre Leute noch haben. Alle drei Fragen beantwortest du mit einer Formel, die für die ganze Liste funktioniert.

Schritt 1: Headcount je Abteilung

Die Mitarbeiterliste enthält Name, Abteilung und Wochenstunden. Die gesuchte Abteilung steht in einer eigenen Zelle, damit du schnell zwischen Abteilungen wechseln kannst.

Aufgabe Die Geschäftsführung fragt nach dem Headcount je Abteilung. Zähle in F2 mit ZÄHLENWENN, wie viele Mitarbeitende in der Abteilung aus F1 arbeiten.

	A	B	C	D	E	F
1	Name	Abteilung	Std./Woche		Abteilung	Vertrieb
2	Jana Weber	Vertrieb	40		Köpfe
3	Murat Kaya	IT	40
4	Lena Hoffmann	Personal	30
5	Tom Schulz	Vertrieb	40
6	Sara Klein	Produktion	20
7	Ali Demir	Vertrieb	40
8	Mia Wagner	IT	32
9	Felix Braun	Produktion	40

Headcount zählt Köpfe, nicht Stellen. Arbeiten zwei Menschen je 20 Stunden, sind das zwei Köpfe, aber nur eine Vollzeitstelle. Für Vollzeitäquivalente summierst du die Wochenstunden mit =SUMMEWENN(B2:B9;F1;C2:C9) und teilst durch die Stunden einer Vollzeitstelle.

Schritt 2: Überstunden für die Lohnbuchhaltung

Aus der Zeiterfassung kommen Soll- und Ist-Stunden des Monats. Die Lohnbuchhaltung braucht nur die Überstunden. Wer unter dem Soll liegt, darf keine negative Zahl bekommen, sonst würden Minusstunden mit Überstunden anderer verrechnet.

Aufgabe Berechne in D2 die Überstunden des Monats: Ist minus Soll, aber nie weniger als 0. Wer unter dem Soll liegt, hat 0 Überstunden. Die Formel wird bis D7 ausgefüllt.

	A	B	C	D
1	Name	Soll-Std.	Ist-Std.	Überstunden
2	Jana Weber	160	172,5
3	Murat Kaya	160	155
4	Lena Hoffmann	120	126
5	Tom Schulz	160	160
6	Sara Klein	80	91
7	Ali Demir	160	148

MAX mit 0 ist ein kurzer Weg, negative Werte zu kappen. Wenn du die Regel lieber ausgeschrieben siehst, nimm =WENN(C2>B2;C2-B2;0). Für Minusstunden legst du eine zweite Spalte mit =MAX(0;B2-C2) an.

Schritt 3: Resturlaub berechnen

Alle in der Liste haben denselben Jahresanspruch. Er steht einmal oben in einer Zelle. Ändert er sich im nächsten Jahr, passt du nur diese eine Zelle an.

Aufgabe Berechne in D2 den Resturlaub in Tagen: Jahresanspruch aus G1 plus Rest aus dem Vorjahr minus genommene Tage. Die Formel wird bis D7 ausgefüllt und muss in jeder Zeile stimmen.

	A	B	C	D	E	F	G
1	Name	Rest Vorjahr	Genommen	Resturlaub		Jahresanspruch	30
2	Jana Weber	2	18
3	Murat Kaya	0	24
4	Lena Hoffmann	5	12
5	Tom Schulz	0	30
6	Sara Klein	3	9
7	Ali Demir	1	21

In der Praxis ist der Anspruch oft nicht für alle gleich, etwa bei Teilzeit mit weniger Arbeitstagen pro Woche. Dann bekommt jede Person eine eigene Spalte für den Anspruch, und der feste Bezug entfällt.

Typische Excel-Auswertungen im Personalbereich

Auswertung	Worum es geht	Excel-Werkzeuge
Headcount-Bericht	Köpfe je Abteilung, Standort oder Vertragsart	ZÄHLENWENN, ZÄHLENWENNS, PivotTable
Überstundenliste	Mehrarbeit für Abrechnung und Planung	MAX, WENN, SUMMEWENN
Urlaubsübersicht	Anspruch, genommen, Rest je Person	Absolute Bezüge, einfache Formeln, bedingte Formatierung
Fehlzeitenstatistik	Krankheitstage je Abteilung und Monat	SUMMEWENNS, MITTELWERT, PivotTable
Jubiläen und Fristen	Betriebszugehörigkeit, Ende der Probezeit, befristete Verträge	Datumsfunktionen, bedingte Formatierung

Tipps für Personaldateien

Mit Personalnummern arbeiten

Namen ändern sich, und manche kommen doppelt vor. Eine Personalnummer ist eindeutig und eignet sich als Suchwert für SVERWEIS und XVERWEIS.

Einheitliche Abteilungsnamen

„Vertrieb“, „Vertrieb “ mit Leerzeichen und „Sales“ sind für ZÄHLENWENN drei verschiedene Abteilungen. Eine Auswahlliste über die Datenüberprüfung verhindert solche Abweichungen schon bei der Eingabe.

Regeln in Zellen, nicht in Formeln

Jahresanspruch, Sollstunden oder Schwellen für Zuschläge gehören in beschriftete Zellen. So ist nachvollziehbar, womit gerechnet wurde, und eine Änderung betrifft nur eine Stelle.

Datenschutz mitdenken

Personaldaten sind sensibel. Gib nur die Spalten weiter, die jemand für seine Frage braucht, und verschicke für einen Headcount keine Liste mit Namen, wenn eine Zahl je Abteilung reicht.

Häufige Fragen