Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1580to1584
1580to1584
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

Zellen einfärben nach einer Matrix

Forumthread: Zellen einfärben nach einer Matrix

Zellen einfärben nach einer Matrix
24.09.2017 21:17:07
Bernd
Hallo,
Ich habe hier mal ein Problem. Zuerst mal meine Aufgabenstellung:
Es soll eien Monatskalender mit mehrern Spalten, die alle bei auffinden eines Sam-, Son-, oder Feiertages grau eingefärbt werden sollen.
Am Anfang steht das Datum an dem die Überprüfung stattfinden soll.
Es gibt eine Matrix in der alle Feiertage und Ferienbereich eingetragen sind.
Da das Blatt ausgedruckt wird möchte ich nur ermitten, ob ein entsprechender Tag vorliegt. Dann soll FT eingtragen werden. Die Textfarbe wird auf weiß gesetzt. Mit einer bedingten Formatierung soll dann eingefärbt werden.
Mit den Sam- und Sonntagen funktioniert das schon (Formel: =WOCHENTAG(R3;2) > 5). In der 2. bedingten Formatierung wollte ich dann auf FT abfragen.
R4= Datum
Bereich mit dem Namen Feiertage
Neujahr 01.01.2017
Karfreitag 14.04.2017
Ostermontag 17.04.2017
Tag der Arbeit 01.05.2017
Chr.Himmelfahrt 25.05.2017
Pfingstmontag 05.06.2017
Fronleichnam 15.06.2017
Maria Himmelfahrt 15.08.2017
Dt. Einheit 03.10.2017
Allerheiligen 01.11.2017
1. Weihnachtstag 25.12.2017
2. Weihnachtstag 26.12.2017
Ferien aus Vorjahr 01.01.2017 05.01.2017
Faschingsferien 27.02.2017 04.03.2017
Osterferien 10.04.2017 22.04.2017
Sommerferien 03.07.2017 14.08.2017
Herbstferien 02.10.2017 14.10.2017
Weihnachtferien 21.12.2017 31.12.2017
Die bisher von mir in 5stündiger Arbeit gebastelte Formel bring aber kein Ergebnis.
Ich bin am verzweifeln. Hier mal der letzte Stand der die wenigsten Fehler produziert:
=WENN(ISTFEHLER(INDEX(Feiertage;VERGLEICH(R4;Feiertage;0)));"";"FT")
Kleine Unterfrage: Kann ich mit der Bedinten Formatierung die Zellfarbe eine Zelle abfragen? Dann bräuchte ich nicht in jeder Zelle die Formel eintragen und könnte somit eine Menge Ballast ersparen.
Gruß
Bernd
Anzeige

9
Beiträge zum Forumthread
Beiträge zu diesem Forumthread

Betreff
Datum
Anwender
Anzeige
Lade deine Datei hoch! o.T.
24.09.2017 21:20:44
Sepp
Gruß Sepp

AW: Lade deine Datei hoch! o.T.
24.09.2017 21:29:16
Bernd
Die ganze Zeile von R3 bis AR3 soll dan im Trefferfall eingefärbt werden.
Anzeige
AW: Lade deine Datei hoch! o.T.
24.09.2017 21:51:25
Sepp
Hallo Bernd,
https://www.herber.de/bbs/user/116486.xls
nur wo die Feiertage angezeigt werden sollen (FT) ist mir nicht klar.
Gruß Sepp

Anzeige
AW: Lade deine Datei hoch! o.T.
24.09.2017 22:05:44
Bernd
Es sollen alle Feiertage und alle Ferientage grau hinterlegt werden.
In der Zelle N10 wähle ich den Monat für diesen Blatt aus. Anhand des Monats und der Feier- und Ferientagematrix soll dann für diesen Monat überprüft werden ob es einen Treffer gibt. In dieser Einstellung der Oktober. Bei Treffer soll die ganze Zeile von z.B. R3 bis AR3 grau einfärbt werden.
Die gleiche Überprüfung soll natürlich auch für die Spalte von R3 bis R33 durchgeführt werden.
Dann sind halt mal bei 14 Tagen Ferien 14 Zeilen nacheinander eingefärbt.
Ist halt zur eine Hilfe, das in diesen Zeilen nix eingetragen wird.
Anzeige
AW: Lade deine Datei hoch! o.T.
24.09.2017 22:10:26
Bernd
Ich brauche nur einen grauen Hintergrund bei Treffer von Sam-, Sonntagen und einem Datum aus der Feiertagsmatrix. Der Rest sill weiß bleiben.
dann änder halt die Farbe!
24.09.2017 22:17:24
Sepp
Hallo Bernd,
die Farben kannst du doch wohl selber an deine Bedürfnisse anpassen, oder?
Gruß Sepp

Anzeige
;
Anzeige
Anzeige

Infobox / Tutorial

Zellen einfärben nach einer Matrix in Excel

Schritt-für-Schritt-Anleitung

