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

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

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

地味に役立つ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億円」と表記されていることが確認できるだろう。

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