「ユーザー定義の表示形式」Excel 上級講座　その10

表示形式を自分で設定する

表を作る時などで

例えば単位を”件”であったり”㎥”に一律設定したいという場合、

また、数値が”2.3”でも表示は”2.30”にして
小数点の位置を揃えて見栄えを良くしたい。

そんなときに使用しましょう。

設定すると↑のように小数点が並び、
単位も自動表示されるので見やすく、
楽になります。

手打ちで単位を入力すると、
関数で計算対象にした場合に文字列と判断されてしまい
エラーになったり、SUM関数で対象外になってしまいます。

内部数値に文字と数値を混同させないための設定でもあります。

ユーザー定義の設定

設定画面を開きましょう。

設定対象のセルを選択して
右クリック　→　セルの書式設定

表示形式　→　ユーザー定義　
ここから”種類”に直接入力することで自由に設定ができます。

先程の例では 「0.00”㎥”」 と設定しています。
小数点以下2桁表示で末尾に”㎥”を表示するという意味です。

では次で基本的な設定方法を確認していきます。

表示形式の設定ルール

“数値”について

“数値”に関する表示形式

まずは基本的な記号の意味を説明します。
これだけでは「？」ですがその後の例と一緒に確認していきましょう。

記号	説明
0	数字の表示指定。 入力桁数が少なければ桁数分を0で埋めます
＃	数字の表示指定。 入力桁数が少なくても何も表示しません。
？	数字の表示指定。 入力桁数が少なければ桁数分をスペースで埋めます。
,	カンマ。 入力することで1000桁区切りなどを表示。
.	ピリオド。 小数点設定時に使用。
;	セミコロン。；で区切ることで 「プラス値;マイナス値;ゼロ;文字列」の個別設定が可能
[DBNum?]	?に1〜3の数値を入れることで 数字の表示を漢字、大字、全角から指定できる。

数値の設定使用例

①＃
入力数値をそのまま表示
②000000
桁数不足分は0で埋めて表示
③#.##
小数点2桁表示(3桁目は四捨五入する)
④0.00
小数点2桁表示、不足分0で埋める
(3桁目があれば四捨五入する)
⑤?.??
小数点2桁表示、不足分はスペースで埋める
（3桁目があれば四捨五入する）
⑥#,###,
末尾の”,”で千単位表示を意味する。
また、”#,###”で3桁区切りを実施。

⑦〜⑨[DBNum?]
“?”の部分を1,2,3のどれかを指定して漢数字や全角文字で表示

“日付”について

日付に関わる表示形式について見ていきます。

ここでの表示結果は入力値が
[2020/09/02 03:05:07]だった場合
のものとします。

”年”に関する表示形式

入力値が[2020/09/02 03:05:07]だった場合です

ユーザー定義	表示結果	説明
yyyy	2020	年を西暦4桁で表示
yy	20	年を西暦2桁で表示
e	2	年を和暦で表示
ee	02	年を和暦2桁で表示
g	R	元号をアルファベット表示
gg	令	元号の漢字一文字を表示
ggg	令和	元号を漢字で表示

”月”と”日”と”曜日”に関する表示形式

入力値が[2020/09/02 03:05:07]だった場合です

ユーザー定義	表示結果	説明
ｍ	9	月を表示
mm	09	月を2桁で表示
mmm	Sep	月を英語略称で表示
mmmm	September	月を英語で表示
mmmmm	S	月を英語頭文字で表示
d	2	日を表示
dd	02	日付を2桁表示
ddd	Web	曜日を英語略称表示
dddd	Wednesday	曜日を英語表示
aaa	水	曜日を漢字一文字表示
aaaa	水曜日	曜日を表示

”時間”に関する表示形式

時間の表示形式には、修正されない欠陥がありまして、
分単独で表示設定ができません。
下の表をみると”分”を指定する方法が「m」と「mm」となっておりますが、これは”月”の表示形式と被っております。

それでは、”分”は表示できないのか？
”時(「h」か「hh」)”または”秒(「s」か「ss」)”の表示との組み合わせで使用する場合は自動判別で”分”の表示になります。

なぜ訂正されないのか、私が知らないだけなのか。
みなさんもご使用時は気をつけてください。

↓の表は入力値が[2020/09/02 03:05:07]だった場合です。

