大体でIT

－ちょっと使えるネタを紹介－

HOME >  Excel VBA >  ピボットテーブルの操作
大体でIT

Excel VBAで、ピボットテーブルをフィルターする方法について、ご紹介します。使うVBAコードは、「.PivotFilters.Add2」です。ピボットテーブルでは、ラベルを「文字」や「数値」、「日付」でフィルターしたり、「ラベルの値」をフィルターすることができます。フィルターする箇所に応じて、VBAコードを使い分けていきましょう。

はじめに

この記事では、VBAでピボットテーブルをフィルターする方法について、ご紹介します。

ピボットテーブルをフィルターするには、「.PivotFilters.Add2」を使います。

フィルターする箇所は、次の項目があります。

  • ラベルを「文字」でフィルター
  • ラベルを「数値」でフィルター
  • ラベルを「日付」でフィルター
  • 「ラベルの値」をフィルター

という感じです。

ピボットテーブルは、フィルターできる箇所が複数あります。

フィルターしたい箇所や項目に合わせて、フィルターするVBAコードを使い分けていきましょう。

では、VBAでピボットテーブルをフィルターする方法について、解説していきます。

この記事で紹介すること

  • VBAでピボットテーブルをフィルターする方法

こちらの記事も参考になります

目次から使いそうな説明のところへ移動すると便利ですよ。

目次

VBAコードまとめ

ピボットテーブルをフィルターするVBAコードをまとめました。

VBAコードだけ確認したい場合に、ご活用ください。

'ラベルを「文字で」フィルター
With ActiveSheet.PivotTables(1)
    .PivotFields("商品").ClearAllFilters
    .PivotFields("商品").PivotFilters.Add2 xlCaptionEquals, , "いちご"
End With

'引数の一覧
'指定の値に等しい（xlCaptionEquals）
'指定の値に等しくない（xlCaptionDoesNotEqual）

'指定の値で始まる（xlCaptionBeginsWith）
'指定の値で始まらない（xlCaptionDoesNotBeginWith）
'指定の値で終わる（xlCaptionEndsWith）
'指定の値で終わらない（xlCaptionDoesNotEndWith）

'指定の値を含む（xlCaptionContains）
'指定の値を含まない（xlCaptionDoesNotContain）

'指定の値より大きい（xlCaptionIsGreaterThan）
'指定の値以上（xlCaptionIsGreaterThanOrEqualTo）
'指定値より小さい（xlCaptionIsLessThan）
'指定の値以下（xlCaptionIsLessThanOrEqualTo）

'指定の範囲内（xlCaptionIsBetween）
'指定の範囲外（xlCaptionIsNotBetween）


'ラベルを「数値で」フィルター
With ActiveSheet.PivotTables(1)
    .PivotFields("点数").ClearAllFilters
    .PivotFields("点数").PivotFilters.Add2 xlCaptionIsGreaterThanOrEqualTo, , 20
End With

'引数の一覧
'指定の値より大きい（xlCaptionIsGreaterThan）
'指定の値以上（xlCaptionIsGreaterThanOrEqualTo）
'指定値より小さい（xlCaptionIsLessThan）
'指定の値以下（xlCaptionIsLessThanOrEqualTo）

'指定の範囲内（xlCaptionIsBetween）
'指定の範囲外（xlCaptionIsNotBetween）


'ラベルを「日付で」フィルター
With ActiveSheet.PivotTables(1)
    .PivotFields("年").ClearAllFilters
    .PivotFields("月").ClearAllFilters
    .PivotFields("日付").ClearAllFilters
    .PivotFields("日付").PivotFilters.Add2 xlDateBetween, , "2021/9/1", "2021/11/1"
End With

'引数の一覧
'指定の値に等しい（xlSpecificDate）
'指定の値より前（xlBefore）
'指定の値より後（xlAfter）
'指定の範囲内（xlDateBetween）


'「ラベルの値」をフィルター
With ActiveSheet.PivotTables(1)
    .PivotFields("商品").ClearAllFilters
    .PivotFields("商品").PivotFilters.Add2 xlValueIsGreaterThanOrEqualTo, .PivotFields("合計 / 売上"), 1400
End With

'引数の一覧
'指定の範囲に等しい（xlValueEquals）
'指定の値に等しくない（xlValueDoesNotEqual）

