TEXT関数で億円・兆円単位をそのまま表示させてみよう
今週も、エク達の時間がやってまいりました。ウェブサイト『エクセルの達人』からの記事の転載も、いよいよ大詰めを迎えています。こうしたなか、本日は「3ケタ区切り」と「4ケタ区切り」の違いをうまく変換するテクニックについて考えてみたいと思います。
エクタツは、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。 |
突然だが、「1200000000000円」と書かれてすんなり読めるという方はいらっしゃるだろうか。私見だが、よっぽどの数値オタクなどでもない限り、これを直ちに「一兆二千億円(いっちょうにせんおくえん)」と読める方はいないだろう。エクセルでこれをうまく表記するにはどうすれば良いか、そのヒントはやはり「TEXT関数」にある。
地味に役立つTEXT関数
以前から当ウェブサイトでは、「TEXT関数」を使った書式設定に関する「実務で使えるテクニック」を取り上げてきた。
たとえば、『TEXT関数で書式を維持したままセルの値を結合する』では、ワードなどに貼り付けるための図表を作るテクニックを紹介した。
エクセルを使ってレポートを書き上げたりする際に、エクセルの段階で書式設定までが完璧に済んでいると、実務上は何かと便利です。こうしたなか、本稿ではTEXT関数を使い、エクセルで書式を壊さずにうまく図表を作り込んでいくテクニックを紹介します。エク達は、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。エクセルを使用すると、セルの書式を細かく設定す... TEXT関数で書式を維持したままセルの値を結合する - 新宿会計士の政治経済評論 |
また、『日付から曜日を抽出する:「地味に役立つ」テクニック』では、日付を曜日に変換するというテクニックを取り上げた。
エクセルを使っていると、日付を入力したつもりがいきなり数字になってしまう、といった「イライラ」もよく発生します。本稿は、『エク達』というウェブサイトにかつて掲載した『入力した日付が「数字の値」になってしまう場合の対処』、『日付から曜日を抽出する:「地味に役立つ」テクニック』という2つの記事を1つにまとめたものです。エク達は、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基... 日付データからさまざまな情報を抽出する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億円」と表記されていることが確認できるだろう。
こうした分析は、財務省が公表する『普通貿易統計』など、とくに桁が大きな数値を報告書に用いるのに非常に役立つ。是非、ご活用いただきたい。
エクタツは、「エクセル評論家」でもある新宿会計士が、エクセルなどの「オフィス系の汎用ソフト」を使い、誰でも気軽に、かつ安価に、とても迅速かつ正確に仕事をこなす、ビジネスマンとしての基本テクニックを研究するサイトである。 |
View Comments (6)
書式設定の[]で条件が設定できますので、以下でも同じ結果を得られると思います。
=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セルの関数内で完全な単位表示(万、億、兆)を実現する方法を考えてみると実用というよりはパズルの領域になりそうですね。
それはそれで面白そうです。
自己レスですみません。全然簡単でした。
=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),"#")
&"円"
度々すみません。
誤り(兆億万の非表示の抜け)があったので修正
=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),"#")
&"円"
とりあえず一番上の桁がどこにあるのかを知りたいのであれば、、、
=TRIM(RC[-1]*10^(-QUOTIENT(LOG(RC[-1]),4)*4) &MID(" 万億兆京垓",QUOTIENT(LOG(RC[-1]),4)+1,1))
などというのもありかもしれませんね。
ほたか 様
アリナシ以前の優勝です。
桁の取り方のエレガンスに惚れ惚れしました。
自分なら同じロジックにしても文字列カウントがせいぜいです
=TRIM(A1/10^(INT(LEN(A1)/4-0.25)*4)&MID(" 万億兆京垓",INT(LEN(A1)/4-0.25)+1,1))
機械可読データから「人間が理解しやすい見映えの」データを「自動で」生成というところがミソですねー。
エクセル方眼もこうだったら嫌われないのにw