Excel für HR: Mitarbeiterdaten und Analysen

Personalteams verarbeiten täglich enorme Datenmengen – Mitarbeiterdaten, Anwesenheitsprotokolle, Leistungsbewertungen, Gehaltsbänder und Fluktuationskennzahlen. Excel ist weltweit eines der am häufigsten genutzten Werkzeuge in HR-Abteilungen, weil es flexibel, zugänglich und leistungsstark genug ist, um alles von einem zehnköpfigen Start-up bis hin zu einem standortübergreifenden Konzern abzubilden. Dieser Leitfaden führt Sie Schritt für Schritt durch den Aufbau eines praxistauglichen HR-Systems in Excel und behandelt die wichtigsten Vorlagen, Formeln und Analysetechniken für effizienteres Arbeiten.

Ihre Mitarbeiter-Stammdatentabelle einrichten

Jedes HR-Excel-System beginnt mit einer sauberen, gut strukturierten Mitarbeiter-Stammdatentabelle. Betrachten Sie diese als Ihre einzige verlässliche Datenquelle. Jede Zeile repräsentiert einen Mitarbeiter, jede Spalte ein Merkmal.

Empfohlene Spalten für Ihre Stammdatentabelle:

• Mitarbeiter-ID — eine eindeutige Kennung (z. B. EMP-001)
• Vollständiger Name
• Abteilung
• Berufsbezeichnung
• Eintrittsdatum
• Beschäftigungsart (Vollzeit, Teilzeit, Vertrag)
• Vorgesetzter
• Jahresgehalt
• Status (Aktiv, Inaktiv, In Elternzeit)

Verwenden Sie Datenüberprüfung, um Benutzereingaben zu steuern, in Spalten wie Abteilung, Beschäftigungsart und Status. Dies verhindert Tippfehler und sorgt für konsistente Daten – ein entscheidender Schritt, bevor Sie Analysen durchführen.

Benennen Sie Ihre Tabelle (Einfügen → Tabelle, dann vergeben Sie einen Namen wie tblEmployees). Benannte Tabellen erweitern sich automatisch, wenn Sie Zeilen hinzufügen, und machen Ihre Formeln deutlich lesbarer.

Betriebszugehörigkeit und Dienstjahre berechnen

Eine der häufigsten HR-Berechnungen ist die Betriebszugehörigkeit. Die Funktion DATEDIF löst diese Aufgabe elegant:

=DATEDIF(B2, TODAY(), "Y") & " years, " & DATEDIF(B2, TODAY(), "YM") & " months"

Dabei enthält B2 das Eintrittsdatum des Mitarbeiters. Das Ergebnis ist eine lesbare Zeichenkette wie 3 years, 7 months. Wenn Sie nur die Anzahl der vollständigen Jahre für Kategorisierungszwecke benötigen:

=DATEDIF(B2, TODAY(), "Y")

Anschließend können Sie Mitarbeiter mithilfe einer WENN-Funktion mit verschachtelten logischen Tests in Betriebszugehörigkeitsgruppen einteilen:

=IF(E2<1,"New Hire",IF(E2<3,"Junior",IF(E2<7,"Mid-Level","Senior")))

Dabei enthält E2 den Wert der Betriebszugehörigkeit in Jahren. Diese Gruppen sind nützlich für Personalberichte und Fluktuationsanalysen.

Eine Anwesenheits-Tracking-Vorlage erstellen

Ein monatlicher Anwesenheitstracker erfasst die tägliche Anwesenheit aller Mitarbeiter. Richten Sie ihn so ein, dass Mitarbeiter in Zeilen und Kalendertage in Spalten aufgelistet sind.

Mitarbeiter	1-Jun	2-Jun	3-Jun	…	Gesamt Anwesend	Gesamt Abwesend	Anwesenheit %
Jane Doe	P	P	A	…	=COUNTIF(B2:AF2,"P")	=COUNTIF(B2:AF2,"A")	=AG2/22
John Smith	P	L	P	…	=COUNTIF(B3:AF3,"P")	=COUNTIF(B3:AF3,"A")	=AG3/22