'指定の値より大きい（xlValueIsGreaterThan）
'指定の値以上（xlValueIsGreaterThanOrEqualTo）
'指定の値より小さい（xlValueIsLessThan）
'指定の値以下（xlValueIsLessThanOrEqualTo）

'指定の範囲内（xlValueIsBetween）
'指定の範囲外（xlValueIsNotBetween）

では、解説していきます。

VBAでピボットテーブルのラベルを文字でフィルターする

VBAで、ピボットテーブルのラベルを「文字」でフィルターしてみます。

フィルターする箇所

フィルターする箇所は、ここです。

「ラベル」をフィルターしたい

ピボットテーブルのラベルを文字でフィルターしたい

では、フィルターしてみます。

ラベルを文字でフィルターする

「商品」ラベルを「文字」でフィルターしてみます。

「商品」ラベルでフィルターする

VBAコードは、こんな感じになります。

Sub TEST1()
    
    With ActiveSheet.PivotTables(1)
        '「商品」ラベルを「いちご」でフィルター
        .PivotFields("商品").PivotFilters.Add2 xlCaptionEquals, , "いちご"
    End With
    
End Sub

既にフィルターされている場合に、フィルターするとエラーとなってしまうので、最初にフィルターを解除します。

Sub TEST2()
    
    With ActiveSheet.PivotTables(1)
        .PivotFields("商品").ClearAllFilters 'フィルターを解除
        '「商品」ラベルを「いちご」でフィルター
        .PivotFields("商品").PivotFilters.Add2 xlCaptionEquals, , "いちご"
    End With
    
End Sub

では、VBAコードを実行してみます。

VBAでピボットテーブルの「商品」ラベルでフィルターできた

VBAでピボットテーブルの「商品」ラベルでフィルターできました。

「支店」ラベルをフィルターする

次は、「支店」ラベルを「文字」でフィルターしてみます。

Sub TEST3()
    
    With ActiveSheet.PivotTables(1)
        .PivotFields("支店").ClearAllFilters 'フィルターを解除
        '「支店」ラベルを「東京」でフィルター
        .PivotFields("支店").PivotFilters.Add2 xlCaptionEquals, , "東京"
    End With
    
End Sub

では、VBAコードを実行してみます。

VBAでピボットテーブルの「支店」ラベルでフィルターできた

VBAで、ピボットテーブルの「支店」ラベルでフィルターできました。

ラベルのフィルター種類の一覧

ラベルのフィルター種類の一覧は、こんな感じです。

  • 指定の値に等しい（xlCaptionEquals）
  • 指定の値に等しくない（xlCaptionDoesNotEqual）
  • 指定の値で始まる（xlCaptionBeginsWith）
  • 指定の値で始まらない（xlCaptionDoesNotBeginWith）
  • 指定の値で終わる（xlCaptionEndsWith）
  • 指定の値で終わらない（xlCaptionDoesNotEndWith）
  • 指定の値を含む（xlCaptionContains）
  • 指定の値を含まない（xlCaptionDoesNotContain）
  • 指定の値より大きい（xlCaptionIsGreaterThan）
  • 指定の値以上（xlCaptionIsGreaterThanOrEqualTo）
  • 指定値より小さい（xlCaptionIsLessThan）
  • 指定の値以下（xlCaptionIsLessThanOrEqualTo）
  • 指定の範囲内（xlCaptionIsBetween）
  • 指定の範囲外（xlCaptionIsNotBetween）

VBAコードの例を挙げると、こんな感じになります。

