Excel VBA質問箱 IV

当質問箱は、有志のボランティア精神のおかげで成り立っています。
問題が解決したら、必ずお礼をしましょうね。
本サイトの基本方針をまとめました。こちら をご一読ください。

投稿種別の選択が必要です。ご注意ください。
迷惑投稿防止のため、URLの入力を制限しています。ご了承ください。


7845 / 13645 ツリー ←次へ | 前へ→

【36430】任意範囲の最大値を求める方法 TAKA 06/3/30(木) 21:27 質問[未読]
【36436】Re:任意範囲の最大値を求める方法 ichinose 06/3/31(金) 6:12 発言[未読]
【36473】Re:任意範囲の最大値を求める方法 bykin 06/3/31(金) 22:29 回答[未読]
【36503】Re:任意範囲の最大値を求める方法 TAKA 06/4/2(日) 9:51 お礼[未読]

【36430】任意範囲の最大値を求める方法
質問  TAKA E-MAIL  - 06/3/30(木) 21:27 -

引用なし
パスワード
   データが96000個あり、A,C,D列に番号 B,D,E列に数値データが入っています。
(データは11行目から始まっています)
1 111 32001 111 64001 111
2 222 32002 222 64002 123
3 123 32003 321 64003 223
*  *   *   *   *   *
*  *   *   *   *   *
*  *   *   *   *   *

全範囲の最大値を求めるには、エクセル関数だけで
MAX(B11:B32010,D11:D32010,E11:E32010)十分なのですが、J1に開始番号、J2に終了番号を入力し、範囲を変更して最大値を求めたいと思っています。
J1に1、J2に64003を入力しt場合 
MAX(B11:B32010,D11:D32010,E11:E13)を求めたいのです。
VBAでMAX関数を使ってトライしましたが、範囲を決めるところでエラーが出てあきらめてしまいました。
初歩的な質問でしたら申し訳ありませんが、どなたか教えてください。
よろしくお願いします。

【36436】Re:任意範囲の最大値を求める方法
発言  ichinose  - 06/3/31(金) 6:12 -

引用なし
パスワード
   ▼TAKA さん:
おはようございます。再送です。

>データが96000個あり、A,C,D列に番号 B,D,E列に数値データが入っています。
>(データは11行目から始まっています)
>1 111 32001 111 64001 111
>2 222 32002 222 64002 123
>3 123 32003 321 64003 223
>*  *   *   *   *   *
>*  *   *   *   *   *
>*  *   *   *   *   *
>
>全範囲の最大値を求めるには、エクセル関数だけで
>MAX(B11:B32010,D11:D32010,E11:E32010)十分なのですが、J1に開始番号、J2に終了番号を入力し、範囲を変更して最大値を求めたいと思っています。
>J1に1、J2に64003を入力しt場合 
>MAX(B11:B32010,D11:D32010,E11:E13)を求めたいのです。
>VBAでMAX関数を使ってトライしましたが、範囲を決めるところでエラーが出てあきらめてしまいました。
>初歩的な質問でしたら申し訳ありませんが、どなたか教えてください。
>よろしくお願いします。
セルJ1、J2には正しい数字が入っているとして、
標準モジュールに

'=================================================================
Sub main()
  Dim rngaddress As String
  rngaddress = get_address(Range("j1").Value, Range("j2").Value)
  MsgBox rngaddress
  MsgBox Application.Max(Range(rngaddress))
End Sub
'=======================================================================
Function get_address(st As Variant, ed As Variant, Optional strow = 11, Optional edrow As Long = 32010) As String
  Dim idx As Long
  Dim myarray As Variant
  Dim rcnt As Long
  Dim r1 As Long, r2 As Long
  Dim c1 As Long, c2 As Long
  rcnt = edrow - strow + 1
  myarray = Array("b", "d", "e")
  r1 = (st - 1) \ rcnt
  c1 = (st - 1) Mod rcnt
  r2 = (ed - 1) \ rcnt
  c2 = (ed - 1) Mod rcnt
  ReDim addarray(r1 To r2)
  For idx = r1 To r2
    If LBound(addarray()) = UBound(addarray()) Then
     addarray(idx) = myarray(idx) & (strow + c1) & ":" & myarray(idx) & (strow + c2)
    Else
     Select Case idx
      Case LBound(addarray())
       addarray(idx) = myarray(idx) & (strow + c1) & ":" & myarray(idx) & edrow
      Case UBound(addarray())
       addarray(idx) = myarray(idx) & strow & ":" & myarray(idx) & (strow + c2)
      Case Else
        addarray(idx) = myarray(idx) & strow & ":" & myarray(idx) & edrow
      End Select
    End If
    Next
  get_address = Join(addarray(), ",")
End Function

として、mainを実行してみて下さい。

【36473】Re:任意範囲の最大値を求める方法
回答  bykin  - 06/3/31(金) 22:29 -

引用なし
パスワード
   こんばんわ。

マクロやないとあかんのかな?
関数でもできると思うねんけど・・・

ちょっと長いけど、こんなんとか。

=MAX(IF(IF(J2>32000,32000,J2)>IF(J1>32000,32001,J1),
MAX(OFFSET(B10,IF(J1>32000,32001,J1),0,
IF(J2>32000,32000,J2)-IF(J1>32000,32001,J1)+1)),0),
IF(IF(J2>64000,64000,J2)<IF(J1>64000,64001,IF(J1>32000,J1,32001)),0,
MAX(OFFSET(D10,IF(J1>64000,64001,IF(J1>32000,J1,32001))-32000,0,
IF(J2>64000,64000,J2)-IF(J1>64000,64001,IF(J1>32000,J1,32001))+1))),
IF(IF(J2>96000,96000,J2)<IF(J1>96000,96001,IF(J1>64000,J1,64001)),0,
MAX(OFFSET(F10,IF(J1>96000,96001,IF(J1>64000,J1,64001))-64000,0,
IF(J2>96000,96000,J2)-IF(J1>96000,96001,IF(J1>64000,J1,64001))+1))))

マクロやと、再計算させるのに直接マクロを実行するか、イベント使うかって
ことになるけど、関数やったら答えが即反映するから、こういう場合は
関数のほうがええと思うねんけどな。

ま、いっぺん試してみておくれやす。
ほな。

【36503】Re:任意範囲の最大値を求める方法
お礼  TAKA E-MAIL  - 06/4/2(日) 9:51 -

引用なし
パスワード
   ichinoseさん
bykinさん

よく分かりました。よく整理してやれば、マクロを使わなくてもできるんですね。
値や列が増えたときには、マクロのほうが処理が早そうですし、両方とも覚えて行きたいと思います。
今回の件、ありがとうございました。

7845 / 13645 ツリー ←次へ | 前へ→
ページ:  ┃  記事番号:
2610219
(SS)C-BOARD v3.8 is Free