Google Sheets-Kochbuch: Benutzerdefinierte Formate

Mit benutzerdefinierten Formaten kann man Zahldarstellungen erzeugen, die es in Google Sheets nicht als Voreinstellung gibt. Das betrifft insbesondere Einheiten (z.B. kg, km, Kryptowährungen ...), Bruchzahldarstellung, Datumsformate, Farben und Zahldarstellungen abhängig von Bedingungen.

Benutzerdefinierte Formate finden Sie im Menü unter Format->Zahl->benutzerdefinierte Währung/Datum und Uhrzeit (benuterdefiniert)/Benutzerdefiniertes Zahlenformat.

Sie verwenden die gleichen Formatcodes auch mit der Funktion =TEXT(Zahl;Formatcode als String). Allerdings stellt die Funktion TEXT keine Farben dar, und die Ausgabe der Funktion ist eben ein Text, keine Zahl.

Zahlen

Das Dezimaltrennzeichen ist im Deutschen das Komma (,), das Tausendertrennzeichen ist der Punkt (.). In den Formaten wird es aber genau umgekehrt eingestellt. Dort ist das Dezimaltrennzeichen der Punkt, das Tausendertrennzeichen das Komma.

Formatierungsmuster

Eine Zahl kann abhängig von ihrem Wert formatiert werden. Dazu geben Sie die Formate in folgender Reihenfolge an:

• positive Zahlen;negative Zahlen;Null;Text
  • Eine negative Zahl wird ohne negatives Vorzeichen ausgegeben (also nur der Betrag). Wenn Sie ein negatives Vorzeichen wollen, müssen Sie dieses in den Formatcode einschließen.

Mit einer Bedingungsangabe können Sie die Zahlenbereiche für die Formate auch ändern, so dass Sie bspw. unterschiedliche Formatierungen für Zahlen unterhalb oder oberhalb eines bestimmten Wertes festlegen können.

Um Formatierungsmuster zu verstehen, betrachten wir ein einfaches Beispiel:

• "positive Zahl";"negative Zahl";"Null";"Ihre Eingabe:" @
  • Befindet sich eine positive Zahl in der Zelle, wird "positive Zahl" ausgegeben.
  • Befindet sich eine negative Zahl in der Zelle, wird "negative Zahl" ausgegeben.
  • Befindet sich eine Null in der Zelle, wird "Null" ausgegeben.
  • Befindet sich ein Text in der Zelle, wird "Ihre Eingabe:", gefolgt von dem Text (das @-Zeichen) ausgegeben.

Formatierungsmuster

Eingabewert	Formatierter Wert
348	positive Zahl
-184	negative Zahl
0	Null
Leider keine Zahl	Ihre Eingabe: Leider keine Zahl

Sie können mit dem Wert der Zelle ganz normal weiterrechnen. Auch wenn eine Zeichenkette - z.B. "positive Zahl" zu sehen ist - steht in der Zelle eine Zahl, hier die "348".

Bedingungen im Formatierungsmuster

Für die ersten beiden Formatierungen können Bedingungen angegeben werden, so dass auch unterschiedliche Zahlbereiche formatiert werden können. Die dritte Formatierung gilt dann für alle Zahlen, auf die keine der beiden Bedingungen zutrifft. Treffen beide Bedingungen auf die Zahl zu, wird die erste Bedingung angewendet.

• [<100]"Wenig";[>1000]"Viel";"Dazwischen"
  • Gibt bei einer Zahl <100 das Wort "Wenig" aus, bei einer Zahl >1000 das Wort "Viel", bei allen Zahlen dazwischen das Wort "Dazwischen".
  • Was man damit schönes anstellen kann, sehen wir gleich bei den Token.

Bedingungen im Formatierungsmuster

Eingabewert	Formatierter Wert
99	Wenig
3903	Viel
394,32	Dazwischen

Farben

Es ist möglich, die Textfarbe im Formatierungsmuster zu setzen, wobei dann die Farbe abhängig vom Wert sein kann. Entweder:

• [Color] wobei Color einen der Werte Black, Blue, Cyan, Green, Magenta, Red, White oder Yellow annehmen muss.

