VBAで自動化 VBA一覧 フィルター結果の取得・集計 抽出・検索処理

【VBA】フィルター後の最大値を取得する方法｜抽出結果から自動で最大値を求める実践テクニック

Excelでオートフィルターを使って特定の条件でデータを抽出したあと、
「その中で最大値を求めたい」と思うことはよくあります。

たとえば、

  • 「営業部の売上で最も高い金額を求めたい」
  • 「指定した月の中で最大の利益を出した店舗を調べたい」
  • 「条件で絞ったデータから最大値をレポートに反映したい」

といったケースです。

ただし、通常の WorksheetFunction.Max では非表示の行（フィルターで除外されたデータ）まで含まれてしまうため、
正しい最大値を取得するには「可視セルだけ」を対象にする必要があります。

この記事では、VBAでフィルター後の最大値を正確に取得する方法を、コード例・応用例・注意点を交えて詳しく紹介します。

✅ フィルター後の最大値を求める基本構文

フィルターをかけたあと、表示されているデータだけを対象にするには、
SpecialCells(xlCellTypeVisible) を使います。

基本構文

Range("範囲").SpecialCells(xlCellTypeVisible)

このメソッドを使うことで、
非表示のセルを除き「表示されているセル（可視セル）」のみを対象に処理できます。

これを WorksheetFunction.Max と組み合わせることで、
フィルター後の最大値を取得することが可能になります。

参考：【VBA】Max関数で最大値を変数に取得・配列の最大値を取得する方法

✅ 実践例①：フィルター後の最大値を取得する基本マクロ

まずは基本形です。
特定の条件で抽出し、その中で最大値を求めるコードを紹介します。

コード例

Sub GetFilteredMaxValue()
    Dim rng As Range
    Dim maxValue As Double

    '--- フィルターを設定（B列：部署、C列：売上）---
    Range("A1:C100").AutoFilter Field:=2, Criteria1:="営業部"

    '--- フィルター後の可視セルを取得（C列の値）---
    On Error Resume Next
    Set rng = Range("C2:C100").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    '--- データが存在する場合のみ処理 ---
    If rng Is Nothing Then
        MsgBox "条件に一致するデータがありません。"
        ActiveSheet.AutoFilterMode = False
        Exit Sub
    End If

    '--- 最大値を取得 ---
    maxValue = Application.WorksheetFunction.Max(rng)
    MsgBox "営業部の最大売上は " & Format(maxValue, "#,##0") & " 円です。"

    '--- フィルター解除 ---
    ActiveSheet.AutoFilterMode = False
End Sub

処理の流れ

  1. A1:C100 の範囲にオートフィルターを設定
  2. B列で「営業部」を抽出
  3. C列の可視セル（売上金額）を取得
  4. WorksheetFunction.Max で最大値を求める
  5. 結果をメッセージで表示

出力例

営業部の最大売上は 128,000 円です。

補足

✅ 実践例②：最大値を取得して別セルに出力する

MsgBoxで表示する代わりに、最大値をExcelシート上に出力するパターンです。
業務レポートや集計シートで自動化したいときに最適です。

コード例

Sub OutputFilteredMaxValue()
    Dim rng As Range
    Dim maxValue As Double

    '--- フィルターを設定 ---
    Range("A1:C100").AutoFilter Field:=2, Criteria1:="開発部"

    '--- フィルター後の可視セルを取得 ---
    On Error Resume Next
    Set rng = Range("C2:C100").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If rng Is Nothing Then
        Range("E1").Value = "データなし"
    Else
        maxValue = Application.WorksheetFunction.Max(rng)
        Range("E1").Value = "開発部の最大売上"
        Range("F1").Value = maxValue
    End If

    ActiveSheet.AutoFilterMode = False
End Sub

実務での応用例

  • 月ごとに部署別最大値を一覧化
  • 日次売上のトップ値をダッシュボードに表示
  • 取引先ごとの最高取引金額を出力

このように、最大値を自動で反映させれば、毎回手作業で確認する必要がなくなります。

参考：【VBA】"value 型が一致しません"エラー（Value型）の原因と対処方法

✅ 実践例③：最大値を持つ行（レコード）全体を取得

「最大値そのもの」だけでなく、
最大値を含む行全体（顧客名や日付を含むデータ行）を取り出したい場合もあります。

コード例