Sub TEST4()

    With ActiveSheet.PivotTables(1)
        .PivotFields("商品").PivotFilters.Add2 xlCaptionEquals, , "いちご" '「いちご」に等しい
        .PivotFields("商品").PivotFilters.Add2 xlCaptionDoesNotEqual, , "いちご" '「いちご」に等しくない

        .PivotFields("商品").PivotFilters.Add2 xlCaptionBeginsWith, , "いちご" '「いちご」で始まる
        .PivotFields("商品").PivotFilters.Add2 xlCaptionDoesNotBeginWith, , "いちご" '「いちご」で始まらない
        .PivotFields("商品").PivotFilters.Add2 xlCaptionEndsWith, , "いちご" '「いちご」で終わる
        .PivotFields("商品").PivotFilters.Add2 xlCaptionDoesNotEndWith, , "いちご" '「いちご」で終わらない

        .PivotFields("商品").PivotFilters.Add2 xlCaptionContains, , "いちご" '「いちご」を含む
        .PivotFields("商品").PivotFilters.Add2 xlCaptionDoesNotContain, , "いちご" '「いちご」を含まない
        
        .PivotFields("商品").PivotFilters.Add2 xlCaptionIsGreaterThan, , "いちご" '「いちご」より大きい
        .PivotFields("商品").PivotFilters.Add2 xlCaptionIsGreaterThanOrEqualTo, , "いちご" '「いちご」以上
        .PivotFields("商品").PivotFilters.Add2 xlCaptionIsLessThan, , "いちご"  '「いちご」より小さい
        .PivotFields("商品").PivotFilters.Add2 xlCaptionIsLessThanOrEqualTo, , "いちご" '「いちご」以下
        
        .PivotFields("商品").PivotFilters.Add2 xlCaptionIsBetween, , "いちご", "キウイ" '「いちご」～「キウイ」の範囲内
        .PivotFields("商品").PivotFilters.Add2 xlCaptionIsNotBetween, , "いちご", "キウイ" '「いちご」～「キウイ」の範囲外
    End With

End Sub

上では書いていないですけど、フィルターする前には、「フィルターの解除」をしてからフィルターしましょう。

VBAでピボットテーブルのラベルを数値でフィルターする

VBAで、ピボットテーブルのラベルを「数値」でフィルターしてみます。

フィルターする箇所

フィルターする箇所は、ここです。

「数値」でフィルターしたい

ピボットテーブルのラベルを数値でフィルターしたい

では、数値でフィルターしてみます。

ラベルを数値でフィルターする

「点数」ラベルを「数値」でフィルターするVBAコードは、こんな感じになります。

Sub TEST5()
    
    With ActiveSheet.PivotTables(1)
        .PivotFields("点数").ClearAllFilters 'フィルター解除
        '「点数」ラベルを「20以上」でフィルター
        .PivotFields("点数").PivotFilters.Add2 xlCaptionIsGreaterThanOrEqualTo, , 20
    End With
    
End Sub

では、VBAコードを実行してみます。

「数値」でフィルターできた

VBAでピボットテーブルのラベルを数値でフィルターできた

VBAで、ピボットテーブルのラベルを「数値」でフィルターできました。

ラベルのフィルター種類の一覧

ピボットテーブルのラベルを「数値」でフィルターする際の引数の一覧です。

  • 指定の値より大きい（xlCaptionIsGreaterThan）
  • 指定の値以上（xlCaptionIsGreaterThanOrEqualTo）
  • 指定値より小さい（xlCaptionIsLessThan）
  • 指定の値以下（xlCaptionIsLessThanOrEqualTo）
  • 指定の範囲内（xlCaptionIsBetween）
  • 指定の範囲外（xlCaptionIsNotBetween）

VBAコードの例を挙げると、こんな感じになります。

Sub TEST6()

    With ActiveSheet.PivotTables(1)
        .PivotFields("点数").PivotFilters.Add2 xlCaptionEquals, , 20   '「20」に等しい
        .PivotFields("点数").PivotFilters.Add2 xlCaptionDoesNotEqual, , 20 '「20」に等しくない
        
        .PivotFields("点数").PivotFilters.Add2 xlCaptionIsGreaterThan, , 20 '「20」より大きい
        .PivotFields("点数").PivotFilters.Add2 xlCaptionIsGreaterThanOrEqualTo, , 20 '「20」以上
        .PivotFields("点数").PivotFilters.Add2 xlCaptionIsLessThan, , 20 '「20」より小さい
        .PivotFields("点数").PivotFilters.Add2 xlCaptionIsLessThanOrEqualTo, , 20 '「20」以下
        
        .PivotFields("点数").PivotFilters.Add2 xlCaptionIsBetween, , 20, 30 '「20」～「30」の間,
        .PivotFields("点数").PivotFilters.Add2 xlCaptionIsNotBetween, , 20, 30 '「20」～「30」の範囲外
    End With
    
End Sub

目的にあったフィルターの引数を使いましょう。

VBAでピボットテーブルの日付をフィルターする

