Feiertage und Wochentage in Excel mit SVERWEIS bearbeiten
Schritt-für-Schritt-Anleitung
Um Feiertage und Wochentage in Excel mit der Funktion SVERWEIS zu markieren, folge diesen Schritten:
-
Tabellenblätter erstellen: Erstelle zwei Tabellenblätter – eines für "Feiertage" und eines für die "Urlaubsliste".
-
Daten eingeben: Trage die Feiertage in das Tabellenblatt "Feiertage" ein. Zum Beispiel:
- A1: "Datum"
- B1: "Tag"
- A2: "25.12.2023"
- B2: "1. Weihnachtstag"
-
Formel in die Urlaubsliste einfügen: Wechsle zur "Urlaubsliste" und trage die Formel in Zelle B5 ein:
=WENN(ODER(WOCHENTAG(A5;2)=6;WOCHENTAG(A5;2)=7;NICHT(ISTFEHLER(SVERWEIS(A5;Feiertage!$B$1:$B$28;1;FALSCH))));"Feiertag";TEXT(A5;"TTT"))
Diese Formel prüft, ob das Datum ein Feiertag oder ein Wochenende ist.
-
Anwenden der Formel: Ziehe die Formel nach unten, um sie auf die nachfolgenden Zellen anzuwenden.
Häufige Fehler und Lösungen
-
#WERT!-Fehler: Wenn du die Formel in Zelle B5 eingegeben hast und einen #WERT!-Fehler erhältst, überprüfe, ob das Datum korrekt formatiert ist. Stelle sicher, dass die Zellen im Datumsformat vorliegen.
-
Fehlende Feiertage: Wenn du keine Feiertage angezeigt bekommst, stelle sicher, dass die Daten im "Feiertage"-Tabellenblatt korrekt eingegeben sind und dass die SVERWEIS-Bereichsangabe korrekt ist (z.B. $B$1:$B$28).
-
Wochentage werden nicht gezählt: Wenn du die Wochentage nicht zählen kannst, könnte dies an der falschen Verwendung der Zellenformatierung liegen. Achte darauf, dass die Wochentage in Spalte B korrekt erstellt wurden.
Alternative Methoden
Eine alternative Methode, um Feiertage zu markieren, ist die Verwendung von bedingter Formatierung:
- Wähle die Zellen in der "Urlaubsliste".
- Gehe zu "Start" und klicke auf "Bedingte Formatierung".
- Wähle "Neue Regel" und dann "Formel zur Ermittlung der zu formatierenden Zellen verwenden".
- Gib die Formel ein:
=NICHT(ISTFEHLER(SVERWEIS(A5;Feiertage!$B$1:$B$28;1;FALSCH)))
- Wähle das gewünschte Format (z.B. Hintergrundfarbe) aus.
Praktische Beispiele
Hier sind Beispiele für Formeln, die du verwenden kannst:
-
Um alle Wochentage (Montag bis Freitag) zu zählen, die keine Feiertage sind, verwende:
=SUMMENPRODUKT((WOCHENTAG(A5:A50;2)<=5)*(B5:B50<>"Feiertag"))
-
Um die Anzahl der Feiertage zu zählen, benutze:
=SUMMENPRODUKT((B5:B152="Feiertag")*(A5:A152<>""))
Tipps für Profis
- Nutze die Funktion
TEXT(A5;"TTT") für die Darstellung der Wochentage, um sicherzustellen, dass diese korrekt angezeigt werden.
- Halte deine Daten in den Tabellenblättern gut strukturiert und überprüfe regelmäßig auf Fehler, um die Genauigkeit der Berechnungen zu gewährleisten.
- Experimentiere mit den Zellformaten, um ein ansprechendes Design zu erzielen und die Lesbarkeit zu verbessern.
FAQ: Häufige Fragen
1. Wie kann ich Feiertage und Wochentage in Excel markieren?
Du kannst Feiertage mit der SVERWEIS-Funktion markieren, indem du eine Formel eingibst, die prüft, ob das Datum ein Feiertag ist oder nicht.
2. Was tun, wenn die Formel nicht funktioniert?
Überprüfe die Formatierung deiner Zellen und ob die Daten in der "Feiertage"-Liste korrekt eingegeben sind. Ein #WERT!-Fehler deutet häufig auf ein Formatierungsproblem hin.
3. Gibt es eine Möglichkeit, Feiertage automatisch zu berechnen?
Ja, du kannst eine Liste von Feiertagen in einem separaten Tabellenblatt speichern und dann die SVERWEIS-Funktion verwenden, um diese Feiertage in deiner "Urlaubsliste" zu prüfen.