Zahlenformate in Excel, die »mitdenken«

Zahlenformate sind sozusagen die Kosmetik von Excel. Denn mit ihnen lässt sich das Aussehen von Zahlen steuern. Ein Beispiel, das jeder kennt: #.##0 €;[Rot]-#.##0 €.
Dieser Formatcode sorgt dafür, dass Zahlen mit zwei Nachkommastellen, einem Dezimalpunkt und dem Eurozeichen versehen werden. Der Teil nach dem Semikolon sorgt zudem dafür, dass negative Werte rote Schriftfarbe und das Minuszeichen erhalten.

Zahlenformate bieten drei Vorteile:

• Daten werden besser lesbar.
• Wichtige Informationen werden herausgestellt.
• Mit den Zellwerten kann trotzdem wie gewohnt weitergerechnet werden.

Was weniger bekannt ist: Zahlenformate können auch »mitdenken«. In meinem Blogbeitrag Überblick über Projektlaufzeiten mit Ampelsymbolen  ist das zu sehen.

Bild 1: Oben neben der Ampel wird je nach eingegebenem Wert hinter der Ziffer als Maßeinheit das Wort “Jahr” oder “Jahre” angezeigt

Hier nun die versprochene Anleitung, wie dieses Format aufgebaut wird.

Jahre oder Jahr als Maßeinheit?

Das benutzerdefinierte Format
[>1]“mehr als „0 „Jahre“;[=1]“mehr als 1 Jahr“;“weniger als 1 Jahr“
sorgt dafür, dass

• Zahlen > 1 als mehr als x Jahre
• Zahlen = 1 als mehr als 1 Jahr
• alle anderen als weniger als 1 Jahr angezeigt werden.

Wie entsteht ein Zahlenformat, das je nach Wert die richtige Maßeinheit anzeigt?

Wichtig beim Anlegen »mitdenkender« Zahlenformate

Zunächst einmal führt der Weg nach dem Markieren der zu formatierenden Zellen über die Tastenkombination Strg + 1 in das Dialogfeld Zellen formatieren und dort auf der Registerkarte Zahlen zur Kategorie Benutzerdefiniert.

Rechts im Eingabefeld unter Typ lassen sich bis zu vier Vorgaben definieren – getrennt durch Semikolon. Damit wird bestimmt, wie

• positive Werte,
• negative Werte,
• Nullwerte oder
• Text

in der Zelle dargestellt werden sollen.

Diese vier Wertebereiche lassen sich aber auch durch eigene Bedingungen ersetzen. Diese werden in eckige Klammern gesetzt – beispielsweise [=1] oder [>=1000]. Dann folgt der Formatcode – beispielsweise #.##0 “Container” oder eben „mehr als „0 „Jahre“.

Bei Bedarf können Sie sogar dem Zahlenformat noch eine Farbe mitgeben – beispielsweise [Blau].

Zur Illustration hier noch ein Beispiel

Bild 2: Große Werte werden mit einer höheren Einheit (hier kg) dargestellt, alle anderen
in Gramm, negative Werte erhalten zudem rote Schriftfarbe

Bei Gewichtsangaben bewirkt der Formatcode [>=1000]0,0. „kg“;[>=0]0 „g“;[Rot] 0 „g“

• Werte größer oder gleich 1.000 werden in der nächsthöheren Einheit kg dargestellt. Dazu wird die Anzeige der Zahl um 3 Stellen gekürzt und mit einer Nachkommastelle dargestellt. Der Punkt nach 0,0 sorgt für die Kürzung um 3 Stellen.
• Werte größer oder gleich 0 werden mit der Maßeinheit für Gramm (g) gezeigt.
• Alle sonstigen Werte werden ebenfalls in Gramm, aber in roter Schriftfarbe gezeigt.

Fazit

Für die differenzierte Anzeige von Zahlen und Texten bietet Excel standardmäßig vier Abschnitte zum Formatieren der Werte – Positiv; Negativ; Null; Text.

Diese Abschnitte lassen sich umfunktionieren, um eigene Bedingungen zu definieren. Diese werden in eckige Klammern gesetzt.

Über solche benutzerdefinierte Zahlenformaten lässt sich auch die Anzeige der jeweils passenden Maßeinheit zu den Werten steuern: Jahr oder Jahre, kg oder g.

Mit der Bedingten Formatierung wäre genau das nicht möglich.