[[20160208171455]]　『ある値に合計が達した日付を返す』（あや）　ページの最後に飛ぶ

『ある値に合計が達した日付を返す』（あや）

A　　　B
2/1 100
2/9 150
3/5 10
4/8 500
4/15 200

Ａ列に日付Ｂ列にランダムな数字があります。
Ｂ列の累計が200を超えたとき（200単位というのでしょうか？）の日付を返したいのですが。

例えば上記の表で行くと、100+150=250で200を超えるので、2/9を返す。
次は400を超えたときの日付を返したいので、100+150+10+500=760で4/8を返す。
次は600なので4/8を返すということなのですが。

宜しくお願いいたします。

＜使用 Excel：Excel2013、使用 OS：Windows7 ＞

Sub main()

    Dim ctr As Long, strctr As Long, cl As Range
    strctr = 200
        For Each cl In Intersect(ActiveSheet.Columns(2).Cells, ActiveSheet.UsedRange)
        ctr = ctr + Val(cl.Value)
            Do
                If ctr > strctr Then
                    MsgBox cl.Offset(, -1) & Chr(10) & strctr
                    strctr = strctr + 200
                Else
                    Exit Do
                End If
            Loop
        Next cl
End Sub
（mm） 2016/02/08(月) 18:07

すみません。こういのはよくわからないのですが、マクロのページみたいなところにコピペしたらそのようになるということなのでしょうか。
（あや） 2016/02/08(月) 18:22

 ベタベタですが、

 C1 : =IF(B1>=200,A1,"")

 C2 : =IF(AND(SUM(B$1:B1)<200*QUOTIENT(SUM(B$1:B2),200),SUM(B$1:B2)>=200*QUOTIENT(SUM(B$1:B2),200)),A2,"")

 C2 を下にフィルコピー。

 これで、該当の行に日付が表示されると思います。
 これを上詰めでということならエキスパートさんの回答をお待ちください。

（β） 2016/02/08(月) 19:38

 ちょっと練習してみました。

 たとえば、

 G1 : =IF(B1>=200,TRUE)
 G2 : =IF(AND(SUM(B$1:B1)<200*QUOTIENT(SUM(B$1:B2),200),SUM(B$1:B2)>=200*QUOTIENT(SUM(B$1:B2),200)),TRUE)

 G2 を下にフィルコピーしておいて

 H1 : =IFERROR(INDEX(A$1:A$10,SMALL(IF(G$1:G$10=TRUE,ROW(G$1:G$10)),ROW(A1))),"")

 これを Ctrl/Shift/Enter で確定し、下にフィルコピーすれば H列に上詰めで該当の日付が表示されますね。

（β） 2016/02/08(月) 19:44

 こんな感じかな

 C1=SUM($B$1:B1)
 D1=INDEX(A:A,MATCH(ROW(A1)*200,C:C)+1)
 C1:D1 を下にフィルコピー

（AAA） 2016/02/08(月) 20:50

 C1セルに
 =IFERROR(INDEX(A$1:A$100,MATCH(1,INDEX((SUBTOTAL(9,INDIRECT("B1:B"&ROW(B$1:B$100)))>=(ROW(A1))*200)*1,0),0)),"")
 と入力して下へフィルコピーではどうか？

（ねむねむ） 2016/02/08(月) 21:11

 あぁ、一挙に大きくなった時の経過である 400 等も表示するんですね。
 では、素人の作ったβ数式は無視してください。

（β） 2016/02/08(月) 21:28

 =IFERROR(INDEX(A$1:A$100,MATCH(1,INDEX((SUMIF(INDIRECT("B1:B"&ROW(B$1:B$100)),"<>")>=(ROW(A1))*200)*1,0),0)),"")
 でも。
（ねむねむ） 2016/02/09(火) 08:42

 過去にいくつか回答がついているのに放置している質問があるがこれも放置になるんだろうか。
（ねむねむ） 2016/02/10(水) 10:37

 =IFERROR(INDEX(A$1:A$100,MATCH(TRUE,INDEX(SUMIF(A$1:A$100,"<="&A$1:A$100,B$1:B$100)>=ROW(A1)*200,0),0)),"")

 でもええかも？ ※A列昇順 ダブリなし前提。
（GobGob） 2016/02/10(水) 11:09

できました。たくさんの回答ありがとうございました。
（あや） 2016/02/11(木) 16:35

コメント返信：

[ 一覧(最新更新順) ]

[[20160208171455]] 『ある値に合計が達した日付を返す』（あや） ページの最後に飛ぶ

[[20160208171455]]　『ある値に合計が達した日付を返す』（あや）　ページの最後に飛ぶ