Excelでは、セルの中に数字と文字を一緒に入力してしまうと、そのセルを使った掛け算など、数字の計算でエラーが出てしまいます。
そこで、数字の計算が成り立つよう、実際のセルの中には数字しか入力していないけど、あたかも文字も一緒だよと「見せかける」、な〜んてことができてしまうのがユーザー定義の表示形式。
「「円」や「個」などの単位を数字と一緒に表示させたい」では、数字や、掛け算が入力されたセルに、図のような表示形式を設定することで、数値に関する、ユーザー定義の表示形式の基本をご説明しています。
今回の例でも、「単価」と「金額」の各セルには、「#,##0"円"」という表示形式を設定しています。
「#,##0"円"」とは、「千の位まで到達している数字は、3桁区切りのカンマ付きで表示し、すべての数字の後ろに「円」という文字を表示してください」という意味です。
なので、この表示形式を設定してあるセルの数字が、マイナスだったり、ゼロだったとしても、当然、設定しているとおりの表示になります。
でも、セルの数字が、マイナスやゼロの時には、違う表示形式にしたい場合もあります。
今回はその方法についてご紹介していきます！
それでは早速操作に入っていきますが、これからご紹介する内容は、ユーザー定義の表示形式の基本をご説明した、「「円」や「個」などの単位を数字と一緒に表示させたい」を既にお読みいただいていることを前提としていますので、まだの方は、先にそちらをご覧ください。
マイナスやゼロの場合の表示形式のお約束
それではセルに、マイナスの数字やゼロが入力されている場合には、表示形式が変わるように設定していきましょう！
今回は「単価」の欄だけ設定することにしたいので、B4からB10番地を範囲選択します。
この時、マイナスの数字やゼロが入力されているB9番地やB10番地だけを選択すればいいのではないかと思った方もいらっしゃるかもですが、他の「単位」欄のセルも、いつマイナスの数字やゼロが入力されてもいいよう、同じ設定をしておきたいので、B4からB10番地を範囲選択します。
範囲選択したセルのところだったらどこでもいいので右クリック、
［セルの書式設定］をクリックすると、
［セルの書式設定］ダイアログボックスが表示されます。
［表示形式］タブの［ユーザー定義］の［種類］欄に、現在、範囲選択しているセルに設定されている表示形式が表示され、更にそれと全く同じものがこの欄の一番上にも映し出されていることを確認します。
プラスの場合と、マイナスやゼロの場合とで表示形式を変えたい場合には、プラスの場合の表示形式に続けて、マイナスやゼロの場合の表示形式を付け加えていきます。
その際にはお約束があり、図のような順番で設定し、更にそれぞれの設定には区切りとして「；（セミコロン）」も入力します。
ユーザー定義で使う記号は、半角がお約束なので、このセミコロンも半角で入力します。
それでは、［種類］の一番上の欄で、現在の表示形式に編集を加えていきましょう。
現在の設定はプラスの場合の表示形式としたいので、そのまま残します。
そしてここに、マイナスの場合の表示形式を付け加えたいので、プラスの場合の表示形式の後ろに、区切りの「；（セミコロン）」を入力します。
もちろん、このセミコロンは半角で入力します。
今回、セルにマイナスの数字が入力されている時には、その数字の前に「値引き」という文字と、マイナス記号を表示するようにしたいです。
でも、「値引き」という文字と、マイナス記号がくっついていると、案外読みづらいので、その間にスペースも入るようにしたいです。
そして、数字の後ろには「円」という文字も表示するようにしたいです。
これをユーザー定義の表示形式の記号で表すと、図のようなものになります。
今回の例では、マイナスの数字も千の位に到達する可能性があると考えて、「#,##0」と、必要に応じて3桁区切りのカンマが表示されるような表現にしました。
また、ユーザー定義の表示形式では、「そのまま表示したい文字の前後をダブルクォーテーションで囲む」のがお約束ですので、「値引き」と「円」という文字をダブルクォーテーションで囲みます。
でも、スペースとマイナス記号は、ダブルクォーテーションで囲まなくてもそのまま表示される特別な記号なので、ダブルクォーテーションで囲む必要はありません。
もちろん、ユーザー定義で使う記号は、半角がお約束なので、ダブルクォーテーションもスペースもマイナスも、半角で入力します。
マイナスの場合の表示形式を入力することができました。
続いて、ゼロの場合の表示形式も加えていきますので、マイナスの場合の表示形式の後ろに、区切りの「；（セミコロン）」を入力します。
もちろん、セミコロンは半角で入力でしたね。
ゼロだった場合には、数字の「0（ゼロ）」を表示させることなく、ただ「サービス」という文字を表示させたいので、「サービス」と入力します。
もちろん、「そのまま表示したい文字の前後をダブルクォーテーションで囲む」のと、「ユーザー定義で使う記号は半角」がお約束ですので、「サービス」の文字の前後を半角のダブルクォーテーションで囲みます。
これで、プラスの場合の表示形式、マイナスの場合の表示形式、ゼロの場合の表示形式のすべてを設定し終わったので、ダイアログボックス右下の［OK］ボタンをクリックしましょう。
セルに入力されている数字が、設定したとおりに表示されるようになりました！
選択したセルの中身は数式バーに映し出されるわけですが、B9番地を選択して数式バーを見ると、このセルには「-500」というマイナスの数字しか入力されていないことが分かります。
でも表面的には、マイナスの場合の表示形式で設定したとおりの表示になっています！
B10番地を選択して数式バーを見ると、このセルには「0（ゼロ）」という数字しか入力されていないことが分かります。
でも表面的には、ゼロの場合の表示形式で設定したとおり、数字の「0（ゼロ）」は表示されることなく、ただ「サービス」という文字が表示されています！
本来、セルの中に文字が入力されていると、そのセルを使った掛け算では、文字の掛け算なんてありえません！と、エラーが表示されてしまいます。
ですが今回のように、「見かけ」で表面的に文字を見せているだけの場合には、実際のセルに入力されているのは「0（ゼロ）」という数字なので、それを使った掛け算も、エラーが出ることなくうまく計算されます！
これが表示形式の醍醐味ですね♪
スポンサーリンク
色の設定もできる
もし、今のままでは「値引きしてるんだぜ！」「サービスしてるんだぜ！」というアピールに欠ける！という場合には、それぞれの文字に色が付くようにしてみるのはどうでしょう？
ただ、フォントの色で、セル内の文字の色を直接変えてしまうと、そのセルの中のデータが、マイナスの数字やゼロでなくなってしまった場合に、いちいち手作業で文字の色を直していかなければなりません。
そこでExcelでは、マイナスの場合にはこの色、ゼロの場合にはこの色、という風に、セルに入力されているデータによって色が変わるよう、表示形式で色を設定できるようになっています。
「単価」の欄に設定した表示形式に、色の設定を加えたいので、再び「単価」の欄であるB4からB10番地を範囲選択し、範囲選択したセルのところだったらどこでもいいので右クリック、
［セルの書式設定］をクリックします。
［セルの書式設定］ダイアログボックスが表示されました。
［表示形式］タブの［ユーザー定義］の［種類］欄に、現在、範囲選択しているセルに設定されている表示形式が表示され、更にそれと全く同じものがこの欄の一番上にも映し出されていることを確認します。
色の設定は、プラスやマイナス、ゼロの場合の表示形式の、それぞれの先頭に、設定したい色を角括弧で囲んで表現します。
この時の色の指定は、色の名前を設定する方法と、色の番号を設定する方法の2種類があります。
色の名前を角括弧で囲んで設定する方法では、黒・緑・白・青・紫・黄・水・赤が設定できます。
マイナスの場合は赤い色で表示させたいのなら、マイナスの表示形式の先頭に、図のように設定します。
色の番号を角括弧で囲んで設定する方法では、色1〜色56が設定できます。
各番号の色がどんな色なのかは、「ユーザー定義の表示形式で使える56色まとめ」をご覧ください。
もし、マイナスの場合は赤い色で表示させたいのなら、「赤」は「色3」なので、マイナスの表示形式の先頭に、図のように設定します。
「色」という漢字は全角でしか入力できないので、全角になるのは当然ですが、色番号の数字はもちろん半角で入力しますヨ。
ちなみに、角括弧ももちろん半角で入力しますが、日本語入力OFFの状態で、図のキーを押せば、角括弧の始まり、角括弧の終わりをそれぞれ半角で入力できます。
というわけで、プラス、マイナス、ゼロの場合それぞれに、色の設定を加えたい場合には、このような表現になります。
今回は、マイナスの場合は赤い色、ゼロの場合は青い色で表示させたいので、マイナスの表示形式とゼロの表示形式それぞれの先頭に、図のように色の設定を加えます。
プラスの場合は、特に色を設定したいわけではないので、プラスの表示形式には色の設定を加えていません。
ダイアログボックス右下の［OK］ボタンをクリックします。
セルにマイナスの数字が入力されていた場合には赤、ゼロが入力されていた場合には青で表示されるようにできました！
色が変わったおかげで、値引きとかサービスしていることを、思いっきりアピールしちゃっています。
スポンサーリンク
表示形式だからこそ
現在、「単価」の欄のB6番地には、プラスの数字が入力されています。
このB6番地のデータを「0（ゼロ）」に修正してみます。
「0（ゼロ）」と入力後、そのセルの中で文字カーソルが点滅しなくなるよう、［Enter］キーでセルを確定すると、
データがゼロの場合には、青い文字で「サービス」と表示されるように設定してあるので、そのように表示されました！
今度はB10番地を見てみます。
B10番地のデータは「0（ゼロ）」なので、青い文字で「サービス」と表示されています。
このB10番地のデータを、「1620」に修正してみます。
「1620」と入力後、そのセルの中で文字カーソルが点滅しなくなるよう、［Enter］キーでセルを確定すると、
入力した数字が、プラスの場合の表示形式で表示されました！
プラスの場合の表示形式には色を設定していなかったので、そのセルに元から設定してある色で表示されています。
そしてもちろん、それぞれのセルには数字しか入力していなかったので、その数字を使った掛け算の計算結果も、エラーが出ることなくちゃんと表示されています！
（今回は「単価」の欄にしか表示形式の変更を加えていませんから、この時点で「単価」と「金額」の欄の表示形式は異なります。）
「そもそも」を考えてみると
そもそも最初の時点では、このような表示形式しか設定していなかったので、
この表示形式を設定したセルに、マイナスの数字が入力されていようが、ゼロが入力されていようが、プラスの数字と同じように表示されていました。
（図は一番最初の、データを修正する前の状態なので、現在の状態とはゼロが入力されているセルが異なります。）
それを図のように「；（セミコロン）」で区切って表示形式を設定することで、マイナスの場合とゼロの場合の表示を、プラスの場合とは別に設定することができました。
でもそれは、逆を言ってしまえば、マイナスの場合やゼロの場合を、プラスの場合とは別に設定する必要が無ければ、最初のようにプラスの場合ひとつの設定でOKだということになります。
ということは、ゼロの場合は特に別の設定をする必要はなく、プラスの設定と同じでいいというのなら、こんな風にプラスの場合とマイナスの場合とを設定するだけでOKです。
現在は、ゼロが入力されているB6番地は、ゼロの場合の表示形式のおかげで「サービス」と表示されていますが、試しにプラスの場合とマイナスの場合だけの設定に変更し、ゼロの場合の設定を削ってみることにしましょう。
「単価」の欄であるB4からB10番地を範囲選択し、［セルの書式設定］ダイアログボックスを表示します。
現在はユーザー定義の表示形式で図のように設定されていますが、このゼロの場合の部分だけを消してしまいます。
ゼロの場合の設定を消すわけですから、マイナスの場合とゼロの場合との設定を区切る「；（セミコロン）」も不要になるので、それも消します。
ゼロの場合の部分だけを消して、プラスの場合とマイナスの場合だけの設定に変更しました。
ゼロの場合の設定を削ったので、ゼロが入力されているセルは、プラスの場合と同じ表示形式になったことが分かります。
但し、1つだけ設定しようが、2つだけ設定しようが、3つ設定しようが、この順番がお約束なので、マイナスの場合は特に別の設定をする必要がなく、プラスの設定とゼロの設定だけしたい、という場合には、マイナスの設定を省略して2つだけ設定するということができません。
「2番目は必ずマイナスの場合」と決まっているので、2つだけ設定すると、それはプラスの設定とゼロの設定ではなく、プラスの設定とマイナスの設定、という風にExcelさんは考えてしまうからです。
なので、マイナスの場合は特に別の設定をする必要がなく、プラスの設定とゼロの設定だけしたい、という場合には、結局2番目のマイナスの設定は省略できないので、
#,##0"円"；#,##0"円"；"サービス"
のように、プラスの設定とマイナスの設定を同じように記述します。
ですが、これだと、マイナスの場合をわざわざ設定しているにも関わらず、そこにマイナス記号を記述していないので、マイナスの数字が入力されているセルには、無理やりマイナス記号を表示させないことになってしまいます。
マイナス記号が表示されなければ、セルの中のデータがプラスなのかマイナスなのかが、パッと見で分かりづらくなってしまうので、
#,##0"円"；−#,##0"円"；"サービス"
のように、マイナスの場合を設定するなら、そこにマイナスを示す記号は記述した方がいいと思います。
そういう意味では、ユーザー定義の表示形式では、マイナスを示す記号が付くか付かないか、という点で、プラスの表示形式とマイナスの表示形式が完全に同じ表示で良いということは考えづらいので、結局は「マイナスの場合は特に別の設定をする必要がなく、プラスの設定とゼロの設定だけでよい」ということは起こりづらいと思います。
スポンサーリンク
なんで表示形式で色が設定できるの？
今回は、表示形式で色を一緒に設定する方法もご紹介しています。
中には、「なんでユーザー定義の表示形式で、色も設定できるの？」と思った方もいらっしゃるかもしれません。
でもこれは、基本に返ってみるとイメージしやすくなります。
表示形式はもともと、簡単に設定できるよう、初めから選択肢がいろいろと用意されていて、数字に関する表示形式は［数値］や［通貨］などの分類に、その選択肢が用意されています。
ここを見ると、［負の数の表示形式］で、マイナスの場合の表示形式が選べるようになっていて、基本の設定ですら、赤い色付きのものが選べるようになっていることが分かります。
マイナスの場合はデータを目立つようにできた方がいいかも、というExcelさんの配慮です。
ユーザー定義の表示形式は、基本の選択肢ではできない設定を、自分で作るという機能ですから、当然基本でできる色の設定も、ユーザー定義の表示形式でできるようになっていて、更に色の種類まで設定できるようになっている、というわけです。
まとめ
最後に、今回ご紹介したユーザー定義の表示形式の表現をまとめます。
表現に使う記号は、すべて半角で入力します。
セルに入力されている数字が、プラスの場合、マイナスの場合、ゼロの場合のそれぞれで表示形式を変えたい場合には、図のような順番で設定し、それぞれを「；（セミコロン）」で区切って設定します。
色を設定したい場合には、表示形式の先頭に、設定したい色を角括弧で囲んで表現します。
この時の色の指定は、色の名前を設定する方法と、色の番号を設定する方法の2種類があります。
色の名前を角括弧で囲んで設定する方法では、黒・緑・白・青・紫・黄・水・赤が設定できます。
色の番号を角括弧で囲んで設定する方法では、色1〜色56が設定できます。
各番号の色がどんな色なのかは、「ユーザー定義の表示形式で使える56色まとめ」をご覧ください。
この時、「色」という漢字は全角でしか入力できないので、全角になるのは当然ですが、色番号の数字はもちろん半角で入力しますヨ。
というわけで、
- プラスの場合、マイナスの場合、ゼロの場合の表示形式を、それぞれ「；（セミコロン）」で区切る
- 色を設定したい場合には、表示形式の先頭に、設定したい色を角括弧で囲んで表現する
というのがお約束ですから、それらを合わせて表現するとこのようになります。
もちろん、色を設定したくない場合には、色を記述する必要はありません。
また、次のお約束もとても大切です。
そのまま表示したい文字の前後をダブルクォーテーションで囲む。
（もちろんダブルクォーテーションは半角で入力。）
但し、マイナスの場合の表示形式のところで例に挙げた「"値引き" -#,##0"円"」のように、スペース、マイナスは、ダブルクォーテーションで囲まなくてもそのまま表示される特別な記号です。
Officeの使いこなしテクニックをメールでお届け！