Malen nach Zahlen, quasi
Excel: Bedingte Formatierung aufgrund Anzahl Zeichen in der Zelle
Im gestrigen Tipp «Dynamische Balken ohne Diagrammfunktion» haben wir gezeigt, wie Sie ohne ein Diagramm einzufügen Balken oder Pfeile in bestimmter Länge in einer Zelle darstellen können. Dies könnte zum Beispiel etwa wie im folgenden Screenshot aussehen. Nun würden Sie gerne die Zelle oder den Inhalt je nach Balkenlänge einfärben.
Die Balken sind da, aber Sie hätten diese gerne je nach Länge des Inhalts eingefärbt
Quelle: PCtipp.chZum Beispiel möchten Sie die untersten 25% oder 33% der Werte in Grün haben, die obersten 25% oder 33% in Rot und alles dazwischen in Gelb. Excel bringt bei der Auswahl der bedingten Formatierung leider keine Option mit, um die Länge der Zelle direkt als Kriterium anzugeben. Sie müssen daher zu mehr oder weniger komplizierten Formeln greifen. Die haben wir für Sie aber hier bereits ausgetüftelt.
Das unterste und oberste Viertel – und alles dazwischen
Nehmen wir an, die aus Zeichen zusammengesetzten Balken wären auch bei Ihnen in Spalte B, in den Zellen B2 bis B13. Markieren Sie den Bereich. Gehen Sie im Reiter Start via Bedingte Formatierung zu Neue Regel und wählen Sie Formel zur Ermittlung der zu formatierenden Zellen verwenden.
Fangen wir mit einer Formel an, die dafür sorgt, dass die untersten 25% der Werte grün werden sollen. Die sieht wie folgt aus:
=LÄNGE($B2)<=QUARTILE.INKL(LÄNGE($B$2:$B$13);1) Erklärung: Via QUARTILE.INKL ermitteln wir über die Funktion LÄNGE im Bereich B2 bis B13 über den abschliessenden Schalter 1 das unterste Viertel. Formatiert werden sollen die Zellen, wenn ihre Länge unterhalb oder gleich dem Grenzwert fürs unterste Viertel liegt.
Die erste Formel, über welche die untersten 25% grün formatiert werden sollen
Quelle: PCtipp.chKlicken Sie auf Formatieren und wählen Sie bei Schrift die Farbe Grün. Nach dem Klick auf OK werden die Werte grün dargestellt.
Nun dasselbe in Grün bzw. hier eben in Rot für die obersten 25%, was wir mit dem abschliessenden Schalter 3 bewerkstelligen. Erstellen Sie für den gleichen Zellbereich wieder eine Neue Regel. Alles, was gleich oder grösser als dieser Wert ist, soll rot werden:
=LÄNGE($B2)>=QUARTILE.INKL(LÄNGE($B$2:$B$13);3) Wählen Sie via Formatieren für diese Zellen die Farbe Rot aus.
Die dritte Regel soll alles dazwischen gelb färben, was wir mit UND verbinden. Was grösser als das kleinste Viertel und kleiner als das grösste Viertel ist, wird hiermit adressiert:
=UND(LÄNGE($B2)>QUARTILE.INKL(LÄNGE($B$2:$B$13);1);LÄNGE($B2)<QUARTILE.INKL(LÄNGE($B$2:$B$13);3)) Für diese via Formatieren noch ein Gelb auswählen, dann wars das bereits:
Die Anzahl Zeichen in diesen Zellen bestimmen nun deren Farbe
Quelle: PCtipp.chUnd ein Drittel, Fünftel, Zehntel?
Und was ist, wenn Sie nicht genau bei einem Viertel, sondern einem Drittel, Fünftel oder Zehntel unterteilen wollen? Das Vorgehen ist genau das gleiche. Hierfür greifen Sie aber nicht zur Funktion QUARTILE.INKL, sondern zu QUANTIL.INKL. Hier können Sie im abschliessenden Schalter die Prozentpunkte angeben, bei denen Sie unterteilen wollen. Wie wäre es zum Beispiel bei einem Drittel, also 33%? Hierfür setzen Sie fürs unterste Drittel diese Formel ein, bei welcher das 0.33 für die Drittelung sorgt:=LÄNGE($B2)<=QUANTIL.INKL(LÄNGE($B$2:$B$13);0.33) Formatieren Sie die Zellen grün.
Fürs oberste Drittel, das Sie mit 0.66 auf alles über 66% Prozent festlegen, verwenden Sie diese Formel und färben die Schrift mit einem Rot ein:
=LÄNGE($B2)>=QUANTIL.INKL(LÄNGE($B$2:$B$13);0.66) Und dann wieder die Kombination der beiden für alles, was dazwischen liegt, um es gelb zu färben:
=UND(LÄNGE($B2)>QUANTIL.INKL(LÄNGE($B$2:$B$13);0.33);LÄNGE($B2)<QUANTIL.INKL(LÄNGE($B$2:$B$13);0.66)) 
Mit QUANTIL.INKL können Sie die Unterteilungen nach beliebigen Prozentpunkten festlegen
Quelle: PCtipp.ch
Kommentare