Kategorie: Bedingte Formatierung

Äpfel, Zirkelbezug, Iteration & Co.

Anbei eine kleine Bildstrecke zum Video Excel – Äpfel, Zirkelbezug, Iteration & Co.  Unten ist auch die Datei als Downloadlink verfügbar.

Man beachte den Zirkelbezug in Zelle O2! Die Formel ist unten im Bild aufgeführt.

Die kleine Formel aus D3 zeigt, dass nur Zufallswerte gezogen werden, wenn die Zelle J14 auf 0 steht.

a4.jpg

Das ist die Formel in den Körben, stellvertretend für Zelle N14 mit der entsprechenden Formatierung für grüne Äpfel.

Damit der Zirkelbezug vernünftig berechnet werden kann, habe ich unter Datei – Optionen – Formeln die iterativen Berechnungen aktiviert und die Iterationszahl auf 1 gesenkt.

a7.jpg

Hier gibt es die Excel-Datei zum Download:
Excel 723 Apfelernte

Hier geht es zum Video:

Videolink: https://youtu.be/PK_1E3aX6iM

Excel – Zellen ohne Zellschutz optisch hervorheben

Sobald ein Tabellenblatt geschützt wird, können nur noch Eingaben in die Zellen vorgenommen werden, die vom Blattschutz ausgenommen sind.

Zunächst müssen die Zellen markiert werden. Dann entweder über STRG + 1 und dann im Register Schutz den Haken bei Gesperrt entfernen, oder im Register START in der Gruppe Zellen auf Format klicken und bei Zellen sperren den Schutz entfernen.

479a

Anschließend lässt sich die bedingte Formatierung auf einen Bereich, entweder die vorher formatierten Zellen oder einen größeren Bereich, anwenden. Wichtig bei der Eingabe der Formel ist, dass der Zellbezug dem der gerade aktiven Zelle ist.

Die Formel

=ZELLE("schutz";Zellbezug)=0

prüft, ob für die entsprechende Zelle der Zellschutz deaktiviert ist. Alternativ würde dort der Wert 1 resultieren. Bei der Eingabe der Funktion ZELLE werden weitere Argumente wie z.B. spalte und zeile angezeigt.

479b

Zum Schluss noch den Blattschutz aktivieren. Fertig.

Hier gibt es die formatierte Übungsdatei:
Excel 479 – ZELLE Schutz

Hier geht es zum Video:

Videolink: http://youtu.be/yq48FdV6Ohk

Excel – Farbige Statusbalken mit WIEDERHOLEN

Ein Statusbalken soll über Farbe und Länge einen Wert visualisieren.
Pro angefangene 20 soll ein Wert von 0 (kein Block), 1-20 (1 Block),… bis 81-100 (5 Blöcke) in der Länge und zusätzlich bei bis zu 2 Blöcken rot, bei 3-4 Blöcken gelb und fünf Blöcken grün auch farblich hervorgehoben werden.

Mit der Funktion WIEDERHOLEN lasse ich das Zeichen n in der Schriftart Wingdings mehrfach hintereinander einfügen. Und zwar so oft, wie 20er in den Wert von Spalte B passen. Dies wird mit AUFRUNDEN(B-Wert/20;0) erreicht.

477

In der bedingten Formatierung prüfe ich dann, wie viele Blöcke genommen wurden. Bei weniger bzw. gleich zwei wird die Schrift rot, bei weniger oder gleich vier die Farbe gelb und ansonsten grün. Wenn man die Blöcke direkt grün färbt, könnte man auch nur zwei Regeln nehmen, wobei die Reihenfolge wichtig ist. Hier im Beispiel wird anders herum gefragt, bei 5 nimm grün, bei mehr oder gleich 3 nimm gelb, bei mehr oder gleich 1 nimm rot… auch hier hätte man die Balken gleich rot färben können und den letzten Formatschritt wegelassen.

Hier ist die Übungsdatei:
Excel_477_Balken

Hier geht es zum Video:

Videolink: http://youtu.be/JF9nXBZGuBo

Excel – Bedingte Formatierung mit SUMMENPRODUKT

Mit Hilfe der Bedingten Formatierung können Zellen hervorgehoben werden, deren eigene Werte oder deren benachbarte Werte bestimmte Eigenschaften erfüllen.

Meist prüft man jedoch nur ab, ob ein Wert lediglich in einem Wertebereich liegt oder einen bestimmten Wert hat. Mit Hilfe der Funktion SUMMENPRODUKT und einem kleinen um 90 Grad versetzten Bereich / Array können jedoch auch mehrere Werte abgeprüft werden.

Insofern wirklich ein Array in der Form {1.4.11.14} genutzt wird, verweigert sich allerdings die Bedingte Formatierung mit dem Hinweis, dass Arraykonstanten nicht genutzt werden dürfen.

Mein Tipp: Die Berechnung einfach in einen Namen auslagern und dann in der Bedingten Formatierung einfach das Ergebnis des Namens überprüfen.

Insofern einer der rot umrandeten Zahlen in Spalte B auftritt und der Name „Bea“ in Spalte A, soll die ganze Zeile markiert werden.

Excel474a

Hat man die Hilfszellen also im Blatt vorliegen, könnte man die folgende Formel in der Bedingten Formatierung nutzen.

=SUMMENPRODUKT(($A2=$H$2)*($B2=$H$1:$K$1))=1

Wichtig sind die $-Zeichen vor A2 und vor B2, damit die Formel sich in jeder Zelle des Bereichs A2:Cxxx jeweils auf die eigenen Werte der Spalten A und B beziehen kann.

Excel474b

Doch hier müssen die Werte zum Vergleich noch in den Zellen H2 und H1:K1 vorgehalten werden.
Klickt man die Zelle A2 an und wechselt auf Formeln – Definierte Namen – Namens-Manager, kann ein Name mit einer Berechnung (z.B. Prüfung) angelegt werden. In diesem Namen lassen sich statt Bezüge auch Konstanten anlegen.

Excel474c

=SUMMENPRODUKT(('SUMMENPRODUKT ARRAY'!$A2="Bea")*('SUMMENPRODUKT ARRAY'!$B2={7011.7100.7400.7700}))

Anschließend muss die Formel in der Bedingten Prüfung auf
=Prüfung=1
geändert werden… Fertig!

Hier ist die Übungsdatei:
Excel 474 Bedingte Formatierung Summenprodukt

Hier geht es zum Video:

Videolink: http://youtu.be/EgcWIyE54TI

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 – Zellen eines benannten Bereichs optisch hervorheben

Der Bereich, der einem benannten Bereich oder dem Schnittpunkt zweier Bereiche entspricht soll optisch per bedingter Formatierung hervorgehoben werden.

457

Folgende Formel der Bedingten Formatierung muss WAHR sein, um die Formatierung auszulösen.
=UND(ZEILE()>=$F$6;ZEILE()=$G$6;SPALTE()<=$G$7)=WAHR

457a

Ich nutze hier die vier Hilfszellen in F6:G7. Eventuell kann diese Berechnung auch in den Namen stattfinden und so direkt in der Bedingten Formatierung abgerufen werden.

Hier ist die Übungsdatei:
Excel 457 Bedingte Formatierung

Hier geht es zum Video:

Videolink: http://youtu.be/tWBUdbkwCTw