[EXCEL]７桁の数値（金額）を３桁毎に区切って３セルに表示する方法（滅多にすることないと思いますが）

▶MAP▶PDF版▶表示▶関数▶ショトカ▶操作/コピー▶実務▶NG▶検索

関連記事：様式/入力表の作成

「こんな記事を見かけたので」のその２。

お題２　金額を３桁ずつ区切って表示する

例として挙げられていたのがこれ。
金額を３桁ずつ、別のセルに入れて表示したい、というもの。

正直、こんな様式自体がちょっとアレな印象。

とはいえ、私も、金額（10,000,000円以上）を、各桁毎に１マスに入れなきゃいけないという様式（ワード）を相手にしたことがあります（わずか５年前の話です）。
面倒だったので、エクセルで作り直しました（金額を１セルに入れると、桁毎に分解して、各セルに１桁ずつ表示）。
それよりは楽かな。

仮に「1234567」という数値（金額）があるとして、
「1234567｣のうち、下３桁を取りだすのは簡単です。
=RIGHT（セル,3)
RIGHT関数で、セル内の数値の右から３文字を抜き出す、だけ。
これで「567」が出ます。

次に「1234567」の「234」の取り出し。
これは数値を1000で割ったものを整数化（＝INT関数で小数点以下をなくす）した後に、同じくRIGHT関数で右３桁を取りだします。
=RIGHT(INT(B3/1000),3)
「1234567」が「÷100」（/100）で「1234.567」になり、INT関数で1234になるので、右3桁は234です。

最後に「1233456」か「１」を取り出します。
これは「234」と同じ発想。
割る数を1000から1000000にするだけです。
=RIGHT(TEXT(INT(B3/1000000),"#,###"),3)

結果は・・・

上のようになります。
しかし・・・
上のものには、カンマ「 , 」が付いていません。

1000以上ならカンマ付けが必要です。
さてこれをどうするか？

最初に思いついたのは
「数値が４桁以上ならカンマを付けて、３桁以下ならカンマを付けない」です。
ただし、数値から普通に文字を取り出してもカンマは付かないので、数値をTEXT関数で文字化してから、取り出します。

「1234567」の「567」の場合は、
数値が４桁以上（LEN関数で判定）だったら、数値をTEXT関数で「#,###」表示にして、文字列として「1,234,567」となったものから、右側４桁を抜き出すと「,567 」となります（カンマも１文字扱いになります）。
桁数が３桁以下なら、単に右３桁を取り出します。

=IF(LEN(B13>3),RIGHT(TEXT(B13,"#,###"),4),RIGHT(B13,3))

後半部分（IFの偽の部分）は、以下の通りでも構いません。=IF(LEN(B13>3),RIGHT(TEXT(B13,"#,###"),4),RIGHT(TEXT(B13,"#,###"),3))

「>3」（３より大きい）は「>=4」（４以上）でも同じです（整数だから）。

「1234567」の「234」場合も同じ発想ですが、もっと簡単です。
数値が３桁以上なら1000で割ったものをINTで整数化（小数点以下を削除）して、TEXT関数で「#,###」表示にしてから、RIGHT関数で右から４桁（カンマ＋３桁）を取り出します。
「1234567」なら「1,234」となったものから右４桁、つまり「,234」が表示されます。
「123456」なら、1000で割ると「123」となり、１の前にカンマはつかないので「４桁取り出す」という指示であっても、実際は「123」の３桁のみが表示されます（カンマなし）。
=RIGHT(TEXT(INT(B13/1000),"#,###"),4)

「1234567」の「１」の場合はもっと簡単。
単に、1000が1000000に変わるだけです。
=RIGHT(TEXT(INT(B13/1000000),"#,###"),3)

整理してみると、以下の通りです。

これでいっちょ上がり、と思ったのですが、お題の記事では、カンマを数値の「後」に付いています。「,345」でなく「345,」です。
さてどうするか？

「1234567」を1000で割ってINT関数で整数化すると、「1234」となります。
これをTEXT関数で「＃,###」でカンマ付けすると「1,234」になりますが、本来あるべき「４」の後のカンマは消えていますので、取り出せません。
ではどうするか？

取り出した数字の後ろにカンマだけ別に付けてあげればいいのです。

「1234567」の下３桁「567」は普通に取り出します。

「234」は、桁数が３桁以上なら1000で割って整数化し、TEXTで「＃,###」で文字化したもの「1,234」から右３桁を取り出し、その後ろに「＆”."」としてカンマを付け足します。これでカンマが一番右に付きます。

右から３桁取り出す、という指示であっても、1000で割ったものが１桁あるいは２桁なら、１桁あるいは２桁しか取り出さないので、問題ありません。それに「,」が付きます。
元の数値が３桁以下なら空欄（””）にします。カンマは付けません。

=IF(LEN(B25)>3,RIGHT(TEXT(INT(B25/1000),"#,###"),3)&",","")

表示するために付け足す「,」と、計算式の要素を区切る「,」とが紛らわしいのが難点ですね。

「1234567」の「１」の場合も同じ。
割る数を1000から1000000に変えるだけです。
=IF(LEN(B25)>=7,RIGHT(TEXT(INT(B25/1000000),"#,###"),3)&",","")

というわけで、整理すると・・・

これで「お困り」ごとは解決するでしょうか？

そもそも、こういう様式は紙時代の遺産（負債）なので、なくしていかないといけないでしょうね。
昔の様式に無理やり表示する、のではなく、様式自体（あるいは業務手順自体）を変えていくことが必要なんでしょうけれ、なかなか難しいのでしょうね（日々、実感し、藻搔いています）。

以上、お役に立てば幸いです。

（力技、失敗編）
「セルの書式設定」で「#, 　###, 　###」というように、３桁ずつ空白を開ける書式を設定して、空いたところにオブジェクトで線を引いておく、という方法も考えました（あくまでお遊び）。

でも、「OK」を押すと以下のようになってしまいます。

カンマがでません。

もう一度「セルの書式設定」を見ると、空白は残っていますが、カンマは消えています。

これでは「力技」作戦はできません。
というか、こんな解決方法を取ってはいけません。

（作業１日1.5ｈ）