【Excel】数字を三桁毎に区切ってセルに振分る

【A1】1,146,900,000
↓
【A2】【A3】【A4】【A5】
　 　1　146　900　000

のように、売上金額を通常入力したものを4つのセルに振り分けたいのですが、
どのような関数を使えばよいでしょうか。

MIDなども使ってみたのですが
桁数が決まっていないので私のレベルでは手に負えませんでした・・。

どなたかご教示いただけないでしょうか。
よろしくお願い致します。

No.11 ベストアンサー 回答者： tom04 回答日時： 2011/09/27 11:18

No.6です。

＞【A2】
【A3】
【A4】
【A5】に分割ではなく
【B1】【C1】【D1】【E1】
に分割でした。

だとすると、前回の数式を利用してB1セルに
=MOD(INT($A$1/(10^(3*(4-COLUMN(A1))))),1000)
という数式を入れ、E1セルまでオートフィルでコピーしてみてください。

次に
＞上記事情でできる範囲でアレンジしてみたのですが、#NUM!エラーになってしまいました。
＞更に、ユーザー定義の中に000を見つけられませんでした

に関してですが、今回はB1セルは何もしないで
C1～E1セルを範囲指定　→　右クリック　→　セルの書式設定　→　ユーザー定義　→
「G/標準」となっている欄に、「G/標準」を消し、ご自身で
000　と入力します。
これでC1～E1セルが必ず3桁で表示されます。

こんなんではどうでしょうか？m（＿ ＿）m

この回答へのお礼

出来ました！！！

素早いご回答ありがとうございます！！

お礼日時：2011/09/27 11:45

No.12 回答者： kagakusuki 回答日時： 2011/09/27 11:25

　回答番号ANo.8です。

>【A2】
>【A3】
>【A4】
>【A5】に分割ではなく

>【B1】【C1】【D1】【E1】
>に分割でした。


　それでしたら次の様な関数となります。

=IF(ISNUMBER(1/$A1+1/($A1=INT($A1))),IF(COLUMNS($B:B)>INT(LOG10($A1)/3)+1,"",MOD(INT($A1/10^(FLOOR(LOG10($A1),3)-(COLUMNS($B:B)-1)*3)),1000)),"")

或いは

=IF(ISNUMBER(1/$A1+1/($A1=INT($A1))),IF(COLUMNS($B:B)>ROUNDUP(LEN($A1)/3,0),"",RIGHT(LEFT(TEXT($A1,"#,##0"),FIND("@",SUBSTITUTE(TEXT($A1,"#,##0")&",",",","@",COLUMNS($B:B)))-1),3)+0),"")

　上記のどちらかの関数をB1セルに入力してから、B1セルをコピーして、C1セルに貼り付けて下さい。
　次に、C1セルの書式設定の表示形式を、[ユーザー定義]の

000

として下さい。
　その上で、C1セルをコピーして、C1セルよりも右側にあるセル（D1、E1等）に貼り付けて下さい。

この回答へのお礼

出来ました！

大変丁寧な解説ありがとうございます！

お礼日時：2011/09/27 11:49

No.10 回答者： gyouda1114 回答日時： 2011/09/27 05:02

No.4、No.9です。

頭に00が付いてしまいますのでA2の表示形式は、標準として下さい。
　

この回答へのお礼

分割後、セルの頭にくる0を、

元の数字の頭である場合は非表示
元の数字の中にあるものの場合は表示

としたい場合、都度表示形式を操作するしかないのでしょうか。
計算式って難しいですね・・・・

お礼日時：2011/09/27 11:22

No.9 回答者： gyouda1114 回答日時： 2011/09/27 04:57

No.4です。

頭に0がつくと表示されなくなります。
A2～A5すべての表示形式をユーザー定義の種類を000
にして下さい。

この回答へのお礼

再度のご回答ありがとうございます。

非表示になった原因はわかりましたが、
ユーザー定義に000が見つけられません。
0.00とかならあるのですが・・

ありがとうございます。

お礼日時：2011/09/27 11:17

No.8 回答者： kagakusuki 回答日時： 2011/09/27 01:43

　何故、【B1】【C1】【D1】【E1】ではなく、【A2】【A3】【A4】【A5】なのかは解りませんが、A2セルに次の関数を入力してから、A3以下に貼り付けると良いと思います。

=IF(ISNUMBER(1/A$1+1/(A$1=INT(A$1))),IF(ROWS($2:2)>INT(LOG10(A$1)/3)+1,"",MOD(INT(A$1/10^(FLOOR(LOG10(A$1),3)-(ROWS($2:2)-1)*3)),1000)),"")

或いは

=IF(ISNUMBER(1/A$1+1/(A$1=INT(A$1))),IF(ROWS($2:2)>ROUNDUP(LEN(A$1)/3,0),"",RIGHT(LEFT(TEXT(A$1,"#,##0"),FIND("@",SUBSTITUTE(TEXT(A$1,"#,##0")&",",",","@",ROWS($2:2)))-1),3)+0),"")

この回答への補足

その通りでした。

【B1】【C1】【D1】【E1】
に分割の間違いだったんです。

大変失礼いたしました。

補足日時：2011/09/27 11:17

この回答へのお礼

ありがとうございます。

