Excelのセルの表示形式は、おもに数値の元データを変えないまま、表示する桁数を変える書式です。例えば、「123.00」のように小数点以下の0を表示する場合や「00123」のように先頭に0をつける場合がありますが、元の数値は全て123です。
ユーザー定義の表示形式でカンマ桁区切りは通常「#,##0」と表しますが、「#,###」「0,000」とは何が違うのでしょうか。数値は、原則として「#」「0」「?」の3つの記号で表します。これら3つの記号の意味と共通点、相違点を理解しなければ、「#,##0」の意味を理解することはできません。
そこで、今回は、ユーザー定義の表示形式のうち、数値を表す記号「#」「0」「?」の共通点と違い、カンマ桁区切り、小数点以下桁数の正しい設定方法について解説します。
目次
- 1.ユーザー定義の表示形式
- 2.まず、共通することを覚えよう
- 3.違いは何か
- 4.#と0を混ぜてみよう
- 5.カンマ桁区切りの方法
- 6.「#,###」と「#,##0」と「0,000」の違い
- 7.さらに詳しい説明はこちら
1.ユーザー定義の表示形式
セルの書式設定(表示形式)の画面を開き、ユーザー定義にします(参考:【Excel】表示形式はセルの書式設定なのでセルの値は変わらない(表示形式の基本))。
また、表示形式を「その他の表示形式」にする方法もあります。
2.まず、共通することを覚えよう
(1)数値の書式記号
問題
セルA1に整数値の「5」と入力した。ユーザー定義の表示形式で「0」と指定するとどうなるか。また、「#」「?」とした場合はどうか。
解説
セルA1に「5」と入力し、セルの書式設定の画面を表示します。ユーザー定義の表示形式で「0」と入力します。画面上特に何も変わりません。
ユーザー定義の表示形式に用いる記号のことを書式記号といいます。「0」は数値の0だけでなく、1文字の半角数字(0~9のいずれか1つ)を表します。「0」と入力しても0~9を表します。セルのデータが1桁の数値であれば、そのまま表示されます。したがって、セルの値が5であればそのまま5と表示されます。
表示形式を「#」とします。何も変わりません。
1文字の数字(0~9)を表す書式記号には「#」「0」「?」の3種類があります。そして、セルのデータが1桁の数値であれば、どの記号を用いたとしてもそのままの表示となります。
(2)書式記号より桁数の多い整数
問題
A列に3桁の整数を入力した。ユーザー定義の表示形式で「0」と指定するとどうなるか。また、「#」「##」とした場合はどうか。
解説
セルの書式設定の画面を表示します。ユーザー定義の表示形式で「0」と入力します。画面上特に何も変わりません。
「0」は原則として1文字の半角数字(0~9)を表します。しかし、セルに入力されている整数の桁数が書式記号の個数より多ければ、あふれた部分はそのまま表示されます。したがって、セルの値が3桁の整数で書式記号が1個であれば3桁すべて表示されます。
表示形式を「#」とします。何も変わりません。
これを「##」としても変わりません。このことは「?」でも同じです。
「#」「0」「?」はいずれも1文字の数字(0~9)を表す書式記号ですが、書式記号の個数よりも、セルに入力されている整数の桁数が多い場合はそのまま表示されます。
(3)元の数値が小数の場合
問題
小数の「123.456」「456.789」を入力した。ユーザー定義の表示形式で「0」と指定するとそれぞれどうなるか。また、「#」「?」とした場合はどうか。
解説
セルの書式設定の画面を表示します。ユーザー定義の表示形式で「0」と入力します。123と457になります。
「0」は原則として1文字の半角数字(0~9)を表しますが、整数部分が3桁であればそのまま表示します。しかし、表示形式に小数点と小数点以下を表す書式記号が無い場合は、小数点以下は四捨五入されて整数の表示になります。
このことは、表示形式を「#」や「?」にしても同じです。
「#」「0」「?」を用いた表示形式で、小数点と小数点以下を表す書式記号が無い場合は、小数点以下四捨五入の整数値の表示になります。
<補足>表示形式が小数点で終わっている場合は、最後に小数点が表示されますが、小数点以下の数字は表示されません。
(4)小数点の右側
問題
小数の「123.456」「456.789」を入力した。ユーザー定義の表示形式で「0.00」と指定するとそれぞれどうなるか。また、「#.##」「?.??」とした場合はどうか。
解説
セルの書式設定の画面を表示します。ユーザー定義の表示形式で「0.00」と入力します。123.46と456.79になります。
小数点の左側にある「0」は整数部分を表し、1個や2個であっても3桁すべて表示されます。書式記号の個数にかかわらず、整数部分についてはすべて表示されます。
これに対して、小数点の右側にある「0」は小数点以下表示桁数を表します。したがって、2個であれば2桁だけ表示されます。書式記号の個数を超える右側の部分は四捨五入となります。したがって小数第3位は四捨五入となります。
このことは表示形式を「#.##」や「?.??」にしても同じです。
「#」「0」「?」を用いた表示形式で、小数点と小数点以下を表す書式記号がある場合は、その書式記号の個数の分だけ、小数点以下が表示され、それより右側の桁は四捨五入となります。
3.違いは何か
(1)整数部の書式記号が多すぎる場合
問題
A列に3桁の整数を入力した。ユーザー定義の表示形式で「00000」と指定するとどうなるか。また、「#####」「?????」とした場合はどうか。
解説
セルの書式設定の画面を表示します。ユーザー定義の表示形式で「00000」と入力します。先頭に0が2個ついて5桁になります。
表示形式の「0」の個数がセルに入力されている整数の桁数より多い場合は左側に0を補って表示します。0を補うことによって桁数を揃えることができます。
表示形式を「#####」とします。先頭の0が消えて元の数値のままとなります。つまり、整数部分に「#」が何個並んでいても変わりません。
これを「?????」としても変わっていないように見えます。
ここで左揃えをしてみましょう。スペースが補われていることが分かります。
「#」「0」「?」の個数が桁数より多い場合、「#」は何も表示されませんが、「0」は左側に0を表示、「?」は左側にスペースを補って表示します。特に、先頭の0を表示させたい場合は「0」を使います。
特訓問題
1桁~8桁の整数を入力した。ユーザー定義の表示形式で「0000」「####」「????」と指定するとそれぞれどうなるか考えなさい。
解説
3桁までの場合は、書式記号の個数より実際のデータの桁数のほうが少ないので、「0000」の場合は4桁になるまで0を補って表示します。「????」の場合はスペースを補って表示します(左揃えをすると分かる)。
「#」は何個あっても同じです。何も変わりません。
5桁以上の場合は、書式記号の個数より実際のデータの桁数のほうが多いので、どの記号を使ってもそのまますべて表示されます(共通)。4桁になるわけではありません。
(2)小数部の書式記号が多すぎる場合
問題
A列に整数または小数を入力した。ユーザー定義の表示形式で「0.00」と指定するとどうなるか。また、「#.##」「?.??」とした場合はどうか。
解説
セルの書式設定の画面を表示します。ユーザー定義の表示形式で「0.00」と入力します。小数点以下の「0」の個数がセルに入力されている桁数より多い場合は右側に0を補って表示します。
小数点以下3桁以上は書式記号の個数より多い部分なので四捨五入となります(共通)。これによって、すべての数値が小数第2位までの表示となり、小数点の位置を揃えることができます。
表示形式を「#.##」とします。小数点以下の「#」の個数がセルに入力されている桁数より多い場合であっても何も補いません。ただし、小数点が無い場合は最後に小数点だけをつけます。
小数点以下3桁以上は書式記号の個数より多い部分なので四捨五入となるのは同じです(共通)。
表示形式を「?.??」とします。小数点以下の「?」の個数がセルに入力されている桁数より多い場合、スペースを補います。ただし、小数点が無い場合は小数点も表示されます。
小数点以下3桁以上は書式記号の個数より多い部分なので四捨五入となるのは同じです(共通)。これによって、固定幅フォントで小数点の位置を揃えることができます。
小数部分の「#」「0」「?」の個数が、実際の小数部分の桁数より多い場合、「#」は何も表示されませんが、「0」は右側に0を表示、「?」は右側にスペースを補って表示します。ただし、小数点が無い場合は小数点も表示されます。一般的に小数点以下表示桁数を揃える(小数第~位まで表示する)ときは「0」を使います。
(3)ちょうど0の場合
問題
A列に-5から5までの整数を入力した。ユーザー定義の表示形式で「0」と指定するとどうなるか。また、「#」「?」とした場合はどうか。
解説
セルの書式設定の画面を表示します。ユーザー定義の表示形式で「0」と入力します。書式記号が「0」の場合は何も変わりません。
書式記号が「#」の場合は0だけ非表示になります。セルの値が0で、表示形式に小数点がなく「#」だけしかない場合は空欄となります。
書式記号が「?」の場合は何も表示されていないように見えますが、スペースを補っています。
セルの値がちょうど0の場合もしくは小数点以下を四捨五入してちょうど0になる場合、「#」は何も表示されませんが、「0」の場合は0を表示し、「?」はスペースを表示します。
<補足>空白やスペースで何も表示されていないように見えますが、それは表示形式の設定によってそのように見えるだけであって、セルには0という値が入力されていることに注意します。
(4)整数部が0の場合
問題
セルA1に「0.123」と入力した。ユーザー定義の表示形式で「0.0」と指定するとどうなるか。また、「#.#」「?.?」とした場合はどうか。
解説
セルの書式設定の画面を表示します。ユーザー定義の表示形式で「0.0」と入力します。表示形式が小数第1位までしかないので四捨五入となります(共通)。0.123を小数第1位で四捨五入すると0.1になります。書式記号が「0.0」の場合は0.1となります。
書式記号が「#」の場合は、整数部分の0を非表示にします。小数点の左側(整数部分)が「#」だけしかなく、セルの値の整数部分が0のときは整数部分を表示せず、小数点から始めます。
書式記号が「?」の場合は、整数部分の0の代わりにスペースを表示します。
整数部分が0の場合、「#」は何も表示されませんが、「0」の場合は1の位に0を補い、「?」はスペースを補って表示します。
特訓問題
セルA1に「0.012」と入力した。ユーザー定義の表示形式で「0.0」と指定するとどうなるか。また、「#.#」「?.?」とした場合はどうか。
解説
セルの書式設定の画面を表示します。ユーザー定義の表示形式で「0.0」と入力します。表示形式が小数第1位までしかないので四捨五入となります(共通)。0.012を小数第1位で四捨五入すると0になります。
書式記号が「0.0」の場合は、整数部分の0を表示するとともに、小数点以下は右側に0を補いますので、「0.0」となります。
書式記号が「#」の場合は、整数部分の0を非表示にするとともに、小数点以下も何も補いませんので、小数点だけが表示されます(拡大しないと見えませんね・・・)。
書式記号が「?」の場合は、整数部分の0の代わりにスペースを表示するとともに、小数第1位にもスペースを補います。さらに小数点も表示されます(虫眼鏡がないと見えませんね・・・)。
4.#と0を混ぜてみよう
#と0を混ぜた場合も基本的には上記のルールに従います。小数点の左側と右側に分けて考えましょう。
問題
「123」「0.789」と入力した。ユーザー定義の表示形式で「##.##」と指定するとそれぞれどうなるか。また、「#0.##」「00.##」「0.0#」とした場合との違いを述べなさい。
解説
「##.##」と指定します。「123.」と「.79」となります。
小数点の左側は2個ですが「#」は何個あっても関係ありません。整数部分が0の場合は非表示になります。また、小数点の右側も2個ですが、これは小数点以下表示桁数を表すので、小数第2位までの四捨五入となります。ただし、小数第2位までなくても補われることはありません。
次に、1の位だけを「0」にしてみます。0.79になります。
1の位が「#」と「0」では整数部分が0の場合に0が表示されるかどうかが異なります。
次に、整数部分を「00」にします。「123.」と「00.79」となります。表示形式が2桁の場合、2桁になるまで左側に0を補い、3桁以上の場合はそのまま表示されます。
1の位より左側が「#」の場合と「0」の場合では桁数が足りない場合に0を補うかどうかが異なります。
このように、一般的には整数部分が0の場合に0を表示し、10の位以上は0を表示しないことが多いため、1の位だけ0にして、10の位以上は#にしておくのが無難です。
そして、10の位の#がなくても10の位以上は表示されるので、先頭の#は無くてもよいです。
そこで10の位の#を無くし、小数第1位を「0」にして「0.0#」にします。123.0と0.79となります。
小数点より右側が「#」の場合と「0」の場合では桁数が足りない場合に0を補うかどうかが異なります。
5.カンマ桁区切りの方法
10の位以上の#は原則として不要ですが、カンマ桁区切りをするときに必要となります。このことを理解しましょう。
問題
ユーザー定義の表示形式を用いて、A列の数値を整数値にしなさい。また、カンマ桁区切りにしなさい。
解説
整数値にするには、表示形式で小数点と小数点以下の桁を指定しなければ良いです。「#」「0」「?」のいずれの記号を用いても構いません。書式記号1文字にすると四捨五入して整数値になります(共通)。ただし、この3つの記号は四捨五入をしたときにちょうど0になる場合が異なります。「0」とすると0も表示されます。
「#」「?」は表示されません。
次に、カンマを入れて「#,#」とします。4桁以上の場合にカンマ桁区切りとなります。また、7桁以上の場合も3桁ごとにカンマが付きます。0の場合はカンマも数値も表示されません。
カンマが「#」や「0」で挟まれている、つまりカンマの前と後の両方に「#」や「0」があれば、カンマ桁区切りになります。カンマが「#」や「0」で挟まれていればいいので、「#」や「0」は2つ以上あれば良いです。
表示形式を「#,###」にします。何も変わりません。書式記号の「#」は整数部分に何個あっても変わりません。したがって、「#,#」と「#,###」は同じです。
次に、「#,0」にします。この場合もカンマが「#」や「0」で囲まれているのでカンマ桁区切りになります。ただし、1の位を「0」にすると整数部分が0の場合に0が表示されます。
6.「#,###」と「#,##0」と「0,000」の違い
(1)1の位だけを0にする理由
問題
さきほどの問題で「#,###」と「#,##0」と「0,000」の違いを述べなさい。
解説
前述のとおり、書式記号の「#」は整数部分に何個あっても変わらないので、「#,#」と「#,###」は同じです。なお、「#,#」より「#,###」と記述することが多く、「#,#」と設定すると自動的に「#,###」と修正されます。0の場合はカンマも数値も表示されません。
また、「#,0」と「#,##0」も同じです。なお、「#,0」より「#,##0」と記述することが多く、「#,0」と設定すると自動的に「#,##0」と修正されます。整数部分が0の場合に0が表示されます。
「#,###」と「#,##0」の違いは、整数部分が0の場合に0が表示されるかどうかが異なります。通常は「#,##0」と指定します。
「0,0」または「0,000」とするのは誤りです。1の位より左側を「0」にすると桁数が足りない場合に0が補われます。通常は、カンマをつけながら先頭に0をつけることはありません。
(2)小数第2位までのカンマ桁区切り
問題
ユーザー定義の表示形式を用いて、A列の数値をカンマ桁区切りにして小数第2位まで表示しなさい。ただし、整数部が0の場合は0を表示しなさい。
解説
整数部分が0の場合に0を表示し、10の位以上は0を表示しないことが多いため、1の位だけ0にして、10の位以上は#にしておくのが無難です。さらに、小数第2位まで必ず表示するには、「.00」としなければなりません。
したがって、表示形式の書式記号は「#,##0.00」となります。
7.さらに詳しい説明はこちら
セルの書式設定ではなく、別のセルに表示するにはTEXT関数を使います。詳しくはこちらの記事をご覧ください。
その他の表示形式についてはこちらの記事をご覧ください。
解説は以上です。