【ExcelVBA】オートフィルタでデータ抽出(日付・期間・時間帯・動的フィルタ)

ExcelVBA,オートフィルタ

テスト対象のテーブル



テスト対象テーブルを使いたい場合は、↓テーブルをコピーしてエクセルの A1 に貼り付けてください。



商品名出荷日出荷時間検品日時個数
りんご2019/5/288:41:002019/5/28 7:21:004
みかん2019/5/299:03:002019/5/29 8:40:003
めろん2019/5/3015:21:002019/5/30 14:01:005
いちご2019/5/319:11:002019/5/31 8:55:002
みかん2019/6/120:01:002019/6/3 18:05:001

抽出編(日付)

データ抽出(日付に一致)

Sub Sample()
 
    ThisWorkbook.ActiveSheet.Range("A1").AutoFilter _
        Field:=2, _
        Criteria1:="=2019/5/29"
        
End Sub
※「 Field:=2 」は B列 です。

結果


実行前のテーブル


日付指定の注意点 B列の書式は「日付型」であること。また、セルの表示形式が 2019/5/29 であれば、Criteria1:="2019/5/29" の記述にします。 セルの表示形式が 2019/05/29 であれば、Criteria1:="2019/05/29" の記述にします。表示形式と相違があるだけでフィルタにヒットしないためご注意ください。

データ抽出(日付より以前)

Sub Sample()
 
    ThisWorkbook.ActiveSheet.Range("A1").AutoFilter _
        Field:=2, _
        Criteria1:="<=2019/5/29"
        
End Sub
※「 Field:=2 」は B列 です。

結果


実行前のテーブル


5行目
Criteria1:="<=2019/5/29" は 、2019年5月29日以前の日付
Criteria1:=">=2019/5/29" は 、2019年5月29日以後の日付
Criteria1:="=2019/5/29" は 、2019年5月29日の日付
Criteria1:="<2019/5/29" は 、2019年5月29日より前の日付
Criteria1:=">2019/5/29" は 、2019年5月29日より後の日付

抽出編(日付期間)

データ抽出(日付期間:比較演算子使用)

出荷日が 2019年5月 に該当するデータを比較演算子で取得する例
Sub Sample()
 
    ThisWorkbook.ActiveSheet.Range("A1").AutoFilter _
        Field:=2, _
        Criteria1:=">=2019/5/01", _
        Criteria2:="<2019/6/01"

End Sub
※「 Field:=2 」は B列 です。

結果


実行前のテーブル


データ抽出(日付期間:日付の日でグループ化)

出荷日が 2019年5月 に該当するデータをグループ化で取得する例
Sub Sample()
  
  ThisWorkbook.ActiveSheet.Range("A1").AutoFilter _
      Field:=2, _
      Criteria2:=Array(1, "2019/5/29"), _
      Operator:=xlFilterValues
        
End Sub
※「 Field:=2 」は B列 です。
※グループ化は、Criteria2 を使用します。

結果


実行前のテーブル


Criteria2:=Array()の記述方法
Criteria2:=Array(数値, "日付", 数値, "日付", 数値, "日時" ・・・)
Array()内に複数の条件を記述した場合の判定は 論理和(Or) になります。
数値日付/日時記述例1記述例2
0日付の年(Year)Criteria2:=Array(0, "2019/5/29 8:00")Criteria2:=Array(0, "2019/5/29")
1日付の月(Month)Criteria2:=Array(1, "2019/5/29 8:00")Criteria2:=Array(1, "2019/5/29")
2日付の日(Day)Criteria2:=Array(2, "2019/5/29 8:00")Criteria2:=Array(2, "2019/5/29")
3日時の時(Hour)Criteria2:=Array(3, "2019/5/29 8:00")Criteria2:=Array(3, "2019/5/29 8:00:21")
4日時の分(Minute)Criteria2:=Array(4, "2019/5/29 8:00")Criteria2:=Array(4, "2019/5/29 8:00:21")
5日時の秒(Seconds)Criteria2:=Array(5, "2019/5/29 8:00:21")

抽出編(時間帯)

データ抽出(時間帯:比較演算子使用)

出荷時間が 9時台 に該当するデータを比較演算子で取得する例
Sub Sample()

    ThisWorkbook.ActiveSheet.Range("A1").AutoFilter _
        Field:=3, _
        Criteria1:=">=9:00:00", _
        Criteria2:="<10:00:00"

End Sub
※「 Field:=3 」は C列 です。

結果


実行前のテーブル


