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

当サイトはWordとExcelの実力を鍛える最高水準の特訓問題集です。リンクを張るのは自由です。みんなで勉強しましょう!

【Excel】ユーザー定義表示形式のサンプルの意味を全部解説します


ユーザー定義の表示形式は書式記号で表しますが、記号のルールについては別の記事で「完全総まとめ」として解説しています(【Excel】セルの表示形式「ユーザー定義」書式記号完全総まとめ)。

今回は、完全総まとめのルールに従って、セルの書式設定の画面にリストで表示される書式記号のサンプルについて、その読み方と使い方を解説します。上から順にグループに分けて解説します。

なお、解説の中で【カッコ書き】で表しているのは上記の記事のことです。例えば、【2章1節例外4】は、完全総まとめ第2章第1節の例外4のことです。

f:id:waenavi:20181107193248g:plain

 

「セルの書式設定」完全マスターシリーズ(この記事は第5回です)
  1. Excel「セルの書式設定」最低限練習すべき設定と解除の特訓問題50問
  2. 【Excel】均等割り付けの設定と解除の方法と、種類を見分ける練習
  3. 表示形式はセルの書式設定なのでセルの値は変わらない(表示形式の基本)
  4. セルの表示形式「ユーザー定義」書式記号完全総まとめ
  5. ユーザー定義表示形式のサンプルの意味を全部解説します
  6. 表示形式で単位を付ける、または単位を変えるときの注意点
  7. Excelで先頭にプラス+をつける、マイナスを三角△で表示する方法
  8. なぜ指数の表示形式「E」が表示されるのか、「E」の設定と解除の方法
  9. Excelの表示形式で「3+3=7」になるのは誤差か?操作ミスか?それとも教育の問題か?

 

目次

第1グループ:標準とアットマーク@

f:id:waenavi:20181107140442j:plain

[A] G/標準
[B] @

解説

  • 「G/標準」は標準の表示形式です【1章原則】。文字列を付加したいときは、その文字列を「二重引用符(”)」で囲みます。
  • 「アットマーク(@)」は文字列をそのまま表示する、文字列の形式です。数値で「@」を使うと文字列扱いになり使い方によっては計算できないことがあるのでおすすめしません。

応用例

  • 「G/標準"cm"」のように後ろに単位をつけることができます。「"西暦"G/標準"年"」のように前後に文字をつけることができます。

 

第2グループ:数値

f:id:waenavi:20181107140327j:plain

[A] 0
[B] 0.00
[C] #,##0
[D] #,##0.00

実際の表示(Excel)

f:id:waenavi:20181107152813j:plain

共通解説

  • 「#」「0」の記号はいずれも、1文字で1ケタ(0~9)を表します【2章1節原則】。ただし、整数部分(小数点より左側のこと)は何ケタでも構いません。「#」「0」の個数を問わず、すべて表示されます【2章1節例外3】
  • 1の位を「0」にしているのは、「#」にしてしまうと四捨五入で0になったときに表示されなくなるからです【2章1節例外4】。基本的に整数部分は1の位を0、それ以外を#にしておくのが無難です。
  • 負の数であれば自動的に先頭にマイナスが付きます【2章1節原則】

違い

  • [A][C][B][D]の違いは後ろの「.00」の有無です。[A][C]のように、小数点がない場合は四捨五入で整数となります【2章1節例外2】[B][D]のように、小数点のあとに0が2個ある場合は小数第2位までの強制表示となります。小数第3位以降があれば四捨五入となります【2章1節例外3】。逆に小数第2位までない場合は後ろに0を補って強制的に2桁表示します【2章1節例外1】
  • [A][B][C][D]の違いは先頭の「#,##」の有無です。[C][D]のように、カンマが「#」で囲まれている場合はカンマ桁区切りになります【2章2節原則】。カンマ桁区切りは先頭に「#,##」をつけるのが慣例です。

個別解説

  • [A]は、整数表示で小数点以下四捨五入となります。
  • [B]は、小数第2位まで強制表示で、小数第3位四捨五入となります。
  • [C]は、[A]のカンマ桁区切りです。
  • [D]は、[C]のカンマ桁区切りです。

