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

質問

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

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

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

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

回答No.11

tom04 · ベストアンサー

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

お礼

出来ました！！！

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

回答No.12

kagakusuki

回答番号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等）に貼り付けて下さい。

お礼

出来ました！

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

回答No.10

gyouda1114

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

お礼

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

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

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

回答No.9

gyouda1114

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

お礼

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

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

ありがとうございます。

回答No.8

kagakusuki

何故、【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),"")

お礼

ありがとうございます。

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

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

お忙しい中すみません。

補足

その通りでした。

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

大変失礼いたしました。

回答No.7

noname#204879

添付図参照

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 をズズーッと右方にドラッグ＆ペースト

お礼

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

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

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

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

補足

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

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

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

回答No.6

tom04

こんばんは！
一例です。
A1セル12桁まで対応できます。

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

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

お礼

ありがとうございます。

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

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

補足

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

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

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

回答No.5

keithin

ご相談のヤリタイ事でちょっとやっかいなのは，たとえば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】
に分割でした。

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

回答No.4

gyouda1114

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

お礼

ありがとうございます。

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

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

補足

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

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

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

回答No.3

m_and_dmp

やったことはありませんが、
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が先頭だといなくなってしまいます。

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

回答No.2

某HN クロメート(Chromate)（@CoalTar）

正の整数限定で
A2セル =IF(LEN(A1)<=9,"",RIGHT(INT(A1/10^9),3))
A3セル =IF(LEN(A1)<=6,"",RIGHT(INT(A1/10^6),3))
A4セル =IF(LEN(A1)<=3,"",RIGHT(INT(A1/10^3),3))
A5セル =RIGHT(A1,3)
(A2:A5セルを右へオートフィル)

お礼

ありがとうございます！

イメージに近い感じでできましたが、分割したセルの先頭に0がくる数字だと、
表示されなくなってしまいます。

何か対処法はないでしょうか？

補足

すみません、No.3様へのお礼と間違えてお礼してしまいました。

こちらの方法でしたら先頭桁に0が来ても大丈夫ですね。

図説までいただきありがとうございます！！
かなり理想に近いと思います。