ユーザー定義	表示結果	説明
h	3	時を表示
hh	03	時を2桁表示
m	9	分を表示
mm	09	分を2桁表示
s	7	秒を表示
ss	07	秒を2桁表示
AM/PM	AM	午前をAM、午後をPMで表示
am/pm	am	午前をam、午後をpmで表示
A/P	A	午前をA、午後をPで表示
a/p	a	午前をa、午後をpで表示

午前午後の表示と時間の表示を併用すると、
12時間方式で時の表示が自動的に切り替わります。

↓こちらだけは日付の指定なく[03:05:07]だった場合です。

ユーザー定義	表示結果	説明
[h]	3	何時間経過しているかを表記（端数切捨て）
[m]	185	何分経過しているかを表記（端数切捨て）
[s]	11107	何秒経過しているかを表記

使用例

入力値	ユーザー定義	表示結果
2020/09/03	gggyy年mm月dd日	令和02年09月02日
3:05:07	[m]分s秒	185分7秒
17:05:07	h:mm AM/PM	5:05 PM

”文字”に関する表示形式

文字を表示させたい場合は””で囲みます。

ユーザー定義	説明
”件”	””で囲まれた「件」を表示。
@	入力値をそのまま表示。

入力するとそのまま表示される記号一覧

$　ドル記号	+　正符号	(　左括弧	:　コロン
^　キャレット	‘　アポストロフィー	{　左中括弧	<　小なり記号
=　等号	– 負記号	/　スラッシュ	)　右括弧
!　感嘆符	&　アンパサンド	~　チルダ	｝右中括弧
スペース

“色の指定”に関する表示形式

表示色を指定してその色で文字を表示することができます。
使用できる文字は次の通りです。

[黒]	[青]	[水]	[緑]
[紫]	[赤]	[白]	[黄]

これ以外に[色32]というように数値で指定して1〜56までの色の中から設定することができます。ちなみに[色2]は白です。

「；（セミコロン）」を使用した設定

さて最初に

；	セミコロン。；で区切ることで 「プラス値;マイナス値;ゼロ;文字列」の個別設定が可能

……と説明しました。こちらについて説明していきます。

4つの種類に対して表示形式を設定

プラス値;マイナス値;ゼロ;文字列の順番でしたね。

それではこちら↓に表示形式を設定してみましょう。

それぞれを

[赤];[青];[黒];[紫]

に指定しました。

では結果を見てみましょう。

色が変わりました。

変わりましたが「-50」の「-(負記号)」が消えています。
なので次のように訂正しました。

マイナスを追加しました。
というか表示形式の指定に「G/標準」が自動追加されています。
これが正しい表記だ！とエクセルに訂正されております。

ともあれ、マイナスが表示されるようになりました。

条件による表示形式を設定

ユーザー定義には条件を指定できます。

セミコロンで区切ることで条件を2つまで指定できます。

条件1;条件2;条件なし
(条件2と条件なしは省略することも可能)

説明の前に演算子のおさらいをしましょう。

演算子	意味
>5	対象が5よりも大きい
<5	対象が5よりも小さい
>=5	対象が5以上
<=5	対象が5以下
=5	対象が5と等しい
<>5	対象が5ではない

例えば100以上を赤色で表示
100未満を青色で表示に設定してみましょう。

「>=100」なら「赤」　；　「<100」なら「青」

という意味です。

このように適応されました。

ですがこの設定、設定内容が見づらく
あまり指定精度もよくない（この場合文字も赤色になる）、
それに加えて条件も2つまでしか指定できません。、

同様の結果を得たいのなら
条件付き書式設定を使用したほうが良いでしょう。

各設定方法を応用して組み合わせて様々な表示形式を設定できます。
しかし、表示形式よりも条件付き書式設定のほうが管理運営しやすいので補助的なものにとどめておいたほうが良いかと思います。

-Excel MOS対策講座-（順次追加作成中）
MOS2016 もくじへ
MOS2016 エキスパートもくじへ
MOS365&2019 もくじへ

-VBA講座-
未作成　

-関数の使い方-
VLOOKUP関数の使い方
MATCH関数の使い方
INDEX関数の使い方
IFS関数の使い方
CLEAN関数の使い方
AND OR NOT　関数の使い方
PHONETIC関数の使い方
XLOOKUP関数の使い方

-条件付き書式-
土日祝日に条件付き書式で色をつけろ！