応用例

  • 「0,」「0.00,」と最後にカンマをつけると1000単位、「0,,」「0.00,,」と2つにすると100万単位になります【2章2節例外1】。小数の場合もカンマは一番最後につけます。カンマを一番最後につけてもカンマ桁区切りにはなりません。
  • カンマ桁区切りでさらに1000単位にする場合は「#,##0,」のように、先頭に「#,##」をつけて最後にもカンマを付けます【2章2節例外2】

 

第3グループ:会計

f:id:waenavi:20181107143114j:plain

[A] _ * #,##0_ ;_ * -#,##0_ ;_ * "-"_ ;_ @_ 
[B] _ * #,##0.00_ ;_ * -#,##0.00_ ;_ * "-"??_ ;_ @_
[C] _ \* #,##0_ ;_ \* -#,##0_ ;_ \* "-"_ ;_ @_
[D] _ \* #,##0.00_ ;_ \* -#,##0.00_ ;_ \* "-"??_ ;_ @_

実際の表示(Excel)

f:id:waenavi:20181107152930j:plain

共通解説

  • セミコロン(;)があったら、最初にセミコロンの数を数えます。個数に応じて何種類の書式を表すかが分かります【7章原則】[A][D]はすべてセミコロンが3つなので「正;負;0;文字列」のパターンであることが分かります。区切って考えます。
  • 「_ 」アンダースコアと半角スペース(空白)の組み合わせは、半角スペースの文字幅の分だけ余白を取るという意味です【3章例外1】。「_ 」を前後に置くことで半角文字分のインデントが左右に入るので、左揃えもしくは右揃えにしてもセルの境界線にくっつかず数字が見やすくなるという工夫です。
  • 「* 」アスタリスクと半角スペース(空白)の組み合わせは、 セル幅いっぱいになるまで半角スペースで埋めるという意味です【3章例外2】。「* 」が左側にあると左側をスペースで埋めるので、右揃えになります。
  • 「#,##0」「#,##0.00」は前述。
  • 負の数の先頭にマイナスが必要な場合、通常は、書式記号にマイナスを付けなくてもよいですが【2章1節原則】、セミコロンを使った場合はマイナスをつけなければなりません【7章例外2】
  • 「@」は文字列をそのまま表示するという意味です【1章原則】

違い

  • [A][C][B][D]の違いは後ろの「.00」の有無です。0の場合に「??」としているのは、ハイフンの右にスペースを2文字分を入れることで文字を揃えようとしています【2章1節例外1】
  • [A][B][C][D]の違いは先頭の「¥」の有無です。円記号(¥)は二重引用符(”)で囲まなくてもよいです【1章例外1】

