前回、ある数値を億万円表示にする方法をご紹介しました。
しかし、前回の方法だとコンマ（，）をつけることができないのです。
例えば、1234567890という数値を上の方法で億万表示にすると12億3456万7890円とすることはできますが、12億3,456万7,890円というように（，）をつけることができません。
また、数値の大きさ（桁数）に合わせて、個別に設定する必要もあります。
個別に設定するのは少しめんどくさいですよね。さらに、数値の大きさと合わない設定をしてしまって、変な表示になってしまう場合もあるかと思います。
ということでこのページでは、ある数値を億万円表示にして、かつコンマをつける方法をご紹介します。
スグにためセル！ – コピペですぐ使える
TEXT(LEFT(RIGHT(対象数値,8),MAX(0,LEN(RIGHT(対象数値,8))-4)),”[<>0]#,##0万;”)&
TEXT(RIGHT(対象数値,4),”[<>0]#,##0円;円”)
※この式は±１円から±9,999億9,999万9,999円まで対応可能です。
実際にタメしてみよう
では、実際にタメしてみましょう。
Ａ１セルに1234567890という数値が入力されています。
この数値をＢ１セルに億万円表示（コンマ付き）で表示してみましょう。
Ｂ１セルに今回ご紹介した計算式を入力します。
（今回の計算式は少し複雑ですので、コピペして使用することをおすすめします。）
すると、B1セルの答えは、12億3,456万7,890円となりますね。ちゃんとコンマも入っています。
それでは、この計算式でほかの数値も億万円表示にしてみましょう。
それが下の図になります。
この図では、B列とD列には今回ご紹介した計算式を使用していますが、数値の大きさ（桁数）が変わってもきちんと表示されていることがわかりますね。
さらに、マイナスでもきちんと表示されています。
また、10000＝１万円や、100000000＝１億円のようにキリのいい数値のときでもきちんと表示されます。
ちなみに表示形式を利用した方法の場合は、10000は1万0000円、100000000は1億0000万0000円と表示されますので、設定を変更しなければなりません。
また、今回の計算式では、10001（1万1円）や100000001（1億1円）のように数値のケタが大きく飛んでいるときでもきちんと表示されます。
ちなみに表示形式を利用した方法の場合は、10001は1万0001円、100000001は1億0000万0001円と表示されます。
このように、今回ご紹介した計算式を使えば、桁数を気にせず億万円表示をすることができます。
計算の仕組みを見てみよう
ここからはなぜこの計算式で億万円表示になるのかをご説明します。
ちょっと難しい話になりますので、わからない方は飛ばしていただいても大丈夫です。コピペだけで計算できるのですから。
今回の計算式は次のようになります。
この計算式は長いのですが、よく見ると３つの部分を「＆」で結合してできているのです。
３つの部分とは、次のとおりです。
- TEXT(LEFT(A1,MAX(0,LEN(A1)-8)),”[<>0]#,##0億;”)・・・億のパート
- TEXT(LEFT(RIGHT(A1,8),MAX(0,LEN(RIGHT(A1,8))-4)),”[<>0]#,##0万;”)・・・万のパート
- TEXT(RIGHT(A1,4),”[<>0]#,##0円;円”)・・・円のパート
億のパート
まずは億のパートから見ていきましょう。
「億」のパートは次のようになっています。
このパートは、TEXT関数がメインとなっています。
TEXT関数
TEXT関数は、数値を指定した表示形式で表示させる関数です。
TEXT（数値 , 表示形式）
ここでいう表示形式とは、
の部分となります。
今回は、億のパートなので表示形式は、「#,##0億」となるのが基本です。
しかし、その前に「[<>0]」という文字が入っていますね。これは、もし数値が0でなければ、#,##0億という表示を返して、そうでないなら、非表示にしてほしいということを表しています。
そして、数値の部分ですが、
となっています。
今必要なのは億の部分の数値です。1234567890でいえば「12」の部分だけが必要なのです。
そのため、LEFT関数で数値を左から取り出しているのです。
LEFT関数
LEFT関数は、対象文字列の左から文字数に指定された文字数だけを取り出す関数です。
LEFT（対象文字列 , 文字数）
ここで、何文字取り出すのかというのが問題となります。
例えば1億という数値は100,000,000なので、9文字のうち1文字が億のパートとなります。残りは万や円のパートで、それが８文字あるわけです。
つまり、億万表示にしたい数値の文字数から８文字引いた数が億のパートとなるのです。
そこで、LEN(A1)-8という計算式が出てくるわけです。
LEN関数
LEN関数は文字列の文字数を数える関数です。
LEN（文字列）
次にMAXの部分の解説ですが、この部分はエラーを回避するために入れてあります。
数値によっては億まで達していないかもしれません。例えば、1234567という数値を億万表示にしたい場合は、123万4567円なので、億まで行っていません。
この場合、LEN(1234567)-8は-1となってしまいます。
そうするとLEFT(文字列 , -1)という形になり、エラーになりますね。
エラーにならないようにMAX関数を使って、最低でも０という数値になるようにしているわけです。
ちなみに、LEFT(文字列 , ０)は空白（０ともいえる）となります。
そのため、TEXT関数の条件であった「もし数値が０であれば、非表示」に該当し、最終的に億のパートは非表示、つまり、何も答えがない状態になるのです。
ふう・・・けっこう、解説が大変です。
もう少し頑張りましょう。
万のパート
次に万のパートを見ていきましょう。
ここもTEXT関数がメインとなっています。
表示形式については、億のパートと同じように０でない場合は#,##0万という表示を、そうでない場合は非表示になるようにしてあります。
数値に関しては、
となっています。
億のパートより少し複雑になっています。
万のパートは、上の部分（億）も下の部分（円）も取り除かなければなりません。
そのため、先にRIGHT(A1,8)によって、上の部分を取り除きます。
RIGHT関数で下〇桁を取り出す
RIGHT関数は、対象文字列の右から文字数に指定された文字数だけを取り出す関数です。
RIGHT(対象文字列 , 文字数）
あとは、残った下８桁から上４桁を取り出せばいいわけですが、単純にLEFT(下８桁 , 4)ではだめです。
なぜかというと、例えば、指定された数値が123456＝12万3456のように６文字だった場合、必要なのは、12だけですが、LEFT(123456 , ４)だと1234となってしまうからです。
そのため、念のために下８桁の文字数をLEN(RIGHT(A1,8))で確認し、それから４文字を引いた数を求めているのです。そうすれば、先ほどの例のように６文字だった場合は、４文字を引いて２文字が必要な文字数となり、LEFT(123456 , 2)で、12が取り出せるというわけです。
ちなみに、MAX関数に関しては億のパートと同じです。
円のパート
最後に円のパートを見ていきましょう。
円のパートは今までで一番カンタンですね。
TEXT関数の数値の部分はRIGHT(A1,4)ですので、下４桁を対象としています。
そして、表示形式については、０でない場合は#,##0円という表示を、そうでない場合は円を表示するようになっています。
この処理によって、10000というような数値が来た場合は、円のパートは０となりますので、「０円」ではなく「円」だけが表示されるようになります。そして、万のパートの「１万」と組み合わされて「１万円」となるわけです。
以上のように、複雑で長い計算式でしたが、それぞれ億万円のパートで分けて処理をし、最後にそれらを結合することで、きちんと表示されるようになっているのです。
この考え方を応用することで、兆の処理も追加することができますね。
・
・
・
ふぅ。とりあえず説明は以上です。お疲れ様でした。
まとめ
今回ご紹介した計算式は複雑ですが、詳しい仕組みを理解しなくてもコピペで使えますので、桁数を気にせずに億万円表示をしたい場合はどうぞご利用ください。
以上、このページでは、ある数値を億万円表示にして、かつコンマをつける方法（例：12億3,456万7,890円）をご紹介しました。