Gängige Statuscodes: P = Anwesend, A = Abwesend, L = Urlaub/Freistellung, WFH = Homeoffice. COUNTIF zählt jeden Code unabhängig und liefert eine vollständige Aufschlüsselung pro Mitarbeiter. Dividieren Sie die Gesamtzahl der Anwesenheitstage durch die Arbeitstage im Monat (in der Regel 22), um den Anwesenheitsprozentsatz zu erhalten. Formatieren Sie diese Spalte als Prozent mit einer Nachkommastelle.

Wenden Sie bedingte Formatierung an, um Anwesenheitsdaten farblich darzustellen – Rot für Abwesenheiten, Grün für volle Anwesenheit –, damit Führungskräfte Muster auf einen Blick erkennen.

Gehaltsanalyse mit SUMMEWENN und MITTELWERTWENN

Gehaltsanalysen erfordern häufig die Aggregation von Gehaltsdaten nach Abteilung, Erfahrungsstufe oder Beschäftigungsart. SUMMEWENN und SUMMEWENNS sind ideal für bedingte Summenberechnungen:

=SUMIF(tblEmployees[Department], "Marketing", tblEmployees[Annual Salary])
=AVERAGEIF(tblEmployees[Department], "Marketing", tblEmployees[Annual Salary])
=COUNTIF(tblEmployees[Department], "Marketing")

Um diese Formeln dynamisch zu gestalten (sodass Sie die Abteilung in einer Zelle ändern und alle Ergebnisse sofort aktualisiert werden), ersetzen Sie den fest eingetragenen Text durch einen Zellbezug:

=SUMIF(tblEmployees[Department], H2, tblEmployees[Annual Salary])

Dabei ist H2 eine Dropdown-Liste mit Abteilungsnamen. Dieses Muster bildet das Grundgerüst eines selbstbedienenden HR-Analyse-Mini-Dashboards.

Bewertungssystem für Leistungsbeurteilungen

Eine strukturierte Leistungsbeurteilungstabelle erfasst Bewertungen über mehrere Kompetenzen hinweg und berechnet automatisch eine Gesamtpunktzahl.

Empfohlene Kompetenzspalten: Kommunikation, Teamarbeit, Fachkenntnisse, Führung, Zielerreichung. Bewerten Sie jede Kompetenz auf einer Skala von 1 bis 5. Berechnen Sie eine gewichtete Gesamtpunktzahl:

=SUMPRODUCT(C2:G2, $C$1:$G$1) / SUM($C$1:$G$1)

Dabei enthält Zeile 1 die Gewichtungen für jede Kompetenz (z. B. Kommunikation = 2, Fachkenntnisse = 3 usw.) und Zeile 2 die Bewertungen eines Mitarbeiters. SUMPRODUCT multipliziert jede Bewertung mit ihrer Gewichtung, summiert die Ergebnisse und dividiert durch die Gesamtgewichtung – so erhalten Sie einen echten gewichteten Durchschnitt ohne komplexe verschachtelte Formeln.

Leistungsstufen automatisch zuweisen:

=IF(H2>=4.5,"Outstanding",IF(H2>=3.5,"Exceeds Expectations",IF(H2>=2.5,"Meets Expectations",IF(H2>=1.5,"Needs Improvement","Unsatisfactory"))))

Dabei ist H2 die gewichtete Punktzahl. Verwenden Sie bedingte Formatierung, um die Stufenspalte farblich zu kennzeichnen – das erleichtert die Lektüre von Beurteilungszusammenfassungen in Gruppengesprächen erheblich.

Mitarbeiterdaten mit INDEX VERGLEICH nachschlagen

SVERWEIS ist weit verbreitet, aber INDEX VERGLEICH ist eine überlegene Nachschlagemethode für HR-Daten, da sie in jede Richtung funktioniert und nicht ausfällt, wenn Sie Spalten einfügen.

Berufsbezeichnung anhand der Mitarbeiter-ID abrufen:

