1. Home » 
2. Excel講座の総目次 » 
3. Excel VBA入門：目次 » 
4. オートフィルタ(AutoFilter)でのデータ抽出

オートフィルタ(AutoFilter)でのデータ抽出:Excel VBA入門

オートフィルタ(AutoFilterメソッド)でのデータ抽出　　　Topへ

• データリストから必要なデータを表示し、他のデータは非表示にします。
• expression.AutoFilter(Field, Criteria1, Operator, Criteria2, VisibleDropDown)
  • Field
    • 必ず指定します。フィルタの対象となるフィールド番号を整数で指定します。
      フィールド番号は、リストの左端列が「1」となります。
  • Criteria1
    • 省略可能。抽出条件となる文字列 ("101" など) を指定します。
      • "=" と指定すると、空白セルが抽出されます。
      • "<>" と指定すると空白以外のフィールドが抽出されます。
      • この引数を省略すると、抽出条件はALLになります。
      • 引数OperatorにxlTop10Itemsが指定されている場合は、引数Criteria1に項目数を指定します(たとえば "10")。
  • Operator
    • 省略可能です。XlAutoFilterOperatorクラスの定数を使用します。

      • メンバ		値
        xlAnd (既定値)	And条件	1
        xlOr	Or条件	2
        xlTop10Items	トップテン	3
        xlBottom10Items		4
        xlTop10Percent		5
        xlBottom10Percent		6

      • 引数 Criteria1 および Criteria2 で xlAnd と xlOr を使って組み合わせて、複合抽出条件を指定します。
  • Criteria2
    • 省略可能です。2 番目の抽出条件となる文字列を指定します。
      引数 Criteria1 および引数 Operator と組み合わせて使い、複合抽出条件を指定します。
  • VisibleDropDown
    • 省略可能です。
      • True：（既定値）フィルタのフィールドのオートフィルタのドロップダウン矢印を表示します。
      • False：フィルタのフィールドにオートフィルタのドロップダウン矢印を非表示にします。

オートフィルタでの抽出例

• 下記の表でオートフィルタを使用してみます。
  

  出席番号	氏名	国語	算数	理科	社会	合計	順位	評価
  1001	佐藤	20	51	48	46	165	9	不可
  1002	鈴木	56	64	67	59	246	5	良
  1003	高橋	89	92	97	81	359	1	優
  1004	田中	71	78	75	85	309	4	良
  1005	渡辺	25	34	45	54	158	10	不可
  1006	伊藤	48	56	42	52	198	7	可
  1007	山本	92	88	84	76	340	2	優
  1008	中村	84	89	76	84	333	3	良
  1009	小林	61	59	65	54	239	6	可
  1010	加藤	34	47	38	49	168	8	可

1. 合計が300以上のデータを抽出します。

   • Sub Macro1() 　　With Worksheets("Sheet1") 　　　　.Range("A1").AutoFilter _ 　　　　　　Field:=7, Criteria1:=">=300" 　　End With End Sub

   • 実行結果
     
2. 合計が200以上、(And) 、300未満のデータを抽出します。

   • Sub Macro2() 　　With Worksheets("Sheet1") 　　　　.Range("A1").AutoFilter _ 　　　　　　Field:=7, _ 　　　　　　Criteria1:=">=200", Operator:=xlAnd, _ 　　　　　　Criteria2:="<300" 　　End With End Sub

   • 実行結果
     

抽出データをコピーして別シートへ貼付ける　　　Topへ

• 抽出されたデータは「可視セル」(条件外のデータは非表示になっている)なので、可視セルをコピーして、貼付けます。
  A1セルを含むアクティブセル領域（CurrentRegion）に対してSpecialCellsメソッドで可視セルのみを選択しています。

  • Sub Macro3() 　　With Worksheets("Sheet1") 　　　　.Range("A1").AutoFilter _ 　　　　　　Field:=7, _ 　　　　　　Criteria1:=">=200", Operator:=xlAnd, _ 　　　　　　Criteria2:="<300" 　　　　.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _ 　　　　　　Worksheets("Sheet2").Range("A1") 　　End With End Sub

  • 実行結果：Sheet2の状態
    
• 列幅が初期状態なので、自動調整してみます。

  • Sub Macro3_1() 　　With Worksheets("Sheet1") 　　　　.Range("A1").AutoFilter _ 　　　　　　Field:=7, _ 　　　　　　Criteria1:=">=200", Operator:=xlAnd, _ 　　　　　　Criteria2:="<300" 　　　　.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _ 　　　　　　Worksheets("Sheet2").Range("A1") 　　End With 　　Worksheets("Sheet2").Activate 　　Columns("A:H").EntireColumn.AutoFit End Sub

  • 実行結果(Sheet2)
    
