コンマ付きの億万円表示にする方法(例:12億3,456万7,890円)

前回、ある数値を億万円表示にする方法をご紹介しました。

ある数値を億万円表示にする方法

億万円表示にする方法(例:1234567890→12億3456万7890円)
1234567890という数値をパッと見ただけでは、いくらなのかわかりづらいです。この数値を12億3456万7890円という風に表示できたら、わかりやすいですよね。このページでは、数値を億万円表示であらわす方法をご紹介します。

しかし、前回の方法だとコンマ(,)をつけることができないのです。

例えば、1234567890という数値を上の方法で億万表示にすると12億3456万7890円とすることはできますが、12億3,456万7,890円というように(,)をつけることができません。

また、数値の大きさ(桁数)に合わせて、個別に設定する必要もあります

個別に設定するのは少しめんどくさいですよね。さらに、数値の大きさと合わない設定をしてしまって、変な表示になってしまう場合もあるかと思います。

設定を誤ると表示がおかしくなる

ということでこのページでは、ある数値を億万円表示にして、かつコンマをつける方法をご紹介します。

スグにためセル! – コピペですぐ使える

TEXT(LEFT(対象数値,MAX(0,LEN(対象数値)-8)),”[<>0]#,##0億;”)&
TEXT(LEFT(RIGHT(対象数値,8),MAX(0,LEN(RIGHT(対象数値,8))-4)),”[<>0]#,##0万;”)&
TEXT(RIGHT(対象数値,4),”[<>0]#,##0円;円”)
※この式は±1円から±9,999億9,999万9,999円まで対応可能です。
※ より詳しい解説はここから下に続きます。
スポンサーリンク

実際にタメしてみよう

では、実際にタメしてみましょう。

A1セルに1234567890という数値が入力されています。

この数値をB1セルに億万円表示(コンマ付き)で表示してみましょう。

A1セルに1234567890と入力されている

B1セルに今回ご紹介した計算式を入力します。

(今回の計算式は少し複雑ですので、コピペして使用することをおすすめします。)

B1セル=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円;円”)

すると、B1セルの答えは、12億3,456万7,890円となりますね。ちゃんとコンマも入っています。

12億3,456万7,890円となる

それでは、この計算式でほかの数値も億万円表示にしてみましょう。

それが下の図になります。

数値の大きさが違っても大丈夫

この図では、B列とD列には今回ご紹介した計算式を使用していますが、数値の大きさ(桁数)が変わってもきちんと表示されていることがわかりますね。

さらに、マイナスでもきちんと表示されています。

また、10000=1万円や、100000000=1億円のようにキリのいい数値のときでもきちんと表示されます

キリ番でも大丈夫

ちなみに表示形式を利用した方法の場合は、10000は1万0000円、100000000は1億0000万0000円と表示されますので、設定を変更しなければなりません。

キリ番の場合0000となる

また、今回の計算式では、10001(1万1円)や100000001(1億1円)のように数値のケタが大きく飛んでいるときでもきちんと表示されます。

桁が飛んでも大丈夫

ちなみに表示形式を利用した方法の場合は、10001は1万0001円、100000001は1億0000万0001円と表示されます。

桁が飛んだときは0000や0001となる

このように、今回ご紹介した計算式を使えば、桁数を気にせず億万円表示をすることができます。

計算の仕組みを見てみよう

ここからはなぜこの計算式で億万円表示になるのかをご説明します。

ちょっと難しい話になりますので、わからない方は飛ばしていただいても大丈夫です。コピペだけで計算できるのですから。

今回の計算式は次のようになります。

B1セル=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円;円”)

この計算式は長いのですが、よく見ると3つの部分を「&」で結合してできているのです。

&で結合とは?

別々のセルに入力された文字を結合したいときは文字の足し算(&)を使おう
エクセルでは、数の足し算、引き算が可能ですが、文字の足し算もできます。 今回は、文字を足し算する方法について、ご紹介します。

3つの部分とは、次のとおりです。

  1. TEXT(LEFT(A1,MAX(0,LEN(A1)-8)),”[<>0]#,##0億;”)・・・億のパート
  2. TEXT(LEFT(RIGHT(A1,8),MAX(0,LEN(RIGHT(A1,8))-4)),”[<>0]#,##0万;”)・・・万のパート
  3. TEXT(RIGHT(A1,4),”[<>0]#,##0円;円”)・・・円のパート

億のパート

まずは億のパートから見ていきましょう。

「億」のパートは次のようになっています。

億のパート = TEXT(LEFT(A1,MAX(0,LEN(A1)-8)),”[<>0]#,##0億;”)

このパートは、TEXT関数がメインとなっています。

TEXT関数

TEXT関数は、数値を指定した表示形式で表示させる関数です。

TEXT(数値 , 表示形式

ここでいう表示形式とは、

“[<>0]#,##0億;”

の部分となります。

今回は、億のパートなので表示形式は、「#,##0億」となるのが基本です。

しかし、その前に「[<>0]」という文字が入っていますね。これは、もし数値が0でなければ、#,##0億という表示を返して、そうでないなら、非表示にしてほしいということを表しています。

そして、数値の部分ですが、

LEFT(A1,MAX(0,LEN(A1)-8))

となっています。

今必要なのは億の部分の数値です。1234567890でいえば「12」の部分だけが必要なのです。

そのため、LEFT関数で数値を左から取り出しているのです。

LEFT関数

LEFT関数は、対象文字列の左から文字数に指定された文字数だけを取り出す関数です。

LEFT(対象文字列 , 文字数

ここで、何文字取り出すのかというのが問題となります。

例えば1億という数値は100,000,000なので、9文字のうち1文字が億のパートとなります。残りは万や円のパートで、それが8文字あるわけです。

つまり、億万表示にしたい数値の文字数から8文字引いた数が億のパートとなるのです。

そこで、LEN(A1)-8という計算式が出てくるわけです。

LEN関数

LEN関数は文字列の文字数を数える関数です。

LEN(文字列

次にMAXの部分の解説ですが、この部分はエラーを回避するために入れてあります。

数値によっては億まで達していないかもしれません。例えば、1234567という数値を億万表示にしたい場合は、123万4567円なので、億まで行っていません。

この場合、LEN(1234567)-8は-1となってしまいます。

そうするとLEFT(文字列 , -1)という形になり、エラーになりますね。

エラーにならないようにMAX関数を使って、最低でも0という数値になるようにしているわけです。

ちなみに、LEFT(文字列 , 0)は空白(0ともいえる)となります

そのため、TEXT関数の条件であった「もし数値が0であれば、非表示」に該当し、最終的に億のパートは非表示、つまり、何も答えがない状態になるのです。

ふう・・・けっこう、解説が大変です。
もう少し頑張りましょう。

万のパート

次に万のパートを見ていきましょう。

万のパート = TEXT(LEFT(RIGHT(A1,8),MAX(0,LEN(RIGHT(A1,8))-4)),”[<>0]#,##0万;”)

ここもTEXT関数がメインとなっています。

表示形式については、億のパートと同じように0でない場合は#,##0万という表示を、そうでない場合は非表示になるようにしてあります。

数値に関しては、

LEFT(RIGHT(A1,8),MAX(0,LEN(RIGHT(A1,8))-4))

となっています。

億のパートより少し複雑になっています。

万のパートは、上の部分(億)も下の部分(円)も取り除かなければなりません。

そのため、先にRIGHT(A1,8)によって、上の部分を取り除きます。

RIGHT関数で下〇桁を取り出す

RIGHT関数は、対象文字列の右から文字数に指定された文字数だけを取り出す関数です。

RIGHT(対象文字列 , 文字数

数値の下〇桁だけ抽出するカンタンな方法とマイナスにも対応させる方法
エクセルで数値の下3桁や下4桁など、下〇桁だけ抽出したいときってありませんか?このページでは、カンタンに下〇桁を取り出す方法と、マイナスの数値はマイナスのまま下〇桁を取り出す方法の2パターンについてご紹介します。

あとは、残った下8桁から上4桁を取り出せばいいわけですが、単純にLEFT(下8桁 , 4)ではだめです。

なぜかというと、例えば、指定された数値が123456=12万3456のように6文字だった場合、必要なのは、12だけですが、LEFT(123456 , 4)だと1234となってしまうからです。

そのため、念のために下8桁の文字数をLEN(RIGHT(A1,8))で確認し、それから4文字を引いた数を求めているのです。そうすれば、先ほどの例のように6文字だった場合は、4文字を引いて2文字が必要な文字数となり、LEFT(123456 , 2)で、12が取り出せるというわけです。

ちなみに、MAX関数に関しては億のパートと同じです。

円のパート

最後に円のパートを見ていきましょう。

TEXT(RIGHT(A1,4),”[<>0]#,##0円;円”)

円のパートは今までで一番カンタンですね。

TEXT関数の数値の部分はRIGHT(A1,4)ですので、下4桁を対象としています。

そして、表示形式については、0でない場合は#,##0円という表示を、そうでない場合は円を表示するようになっています。

この処理によって、10000というような数値が来た場合は、円のパートは0となりますので、「0円」ではなく「円」だけが表示されるようになります。そして、万のパートの「1万」と組み合わされて「1万円」となるわけです。

以上のように、複雑で長い計算式でしたが、それぞれ億万円のパートで分けて処理をし、最後にそれらを結合することで、きちんと表示されるようになっているのです。

この考え方を応用することで、兆の処理も追加することができますね。

ふぅ。とりあえず説明は以上です。お疲れ様でした。

まとめ

今回ご紹介した計算式は複雑ですが、詳しい仕組みを理解しなくてもコピペで使えますので、桁数を気にせずに億万円表示をしたい場合はどうぞご利用ください。

以上、このページでは、ある数値を億万円表示にして、かつコンマをつける方法(例:12億3,456万7,890円)をご紹介しました。