個別解説

  • [A]は、数値についてはカンマ桁区切り整数表示(#,##0)で右揃え(* )、負の数はマイナスをつけ、0の場合はハイフン("-")で右揃え、文字列はそのまま表示(@)します。ただし、常に左右の端に1文字分の余白(_ )があります。
  • [B]は、[A]を小数第2位表示にしたもので、0の場合はハイフンの右にスペースを2つ入れて(??)文字を揃えます。
  • [C]は、[A]に円記号(¥)をつけたものです。0の場合もハイフンに¥がつきます。ただし、「¥」は「* 」より左にあるので、「¥」は左揃え、数値は右揃えとなります【3章例外2】
  • [D]は、[C]を小数第2位表示にしたもので、0の場合はハイフンの右にスペースを2つ入れて文字を揃えます。

応用例

  • カンマ区切りがいらない場合は「#,##」の部分をすべて削除します。
  • +0;-0;0」「+#,##0;-#,##0;0」とすればプラスの記号を表示することができます。
  • 1000単位にする場合は「0」や「0.00」のあと(0とアンダースコアの間)に、それぞれカンマを付けます(前述)。

 

第4グループ:記号のない通貨

f:id:waenavi:20181107143124j:plain

[A] #,##0;-#,##0
[B] #,##0;[赤]-#,##0
[C] #,##0.00;-#,##0.00
[D] #,##0.00;[赤]-#,##0.00 

実際の表示(Excel)

f:id:waenavi:20181107153150j:plain

共通解説

  • セミコロン(;)があったら、最初にセミコロンの数を数えます【7章原則】[A][D]はすべてセミコロンが1つなので「正0;負」のパターンであることが分かります。
  • 「#,##0」「#,##0.00」は前述。
  • 負の数の場合にはマイナスをつけます【2章1節例外2】

違い

  • 「#,##0」と「#,##0;-#,##0」の違いについて、セミコロンを使わなければ自動的にマイナスが付くので【2章1節原則】ほぼ同じ意味です。ただし、-0.5より大きい負の数(-0.4など)は四捨五入・整数表示にすると0になりますが、「#,##0」では「0」になり、「#,##0;-#,##0」では「-0」になります。つまり、セミコロンが無い場合は四捨五入をした結果でマイナスをつけるかを判定し、セミコロンがある場合は四捨五入をする前の元の数で正負を判定します。
  • [A][C][B][D]の違いは後ろの「.00」の有無です(前述)。
  • [A][B][C][D]の違いは先頭の「[赤]」の有無です。文字の色は角かっこで囲んで先頭につけます【6章原則】

応用例

  • カンマ区切りがいらない場合は「#,##」をすべて削除します。
  • 1000単位にする場合は「0」や「0.00」のあとに、それぞれカンマを付けます(前述)。
  • 負の数の場合に、マイナスを付けずに赤色にしたいときは「[赤]#,##0」とします。さらに、カッコ書きにしたいときは第9グループ参照。
  • セミコロンを残して、その後を削除すると負の数は非表示になります【7章例外1】

 

第5グループ:円記号のある通貨

f:id:waenavi:20181107143150j:plain

[A] \#,##0;\-#,##0
[B] \#,##0;[赤]\-#,##0
[C] \#,##0.00;\-#,##0.00
[D] \#,##0.00;[赤]\-#,##0.00 

実際の表示(Excel)

f:id:waenavi:20181107153330j:plain

共通解説

  • 第4グループの先頭に円記号(¥)がについたものです。円記号(¥)は二重引用符(”)で囲まなくてもよいです【1章例外1】
  • 円記号を左で揃えたいときは、第3グループのように「¥」の後に「* 」が必要です【3章例外2】。三角の場合は「"▲"* #,##0」のようにします。

 

第6グループ:パーセント

f:id:waenavi:20181107143307j:plain

[A] 0%
[B] 0.00%

実際の表示(Excel)

f:id:waenavi:20181107153630j:plain

解説

  • 第2グループとの違いは「%」の有無です。最後にパーセント記号(%)をつけると、100倍した数値に%をつけます【2章3節原則】

応用例

  • 「#,##0%」とするとパーセンテージのカンマ区切りになります。
  • 第4グループのように「0%;[赤]-0%」とするとマイナスの時に赤色にすることができます。

 

第7グループ:指数

f:id:waenavi:20181107143317j:plain

[A] 0.00E+00
[B] ##0.0E+0

実際の表示(Excel)

f:id:waenavi:20181107153712j:plain

共通解説

  • Eを使うと整数部分(小数点より左側)の桁数にあわせた指数形式になります【2章4節原則】。m x 10nのような形にしてmE+nと表示します。

個別解説

  • [A]は、整数部分が「0」なので整数部分が1桁、小数第2位までの表示になります。指数は2桁です。
  • [B]は、整数部分が「##0」なので整数部分が3桁、小数第1位までの表示になります。指数は1桁です。ただし、指数部が3の倍数でないときは、整数部分の桁数を減らして、指数部を3の倍数にします【2章4節原則】。例えば、12345は本来なら123.5E+2になるはずですが、指数を3の倍数にするため12.3E+3となります。また、0.004は本来なら400.0E-5になるはずですが、指数を3の倍数にするため4.0E-3となります。

 

第8グループ:分数

f:id:waenavi:20181107143346j:plain

[A] # ?/?
[B] # ??/??

実際の表示(Excel)

f:id:waenavi:20181107175117j:plain

共通解説

  • 「スラッシュ(/)」で分数表示になります【2章4節原則】。その前に#があるときは帯分数表示になります。整数部分は何ケタでも構いません【2章1節例外3】
  • 整数の場合、分数の部分は表示されずスペースで補います。小数の場合は指定された桁数で表すことのできる最も近い分数になります。

個別解説

  • [A]は、分母分子が1桁になるような帯分数表示になります。
  • [B]は、分母分子が2桁になるような帯分数表示になります。「??」なので分母分子が1桁の場合はスペースで補い、スラッシュが揃うようにします【2章1節例外1】

 

第9グループ:負数にカッコをつける

f:id:waenavi:20181107143354j:plain

[A] $#,##0_);($#,##0)
[B] $#,##0_);[赤]($#,##0)
[C] $#,##0.00_);($#,##0.00)
[D] $#,##0.00_);[赤]($#,##0.00)

実際の表示(Excel)

f:id:waenavi:20181107175403j:plain

解説

  • 第5グループの円記号(¥)をドル記号($)に変えたものです。
  • 負の数の場合は、先頭にマイナスを付けない代わりにカッコ書きにしています。負の数だけカッコ書きにすると、正の数と負の数が閉じかっこの分だけ数字がずれます。そこで、正の数の場合は最後にアンダースコアと閉じかっこ「_)」をつけることによって、閉じかっこの文字幅の分だけスペースを補い、数字を揃えています【3章例外1】

 