• 抽出条件をコード内に書いていますが、インプットボックスを利用して入力するようにしたいと思います。

  • Sub Macro3_2() 　　Dim myRL As Long 　　Dim myRH As Long 　　　　Worksheets("Sheet2").Range("A:I").Delete　’抽出先Sheet2のA:I列を削除 　　　　myRL = Application.InputBox(prompt:="以上の数値を入力しなさい", Type:=1)　　'下限値の入力 　　　　myRH = Application.InputBox(prompt:="未満の数値を入力しなさい", Type:=1)　　'上限値の入力 　　　　With Worksheets("Sheet1") 　　　　　　.Range("A1").AutoFilter _ 　　　　　　　　Field:=7, _ 　　　　　　　　Criteria1:=">=" & myRL, Operator:=xlAnd, _ 　　　　　　　　Criteria2:="<" & myRH 　　　　　　.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _ 　　　　　　　　Worksheets("Sheet2").Range("A1") 　　　　End With 　　　　With Worksheets("Sheet2") 　　　　　　.Activate 　　　　　　.Columns("A:I").EntireColumn.AutoFit 　　　　End With 　　　　Worksheets("Sheet1").Range("A1").AutoFilter　'---Sheet1のオートフィルターを解除する End Sub

  • 実行画面
  1. 以上の値を 150 と入力します。
     
  2. 未満の値を 310 と入力しました。
     
  3. Sheet2に150以上、310未満のデータが抽出されました。
     

オートフィルタを解除する　　　Topへ

• AutoFilterMode = False　を実行すると解除できます。
• AutoFilterMode プロパティで状態を調べることができます。
  • FilterModeプロパティではAutoFilterがONでもデータが抽出されていないどきはFalseのままですので、AutoFilterModeプロパティを利用します。
  • If Worksheets("Sheet1").AutoFilterMode = True Then
    　　MsgBox "フィルタ モードがONです"
    Else
    　　MsgBox "フィルタ モードはOFFです"
    End If
    ↓
    With Worksheets("Sheet1")
    　　If .AutoFilterMode Then
    　　　　MsgBox "フィルタ モードがONです"
    　　Else
    　　　　MsgBox "フィルタ モードはOFFです"
    　　End If
    End With
• 上のMacro3に追加してみます。

  • Sub Macro4() 　　With Worksheets("Sheet1") 　　　　If .AutoFilterMode Then 　　　　　　.AutoFilterMode = False 　　　　End If 　　　　.Range("A1").AutoFilter _ 　　　　　　Field:=7, _ 　　　　　　Criteria1:=">=200", Operator:=xlAnd, _ 　　　　　　Criteria2:="<300" 　　　　.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _ 　　　　　　Worksheets("Sheet2").Range("A1") 　　　　.AutoFilterMode = False 　　End With End Sub

• 画面がちらつくのでマクロ実行中は画面表示を停止します。

  • Sub Macro5() 　Application.ScreenUpdating = False 　　With Worksheets("Sheet1") 　　　　If .AutoFilterMode Then 　　　　　　.AutoFilterMode = False 　　　　End If 　　　　.Range("A1").AutoFilter _ 　　　　　　Field:=7, _ 　　　　　　Criteria1:=">=200", Operator:=xlAnd, _ 　　　　　　Criteria2:="<300" 　　　　.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy _ 　　　　　　Worksheets("Sheet2").Range("A1") 　　　　.AutoFilterMode = False 　　End With 　Application.ScreenUpdating = True End Sub

日付を条件とするときの注意点　　　Topへ

• 日付データを抽出条件とするときには、データの表示形式と関係してきます。
  • 標準の日付の表示形式の時
    Excel2003以前とExcel2007の場合は抽出条件にCriteria1:=DateValue("2006/5/1")　のようにシリアル値で指定します。
    Excel2010ではCriteria1:="2006/5/1"　のように文字列とします。
  • 標準の表示形式でないときは、文字列で表示形式と同じ書式とします。これはバージョンによる違いはありません。
• どのようなコードにすればよいのか?
  • データの表示形式を取得して、その表示形式に合わせた条件にすればよさそうです。
    ただし、データの表示形式が統一されているのが前提条件となります。
  • コードの例
    A列に日付データがあるケースです。
    • 2010/5/1　という文字列をシリアル値に直して、A2セルの表示形式にFormat関数で合わせています。

    Sub Macro6() 　　With Worksheets("Sheet1") 　　　　.Range("A1:G20").AutoFilter _ 　　　　　　Field:=1, Criteria1:=Format(DateValue("2010/5/1"), .Range("A2").NumberFormatLocal) 　　End With End Sub

スポンサードリンク

PageViewCounter
　
　Since2006/2/27