Excel累積が規定数を超えたらまたゼロから累積

質問

言葉でうまく説明できないゆえ検索もままならず，ここで質問させていただきます。お願いいたします。
Excel2007です。理想図を添付しましたので，それに従い説明いたします。

Ａ列に数値が入っています。1000行ほどあります。
それらの数値を上から順にA1＋A2＋…と加算していき，500になったらその行のＢ列にその合計を記入し，一旦ゼロにリセットし次のＡ列の行（添付の図ではA6）からまた加算を始め，500になったらその行のＢ列に合計を記入し…ということを，Ａ列の数値が終わるまで繰り返す，という作業を行いたいと思います。
また，合計数がちょうど500になることはまれなので，近似のセルをとるようにします。例えば添付画像において，A５の段階での合計は480で，A６になると635ですので，A５のほうが500に近いためそのセルで区切ります。

関数でもマクロでもかまいません。
何かしらのヒントでもいただければと存じます。
よろしくお願いいたします。

回答No.2

tom04 · ベストアンサー

こんばんは！

VBAでの一例です。
データは1行目からあるとします。

Sub Sample1()
Dim i As Long, myVal1, myVal2
Range("B:B").ClearContents
For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
myVal1 = myVal1 + Cells(i, "A")
myVal2 = myVal1 + Cells(i + 1, "A")
If myVal1 <= 500 And myVal2 > 500 Then
If 500 - myVal1 <= myVal2 - 500 Then
Cells(i, "B") = myVal1
Else
Cells(i + 1, "B") = myVal2
i = i + 1
End If
myVal1 = 0
End If
Next i
End Sub

こんな感じではどうでしょうか？

※　データが2行目以降にある場合は
＞For i = 1 To Cells(Rows.Count, "A").End(xlUp).Row
の「1」の部分を「2」に変更してみてください。m(_ _)m

お礼

ご回答，ありがとうございました。
きちんと動作し，結果につきましても最初の方と同じになりました。
VBAにつきましては，その都度調べながら調整している程度の知識しか持ち合わせませんので，今回のコードも大変参考になりました。ありがとうございました。

No1の回答者kagakusuki様も，質問後すぐに回答くださり，かつ正常に機能いたしました。どちらの方にも平等にお礼をしたいところでございますが，VBAによる操作のほうが，即座に実行が完了し，かつ操作後のファイルの軽さにも影響がでませんでしたので，tom04様の回答をベストアンサーに選びたいと思います。

回答No.1

kagakusuki

まず、A1セルから数値を入れるのではなく、

A1セル　　項目名
A2セル　　135
A3セル　　85
A4セル　　85
A5セル　　30
A6セル　　135
A7セル　　155

の様に、A2セルから数値を入れる様にして下さい。
　その上で、まず、A2セルに次の関数を入力して下さい。

=IF(ISNUMBER($A2),IF(OR(SUM(INDEX($A$1:$A2,IF(COUNT(B$1:B1),MATCH(9E+307,B$1:B1)+1,1)):$A3)>500,SUM(INDEX($A$1:$A2,IF(COUNT(B$1:B1),MATCH(9E+307,B$1:B1)+1,1)):$A2)=500),IF(ABS(SUM(INDEX($A$1:$A2,IF(COUNT(B$1:B1),MATCH(9E+307,B$1:B1)+1,1)):$A2)-500)>SUM(INDEX($A$1:$A2,IF(COUNT(B$1:B1),MATCH(9E+307,B$1:B1)+1,1)):$A3)-500,"",SUM(INDEX($A$1:$A2,IF(COUNT(B$1:B1),MATCH(9E+307,B$1:B1)+1,1)):$A2)),""),"")

　そして、A2セルをコピーして、A3以下に貼り付けて下さい。

お礼

早々にありがとうございました。
思うとおりに機能して満足しております。大変助かりました。

（関数を入力するセルは，Ａ２ではなくＢ２で，以降もＢ列に関数を入れてＡ列の数値の処理をしていけばいいのですね。）