Excel。セルごとに分割された数値を合算し、その値もセルごとに分割したい
＜CONCAT・SUM・IFERROR・MID・LEN・COUNT関数＞
なんでそんなことしちゃったのという、Excelの帳票をみることがあります。
例えば、数値をセルごとに分割してある帳票。
そもそも、1500と分割しないでセル内にあれば、単純な和算で済むわけですが、セルごとに分割してあるわけですね。
このような場合、一の位で合算して、繰り上がったら…なんてやっていたら、桁数が増えたら大変というか、数式を考えるだけでも大変です。
最初にやることは、結合して1つのセルにまとめて数値にします。
A6に次の数式を作り、交通費を一つのセルにまとめます。
A6に設定した数式は、
=CONCAT(B2:G2)
同じように、A7に、宿泊費を一つのセルにまとめます。
A8には、合算値を算出したいので、A8に設定する数式は、
=SUM(A6:A7)
ところが、「0」と算出されてしまいました。
原因は、よくみると、A6とA7は、左揃えになっています。
これは文字型になっていることを表しています。
CONCAT関数は、文字結合をする関数なので、算出されたものは、文字になっていて数値ではないわけです。
そこで、A6とA7の数式のうしろに「*1」を追加します。
=CONCAT(D2:G2)*1
こうすると、文字型は文字型でも、数値文字型に変わるので、合算値を算出することができます。
あとは、どうやって、一つずつのセルに分割して表示させる数式を考えていきます。
合計のB4の数式は、
=IFERROR(MID($A$8,LEN($A$8)-COUNT(C1:$G$1),1)*1,"")
あとは、この数式をG4まで、オートフィルで数式をコピーすれば完成です。
数式が長くなったので、説明していきます。
B1:G1までの見出し行は、「十万・一万…一」と表示してますが、元は、普通の数値が入力してあって、表示形式をつかって、漢数字にしています。
別に直接、漢数字で入力しても問題はありません。
最初の「IFERROR関数」は、数値がない場合、#VALUEというエラーが発生するので、エラーならば空白にするようにしています。
MIDはセル内の文字列を指定の場所にある、指定した文字数分だけ抽出する関数です。
先程、合算値を算出したA8を指定します。
オートフィルで数式をコピーしますので、絶対参照を忘れずに設定します。
何文字目の文字なのかを指定します。
「LEN($A$8)-COUNT(C1:$G$1)」
LEN関数は、文字数を算出する関数です。これで合算値が何桁なのかを算出します。
その値から、C1:G1の数値の個数を算出した値を減算します。
桁数をずらす必要があるので、終点のみを絶対参照に設定しておきます。
今回の例としては、
LEN($A$8)は、「5」
COUNT(C1:$G$1)は、「5」
5-5=0なので、0文字目を抽出ということになるのですが、0文字目を抽出というのは、おかしいので、エラーが表示されるわけですね。
このように、ちょっと複雑な数式になっていますね。
帳票上の必要があるかもしれませんが、基本的には、数値を一つずつ、一つのセルごとに分割する場合には、アレコレ、アイディアが必要になりますね。
