エクセルのMAX値の求め方についてお教えください

質問

こんばんは。

　画像にありますように、
E2:E6　の最大値（黒線）

　その下にある次の５セル内
E7:E11　の最大値（黄緑線）

　さらに、その下にある次の５セル内
E12:E16　の最大値（赤線）

↓
↓
↓

　を右側にあるセルに、次々と代入したいと思っています。

　なお、50000行を超えるMAX値を求め続けなければならないため、

単純な手作業では、困難な状態となっております。

　つきましては、効果的な方法（VBA等）をご教示いただければ、幸いです。

　それではよろしくお願い申し上げます。

回答No.1

f272 · ベストアンサー

簡単に作ってみた。
5個づつの最大値を求めるんだよね。
5万個のセルから1万個の最大値を求める例です。個数が違えばn=10000を修正してください。
私の使っている機械だと1秒以内に終わります。

Sub aaa()
Dim n As Long
Dim rng As Range
n = 10000
ReDim aa(1 To n, 1 To 1)
Set rng = Range("E2:E6")
For i = 1 To n
aa(i, 1) = WorksheetFunction.Max(rng)
Set rng = rng.Offset(5)
Next i
Range("J2").Resize(n) = aa
End Sub

お礼

ご回答いただきまして、どうもありがとうございました。

　一瞬でMAX値がはじき出されました。

　どうもありがとうございました。

回答No.6

tom04

No.5です！
たびたびごめんなさい。
投稿後、間違いに気づきました。

＞E2:E6　の最大値・・・

を見逃して、B～D列も含んでいました。

前回のコードは削除して↓のコードに変更してください。

Sub test() 'この行から
Dim i As Long, k As Long
Range("J:J").ClearContents
k = 1
For i = 2 To Cells(Rows.Count, "E").End(xlUp).Row Step 5
k = k + 1
Cells(k, "J") = WorksheetFunction.Max(Range(Cells(i, "E"), Cells(i + 4, "E")))
Next i
End Sub 'この行まで

何度も失礼しました。m（_ _）m

回答No.5

tom04

こんばんは！
一例です。

データはB～E列の2行目以降にあるとして、J2セル以降に表示させるとします。

画面左下の操作したいSheet見出し上で右クリック　→　コードの表示　→　VBE画面に
↓のコードをコピー＆ペーストしてマクロを実行してみてください。

Sub test() 'この行から
Dim i As Long, k As Long
Columns(10).ClearContents
k = 1
For i = 2 To Cells(Rows.Count, 2).End(xlUp).Row Step 5
k = k + 1
Cells(k, 10) = WorksheetFunction.Max(Range(Cells(i, 2), Cells(i + 4, 5)))
Next i
End Sub 'この行まで

こんな感じではどうでしょうか？m（_ _）m

回答No.4

noname#204879

J2: =MAX(OFFSET(E$2,(ROW(A1)-1)*5,,5,))

お礼

ご回答いただきまして、どうもありがとうございました。

　ですが、カッコの確認等種々試してみたのですが、

「入力した数式はただしくありません」

　とのメッセージが出てきてしまいました。

　ですが、ご回答いただきましたこと、厚くお礼申し上げます m(_ _)m

回答No.3

keithin

一番簡単な数式で結果を出す方法：
Ｊ１に「データ」と記入する
Ｊ２に
=MAX(E2:E6)
と記入する
Ｊ２：Ｊ６を選択、下向けにリスト下端まで（５万行）オートフィルドラッグする
Ｊ列を列選択する
Ｃｔｒｌ＋Ｇを押す
現れたダイアログでセル選択をクリックする
現れたダイアログで空白セルにマークしてＯＫする
空白セルが飛び飛びに選択されるので、右クリックして削除を選び、上に詰めて完成。

自分で考えるのはメンドクサイけど、教わった数式を考えずにコピーすれば出来る方法：
Ｊ２に
=MAX(INDEX(J:J,ROW()*5-8):INDEX(J:J,ROW()*5-4))
と記入し、下向けにコピーする。

＃参考
　この手の方式は、実はご相談投稿がウソ情報だったりするとご自分で正しい数式をやり直さなきゃならないんで、大概役に立ちません。

お礼

ご回答いただきまして、どうもありがとうございました。

　ですが、カッコの確認等種々試してみたのですが、

「入力した数式はただしくありません」

　とのメッセージが出てきてしまいました。

　ご回答賜わりましたこと、厚くお礼申し上げます。

回答No.2

bin-chan

VBAとのことですが、式設定による方法。

データ行は、必ず５の倍数行？
列Jにまとめて書いても良いが、列Iを確認用に使用させてください。
※セルJ2に式[=max(INDIRECT("B" & (Row()* 5) + 1 & ":E" & (Row()* 5) + 5))]を
　設定しても同じだけど、列Iで範囲を確認してください。

セルI2に式[="B" & (Row()* 5) + 1 & ":E" & (Row()* 5) + 5]を設定
これでセルI2が[B2:E6]になったら、セルI2をコピーしてセルI3に貼り付け。
これでセルI3が[B7:E11]になったら、次へ。

セルJ2に式[=max(INDIRECT(I2))]を設定。
意図した結果が得られたら、セルJ2をコピーしてセルJ3に貼り付け。
意図した結果が得られたら、セルI2：J2コピーして[データ行数÷５]行分貼り付け。

お礼

ご回答いただきまして、どうもありがとうございました。

　ですが、カッコの確認等種々試してみたのですが、

「入力した数式はただしくありません」

　とのメッセージが出てきてしまいました。

　ですが、ご回答いただきましたこと、厚くお礼申し上げます。