基礎からわかる!パソコン入門・再入門 / 無料パソコン教室・基礎知識・Word・Excel・PowerPoint

ゼロ値の表示と非表示

ルに数値を入力したり、数式によって計算結果を表示させる場合で、ゼロ「0」の値の表示・非表示で戸惑った経験はないでしょうか?

例えば、セルに「001」と表示させたいのに、Excelの初期設定のままでは自動で「1」に変換されてしまいます。また、逆に値がゼロのときは「0」と表示させずに「空白」にしたい場合があったりします。

このようなゼロの扱い方、つまり「0」を表示させるのか表示させないのかは、Excelを使いこなすうえで避けて通れないスキルになってきます。

プログラミング用語になりますが、ゼロを表示しない(ゼロを省く)ことを、

ゼロサプレス

といい、任意の桁数まで満たない場合にゼロを表示する(その桁数までゼロで埋める)ことを、

ゼロパディング

といいます。 ゼロサプレスはExcelの初期設定なので、意識せずに通常利用していることになりますが、もっと掘り下げていくと、値が「0」そのものの場合(ゼロ値)でも非表示にすることが可能です。

例えば、下図の表のように、半数以上の値がゼロであって、ゼロ値以外の値がまばらな場合、ゼロを省いてしまったほうが見やすい表になります。

6人の月別の遅刻回数を表にしたイメージ

ここから、ゼロ値を非表示にすると、すっきりとして見やすい表になりました。こうすることで、「7月」が異常に遅刻が多いということが一目でわかります。

ゼロ値を非表示にして空白になったセルと表のイメージ

このように、状況に応じてゼロ値を非表示にすると、グラフ化するまでもなく、一目でアピールできる表を作成することができます。

ではまず、ゼロ値を「非表示」にする方法から学習していきましょう。

Excelのデフォルト(初期設定)はゼロサプレスとなっており、値の前後の余分なゼロは省略されます。しかし、ゼロ値の場合は「0」と表示されます。このゼロ値も非表示にするという意味です。

ゼロ値の非表示にはいくつかの方法がありますが、まずは、最も簡単でシート全般にわたり一括して設定できる方法からです。

「ファイル」タブの「オプション」より「Excelのオプション」画面を開き、「詳細設定」の「次のシートで作業するときの表示設定」項目の「ゼロ値のセルにゼロを表示する」のチェックを外します。

「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」となっています。

セルにAVERAGE関数を記述したイメージ

すなわち、月の平均が「0.89」で「1」未満ということは、空白セルの月の「0」が計算に入っていることになります。つまり、「(2+1+3+2)÷9」の計算式です。

全行の平均を出すと下図のようになります。すべて、遅刻回数を「4月~12月」までの「9」で割り算をした値になっています。

6人の月別の遅刻回数を表にしたイメージ

ここで、空白セルの「0」をDeleteで削除すると、下図のように平均値が大きく変わります。この場合は、数値入力月のみの平均となるため、例えば「工藤勝信」の平均は「(2+1+3+2)÷4」となります。

6人の月別の遅刻回数を表にしたイメージ

このように、ゼロ値を非表示にしたことで、そこに「見えない0」があることを忘れないようにしてください。

上記の例では、空白の月の「0」は計算に入れなければなりません。しかし、この表の作成日が「10月」だとしたらどうでしょうか?まだ「11月」と「12月」が来ていないので、値は10月までしか入れることができません。その時点までの平均を出すには、11月と12月の「0」を削除しなければ正しい平均は求められないことになります。

未来月に「0」があらかじめ入力されていることはないと思われるかもしれませんが、これが例えば、数値ではなく計算式が入力されていた場合はどうでしょう?未来月であっても計算式であれば値が「0」になっている可能性は十分にあります。

また、他人が作成したシートなど、ゼロ値が非表示になっていないか意識しておくことも必要です。

それでは逆に、ある桁数までゼロで埋める「ゼロパディング」の方法も知っておきましょう。

ゼロパディングは、前述のとおり任意の桁数まで「0」を表示させることです。そのため、状況によって桁数を指定する必要があります。例えば、扱う数字が99までの場合は2桁、999までの場合は3桁という具合です。

ゼロパディングについてもExcelでは方法がいくつかありますが、もうおわかりのとおり、最も簡単で一般的な方法は「ユーザー定義」を使う方法です。

例えば、同表の「社員コード」の桁数を揃えてみましょう。

最長6桁の社員コード表のイメージ

ゼロサプレスと同様に表示にしたい範囲をマウスで範囲指定して、「セルの書式設定」画面を開き、ユーザー定義を設定します。ユーザー定義は、ゼロを表示する「0」を使います。

「セルの書式設定」画面のイメージ

この「0」は、ゼロを表示する記号です。この場合、最長6桁のコードになるので、「000000」を指定すると、6桁に満たない数値には6桁まで0が埋められます。

6桁まで数値の先頭に「0」が埋められたイメージ

また、カンマで区切りたい場合は、カンマを入れて「000,000」を指定すると、カンマで区切ることができます。(「0000,00」などのように不適切な位置でカンマを挿入することはできません)

本項ではユーザー定義による方法の解説は以上に留めます。ユーザー定義について詳しくは、書式のユーザー定義 を参照してください。

次は、数値を文字列に変換する方法です。

文字列に変換すると、Excelの認識上数字ではなくなるので、先頭に「0」をつけても省略されることはありません。文字列に変換するには、同「セルの書式設定」画面から、「文字列」を設定します。

「セルの書式設定」画面のイメージ

書式が文字列に変換されたら、値が左揃えレイアウトになるので、先頭に「0」を加えた値を入力します。(あくまで手入力になります)

また、簡単に文字列変換できる方法として、シングルクォーテーション「'」を数値の前に入力すると、セルは文字列として認識するようになります。

数値の前に「'」を入力したイメージ

こうして文字列として認識されたセルや数値には、下図のように、左揃えレイアウト以外にセルの角に緑色の三角マークが付きます。これは、数値が文字列として認識されていることを知らせるマークです。

数値が文字列として認識されているイメージ

ただし、このように文字列として認識されてしまうと、計算に影響が出る場合があります。Excelのバージョン等によっては、計算されなくなる可能性がありますので、数値は数値認識させておくほうが良いと思います。

次に、関数を使う方法です。

使うのは「TEXT(テキスト)関数」という関数になります。TEXT関数は、表示形式を指定する関数で、関数でユーザー定義を設定できるようなイメージです。

TEXT関数については、TEXT関数 で詳しく解説しますので、本項では割愛しますが、「=TEXT(セル,"表示形式")」を記述すれば任意の表示形式に設定することができます。

「関数の引数」画面のイメージ

ただし、TEXT関数を使う方法も、結果が文字列となって表示されてしまいます。

そのうえ、IF関数の場合と同じく、値を手入力するような表や、値や式がすでに入力されているセルには使えないので、逆に煩雑になってしまいます。

ゼロパディングについては、ユーザー定義を使うのが最も簡単で効率的な方法になります。

ページトップへ戻る


関連情報&オススメ

JALカードはJALマイレージバンク機能に、各提携会社のクレジット機能が備わったクレジットカードです。フライトでもショッピングでも効率よくマイルをためることができます。

マイルをためようとている方、旅行好きな方には最適なクレジットカードです。JAL派とANA派に分かれますが、JAL便を多く利用するなら、はっきり言ってJALカードは「必須」です。

JAL便の場合、チェックインも搭乗手続きもJALカードをかざすだけで可能になりますし、カード付帯保険や各地の特約ホテルで割引などもあります。

たまったマイルは特典航空券と交換でき、マイルをためることで無料で旅行を楽しむことができたりもします。その他にも様々な商品や各地で使えるクーポン、電子マネーやAmazonギフト券などとも交換することができます。

私はクレジットカードはすべてJALカードにまとめ、電話代等の引落しもJALカードを使っています。すると、月々の支払いによってマイルが自動的たまり、数年に一度は無料で飛行機に乗ったり、Amazonギフト券に交換したりと重宝しています。

ページトップへ戻る

更新履歴

2018年3月12日
ページを公開。

ページトップへ戻る

参考文献・ウェブサイト

当ページの作成にあたり、以下の文献およびウェブサイトを参考にさせていただきました。

文献
なし
サイト
なし

ページトップへ戻る