VBAでピボットテーブルの日付をフィルターしてみます。

フィルターする箇所

フィルターする箇所は、ここです。

「日付」でフィルターしたい

ピボットテーブルのラベルを日付フィルターしたい

では、ピボットテーブルのラベルを「日付」フィルターしてみます。

日付をフィルターする

「日付」ラベルを日付フィルターするVBAコードは、こんな感じになります。

Sub TEST7()
      
    With ActiveSheet.PivotTables(1)
        'フィルター解除
        .PivotFields("年").ClearAllFilters
        .PivotFields("月").ClearAllFilters
        .PivotFields("日付").ClearAllFilters
        '「2021/9/1」～「2021/11/1」の範囲内でフィルター
        .PivotFields("日付").PivotFilters.Add2 xlDateBetween, , "2021/9/1", "2021/11/1"
    End With
        
End Sub

では、VBAコードを実行してみます。

「日付」でフィルターできた

VBAでピボットテーブルのラベルを日付でフィルターできた

VBAでピボットテーブルのラベルを日付でフィルターできました。

日付のフィルター種類の一覧

日付フィルターする際の、引数の一覧は、こんな感じです。

  • 指定の値に等しい（xlSpecificDate）
  • 指定の値より前（xlBefore）
  • 指定の値より後（xlAfter）
  • 指定の範囲内（xlDateBetween）

VBAコードの例を挙げておくと、こんな感じになります。

Sub TEST8()

    With ActiveSheet.PivotTables(1)
        .PivotFields("日付").PivotFilters.Add2 xlSpecificDate, , "2021/9/1" '「2021/9/1」に等しい
        .PivotFields("日付").PivotFilters.Add2 xlBefore, , "2021/9/1" '「2021/9/1」より前
        .PivotFields("日付").PivotFilters.Add2 xlAfter, , "2021/9/1" '「2021/9/1」より後
        .PivotFields("日付").PivotFilters.Add2 xlDateBetween, , "2021/9/1", "2021/11/1" '「2021/9/1」と「2021/11/1」の範囲内
    End With
    
End Sub

フィルターする項目で、引数がちょっと変わります。

VBAでピボットテーブルの値をフィルターする

VBAで、ピボットテーブルの「ラベルの値」をフィルターしてみます。

フィルターする箇所

フィルターする箇所は、「ラベルの値」です。

「ラベルの値」をフィルターしたい

ピボットテーブルの値をフィルターしたい

では、ピボットテーブルの値をフィルターしてみます。

値をフィルターする

「商品」ラベルの「値」をフィルターしてみます。

「商品」ラベルの「値」をフィルター

VBAコードは、こんな感じです。

Sub TEST9()
    
    With ActiveSheet.PivotTables(1)
        .PivotFields("商品").ClearAllFilters 'フィルター解除
        '「商品」ラベルの「値」を「1400以上」でフィルター
        .PivotFields("商品").PivotFilters.Add2 xlValueIsGreaterThanOrEqualTo, .PivotFields("合計 / 売上"), 1400
    End With
    
End Sub

では、VBAコードを実行してみます。

VBAでピボットテーブルの「商品」の値をフィルターできた

VBAで、ピボットテーブルの「商品」の値をフィルターできました。

「商品」ラベルの「値」をフィルターしてみます。

「支店」ラベルの「値」をフィルター

VBAコードは、こんな感じです。

Sub TEST10()

    With ActiveSheet.PivotTables(1)
        .PivotFields("支店").ClearAllFilters 'フィルター解除
        '「支店」ラベルの「値」を「1500以上」でフィルター
        .PivotFields("支店").PivotFilters.Add2 xlValueIsGreaterThanOrEqualTo, .PivotFields("合計 / 売上"), 1500
    End With
End Sub

では、VBAコードを実行してみます。

VBAでピボットテーブルの「支店」の値をフィルターできた

VBAで、ピボットテーブルの「支店」の値をフィルターできました。

値のフィルター種類の一覧

「ラベルの値」をフィルターする際の引数は、こんな感じになります。

  • 指定の範囲に等しい（xlValueEquals）
  • 指定の値に等しくない（xlValueDoesNotEqual）
  • 指定の値より大きい（xlValueIsGreaterThan）
  • 指定の値以上（xlValueIsGreaterThanOrEqualTo）
  • 指定の値より小さい（xlValueIsLessThan）
  • 指定の値以下（xlValueIsLessThanOrEqualTo）
  • 指定の範囲内（xlValueIsBetween）
  • 指定の範囲外（xlValueIsNotBetween）