Um Zellen in Excel basierend auf einer Matrix (z.B. Feiertagen oder Wochenenden) einzufärben, kannst Du die bedingte Formatierung verwenden. Hier ist eine einfache Schritt-für-Schritt-Anleitung:

  1. Daten vorbereiten: Stelle sicher, dass Du eine Matrix hast, die alle Feiertage und Ferien enthält. Diese solltest Du als benannten Bereich in Excel festlegen (z.B. "Feiertage").

  2. Zelle auswählen: Wähle die Zelle aus, die Du überprüfen möchtest (z.B. R4).

  3. Bedingte Formatierung öffnen:

    • Gehe zu „Start“ > „Bedingte Formatierung“ > „Neue Regel“.

  4. Formel zur Bestimmung der zu formatierenden Zellen verwenden:

    • Wähle „Formel zur Ermittlung der zu formatierenden Zellen verwenden“.
    • Gib folgende Formel ein: 
      =ODER(WOCHENTAG(R4;2)>5;WENN(ISTFEHLER(INDEX(Feiertage;VERGLEICH(R4;Feiertage;0)));FALSCH;WAHR))

  5. Format festlegen: Klicke auf „Formatieren“ und wähle die gewünschte Hintergrundfarbe (z.B. Grau).

  6. Regel anwenden: Klicke auf „OK“, um die Regel zu speichern. Die Zellen, die Samstage, Sonntage oder Feiertage enthalten, sollten nun grau hinterlegt werden.

  7. Bereich erweitern: Wende diese Regel auf den gesamten Bereich an, den Du einfärben möchtest (z.B. R3 bis AR3).

Häufige Fehler und Lösungen

  • Fehler: Keine Einfärbung trotz korrekter Formel
    Lösung: Überprüfe, ob die Matrix "Feiertage" korrekt definiert ist und ob die Daten in den Zellen wirklich mit den Feiertagen übereinstimmen.

  • Fehler: Zellen werden nicht korrekt überprüft
    Lösung: Stelle sicher, dass das Datum in der Zelle im richtigen Format vorliegt (z.B. TT.MM.JJJJ).

  • Fehler: Bedingte Formatierung wird nicht angewendet
    Lösung: Überprüfe die Reihenfolge der Regeln in der bedingten Formatierung. Möglicherweise wird eine andere Regel vorab angewendet.

Alternative Methoden

Eine alternative Methode zur Einfärbung von Zellen ist die Verwendung von VBA (Visual Basic for Applications). Hier ein einfaches Beispiel, um Zellen automatisch einzufärben:

Sub ZellenEinfärben()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets("DeinBlattname")
    Dim rng As Range
    Set rng = ws.Range("R3:AR33")

    For Each cell In rng
        If Weekday(cell.Value, vbMonday) > 5 Or Not IsError(Application.Match(cell.Value, ws.Range("Feiertage"), 0)) Then
            cell.Interior.Color = RGB(192, 192, 192) ' Grau
        End If
    Next cell
End Sub

Praktische Beispiele

  • Beispiel 1: Einfärbung eines Monatskalenders

    • Erstelle eine Tabelle, in der die Daten für jeden Tag eines Monats eingetragen sind. Verwende die oben genannten Schritte, um alle Samstage, Sonntage und Feiertage grau zu hinterlegen.

  • Beispiel 2: Ferienzeiten markieren

    • Nutze die Feiertagsmatrix, um automatisch die Zellen für die Ferienzeiten in Deinem Kalender einzufärben.

Tipps für Profis

  • Zellfarbenerkennung: Du kannst die Zellfarbe in einer Formel abfragen, indem Du die Funktion CELL verwendest, jedoch ist dies in bedingten Formatierungen nicht direkt möglich. Es ist besser, die Formeln direkt in den Zellen zu verwenden.

  • Dynamische Bereiche: Wenn Du oft Feiertage oder Ferien aktualisierst, überlege Dir, wie Du dynamische benannte Bereiche verwenden kannst, um die Werte automatisch zu aktualisieren.

  • Formatierung kopieren: Du kannst die Formatierung von einer Zelle auf andere Zellen übertragen, indem Du das Formatierungswerkzeug in der Symbolleiste verwendest.

FAQ: Häufige Fragen

1. Kann ich die bedingte Formatierung auf mehrere Blätter anwenden?
Ja, Du kannst die bedingte Formatierung auf mehrere Blätter anwenden, indem Du die Regel auf jedes Blatt manuell anwendest oder VBA zur Automatisierung verwendest.

2. Funktioniert das in allen Excel-Versionen?
Die beschriebenen Methoden zur bedingten Formatierung sind in Excel 2007 und höheren Versionen verfügbar. Achte darauf, dass Du die richtige Formel entsprechend Deiner Excel-Version verwendest.

Beliebteste Forumthreads (12 Monate)

Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
Anzeige
Anzeige
Anzeige