エクセル・スプレッドシートで、一定数を超えたらゼロから再累計する方法

累計が1000を超えると、またゼロからカウントするという方法をExcelあるいはGoogleスプレッドシートで実行したいのですが、わからなくて困っております…。

条件は以下の通りです。（※画像参照）
・Ａ列数値の累計をＢ列に反映したい
・ただし、数値反映は、累計が1000を超えた場合のみで良い
・また、累計が1000を超えた場合、次の行でまたゼロから累計をしてほしい
・（Googleスプレッドシートの場合）Arrayformula関数を利用して、全列にその計算式が反映されるようにしたい
・（Excelの場合）スピル機能を利用して、上記同様のことを実行したい

VBAでも関数でも良いのですが
もしVBAのコードの場合、VBAはほぼ理解していないので、分かりやすく教えていただけますと大変助かります…！

もしどなたかお分かりになる方がいらっしゃればご教示お願いします。

質問者からの補足コメント

• 

  オートフィル機能で、関数を全セルにコピペするのではなく、
  一つのセルに関数を入れたら、自動的に下のセルに関数を繰り返し反映してくれる方法を知らないでしょうか。
  ご教示いただいた関数だとarrayformula関数では対応できず…。

    補足日時：2022/05/29 13:58

• ご回答ありがとうございます。ただ、手動で関数を下のセルにコピペしなくても済む方法はありますでしょうか。新たに行を挿入したときに関数をコピペする作業を減らしたく。スプレッドシート上のarrayformula関数で対応できる式はありますでしょうか

  No.3の回答に寄せられた補足コメントです。 補足日時：2022/05/29 14:03

• ご回答ありがとうございます。ただVBAにあまり精通しておらず、ご教示いただいたコード情報のみでは実行することが出来ませんでした…。もし具体的なコードをご教示いただけれるのであれば大変助かります。

  No.5の回答に寄せられた補足コメントです。 補足日時：2022/05/30 05:39

No.7 ベストアンサー 回答者： Belltreeriver 回答日時： 2022/05/29 23:20

№3です。

ご希望に沿っているのか？ですが

①表をテーブルとして書式設定する。
②B2セルに　=IF(SUM(OFFSET($A$1,,,ROW()))-SUM(OFFSET($B$1,,,ROW()-1))>=1000,SUM(OFFSET($A$1,,,ROW()))-SUM(OFFSET($B$1,,,ROW()-1)),"")
と入力する。

テーブル化しておけばA列に追加や、行の挿入・削除してもB列の数式は自動で対応されます。②の数式は　IF(SUM($A$1:A2)-SUM($B$1:B1)>=1000,SUM($A$1:A2)-SUM($B$1:B1),"")のセル領域を可変対応したもので、同一です。可変対応でないとテーブル化した時、数式が正しく認識されないためです。

この回答へのお礼

ありがとうございます！スプレッドシートにはテーブル機能がないので、あいにく実行することはできませんでしたが、エクセルでは対応することができました。スプレッドシートでの対応方法は引き続き模索してみます。

お礼日時：2022/05/30 05:35

No.8 回答者： hippo92 回答日時： 2022/05/30 05:45

上の理屈を書ききっている頭があれば、

プログラムなんてすぐ覚えられると思うので、ご自身で勉強された方が後々も考えて無難ではないかと思います。

No.6 回答者： mike_g 回答日時： 2022/05/29 16:29

》 一つのセルに関数を入れたら、自動的に下のセルに関数を

》 繰り返し反映してくれる方法を知らないでしょうか
はい、知りません。私の Excel は今や古典的な Excel 2019 なので、無理なのではなかろうかと。

No.5 回答者： hippo92 回答日時： 2022/05/29 15:50

VBAで作るのが簡単と思います。

変数asumとかに加算していって
if asum >= 1000 then asum = 0
でできると思います。

No.4 回答者： mike_g 回答日時： 2022/05/29 06:56

式 =IF(B1<1000,SUM(B1,A2),A2)

を入力し、かつ、[条件付き書式]でその戻り値が1000未満の場合はフォント色を白に設定したセル B2 を下方にズズーッとオートフィル
オ・シ・マ・イ

「VBAはほぼ理解していない」は、関数の場合は無駄な解説不要と理解しました、ハイ。

No.3 回答者： Belltreeriver 回答日時： 2022/05/28 05:27

B2セルに次の式を入れ、下へCOPYする

=IF(SUM($A$1:A2)-SUM($B$1:B1)>=1000,SUM($A$1:A2)-SUM($B$1:B1),"")

No.2 回答者： hippo92 回答日時： 2022/05/28 04:20

すみません、4桁じゃなくて３桁でした。

No.1 回答者： hippo92 回答日時： 2022/05/28 04:19

right関数で４桁とってVal関数で囲んだらできそうな気がします。

Valはいらないかもです。