Excel 数値の累積が規定値を超えたらカウントし、規定値の余りから再び累積

在庫管理の為の表を作成しているのですが、にっちもさっちもいかなくなってしまったため、ここで質問させていただきます。
Excel2016です。画像を添付しましたので、参考にして説明させていただきます。
見づらかったら申し訳ありません。

4～6行に数値が入力してあります。この数値をC4＋C5＋C6＋D4＋D5＋D6＋E4…としていき、累積が規定値（画像の場合は100）を超えた、もしくはちょうどの場合に、超えた列の7行目のセルに規定値を入力し、100を超えた余り（画像では21）を8行目に記入します。
100をカウントしたら、以降は余りを起点として再び各列の4,5,6行を累積、これを延々と繰り返すという作業を行いたいと思います。
これが「メッシュ　60H」のほかに30種類程度あり、ものによって規定値を変更します。

合計値がちょうど規定値になることは稀だと思いますので、規定値を超過した余りを別のセル入力する形にしたいです。
こんなことが可能かどうかも不明の為、ご教示頂ければと思います。

よろしくお願い致します。

No.2 ベストアンサー 回答者： tom04 回答日時： 2020/05/13 14:14

No.1です。

＞C4~C6の合計値が108の場合、108のまま入力されてしまう

結局画像通り、100を超えた列に「100」を表示させればよいのですかね？
その辺も画像ではよく判りません。
部分的で良いので拡大したアフターの画像があれば具体的なアドバイスができるのですが・・・

とりあえず、C7セルを
=IF(SUM($C4:C6)-SUM($B7:B7)>=100,100,"")

C8セルを
=IF(C7="","",MOD(SUM($C4:C6),100))

にしてみてください。m(_ _)m

この回答へのお礼

ご返答ありがとうございます！
画像の件はご迷惑おかけして申し訳ありません・・・

思った通りの挙動でとても満足しております。
助かりました！

お礼日時：2020/05/13 14:57

No.3 回答者： fujillin 回答日時： 2020/05/13 14:23

こんにちは

＞ものによって規定値を変更します。
とのことなので、規定値も変更できるようにしてみました。

とりあえずA1セルんに規定値が設定されているものとします。
（ご質問の例の場合で言えば、A1セルに100が入力されているものとします）
C7セルに
　=IF(SUM($C$4:C6)-SUM($B$7:B7)<$A$1,"",$A$1)
C8セルに
　=IF(C7="","",MOD(SUM($C$4:C6),$A$1))
の式を入力して、右方へフィルコピーでいかがでしょうか？

Ａ１セルの位置を変えたい場合は、式中の「$A$1」の部分をそのセルのアドレスに変えてください。

この回答へのお礼

こんにちは。
ご回答ありがとうございます。

希望通りの挙動になり、とても感激しております！
見にくい画像でご対応頂いた上、規定値の設定まで盛り込まれており、笑いが止まりません。

本当にありがとうございました！

お礼日時：2020/05/13 14:59

No.1 回答者： tom04 回答日時： 2020/05/13 12:20

こんにちは！

画像が小さすぎて詳細がよく判らないのですが、おそらくこういうコトだろうとして・・・

画像の配置だとC7セルに
=IF(SUM($C4:C6)-SUM($B7:B7)>=100,SUM($C4:C6)-SUM($B7:B7),"")

C8セルに
=IF(C7="","",MOD(C7,100))

という数式を入れ、フィルハンドルで右へコピーしてみてください。

※　的外れならごめんなさい。m(_ _)m

この回答へのお礼

ご回答ありがとうございます。
挙動は要望の通りで感激しております！

1点、C7に入力される数字が100を超えてしまう（C4~C6の合計値が108の場合、108のまま入力されてしまう）のですが、関数のどこをどう直せばいいでしょうか？
再度ご教示頂きたいです。

お礼日時：2020/05/13 13:35