読み辛い「桁の多い数値」をTEXT関数で丸める方法

TEXT関数で億円・兆円単位をそのまま表示させてみよう

今週も、エク達の時間がやってまいりました。ウェブサイト『エクセルの達人』からの記事の転載も、いよいよ大詰めを迎えています。こうしたなか、本日は「3ケタ区切り」と「4ケタ区切り」の違いをうまく変換するテクニックについて考えてみたいと思います。

エクタツは、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。

突然だが、「1200000000000円」と書かれてすんなり読めるという方はいらっしゃるだろうか。私見だが、よっぽどの数値オタクなどでもない限り、これを直ちに「一兆二千億円(いっちょうにせんおくえん)」と読める方はいないだろう。エクセルでこれをうまく表記するにはどうすれば良いか、そのヒントはやはり「TEXT関数」にある。

地味に役立つTEXT関数

以前から当ウェブサイトでは、「TEXT関数」を使った書式設定に関する「実務で使えるテクニック」を取り上げてきた。

たとえば、『TEXT関数で書式を維持したままセルの値を結合する』では、ワードなどに貼り付けるための図表を作るテクニックを紹介した。

また、『日付から曜日を抽出する:「地味に役立つ」テクニック』では、日付を曜日に変換するというテクニックを取り上げた。

いずれも、TEXT関数を使えば、整数とパーセントを結合したり、日付のシリアル値を曜日や年月に変換したりするのも自由自在である。

エクセルの「3桁区切り」の問題点

さて、エクセルの場合は「3桁区切り」で表記させる機能がある。

たとえば、「1200000」と表記されるのではなく、「1,200,000」と表記されていた方が、すんなりと「ひゃくにじゅうまん」と読めるだろう。また、「12000000」「120000000」も、それぞれ、「12,000,000」「120,000,000」と表記してくれた方がわかりやすい。

このように、ビジネスの現場では、数値は3桁区切りが一般的に用いられる。

ただ、「1,200,000」や「12,000,000」くらいならまだわかるが、いきなり、「12,000,000,000」などと書かれると、多くの方が戸惑うのではないだろうか。たとえば、ある企業の売上高では「120億円」などと表現することがあるが、これについては財務諸表などの表示上は「12,000百万円」などと表記される。

  • 口語では…「売上高120億円」
  • 表示では…「売上高12,000百万円」または「売上高12,000,000,000円」

この「120億円」と「12,000百万円」、あるいは「120億円」と「12,000,000,000円」は同じ意味だが、経理の現場などにいる人でないと、いきなり「12,000百万円」という数値を見て「ひゃくにじゅうおくえん」という表現は出てこないだろう。ましてや「12,000,000,000円」ならなおさらだ。

また、少し規模が大きな自治体だと、毎年の予算が「1200億円」、「1.2兆円」などのケースもある。これらの場合は「120,000百万円」「1,200,000百万円」、酷い場合には「120,000,000,000円」「1,200,000,000,000円」などと表記されることもある。

  • 1200億円(せんにひゃくおくえん)→120,000,000,000円
  • 1兆2000億円(いっちょうにせんおくえん)→1,200,000,000,000円

著者自身の経験上、人間の頭脳では、特殊な訓練でも受けていない限り、注釈なしで読めるのはせいぜい8桁、すなわち「12,000,000円」(せんにひゃくまんえん)までだ。商売をしている人であれば、「120,000,000円」(いちおくにせんまんえん)くらいなら何とか読めるかもしれない。

TEXT関数で「うまく丸める」

いずれにせよ、日本語は4桁ごとに「万→億→兆」と単位が増えて行くのに、エクセルでは3桁区切りしか使えないというのは、いかにも不便である。

そこで、「TEXT関数」を使ってこれを「丸める」方法について考えてみよう。

上記の議論をまとめると次のとおりだ(図表1)。

図表1 わかり辛い「桁の多い数値」

これについて、日本語の「億円単位」は9桁以上であるため、便宜上、B8セル、すなわち「120,000,000(いちおくにせんまん)」に注目してみよう。

そして、C8セルにこんな関数を入力する。