Sub GetMaxRow()
    Dim rng As Range
    Dim maxValue As Double
    Dim cell As Range
    Dim wsResult As Worksheet

    '--- 結果出力シート ---
    Set wsResult = Sheets("結果")
    wsResult.Cells.Clear

    '--- フィルターを設定（C列：売上）---
    Range("A1:C100").AutoFilter Field:=2, Criteria1:="営業部"

    On Error Resume Next
    Set rng = Range("C2:C100").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If rng Is Nothing Then
        wsResult.Range("A1").Value = "データなし"
        ActiveSheet.AutoFilterMode = False
        Exit Sub
    End If

    '--- 最大値を取得 ---
    maxValue = Application.WorksheetFunction.Max(rng)

    '--- 最大値のセルを検索して行ごとコピー ---
    For Each cell In rng
        If cell.Value = maxValue Then
            cell.EntireRow.Copy wsResult.Range("A1")
            Exit For
        End If
    Next cell

    ActiveSheet.AutoFilterMode = False
    MsgBox "最大値の行を結果シートにコピーしました。"
End Sub

処理の内容

  1. フィルターをかけて営業部のデータを抽出
  2. 売上の最大値を取得
  3. 最大値と一致する行をコピー
  4. 「結果」シートに転記

出力結果

部署氏名売上
営業部田中128000

このように、最大値を持つレコード全体を抽出できます。
たとえば「営業部で最も売上が高い社員」を自動で判定するような業務に活用できます。



✅ 実践例④：複数条件で抽出してから最大値を求める

「部署」＋「月」など、複数条件で抽出したうえで最大値を求めることも可能です。

コード例

Sub GetFilteredMax_MultiCondition()
    Dim rng As Range
    Dim maxValue As Double

    '--- フィルター設定（部署＝営業部、月＝3月）---
    Range("A1:D100").AutoFilter Field:=2, Criteria1:="営業部"
    Range("A1:D100").AutoFilter Field:=3, Criteria1:="3月"

    '--- 可視セルを取得（D列の売上）---
    On Error Resume Next
    Set rng = Range("D2:D100").SpecialCells(xlCellTypeVisible)
    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox "該当データがありません。"
        ActiveSheet.AutoFilterMode = False
        Exit Sub
    End If

    '--- 最大値を取得 ---
    maxValue = Application.WorksheetFunction.Max(rng)
    MsgBox "営業部・3月の最大売上は " & maxValue & " 円です。"

    ActiveSheet.AutoFilterMode = False
End Sub

処理の流れ

活用例

✅ 実践例⑤：SUBTOTAL関数を使って可視セルから最大値を取得する方法

WorksheetFunction.Subtotalを使えば、
フィルター後のデータを自動的に判別して最大値を返すことも可能です。

コード例

Sub GetMaxWithSubtotal()
    Dim maxValue As Double

    '--- フィルターを設定 ---
    Range("A1:C100").AutoFilter Field:=2, Criteria1:="総務部"

    '--- SUBTOTAL(104, 範囲) → フィルター後の最大値を取得 ---
    maxValue = Application.WorksheetFunction.Subtotal(104, Range("C2:C100"))

    MsgBox "総務部の最大売上は " & maxValue & " 円です。"

    ActiveSheet.AutoFilterMode = False
End Sub

ポイント

  • 104 は「最大値（フィルター非表示を除外）」を意味する番号
  • SUMの場合は 109、平均は 101、件数は 102 などに変更可能

この方法では、わざわざ SpecialCells を使わなくても
自動的にフィルター結果だけが対象になります。

✅ よくあるエラーと対処法

症状原因対策
実行時エラー1004該当データが0件On Error Resume Next＋If rng Is Nothing Thenで制御
最大値が正しくないフィルターが未設定先に .AutoFilter を設定する
SUBTOTALが非表示行を含む引数が誤っている104など「100番台」を使用する
文字列が混ざってエラー対象列に文字データあり数値列のみを範囲指定する

✅ 処理を高速化するポイント

大量データを扱う場合、フィルター＋最大値取得は負荷が高くなることがあります。
以下の工夫で処理速度を改善できます。

1. 画面更新を止める

Application.ScreenUpdating = False

参考：【VBA】Application.ScreenUpdatingプロパティの使用方法と特徴

2. 自動計算を一時停止

Application.Calculation = xlCalculationManual

3. フィルター範囲を必要最小限に

例：A1:C10000 ではなく、A1:C5000 など実データ範囲を限定する

✅ まとめ：フィルター＋最大値で抽出結果を的確に分析する

  • SpecialCells(xlCellTypeVisible)で可視セルのみを取得
  • WorksheetFunction.Maxで最大値を求める
  • データなし時のエラー制御を必ず入れる
  • Subtotal(104, 範囲)を使えばさらに簡潔に記述可能
  • 最大値を含む行を特定して転記することで応用範囲が広がる

VBAでフィルター後の最大値を取得できるようになると、
「抽出結果の分析」「条件別レポート」「最新データ抽出」などが完全自動化できます。

手作業で最大値を確認していた作業を、一瞬で自動化できるようになるのがこのテクニックの魅力です。

-VBAで自動化, VBA一覧, フィルター結果の取得・集計, 抽出・検索処理