セルの中に入力された内容量の数字。
この数字はグラム数です。
今回は、このグラム数の数字を、
「g（グラム）」、「kg（キログラム）」、「t（トン）」のうちの適切な単位に、自動的に換算して表示されるようにしてみます！
早速解説をスタートしますが、今回は、
を、既にご覧いただいていることを前提として進めていきますので、まだの方は先に上記のページをご覧ください。
「g（グラム）」だけの表示なら速攻解決！
まずは単純に、グラム数の数字の後ろに、「g」の単位記号を表示させるだけの場合について、考えてみましょう。
まずはグラム数の数字を読みやすくするため、3桁ごとにカンマを付けたいのですが、それは表示形式の役割。
それをユーザー定義の表示形式の記号を使って設定する際の、おなじみの表現が「#,##0」。
そして更に、この数字の後ろに、「g」という、単位にあたる文字を表示させたいので、ユーザー定義の表示形式を図のように設定すればいいことになります。
ちなみに、今回使用している「グラム」の「g」は、アルファベットの「g」を単純に入力しているだけです。
それでは実際に、この表示形式をセルに設定してみましょう。
ユーザー定義の表示形式を設定したいセルを範囲選択し、選択したセルのところだったらどこでもいいので右クリック、
［セルの書式設定］をクリックすると、
［セルの書式設定］ダイアログボックスが表示されます。
表示形式の設定を自分で作りたいので、［表示形式］タブを選択後、左側の［分類］から［ユーザー定義］を選択します。
すると、その右側の［種類］欄に、現在、範囲選択しているセルに設定されている表示形式を、記号で表したものが表示され、更にそれと全く同じものがこの欄の一番上にも映し出されます。
この［種類］の一番上の欄が、表示形式の記号を入力するところなので、この欄の文字をすべて消して、「#,##0"g"」と、表示形式の記号をすべて自分で入力してもいいですし、
［種類］の一覧の中には、よく使いそうな表示形式の表現が表示されているので、この中から「#,##0」を選択すると、［種類］の一番上の欄に、「#,##0」が映し出されるので、ここまでをいちいち入力する手間が省けます。
そして映し出された「#,##0」の一番後ろに、
「"g"」を付け足せば、少ない手間で「#,##0"g"」と設定することができます。
もちろん、ユーザー定義の表示形式の記号は、半角で入力するのがお約束です。
ダイアログボックス右下の［OK］ボタンをクリックすると、
3桁ごとにカンマを付け、更に数字の後ろに「g」という単位も表示させることができました。
スポンサーリンク
単位ごとの表示形式の表現はこうなる！
でも、セルに入力されている数字を自動的に判断して、こんな風に「g（グラム）」、「kg（キログラム）」、「t（トン）」の単位を使い分けて表示させたい場合には、どうすればいいでしょう？
まずは重さの単位について考えます。
重さの単位は、
「1,000g」は「1kg」、
「1,000,000g」は「1t」
と、千単位、百万単位で単位が繰り上がります。
ということは、単位に合わせて内容量の数字を換算して表示させるためには、数字の一ケタ目を千の位や百万の位にして、それ未満の桁を省略するユーザー定義の表示形式を使えばいいことになります！
それでは、グラム数を「kg（キログラム）」に換算して表示させる場合のことから考えてみましょう。
「1,000g」を「1kg」となるように表示させるには、数字の一ケタ目を千の位にして、それ未満の桁を省略する表示形式にすればいいので、おなじみの「#,##0」の表現の後ろに、千の位のカンマを付けることで、一ケタ目に千の位の数字が表示されるようにできる「#,##0,」の表現を使うことになります。
そして最後に、単位記号の文字である「kg」が表示されるように設定します。
ちなみに、今回使用している「キログラム」の「kg」は、アルファベットの「k」と「g」を単純に入力しているだけです。
ところが今回の場合、内容量が「1,000kg」以上だった場合、更に単位を繰り上げて「1t」と表示させたいので、「kg」の単位で「1,000kg」以上の表示がされることはありません。
ということは、図のグレーの部分内にある、3桁区切りのカンマは不要だということになります。
そうすると、1桁分の数字を表す表示形式の記号「ゼロ」さえあれば、桁が増えても、その増えた分の桁も自動的に増やして表示してくれるので、図のグレーの部分は不要ということになり、
単位が「kg（キログラム）」の場合の、ユーザー定義の表示形式の表現は、図のような表現で事足りることになります。
また、「1,000,000g」を「1t」となるように表示させるには、数字の一ケタ目を百万の位にして、それ未満の桁を省略する表示形式にすればいいので、先ほどの「0,」の表現に、百万の位のカンマを加えて「0,,」とします。
そうすることで、一ケタ目に百万の位の数字が表示されるようにできます。
そして最後に、単位記号の文字である「t」が表示されるように設定します。
単位を切り分けて表示させるために［条件］を設定！
重さの単位ごとの表示形式の表現はこれでOKですが、これらの表現は、どのようにすれば自動的に使い分けて表示されるようにできるのでしょうか？
先ほども見ていただいた通り、重さの単位は
「1,000g」は「1kg」
「1,000,000g」は「1t」
という風に繰り上がっていくので、
セルに入力されている数字が、
1000以上だった場合には、「kg」の場合の表示形式
1000000以上だった場合には、「t」の場合の表示形式
どちらにも当てはまらない場合には、「kg」にも「t」にも繰り上がれない小さい数字なので、数字をそのまま「g」付きで表示
と考えることができます！
と、ここで出てくるのが表示形式に条件を設定する方法。
実はユーザー定義の表示形式には条件を設定することができて、この条件に該当した場合にはこの表示形式で、その条件に該当した場合にはその表示形式で、という風に設定することができるんです！
ユーザー定義の表示形式に条件を設定する場合の書き方がこれ。
表示形式の前に、条件を角括弧で囲んで表現します。
今回は、「1000以上だった場合には、「kg」の場合の表示形式」としたいので、その表現はこうなります。
もちろん、ユーザー定義の表示形式で使う記号は、半角で入力するのがお約束なので、角括弧や、「1000以上」の「以上」を表す「>=」などは半角で入力します。
また、「1000000以上だった場合には、「t」の場合の表示形式」としたいので、その表現はこうなります。
そして、「どちらにも当てはまらない場合には、数字をそのまま「g」付きで表示」したいのですが、条件に該当しないものは、角括弧で囲んだ条件を書くことなく、表示形式だけを書くので、表現はこうなります。
そして、条件に該当する場合、該当しない場合それぞれを、図のような順番で記載し、それぞれを「；（セミコロン）」で区切って設定します。
当然、ユーザー定義の表示形式で使う記号は、半角で入力するのがお約束なので、セミコロンも半角で入力します。
ちなみに、ユーザー定義の表示形式に条件を設定する場合、設定できる条件は2つまでです。
そして、このように表示形式を書くことで、Excelさんは左から順番に条件に合うかどうかを判断していくので、
こんな感じでExcelさんは処理していくことになります。
では、この書き方に、今回の表現を当てはめると、
このようになります！
実はここで隠れたポイントとして、条件1を「1000000以上」、条件2を「1000以上」としています。
条件1を「1000以上」、条件2を「1000000以上」のように、逆にしてしまうと、うまく設定できないのでこうしているのですが、この理由については後でご説明しますので、まずは設定を先にしていきましょう！
スポンサーリンク
［条件］のあるユーザー定義の表示形式を、実際に設定してみよう！
それでは、このユーザー定義の表示形式の表現を、実際のセルに設定してみましょう！
「内容量」のセルを範囲選択し、選択したセルのところだったらどこでもいいので右クリック、
［セルの書式設定］をクリックします。
表示されたダイアログボックスの、［表示形式］タブの［ユーザー定義］、［種類］の一番上の欄に、先程まとめた下図の表現を入力したら、ダイアログボックス右下の［OK］ボタンをクリックします。
セルに入力されている数字に合わせて、「t」「kg」「g」の単位を使い分けて表示させることができました！
なぜ「1000000以上」を条件1に設定したのか？
今回の設定では、条件1を「1000000以上」、条件2を「1000以上」としました。
実は条件1を「1000以上」、条件2を「1000000以上」のように、逆にしてしまうと、うまく設定できないのですが、それについてご説明していきます。
今回の設定のように、条件1を「1000000以上」と設定した時点で、
条件1に該当していないものを、条件2という「ふるい」にかけることになるので、条件2のふるいにかかるものは、「1000000以上」に該当していないもの、つまり「1000000未満」であることが大前提になります。
その大前提があった上で、「1000以上」という条件2がかかってくるので、条件2に当てはまるものは、必然的に「1000以上、1000000未満」のものになる、という仕組みです。
ところが、この条件の設定を逆にして、条件1を「1000以上」、条件2を「1000000以上」としてしまうと、条件1の「1000以上」に、そもそも条件2の「1000000以上」というものが含まれてしまっているため、「条件1に該当しなかったものを、条件2のふるいにかける」という大前提が崩れた設定になってしまうのです。
大前提が崩れた設定をしてしまうと、条件によってうまく切り分けて表示させることができません。
条件を設定する場合には、「条件1に該当しなかったものを、条件2のふるいにかける」という大前提を意識しながら設定すると、どちらを条件1にすべきか、ということが見えてきます。
表示が変？
さて、こちらの図をご覧ください。
この図は、ちょっとおかしなことになっています。
C3番地には「1000g」と表示されていて、C4番地には「1kg」と表示されています。
「1000g」は「1kg」のことなので、今回の設定ではC3番地は「1kg」と表示されるはずなのに。
こんな時は、表示が変だな、と思うセルを選択して、数式バーを見ると、そのセルの中身が映し出されるので、それが参考になります！
このC3番地に入力されている数字は、「1000」ではなく、「999.9」であることが分かります。
今回設定した表示形式では、「1000以上」で「kg」単位の表示となるよう設定してあるので、「999.9」は「1000以上」に該当しないため、「kg」ではなく、「g」で表示されているというわけです。
そして更に、「小数点以下の四捨五入で、違いを知らないとマズイ表示形式とROUND関数」でご紹介している通り、Excelさんは、セルに入力されている数字を、表示形式に沿って見せる際、隠れ始める桁数のところで四捨五入した状態を表示するようになっています。
今回設定した表示形式は、小数点以下を表示する設定ではないため、隠れ始めの桁数となる小数第一位で四捨五入した状態の数字が、セルの表向きとして表示されます。
なので、実際は「999.9」なのに「1000」と表示されたというわけです。
こういった半端な数字が入力される可能性が無い表なら、今回のような設定でOKですし、その可能性があるなら、「1000g」ではなく、3桁区切りのカンマ付きで「1,000g」と表示されるよう「0"g"」の表示形式を「#,##0"g"」にするのも一案です。
また、小数点付きで表示されるよう「0.0"g"」という設定にすれば、「999.9g」と表示させることができます。
自分の作る表に合わせて、表示形式を柔軟に変えてみてください。
千の位を一ケタ目にして表示させている「kg」、百万の位を一ケタ目にして表示させている「t」も、小数点付きで表示させたい場合には、「千円単位・百万円単位で、なおかつ小数第1位まで表示」でご紹介している、ユーザー定義の表示形式の表現を使います。
スポンサーリンク
設定できる条件は2つまで
先程もご紹介しましたが、ユーザー定義の表示形式に条件を設定する場合、設定できる条件は2つまでです。
設定できる条件は2つまでなので、逆を言ってしまえば、条件が1つでもOK。
その場合も、最初に条件に該当する場合を表記し、最後に条件に該当しない場合を表記することになります。
条件付き書式でも設定できる
今回は、ユーザー定義の表示形式に直接条件を設定する方法をご紹介しましたが、条件付き書式でも、同じような設定が可能です。
Excel 2007以降の条件付き書式では、条件はいくつでも設定できるので、ユーザー定義の表示形式に直接条件を設定する今回の方法より、より自由がききます。
条件付き書式という機能があるにも関わらず、じゃぁ何で、ユーザー定義の表示形式に直接条件が設定できるのかというと、Excel 2003以前は、条件付き書式で表示形式を設定することができませんでした。
条件によって表示形式を変えられるのは、今回ご紹介したユーザー定義の表示形式に直接条件を設定する方法だけだったんです。
ところが、Excel 2007で条件付き書式に表示形式を設定できるようになったので、どっちの機能を使って設定しよう？なんて迷うことができるようになったわけです。
私の場合は、そんな歴史の流れで、表示形式の複雑な設定はユーザー定義の表示形式で、というのが染みついているので、今回はこちらの方法をご紹介しました。
Officeの使いこなしテクニックをメールでお届け！