Excel VBA質問箱 IV

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

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


2397 / 13646 ツリー ←次へ | 前へ→

【68277】指定した項目の最大値を求めたいのですが 江々 11/2/20(日) 9:09 質問[未読]
【68278】Re:指定した項目の最大値を求めたいのですが kanabun 11/2/20(日) 9:44 発言[未読]
【68280】Re:指定した項目の最大値を求めたいのですが 江々 11/2/20(日) 10:00 質問[未読]
【68282】Re:指定した項目の最大値を求めたいのですが kanabun 11/2/20(日) 15:56 発言[未読]
【68285】Re:指定した項目の最大値を求めたいのですが 江々 11/2/20(日) 17:18 お礼[未読]

【68277】指定した項目の最大値を求めたいのですが
質問  江々  - 11/2/20(日) 9:09 -

引用なし
パスワード
   以下のデータで指定氏名の最大値を取得したいのですが・・・

氏名    第一回    第二回    第三回
AAA    10    34    25
BBB    12    53    23
CCC    13    90    31
AAA    42    61    22
BBB    5    13    36
CCC    12    15    45
AAA    45    2    63

最大値の取得の方法はわかるのですが、
氏名AAAに制限して最大値を取得する方法はどのようにすれば良いのでしょうか。
tに《変数を》加えて…と思ったのですが、・・・アドバイスお願いします。
AAAが分かればBBBなどにも展開できるのですが、進みません。

Sub kzoeru()
Dim i, t, m
  m = 1
For i = 6 To Range("A" & Rows.Count).End(xlUp).Row
  If Cells(i, 1).Value = "AAA" Then
    t = Range(Cells(i, 2), Cells(i, 4))
    m = 1 + 1
  End If
Next
  MsgBox "最大値は" & Application.WorksheetFunction.Max(t)
End Sub

【68278】Re:指定した項目の最大値を求めたいので...
発言  kanabun  - 11/2/20(日) 9:44 -

引用なし
パスワード
   ▼江々 さん:

>最大値の取得の方法はわかるのですが、
>氏名AAAに制限して最大値を取得する方法は

A列にフィルタかけて 抽出行に MAX関数でなく、SubTotal関数を適用したら
どうでしょう?

'◆範囲は そちらのセル範囲に変更してください

Sub Try1()
  With Range("A1").CurrentRegion
    .Worksheet.AutoFilterMode = False
    .AutoFilter 1, "AAA"
    If .Columns(1).SpecialCells(xlVisible).Count > 1 Then
      MsgBox "最大値は" & WorksheetFunction.Subtotal(4, Intersect(.Cells, .Offset(1, 1)))
    End If
    .AutoFilter
  End With
      
End Sub

【68280】Re:指定した項目の最大値を求めたいので...
質問  江々  - 11/2/20(日) 10:00 -

引用なし
パスワード
   ▼kanabun さん:
早速ありがとうございました。
フィルターとSubtotalには驚きました。
結果はうまくいきました。
for〜nextで何とかピックアップできないか?と思いやりましたが、一列処理では問題が無いのですが、複数行にまたがると処理できず何か良い方法が無いか聞かせていただき、ご返答感謝しています。

◆範囲は そちらのセル範囲に変更してください
   MsgBox "最大値は" & WorksheetFunction.Subtotal(4, Intersect(.Cells, .Offset(1, 1)))
のintersectのところは範囲を示していると思いますが、どのような意味なのでしょうか?    

【68282】Re:指定した項目の最大値を求めたいので...
発言  kanabun  - 11/2/20(日) 15:56 -

引用なし
パスワード
   ▼江々 さん:
>   MsgBox "最大値は" & WorksheetFunction.Subtotal(4, Intersect(.Cells, .Offset(1, 1)))
>のintersectのところは範囲を示していると思いますが、どのような意味なのでしょうか?    

Intersect メソッドは、ある範囲とある範囲の重なる範囲を
求めるときに使います。

今 ある範囲rを
 Set r = Range("A1").CurrentRegion
として指定したとき r.Address[A1:D8]が得られたとしますと、
  A B C D
1 □□□□
2 □□□□
3 □□□□
4 □□□□
5 □□□□
6 □□□□
7 □□□□
8 □□□□

元の範囲とその範囲を
下へ一行シフトした範囲が重なるセル範囲は
  A B C D
1 □□□□
2 ■■■■
3 ■■■■
4 ■■■■
5 ■■■■
6 ■■■■
7 ■■■■
8 ■■■■
9 □□□□
Intersect(r, r.Offset(1))
で求めることができ、具体的には[A2:D8]がその範囲です。

元の範囲とその範囲を右へ1列シフトした範囲とが重なるセル範囲は
  A B C D E
1 □■■■□
2 □■■■□
3 □■■■□
4 □■■■□
5 □■■■□
6 □■■■□
7 □■■■□
8 □■■■□
Intersect(r, r.Offset(,1))
で求めることができ、具体的にはその範囲は[B1:D8]です。
いずれのばあいも、求まるセルの最終セルは元のセル範囲の
最終セル[D8]であることにご留意ください。

さて、今回は1行目と1列目の両方を除外した範囲をSubTotalの対象と
したいのですから、これまでの例を応用して、

 Set r = Range("A1").CurrentRegion のとき
求めたい範囲(最大値を検索する範囲)は
 Intersect(r, r.Offset(1, 1)))

とすればよいことになります。
  A B C D E
1 □□□□□
2 □■■■□
3 □■■■□
4 □■■■□
5 □■■■□
6 □■■■□
7 □■■■□
8 □■■■□
9 □□□□□

[B2:D8]が求まります。
このばあいも最終セルは[D8]です。

SubTotal関数は[B2:D8]セル範囲の可視セルだけを対象に
第1引数に応じた統計処理結果を返してくれます。
1:AVG 2:COUNT 3:CountA 4:MAX 5:MIN 9:SUM などなど。

【68285】Re:指定した項目の最大値を求めたいので...
お礼  江々  - 11/2/20(日) 17:18 -

引用なし
パスワード
   ▼kanabun さん:
非常に分かりやすい説明をしていただきありがとうございます。
intersectについて良く理解できました。
今まではoffsetとresizeの組み合わせでしていたので、このintersectはEventMacroに使うものだと思い込んでしました。
大変ありがとうございました。

書いていただいて内容を忘れないよう、コピーして貼っておきます。
また、バリエーションのコードを実際に動作させ確認できました。

感謝感激です、ありがとうございました。今後ともよろしくお願い申し上げます。

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