トライしてみたのですが($2:2）をどのようにアレンジしたらよいかわからず、
未完成状態で力尽きてしまいました。

【B1】【C1】【D1】【E1】
にする場合はどのようにしたらよろしいでしょうか？

お忙しい中すみません。

お礼日時：2011/09/27 11:17

No.7 回答者： mike_g 回答日時： 2011/09/26 23:27

添付図参照

Sheet2 において、
A1: =TEXT(Sheet1!A1,"#,##0")
A2: =IF(ROW()<LEN(A$1)-LEN(SUBSTITUTE(A$1,",",""))+2,SUBSTITUTE(A1,Sheet1!A2&",",""),"")
セル A2 をズズーッと下方に（例えばセル A10 まで）ドラッグ＆ペースト
範囲 A1:A10 をズズーッと右方にドラッグ＆ペースト

Sheet1 において、
セル A2 に次式を入力して、此れをズズーッと下方に（例えばセル A10 まで）ドラッグ＆ペースト
=IF(ISERROR(FIND(",",Sheet2!A1)),Sheet2!A1,LEFT(Sheet2!A1,FIND(",",Sheet2!A1)-1))
範囲 A2:A10 をズズーッと右方にドラッグ＆ペースト

この回答への補足

【A2】
【A3】
【A4】
【A5】に分割ではなく

【B1】【C1】【D1】【E1】
に分割でした。

これにより計算方法も変わるのでしょうか？
すみませんでした。

補足日時：2011/09/27 11:05

この回答へのお礼

2パターンも図説付きでありがとうございます。

ただ、上記事情のためなのか、うまくいきませんでした。。。

計算式を見ても私の知識ではどういう計算なのか解らず、アレンジしきれません。
勉強不足で申し訳ありません。

貴重なお時間をどうもありがとうございました。

お礼日時：2011/09/27 11:09

No.6 回答者： tom04 回答日時： 2011/09/26 20:10

こんばんは！

一例です。
A1セル12桁まで対応できます。

A2セルの表示形式はユーザー定義から　000　としておきます。

A2セルに
=MOD(INT($A$1/(10^(3*(4-ROW(A1))))),1000)
という数式を入れA5セルまでオートフィルでコピーではどうでしょうか？m（＿ ＿）m

この回答への補足

【A2】
【A3】
【A4】
【A5】に分割ではなく

【B1】【C1】【D1】【E1】
に分割でした。

これにより計算方法も変わるのでしょうか？
すみませんでした。

補足日時：2011/09/27 11:01

この回答へのお礼

ありがとうございます。

上記事情でできる範囲でアレンジしてみたのですが、#NUM!エラーになってしまいました。
更に、ユーザー定義の中に000を見つけられませんでした。

せっかく回答くださったのにすみません。
どうもありがとうございました。

お礼日時：2011/09/27 11:04

No.5 回答者： keithin 回答日時： 2011/09/26 19:18

ご相談のヤリタイ事でちょっとやっかいなのは，たとえばA1に

1,023,400,050
のように数字が有った場合，２カタマリ目は「０２３」のような先頭のゼロが必要ですが，
　23,400,050
のような数字では「２３」のように先頭のゼロを無くしたい，

そういう操作を「数値の書式設定では原則として出来ない」点です。
（参考：Excel2007以降では，条件付き書式を使って出来ます）


というワケで，結果の数字を「文字列」で表示させてみます。
A1に元の数字として
B1に
=MID(TEXT($A1,"????????????"),(COLUMN(A1)-1)*3+1,3)
のように記入し，E1までコピーして貼り付ける。

B1:E1はセルの配置を「右寄せ」にする。

この回答への補足

【A2】
【A3】
【A4】
【A5】に分割ではなく

【B1】【C1】【D1】【E1】
に分割でした。

これにより計算方法も変わるのでしょうか？
すみませんでした。

補足日時：2011/09/27 10:53

この回答へのお礼

そうなんです！

そうなんですが、できる範囲で上記事情を踏まえアレンジしてみたのですが、
すべてブランク表示になってしましました。

勉強不足ですみません。
貴重なお時間をどうもありがとうございました。

お礼日時：2011/09/27 11:01

No.4 回答者： gyouda1114 回答日時： 2011/09/26 19:02

A2に　=IF(A1>=1000000000,INT(A1/1000000000),"")

A3に　=IF(A1>=1000000000,INT(MOD(A1,1000000000)/1000000),"")
A4に　=IF(A1>=1000000,INT(MOD(A1,1000000)/1000),"")
A5に　=IF(A1>=1000,INT(MOD(A1,1000)),"")


A5の表示形式を　ユーザー定義　000

数値の社員コードに自動で前ゼロをつける
http://kokodane.com/tec49.htm

この回答への補足

【A2】
【A3】
【A4】
【A5】に分割ではなく

【B1】【C1】【D1】【E1】
に分割でした。

これにより計算方法も変わるのでしょうか？
すみませんでした。

補足日時：2011/09/27 10:52

この回答へのお礼

ありがとうございます。

上記事情もあり、解る範囲で書き変えつつやってみたのですが、
【B1】【C1】が非表示状態です。

勉強不足ですみません。
どうもありがとうございます。

お礼日時：2011/09/27 10:52

No.3 回答者： m_and_dmp 回答日時： 2011/09/26 18:55

やったことはありませんが、

A5は、＝Right（A1,３）
A４は、=Rounddown(Right(A1,6)/1000,0)
A3は、=Ronddown(Right(A1,9)/1000000,0)
A2は、=Rounddown(Right(A1,12)/1000000000,0)

INT関数なら
A4は、=Int(Right(A1,6)/1000)

今エクセルを開けない状況なのでINT関数の方はこれでよかったかどうか自信がありませんので調べてください。

この回答へのお礼

ありがとうございます。

分割したセルの先頭に0がくる数字だと、 表示されなくなってしまいます。
ちなみにINTでもやってみまして、
計算式は大丈夫でしたがやはり0が先頭だといなくなってしまいます。

わがままな要求ですみません。。
貴重なお時間ありがとうございました。

お礼日時：2011/09/27 10:48