エクセルで指定した月末の残高を表示

＿＿月　日　件名　入　出　残高

　　Ａ　Ｂ　Ｃ　　Ｄ　Ｅ　Ｆ
１　４　３　　　　100　　 100
２　４　10　　　　　　20　80
３　５　８　　　　　　 10　70
４　５　11　　　　　　20　50
５　５　15　　　　60　　　110
６　７　16　　　　　　10　100
７　７　18　　　　　　50　50

このような表で、例えばＨ1に4と入力したときはＨ2に4月末残高である80、
Ｈ1に6と入力したときは、6月の入出はなくてもＨ2に直近の5月末残高である110

というような入力がされるような式をご教示いただきたいと思います。
【条件】
・Ａ列には４から３（月）までが入力される可能性がある。（入力がない月もある）
・Ｂ列に入力される数字は１から３１（日）のうち不定。
・Ａ列、Ｂ列ともに必ず時系列順に入力される。
・年度で管理するため、３月の次に４月が入ることはない。

No.1 ベストアンサー 回答者： tom04 回答日時： 2018/04/03 22:28

こんばんは！

年度表示のようなので、12の後は1・2・3　となる訳ですね。

一案です。
↓の画像のように作業用の列を1列設けてみました。
作業列G2セルに
=IF(A2="","",IF(A2<=3,A2+12,A2))

という数式を入れフィルハンドルでこれ以上データはない！というくらいまで下へコピーしておきます。

そしてH2セルに
=INDEX(F1:F1000,MAX(IF((G1:G1000<>"")*(G1:G1000<=(IF(H1<=3,H1+12,H1))),ROW(A1:A1000))))

配列数式なのでCtrl＋Shift＋Enterで確定！（←必須★）しています。

※　作業列が目障りであれば遠く離れた列にするか
非表示にしておいてください。m(_ _)m

この回答へのお礼

回答ありがとうございました。
ご教示いただいた内容で値を求めることができました。

ただ、可能であれば作業列を用いない方法もご教示いただけると幸いです。

お礼日時：2018/04/04 11:05

No.3 回答者： tom04 回答日時： 2018/04/04 11:56

No.1です。

＞可能であれば作業列を用いない方法も・・・

単純に1月～12月までの昇順のデータであれば簡単なのですが、
4月～翌年の3月までの並びだというコトなので作業列なしだと結構厄介です。

手っ取り早くVBAにしてみました。
尚、前回の画像の配置通りだとします。

画面左下の操作したいシート見出し上で右クリック　→　コードの表示　→　VBE画面のカーソルが点滅しているところに
↓のコードをコピー＆ペースト　→　Excel画面に戻り（VBE画面を閉じて）、H1セルにデータを入力してみてください。

Private Sub Worksheet_Change(ByVal Target As Range) '//この行から//
Dim i As Long, trgMnt As Long, myMnt As Long
With Target
If .Address = "$H$1" Then
If .Value <> "" Then
If .Value <= 3 Then
myMnt = .Value + 12
Else
myMnt = .Value
End If
For i = Cells(Rows.Count, "A").End(xlUp).Row To 2 Step -1
If Cells(i, "A") <= 3 Then
trgMnt = Cells(i, "A") + 12
Else
trgMnt = Cells(i, "A")
End If
If trgMnt <= myMnt Then
.Offset(1) = Cells(i, "F")
Exit For
End If
Next i
Else
.Offset(1).ClearContents
End If
End If
End With
End Sub '//この行まで//

とりあえずこれでお望みの動きにはなると思います。m(_ _)m

この回答へのお礼

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

やはり関数では限界があるのですね。
VBA自体は私は少しだけ知識はあるので修正程度ならできますが、担当が居なくなった場合に編集できなくなるので使用が避けられている現状です。
ただ、この回答は非常に勉強になりましたので参考にさせていただきます。

お礼日時：2018/04/04 13:08

No.2 回答者： mike_g 回答日時： 2018/04/04 00:43

H2: =IF(COUNTIF(A$1:A$500,H1),INDEX(F$1:F$500,MAX(IF(A$1:A$500=H$1,ROW(A$1:A$500)))),"")

【お断り】上式は必ず配列数式として入力のこと

この回答へのお礼

回答ありがとうございました。

ご教示いただいた内容で入力してみましたが、例えば6月、8月以降といった入力がなかった月の場合、Ｈ2は0となってしまうようです。

お礼日時：2018/04/04 11:09