わえなび ワード&エクセル問題集

ワードとエクセルの練習問題の動画を「Youtube」で無料公開しています

【Excel】セルの表示形式「ユーザー定義」書式記号完全総まとめ

セルの書式設定(表示形式)には「ユーザー定義」という、自分で表示形式を設定できる機能があります。

f:id:waenavi:20180820200445g:plain

今回は、ユーザー定義で使用できる「書式記号」について、Microsoftの公式サイトより詳しい完全なマニュアルを作ってみようという企画です。今後も随時追記して完全を目指します。

なお、スマホやタブレットに対応するため、すべて箇条書きにしています。また、【 】で囲まれた部分は補足説明です。

 

目次

第1章 標準書式と文字列

原則

表示形式を何も設定しない標準書式は「G/標準」である。
また、セルの値を文字列としてそのまま表示するには「アットマーク(@)」を使うが、@は数値の時にうまくいかないことがある。
文字列を付加するには「二重引用符(”)」で囲み、「"文字列"」とする。連結演算子の「&」は不要。

  • G/標準
     0.123 → 0.123
  • G/標準"様"
     松平竹千代 → 松平竹千代様
  • G/標準"cm"
     170→ 170cm
  • @
     東京 → 東京
  • @"様"
     木下藤吉郎 → 木下藤吉郎様
  • @" 様"【原則としてスペースはダブルクォートの中】
     木下藤吉郎 → 木下藤吉郎 様
  • "JR"@"駅"
     東京 → JR東京駅
  • "なし"
     @がないので、値に関係なく「なし」と表示される

 

例外1

円記号(¥)、プラス(+)、マイナス(-)、カッコなどの記号は「二重引用符(”)」で囲まなくてもよい。

