Wie kann man Wochenenden und Feiertage in Excel hervorheben?

Beim Arbeiten mit täglichen Aufzeichnungstabellen in Excel ist es oft hilfreich, Wochenenden oder gesetzliche Feiertage visuell von regulären Wochentagen zu unterscheiden, um eine einfache Datenüberprüfung zu ermöglichen. Eine solche Hervorhebung ist besonders nützlich für Anwesenheitslisten, Projektzeitpläne oder Geschäftstagebücher, wo die Unterscheidung zwischen Arbeitstagen und Nicht-Arbeitstagen sowohl bei der visuellen Verwaltung als auch bei nachfolgenden Analysen hilft. Excel bietet eine Reihe von Methoden, um dies zu erreichen, einschließlich bedingter Formatierung, benutzerdefinierter Hilfsformelspalten oder fortgeschrittener Automatisierung mit VBA. Die Wahl einer geeigneten Lösung kann von Ihrer Tabellenstruktur, wie oft neue Daten hinzugefügt werden, und ob Feiertagslisten sich häufig ändern, abhängen.

• Wochenenden und Feiertage mit bedingter Formatierung hervorheben
• Excel-Formel – Verwenden Sie eine Hilfsspalte, um Wochenenden und Feiertage zu markieren
• VBA-Code – Automatisches Schattieren von Wochenenden und Feiertagen

Wochenenden und Feiertage mit bedingter Formatierung hervorheben

Excels Funktion der bedingten Formatierung ermöglicht es Ihnen, automatisch Wochenenden (typischerweise Samstage und Sonntage) und Feiertage innerhalb Ihrer Datensätze hervorzuheben – ohne dass Berechnungen in Ihre Daten eingefügt werden müssen. Diese Methode eignet sich für Benutzer, die ihre Arbeitsblätter häufig überprüfen oder aktualisieren und Farbhinweise für Nicht-Arbeitstage wünschen, die sich sofort aktualisieren, wenn sich Daten oder Feiertage ändern.

Um die bedingte Formatierung für Wochenenden und Feiertage einzurichten, folgen Sie diesen Anweisungen:

1. Wählen Sie den Bereich mit den zu markierenden Daten aus.

2. Gehen Sie zur Registerkarte Start, klicken Sie auf Bedingte Formatierung und wählen Sie Neue Regel, um den Regel-Editor zu öffnen. Siehe Screenshot:

3. Im Dialogfeld Neue Formatierungsregel:

• Wählen Sie Formel verwenden, um festzulegen, welche Zellen formatiert werden sollen, aus der Liste der Regeltypen.
• Geben Sie im Formeleingabefeld die folgende Formel ein: =NETTOARBEITSTAGE($A2,$A2,$F$2:$F$6)=0
• Klicken Sie auf die Formatieren-Schaltfläche, um Ihren Hervorhebungsstil festzulegen.

4. Wählen Sie im Fenster Zellen formatieren den Reiter Füllung und wählen Sie eine Hintergrundfarbe für Wochenenden und Feiertage. Diese Farbe unterscheidet Nicht-Arbeitstage von regulären Tagen zur schnellen visuellen Orientierung.

5. Klicken Sie zweimal auf OK, um die Dialogfelder zu schließen. Ihre gewählte Hervorhebung wird automatisch für jedes Datum angezeigt, das Wochenenden oder aufgelistete Feiertage innerhalb des ausgewählten Bereichs entspricht. Feiertage können jederzeit durch Bearbeiten Ihres Feiertagsbereichs aktualisiert werden, und die Formatierung wird entsprechend aktualisiert.

Entfesseln Sie die Magie von Excel mit Kutools AI

• Intelligente Ausführung: Führen Sie Zellenoperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles angetrieben durch einfache Befehle.
• Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe zu optimieren.
• VBA-Codierung: Schreiben und implementieren Sie VBA-Code mühelos.
• Formelinterpretation: Verstehen Sie komplexe Formeln mit Leichtigkeit.
• Textübersetzung: Überwinden Sie Sprachbarrieren in Ihren Tabellen.

Erweitern Sie Ihre Excel-Fähigkeiten mit KI-gestützten Tools. Laden Sie jetzt herunter und erleben Sie eine Effizienz wie nie zuvor!

Excel-Formel – Verwenden Sie eine Hilfsspalte, um Wochenenden und Feiertage zu markieren

