Excel-TippZahlenformat abhängig vom Zahlenbetrag definieren
Zahlen in Geschäftsberichten mit unterschiedlichen Beträgen
Müssen Sie eine Berichtsvorlage erstellen, die für unterschiedliche Unternehmen, Geschäftsbereiche oder Profit-Center verwendet werden soll, dann kann dies bei den zu erfassenden Zahlen zu Problemen mit den Zahlenformaten führen. Mal geht es um drei-, mal um vier- oder sogar um sechsstellige Zahlen und Beträge.
Je nach Betrag einer Zahl bietet es sich an, sie in EUR (€), TEUR (T€) oder in Mio. EUR (Mio. €) zu präsentieren.
Die folgende Abbildung zeigt eine einfache Gewinn-und-Verlust-Rechnung (GuV), die für unterschiedliche Geschäftsbereiche eingesetzt werden soll. Die Daten sollen dabei automatisch wie folgt dargestellt werden:
- < 100.000 in EUR mit zwei Dezimalstellen, zum Beispiel 50.000,00 €
- >= 100.000 in TEUR mit einer Dezimalstelle, zum Beispiel 500,0 T€
- >= 1000.000 in Mio. EUR mit zwei Dezimalstellen, zum Beispiel 1,50 Mio. €
Die automatische Einstellung für das Zahlenformat soll abhängig vom größten absoluten Wert in der Gewinn-und-Verlust-Rechnung erfolgen.
Der absolute Wert einer Zahl ist ihr Abstand von null auf der Zahlengeraden, unabhängig von ihrer Richtung. Er wird auch als Betrag einer Zahl bezeichnet und ist immer eine nicht-negative Zahl. Beispiel: Der absolute Wert der Zahl -100 entspricht somit 100.
Für diese Aufgabe können Sie die Bedingte Formatierung in Excel nutzen.
Zahlenformat für Werte < 100.000 mit bedingter Formatierung
Zunächst soll das Zahlenformat für die absoluten Werte unter 100.000 definiert werden.
Markieren Sie den Bereich, in dem die Zahlen dargestellt werden, und aktivieren Sie im Menüband die Befehlsfolge Registerkarte Start > Befehlsgruppe Formatvorlagen > Befehl Bedingte Formatierung > Befehl Neue Regel.
Es öffnet sich das Dialogfeld Neue Formatierungsregel.
Aktivieren Sie hier bei Regeltyp auswählen, den Eintrag Formel zur Ermittlung der zu formatierenden Zellen verwenden und erfassen Sie hier bei Werte formatieren, für die diese Formel wahr ist die folgende Formel:
=MAX(ABS($C$2:$C$11))<100000
Mit dieser Formel ermitteln Sie im Bereich C2:C11 die größte Zahl, und zwar unabhängig davon, ob es sich um eine positive oder negative Zahl handelt. Denn mit der Funktion ABS() wird zunächst der absolute Wert ermittelt. Das heißt, eine negative Zahl wird auch als positive Zahl interpretiert.
Aus diesen Werten wird dann der höchste Wert ermittelt und überprüft, ob dieser Wert kleiner als 100000 ist. Sollte der Wert kleiner als 100000 sein, dann müssen Sie jetzt das Zahlenformat definieren, das angezeigt werden soll.
Klicken Sie hierzu rechts unten auf die Schaltfläche Formatieren.
Es öffnet sich das Dialogfeld Zellen formatieren.
Aktivieren Sie hier die Registerkarte Zahlen und klicken Sie dann auf die Kategorie Währung. Hier können Sie das gesuchte Zahlenformat direkt auswählen, da es sich um ein Standardformat handelt.
Bestätigen Sie Ihre Einstellungen, indem Sie alle offenen Dialogfelder durch Klick auf OK schließen. Die folgende Abbildung zeigt das Ergebnis.
Zahlenformat für Werte >= 100.000
Nun definieren Sie das Zahlenformat für die Werte >= 100.000. Gehen Sie hierzu so vor, wie oben beschrieben. Sie erstellen eine neue, zweite Regel für die Bedingte Formatierung.
Erfassen Sie für diese zweite Regel als Bedingung im Dialogfeld Formatierungsregel die Formel:
=MAX(ABS($C$2:$C$11))>=100000
Die Formel prüft, ob der höchste absolute Wert größer oder gleich 100.000 ist.
Definieren Sie als Nächstes das Zahlenformat, das angewendet werden soll, wenn die Bedingung zutrifft. Klicken Sie hierzu auf die Schaltfläche Formatieren.
Benutzerdefiniertes Zahlenformat für TEUR oder T€
Im Dialogfeld Zellen formatieren müssen Sie jetzt das entsprechende Zahlenformat hinterlegen. Da es sich um kein Standardformat handelt, müssen Sie dieses selbst definieren.
Klicken Sie auf das Register Zahlen und anschließend auf die Kategorie Benutzerdefiniert. Erfassen Sie bei Typ den folgenden Formatcode:
#.##0,0. "T€";[Rot]-#.##0,0. "T€"
Mit diesem Code werden nur volle Tausender mit einer Dezimalstelle in den jeweiligen Zellen angezeigt. In den Zellen sind aber weiterhin die kompletten Werte enthalten, sie werden nur verkürzt als T€ angezeigt.
Hinweis: Für das Ergebnis in der folgenden Abbildung wurden die Beträge aus der ersten Beispiel-GuV mit 10 multipliziert.
Zahlenformat für Werte >= 1.000.000
Als letzte Regel müssen Sie nun noch die Bedingung und den Formatcode (Zahlenformat) für die Variante mit Werten größer oder gleich 1.000.000 festlegen.
Die Formel für die Bedingung lautet:
=MAX(ABS($C$2:$C$11))>=1000000
Der entsprechende Code für das Benutzerdefinierte Zahlenformat ist:
0,00.. "Mio. €";[Rot]-0,00.. "Mio. €"
Erfassen Sie diese in den entsprechenden Dialogfeldern. Sie finden diese in den folgenden Abbildungen dargestellt.
Hinweis: Wenn Sie für Ihren Bericht die Beträge nicht mit dem €-Zeichen, sondern mit EUR, Euro, TEUR, Mio. EUR, CHF, Fr. oder einer anderen Währungseinheit formatieren wollen, ändern Sie dies einfach bei Ihrem jeweiligen benutzerdefinierten Zahlenformat.
Für das Ergebnis in der folgenden Abbildung wurden die GuV-Beträge aus dem ersten Beispiel mit 1.000 multipliziert.