囲まなくてもよい記号は次のとおり。
¥ 円記号・$ ドル記号・+ 正符号・( 左かっこ・: コロン・^ キャレット・' アポストロフィ・{ 左中かっこ・< 小なり記号・= 等号・- 負符号・/ スラッシュ記号・) 右かっこ・& アンパサンド・~ チルダ・} 右中かっこ・> 大なり記号・スペース文字

 

例外2 

1文字だけの場合は「二重引用符(”)」で囲む代わりに、「感嘆符(!)+文字」としてもよい。
「びっくりマーク(!)」は表示されず、その直後の文字がそのまま表示される。びっくりマーク(!)を直前につければすべての書式記号を文字として表示することができるエスケープという】
「@」をそのまま表示するには、「"@"」または「!@」とする。びっくりマーク(!)をそのまま表示するときは「!!」とする。

  • !0
     値に関係なく「0」と表示される
  • !@@
     hogehoge → @hogehoge
  • @!!
     hogehoge → hogehoge!

【Microsoftの日本語のヘルプに「エスケープは円記号(¥)を使用する」旨が記載されていますが誤りです(補足説明後述)】

 

例外3

セル内の改行は [Ctrl]+[J] を使う。

 

第2章 数値

第1節 小数点数の表示

原則

数値は、原則として「#」「0」「?」の記号で表し、いずれも1文字で1ケタの数字(0~9)を表す。「0」と入力しても0~9を表す。【本当に「0」と表示したいのであれば「!0」とする】
マイナスの数は特に指定しなくても先頭にマイナスが表示される。ただし、セミコロン(;)を使った場合を除く(後述)。

  • ###"円"【原則としてどの記号を使っても同じ】
     123 → 123円
     -123 → -123円
  • 000"円"
     123 → 123円
     -123 → -123円
  • ???"円"
     123 → 123円
     -123 → -123円

 

例外1

「#」「0」「?」の記号が多すぎる場合、「#」は非表示、「0」は0を表示、「?」はスペースに置き換わる。

  • ####
     34 → 34【変わらない】
  • 0000
     34 → 0034【先頭に0を補う】
  • ????
     34 →  34【左にスペースが2つ入る】
  • ###.###
     3.4 → 3.4【変わらない】
  • 000.000
     3.4 → 003.400
  • #.???
     3.4 → 3.4 【右にスペースが2つ入って小数点揃えができる】

 

例外2

小数点がない場合は四捨五入で整数となる。
小数点があって、右側に「#」「0」「?」がない場合は小数点で終わる。

  • #
     3.4 → 3
     3.6 → 4
  • #. 【小数点がある】
     3.4 → 3.
     3.6 → 4.

 

例外3

小数点の右側(小数部分)で「#」「0」「?」の記号が少すぎる場合は、その桁数で四捨五入をする。
小数点の左側(整数部分)は「#」「0」「?」の記号が少すぎても全部表示する。

  • #.#
     123.456 → 123.5【どの記号を使っても同じ】
  • 0.0
     123.456 → 123.5
  • ?.?
     123.456 → 123.5

 

例外4

小数点の左側(整数部分)が「#」だけしかなく、1未満の場合は0を表示せず、小数点から始める。
さらに、小数点がなく「#」だけの連続で、0.5未満の場合は空欄になる。
0を表示するのが普通なので、1の位は0にしておいた方が無難。

  • #.##
     3.45 → 3.45
     0.45 → .45
  • 0.00
     3.45 → 3.45
     0.45 → 0.45
  • ####
     0.5 → 1【例外2の整数表示によって1になる場合は表示する】
     0 →【何も表示されない】
     0.4 →【整数表示で0になるため何も表示されない】
  • ###0
     0 → 0
     0.4 → 0

 

例外5

「0」「#」「?」が連続している途中に文字列がある場合は、1の位を基準として右端から数えたときの位置に文字列を挿入する。したがって、桁数が多い場合は左側にあふれる。

  • 000-0000
     12345 → 001-2345【0012345】
     1234567 → 123-4567
     12345678 → 1234-5678【12345678】
  • ###-####
     
    123 → -123【0000123、0は非表示】
     
    12345 → 1-2345【0012345、0は非表示】
     
    12345678 → 1234-5678【12345678】
  • 00"A"000"A"00
     12345 → 00A123A45
     1234567 → 12A345A67
     12345678 → 123A456A78

 

第2節 カンマ桁区切り

原則

カンマが「#」または「0」で囲まれていれば、カンマ桁区切りになる。カンマは1回でよい。この場合も1の位は0にしておいた方が無難。

  • #,##0
     12000 → 12,000
     12000000 → 12,000,000
  • ¥#,##0
     12000 → ¥12,000
     12000000 → ¥12,000,000
  • #,##0.00
     12000 → 12,000.00
     12000000 → 12,000,000.00

 

例外1

1000単位にするときは最後にカンマをつける。小数の場合もカンマは一番最後につける。100万単位の場合はカンマ2個。端数は四捨五入になる。
ただし、カンマが「#」または「0」で囲まれているわけではないので、カンマ桁区切りにはならない。

  • ###0,
     12300 → 12
     12300000 → 12300
  • ###0,,
     12300 → 0
     12300000 → 12
  • ¥###0,
     12300 → ¥12
     12300000 → ¥12300
  • ¥###0,,
     12300 → ¥0
     12300000 → ¥12
  • ###0.0,【カンマは一番最後につける】
     12300 → 12.3
     12300000 → 12300.0
  • ###0.000,,
     12300 → 0.012
     12300000 → 12.300
  • 0.00,,"百万円"
     1230000 → 1.23百万円

 

例外2 

1000単位、100万単位で、さらにカンマ桁区切りにする場合は、最後のカンマと途中のカンマの両方が必要である。

  • #,##0,
     12300000 → 12,300
  • ¥#,##0,
     12300 → ¥12
     12300000 → ¥12,300

 

第3節 パーセンテージ

最後にパーセント記号(%)をつけると、100倍した数値に%が付く。

  • 0%
     0.081 → 8.1 → 8%
  • 0.0%
     2.8 → 280 → 280.0%

 

第4節 指数表記、分数

「E」を使うと指数表記になる。つまり、m x 10n(m=仮数部、n=指数部)であれば、「mE+n」と表示する。n<0であればマイナスが表示される。
原則としてmの整数部分の桁数(小数点の左側にある0や#の個数のこと。以下「D」という。)にあわせてnを決める。ただし、nがDの倍数でないときは、Dの倍数になるようにmの桁数を減らす。

「スラッシュ(/)」で分数表示になる。スラッシュのあとに数値を入れると分母が固定になる。

  • 0.00E+00
     12200000 → 1.22E+07【1.22x107D=1)】
  • #0.00E+0
     12200000 → 12.2E+6【12.2x106D=2、nが2の倍数なのでOK)】
  • ##0.0E+00
     0.0003 → 300.0E-06【300x10-6D=3、nが3の倍数なのでOK)】
     0.03 → 30.0E-03
    D=3なので本来なら「300.0E-04」となるが、nを3の倍数にするため「030.0E-03」となる。#なので先頭の0は非表示。】
  • ?/?
     0.25 → 1/4
     5.25 → 21/4【仮分数表示】
  • # ?/?
     5.25 → 5 1/4【帯分数表示】
  • ?/5
     1 → 5/5【分母を5で固定】

 