=TEXT(B8/100000000,”###0億円”)

ここで、クオーテーションマークの部分に “#,##0” と入力すれば、3桁区切りに変わってしまう。しかし、あえてこのような形で入力することで、億円表示にすることができる。計算結果は、こんな具合だ(図表2)。

図表2 計算結果

この場合、「120,000,000(いちおくにせんまん)」の億円未満が四捨五入されて「1億円」になってしまう不具合はあるが、以下、「1,200,000,000円」は「12億円」、「12,000,000,000円」は「120億円」、「120,000,000,000円」は「1200億円」と表示されていることが確認できるだろう。

ただし、問題はC12セルだ。

このセル、本来ならば「1,200,000,000,000円」を「1兆2000億円」などと表記したいところだが、「12000億円」と表示されてしまっている(発音は「いちまんにせんおくえん」だろうか?)。これだと不正確だ。

そこで、関数をこんな具合に修正してみよう。

=IF(B8>1000000000000,TEXT(B8/100000000,”#兆###0億円”),TEXT(B8/100000000,”###0億円”))

わかりやすく、これをC8セル以降に入力して行こう。その結果が、図表3だ。

図表3 入力結果

いかがだろうか。

この方法だと、とくに「1,200,000,000,000円」がD12セルにおいて「1兆2000億円」と表記されていることが確認できるだろう。

こうした分析は、財務省が公表する『普通貿易統計』など、とくに桁が大きな数値を報告書に用いるのに非常に役立つ。是非、ご活用いただきたい。

エクタツは、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。

読者コメント一覧

  1. へちまたわしのみに非ず より:

    書式設定の[]で条件が設定できますので、以下でも同じ結果を得られると思います。
    =TEXT(A1/100000000,”[>=10000]#兆####億;#億”)&”円”

    IF条件を用いて万の桁までなら以下でしょうか。
    =IF(INT(A1/100000000),
      TEXT(A1/100000000,”[>=10000]#兆####億;[<9999]#億"),
      TEXT(A1/10000,"#万")
      )
     &"円")

    億と万の一桁が四捨五入(1億4千万→1億、1億5千万→2億…)されるのは同じです。
    VBAや他セルなど使用せず、1セルの関数内で完全な単位表示(万、億、兆)を実現する方法を考えてみると実用というよりはパズルの領域になりそうですね。
    それはそれで面白そうです。

    1. へちまたわしのみに非ず より:

      自己レスですみません。全然簡単でした。

      =TEXT(INT(A1/1000000000000),”#兆”)
      &TEXT(INT(MOD(A1/1000000000000,1)*10000),”#億”)
      &TEXT(INT(MOD(A1/100000000,1)*10000),”#万”)
      &TEXT(INT(MOD(A1/10000,1)*10000),”#”)
      &”円”

    2. へちまたわしのみに非ず より:

      度々すみません。
      誤り(兆億万の非表示の抜け)があったので修正

      =TEXT(INT(A1/1000000000000),”[>=1]#兆;#”)
      &TEXT(INT(MOD(A1/1000000000000,1)*10000),”[>=1]#億;#”)
      &TEXT(INT(MOD(A1/100000000,1)*10000),”[>=1]#万;#”)
      &TEXT(INT(MOD(A1/10000,1)*10000),”#”)
      &”円”

  2. ほたか より:

    とりあえず一番上の桁がどこにあるのかを知りたいのであれば、、、

    =TRIM(RC[-1]*10^(-QUOTIENT(LOG(RC[-1]),4)*4) &MID(“ 万億兆京垓”,QUOTIENT(LOG(RC[-1]),4)+1,1))

    などというのもありかもしれませんね。

    1. へちまはたわしのみに非ず より:

      ほたか 様

      アリナシ以前の優勝です。
      桁の取り方のエレガンスに惚れ惚れしました。

      自分なら同じロジックにしても文字列カウントがせいぜいです
      =TRIM(A1/10^(INT(LEN(A1)/4-0.25)*4)&MID(“ 万億兆京垓”,INT(LEN(A1)/4-0.25)+1,1))

※【重要】ご注意:他サイトの文章の転載は可能な限りお控えください。

やむを得ず他サイトの文章を引用する場合、引用率(引用する文字数の元サイトの文字数に対する比率)は10%以下にしてください。著作権侵害コメントにつきましては、発見次第、削除します。

※【重要】ご注意:人格攻撃等に関するコメントは禁止です。

当ウェブサイトのポリシーのページなどに再三示していますが、基本的に第三者の人格等を攻撃するようなコメントについては書き込まないでください。今後は警告なしに削除します。なお、コメントにつきましては、これらの注意点を踏まえたうえで、ご自由になさってください。また、コメントにあたって、メールアドレス、URLの入力は必要ありません(メールアドレスは開示されません)。ブログ、ツイッターアカウントなどをお持ちの方は、該当するURLを記載するなど、宣伝にもご活用ください。なお、原則として頂いたコメントには個別に返信いたしませんが、必ず目を通しておりますし、本文で取り上げることもございます。是非、お気軽なコメントを賜りますと幸いです。

コメントを残す

メールアドレスが公開されることはありません。