Daten in Abhängigkeit eines Datums auslesen
Schritt-für-Schritt-Anleitung
Um Daten in Abhängigkeit eines Datums aus einer Excel-Tabelle auszulesen, kannst du die folgende Schritt-für-Schritt-Anleitung nutzen. Diese Anleitung bezieht sich auf Excel 2003, aber die grundlegenden Konzepte sind auch auf neuere Versionen anwendbar.
- Öffne deine Excel-Datei mit den Mitarbeiterdaten (Tabelle1) und erstelle eine neue Tabelle (Tabelle2) für die Abwesenheitsliste.
- Erstelle ein Makro:
- Öffne den VBA-Editor (Alt + F11).
- Füge ein neues Modul hinzu und kopiere den folgenden VBA-Code hinein:
Sub Fehlt()
Dim wksDaten As Worksheet
Dim wksFehlt As Worksheet
Dim datDatum As Date
Dim lngFehlt As Long 'Zeilenzähler im Blatt wksFehlt
Dim lngData As Long 'Zeilenzähler im Blatt wksDaten
Dim SpalteTag As Long 'Spalte des gesuchten Datums
Dim lngSpalte As Long 'Spaltenzähler
'Konstanten für Zeilen und Spalten im Fehlt-Blatt
Const TitelFehlt As Long = 7 'Zeile mit Titeln
Const SpNameFehlt As Long = 2 'Spalte mit Namen
'Konstanten für Zeilen und Spalten im Daten-Kalender Blatt
Const ZeileDatum As Long = 3 'Zeile mit Datum
Const SpalteJan1 As Long = 7 'Spalte mit 1. Januar
Const SpalteName As Long = 4 'Spalte mit Namen
Const ZeileName1 As Long = 5 '1. Zeile mit Namen
Set wksDaten = Worksheets("Tabelle1")
Set wksFehlt = Worksheets("Tabelle2")
With wksFehlt
.Range(.Rows(TitelFehlt + 1), _
.Rows(.Cells(TitelFehlt + 1, 2).End(xlDown).Row)).ClearContents
datDatum = .Range("F1") 'Stichtag
lngFehlt = TitelFehlt 'Zeile mit Spaltentiteln
End With
With wksDaten
'Spalte mit gewünschtem Datum in Zeile 3 ermitteln
For lngSpalte = SpalteJan1 To .Cells(ZeileDatum, .Columns.Count).End(xlToLeft).Column
If .Cells(ZeileDatum, lngSpalte).Value = datDatum Then
SpalteTag = lngSpalte
Exit For
End If
Next
If SpalteTag = 0 Then
MsgBox "Datum " & datDatum & " nicht gefunden im Blatt " & .Name
GoTo Beenden
End If
'Spalte mit Mitarbeiternamen abarbeiten
For lngData = ZeileName1 To .Cells(.Rows.Count, SpalteName).End(xlUp).Row
Select Case .Cells(lngData, SpalteTag).Value
Case "K", "F", "U" 'Einträge die als Fehlt gelten
lngFehlt = lngFehlt + 1
wksFehlt.Cells(lngFehlt, SpNameFehlt).Value = .Cells(lngData, SpalteName).Value
'1. Arbeitstag nach Fehlt-Tag ermitteln
For lngSpalte = SpalteTag To .Cells(ZeileDatum, .Columns.Count).End(xlToLeft).Column
Select Case .Cells(lngData, lngSpalte).Value
Case "K", "F", "U"
Case ""
If Weekday(.Cells(ZeileDatum, lngSpalte)) = vbSaturday Or _
Weekday(.Cells(ZeileDatum, lngSpalte)) = vbSunday Then
ElseIf .Cells(lngData, lngSpalte).Interior.ColorIndex = 45 Then
Else
wksFehlt.Cells(lngFehlt, SpNameFehlt + 1).Value = _
.Cells(ZeileDatum, lngSpalte).Value
Exit For
End If
Case Else
wksFehlt.Cells(lngFehlt, SpNameFehlt + 1).Value = _
.Cells(ZeileDatum, lngSpalte).Value
End Select
Next
Case Else
End Select
Next
End With
Beenden:
End Sub
- Füge in Zelle F1 das gewünschte Datum ein, für das du die Abwesenheiten auslesen möchtest.
- Führe das Makro aus (F5 oder über das Menü) und überprüfe die Ergebnisse in Tabelle2.
Häufige Fehler und Lösungen
Alternative Methoden
Solltest du kein Makro verwenden wollen, kannst du auch mit Excel-Formeln arbeiten, um die Abwesenheiten auszulesen. Zum Beispiel könntest du die WENN-Funktion zusammen mit SVERWEIS oder INDEX und VERGLEICH verwenden, um die Daten dynamisch zu erhalten.
Ein Beispiel könnte so aussehen:
=WENN(SVERWEIS(F1;Tabelle1!A$1:D$100;2;FALSCH)="K";"Abwesend";"Anwesend")
Praktische Beispiele
- Mitarbeiterliste aufbauen: Du kannst die Mitarbeiter in Tabelle1 auflisten und die Abwesenheiten für verschiedene Monate sammeln.
- Kalender erstellen: Nutze eine Tabelle, um die Monate und die entsprechenden Abwesenheiten visuell darzustellen.
Tipps für Profis
- Verwende Named Ranges: Das erleichtert die Verwaltung deiner Daten und macht deine Formeln übersichtlicher.
- Automatisierung: Kombiniere dein Makro mit einem Button in Excel für eine einfachere Benutzeroberfläche.
- Testen: Teste dein Makro regelmäßig, insbesondere nach Änderungen an den Daten, um sicherzustellen, dass alles korrekt funktioniert.
FAQ: Häufige Fragen
1. Was mache ich, wenn ich mehr als 255 Mitarbeiter habe?
Du kannst für jedes Halbjahr eine separate Tabelle anlegen, um die Daten zu verwalten.
2. Wie kann ich Feiertage in meinem Makro berücksichtigen?
Kennzeichne Feiertage in einer separaten Zeile oder Spalte in Tabelle1, sodass das Makro diese Einträge ignorieren kann.
3. Funktioniert das Makro in neueren Excel-Versionen?
Ja, das Makro kann auch in neueren Versionen von Excel verwendet werden, aber einige Anpassungen könnten notwendig sein.