第10グループ:和暦

f:id:waenavi:20181107143413j:plain

[A] [$-411]ge.m.d
[B] [$-411]ggge"年"m"月"d"日"

※Excel2016では [$-ja-JP]です。

実際の表示(Excel)

f:id:waenavi:20181107181336j:plain

 

共通解説

  • gは元号を表します。1個の場合はアルファベットの頭文字、2個の場合は漢字1文字、3個の場合は漢字2文字です【4章3節原則】。ただし、表示形式で数値を日本語に変換するときに正しく表示されないバグがあるため[$-411]をつけます【4章3節例外2】。Excel2016は [$-ja-JP]です。
  • eは和暦年を表します【4章3節原則】。ただし、1年は元年にはなりません。mは月、dは日を表します【4章1節原則】

個別解説

  • [A]は、元号アルファベット1文字で、和暦年月日を「e.m.d」の形式で表します。
  • [B]は、元号漢字2文字で、和暦年月日を「e年m月d日」の形式で表します。年月日はダブルクォートが必要です【1章原則】

応用例

  • 年月日が1桁と2桁が混ざると文字が揃わないので、すべて2桁で統一することがあります。その場合は2文字にして「gee.mm.dd」「gggee"年"mm"月"dd"日"」とします【4章1節原則】
  • 年月日のあいだに文字を入れる必要はないので「geemmdd」とすれば、7桁のコードになります(例:H020304)。
  • 年月日すべてを表示させる必要もないので、「[$-411]ggge"年"」だけにすれば和暦年だけになり、月日は非表示になります。

 

第11グループ:西暦年月日

f:id:waenavi:20181107143934j:plain

[A] yyyy/m/d
[B] yyyy"年"m"月"d"日"
[C] yyyy"年"m"月
[D] "m"月"d"日"

実際の表示(Excel)

f:id:waenavi:20181107181909j:plain

共通解説

  • どのような表示形式にしたとしても、セルの値はシリアル値のままです(後述)。
  • yyyyは西暦年4桁を表します。mは月、dは日を表します【4章1節原則】

違い

  • [B][C]の違いは「"d"日"」の有無です。元の値に日付があったとしても、「d」を使わなければ日にちは表示しません。
  • [B][D]の違いは「yyyy"年"」 の有無です。元の値に年があったとしても、「y」を使わなければ年は表示しません。

個別解説

  • [A]は、西暦4桁で、年月日をスラッシュで区切って表します。
  • [B]は、西暦4桁で、年月日を「y年m月d日」の形式で表します。年月日はダブルクォートが必要です【1章原則】
  • [C]は、西暦4桁で年と月だけ表示します。
  • [D]は、月日だけ「m月d日」の形式で表示して、年を表示しません。

応用例

  • 上の例のように、元のデータが年月日だったとしても、y,m,dを省略すれば表示しません。例えば、セルに2002年3月14日と入力しても、表示形式がyyyy-mであれば、2002-3となります(セルの値はシリアル値のままですが、表示されないだけです)。表示形式を「d」だけにすれば「14」になります(セルの値は14ではない)。
  • 年月日が1桁と2桁が混ざると文字が揃わないので、すべて2桁で統一することがあります。その場合は2文字にして「mm」「dd」とします【4章1節原則】。また、年月日のあいだに文字を入れる必要はないので「yyyymmdd」とすれば、8桁のコードになります(例:20020304)。