VBAコードの例を挙げると、こんな感じになります。

Sub TEST11()

    With ActiveSheet.PivotTables(1)
        .PivotFields("商品").PivotFilters.Add2 xlValueEquals, .PivotFields("合計 / 売上"), 1400 '「1400」に等しい
        .PivotFields("商品").PivotFilters.Add2 xlValueDoesNotEqual, .PivotFields("合計 / 売上"), 1400 '「1400」に等しくない
        
        .PivotFields("商品").PivotFilters.Add2 xlValueIsGreaterThan, .PivotFields("合計 / 売上"), 1400 '「1400」より大きい
        .PivotFields("商品").PivotFilters.Add2 xlValueIsGreaterThanOrEqualTo, .PivotFields("合計 / 売上"), 1400 '「1400」以上
        .PivotFields("商品").PivotFilters.Add2 xlValueIsLessThan, .PivotFields("合計 / 売上"), 1400 '「1400」より小さい
        .PivotFields("商品").PivotFilters.Add2 xlValueIsLessThanOrEqualTo, .PivotFields("合計 / 売上"), 1400 '「1400」以下
        
        .PivotFields("商品").PivotFilters.Add2 xlValueIsBetween, .PivotFields("合計 / 売上"), 1400, 1600 '「1400」～「1600」の範囲内
        .PivotFields("商品").PivotFilters.Add2 xlValueIsNotBetween, .PivotFields("合計 / 売上"), 1400, 1600 '「1400」～「1600」の範囲外
    End With

End Sub

「値のラベル」のフィルターする場合は、「値」ラベルも入力するところがポイントですね。

VBAでピボットテーブルをラベルを複数でフィルターする

VBAで、ピボットテーブルをラベルを「複数」でフィルターしてみます。

やりたいこと

ラベルを複数の項目でフィルターする、というのがやりたいです。

「複数の項目」でフィルターしたい

ピボットテーブルのラベルを複数の項目でフィルターしたい

では、ラベルを複数の項目でフィルターしてみます。

ラベルを複数の項目でフィルターする

ラベルを「複数の項目」でフィルターするVBAコードは、こんな感じになります。

Sub TEST12()
    
    '「商品」ラベルをループ
    For Each A In ActiveSheet.PivotTables(1).PivotFields("商品").PivotItems
        Select Case A.Value
            Case "いちご", "キウイ"
                A.Visible = True
            Case Else
                A.Visible = False
        End Select
    Next

End Sub

では、実行してみます。

複数の項目でフィルターできた

VBAでピボットテーブルのラベルを複数の項目でフィルターできた

VBAで、ピボットテーブルのラベルを「複数の項目」でフィルターできました。

手順を追ってVBAコードをみてみます。

複数のラベルでフィルターする手順

複数のラベルでフィルターする手順です。

「PivotItems」で表示と非表示ができる

「PivotItems」を使ってラベルを「非表示」にしてみます。

Sub TEST13()
    
    '「商品」ラベルの「いちご」を「非表示」
    ActiveSheet.PivotTables(1).PivotFields("商品").PivotItems("いちご").Visible = False
    
End Sub

VBAコードを実行します。

VBAで商品のラベルを非表示にできた

VBAで商品のラベルを「非表示」にできた。

「PivotItems」を使ってラベルを「表示」してみます。

Sub TEST14()
    
    '「商品」ラベルの「いちご」を「表示」
    ActiveSheet.PivotTables(1).PivotFields("商品").PivotItems("いちご").Visible = True
    
End Sub

VBAコードを実行します。

VBAで商品のラベルを表示できた

VBAで商品のラベルを「表示」できた。

ラベル内をFor Eachでループする

「For Each」を使うと、ラベル内をループすることができます。

Sub TEST15()
    
    '「商品」ラベルをループ
    For Each A In ActiveSheet.PivotTables(1).PivotFields("商品").PivotItems
        Debug.Print A.Value
    Next

End Sub

「商品」ラベル内の項目をループして取得します。

