表示形式「#,##0」の意味がいまいちピンとこない件
Excel研修でよく休憩時間にいただくご質問です。
Excel資料を社内で引き継ぎ・共有する場合、入力されている「数式」の理解も大切ですが、
「書式」も定義できるようになると思いどおりにデータを表示させることができます。
数値データの表示形式「#」と「0」について、違いは把握されていますか？
カンマを表示させる
4桁以上の数値を入力したセルを選択し、リボンの[ホーム]タブ-桁区切りスタイルボタンをクリックしてカンマ表示を設定します。その後、Ctrl+1で「セルの書式設定」をご確認ください。
表示形式の分類は「標準」から「通貨」に変わりました。ここで、分類を「ユーザー定義」に切り替えると「#,##0;[赤]-#,##0」と定義されていることが分かります。
「;」より左側が正の数、右側が負の数の表示形式を定義しています。
3桁ごとにカンマを表示させ、負の数の場合は「-」をつけて赤い字で表示するの意。
ちなみに、ショートカットキー(Ctrl+「!」)でカンマを設定した場合は、「#,##0」とだけ定義され、
負の数については定義されません。
「#,####」と設定して4桁ごとにカンマが振れるか実験したところできませんでした。
「##,###」というふうに、3桁ごとのカンマに自動修正されてしまいました。
100万以上の大きな数値でも、繰り返し定義する必要はありません。
どんなに大きな数値も3桁ごとにカンマが表示されます。
#や0は何なのか
分類「ユーザー定義」で数値の表示形式を定義する場合、「#」か「0」を使用するのが決まり事です。
よくインターネットなどで調べると、「#」は有効桁数が表示されると説明されています。「###」と設定すると3桁までしか表示できなのかと思いますよね。でも、5桁入力してもすべて表示されるし、意味不明だなとなるわけです。この有効桁数とは何なのでしょうか。
「#」を一つだけ設定しても、データが整数の場合、入力した分だけ表示されます。
実は、有効桁数を表示するというのは、少数部のことを言っているのです。
「#.##」と設定した場合、そのセルに「123.456」と入力すると「123.46」と表示されます。
整数部は入力した通りに表示され、少数部分は2桁に丸められます。
ちなみに、「#.#####」と、小数部のデータよりも多く「#」を設定しても「123.456」と表示され、実際のデータの桁より多い小数部の「#」は無視されます。
「#,##0」 なぜ1桁目が「0」なのか
さて、0との違いです。「#」は数値「0」データを表示できません。
100とか1,000は表示できます。純粋な「0」というデータは空白表示になるのです。
そこで、「0」で定義すれば数値「0」データを表示することが可能です。
なので、ユーザー定義の1桁目では「0」を定義するのが基本ルールとなっています。
もし、皆さんが扱われている資料で「0」は表示させたくないなあと思ったら、
敢えて「#,###」と設定すれば良いですね。
「0」はおせっかい
「0」の定義は、有効桁数については「#」と同様です。
「0」を一つだけ定義した場合も、データが整数の場合、入力した分だけ表示されます。
「0.00」と定義した場合、セルに「123.456」と入力すると「123.46」のように、整数は入力した通りに表示され、小数部が2桁に丸められて表示されます。
ここからが違います。
「0.00000」のように、小数部に実データより多く「0」を定義した場合は「123.45600」と表示されます。定義した分だけ「0」が表示されます。おせっかいというより忠実といったほうがいいでしょうか。
整数部も、「000」と定義した場合、セルに「1」と入力すると「001」と表示されます。
つまり、「0」は呼ばれた分だけ顔を出すということです。伝票番号などには便利そうですね。
意味を知った上で上手に使い分けてください。
おまけ
名前に様をつける、御中をつけるなどの場合は、文字を表記するためのコードは「@」(アットマーク)です。
「@ 様」と設定、セルに「田中一郎」と入力すると「田中一郎 様」と表示されます。
請求書などには必須ですね。
ちなみに、様の両脇のダブルクォーテーションは省略可能です。再度、確認すると自動的に”様”となっています。複雑な定義でなければ省略できます。
Excelは実データと見た目は別管理が鉄則です。表示形式を操ることができれば思い通りに表記させることが可能です。関数や機能を使用する前に、表示形式でできることはないか検討してみてください。