曜日について

  • 曜日は「aaa」または「aaaa」です【4章2節原則】。mm/dd(aaa)は11/02(月)のような表示になります。
  • 日付の隣のセルに曜日を表示するには、日付をイコールで参照して「a」の表示形式にすればよいです。セルの値はシリアル値のままですが、セルの表示が曜日になります。この点について、一部のサイトでは「a」の記号を使うときにはWEEKDAY関数が必要だ、などと意味不明な説明をしているようですが間違いです。「a」の記号は、セルの値はシリアル値のままで、シリアル値からダイレクトに日本語に変換する表示形式です。WEEKDAYやMODを使ったら「a」を使う意味がありません。

 

第12グループ:欧米か

f:id:waenavi:20181107143942j:plain

[A] m/d/yy
[B] d-mmm-yy
[C] d-mmm
[D] mmm-yy

実際の表示(Excel)

f:id:waenavi:20181107182133j:plain

解説

  • 「m」は3個以上で英語表示になります【4章1節例外】。その他は第11グループと同じです。ちなみに、「d」を3個以上にすると英語の曜日になります【4章2節原則】

 

第13グループ:時刻

f:id:waenavi:20181107144009j:plain

[A] h:mm AM/PM
[B] h:mm:ss AM/PM
[C] h:mm
[D] h:mm:ss
[E] h"時"mm"分
[F] h"時"mm"分"ss"秒"
[G] yyyy/m/d h:mm
[H] mm:ss
[I] mm:ss.0
[J] [h]:mm:ss

実際の表示(Excel)

f:id:waenavi:20181107192218j:plain

解説

  • 考え方は日付(第11グループ)と同じで、時間が「h」、分が「m」、秒が「s」で、アルファベット2つで2桁表示になります【5章原則】
  • 月の「m」と、分の「m」は全く同じ記号です。原則として「m」は月を表しますが、「m」を分として使うときには「h」または「s」と同時に使う必要があり、しかも「h」より右側、または「s」より左側で使う必要があります。例えば、「yyyy/m/d h:mm」の場合、「h」の左側にあるmは月を表しますが、右側にあるmmは分を表します。
  • 時刻はシリアル値の小数点以下の部分で計算するので、元のデータに日付が含まれても構いません。

違い

  • [A][B][C][D]の違いは「AM/PM」の有無です。12時間制表示で 「AM」または「PM」がつきます【5章例外3】
  • [H][I]の違いは「.0」の有無です。10分の1秒を表します【5章例外2】
  • [D][J]の違いは角括弧の有無です。24時間を超えたときの表示が異なります。例えば30時間の場合、「h」は1日+6時間という解釈で6になりますが、「[h]」はそのまま30と表示されます【5章例外1】

応用例

  • 「h」「m」「s」は、1日=24時間、1時間=60分、1分=60秒として、24時間、60分、60秒を超えないように換算して表示されます。超えるように表示をするには[h]、[m]、[s]とします。
  • 「m」を分として単独で使うときは、[m]または[mm]とします【5章例外1】
  • 100分の1秒は「.00」で表します。

 

動画のご紹介(無料)

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

 

「セルの書式設定」完全マスターシリーズ(この記事は第5回です)
  1. Excel「セルの書式設定」最低限練習すべき設定と解除の特訓問題50問
  2. 【Excel】均等割り付けの設定と解除の方法と、種類を見分ける練習
  3. 表示形式はセルの書式設定なのでセルの値は変わらない(表示形式の基本)
  4. セルの表示形式「ユーザー定義」書式記号完全総まとめ
  5. ユーザー定義表示形式のサンプルの意味を全部解説します
  6. 表示形式で単位を付ける、または単位を変えるときの注意点
  7. Excelで先頭にプラス+をつける、マイナスを三角△で表示する方法
  8. なぜ指数の表示形式「E」が表示されるのか、「E」の設定と解除の方法
  9. Excelの表示形式で「3+3=7」になるのは誤差か?操作ミスか?それとも教育の問題か?

 


Copyright(C)2018-2021 waenavi, All rights reserved. [www.waenavi.com 定礎 平成三十年八月]
Youtube | 公式サイト | twitter | 著作権 | 運営者情報 | お問い合わせ