Benutzerdefinierte Zahlenformate

Arbeiten mit benutzerdefinierten Zahlenformaten
Mit diesem Beitrag soll der Nutzer ein wenig in die Geheimnisse der benutzerdefinierten Zahlenformate
eingewiesen werden.
Inhaltsverzeichnis
1
2
2.1
2.2
2.3
2.4
2.5
3
4
4.1
4.2
4.3
4.4
4.5
4.6
4.7
4.8
4.9
5
Vorbetrachtungen
Zahlenformatcodes
Allgemeine Formate
Datumsformate
Zeitformate
Text- und Leerzeichenformate
Bedingungen und Farbmarkierungen
Struktur eines benutzerdefinierten Zahlenformats
Ausgewählte Beispiele
Ausblenden von Nullwerten
Zahlen bestimmter Größe andersfarbig darstellen
Darstellung der Wochentage Samstag und Sonntag mit roter Schrift
Datum mit automatischer Ortsangabe
Verkürzte Datumseingabe
Darstellung von Zahlen mit T€, Mill. oder Mrd. Angaben
Zahlen mit unterschiedlicher Anzahl von Dezimalstellen am Komma ausrichten
Sperren von Leerstellen vor der ausgegebenen Zahl
Gestalten von Telefonnummern, Fax-Nummern, Artikelnummern, Kursnummern usw.
Zahlenformate mit Shortcuts zuweisen
1
Vorbetrachtungen
In allen EXCEL - Versionen sind bestimmte Zahlenformate vorgegeben. Mit steigender Versionszahl
werden dem Nutzer immer mehr vordefinierte Formate angeboten.
Trotzdem stößt jeder Nutzer irgendwann auf ein notwendiges Format, dass er nicht vorgegeben
bekommt. In diesem Fall hilft nur die Kategorie „Benutzerdefiniert" aus dem Menü
„Format - Zellen -Zahlenformat".
Als Empfehlung kann gelten, dass gewünschte Zellen mit einem nahe liegenden Format vorformatiert
werden und dann mittels der Kategorie „Benutzerdefiniert“ den eigenen Bedürfnissen angepasst
werden.
Um die entsprechenden Ergebnisse zu erhalten, ist es notwendig, die Regeln für die Erstellung
benutzerdefinierter Zahlenformate zu kennen.
Fieting, Olaf
1 von 8
24.04.05, 14:57
2
Zahlenformatcodes
2.1
Allgemeine Formate
Zeichen
Bedeutung
Standard
Zahlen werden als ganze Zahlen entsprechend der eingegeben Stellen hinter dem
Komma (nicht überflüssigerweise eingegebene Nullen) und Brüche als
Dezimalzahlen dargestellt.
E+, e+, E-, e-
Darstellung von Zahlen, die die Spaltenbreite überschreiten (ohne die Spaltenbreite
zu verändern) in der Exponentialform.
0
Zeigt zusätzlich Nullen links oder rechts vom Komma in der angegebenen Anzahl
(auch wenn sie nicht vorhanden sind) an.
#
Zeigt zusätzliche Nullen nicht an (auch wenn sie eingegeben wurden).
?
Es werden Zahlen mit unterschiedlicher Anzahl von Dezimalstellen am Komma
ausgerichtet, ohne dass für fehlende Dezimalstellen Nullen aufgefüllt werden.
Dieser Code kann des Weiteren bei der Formatierung von Brüchen eingesetzt
werden, um die Genauigkeit bei der Bruchrechnung zu erhöhen.
Punkt
Setzt einen Tausenderpunkt, wenn er zwischen Platzhaltern angegeben wird.
Teilt eine Zahl durch 1000, wenn er hinter dem Zahlenformat angegeben wird. Das
kann dazu genutzt werden, um Zahlen in T€, Mill. oder Mrd. anzugeben, wenn ein
entsprechender Textzusatz gesetzt wird.
%
Die Zahlen werden im Prozentformat dargestellt. Dabei ist zu berücksichtigen, dass
jede eingegebene Zahl automatisch mit 100 multipliziert wird. Für Formeln der
Prozentrechnung bedeutet das, dass in der Formel auf die Eingabe der 100
verzichtet werden kann (muss).
*
Wiederholt ein Zeichen bis zur Auffüllung der Spaltenbreite, wenn es vor dem
Zeichen eingefügt wird.
2.2
Datumsformate
M
Monat wird als ein-/zweistellige Zahl ausgegeben.
MM
Monat wird immer als zweistellige Zahl (eventuell mit führender Null) ausgegeben.
MMM
Monat wird mit den drei ersten Buchstaben des Namens ausgegeben.
MMMM
Monat wird mit vollem Namen ausgegeben.
T
Wochentag wird als ein-/zweistellige Zahl ausgegeben.
TT
Wochentag wird immer als zweistellige Zahl (eventuell mit führender Null)
ausgegeben.
TTT
Wochentag wird mit den ersten zwei Buchstaben des Namens ausgegeben.
TTTT
Wochentag wird mit vollem Namen ausgegeben.
JJ
Jahr wird zweistellig ausgegeben.
JJJJ
Jahr wird vierstellig ausgegeben.
Fieting, Olaf
2 von 8
24.04.05, 14:57
2.3
Zeitformate
h
Stunden werden als ein-/zweistellige Zahl ausgegeben.
hh
Stunden werden immer als zweistellige Zahl (eventuell mit führender Null)
ausgegeben.
m
Minuten werden als ein-/zweistellige Zahl ausgegeben.
mm
Minuten werden immer als zweistellige Zahl (eventuell mit führender Null)
ausgegeben.
s
Sekunden werden als ein-/zweistellige Zahl ausgegeben.
ss
Sekunden werden immer als zweistellige Zahl (eventuell mit führender Null)
ausgegeben.
h AM/PM
Stunden (auch bei weiterer Zeitdifferenzierung) werden im 12-Stunden-Regime
(mit am/pm-Angabe) ausgegeben.
Es können auch die Zeitangaben mit am/pm oder a/p versehen werden. Dann erfolgt
die Ausgabe mit den entsprechend angegebenen Buchstaben. Es muß jedoch
berücksichtigt werden, dass die Angaben hinter der Zeitformatierung einheitlich
angegeben werden, da sonst Angaben wie „A1“ oder „A0“ erscheinen können.
[h]:mm
Gibt Zeitangaben über 24 Stunden als Gesamtzeit in Stunden aus.
[mm]:ss
Gibt Zeitangaben über 60 Minuten als Gesamtzeit in Minuten aus.
[ss]
Gibt Zeitangaben über 60 Sekunden als Gesamtzeit in Sekunden aus.
2.4
Text- und Leerzeichenformate
Zeichenart
Behandlung
Allgemeine Zeichen (außer den unten
aufgeführten)
Diese Zeichen müssen in doppelte
Anführungszeichen gesetzt werden oder es muss
Ihnen ein umgekehrter Anführungsstrich
vorausgesetzt werden.
Dollar, Komma, Bindestrich, Plus, Schrägstrich,
runde Klammer auf, runde Klammer zu,
Doppelpunkt und Leerzeichen
Diese benötigen keine Anführungszeichen.
Ausrufezeichen, ^, &, linkes Hochkomma, rechtes
Hochkomma, Tilde, geschwungene Klammer auf,
geschwungene Klammer zu; =, < und >
Das umgekehrte Anführungszeichen wird
automatisch gesetzt.
Unterstrich gefolgt von einem Zeichen
Es wird ein Leerzeichen in der Breite des
folgenden Zeichens erstellt.
@
Einschließen eines Textes in das Zahlenformat.
Fieting, Olaf
3 von 8
24.04.05, 14:57
2.5
Bedingungen und Farbmarkierungen
Für die Darstellung von Zahlenformaten können Farbfestlegungen und Bedingungen abgefragt
werden. Die Farbfestlegung und die Bedingungen müssen in eckige Klammern gesetzt werden.
Es stehen Ihnen die Farben in direkter Ansprache Schwarz, Cyan, Magenta, Weiß, Blau, Grün, Rot
und Gelb zur Verfügung.
Weiterhin können Sie in den eckigen Klammern den Ausdruck „Farbe n“ mit 0<= n <= 56 verwenden.
Für das Stellen von Bedingungen können folgende Abfragen benutzt werden:
<, >, =, >=, <= oder <>.
3
Struktur eines benutzerdefinierten Zahlenformats
Jedes benutzerdefinierte Zahlenformat kann bis zu 4 Abschnitte enthalten, die jeweils durch ein
Semikolon von einander getrennt werden. Dabei gelten folgende Festlegungen:
1. Abschnitt
2. Abschnitt
3. Abschnitt
4. Abschnitt
Format für positive Zahlen
Format für negative Zahlen
Format für Nullwerte
Format für Text
Bei der Formatierung kann auf Abschnitte verzichtet werden. Das Resultat hängt dann aber vom
Setzen der Semikola ab. Einige Möglichkeiten sollen am Beispiel „#.##0,00“ dargelegt werden.
#.##0,00
Die Formatierung gilt für alle Zahlenbereiche, d.h. für positive,
negative und Nullwerte.
#.##0,00;- #.##0,00
Die Formatierung gilt für positive und Nullwerte (erster Abschnitt) und
für negative Werte (zweiter Abschnitt).
#.##0,00;- #.##0,00;0,00
Die Formatierung gilt für positive Werte (erster Abschnitt), negative
Werte (zweiter Abschnitt) und Nullwerte (dritter Abschnitt).
#.##0,00;;
Es werden nur positive Werte dargestellt.
;#.##0,00;
Es werden nur negative Werte dargestellt.
;;#.##0,00
Es werden nur Nullwerte dargestellt.
;;;
Die in der Zelle eingetragene Zahl wird nicht dargestellt, bleibt aber in
der Zelle erhalten.
Dabei können natürlich noch eine Reihe weiterer Kombinationen erzeugt werden.
Fieting, Olaf
4 von 8
24.04.05, 14:57
4
Ausgewählte Beispiele
4.1
Ausblenden von Nullwerten
Nicht immer ist es notwendig, Nullwerte in der Tabelle darzustellen. An vielen Stellen tragen diese
dann zur Unübersichtlichkeit bei. Diesem Problem kann man beikommen, wenn z.B. über „Extras Optionen - Ansicht" die Nullwerte ausblendet werden. Das gilt aber dann für das gesamte
Tabellenblatt. Nicht immer ist das das gewünschte Ergebnis. Es ist oft nötig, die Nullwerte nur in
bestimmten Tabellenbereichen auszublenden. Dieses Problem kann dann nur mittels der
entsprechenden Formatierung erreicht werden.
#.##0,00;;
#.##0,00;-#.##0,00;
blendet negative Werte und Nullwerte aus
blendet nur Nullwerte aus
Dabei kann es in einigen Fällen zu gewissen Einschränkungen kommen. So kommt es z.B. bei der
Formatierung mit „#.##0,00;;“ dazu, dass die Schaltflächen „Dezimalstelle hinzufügen“ oder
„Dezimalstelle löschen“ nicht mehr reagieren.
Anderenfalls wird die Formatierung „Standard;;“ durch das Betätigen der o.g. Schaltflächen wieder
aufgehoben und durch die neu gestaltete Formatierung ersetzt.
4.2
4.3
Zahlen bestimmter Größe andersfarbig darstellen
[>1000][Blau]#.##0,00
Alle Zahlen größer Tausend werden blau dargestellt.
[>1000][Farbe 5]#.##0,00
Alle Zahlen größer Tausend werden blau dargestellt.
#.##0,00,[Rot]-#.##0,00;
Negative Zahlen werden rot dargestellt.
Darstellung der Wochentage Samstag und Sonntag mit roter Schrift
Wenn Sie zum Beispiel in den entsprechenden Zellen den Wochentag über die Formel
=WOCHENTAG(Datum)
bestimmt haben, können Sie mit folgendem Format den Samstag und den Sonntag rot darstellen:
[=1][Rot]TTTT;[=7][Rot]TTTT;TTTT
Dabei steht die 1 für Sonntag und die 7 für Samstag. Die Tage werden mit ihrer vollen Bezeichnung
dargestellt.
4.4
Datum mit automatischer Ortsangabe
Die folgende Variante ermöglicht Ihnen die Eingabe eines Datums, wobei beim Abschluss des
Vorgangs die festgelegte Ortsangabe automatisch vorangestellt wird.
“Berlin, “TT.MM.JJJJ“
In diesem Zusammenhang ist es interessant zu erwähnen, dass man auch weiterhin mit den
eingegebenen Zahlen Rechnen kann. D.h., dass man trotz des eingebundenen Textes auch weiterhin
die Differenz, usw. zwischen zwei Zeitangaben bilden kann. Das gilt auch für einige weitere
dargestellte Beispiele.
Fieting, Olaf
5 von 8
24.04.05, 14:57
4.5
Verkürzte Datumseingabe
Möchte man auf die Möglichkeit des Rechnens mit Datumsangaben verzichten, aber auf schnelle Art
eine Datumsangabe, z.B. nur durch Eingabe der Tages- und Monatszahl ohne Punktangaben,
erzeugen, so kann man das mit folgender Formatierung erreichen:
0#“.“##“.“1997“
Dabei muss aber berücksichtigt werden, dass wechselnde Jahreseingaben nicht möglich sind. Das
kann man aber mit folgender Variante erreichen:
0#“.“##“.19“##
Natürlich muss dann auf eine Fehlerprüfung seitens EXCEL verzichtet werden, da es sich ja um ein
Textformat und nicht mehr um eine Zahl handelt. Es kann also ohne weiteres ein 20. Monat
auftauchen. Weiterhin bedeutet es aber auch, dass führende Nullen unbedingt angegeben werden
müssen, da es sonst zu Fehldarstellungen kommen kann.
4.6
Darstellung von Zahlen mit T€, Mill. oder Mrd. Angaben
Bei der Formatierung wird hier der Punkt genutzt, der eine Teilung durch 1000 bewirkt.
So wird die Zahl 1.200.000 als 1.200 T€ ausgegeben, wenn Sie folgende Formatierung vornehmen:
#.##0. “T€“
Die gleiche Zahl wird als 1,2 Mill. ausgegeben, wenn Sie folgende Formatierung festlegen:
#.##0,0.. “Mill.“
Jeder weitere gesetzte Punkt bedeutet eine weitere Division durch 1000. Es muss dann aber die
entsprechende Bezeichnung hinten angesetzt werden.
4.7
Zahlen mit unterschiedlicher Anzahl von Dezimalstellen am Komma
ausrichten
Wenn Sie Zahlen am Komma ausrichten wollen, ohne eventuell fehlende Dezimalstellen mit Nullen
aufzufüllen, können Sie z.B. folgende Formatierung anwenden.
0,???
Die Anzahl der dargestellten Dezimalstellen hängt von der Anzahl der Fragezeichen ab. Ansonsten
wird bei der letzten geforderten Dezimalstelle „bildschirmmäßig“ gerundet. Intern bleibt die Zahl in
ihrem Wert erhalten.
4.8
Sperren von Leerstellen vor der ausgegebenen Zahl
Wenn es nicht erwünscht ist bei Ausdrucken vor angegebenen Zahlen durch Hinzusetzen von weiteren
Ziffern Manipulationen vorzunehmen, kann man durch das folgende Format erreichen, dass der
Leerraum bis zur linken Zellgrenze mit Sternchen aufgefüllt wird.
**#.##0,00 DM
Fieting, Olaf
6 von 8
24.04.05, 14:57
4.9
Gestalten von Telefonnummern, Fax-Nummern, Artikelnummern,
Kursnummern usw.
Vorwahlnummern der Telekom
Recht einfach ist das Problem für Vorwahlnummern oder andere Zahlenangaben, die eine führende
Null besitzen zu lösen.
0##
Dabei ist natürlich die Anzahl der führenden Nullen zu beachten. Hier wird genau eine Null
vorangestellt. Bei der Eingabe der entsprechenden Zahl kann auf die Eingabe der Null verzichtet
werden.
Sind mehrere Nullen z.B. zwei gefordert, so müssen diese auch definiert werden. Dann kann es
natürlich bei Eingaben von Zahlen mit verschiedener Anzahl von Nullen zu Problemen kommen.
Weiterhin ist bei Vorwahlnummern die maximale Länge der Vorwahl zu beachten. In der
Preisinformation der Telekom Teil 1 sind maximal 6 Stellen angegeben. Wollen Sie die Vorwahl auch
noch nach jeweils 2 Ziffern durch ein Leerzeichen trennen, wie im o.g. Dokument angegeben, müssen
Sie das Format in folgender Art angeben:
0# ## ##
Telefon-/Faxnummern der Telekom
Wollen Sie die Telefonnummern in gleicher Art darstellen, so ist folgendes Format zu empfehlen:
## ## ## ##
Damit können Telefonnummern bis zu 8 Stellen mit Zwischenraum nach je zwei Stellen dargestellt
werden.
Leider kann eine Verbindung von Vorwahl und Telefon-/Faxnummern nicht formatiert werden, da keine
allgemein gültige Aussage über die Länge der Vorwahl und der Nummern gegeben werden kann.
Weiterhin können Sie den Telefonnummern einen entsprechenden Text voranstellen:
“Tel.-Nr.:“ ## ## ## ##
Postleitzahlen mit Länderkennung
Sollen der Postleitzahl die Länderkennung des entsprechenden Landes vorangestellt werden, so kann
das mit folgender Formatierung gelöst werden:
D-00000
Fieting, Olaf
7 von 8
24.04.05, 14:57
4.10 Artikel- oder Kursnummern
In diesem Fall kommt der Formatierung entgegen, dass diese Nummern in der Regel nach einem
einheitlichen System organisiert sind. Dabei können zum Beispiel folgende Darstellungen auftreten, die
mit folgenden Formatierungsvarianten realisiert werden können:
Gewünschtes Format
Formatierung
Ergebnis
037/262/96
137/262/96
037/262/96
137/262/96
###“/“###“/“###
###“/“###“/“###
0##“/“###“/“###
0##“/“###“/“###
37/262/96
137/262/96
037/262/96
137/262/96
037-262-96
137-262-96
037-262-96
137-262-96
###-###-###
###-###-###
0##-###-###
0##-###-###
37-262-96
137-262-96
037-262-96
137-262-96
5
Zahlenformate mit Shortcuts zuweisen
Die Formatierung über Menü oder Symbolleiste ist den meisten Nutzern von EXCEL bekannt. Weniger
bekannt ist aber die Möglichkeit, den eingegebenen Zahlen mittels eines Shortcuts ein entsprechendes
Format zuzuweisen.
STRG+&
STRG+$
STRG+%
STRG+"
STRG+§
STRG+º
STRG+!
Standardzahlenformat.
Währungsformat mit zwei Dezimalstellen. Negative Werte werden rot
dargestellt.
Prozentangabe ohne Dezimalstellen. Negative Werte werden schwarz
dargestellt.
Exponentialdarstellung mit zwei Dezimalstellen. Negative Werte werden
schwarz dargestellt.
Datumsangabe im Format TT.MMM.JJ.
Zeitangabe in Stunden und Minuten.
Darstellung mit Tausendertrennzeichen und zwei Dezimalstellen, negative
Werte werden schwarz dargestellt.
In diesem Zusammenhang sollen noch zwei nützliche Shortcuts zum Einfügen des aktuellen Datums
und der aktuellen Systemzeit vorgestellt werden.
STRG+PUNKT
STRG+DOPPELPUNKT
Einfügen des Datums im Format TT.MM.JJ.
Einfügen der Zeit im Format hh:mm.
So kann man mittels dieser beiden Varianten in einer Zelle ohne weiteres folgendes Ergebnis erzielen.
26.10.1996, 20:45,
indem man zwischen beiden Shortcuts das Komma und ein Leerzeichen setzt. Ein Rechnen mit dieser
Darstellung ist jedoch nicht möglich.
Fieting, Olaf
8 von 8
24.04.05, 14:57