Für Benutzer, die mehr Kontrolle über die Farbcodierung haben möchten oder nach Wochentagen oder Nicht-Arbeitstagen sortieren/filtern möchten, ist das Hinzufügen einer Hilfsspalte mit Formeln eine zuverlässige Wahl. Diese Technik kennzeichnet jede Zeile klar als Wochenende, Feiertag oder regulären Tag und kann verwendet werden, um zu filtern, manuell zu formatieren oder Ihre Daten zusammenzufassen.
Diese Methode ist besonders nützlich in Szenarien, in denen allein die Farbcodierung nicht ausreicht, oder wenn Sie direkt Gesamtwerte für Wochenenden/Feiertage berechnen möchten.

Angenommen, Ihre Daten befinden sich in Spalte A ab Zeile 2 und Feiertage sind in F2:F6 aufgelistet. Fügen Sie eine neue Spalte neben Ihrer Datums-Spalte ein und geben Sie eine Überschrift wie „Tag-Typ“ ein.

1. Geben Sie in Zelle B2 (die Hilfsspalte) die folgende Formel ein:

=IF(OR(WEEKDAY(A2,2)>=6,COUNTIF($F$2:$F$6,A2)>0),"Weekend/Holiday","Workday")

2. Ziehen Sie dann den Ausfüllkursor nach unten, um die Formel auf andere Zeilen anzuwenden. Sobald sie ausgefüllt ist, können Sie die Tabelle nach „Wochenende/Feiertag“ filtern, um diese Zeilen zu isolieren oder manuell zu markieren. Sie können auch bedingte Formatierung basierend auf dieser Spalte anwenden, falls Sie Farben benötigen.

VBA-Code – Automatisches Schattieren von Wochenenden und Feiertagen

Wenn Sie häufig Datumsbereiche aktualisieren müssen oder wenn Sie den Prozess des Hervorhebens von Wochenenden und Feiertagen über das hinaus automatisieren möchten, was Formeln und bedingte Formatierung leisten können, ist die Verwendung eines einfachen VBA-Makros sehr effizient. Dies ist ideal für Benutzer, die längere Listen oder wiederholte Arbeitsblattgenerierungen verwalten.

Mit einem Makro hebt Excel sofort Wochenenden und Feiertage basierend auf Ihrem gewählten Datumsbereich und Ihrer Feiertagsliste hervor. Sie können die Hervorhebungsfarben im Code bei Bedarf ändern und das Makro erneut ausführen, wann immer sich Ihre Daten ändern.

1. Klicken Sie auf Entwicklertools > Visual Basic. Wählen Sie im Fenster Microsoft Visual Basic for Applications Einfügen > Modul.

2. Kopieren und fügen Sie den folgenden Code in das neu erstellte Modul ein:

Sub ShadeWeekendsAndHolidays()
    Dim rngDates As Range
    Dim rngHolidays As Range
    Dim cell As Range
    Dim xTitleId As String
    
    xTitleId = "KutoolsforExcel"
    On Error Resume Next
    Set rngDates = Application.InputBox("Select the range with dates:", xTitleId, Selection.Address, Type:=8)
    Set rngHolidays = Application.InputBox("Select the range with holiday dates:", xTitleId, , Type:=8)
    On Error GoTo 0
    
    If rngDates Is Nothing Then Exit Sub
    If rngHolidays Is Nothing Then Exit Sub
    
    Application.ScreenUpdating = False
    
    For Each cell In rngDates
        If IsDate(cell.Value) Then
            If Weekday(cell.Value, vbMonday) >= 6 Or Not IsError(Application.Match(CDbl(cell.Value), rngHolidays, 0)) Then
                cell.Interior.Color = RGB(255, 199, 206) ' Light red fill; adjust as needed
            Else
                cell.Interior.ColorIndex = xlNone ' Remove fill from regular days
            End If
        End If
    Next cell
    
    Application.ScreenUpdating = True
End Sub

3. Drücken Sie die F5-Taste, um diesen Code auszuführen, ein Prompt wird Sie auffordern, Ihre Datumszellen auszuwählen, dann Ihre Liste der Feiertagszellen. Folgen Sie den Anweisungen auf dem Bildschirm.

Das Makro hebt Wochenenden (Samstag/Sonntag) und Feiertage (gemäß Ihrer Liste) mit einer Füllfarbe hervor. Passen Sie RGB(255,199,206) im Code an, um Ihre bevorzugte Farbe zu verwenden, falls gewünscht.

Weitere Artikel: