Getagged: Bedingte Formatierung

Excel – Terminplan in Gantt-Ansicht erzeugen

Heute habe ich eine neue Serie bei YouTube gestartet: Die Erzeugung eines Terminplans in Excel mit Darstellung eines Gantt-Balkenplans über die bedingte Formatierung.

Die Tabelle auf der linken Seite steuert die Darstellung der Balken auf der rechten Seite. Zudem werden Meilensteine als Raute dargestellt.

G1

Bei der Darstellung des Projektfortschritts können die Prozentwerte visuell dargestellt werden. Feiertage werden ignoriert.

G2

Hier kann man der Playlist folgen:

Die Übungsdateien werden schrittweise zum Nachvollziehen pro Videoeinheit angeboten.

Excel – Stundenzettel – Datum farbig hervorheben – Bedingte Formatierung

Teil 3…
Der Stundenzettel soll mit der Hilfe von bedingter Formatierung übersichtlicher gestaltet werden.
Zum einen sollen generell die Tage von Wochenenden grau hinterlegt sein – in der ganzen Zeile.
Dann soll auch eine graue Linie bei einem Wochenwechsel erschein.
Zudem sollen begonnene Einträge bei Leistung so lange orange hinterlegt sein, wie in beiden Nachbarzellen noch keine Uhrzeiten angelegt worden sind.

606a

Zunächst markiere ich den Datenbereich von Zelle A5:H35, da hier später die bedingte Formatierung für die grauen Wochenendzeilen gelten soll. Dann klicke ich auf START und anschließend auf die Befehlsschaltfläche Bedingte Formatierung.

Hier sieht man schon die Bereiche für die fertigen Regeln.

606b

Für eine neue Regel klicke ich auf Neue Regel und wähle den unteren Eintrag aus, dass ich eine Regel auf Basis des Ergebnisses einer Formel erstellen möchte. Wenn das Formelergebnis den Wert WAHR ergibt, gilt die Formel. Es soll hier einfach nur geprüft werden, ob vorne in Spalte A der gleichen Zeile der Wert Sa oder So steht.

606e

Nach Eingabe der Regel nicht vergessen, dann auch noch das Format über die Schaltfläche Formatieren anzupassen. Hier wähle ich z.B. den grauen Hintergrund.

Anschließend markiere ich den Bereich A6:H35 – also ohne die obere Datumszeile.
Hier wird geprüft, ob der Wochentagswert in Spalte B größer ist, als der Tag der eigenen Woche. Falls ja, fand ein Wochenwechsel statt und die Linie kann gesetzt werden.

606d

Hier wähle ich das Format, dass bei Geltung der Regel der Rahmenlinienstrich oben in grau gesetzt wird.

Zuletzt markiere ich den Datenbereich von Zelle c5:c35, da hier später die bedingte Formatierung für die Prüfung liegen soll, ob die Nachbarzellen belegt sind und in der eigenen Zelle ein Wert ausgewählt wurde.

606c

Hier wähle ich z.B. Orange.

Hier ist die Ergebnisdatei aus Video Excel # 606:
Excel606_Ergebnisdatei

Hier geht es zum dritten Video:

Videolink: http://youtu.be/bFEmzlZ1J60

Excel – Wechselnde Zeilenformatierung bei wechselnden Inhalten

In einer Liste sollen alle Zeilen mit identischen Inhalten aus Spalte A gleich formatiert werden. Wechselt der Inhalt, wechselt auch automatisch die Farbe. Dann soll so lange die gleiche Farbe pro Zeile genommen werden, wie sich dieser Inhalt in Spalte wiederholt.

Gelöst wird das Problem mit zwei Formeln in der bedingten Formatierung. Bei YouTube wird noch einmal das Problem beschrieben.

467_a

Hier ist die Fragestellung auf YouTube:

Videolink: http://youtu.be/67CIoTslVBY

Von den vielen Lösungsmöglichkeiten möchte ich die geniale Lösung von Andreas Entenmann (Excel MVP) beschreiben.
Für den kompletten Wertebereich A2:Cxxx wird eine Prüfung auf das Ergebnis einer Formel in der bedingten Formatierung hinterlegt.

1. Zunächst wird der Bereich markiert
2. Anschließend START – Formatvorlagen – Bedingte Formatierung – Neue Regel
3. Dann auf Formel zur Ermittlung der zu formatierenden Zelle verwenden klicken
4. Folgende Formel hinterlegen:
=REST(SUMMENPRODUKT(N($A$1:$A1$A$2:$A2));2)=1
5. Eine Formatierung für den Fall hinterlegen, dass bei der Prüfung der Wert WAHR resultiert.
6. Dialog schließen

Den gleichen Vorgang für =REST(SUMMENPRODUKT(N($A$1:$A1$A$2:$A2));2)=0 mit einer anderen Formatierung wiederholen.

467_2

Doch was passiert hier in der Formel?
Es werden die Funktionen N(), REST() und SUMMENPRODUKT() genutzt. Dazu noch durch den Bereichsbezug $A$1:$A1 bzw. $A$2:$A2 ein mit jeder Zeile anwachsender Bereich.

Die Funktion REST(Zahl;2) teilt eine Zahl durch 2. Es wird aber nicht geschaut, wie oft die Zahl 2 in die Zahl passt, sondern welcher Rest nach der Division übrig bleibt. Bei geraden Zahlen resultiert ein Rest von 0, bei ungeraden Zahlen ein Rest von 1. Das Ergebnis von Rest wird in der bedingten Formatierung genutzt.