Oder:

• [Color#] wobei # eine Zahl zwischen 0 und 56 sein muss. Für die Farbpalette siehe The 56 Excel ColorIndex Colors.

Token - Steuerzeichen

Es gibt vielfältige Steuerzeichen, um die Anzeige von Zahlen zu steuern.

Formatierungstoken und Beispiele

Code	Beschreibung	Zahl	Formatcode	Formatierte Zahl
#	Steht für eine Ziffer in einer Zahl.	22,49	#	22
	Darstellung mit zwei Nachkommastellen	22,49	#.##	22,49
0	Steht für eine Ziffer in einer Zahl. Ergänzt 0en, wenn in der Stelle keine Ziffer steht.	22,49	0	22
	Typische Anwendung: gleiche Anzahl von Nachkommastellen	22,49	000.000	022,490
?	Steht für eine Ziffer in einer Zahl. Ist die Ziffer an der Stelle nicht vorhanden, wird ein Leerzeichen ausgegeben.	22,49	?	22
	Typische Anwendung: Ausrichtung von Zahlen am Dezimalkomma. Wird auf dem iPad nicht angezeigt.	22,49	???.???	22,49
.	Der erste Punkt repräsentiert das Dezimalkomma in der Zahl. Folgende Punkte werden als Punkte dargestellt. Wird ein Punkt im Formatcode angegeben, wird das Dezimalkomma immer angezeigt, auch für ganze Zahlen.	22	#0.#	22,
	Beispiel für mehrere folgende Punkte	22,49	#.##...	22,49...
,	Steht ein Komma zwischen zwei Zahlzeichen (0, # oder ?), wird die Zahl mit Tausendertrennpunkten dargestellt. Wenn es den Zahlzeichen folgt, wird die Zahl jeweils mit drei Stellen weniger dargestellt.	1434322,49	#,#.#	1.434.322,5
	Typische Anwendung: metrische Vorsätze (Kilo / Mega / Giga ...)	1434322,49	#,#.#,, "Megatonnen"	1,4 Megatonnen
		1434322,49	#,#, "Kilotonnen"	1.434 Kilotonnen
_	Der Unterstrich sorgt dafür, dass das nächste Zeichen im Formatcode nicht angezeigt, sondern ein Leerzeichen gerendert wird. Das braucht man manchmal im Zusammenhang mit Formatierungsmustern, um die Ergebnisse für positive und negative Zahlen aneinander auszurichten.
*	Für Kompatibilität zu Excel-Formatcodes, wird ignoriert.

Prozente

Formatierungstoken Prozent

Code	Beschreibung	Zahl	Formatcode	Formatierte Zahl
%	Erscheint als Prozentzeichen in der Ausgabe. Die Zahl wird vorher mit 100 multipliziert.	0,38	#.0# %	38,0 %

Vor dem Prozentzeichen sollte stets ein Leerzeichen stehen, die automatische Formatierung macht das leider nicht. Im Englischen ist das Leerzeichen auch nicht notwendig.

Wissenschaftliche Notation

Die Wissenschaftliche Notation stellt eine Zahl als Produkt einer einstelligen Dezimalzahl (1 <= x <10) und einem Vielfachen von 10 dar. Bsp:

• 5.200 = 5,2 * 1000 = 5,2 * 10³
• 73.000 = 7,3 * 10000 = 7,3 * 10⁴
• 0,0038 = 3,8 * 0,001 = 3,8 * 10^-3

Leider erfolgt in einer Tabellenkalkulation nicht die Ausgabe mit Hochzahlen, sondern als E bzw. e.

Formatierungstoken Wissenschaftliche Notation

Code	Beschreibung	Zahl	Formatcode	Formatierte Zahl
E-	Formatiert die Zahl in wissenschaftlicher Notation mit großem E. Ein Vorzeichen vor dem Exponenten wird nur bei negativen Exponenten dargestellt.	5.200	#.# E-##	5,2 E3
E+	Formatiert die Zahl in wissenschaftlicher Notation mit großem E. Ein Vorzeichen vor dem Exponenten wird immer dargestellt.	5.200	#.# E+##	5,2 E+3
e-	Formatiert die Zahl in wissenschaftlicher Notation mit kleinem e. Ein Vorzeichen vor dem Exponenten wird nur bei negativen Exponenten dargestellt.	0,0038	#.# e-##	3,8 e-3
e+	Formatiert die Zahl in wissenschaftlicher Notation mit kleinem e. Ein Vorzeichen vor dem Exponenten wird immer dargestellt. Ich empfehle diese Darstellung, wenn nichts anderes vorgegeben wird.	73.000	#.# e+##	7,3 e+4

Bruchzahlen

Erscheint ein / zwischen zwei Zahleichen (#, 0, ?) wird die Zahl als Bruchzahl ganzer Zahlen dargestellt, dabei versucht Google Sheets zu kürzen, soweit das möglich und durch das Zahlenformat vorgegeben ist. Je nach Format werden Zahlen als gemischter oder als unechter Bruch dargestellt. Der Nenner kann auch fest vorgegeben werden.

Eine kleine Erinnerung an den Mathematikunterricht: die Zahl über dem Bruchstrich bezeichnet man als Zähler, die Zahl unter dem Bruchstrich als Nenner. Ein unechter Bruch ist ein Bruch, dessen Zähler größer ist als der Nenner, also z.B. ${\frac {12}{5}}$ Der Zähler ist hier 12, der Nenner 5. Unechte Brüche werden üblicherweise als gemischte Brüche dargestellt, also als die Summe aus einer ganzen Zahl und einem Bruch. ${\frac {12}{5}}=2{\frac {2}{5}}$

Wenn der Zähler 0 ist, wird der Bruchteil gar nicht dargestellt. / funktioniert nicht in Kombination mit der wissenschaftlichen Darstellung oder einem Punkt (für das Dezimalkomma im Deutschen).

Beispiele für Bruchzahldarstellung

Formatcode	Beschreibung	Zahl	Formatierte Zahl	Wert der formatierten Zahl
#/#	Formatiert die Zahl als (unechten) Bruch. Der Bruch ist unecht, wenn die Zahl größer als 1 ist. Die Anzahl der Zahlzeichen im Nenner gibt an, wieviele Stellen der Nenner haben darf, hier nur eine Stelle. Die Darstellung als Bruchzahl ist im Beispiel nicht exakt, weil es keinen ganzzahligen Bruch mit einer Stelle im Nenner gibt, der die Zahl darstellen kann.	23,275	163/7	≈ 23,286
#/##	Hier werden zwei Stellen im Nenner verwendet. Die Darstellung als Bruchzahl ist im Beispiel exakt.	23,275	931/40	= 23,275
# #/#	Darstellung als gemischter Bruch, eine Stelle im Nenner. Die Darstellung als Bruchzahl ist im Beispiel nicht exakt.	23,275	23 2/7	≈ 23,286
# #/8	Darstellung als gemischter Bruch, der Nenner ist 8. Die Darstellung als Bruchzahl ist im Beispiel nicht exakt.	23,275	23 2/8	= 23,25
# #/80	Darstellung als gemischter Bruch, der Nenner ist 80. Die Darstellung als Bruchzahl ist im Beispiel exakt. Hier wird nicht gekürzt, da der Nenner fest vorgegeben ist.	23,275	23 22/80	= 23,275
# #/##	Darstellung als gemischter Bruch, der Nenner ist zweistellig. Die Darstellung als Bruchzahl ist im Beispiel exakt. Hier kürzt Google Sheets.	23,275	23 11/40	= 23,275
# #/3	Darstellung als gemischter Bruch, der Nenner ist einstellig. Die Darstellung als Bruchzahl ist im Beispiel nicht exakt. Der Bruchteil wird nicht dargestellt, da 0,1 zu ${\frac {0}{3}}$ gerundet wird.	23,1	23	= 23

Text, Einheiten und Sonderzeichen

Text wird durch Hochkommata eingeschlossen. Dabei werden die folgenden Zeichen immer als Literale (also so wie eingegeben) dargestellt, und müssen nicht in Hochkommata eingeschlossen werden:

• $ - + ( ) : Leerzeichen
  • Insbesondere wird also jedes Leerzeichen im Formatcode auch in der Ausgabe dargestellt.
• Einzelne Zeichen können sie ebenfalls mit einem \ (Backslash) maskieren. Das ist z.B. bei den Formatcodes für Datum und Uhrzeit notwendig (h, m, s, d, y etc.).
• Viele Zeichen werden allerdings auch ohne Hochkommata als Text erkannt, und funktionieren auch ohne deren Eingabe.
• Das @-Zeichen übernimmt einen reinen Text-Wert in die Ausgabe.

Beispiele für Textdarstellungen

Formatcode	Beschreibung	Zahl	Formatierte Zahl
0.0# "g"	Einheit Gramm mit einer Genauigkeit von zwei Stellen hinter dem Komma.	0,348	0,35 g
0.000 "kg"	Einheit kg mit drei festen Stellen hinter dem Komma.	22,49	22,490 kg
▲_-0.0 %;▼-0.0 %;◄►?0.0 %	Darstellung als Prozente mit Unterscheidung zwischen positiven, negativen und gleichbleibenden Ergebnissen. Der Unterstrich vor dem Formatcode für positive Zahlen sorgt dafür, dass das Minus (-) als Leerzeichen gerendert wird, um positive und negative Ergebnisse aneinander auszurichten.	0,25	▲ 25,0 %
		0	◄► 0,0 %
		-0,25	▼ -25,0 %

Datum und Uhrzeit

Das Verwenden von Formaten für Datum und Uhrzeit ist sehr einfach ... aber es gibt einen kleinen Bug in der Oberfläche. Das Menü zur Auswahl der verschiedenen Formate ist durch den Button "Anwenden" fast verdeckt, nur ein ganz kleines Stück des Menüs schaut heraus und kann angeklickt werden. In der Abbildung habe ich den Teil blau umrandet.

Bug in der Systemoberfläche für die Auswahl der Datumsformate

Klickt man das Menü an, kann man aus den verschiedenen Formatoptionen wählen.

Auswahlmenü für Datums- und Uhrzeitformate

Nun klickt man sich aus den verschiedenen Optionen das gewünschte Format zusammen. Im entstehenden Formatstring kann man für jeden Eintrag die verschiedenen Optionen wählen.

Kombination von Datumsformaten in Google-Sheets über das Menü.

Formatierungsoptionen für die Darstellung von Tagen.

Die verschiedenen Formatierungsstrings findet man im Handbuch, ich will hier nur die kurz auf die Optionen für die Dauer eingehen.

Formatierungsoptionen für Dauer

Wenn Sie die Formatierungsoptionen für die Zeitdauer auf ein Datum anwenden, wird die verstrichene Zeit seit dem 30.12.1800, 00:00 Uhr berechnet. Das ist meistens nicht das, was man möchte. Es geht vermutlich um Zeitdifferenzen.

Bsp.: 17.04.2022 12:00 Uhr - 14.04.2022 15:00 Uhr = 2,875 (2,875 Tage). Das sind 69 Stunden bzw. 4140 Minuten.

Formatierungstoken Zeit und Zeitdauer

Code	Beschreibung	Zahl	Formatcode	Formatierte Zahl
h	Stunde. Es wird die Stunde des jeweiligen Datums dargestellt.	2,875	hh	21
[h]	Stundendifferenz. Die Zeitspanne wird in Stunden umgerechnet.	2,875	[hh]	69
(h)m(s)	Minute, aber nur, wenn Stunde vorausgeht oder Sekunde folgt. Sonst Monat. Es wird die Minute des jeweiligen Datums dargestellt.	2,01	hh:mm	00:14
[m]	Minutendifferenz. Die Zeitspanne wird in Minuten umgerechnet.	2,01	[mm]	2894
[s]	Sekundendifferenz. Die Zeitspanne wird in Sekunden umgerechnet.	2,01	[ss]	173664

Links

Die englischsprachige, vollständige Referenz: Handbuch

Die Beispiele auf dieser Seite wurden getestet: 04/2022