[Excel] Tipps zu benutzerdefinierter Formatierung

[chris-ka]

Ursprüngliches Thema:
RE: Formatierung Ziffer 0 unsichtbar http://www.clever-excel-forum.de/Thread-...41#pid6041
Aus Forum Excel hierher verschoben.

Moderator



Hallo,
da ich für eine Schulung ohne hin gerade etwas für benutzerdef.Formate erstellen muss.
Habe ich heute was zusammengeschustert. Vielleicht kann das ja wer brauchen.
Ich habe den Beitrag auch in meinem Stammforum veröffentlicht, will aber nicht direkt den Link posten. Falls es den Admin gefällt, könnt Ihr dieses Post auch gerne bei Tipps unterbringen!

Grundlegendes: (Zahlen)
Markieren Sie zuvor die gewünschten Zellen und rufen danach die Zellformatierung auf.
folgende Möglichkeiten:

• mit Strg+1
  
• rechte Maustaste -> Zellen formatieren
  
• oder über das Menü 2007-2013 im Menü Start, 95-2003 im Menü Format
  

Zuerst sollten Sie überlegen wie Ihre Zahl erscheinen sollte:
Bei der Auswahl von:
0 -> die Zahl die in der Zelle steht wird optisch als ganze Zahl wiedergegeben. Bsp. 1234,567 Anzeige: 1235
0,0 -> Anzeige mit einer Kommastelle, jede Null die hinzugefügt wird ist eine Kommastelle mehr bei der Anzeige. Bsp. 1234,567 Anzeige: 1234,6
#.##0 ->mit Tsd. Punkt Bsp. 1234,567 Anzeige: 1.235
#.##0,00 Tsd.Punkt mit zwei Kommastellen Bsp. 1234,567 Anzeige: 1.234,57

Sollte nun ein Text bei der Zahl erscheinen können Sie diesen Text unter "" (Anführungsstrichen) eingeben.
#.##0 "Stk" würde bei 1234 -> 1.234 Stk anzeigen.
Texte können auch vor der Zahlenformatierung stehen oder auch vorne und hinten.
"ab" #.##0 "Stk" würde bei 1234 -> ab 1.234 Stk anzeigen.

Das waren auch schon die Grundlagen der benutzerdefinierten Zahlenformatierung und reichen zumeist schon aus und wahrscheinlich war Ihnen dies auch schon alles bekannt.

fortgeschrittene Zahlenformatierung:
Es ist möglich bis zu 4 Formate gleichzeitig zu formatieren:
positive Zahlen;negative Zahlen;Nullwerte;Texte
wichtig dabei ist der ; (Strichpunkt) der die Formate voneinander trennt.
Beispiel1:
+0;-0;;[ROT]@
würde positive Zahlen auch mit einem + (Plus) versehen, negative Zahlen mit - , Nullwerte nicht anzeigen, und Texte in roter Farbe ausgeben.
Sonderzeichen wie +,€,µ usw. brauchen nicht unter "" zu stehen!
Farben können unter [] eingegeben werden.[weiß],[schwarz],[gelb],[rot],[blau],[grün],[magenta]

Beispiel2
[blau]+#.##0;[rot]-#.##0;"Leer"
Texte werden jetzt nicht formatiert, positive Zahlen sind jetzt Blau mit +, neg. Zahlen in roter Farbe mit – und bei Nullwerten erscheint Leer

Nullwerte ausblenden wird des Öfteren gefragt:
Wenn im gesamten Tabellenblatt keine Nullwerte erscheinen sollten könnten Sie in den Optionen von Excel diese ausblenden und sparen sich die Formatierung!
Sollten in bestimmten Bereichen Nullwerte nicht angezeigt werden:
benutzerdefiniertes Format
[=0]""
Das würde die Standardformatierung belassen und nur Nullwerte würden nicht angezeigt werden.
Wie Sie bemerkt haben können unter eckigen Klammern auch Bedingungen eingeben werden.

Beispiel3
[Rot][>1000]#.##0;[<0]-#.##0;0
Das würde Zahlen größer als Tausend in Rot ausgeben, Negative mit –
Warum? Es gibt ja die bedingte Formatierung ja auch noch oder?
ja, aber da dies eine volatile Funktion ist gehe ich sehr sparsam damit um und verwende dafür lieber die normale Zellformatierung. Damit ist eine bessere Performance der Mappe gegeben.

Ein Punkt nach der Zahlenformatierung
#.##0. -> gibt den Zellinhalt in Tausender aus
#.##0.. .> in Millionen

Auch bis hierhin dürfte für sehr viele Leser nichts Neues dabei gewesen sein.

Feinheiten der benutzerdefinierten Formatierung:

