Malen nach Zahlen, quasi
16.07.2021, 08:00 Uhr
Excel: Bedingte Formatierung aufgrund Anzahl Zeichen in der Zelle
Sie möchten in einer Spalte automatisch die Zell- oder Schriftfarbe formatieren, abhängig davon, wie viele Zeichen die Zelle enthält. Es ist etwas knifflig, geht aber doch.
(Quelle: PCtipp.ch)
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.ch
Zum 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.ch
Klicken 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.ch
16.07.2021