エクセル IF？セルを二段階に参照する関数

質問

現在、バイクの整備記録から今後の整備時期を自動で表示する表計算を作っています。
添付画像の黄色のセルに入れる関数がわからず困っています。

仕様・希望は以下となります。
・G列以降に整備した日付と距離と内容を記入
・D1に現在の走行距離を手動で入力
・黄色セル（D4～F4）は自動で表示【←ここがわかりません】
・G列以降の整備内容欄（G4、H4...）に「交換」と入力してある一番日付が新しい列（一番右側）の距離を読み取り（G4ならばG3の3,000km）、黄色セルに渡す
・「交換」以外の列は無視する
・「エンジンオイル交換」以外にも項目（行）を追加し、同時に行った整備を同列に記入する予定
・G4に直接距離を入力することは避けたい
・今回は距離の計算を知りたいが、もし可能なら期間も自動で表示するようにしたい

以上、面倒な内容ですが、エクセルにお詳しい方の知恵をお借りできれば幸いです。
ご教示よろしくお願いいたします。

回答No.1

HohoPapa · ベストアンサー

エクセルが予め用意している関数たちの組み合わせで
期待の計算を行うのは困難と思います。
やるとすれば、VBAを使った関数を使うことになりましょう。
もし、VBAに挑戦するのであれば、以下を参考にしてみてください。

添付画像のようなレイアウトで、
次のようなVBAなら期待の結果を得ることができましょう。

なお、
>「交換」と入力してある一番日付が新しい列（一番右側）の距離
一番日付が新しい列とするのか、
一番右側の列とするのか悩ましいところですが
後者としました。

また、G3、G4、G5に埋まっているのは数値で、
表示形式を使い"Km"を付加しているんですね？

E4,E5は走行距離や周期とD4を使った計算になるものと思いますが
提示がないので考えていません。

>もし可能なら期間も自動で表示するようにしたい
これもやりたいことが読み取れないので考えていません。

Function GetKyori(MyRange As Range) As Long

　Dim ColCount As Long

　GetKyori = 0
　For ColCount = 1 To MyRange.Columns.Count
　　If MyRange.Cells(2, ColCount).Value = "交換" Then
　　　GetKyori = MyRange.Cells(1, ColCount).Value
　　End If
　Next ColCount

End Function

お礼

早速のご回答および詳しいご説明をいただきありがとうございます。
やはり一般的な方法では難しいのですね。
VBAは全くやったことがないのですが、ネットで調べながら挑戦してみます！
のちほど補足欄にて結果をお伝えします。
またその時に質問させていただくかもしれませんが、どうぞよろしくお願いします。

ご回答についていくつか補足いたします。

＞一番日付が新しい列とするのか、一番右側の列とするのか
同行の中で「交換」と記入している内で最も右側（整備日が新しい）という意味です。
伝わりにくい文章で申し訳ございません。

＞また、G3、G4、G5に埋まっているのは数値で、表示形式を使い"Km"を付加しているんですね？
はい、その通りです。
添付画像では、わかりやすくするために文字列として「km」を記入していますが、実際は数値がなければ空白です。

補足

丁度休日だったので半日かけてVBAの勉強をしました。
初歩から学びながら都度コードを付け足していったので、ご回答いただいたコードとは全く異なりますが、何とか思い通りに動作させることができました。
つたないコードですが、一応投稿します(^^;)

Private Sub Worksheet_Change(ByVal Target As Range)
Application.EnableEvents = False
Dim seibiChangeKm As Long
Dim seibiLastCol As Integer

seibiLastCol = Range("I3").End(xlToRight).Column

Do While Cells(4, seibiLastCol) <> "交換"
seibiLastCol = seibiLastCol - 1
If seibiLastCol = 6 Then
Exit Do
End If
Loop

If seibiLastCol = 6 Then
seibiChangeKm = 0
Else
seibiChangeKm = Cells(3, seibiLastCol)
End If

Range("D4") = Range("D1") - seibiChangeKm
Range("E4") = Range("C4") + seibiChangeKm
Range("F4") = Range("E4") - Range("D1")
Application.EnableEvents = True
End Sub

最初に整備記録の中から最も新しい列を探し、内容が「交換」でなければ一つ古い列...とループし、その時の距離を計算式に渡しています。
なお、「交換」が一つもない場合はループを終了し0kmからの計算になるようにしています。

初めてVBAを触りましたが、実行して上手くいったときの喜びにハマりました！^^
このコードももっと綺麗なコードにできるかと思いますし、ご回答いただいたコードも現状では全ては理解できていません。
今後も引き続き勉強していこうと思いますので、行き詰まった時にはまた、お力添えいただければ幸いです。
HohoPapaさん、ご回答ありがとうございました！