Excel書式記号を使って表示形式を変更する
Excelの書式設定を適切に行うと、読む人にとってわかりやすく表示されたシートを簡単に作ることができます。
金額を入力するセルに「通貨」の書式を設定しておくことで、数値を入力するだけで3桁ごとのカンマ区切りや通貨記号(円マークやドルマーク、など)が自動で入力されるようになります。
また、日付を入力したときに自動的に曜日を表示したり、時刻を入力したときに自動的にAM/PMを表示することも、書式の設定だけで実現できます。
「数値」「日付」「時刻」などの典型的な表示形式については、専用の画面から選択するだけで設定することもできますが、複雑な設定を行う場合には、Excelの書式記号を理解して設定する必要があります。
例えば、年月を和暦で表示したり、あるいは、数値を帯分数(1 1/2、のような)形式で表示する場合には、デフォルトの設定ではできないので、書式記号を使用して独自の書式を設定する必要があります。
この記事では、Excelで使用できる書式記号には何があるのか、それらがどのような意味なのかを解説いたします。
ひとつひとつの書式記号について意味を説明するとともに、具体的な使用例や、似た意味を持つ他の記号とどのように異なるのか、といったところを補足していきます。
大まかなカテゴリーごとに解説していきますので、特定の書式記号の使い方を知りたい場合は、その部分のみを読んでいただければと思います。
また、書式記号というものをあまり知らず、全体像を理解したい方は、最初から順番に読んでいただければと思います。
- 1. 書式記号を使用して書式を設定する方法
- 2. 書式記号の種類と使い方
- 2.1. 数値
- 2.1.1. セルに入力された数値が指定した桁数より多い場合
- 2.1.2. セルに入力された数値が指定した桁数に満たない場合
- 2.1.3. カンマ区切り
- 2.1.4. 分母と分子の間の境界線
- 2.2. 日付
- 2.3. 時間・時刻
- 2.3.1. 12時間表示と24時間表示
- 2.3.2. 上位の時間の経過を表す[]の使い方
- 2.4. 色付け
- 2.5. 特定の文字列を付与する
- 2.6. 特定の文字で桁埋めする
- 2.7. スペースを付与する
- 2.8. パターン分け
- 2.8.1. ①条件を指定しない場合
- 2.8.2. ②条件を指定する場合
- 3. おわりに
書式記号を使用して書式を設定する方法
まず、書式記号を使用して書式を設定する手順を解説します。
①「ホーム」タブにある書式プルダウンから「その他の表示形式(M)」を選択。
②「表示形式」タブの「分類」から「ユーザー定義」を選択。
③「種類(T)」のテキストボックスに書式を入力。
これで、書式記号を使用した書式を設定することができます。
書式記号の種類と使い方
数値
数値に関する記号は、以下の表の6つがあります。数字そのものを表す3つの記号と、小数点やカンマ区切りなど、数字の合間にあるものを表現する記号があります。
記号 | 意味 |
---|---|
# |
1桁の数値を表す。複数個続けることで、その桁数を表す。 指定した桁数に満たない場合、桁埋めはされない。 |
0 |
1桁の数値を表す。複数個続けることで、その桁数を表す。 指定した桁数に満たない場合、0埋めを行う。 |
? |
1桁の数値を表す。複数個続けることで、その桁数を表す。 指定した桁数に満たない場合、スペースで埋める。 |
. | 小数点を表す。 |
, | カンマ区切りを表す。 |
/ | (分数の)分子と分母の間の線を表す。 |
「#」「0」「?」はいずれも1桁の数値を表す記号であり、複数個続けることで桁数を表現します。
つまり、「#」であれば1桁の数値、「###」であれば3桁の数値を意味するということです。
「##.###」とすることで、整数部2桁、小数部3桁の数値を表すことも可能です。
セルに入力された数値が指定した桁数より多い場合
セルに入力された数値が指定した桁数よりも多い場合は、以下のように処理されます。
- 整数部:全ての桁が表示される。
- 小数部:指定した桁の1つ下の桁を四捨五入する。
例)
書式記号 | セルに入力する数値 | 表示 |
---|---|---|
#.# | 12.34 | 12.3 |
##.## | 123.456 | 123.46 |
セルに入力された数値が指定した桁数に満たない場合
「#」「0」「?」の3つの記号の違いは、セルに入力された数値が指定した桁数に満たない場合の挙動です。
- #:指定した桁数に満たない場合、桁埋めはされない。
- 0:指定した桁数に満たない場合、0埋めを行う。
- ?:指定した桁数に満たない場合、スペースで埋める。
例)
書式に「###.###」が指定されているときには、入力された数値がそのまま表示されています。
しかし、書式に「000.000」が指定されていると、不足している桁数の分、0が自動で付与されています。
また、「???.???」の場合には、不足している桁数の分だけスペースが付与されています。
「000.000」と「???.???」では、ともに不足している桁が埋められていることで、10.55と1.5の小数点の位置が揃った状態で表示されています。
カンマ区切り
書式記号の中にある「,」は、数値を3桁ごとに区切るときに使います。
金額を記載するときに「1,000,000円」といった形で、数値を3桁ごとに区切って表示することがありますが、
これは書式を「#,##0」とすることで実現できます。
カンマが1箇所しか記載されていないのですが、これだけで”3桁ごとに区切る”という意味になり、「1,000,000」や「1,000 ,000,000」といった表示ができるようになります。
なお、最後の1桁を「#」ではなく「0」としているのは、数値として0を入力したときに「0」と表示するためです。
最後の1桁も「#」としてしまうと、数値の0を入力したときに何も表示されなくなってしまいます。
分母と分子の間の境界線
「/」は分母と分子の境界線を示す記号です。これを使用して仮分数・帯分数の表示をすることができます。
仮分数とは、1を超える数値であっても全て分子と分母だけで表現する形式のことで、帯分数は、整数部を整数として表示し、残った部分のみ分数として表示する形式のことです。
例)「1.5」を仮分数・帯分数として表示する場合
- 仮分数:3/2
- 帯分数:1 1/2
仮分数を表示するには、分子+/+分母というように書式を設定します。
つまり、上記の例にあるように「1.5」を「3/2」と表示するには、「?/?」という書式を設定すればいいことになります。
帯分数を表示するには、整数部+半角スペース+分数部というように書式を設定します。
「1.5」を「1 1/2」と表示するには、書式を「0 0/0」と設定します。
日付
日付に関数する書式記号には以下の表のものがあります。
※一番右の列にある例は、2022(令和4年)年8月1日を入力した場合の表示内容です。
記号 | 意味 | 例 |
---|---|---|
yy | 西暦の年数(下2桁のみ) | 22 |
yyyy | 西暦の年数 | 2022 |
g | 元号(アルファベットにした場合の頭文字) | R |
gg | 元号(漢字の頭文字) | 令 |
ggg | 元号 | 令和 |
e | 和暦の年数 | 4 |
ee | 和暦の年数(1桁の場合は0埋め) | 04 |
m | 月の数字 | 8 |
mm | 月の数字(1桁の場合は0埋め) | 08 |
mmm | 月を表す英語(略称) | Aug |
mmmm | 月を表す英語 | August |
Mmmmm | 月を表す英語(頭文字) | A |
d | 日の数字 | 1 |
dd | 日の数字(1桁の場合は0埋め) | 01 |
ddd | 曜日を表す英語(略称) | Mon |
dddd | 曜日を表す英語 | Monday |
aaa | 曜日を表す漢字(頭文字) | 月 |
aaaa | 曜日を表す漢字 | 月曜日 |
時間・時刻
時間・時刻に関数する書式記号には以下の表のものがあります。
記号 | 意味 |
---|---|
h | 時(hour)を表す数字 |
hh | 時(hour)を表す数字(1桁の場合は0埋め) |
m | 分(minute)を表す数字 |
mm | 分(minute)を表す数字(1桁の場合は0埋め) |
s | 秒(second)を表す数字 |
ss | 秒(second)を表す数字(1桁の場合は0埋め) |
AM/PM、am/pm | 午前午後を表す |
A/P、a/p | 午前午後を表す(頭文字のみ) |
[] | 上位の時間の経過を表す |
12時間表示と24時間表示
時刻の12時間表示と24時間表示は、午前午後を表す書式記号が含まれているかどうかによって決まります。
「AM/PM」「A/P」などの記号が含まれていれば12時間表示になり、それがなければ24時間表示になります。
例)セルに「22:00」と入力する場合
書式記号 | 表示 |
---|---|
h:mm | 22:00 |
h:mm AM/PM | 10:00 PM |
上位の時間の経過を表す[]の使い方
[]は、上位の時間の経過を表す場合に使用します。
これは、例えば1時間30分を90分と表示したり、あるいは、2分を120 秒を表示するようなことを指しています。
使い方としては、上位の時間を含む部分に[]を付けるだけです。
例)セルに「1:30:00」と入力する場合
書式記号 | 表示 |
---|---|
h:mm:ss | 1:30:00 |
[mm]:ss | 90:00 |
※「m」「mm」といった書式記号は、時間を表す記号(「h」など)や秒を表す記号(「s」など)と並記されない場合、月を表す記号として解釈されます。そのため、上記の例では、後ろに秒を表す「ss」を付与しています。
色付け
書式記号を使用することで、文字色を指定することができます。以下の色の名前を[]で囲み、書式の先頭に記載します。
【色一覧】
- 黒
- 青
- 水
- 緑
- 紫
- 赤
- 白
- 黄
例)書式記号「[赤]0」と指定した場合
特定の文字列を付与する
書式記号を使用することで、入力された内容に対して特定の文言を付与することができます。
付与する文言は「”」(ダブルクォーテーション)で囲った上で、書式記号の中に含めます。
数値に対して特定の文字列を付与する場合には、上記で解説している数値の書式記号に対して、「”」で囲った文言を付け加えます。
一方、文字列に対して特定の文字列を付与する場合には、セルに入力された文字列を「@」で表し、それに対して付与する文字列を追記します。
例)
書式記号 | 入力された値 | 表示 |
---|---|---|
#,##0”円” | 1000 | 1,000円 |
@“様” | 佐藤 | 佐藤様 |
特定の文字で桁埋めする
「*」の後に特定の文字を入力することで、特定の文字でセルの桁埋めをすることができます。
例)
セルに入力された「1」が書式記号の左側の「0」に対応していて、その後の「*0」という書式によって「1」の右側のスペースが全て「0」で埋められています。
スペースを付与する
スペースを付与するには3つの方法があります。
記号 | 意味 |
---|---|
(スペース) | 1つのスペース |
_(アンダーライン) | 1つのスペース |
_【特定の文字】 | 【特定の文字】と同じ幅のスペース |
※記号列にある()で囲った部分は、書式記号ではなく補足説明です。
特定の文字と同じ幅のスペースを付与するやり方は、マイナスの数値を()で囲って表記する場合に、数字の位置を揃えるために用いられます。
例)マイナスの数値を()で囲って表示する例
プラスの数値の末尾に、「)」と同じ幅のスペースを付けることで、数字の位置が揃っています。
パターン分け
書式記号を使用すると、入力された値によって書式を切り替えることができます。
切り替える方法には、①切り替えの条件を指定しない方法と、②切り替えの条件を指定する方法の2つがあります。
①条件を指定しない場合
4種類の書式記号をセミコロンで区切って並べることによって、以下のような意味になります。
正(プラス)の数の場合の書式; 負(マイナス)の数の場合の書式; 0の場合の書式; 文字列の場合の書式
例)書式記号「[赤]0; [青]-0; [紫]0; [緑]@」を指定した例
②条件を指定する場合
条件を[]で囲って指定することで、その条件に該当する場合書式を指定することができます。
条件の指定には、以下の等号・不等号を使用することができます。
記号 | 意味 |
---|---|
= | 右辺と等しい |
< | 右辺より小さい |
> | 右辺より大きい |
<= | 右辺以下 |
>= | 右辺以上 |
<> | 右辺と異なる |
例)書式記号「[赤] [>100]0;[青]0」を指定した例
おわりに
この記事では、Excelの書式記号について解説しました。
使える記号の数が多く、最初に見たときには圧倒されてしまうかもしれませんが、しばらく意識して使ってみるとだんだんと慣れてくると思います。
全ての記号を覚えようとしなくても、必要になったときに調べながら活用していけば徐々に頭に入っていくので、ぜひ、積極的に活用してみてください。