=INDEX(tblEmployees[Job Title], MATCH(A2, tblEmployees[Employee ID], 0))

Gehalt anhand des Namens abrufen (nützlich in einem Schnellsuche-Bereich):

=INDEX(tblEmployees[Annual Salary], MATCH(B5, tblEmployees[Full Name], 0))

Kombinieren Sie dies mit einem einfachen Suchbereich auf einem separaten Tabellenblatt, damit HR-Mitarbeiter einen Namen eingeben und sofort das vollständige Profil des Mitarbeiters aus der Stammdatentabelle angezeigt bekommen – ohne Scrollen, ohne manuelle Suche.

Ein PivotTable-HR-Dashboard erstellen

Sobald Ihre Stammdaten sauber und konsistent sind, sind PivotTables die schnellste Methode, um HR-Daten zusammenzufassen. Fügen Sie eine PivotTable aus Ihrer Mitarbeiter-Stammdatentabelle ein und erkunden Sie diese nützlichen Auswertungen:

• Personalbestand nach Abteilung: Zeilen = Abteilung, Werte = ANZAHL der Mitarbeiter-IDs
• Durchschnittsgehalt nach Berufsbezeichnung: Zeilen = Berufsbezeichnung, Werte = MITTELWERT des Jahresgehalts
• Personalbestand nach Beschäftigungsart: Zeilen = Beschäftigungsart, Werte = ANZAHL der Mitarbeiter-IDs
• Verteilung der Betriebszugehörigkeit: Zeilen = Betriebszugehörigkeitsgruppe (die per WENN-Funktion abgeleitete Spalte), Werte = ANZAHL der Mitarbeiter-IDs

Ergänzen Sie jede PivotTable mit einem Diagramm – Balkendiagramme für Personalbestandsvergleiche, ein Kreisdiagramm für die Aufteilung nach Beschäftigungsart. Verbinden Sie mehrere PivotTables mit einem einzelnen Datenschnitt (Einfügen → Datenschnitt), sodass das Anklicken einer Abteilung alle Diagramme gleichzeitig filtert. Dies ist die Grundlage eines wirklich nützlichen dynamischen HR-Dashboards in Excel.

Fluktuationsrate und wichtige HR-Kennzahlen

Die Verfolgung der freiwilligen Fluktuation ist für die Personalplanung unerlässlich. Erstellen Sie ein einfaches Austrittsprotokoll mit den Spalten: Mitarbeiter-ID, Name, Abteilung, Austrittsdatum, Grund (freiwillig / unfreiwillig).

Formel für die monatliche freiwillige Fluktuationsrate:

=COUNTIFS(tblTerminations[Reason],"Voluntary",tblTerminations[Month],B1) / tblEmployees_Count * 100

Dabei ist B1 der ausgewählte Monat und tblEmployees_Count ein benannter Bereich, der den Gesamtpersonalbestand enthält. Die Darstellung dieses Wertes über 12 Monate in einem Liniendiagramm gibt der Führungsebene einen klaren Überblick über Bindungstendenzen – ganz ohne spezialisierte HR-Software.

Weitere Kennzahlen, die im selben Dashboard verfolgt werden sollten:

• Durchschnittliche Tage bis zur Besetzung offener Stellen (aus dem Stellenausschreibungsprotokoll)
• Abwesenheitsrate nach Abteilung (aus dem Anwesenheitstracker)
• Schulungsstunden pro Mitarbeiter (aus einem Schulungsprotokoll)
• Verteilung der Leistungsbewertungen (aus den Beurteilungsdaten)

Wiederkehrende HR-Berichte automatisieren

Monatliche Personalberichte, Anwesenheitszusammenfassungen und Gehaltskosten-Übersichten folgen jeden Monat derselben Struktur. Anstatt sie manuell neu zu erstellen, empfiehlt sich eine Automatisierung. Excel-Automatisierung mit Power Automate kann die Berichtserstellung auslösen, E-Mail-Benachrichtigungen senden, wenn die Anwesenheit unter einen Schwellenwert fällt, oder fertiggestellte Tabellenblätter automatisch nach SharePoint kopieren – alles ohne eine einzige Zeile Code.