das Malzeichen *
Der Stern trennt in links/rechts Formatierung.
"Stk"* #.##0
würde den Text "Stk" jetzt links in der Zelle anzeigen und die Zahl rechtsbündig, das Zeichen nach dem * gibt die Füllung dazwischen an. (in diesem Fall das Leerzeichen)
"Stk"*.#.##0
Jetzt würden zwischen Stk und Zahl Punkte sein.

Das Steuerzeichen \ (Backslash)
Angenommen Sie möchten eine Formatierung wie 123.456.789 haben.
000\.000\.000
Der Backslash sorgt dafür das das Zeichen, dass als nächstes kommt genau so dargestellt wird, natürlich hätten sie voriges Beispiel auch so lösen können:
000"."000"."000
Aber als notorischer Tastenverweigerer nehme ich zumeist den Backslash.

Farben mit [Farbe] setzen.
Versuchen Sie mal [Grün]0 als Format zu nehmen…grässliches Grün
Besser
[Farbe10]0

Sie können Farbe 1 -56 nutzen, wenn Sie davon eine Liste erzeugen wollen öffnen Sie eine neue Mappe und lassen mal folgenden VBA-Code laufen:

Sub Farbe()
Dim i As Integer
For i = 1 To 56
   With Cells(i, 1)
       .Value = 123456789
       .NumberFormat = "[Color" & i & "]0"
       .Offset(0, 1).Value = "[Farbe" & i & "]"
   End With
Next
End Sub

Das Abstandszeichen _- (Unterstrich und Bindestrich)
Das sorgt für einen Abstand vor bzw. nach der Zeilenbreite.

Beispiel zum Vergleich zuerst diese Formatierung:
(geben Sie zuerst ein paar pos. Zahlen ein, neg. Zahlen, Nullwerte und Text)
[Farbe10]+#.##0;[Rot]-#.##0;"";[Blau]"Text:"* @

danach
[Farbe10]+#.##0_-;[Rot]-#.##0_-;""_-;[Blau]_-"Text:"* @_-

Sie werden den Unterschied merken.

Sie können auch innerhalb von Zahlen formatieren:

Beispiel:
"Nr:"0* \/###
Das würde bei einer Eingabe von 123456
Nr:123 /456 ausgeben mit Abstand zwischen 123 und 456

oder
#\s 0\m 0
gibt bei 1234
12s 3m 4
aus.

Das Fragezeichen ?

Wenn Sie Kommastellen untereinander darstellen möchten.
Sie haben Zahlen untereinander stehen wie 7,525, 8,2, 6
und wollen diese 3 Zahlen nach dem Komma ausrichten.
Format der Zellen
0,0??
^^ einfach mal testen.

Datumsformatierung benutzerdefiniert

Eingabe:
T -> der Tag der jeweiligen Datumszelle wird einstellig ausgeben 1,2,3…
TT-> der Tag der jeweiligen Datumszelle wird zweistellig ausgeben 01,02,03…
TTT -> der Wochentag der jeweiligen Datumszelle wird abgekürzt ausgegeben Mo, Di, Mi,…
TTTT-> der Wochentag der jeweiligen Datumszelle wird ausgegeben Montag, Dienstag, …
M -> der Monat der jeweiligen Datumszelle wird einstellig ausgeben 1,2,3…
MM -> der Monat der jeweiligen Datumszelle wird zweistellig ausgeben 01,02,03…
MMM -> der Monat wird abgekürzt ausgegeben Jan, Feb, …..
MMMM -> der Monat wird ausgeschrieben
MMMMM -> der Monat wird mit einem Buchstaben angezeigt (??, für was das blos gut sein sollte)
JJ -> Jahr zweistellig
JJJ -> Jahr vollständig

Ich verwende fast immer bei Datum dieses Format

TTT* TT.MM.JJ

Zeitenformatierung:

h -> Stunde einstellig
hh -> Stunde zweistellig
[hh] oder auch [h] -> es werden mehr als 24 Std angezigt
m -> Minute einstellig
mm -> Minute zweistellig
[mm] -> Gesamtanzahl der Minuten
s -> Sekunde einstellig
ss -> Sekunde zweistellig
[ss] -> Gesamtsekunden

Stichworte für die Suche im Clever-Excel-Forum

benutzerdefinierte Formatierung
Zahlenformate
Nullwerte formatieren
Zeitformate benutzerdefiniert
Datumsformate benutzerdefiniert
Platzhalter mit benutzerdefinierter Formatierung
benutzerdefinierte Formatierung clever eingesetzt

Ergänzung durch Moderator:
Hier der Erläuterungen von chris-ka zusammengefasst in einer ausdruckbaren Tabelle: