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

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

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

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

地味に役立つTEXT関数

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

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

新宿会計士の政治経済評論

 

1 Pocket

2021.12.25

TEXT関数で書式を維持したままセルの値を結合する

https://shinjukuacc.com/20211225-99

エクセルを使ってレポートを書き上げたりする際に、エクセルの段階で書式設定までが完璧に済んでいると、実務上は何かと便利です。こうしたなか、本稿ではTEXT関数を使い、エクセルで書式を壊さずにうまく図表を作り込んでいくテクニックを紹介します。エク達は、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。エクセルを使用すると、セルの書式を細かく設定す...

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

新宿会計士の政治経済評論

2021.11.27

日付データからさまざまな情報を抽出するTEXT関数

https://shinjukuacc.com/20211127-99

エクセルを使っていると、日付を入力したつもりがいきなり数字になってしまう、といった「イライラ」もよく発生します。本稿は、『エク達』というウェブサイトにかつて掲載した『入力した日付が「数字の値」になってしまう場合の対処』、『日付から曜日を抽出する：「地味に役立つ」テクニック』という２つの記事を１つにまとめたものです。エク達は、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基...

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

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

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

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

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

ただ、「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円

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

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

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

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

上記の議論をまとめると次のとおりだ（図表１）。

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

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

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

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

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

図表２　計算結果

この場合、「120,000,000（いちおくにせんまん）」の億円未満が四捨五入されて「１億円」になってしまう不具合はあるが、以下、「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セル以降に入力して行こう。その結果が、図表３だ。

図表３　入力結果

いかがだろうか。

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

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