エクセル関数式により、列の最後尾から２行目の値からの最大値の求め方。

質問

エクセル関数式により、列の最後尾から２行目の値からの最大値の求め方。

　初めまして、よろしくお願いします。
　　　　　Ａ　　Ｂ　　Ｃ　　Ｄ　　Ｅ
１　　　　赤　　青　　黄　　緑　　黒
２　
３　　　
４　　　　１　　３　　２　　０　　９
５　　　　９　　１　　３　　４　　３
・　　　　・　　・　　・　　・　　・
・　　　　・　　・　　・　　・　　・　　
１１２　　４　　６　　１　　９　　６
１１３　　７　　７　　５　　８　　１
１１４　　６　　９　　１　　３　　７　　　
１１５　　７　　　　　２　　５　　０
１１６　　８　　　　　０　　　　　０

　という表があります。
各列の空白、もしくは０の入力されている後の値（A列では８、B列では９、C列では２、D列では５、E列では７）を最後尾とし、そこから２行目の値（A列では６、B列では６、C列では５、D列では８、E列では６）からの列（A列ではA4の１まで、B列ではB4の３まで、C列ではC4の２まで、D列ではD4の０まで、E列ではE4の９まで）の間の値の最大値（A列で、見えている部分のみではA5の9になります）を抜き出すことのできる関数式の方法がありましたら、教えていただきたく。よろしくお願いします。

回答No.2

grumpy_the_dwarf · ベストアンサー

なんだかこの話に聞き覚えがあると思ったら、質問番号5945963の方
ですね。

質問番号5945963では最後尾から2つ上の「値」を返すために、index
関数を使ってindex(範囲,最後尾から2つ上の行番号)という計算をし
ました。今回はmax関数に入れる範囲を「4行目から最後尾から2つ上
の行まで」で生成したいのですから、そのまま応用が利くはずなん
ですが。

最後尾から2つ上の行番号は
large(index(row(A4:A200)*(A4:A200>0),0),3)
でしたね。A4から始まる範囲なら行数は上の式から3引きますから、
たとえばoffset関数で
offset(A4,0,0,large(index(row(A4:A200)*(A4:A200>0),0),3)-3)
のようになります。これをmax関数に放り込むだけで行けると思いま
すよ。

お礼

前回お世話になりました。今回も回答いただき、ありがとうございます。

回答No.4

merlionXX

A～E列はそれぞれ４行目からが数値データなんですね？
わたしはこの手の複雑な関数は不得手なのでVBAでやってみました。
F1:J1にA～E列の対象範囲の最大値を転記します。

簡単ですので以下の手順をおためしください。

1.AltキーとF11キー同時に押し（以下Alt+F11キーと記述）て Visual Basic Editor を呼び出します。

2.Visual Basic Editor のメニューから「挿入」、「標準モジュール」で出てきたコードウィンド（右側の白い広い部分）に以下のコード（Sub～End Sub）をコピペします。

'********これより下**********

Sub test01()
　　Dim i As Integer
　　Dim tg(1 To 5) As Range
　　Dim myAr(1 To 5) As Double
　　Dim myStr As String
　　For i = 1 To 5
　　　　Set tg(i) = Cells(Rows.Count, i).End(xlUp)
　　　　Do While tg(i).Value = 0
　　　　　　Set tg(i) = tg(i).Offset(-1)
　　　　Loop
　　　　myAr(i) = Application.Max(Range(Cells(4, i), tg(i).Offset(-2)))
　　Next i
　　For i = 1 To 5
　　　　myStr = myStr & vbNewLine & i & "列目：" & myAr(i)
　　Next i
　　Range("F1").Resize(, UBound(myAr)).Value = myAr()
　　MsgBox "F1:J1に転記しました。" & myStr
End Sub

'********これより上**********

3.Alt+F11キーでワークシートへもどります.

4.Alt+F8キーで出てきたマクロ名（test01）を選択して実行します。

お礼

回答ありがとうございます。ＶＢＡでの処理は考えていませんでしたが、良い勉強をさせていただけました。ありがとうございます。

回答No.3

guchi_yama

数値の入力された最後の行をCOUNT関数で出してみました。始まりは4行目に特定されていたようなので＋３しています。また、最後の数値が０の場合を2回までと考えてネストを組んでいますので、それ以上０があるなら、さらに重ねて下さい。ただ、重ねる回数に制限がありますが、、、。
あと、B列、C列・・・の際、"A"のところを全て"B"などに変更して下さい。

=IF(INDIRECT("A"&COUNT(A4:A116)+3)>0,MAX(A4:INDIRECT("A"&COUNT(A4:A116)+1)),IF(INDIRECT("A"&COUNT(A4:A116)+2)>0,MAX(A4:INDIRECT("A"&COUNT(A4:A116))),IF(INDIRECT("A"&COUNT(A4:A116)+1)>0,MAX(A4:INDIRECT("A"&COUNT(A4:A116)-1)))))

お礼

回答ありがとうございます。回答いただいたやり方でうまくできました。ありがとうございます。

回答No.1

ji-ba

１　列の最後尾を指定　
２　１より前２列目を指定
３　２とA４列までを指定
４　３の間で最大値を検索
５　４を表示

以上の順番で関数を組み合わせればいいでしょう。

お礼

回答ありがとうございます。おっしゃるとおりです。