N() wandelt den Wert WAHR in eine 1, Text und den Wert FALSCH in den Wert 0 um, Zahlen behalten Ihren Wert. Befindet sich eine Prüfung in den Klammern von N() so z.B. N(8>=4), dann wird N(WAHR) = 1 gerechnet. Hier wird geprüft, ob der Wert der Vorzelle aus Spalte A anders ist als der Wert der aktuellen Zelle in Spalte A aus der gleichen Zeile. Also z.B. N(A4A5) oder N(A1A2). Immer dann, wenn also praktisch eine Zeile höher der gleiche Wert steht, kommt bei dieser Funktion der Wert 0 heraus, ansonsten eine 1.

SUMMENPRODUKT(Array1;Array2;…) bildet die Summe seiner einzelnen Produkte. Da aber nur ein einziges Array existiert, gibt es nichts zu Multiplizieren, sondern lediglich zu Addieren. Und was wird addiert? Der Wert der jeweiligen Prüfungen eines Bereichs, ob die Werte eines Bereichs mit den Werten eines anderen Bereichs verschieden sind. Und zwar jeweils beim Vergleich von einer Zelle in Spalte A zur Zelle darüber. Das nur in Spalte A geschaut wird, wird durch die $-Zeichen vor dem jeweiligen A garantiert.

Bei der Formatierung wird also pro Zelle geprüft, wie viele unterschiede von aktueller Zeile Spalte A gibt es bis zur Zelle A1 zurück. Liegen identische Werte untereinander, wird dieser Wert nicht erhöht. Wechselt aber ein Eintrag in der aktuellen Zeile, wird um 1 erhöht und die Funktion REST wird letztendlich zu einem anderen Ergebnis kommen.

Hier ist die Lösungsdatei:
Excel 467 – Excel Lösung wechselnde Zeilenfarbe

Und hier ist die Lösung auf YouTube:

Videolink: http://youtu.be/qv7fjEmuMcA

Excel – Flächentreues Histogramm mit bedingter Formatierung

Inspiriert durch die fantastischen Diagramme von E90E50Charts habe ich mal einen eigenen Anlauf für flächentreue Histogramme gewagt. Einige Videos dazu sind ja schon bei YouTube.

E409

Hier einmal eine andere Variante über Bedingte Formatierung.
E409_HistogrammConditionalFormatting

Hier geht es zum ersten Teil der Videos:

Hier zum zweiten:

Und die abschließende Nummer 3:

Excel – gestapelte Prozentanzeige – Bedingte Formatierung

Bill Jelen hat in seinem Video-Podcast #1652 die grafische Umsetzung einer Prozentanzeige mit Kästchen demonstriert – ich finde die Idee ziemlich cool und habe das Ganze noch einmal mit einer alternativen bedingten Formatierung hinterlegt, welche keine Formeln oder Werte in den Zellen des Tabellenblatts erfordert.

E366

Für das linke Diagramm Case 1 wurden folgende Einstellungen getätigt:

– Zellhintergrund und Rahmenfarbe wurden auf ein helle Orange gesetzt.
– Der Bereich B2:B11 wurde bedingt formatiert.
– In Zelle B12 befindet sich der Prozentsatz 86%.

Die bedingte Formatierung errechnet einen Wert, der sich auf ZEILE() und SPALTE() ergibt.

E366_1

Die Formel aus der bedingten Formatierung arbeitet wie folgt:
=(11-ZEILE())*10+SPALTE()-1<=$B$12*100

=(11-ROW())*10+COLUMN()-1<=$B$12*100 (engl.)

Für jede Zelle der quadratischen 10×10 Kästchen großen Fläche gilt dieselbe Formel. Jede Zelle prüft für sich, in welcher ZEILE() und welche SPALTE() sie steht. Durch die Rechnung ergibt sich ein Raster, das die Zelle unten links den Wert 1 erhält und die Zelle oben rechts den Wert 100.

Der 10×10 Kästchen große Bereich errechnet für die Zelle oben links, also Zelle B2:
=(11-2)*10+2-1
=9*10+1
=91

Für jede Zelle des Kästchenbereichs wird nun ermittelt, ob der Wert kleiner als $B$12*100 ist. Falls WAHR, gilt die bedingte Formatierung.

Für Case 2 habe ich alle markiert und mit einer Spalte Versatz daneben eingefügt. Die Formel musste so angepasst werden, dass die neue Spaltennummer berücksichtigt wird. Der Vergleichswert steht in Zelle M12. Die Spaltennummer musste um einen um 11 höheren Wert reduziert werden.

=(11-ZEILE())*10+SPALTE()-12<=$M$12*100

Das Thema kann natürlich noch weiter ausgebaut werden. Wie wäre es mit zwei Prozentsätzen. Links steht IST, rechts steht SOLL. Alle IST-Werte größer SOLL sollen hervorgehoben werden. Alles SOLL, was größer IST ist, wird rot markiert.

E366_2

Die Datei… ohne Makros – einfach F9 drücken: E366_Faesser_stapeln

Das Video für die einfache Variante ohne Soll/Ist-Vergleich bei YouTube:

Die komplexere Variante mit Soll-Ist-Vergleich: