エクセルでは、実際にセルに入力されている値と、目に見える表示結果を変える事ができるのですが、結構知らない方も多いです。

結果、下図のような、数値を入力するセルに単位まで入力して計算式がエラーになっていたり、電卓で計算した結果を手入力している、といった悲惨シートを見かけます。

今回は、数字を入力するだけ単位まで表示させたり、数字の桁数を丸めて表示させたり、と言った、ちょっと知っているだけで便利な「セル表示形式」について深掘り解説いたします。

数値と文字列の違いを理解しましょう

例えば、エクセルでは、セルに123と数字だけ入力すると、数値として扱われますが、123個と「個」まで入力してしまうと、文字列として扱われ、計算する事ができなくなります。

下図の例では、四則演算はできる場合もありますが、SUM関数での合計はできていません。
また、計算できているものも、左寄せで表示さているなど、変な結果になっています。

セルに入力するデータは、以下の3種類です。しっかり意識してエクセルを使いましょう。

●数値:
四則演算や関数による集計が可能な値になります。
日付や時刻のシリアル値も「数値」です。(わけて考えた方がわかりやすいですが。)
●文字列:
数字以外は文字データして扱われます。文字データは四則演算などの計算には使えません。
セルに数字のみを入力したものを、数値ではなく文字として扱う場合は、セルの書式を「文字」にする必要があります。
●数式・関数:
イコール記号(=)から書き始める計算式。(プラス記号(+)から始める人もいます。)

本来は、単位は別のセルに入力するようシートを構成すべきですが、どうしても1つのセルに数値に加えて単位も表示させたい場合は、セルの表示形式を使うと便利です。

その他にも、単位1円の入力データを、単位千円や百万円で表示するといった事もできますので、その設定方法を解説していきます。

セル表示形式の設定方法

セルの表示形式の設定は、以下の手順でユーザー定義から「種類」を入力します。

  1. 設定したい範囲を選択し
  2. CTRL + 1 を押す
  3. 表示形式のタブから分類を「ユーザー定義」にする
  4. 種類に記号と文字を入力する

CTRL + 1 で表示されるセルの書式設定のダイアログです。
サンプルで表示結果を確認しながら設定すると効率的です。

リボンの「ホーム」から数値グループのダイアログボックスランチャーからでもできますが、ショートキー(CTRL + 1 )で操作した方が簡単便利です。

それでは、どのように設定すれば、どのような表示結果になるのか?具体的な設定例で見ていきましょう。

セルの表示形式の設定例について

1円単位の数値を千円や百万円で表示させるには?

エクセルでは、実際の値が10,000,000のセルを、単位千円へ10,000、単位百万円で10.0というように、表示結果の桁数を丸める事ができます。
売上金額などで扱う数値の桁数が大きくなると、セル幅が大きくなり年間の集計表がA3横でも入らない場合もあります。
また、単位一円では数字がパッと把握しづらいといったときにも便利ですので使って見てください。

セルの書式設定のダイアログで、種類に #,##0, と入力すると単位千円での表示になります。

よく使う定義を下図にまとめました。

最後にカンマ(,)を付け加える事で、3桁ごとに表示を丸める事ができます。,で表示単位が千円、,,で表示単位が百万円になります。
小数点まで使いたい場合も同様です。

データを入力する際は注意して下さい

例えば、下図C18のセルに、1,000.0百万円(10億円)と入力したい場合と、1000と入力していましがちですが、表示結果の桁数を丸める設定にしていると、0.0百万円になってしまいます。

特に他人にデータ入力をしてらうようなシートには、入力したけど表示がおかしい・・・といったトラブルになりますので、ご注意下さい。

単位名まで表示させるには?

金額に単位名まで表示させたい場合は、種類に単位名を付け加えます。普通に入力するだけで構いません。

こちらも設定例をまとめてみましたので参考になれば幸いです。

また、数字に「個」という単位を付け加えたいなら、種類に #,##0個 と入力すればOKです。4個なら4と数字だけ入力するだけで4個と表示されるようになります。

ゼロを表示させる?させない?

エクセルの初期設定のままだと、計算式の結果がゼロの場合、下図のように0が表示されます。
これを、計算結果(入力値も)がゼロの場合は、何も表示しないという設定にする事ができます。

下図の例では、D45:D47には計算式が入力されていますが、計算結果がゼロなので何も表示されていません。
このように、特定のセルだけゼロを表示させない設定にする時に、セルの表示形式を使います。

ゼロを表示しないようにするには、種類に#,###と入力します。

ゼロを表示する場合は、種類に#,##0となります。

最後の一桁が0か#で、ゼロを表示する、しないを設定できます。

ちなみに、シート全体でゼロを表示させない設定にする場合はエクセルのオプションから行います。
ファイルのオプションを実行するか、ショートカットキーでALT → F → Tを押して下さい。

詳細設定から、「ゼロ値のセルにゼロを表示する」のチェックをOFFにするとゼロが非表示にできます。
設定項目が多いのですが、真ん中あたりある「次のシートで作業するときの表示設定」を目印にすると、探しやすいと思います。

名前の後に「様」をつける

セルの値が文字列の場合は、前後に文字を追加する事はできます。
実務レベルで使った事はないので参考までにというレベルになりますが、例として、入力されている氏名の後に「様」をつけて表示させてみます。

氏名が入力されているセルを選択します。

セルの表示形式で、種類に @ 様 と入力します。

名前の後に様が付きました。@と様の間は、スペースを入力して調整して下さい。

セルの表示形式の定義を @ にすると、設定したセルは「文字列」になりますので、冒頭で説明した通り関数などで集計はできません。
文字と数字をしっかり理解して使い分けしましょう。

セルに入力する「文字」と「数値」データの違い、そしてセル表示形式はエクセルの基本中の基本になりますので、しっかり習得しましょう。