第5節 全角数字と漢数字

数値を全角数字または漢数字にするには[DBNum1]~[DBNum4]を先頭につける。
ただし、数値を日本語に変換するときに正しく表示されないバグがあり、[$-411]をつけるとうまくいく。【Excel2016では[$-ja-JP]】

  • [DBNum1][$-411]0
     1234 → 一二三四
  • [DBNum1][$-411]G/標準
     1234 → 千二百三十四
  • [DBNum2][$-411]0
     1234 → 壱弐参四
  • [DBNum2][$-411]G/標準
     1234 → 壱阡弐百参拾四
  • [DBNum3][$-411]0
     1234 → 1234
  • [DBNum3][$-411]#,##0
     1,234 → 1,234
  • [DBNum4][$-411]G/標準
     1234 → 千2百3十4

 

第6節 文字列との連結(文字間隔)

文字位置を揃える目的でスペースを入れるには「アンダースコア(_)+文字」を使う。
アンダースコア(_)とその直後にある文字は表示されず、その文字の幅の分だけ文字間隔が広がる
特に、アンダースコアと半角スペースの組み合わせ(_ )で、スペースの幅の分だけ文字間隔が空く。「_ #,##0_ 」のように、最初と最後に入れることによって左右に1文字分の余白をあけることができる(セルの左端と右端の境界線にくっつかないので数字が見やすくなる)。

  • #_ "人"【_のあとにスペース】
     1000 → 1000 人
  • _ #,##0_ 【_のあとにスペース】
     1000 → 1,000 【左右に余白ができる】
  • #_)"人"【_のあとにカッコ】
     1000 → 1000 人
     【かっこの幅だけ文字間隔が広がるので、かっこあるもの「(2000)人」と縦に揃えられる】

 

