- ホーム >
- 応用操作 >
- エクセル（Excel）編 >
ゼロ値の表示と非表示
セルに数値を入力したり、数式によって計算結果を表示させる場合で、ゼロ「0」の値の表示・非表示で戸惑った経験はないでしょうか？
例えば、セルに「001」と表示させたいのに、Excelの初期設定のままでは自動で「1」に変換されてしまいます。また、逆に値がゼロのときは「0」と表示させずに「空白」にしたい場合があったりします。
このようなゼロの扱い方、つまり「0」を表示させるのか表示させないのかは、Excelを使いこなすうえで避けて通れないスキルになってきます。
プログラミング用語になりますが、ゼロを表示しない（ゼロを省く）ことを、
ゼロサプレス
といい、任意の桁数まで満たない場合にゼロを表示する（その桁数までゼロで埋める）ことを、
ゼロパディング
といいます。 ゼロサプレスはExcelの初期設定なので、意識せずに通常利用していることになりますが、もっと掘り下げていくと、値が「0」そのものの場合（ゼロ値）でも非表示にすることが可能です。
例えば、下図の表のように、半数以上の値がゼロであって、ゼロ値以外の値がまばらな場合、ゼロを省いてしまったほうが見やすい表になります。
ここから、ゼロ値を非表示にすると、すっきりとして見やすい表になりました。こうすることで、「7月」が異常に遅刻が多いということが一目でわかります。
このように、状況に応じてゼロ値を非表示にすると、グラフ化するまでもなく、一目でアピールできる表を作成することができます。
ではまず、ゼロ値を「非表示」にする方法から学習していきましょう。
Excelのデフォルト（初期設定）はゼロサプレスとなっており、値の前後の余分なゼロは省略されます。しかし、ゼロ値の場合は「0」と表示されます。このゼロ値も非表示にするという意味です。
ゼロ値の非表示にはいくつかの方法がありますが、まずは、最も簡単でシート全般にわたり一括して設定できる方法からです。
「ファイル」タブの「オプション」より「Excelのオプション」画面を開き、「詳細設定」の「次のシートで作業するときの表示設定」項目の「ゼロ値のセルにゼロを表示する」のチェックを外します。
これだけで、当該シートの中でゼロ値は非表示になります。
次は、これまで学習した知識でできる方法です。書式のユーザー定義 で学習した「ユーザー定義」を使います。これはもうおわかりだったかもしれません。
非表示にしたい範囲をマウスで範囲指定して、「セルの書式設定」画面を開き、ユーザー定義を設定します。ユーザー定義は、ゼロを非表示にする「#」を使います。
この「#」は、値がゼロの時は何も表示しない記号です。この場合「#」もしくは桁数のカンマ区切りを表示する「#,###」を指定すると、ゼロ値が非表示になります。
本項ではユーザー定義による方法の解説は以上に留めます。ユーザー定義について詳しくは、書式のユーザー定義 を参照してください。
次に、基本操作編の IF関数 で学習した「IF関数」を使う方法です。IF関数は、同項で学習したとおり、「もし〇〇が□□なら、△△の値を返す」関数になります。
したがって、「もし【セルの値】が【0】なら【空白】を返す」という関数式を記述すると、ゼロ値を非表示にすることができます。
ただし、この方法は手入力形式の表には使えません。循環参照といってIF関数を入力したいセル自身を参照しなければならなくなるためです。
例えば「A1」セルがゼロ値だった場合、「A1」セルにIF関数を記述することになります。つまり、自身のセルを参照する循環参照になってしまうからです。
IF関数でゼロ値を非表示にするには、計算結果としてゼロ値になるような場合です。例えば下図のように、「単価」と「販売数」を掛けて「売上」を算出する計算式を入力するセルで使用します。
この場合、「E4」セルには「=C4*D4」の計算式が入ります。ここで、IF関数を使い「=IF(D4=0,"",C4*D4)」と記述すると、「販売数」がゼロ値の時に売り上げもゼロ値となるので、「E4」セルは空白になります。
このようにIF関数を使ってゼロ値を非表示にするのは、逆に煩雑です。ゼロ値に限らず、IF関数で「空白」を返す場合というのは、例えば、あるセルに値が入力されていない状態（セル=""）では空白、値が入力されたら計算するといった使われ方が多いです。
このほかに、ゼロ値のセルのフォント色を白色にする方法や、「条件付き書式」という機能を使って、指定する条件に一致する場合（ゼロ値の場合）に自動的にフォント色を白色にするなど方法がありますが、条件付き書式については、条件付き書式 で詳しく解説します。
さて、もうこれでゼロを省略するゼロサプレスの概要、さらにゼロ値を非表示にする方法はマスターできています。 ただし、ゼロ値を非表示にする場合、ある重要なことに気をつけなければなりません。
それは、IF関数などでゼロ値を空白に変換して返している場合以外では、空白セルに見えるだけで、ゼロ値もゼロとして数値認識されているということです。そのため、
ゼロ値のセルもゼロとして計算に入っている
ということを忘れないでください。例えば、平均値を算出する場合には特に注意が必要です。平均値を求めるときに、ゼロを含むか含まないかで値は大きく異なってくるからです。
平均値を求めるにはAVERAGE関数を使いますが、下図のように、「D3:L3」までの平均を出すと、「0.89」となっています。
すなわち、月の平均が「0.89」で「1」未満ということは、空白セルの月の「0」が計算に入っていることになります。つまり、「（2+1+3+2）÷9」の計算式です。
全行の平均を出すと下図のようになります。すべて、遅刻回数を「4月～12月」までの「9」で割り算をした値になっています。
ここで、空白セルの「0」をDeleteで削除すると、下図のように平均値が大きく変わります。この場合は、数値入力月のみの平均となるため、例えば「工藤勝信」の平均は「（2+1+3+2）÷4」となります。
このように、ゼロ値を非表示にしたことで、そこに「見えない0」があることを忘れないようにしてください。
上記の例では、空白の月の「0」は計算に入れなければなりません。しかし、この表の作成日が「10月」だとしたらどうでしょうか？まだ「11月」と「12月」が来ていないので、値は10月までしか入れることができません。その時点までの平均を出すには、11月と12月の「0」を削除しなければ正しい平均は求められないことになります。
未来月に「0」があらかじめ入力されていることはないと思われるかもしれませんが、これが例えば、数値ではなく計算式が入力されていた場合はどうでしょう？未来月であっても計算式であれば値が「0」になっている可能性は十分にあります。
また、他人が作成したシートなど、ゼロ値が非表示になっていないか意識しておくことも必要です。
それでは逆に、ある桁数までゼロで埋める「ゼロパディング」の方法も知っておきましょう。
ゼロパディングは、前述のとおり任意の桁数まで「0」を表示させることです。そのため、状況によって桁数を指定する必要があります。例えば、扱う数字が99までの場合は2桁、999までの場合は3桁という具合です。
ゼロパディングについてもExcelでは方法がいくつかありますが、もうおわかりのとおり、最も簡単で一般的な方法は「ユーザー定義」を使う方法です。
例えば、同表の「社員コード」の桁数を揃えてみましょう。
ゼロサプレスと同様に表示にしたい範囲をマウスで範囲指定して、「セルの書式設定」画面を開き、ユーザー定義を設定します。ユーザー定義は、ゼロを表示する「0」を使います。
この「0」は、ゼロを表示する記号です。この場合、最長6桁のコードになるので、「000000」を指定すると、6桁に満たない数値には6桁まで0が埋められます。
また、カンマで区切りたい場合は、カンマを入れて「000,000」を指定すると、カンマで区切ることができます。（「0000,00」などのように不適切な位置でカンマを挿入することはできません）
本項ではユーザー定義による方法の解説は以上に留めます。ユーザー定義について詳しくは、書式のユーザー定義 を参照してください。
次は、数値を文字列に変換する方法です。
文字列に変換すると、Excelの認識上数字ではなくなるので、先頭に「0」をつけても省略されることはありません。文字列に変換するには、同「セルの書式設定」画面から、「文字列」を設定します。
書式が文字列に変換されたら、値が左揃えレイアウトになるので、先頭に「0」を加えた値を入力します。（あくまで手入力になります）
また、簡単に文字列変換できる方法として、シングルクォーテーション「'」を数値の前に入力すると、セルは文字列として認識するようになります。
こうして文字列として認識されたセルや数値には、下図のように、左揃えレイアウト以外にセルの角に緑色の三角マークが付きます。これは、数値が文字列として認識されていることを知らせるマークです。
ただし、このように文字列として認識されてしまうと、計算に影響が出る場合があります。Excelのバージョン等によっては、計算されなくなる可能性がありますので、数値は数値認識させておくほうが良いと思います。
次に、関数を使う方法です。
使うのは「TEXT（テキスト）関数」という関数になります。TEXT関数は、表示形式を指定する関数で、関数でユーザー定義を設定できるようなイメージです。
TEXT関数については、TEXT関数 で詳しく解説しますので、本項では割愛しますが、「=TEXT(セル,"表示形式")」を記述すれば任意の表示形式に設定することができます。
ただし、TEXT関数を使う方法も、結果が文字列となって表示されてしまいます。
そのうえ、IF関数の場合と同じく、値を手入力するような表や、値や式がすでに入力されているセルには使えないので、逆に煩雑になってしまいます。
ゼロパディングについては、ユーザー定義を使うのが最も簡単で効率的な方法になります。
更新履歴
- 2018年3月12日
- ページを公開。
参考文献・ウェブサイト
当ページの作成にあたり、以下の文献およびウェブサイトを参考にさせていただきました。
- 文献
- なし
- サイト
- なし
- 管理人のつぶやき
- Twitterのフォローお願いします！