1桁ずつセルごとに振り分けられた数字を合計する方法

質問

excelで請求書を書いているのですが、

添付の画像は表紙になるシートで、別のシートで現場ごとに計算した金額がこのシートの
金額の欄でセルごとに1桁ずつ振り分けられるようになっています。
それで一番下の合計の欄にその6～20までの数字の合計が出るようにしたいと
思っているのですが、そのような計算式があれば教えてください。

どうか宜しくお願いします。

回答No.9

Chiquilin · ベストアンサー

結局 枠外に数値を表示させるのが何故難しいのかがよく分からん
のですが……

■X21
=TRIM(LEFT(RIGHT(" "&SUMPRODUCT((0&$X$6:$AF$20)*10^{8,7,6,5,4,3,2,1,0}),10-COLUMN(A21))))
右方向にコピー

こんな計算をする方が どう考えても無駄でしょうに。

お礼

セルに数値を入力したら算出できました。
どうもありがとうございます。
一応印刷枠外で計算してから入力していたのですが、
印刷の時に頻繁に消すのを忘れてそのまま印刷してしまっていたため、
何とかそのまま枠の中で計算できないかと投稿に至った次第です。
ズボラな為の注文に付き合っていただいてありがとうございました。
とても助かりました。

回答No.8

tom04

No.7です。
たびたびごめんなさい。

前回の回答は無視してください。

集計行が15行あるというコトは2桁繰り上がる場合がありますので、
お望み通りの結果にならないと思います。

もう一度じっくり考えてみます。
どうも失礼しました。m(_ _)m

お礼

ご丁寧な回答ありがとうございます。

何とか模索してみます。

回答No.7

tom04

こんにちは！
オーソドックスに繰り上げをやってみました。

↓の画像でX21セルに
=IF(OR(COUNT(X6:X20),INT(SUM(Y6:Y20)/10)>0),MOD(SUM(X6:X20),10)+INT(SUM(Y6:Y20)/10),"")
という数式を入れ、AE21セルまでフィルハンドルでコピー！
AF21セルには
=IF(COUNT(AF6:AF20),MOD(SUM(AF6:AF20),10),"")
という数式を入れています。m(_ _)m

回答No.6

MackyNo1

先の回答で合計値を計算する数式を提示しましたが、ご希望の操作が、合計値をそのまま表示するのではなく、一桁ごとにセルに分割して表示したいということであれば、X21セルに以下のような数式を入力して右方向にオートフィルすることになります。

=MID(TEXT(SUMPRODUCT($Z$6:$AF$20*10^(7-COLUMN($A:$G))),"????????0"),COLUMN(A:A),1)

お礼

教えて頂いた数式を入力したのですがVALUE！という文字が出てきてうまく出ませんでした。

Z6～AF20までのセルに他のシートで入力したセルの数値がこちらのセルに出るように=乙様式!I5という数値を入力していたのでそれらを消してもう一度教えてもらった数式を入力したところうまく合計は出てきました。

Z6～AF20のセルに数値を入力していたので上手くできなかったのでしょうか？

ちなみにもう一つのシートは数量と単価を1ずつのセルに入力すると桁ごとにセルに振り分けられた数字が
出てくるようにこのような数式を入力しています。
=MID(TEXT($Q15*$T15,"?????????????????????????"),COLUMN(Y15),1)
これを横にオートフィルし、10行くらいあるので同じように入力しています。

その合計をこのような式で表しています。
=MID(TEXT(SUMPRODUCT($Q15:$Q25,$T15:$T25)*1.08,"?????????????????????????"),COLUMN(R25),1)
この数字が画像で載せた表紙に出てくるようになっています。

このような数式が邪魔をしてしまい、教えて頂いた数式がうまく働かなくなってしまうのでしょうか？

回答No.5

MackyNo1

合計値を1つの式で表示するなら以下のような関数になります。

=SUMPRODUCT(Z6:AF20*10^(7-COLUMN(A:G)))

お礼

合計のセルに入力してみたのですがVALUE！という文字が出てきて
うまくいきませんでした。
X21に入れてオートフィルするのでしょうか？

回答No.4

aokii

AG6セルに、以下の式を記入してAG20セルまでドラッグコピー。
=(X6&Y6&Z6&AA6&AB6&AC6&AD6&AE6&AF6)*1

AG21セルに、以下の式を記入。
=SUM(AG6:AG20)

X21セルに、以下の式を記入してAF21セルまでドラッグコピー。
=MID($AG$21,COLUMN(X21)-COLUMN($W21),1)

お礼

教えてもらった通りにやってみたのですが、＃VALUE！という字が出てきて
うまくいきませんでした。
すでに入力している数式などをけしてみてもうまくいきません。
何が原因なのでしょうか？

回答No.3

ryo_ Deathscythe（@Deathscythe）

何で1桁ずつセルを変える必要があるの?
出来るけど繰り上げ算の関数まで書かなきゃいけないから、
超めんどくさい上に文字が増えるから重くなります。

ひとつのセルで1つの金額書けば
SUM関数で6～20の範囲指定して終わりだけど？

見た感じだと「セルのサイズが変更できる事を知らない?」とかおもっちゃいましたが。

お礼

すいません、取引先の指定の請求用紙なので枠がいじれなくて
このような方法で行っています。
多くの取引先の仕様の請求書ようしなどがこのような形で
いつも枚数なども多く、一つ一つ入力していくとかなり時間が
かかってしまい何とか自動で出来ないか色々模索している状態
という理由です。

回答No.2

Chiquilin

> 別のシートで現場ごとに計算した金額
これを印刷枠外に表示させといて それを１桁ずつに分ける仕様に変えたら
どうですか？　それなら合計も簡単に計算できますよね？

今のままでも計算できなくはないですが 表の悪さを計算でカバーするとい
うのは 上手いやり方ではないなと思います。

お礼

すいません、取引先の指定の請求用紙なので枠がいじれなくて
このような方法で行っています。
多くの取引先の仕様の請求書ようしなどがこのような形で
いつも枚数なども多く、一つ一つ入力していくとかなり時間が
かかってしまい何とか自動で出来ないか色々模索している状態
という理由です。

回答No.1

kuma56

方法はいろいろあると思いますが、自分では何か考えてやってみましたか？
その場合、どういうやり方をして、どこがうまくいかなかったとかありませんか？？
たとえば、合計欄のセルは一つに連結させて表示させ、一の位はそのまま合計させ、十の位はそのまま合計したものを10倍させて、百の位はそのまま合計させたものを百倍させて・・・・それぞれの答えを足してやればできそうだけど・・・・？？？
計算式を作れって事なのかなぁ・・・・・？？？

お礼

おっしゃる通りです。
数式などはほぼ無知の状態で教えてgooなどで教えてもらいながら
やっている状態です。
色々模索しながらやっていますが、数式の文字などは
何を意味しているのか解っていないのが現状です。
勉強します。