Excelで数字を1つずつ別々に分けるには？どんな関数でできる？

excelで請求書などの書類を作成する際、
金額などの数値を1ケタずつセルに配置しなければいけないことってありませんか？

 

先日、請求書のフォーマットをExcelで作成することになり、
いろいろ調べてみたので、備忘録的に残しておこうと思います。

 

同じことで悩んでいる人がいたら、ぜひ参考にしてみてくださいね。

　複数桁の数字を1桁ずつセルに分ける方法

上の図のようなフォーマットのエクセルに、金額を入力していこうとすると、
1つ数字を入力したら隣りに移動して、また1桁入力する・・・
というように1文字ずつ入れていかないといけないですよね。

 

これ、絶対にめんどくさいヤツです。。。

 

たまにしかやらないとか、入力するのが1行とか2行しかないという場合は、
コツコツそれで入力していってもいいかもしれません。

 

めんどくさいのは間違いないけど。。。

 

でも、時間のムダな感じはどうしてもありますよね～。

 

 

せっかくExcelには関数という効率的にできる方法があるんだから、
使った方が絶対にラクだなと思うわけです。

 

最初は、関数を入れて設定をする作業が必要なのでちょっと面倒ですが、
1回やっておけば次からは楽になると考えれば、
まぁ頑張ってやってみようかなと思えると思います。

 

 

エクセルで関数を使えば、例えば1つのセルの中に「123456」と普通に入力するだけで、

こんなふうに表示することができますよー♪

 

 

ではここから、Excel(エクセル)で金額など複数桁の数字を、1つずつセルにばらす方法を
説明していきたいと思います。

 

今回は、「請求書」のフォーマットを例にしていきます。

 

 

まずは、Excel(エクセル)の空いているところに、1文字ずつに分割したい数字をそのまま入力します。

 

エクセルの印刷範囲から外れている場所を使って、邪魔にならないようにしましょう。

 

 

次は、この入力した数字「123456」を位に分けて1つずつ分ける作業となります。

 

「123456」と金額などの数字を入力した場所の近くでいいので、
1の位、10の位、100の位、1000の位…と必要な分だけ「ケタ」を入力します。

 

 

ここまで準備できたら、いよいよエクセル関数を使っていきます。

 

セルを分かりやすくするため、
説明に必要ないフォーマット部分はいったん画像から消しますね。

 

最初は「INT」という関数を使います。

 

1の位に「=INT(L2/S1)」と入力します。

 

この関数の中の『L2/S1』という計算式で、
L2のセルに入っている「123456」をS1のセル「1」で割っています。

 

そしてこの計算式に、INTを付け加えると、
計算結果に小数点以下があった場合も切り捨てて整数に直してくれるんです。

 

で、今回の答え、

123456 ÷ 1 ＝ 123456 が、関数を入れたセルに表示されます。

 

 

ん～σ(￣、￣=)、1で割っているので若干分かりづらいですね。

 

10の位で再度見てみましょう。

 

「10」と入力したところ、つまり10の位にも同じように「=INT(L2/R1)」と入れてみます。

この計算式の答えは「12345」となりました。

 

L2／R1、つまり 123456 ÷ 10 ＝ 12345.6 が計算式の答えですね。

 

この「12345.6」という計算結果をINT関数が切り捨てて整数に直しているので、
小数点以下の6が切り捨てられて「12345」となっているんです。

 

同じように、100、1000、10000…に関数を入れていきましょう。

 

このようになりましたでしょうか？

では、次に請求書内の金額欄にあるセルに、1桁ずつ値が入るようにしていきます。

 

『エクセル内で、最終的に数字を桁ごとに分けてセルに表示させたい場所』

に関数を設定していきます。

 

 

ここでは、「IF」と「Right」という2つの関数を使っていきます。

 

エクセル内の、数字または金額の1桁目(1の位の値)を表示したいセルに、「=IF(S2=0,””,RIGHT(S2,1))」と入れます。

 

これで、「6」が入ったと思います。

 

S2にある「123456」の、右(Right)から1桁を表示するという関数になります。

 

IF文が追加されているのは、後ほど説明しますね。

 

同じように、10の位には「=IF(R2=0,””,RIGHT(R2,1))」、
100の位には「=IF(Q2=0,””,RIGHT(Q2,1))」・・・と関数を入れていきましょう。

 

この図のように出来上がりましたか？

 

おめでとうございます(ﾟ∇ﾟﾉﾉ”♪

 

 

 

では、なぜ「IF」という関数が使われているのかについても説明しておきますね。

 

例では「123456」でしたが、これが例えば「9876」だった場合、
IF文なしで「＝Right（・・・）」関数だけで作っていたとしたら、

このように、数字のない桁にも「0(ゼロ)」が入ってしまうんですね(>_<)

 

必ず最大の位(ケタ)まで数字があるなら問題ないですが、
そんなことあり得ないですよね、たぶん。

 

なので、「IF」を使って、「もし0だったら何もしない」という条件を
Right関数に付け加えているんです。

 

何となくお分かりいただけました？？

 

あとは、この関数を下にバァーッとコピーしたら完成です！

 

 

 

え？できてない？？

 

できていない人は、
たぶん下の画像のような感じで「#DIV/0!」と表示されてしまっているんじゃないでしょうか。

もしかしたらコピーしたときに、
計算式に使うセルが1個ずつずれてしまったからかもしれません。

 

セルの列や行を数式の中で固定させたい時には、

固定させたい部分の前に「＄」を付けると固定できます。

 

 

どういうことかというと、

本当は、ずっと「O1」を使った数式(関数)でいて欲しいのに、
コピーしたことで計算式に使われるセルが1つずつ下にずれてしまい、「O2」になっています。

 

このズレが起きないようにするのが「＄」の役目！

 

この場合だと、「=INT(M3/$O$2)」としておけば、
Oと2が固定され、関数を横や縦にコピーしてもズレなくなります。

 

同じように、他の桁の関数にも＄を入れて固定させていきます。

 

全部のケタの関数を固定させたら、また下へバァーッとコピーしちゃいましょう。

 

これで完成したのではないでしょうか♪

 

お疲れ様でした(^^)

 

　複数桁の数字を1桁ずつにばらす まとめ

こういった作業って、
やりだすまではとっても面倒な気がするし難しそうだなと思っちゃいますが、
たった一度、ほんの少し頑張ってみるだけで、次からはグッと楽になることっていっぱいありますよね。

 

こうしたいけど、どうやってやるんだろう？

これできたら楽なのにな～という方のお役に立てたら嬉しいです。