Wie kann man Wochenenden und Feiertage in Excel hervorheben?
Beim Arbeiten mit täglichen Datentabellen in Excel ist es oft nützlich, Wochenenden oder gesetzliche Feiertage visuell von normalen Wochentagen zu unterscheiden, um eine einfache Datenüberprüfung zu ermöglichen. Eine solche Hervorhebung ist besonders hilfreich für Anwesenheitslisten, Projektzeitpläne oder Geschäftstagebücher, wo die Unterscheidung zwischen Arbeitstagen und Nicht-Arbeitstagen sowohl der visuellen Verwaltung als auch der nachfolgenden Analyse dient. 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, der Häufigkeit, mit der neue Daten hinzugefügt werden, und davon abhängen, ob sich Feiertagslisten häufig ändern.
- Wochenenden und Feiertage mit bedingter Formatierung hervorheben
- Excel-Formel – Verwenden Sie eine Hilfsspalte, um Wochenenden und Feiertage zu markieren
- VBA-Code – Automatische Schattierung von Wochenenden und Feiertagen
Wochenenden und Feiertage mit bedingter Formatierung hervorheben
Die Funktion Bedingte Formatierung in Excel 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 ist ideal für Benutzer, die ihre Arbeitsblätter regelmäßig ü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 Datumsangaben aus, die Sie hervorheben möchten.
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 Schaltfläche Formatieren, um Ihren Hervorhebungsstil festzulegen.
4. Im Fenster Zellen formatieren wechseln Sie zum Register Füllung und wählen Sie eine Hintergrundfarbe für Wochenenden und Feiertage. Diese Farbe unterscheidet Nicht-Arbeitstage von regulären Tagen für eine schnelle visuelle Referenz.
5. Klicken Sie zweimal auf OK, um die Dialogfelder zu schließen. Ihre gewählte Hervorhebung wird automatisch für jedes Datum angezeigt, das auf Wochenenden oder aufgeführte Feiertage im ausgewählten Bereich passt. Feiertage können jederzeit durch Bearbeiten Ihres Feiertagsbereichs aktualisiert werden, und die Formatierung wird entsprechend aktualisiert.
Tipps & Fehlerbehebung: Wenn die Hervorhebung nicht erscheint, überprüfen Sie Ihr Datumsformat (die Formel funktioniert mit echten Excel-Daten); Unstimmigkeiten können dazu führen, dass Regeln fehlschlagen. Passen Sie die Zellbezüge in der Formel an, wenn sie auf mehrere Spalten angewendet wird. Außerdem sollten Sie Ihre Feiertagsliste bei Bedarf aktualisieren, wenn Ihre Daten mehrere Jahre umfassen, um die korrekten Nicht-Arbeitstage widerzuspiegeln.
Vorteile: Keine zusätzlichen Spalten oder manuelle Arbeit erforderlich; vollständig dynamisch, wenn sich Ihre Tabelle erweitert oder sich die Feiertagsliste ändert.
Potenzielle Einschränkungen: Bedingte Formatierung hat eine maximale Anzahl von Regeln pro Arbeitsblatt (selten erreicht), und übermäßig komplexe Formeln können die Leistung der Arbeitsmappe bei großen Datensätzen verlangsamen.
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.
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/Nicht-Wochentagen sortieren/filtern möchten, ist das Hinzufügen einer Hilfsspalte mit Formeln eine zuverlässige Wahl. Diese Technik kennzeichnet jede Zeile deutlich 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 Totale für Wochenenden/Feiertage direkt 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 Datenspalte ein und geben Sie eine Überschrift wie „Tagetyp“ 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")Diese Formel führt zwei Überprüfungen für jedes Datum durch:
- WOCHENTAG(A2,2)>=6: Behandelt Samstag (6) und Sonntag (7) als Wochenende (falls Ihre Wochenenden unterschiedlich sind, passen Sie entsprechend an).
- ZÄHLENWENN($F$2:$F$6,A2)>0: Überprüft, ob das Datum mit einem in der Feiertagsliste übereinstimmt.
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 hervorzuheben. Sie können auch bedingte Formatierung basierend auf dieser Spalte verwenden, falls Sie Farben wünschen.
Tipps: Wenn Ihre Wochentage unterschiedlich sind, ändern Sie die WOCHENTAG-Logik (z.B., verwenden Sie 1 für Sonntag oder spezifische Nummern für Ihre Region). Halten Sie die Feiertagsliste immer aktuell. Wenn sich Ihr Datenbereich ändert, wenden Sie die Ausfüllung/Kopie bei Bedarf erneut an.
Vorteile: Hilfsspalten ermöglichen mehr benutzerdefiniertes Filtern und Reporting. Sie können leicht identifizieren, zählen oder nach Bedarf einfärben.
Nachteile: Erfordert eine zusätzliche Spalte in Ihren Daten und manuelles Einfärben, es sei denn, sie wird mit weiterer bedingter Formatierung verwendet.
VBA-Code – Automatische Schattierung von Wochenenden und Feiertagen
Wenn Sie häufig Datumsbereiche aktualisieren müssen oder wenn Sie den Prozess der Hervorhebung von Wochenenden und Feiertagen automatisieren möchten, geht das über das, 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 färbt Excel automatisch Wochenenden und Feiertage basierend auf Ihrem gewählten Datumsbereich und Ihrer Feiertagsliste ein. Sie können die Hervorhebungsfarben im Code bei Bedarf ändern und das Makro erneut ausführen, sobald sich Ihre Daten ändern.
1. Klicken Sie auf Entwickler > Visual Basic. Im Fenster Microsoft Visual Basic for Applications wählen Sie 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 Sub3. Drücken Sie die Taste F5, um diesen Code auszuführen. Eine Eingabeaufforderung fragt Sie, Ihre Datumszellen und dann Ihre Liste von Feiertagszellen auszuwählen. Folgen Sie den Bildschirmanweisungen.
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.
Tipps:
Wenn Sie versehentlich den falschen Bereich ausgewählt haben, führen Sie das Makro einfach erneut aus. Alle bestehenden Füllungen im ausgewählten Bereich werden für nicht übereinstimmende Zellen zurückgesetzt.
Stellen Sie sicher, dass Ihre Daten und Feiertage korrekte Excel-Datumswerte verwenden (kein Text).
Vorteile: Spart Zeit bei wiederholten Aufgaben und bietet große Flexibilität. Keine Notwendigkeit, die Formatierung manuell erneut anzuwenden.
Nachteile: Makros müssen für Ihre Arbeitsmappe aktiviert sein; Benutzer, die mit VBA nicht vertraut sind, sollten vor dem Ausführen ein Backup speichern und das Makro zunächst auf einer Kopie testen.
Weitere relevante Artikel:
- Farbige alternative Zeilen für verbundene Zellen
- Es ist sehr hilfreich, alternative Zeilen mit einer anderen Farbe in großen Daten zu formatieren, um die Daten zu scannen, aber manchmal gibt es möglicherweise einige verbundene Zellen in Ihren Daten. Um die Zeilen abwechselnd mit einer anderen Farbe für die verbundenen Zellen hervorzuheben, wie im folgenden Screenshot gezeigt, wie könnten Sie dieses Problem in Excel lösen?
- Hervorhebung der ungefähren Übereinstimmungssuche
- In Excel können wir die VLOOKUP-Funktion verwenden, um schnell und einfach den ungefähren passenden Wert zu erhalten. Aber haben Sie jemals versucht, die ungefähre Übereinstimmung basierend auf Zeilen- und Spaltendaten zu erhalten und die ungefähre Übereinstimmung aus dem ursprünglichen Datenbereich hervorzuheben, wie im folgenden Screenshot gezeigt? Dieser Artikel wird darüber sprechen, wie man diese Aufgabe in Excel löst.
- Hervorhebung einer Zelle, wenn ihr Wert größer als der einer anderen Zelle ist
- Um die Werte in zwei Spalten zu vergleichen, zum Beispiel, wenn der Wert in Spalte B größer als der Wert in Spalte C in derselben Zeile ist, dann heben Sie die Werte aus Spalte B hervor, wie im folgenden Screenshot gezeigt. In diesem Artikel werde ich einige Methoden zur Hervorhebung einer Zelle vorstellen, wenn ihr Wert größer als der einer anderen Zelle in Excel ist.
- Zeilen basierend auf einer Dropdown-Liste hervorheben
- Dieser Artikel wird darüber sprechen, wie man Zeilen basierend auf einer Dropdown-Liste hervorhebt, nehmen Sie das folgende Screenshot als Beispiel: Wenn ich „In Bearbeitung“ aus der Dropdown-Liste in Spalte E auswähle, muss ich diese Zeile rot hervorheben, wenn ich „Abgeschlossen“ aus der Dropdown-Liste auswähle, muss ich diese Zeile blau hervorheben, und wenn ich „Nicht begonnen“ auswähle, wird grün verwendet, um die Zeile hervorzuheben.
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins