Excel。合計値の数値をセル一つずつに分割して表示するには
＜CONCATNATE&LEFT&RIGHT&COLUMN&SUM関数＞
現場には様々な帳票がありまして、その中には、
なんでそんな風にしちゃったのかなぁ～という帳票もあるようでして、
例えば、次のような帳票がその一例ですね。
2行目の金額が、一つのセルにあるのではなくて、
セルごとに数値が入力されている状況なのです。
たぶん、かつて使っていた紙の帳票のレイアウトを基に同じように作ったので
このようなことになってしまったと推測はできますが、
変更しようとしても、帳票番号などを取っていると変更もままなりません。
そこで、今回は、3行目の消費税と4行目の合計を算出させて、
セルごとに表示する方法を考えていきます。
【セルごとに分かれていては何もできない】
2行目の金額ですが、このままでは、2・9・4・6・1という数値でしかないので、
一つの数値にしないと、消費税も算出することができません。
G列に一つの数値にするための数式を作っていきます。
G2をクリックして、CONCATNATE関数ダイアログボックスを表示しましょう。
文字1には、万の位のB2を入力します。
同じように、一の位まで入力してOKボタンをクリックします。
29461と算出されました。
数式は、
=CONCATENATE(B2,C2,D2,E2,F2)
”&”で結合させてもOKです。
ただ、今は、文字型の数字になっているので、
×1をして数値型に変更しますので、数式を修正します。
=CONCATENATE(B2,C2,D2,E2,F2)*1
次に、G3に消費税を算出します。
算出した結果をセルごとに分けて表示させていきます。
今回は、小数点は切り捨てにすることにしますので、
ROUNDDOWN関数を使っていきます。
数値には、G2*8%
桁数は、小数点は不要なので、0と入力します。
OKボタンをクリックします。
G3の数式は、
=ROUNDDOWN(G2*8%,0)
G4には、合計を算出させますので、SUM関数を使って算出させましょう。
G4の数式は、
=SUM(G2:G3)
今のところ、このようになっています。
あとは、算出した結果をセルごとに振り分けて
表示させてあげればいいわけですね。
【一文字ずつ振り分けるのがポイント】
3行目の消費税の万の位である、B3に数式を作っていきます。
最初に、LEFT関数ダイアログボックスを表示しましょう。
仮に、文字列に算出した数値のG3を入力して、
文字数はセルひとつに一つの数値ですから、1としてしまうと、
万の位にも関わらず、千の位の2が表示されてしまいますので、
アイディアが必要になります。
左から1文字を抽出したいけど、空白があるわけですね。
そこで、RIGHT関数を使って、右側から指定した文字数を抽出して、
その左側の数値を持ってくるようにしてあげればいいわけです。
指定した文字数が変われば、桁数をスライドさせることが出来ます。
わかりにくいので、一度、RIGHT関数を使って動きを確認しましょう。
文字列には、
" "&$G3
と入力します。
スペースを結合させているのは、位がないケースの対策です。
スペース一文字でいいの？と思われた方もいるかもしれませんが、
最終的に一番左の文字を抽出させたいわけですから、
先頭が空白ならば別に構いません。
G3を$G3と複合参照にしているのは、
オートフィルで数式をコピーするためですね。
文字数には、
7-COLUMN()
スライドさせたいので、右側から何文字抽出するのかを指示させるのですが、
このように数値が変動する場合には、数式を列方向にコピーをしますから、
COLUMN関数を使ってあげると便利です。
7は、逆算して求めた値です。
F列の一の位を抽出したいわけですから、
1を求めたいF列はCOLUMN()で6なので、7-6で1というわけですね。
では、OKボタンをクリックして、オートフィルで数式をコピーしましょう。
B3の数式は、
=RIGHT(" "&$G3,7-COLUMN())
です。
それでは、このRIGHT関数の考え方を踏まえて、
改めてLEFT関数から数式を作っていきます。
OKボタンをクリックして、オートフィルで数式をコピーしましょう。
B3の数式は、
=LEFT(RIGHT(" "&$G3,7-COLUMN()),1)
ですね。
算出はできたのですが、数値が文字型になってしまっています。
単純に右揃えにすればいいのですが、
数式でクリアしたい方は数式を修正する必要があります。
B3には、” “というスペース（空白）という文字が入っていますので、
数式に×1としてしまうと、エラーが発生するので、IF関数を使って修正します。
=IF(LEFT(RIGHT(" "&$G3,7-COLUMN()),1)=" ","",LEFT(RIGHT(" "&$G3,7-COLUMN()),1)*1)
これで完成しましたね。
このように、なかなか厄介なので、
本来ならこのような帳票は避けたいところですね。
