Douglas hat einige Probleme, sich mit einem benutzerdefinierten Format vertraut zu machen, das er benötigt. Er hat ein benutzerdefiniertes Format erstellt, in dem große Zahlen nach seinen Wünschen angezeigt werden, sodass 1.000.000 USD als 1,0 Mio. USD und 1.000 USD als 1,0.000 USD angezeigt werden. Dieses Format lautet wie folgt:

[> 1000000] $ . 0 ,, „M“; [> 1000] $ #. 0, „K“; $ #, # 0.0. Douglas möchte wissen, wie das benutzerdefinierte Format so angepasst werden kann, dass negative Zahlen auf dieselbe Weise angezeigt werden, jedoch in Rot mit Klammern um sie herum, z. B. ($ 1.0K).

Leider ist das, was Sie tun möchten, mit einem einzelnen benutzerdefinierten Format nicht möglich. Der Grund dafür ist, dass ein einzelnes benutzerdefiniertes Format nur vier Bedingungen haben kann, die jeweils durch ein Semikolon getrennt sind. Dies ist die allgemeine Syntax eines benutzerdefinierten Formats:

positive; negative; zero; text

Beachten Sie, dass das erste Format verwendet wird, wenn der Wert positiv ist, das zweite, wenn es negativ ist, das dritte, wenn der Wert genau Null ist, und das vierte, wenn der Wert Text ist. Während dies die allgemeine Syntax für benutzerdefinierte Formate ist, können Sie die Formate ein wenig nach Ihren Wünschen „fummeln“. Betrachten Sie das Format, das Sie verwenden:

[>1000000]$#.0,,"M";[>1000]$#.0,"K";$#,##0.0

Beachten Sie, dass gemäß der allgemeinen Syntax das Format vor dem ersten Semikolon für positive Werte, das nächste Format für negative Werte und das dritte für Nullwerte verwendet wird. Dies ist jedoch nicht die Art und Weise, wie Excel dieses benutzerdefinierte Format übersetzt. Es übersetzt es als „wenn mehr als 1.000.000, tun Sie dies; wenn mehr als 1.000 dies tun; sonst tun Sie dies“. Das Format enthält keine positive oder negative Konnotation. In der Tat werden alle negativen Werte mit der Standardbehandlung behandelt, die das dritte Format ist.

Sie versuchen, zwei positive Bedingungen (eine für Millionen und eine für Tausende) und zwei negative Bedingungen (wiederum für Millionen und Abertausende) zu definieren. Dies kann nicht in einem einzigen benutzerdefinierten Format erfolgen, unabhängig davon, wie Sie versuchen, es zusammenzusetzen. Stattdessen sollten Sie zwei benutzerdefinierte Formate verwenden, z. B.

[>=1000000]$#.0,,"M ";[>=1000]$#.0,"K ";$#,##0.0 [Red][<=-1000000]($#.0,,"M");[Red][<=-1000]($#.0,"K");[Red]($#,##0.0)

Das erste Format ist bei positiven Werten zu verwenden; Es handelt sich um eine Variation des Originalformats, das am ersten Tipp vorgeschlagen wurde. Das zweite Format ist mit negativen Werten zu verwenden. Diese benutzerdefinierten Formate müssen basierend auf dem Wert in der Zelle manuell angewendet werden.

Dies scheint eine Menge Arbeit zu sein, um die gewünschte Formatierung zu erhalten. Es ist möglich, ein Makro zu erstellen, das die Formate anwendet, aber das Makro wäre kein triviales Unterfangen. Es müsste überprüft werden, wie hoch der Wert in der Zelle ist, das richtige Format auswählen, das Format erstellen, es in das benutzerdefinierte Format für die Zelle einfügen und dann mit der nächsten Zelle fortfahren.

Sie können jedoch eines tun: Sie können die Verwendung eines benutzerdefinierten Formats mit den bedingten Formatierungsfunktionen von Excel kombinieren. Richten Sie die folgenden drei benutzerdefinierten Formate in Ihrem Arbeitsblatt ein:

_($#.0_K_);[Red]($#.0_K);;@ _($#.0,"K"_);[Red]($#.0,"K");;@ _($#.0,,"M"_);[Red]($#.0,,"M");;@

Anschließend können Sie mithilfe der Funktionen für die bedingte Formatierung (Registerkarte „Startseite“ der Multifunktionsleiste | Bedingte Formatierung | Regeln verwalten) sechs verschiedene Formatierungsregeln definieren. Wenn Sie auf die Schaltfläche Neue Regel klicken, um mit der Definition der einzelnen Regeln zu beginnen, wählen Sie oben im Dialogfeld Neue Formatierungsregel die Option Nur Zellen formatieren, die enthalten sind. Hier sind die sechs Regeln, die Sie definieren werden:

Cell Value <= -1000000 Cell Value >= 1000000 Cell Value between -999999 and -1000 Cell Value between 1000 and 999999 Cell Value between -999 and -1 Cell Value between 0 and 999

Wenn Sie jede dieser Regeln definieren, klicken Sie im Dialogfeld Neue Formatierungsregel auf die Schaltfläche Format. Daraufhin wird das Dialogfeld Zellen formatieren angezeigt, in dem Sie auf die Registerkarte Nummer klicken sollten. Dort können Sie die Kategorie Benutzerdefiniert auswählen und eines der drei von Ihnen definierten benutzerdefinierten Formate auswählen.

Hier sind diejenigen, die Sie auswählen sollten:

  • Wählen Sie für die ersten beiden oben aufgeführten Regeln für die bedingte Formatierung das dritte oben aufgeführte benutzerdefinierte Format.

  • Wählen Sie für die oben aufgeführten dritten und vierten bedingten Formatierungsregeln das zweite oben aufgeführte benutzerdefinierte Format.

  • Wählen Sie für die oben aufgeführten fünften und sechsten bedingten Formatierungsregeln das erste oben aufgeführte benutzerdefinierte Format.

Das ist es; Die Regeln für die bedingte Formatierung führen die Tests für Ihre Wertebereiche durch und wenden dann die richtigen benutzerdefinierten Formate für diese Zahlen an.

ExcelTips ist Ihre Quelle für kostengünstige Microsoft Excel-Schulungen.

Dieser Tipp (10227) gilt für Microsoft Excel 2007, 2010, 2013, 2016, 2019 und Excel in Office 365. Eine Version dieses Tippes für die ältere Menüoberfläche von Excel finden Sie hier:

Umgang mit negativen Zahlen in einem komplexen benutzerdefinierten Format.