第7節 文字列との連結(繰り返し

文字を繰り返して、セルの幅いっぱいに埋める場合は「アスタリスク(*)+文字」を使う。「アスタリスク(*)」は表示されず、その直後の文字を列幅いっぱいに表示する。
特に、アスタリスクと半角スペースの組み合わせ(* )でスペースの連続を表すので、左端にあれば右揃え、右端にあれば左揃え、途中にあれば左右に分けた両端揃えを表す。

  • *-@
     hoge → -------------hoge
  • @*-
     hoge → hoge-------------
  • * #,##0【*のあとにスペース、右揃えになる】
     1000 →                   1,000
  • #,##0* 【*のあとにスペース、左揃えになる】
     1000 → 1,000
  • "▲"* #,##0【*のあとにスペース、左右に分かれる】
     1000 → ▲                  1,000
  • @* "御中"【*のあとにスペース、左右に分かれる】
     わえなび大学 → わえなび大学             御中

 

第3章 日付

第1節 西暦年月日

原則

西暦年が「y」、月monthが「m」、日が「d」を表す。「yy」「mm」「dd」は2桁、「yyyy」は4桁の表示になる。
なお、「y」「m」「d」の記号は、元のデータが日付の場合に、年、月、日をそれぞれ抽出する表示形式である(正確にはシリアル値で計算している)。それぞれの記号は単独で使うことも、併用して使うこともできる。

  • yy
     2001/2/3 → 01
  • yyyy
     2001/2/3 → 2001
  • m
     2001/2/3 → 2
     2001/12/3 → 12
  • mm
     2001/2/3 → 02
     2001/12/3 → 12
  • d
     2001/2/3 → 3
     2001/2/13 → 13
  • dd
     2001/2/3 → 03
     2001/2/13 → 13
  • yyyymmdd
     2001/2/3 → 20010203
  • yyyy/mm/dd
     2001/2/3 → 2001/02/03
  • yyyy"年"mm"月"dd"日"
     2001/2/3 → 2001年02月03日
  • mm"月"dd"日"
     2001/2/3 → 02月03日

【元のデータが年月日だったとしても、yの記号を省略すれば年は表示されません。mやdも同様です。

 

例外

「m」は3個以上で英語表示になる。

  • mmm
     2001/2/3 → Feb 【Jan~Dec】
  • mmmm
     2001/2/3 → February 【January~December】
  • mmmmm
     2001/2/3 → F 【頭文字J~D】

 

第2節 曜日

曜日は「a」を3つまたは4つにする。「d」にすると英語になる。
なお、「a」または「d」の記号は、元のデータが年月日のときにシリアル値を7で割った余りをもとに曜日を求めて、日本語または英語で表示する。【WEEKDAY関数やMOD関数を使用する必要はありません。 

  • aaa
     2001/2/3 → 土
  • aaaa
     2001/2/3 → 土曜日
  • ddd
     2001/2/3 → Sat 【Sun~Sat】
  • dddd
     2001/2/3 → Saturday 【Sunday~Saturday】
  • (aaa)
     2001/2/3 → (土)
  • aaa"曜"
     2001/2/3 → 土曜

 

第3節 和暦

原則

和暦年は「e」で、「ee」は2桁の表示になる。
その前に元号をつける場合は「g」を使う。明治以降に対応。新元号非対応。和暦の場合、1年のことを元年というが、Excelでは元年とは表示されない。

  • e
     1994/5/6 → 6
  • ee
     1994/5/6 → 06
  • g
     1994/5/6 → H
  • gg
     1994/5/6 → 平
  • ggg
     1994/5/6 → 平成
  • gggee
     1994/5/6 → 平成06

 

例外1

「r」を使うこともできる。「r」は「ee」と同じ。「rr」は「gggee」と同じ。

 

例外2 

日付を漢数字または全角数字にするときは、数値と同じように[DBNum1]~[DBNum3]を先頭につける。
ただし、数値を日本語に変換するときに正しく表示されないバグがあり、[$-411]をつけるとうまくいく。

 

第4章 時刻

原則

時間が「h」、分minuteが「m」、秒が「s」で、アルファベット2つで2桁表示になる。
原則として「m」は月monthを表すが、「h」より右側、または「s」より左側で使った場合に限り「m」を分minuteと解釈する。

  • h
     1:02:03→ 1
  • hh
     1:02:03→ 01
  • h:m
     1:02:03→ 1:2
  • hh:mm
     1:02:03→ 01:02
  • s
     1:02:03→ 3
  • ss
     1:02:03→ 03
  • m:s
     1:02:03→ 2:3
  • mm:ss
     1:02:03→ 02:03
  • h"時"mm"分"ss"秒"
     1:02:03→ 1時02分03秒
  • yyyy/mm/dd hh:mm:ss 【hhの後にあるmmが分を表す】
     2050/1/2 3:04:05 → 2050/01/02 03:04:05

 

例外1

「m」を分minuteとして単独で使うときは、[m]または[mm]とする。
また、時間を合計する場合など、24時間、60分、60秒を超える表示をするには、[h]、[m]、[s]とする。

  • [m]
     1:02:03→ 2
  • [mm]
     1:02:03→ 02
  • h:mm
     25:30 → 1:30【24時間制表示】
  • [h]:mm
     25:30 → 25:30【24時間を超える表示】
  • m:ss
     1:15:30 → 15:30
  • [m]:ss
     1:15:30 → 75:30
  • [m]
     1:15:30 → 75【1時間15分=75分】
  • [s]
     1:15:30 → 4530【1時間15分30秒=4530秒】

 

例外2 

10分の1秒は「.0」で表す。100分の1秒は「.00」で表す。

  • h:mm:ss.0
     1:15:30.45 → 1:15:30.4
  • h:mm:ss.00
     1:15:30.45 → 1:15:30.45

 

例外3

12時間制表示にして「AM」または「PM」をつけるには最後に「AM/PM」をつける。
「am/pm」「A/P」「a/p」を使うこともできる。

  • h:mm AM/PM
     1:15:30 → 1:15 AM

 

第5章 文字色指定

フォントの色の指定をするときは、色の名前を角かっこで囲んで、先頭に指定する。次の8色が使える。
[黒]、[白]、[赤]、[緑]、[青]、[黄]、[紫]、[水]【色番号1~8】
また、[色1]~[色56]の56色のカラーインデックスを使うこともできる。
f:id:waenavi:20180820223132j:plain

  • [赤]#,###
     -12345 → -12,345
  • [色3]#,###
     -12345 → -12,345

 

第6章 セミコロン

原則

表示形式はセミコロンで区切ることによって、1つのセルに対して同時に1種類~4種類の書式を定義することができる。
(例)_ ¥* #,##0.00_ ;_ ¥* -#,##0.00_ ;_ ¥* "-"??_ ;_ @_

  • セミコロンがない場合はすべての値に対して1種類の書式が適用される(通常はこの形式である)
  • セミコロン1個 A;B
     プラスとゼロのとき書式Aが適用される
     マイナスのとき書式Bが適用される
  • セミコロン2個 A;B;C
     プラスのとき書式Aが適用される
     マイナスのとき書式Bが適用される
     ゼロのとき書式Cが適用される
  • セミコロン3個 A;B;C;D 
     プラスのとき書式Aが適用される
     マイナスのとき書式Bが適用される
     ゼロのとき書式Cが適用される
     文字列のとき書式Dが適用される

 

例外1

A~Dが省略されている場合は非表示(空欄)となる。

  • 途中の省略
     A;;C → マイナスのときは空欄になる
  • 最後の省略
     A;B; → ゼロのときは空欄になる
     A;; → マイナスとゼロのときは空欄になる

 

例外2 

セミコロンを使った場合、マイナスをつけなければ、マイナスは非表示となる。

  • ¥#,##0;[赤]¥#,##0【マイナスがない】
     1234 → ¥1,234
     -1234 → ¥1,234
  • ¥#,##0;[赤]¥-#,##0【マイナスがある】
     1234 → ¥1,234
     -1234 → ¥-1,234

 

例外3

等号や不等号などの比較演算子を使用して条件を設定することができる。
比較演算子(<, >, =, >=, <=, <>)のあとに数値を入力して、角かっこ[  ]で囲む。
設定できる条件は2つまで(セミコロン2つまで)で、一番最初の条件が優先的に適用される。

  • [青][>10]#,###.00;[緑][>5]#,###.00;[赤](#,###.00)
     12 → 12.00
     6 → 6.00
     -2 → (2.00)

 

第7章 具体例の解説

第1節 サンプルの書式記号の意味 全部解説

第2節 先頭にプラス(+)を付ける、マイナスを三角(▲)にする

 


参考文献

ユーザー定義の表示形式を作成または削除する (C)Microsoft

support.office.com

 

補足説明

補足説明1
今回の記事は初心者向けの記事ではないので、動画も問題文も解説もありません。
また、「ユーザー定義」の操作説明やTEXT関数の使い方の説明は割愛させていただきます。

補足説明2
エスケープ文字は「エクスクラメーションマーク(!)」を使用するのが正しいです。
この点について、Microsoftの日本語版のヘルプに「円記号(¥)を使用する」旨が記載されていますが、これはマクロ(VBA)や外国語版のExcelの話であり、翻訳ミスです。
ちなみに、円記号(¥)はエスケープ不要、ダブルクォートも不要です。

 

動画のご紹介(無料)

こちらは表示形式の初心者向け練習問題の動画です。ぜひご覧ください。

 


Youtube わえなび公式チャンネル
http://www.youtube.com/c/waenavijp

チャンネル登録(無料)をすると最新の動画をチェックできます。
初心者向けの動画が大量にあります。

 

 

 

 

 

Copyright(C)2018 waenavi, All rights reserved. [www.waenavi.com 定礎 平成30年8月]