Für Teams, die mit Makros vertraut sind, ermöglicht die Berichtsautomatisierung mit Excel VBA die Erstellung von Schaltflächen, die auf Knopfdruck Daten aktualisieren, Formatierungen anwenden und PDFs in Sekunden exportieren.

KI zum schnelleren Erstellen von HR-Formeln nutzen

Das Erstellen komplexer HR-Formeln – insbesondere verschachtelter WENN-Funktionen, SUMPRODUCT-Bewertungsmodelle oder mehrbedingter ZÄHLENWENNS – kann zeitaufwendig und fehleranfällig sein. Wenn Sie nicht weiterkommen, können Sie mit GPTExcel auf einfache Weise beschreiben, was Sie benötigen, und erhalten sofort eine einsatzbereite Formel. Zum Beispiel: „Berechne den gewichteten Durchschnitt der Leistungsbewertung, wobei die Kompetenzgewichtungen in Zeile 1 und die Bewertungen in C2:G2 stehen" – und die korrekte SUMPRODUCT-Formel erscheint sofort zum Einfügen.

Sie können auch KI-gestützte Datenanalyse in Excel erkunden, um noch weiter zu gehen – und Muster in Ihren HR-Daten zu erkennen, die bei der manuellen Analyse möglicherweise übersehen werden.

Häufig gestellte Fragen

Was ist die beste Excel-Formel zur Berechnung der Betriebszugehörigkeit?

Verwenden Sie DATEDIF(Startdatum; HEUTE(); "Y"), um die vollständigen Dienstjahre zu ermitteln. Für ein detaillierteres Ergebnis mit Jahren und Monaten kombinieren Sie zwei DATEDIF-Aufrufe: =DATEDIF(B2,TODAY(),"Y") & " yrs " & DATEDIF(B2,TODAY(),"YM") & " mo". Diese Formel wird jedes Mal automatisch aktualisiert, wenn die Datei geöffnet wird.

Wie verfolge ich die Anwesenheit mehrerer Mitarbeiter in Excel?

Erstellen Sie ein monatliches Tabellenblatt mit Mitarbeitern in Zeilen und Datumsangaben in Spalten. Tragen Sie Statuscodes (P, A, L) in jede Zelle ein. Verwenden Sie ZÄHLENWENN, um jeden Status pro Mitarbeiter zu summieren, und ZÄHLENWENNS, um nach Abteilung zusammenzufassen. Wenden Sie bedingte Formatierung an, um Abwesenheiten rot hervorzuheben, damit diese bei der Durchsicht schnell auffallen.

Kann Excel dedizierte HR-Software ersetzen?

Für kleine bis mittlere Teams (bis zu einigen hundert Mitarbeitern) kann Excel grundlegende HR-Funktionen effektiv abdecken: Mitarbeiterdaten, Anwesenheit, Leistungsbeurteilungen und grundlegende Analysen. Bei großen Unternehmen mit komplexen Anforderungen an Gehaltsabrechnung, Benefits oder Compliance ist dedizierte HRIS-Software angemessener – aber Excel bleibt bei diesen Systemen für Ad-hoc-Analysen und Berichte unverzichtbar.

Wie schütze ich sensible Mitarbeiterdaten in einer Excel-HR-Datei?

Verwenden Sie den Blattschutz (Überprüfen → Blatt schützen), um Formelzellen zu sperren und gleichzeitig Dateneingabezellen bearbeitbar zu lassen. Nutzen Sie den kennwortgeschützten Arbeitsmappenschutz (Datei → Informationen → Arbeitsmappe schützen), um das Öffnen der Datei einzuschränken. Erwägen Sie bei Gehaltsspalten, diese Tabellenblätter separat auszublenden und zu schützen, und teilen Sie mit Führungskräften nur Zusammenfassungsansichten statt der vollständigen Stammdatei.