VBAで商品のラベルをループして取得する

では、VBAコードを実行します。

VBAで商品のラベルをループして取得できた

VBAで、「商品」ラベル内の項目をループして取得できました。

Select Caseで表示か非表示にする

「Select Case」を使って、ラベルの項目を表示か、非表示にします。

Sub TEST16()
    
    '「商品」ラベルをループ
    For Each A In ActiveSheet.PivotTables(1).PivotFields("商品").PivotItems
        Select Case A.Value
            Case "いちご"
                A.Visible = True
            Case Else
                A.Visible = False
        End Select
    Next

End Sub

では、VBAコードを実行します。

VBAで1つのラベルだけをフィルターする

VBAで1つのラベルだけをフィルターできました。

複数の項目でフィルターする

先ほどと同じ要領で、「複数の項目」でフィルターします。

Sub TEST17()
    
    '「商品」ラベルをループ
    For Each A In ActiveSheet.PivotTables(1).PivotFields("商品").PivotItems
        Select Case A.Value
            Case "いちご", "キウイ"
                A.Visible = True
            Case Else
                A.Visible = False
        End Select
    Next

End Sub

では、VBAコードを実行します。

VBAで商品のラベルを複数の項目でフィルターする

という感じで、商品のラベルを「複数の項目」でフィルターすることができます。

VBAでピボットテーブルのフィルターを解除する

VBAで、ピボットテーブルのフィルターを解除する方法です。

  • すべてのフィルターを解除
  • 項目ごとにフィルターを解除

という感じで、2種類の解除方法があります。

では、やってみます。

すべてのフィルターを解除する

すべてのフィルターを解除するVBAコードです。

Sub TEST18()
    
    'すべてのフィルターを解除
    ActiveSheet.PivotTables(1).ClearAllFilters
    
End Sub

「商品」と「支店」のラベルをフィルターしておきます。

「商品」と「支店」をフィルターしておく

ピボットテーブルの「商品」と「支店」のラベルをフィルターしておく

では、VBAコードを実行します。

すべてのフィルターを解除できた

VBAですべてのラベルのフィルターを解除する

VBAで、すべてのラベルのフィルターを解除できました。

項目ごとにフィルターを解除する

「項目ごと」にフィルターを解除してみます。

「商品」ラベルのフィルターを解除

「商品」ラベルのフィルターを解除するVBAコードです。

Sub TEST19()
    
    '「商品」ラベルのフィルターを解除
    ActiveSheet.PivotTables(1).PivotFields("商品").ClearAllFilters
    
End Sub

「商品」と「支店」のラベルをフィルターしておきます。

ピボットテーブルの「商品」と「支店」のラベルをフィルターしておく

では、VBAコードを実行します。

VBAで「商品」のラベルのフィルターを解除する

VBAで「商品」ラベルのみ、フィルターを解除できました。

「支店」ラベルでもやってみます。

「支店」ラベルのフィルターを解除

「支店」ラベルのフィルターを解除するVBAコードです。

Sub TEST20()
    
    '「支店」ラベルのフィルターを解除
    ActiveSheet.PivotTables(1).PivotFields("支店").ClearAllFilters
    
End Sub

「商品」と「支店」のラベルをフィルターしておきます。

ピボットテーブルの「商品」と「支店」のラベルをフィルターしておく

では、VBAコードを実行します。

VBAで「支店」のラベルのフィルターを解除する

VBAで「支店」ラベルのみ、フィルターを解除できました。

おわりに

この記事では、VBAでピボットテーブルをフィルターする方法について、ご紹介しました。

ピボットテーブルをフィルターするには、「.PivotFilters.Add2」を使います。

フィルターする箇所は、次の項目があります。

  • ラベルを「文字」でフィルター
  • ラベルを「数値」でフィルター
  • ラベルを「日付」でフィルター
  • 「ラベルの値」をフィルター

という感じです。

ピボットテーブルは、フィルターできる箇所が複数あります。

フィルターしたい箇所や項目に合わせて、フィルターするVBAコードを使い分けていきましょう。

参考になればと思います。最後までご覧くださいまして、ありがとうございました。

関連する記事から探す

カテゴリから探す

カテゴリから見たい項目を探すと便利ですよ。

サイト内を検索する

↓キーワードを入力する

アーカイブから探す