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.

Einige Leute denken möglicherweise auch, dass Sie in diesem Fall eine bedingte Formatierung verwenden könnten. Durch die bedingte Formatierung können Sie die Darstellung der Nummer in der Zelle jedoch nicht ändern. Während Sie nach dem Wert suchen können (z. B. mehr als tausend oder eine Million), können Sie den Wert in der Zelle nicht teilen, runden und ein Suffix hinzufügen. Sie können auch keine negativen Klammern in Klammern setzen.

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

Dieser Tipp (10226) gilt für Microsoft Excel 97, 2000, 2002 und 2003.

Eine Version dieses Tippes für die Multifunktionsleistenoberfläche von Excel (Excel 2007 und höher) finden Sie hier:

Umgang mit negativen Zahlen in einem komplexen benutzerdefinierten Format.