データ抽出(時間帯:時間の時でグループ化:不可

上の方にも書いた「日付の日でグループ化」ができるのだから、C列の時間に対して「時間の時でグループ化」も可能なように思えますが、実のところできません。 下記のソースは失敗します。時間のみのセルに対してフィルタをしたい場合は、上の項目「データ抽出(時間帯:比較演算子使用)」を参考にしてください。
グループ化するには、array()の中で必ず年月日の指定が必要です。失敗例の Array(3,"9:00:00")は、年月日が指定されていないので失敗します。正しい記述と正しい指定列は Array(3, "2019/5/28 9:00:00") のように記述してD列(検品日時列)を指定します。これにより結果として2019年5月28日の9時台のデータが取得できます。でも、それって出荷時間のフィルタではなく、検品日時のフィルタなんですけどね・・・。

間違った記述方法
Sub Sample()
  
	'これは正しくフィルタリングできません!
  ThisWorkbook.ActiveSheet.Range("A1").AutoFilter _
      Field:=3, _
      Criteria2:=Array(3, "9:00:00"), _
      Operator:=xlFilterValues
        
End Sub
※「 Field:=3 」は C列 です。
※グループ化は、Criteria2 を使用します。

結果(失敗


実行前のテーブル


正しい記述方法の例(arrayの中に年月日の指定を追加)
Sub Sample()
  
  ThisWorkbook.ActiveSheet.Range("A1").AutoFilter _
      Field:=4, _
      Criteria2:=Array(3, "2019/5/31 8:00:00"), _
      Operator:=xlFilterValues
        
End Sub
※上記は、検品日時が 2019年5月31日 8時台 のデータをグループ化して取得します。
※「 Field:=4 」は D列 です。
※グループ化は、Criteria2 を使用します。


データ抽出(時間帯:日時の時でグループ化)

検品日時が 2019年5月29日 8時台 のデータをグループ化で取得する例
年月日の制約を受けないで純粋に8時台のみのデータを取得したい場合は、「データ抽出(時間帯:比較演算子使用)」を、参考にしてください。
Sub Sample()

  ThisWorkbook.ActiveSheet.Range("A1").AutoFilter _
      Field:=4, _
      Criteria2:=Array(3, "2019/5/29 8:00"), _
      Operator:=xlFilterValues
        
End Sub
※「 Field:=4 」は D列 です。
※グループ化は、Criteria2 を使用します。

結果


実行前のテーブル


Criteria2:=Array()の記述方法
Criteria2:=Array(数値, "日付", 数値, "日付", 数値, "日時" ・・・)
Array()内に複数の条件を記述した場合の判定は 論理和(Or) になります。
数値日付/日時記述例1記述例2
0日付の年(Year)Criteria2:=Array(0, "2019/5/29 8:00")Criteria2:=Array(0, "2019/5/29")
1日付の月(Month)Criteria2:=Array(1, "2019/5/29 8:00")Criteria2:=Array(1, "2019/5/29")
2日付の日(Day)Criteria2:=Array(2, "2019/5/29 8:00")Criteria2:=Array(2, "2019/5/29")
3日時の時(Hour)Criteria2:=Array(3, "2019/5/29 8:00")Criteria2:=Array(3, "2019/5/29 8:00:21")
4日時の分(Minute)Criteria2:=Array(4, "2019/5/29 8:00")Criteria2:=Array(4, "2019/5/29 8:00:21")
5日時の秒(Seconds)Criteria2:=Array(5, "2019/5/29 8:00:21")

抽出編(動的フィルタ)

データ抽出(月指定)

出荷日が 5月 のデータを動的フィルタで取得する例
Sub Sample()
    
  ThisWorkbook.ActiveSheet.Range("A1").AutoFilter _
    Field:=2, _
    Criteria1:=xlFilterAllDatesInPeriodMay , _
    Operator:=xlFilterDynamic

End Sub
※「 Field:=2 」は B列 です。
※年の制約は受けません。5月であれば来年・昨年の5月でもヒットします。
※動的フィルタは、 Operator:=xlFilterDynamic が必要です。

結果


実行前のテーブル


データ抽出(動的フィルタを使いつつ絞り込み)

Criteria1 に使用する動的フィルタの定数は、32種類ある中からひとつのみ使用できます。複数使用は不可です。 また、xlFilterDynamic を使用したオートフィルタ内では、その他のフィルタ条件も追加設定できません。 なので、端的に言えば動的フィルタをひとつ使用したら、それ以上の追加設定はできないと言うことになります。しかし、動的フィルタを使用した状態で更に絞り込みたいときは、新しい .Autofilter を宣言すれば可能です。

出荷日が 5月で 出荷時間が 15時以降のデータを取得する例
Sub Sample()
    
  ThisWorkbook.ActiveSheet.Range("A1").AutoFilter _
    Field:=2, _
    Criteria1:=xlFilterAllDatesInPeriodMay, _
    Operator:=xlFilterDynamic '動的フィルタ

  ThisWorkbook.ActiveSheet.Range("A1").AutoFilter _
    Field:=3, _
    Criteria1:=">=15:00:00"

End Sub
※「 Field:=2 」は B列 です。
※「 Field:=3 」は C列 です。
※年の制約は受けません。5月の15時以降であれば、来年・昨年でもヒットします。
※動的フィルタは、 Operator:=xlFilterDynamic が必要です。

結果


実行前のテーブル


動的フィルタの定数(32種類)


動的フィルタには下に書いた32種類の定数が用意されています。
定数説明
xlFilterToday1今日
xlFilterYesterday2昨日
xlFilterTomorrow3明日
xlFilterThisWeek4今週
xlFilterLastWeek5先週
xlFilterNextWeek6来週
xlFilterThisMonth7今月
xlFilterLastMonth8先月
xlFilterNextMonth9来月
xlFilterThisQuarter10今四半期
xlFilterLastQuarter11前四半期
xlFilterNextQuarter12来四半期
xlFilterThisYear13今年
xlFilterLastYear14昨年
xlFilterNextYear15来年
xlFilterYearToDate16今年の初めから今日まで
xlFilterAllDatesInPeriodQuarter117第1四半期
xlFilterAllDatesInPeriodQuarter218第2四半期
xlFilterAllDatesInPeriodQuarter319第3四半期
xlFilterAllDatesInPeriodQuarter420第4四半期
xlFilterAllDatesInPeriodJanuary211月
xlFilterAllDatesInPeriodFebruray222月
xlFilterAllDatesInPeriodMarch233月
xlFilterAllDatesInPeriodApril244月
xlFilterAllDatesInPeriodMay255月
xlFilterAllDatesInPeriodJune266月
xlFilterAllDatesInPeriodJuly277月
xlFilterAllDatesInPeriodAugust288月
xlFilterAllDatesInPeriodSeptember299月
xlFilterAllDatesInPeriodOctober3010月
xlFilterAllDatesInPeriodNovember3111月
xlFilterAllDatesInPeriodDecember3212月
2月の英語は February ですが、上記の Februray のままで使用してください。