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

2019.05.26ExcelVBA,オートフィルタ

1. テスト対象のテーブル
2. 抽出編（日付）
- 2.1. データ抽出（日付に一致）
- 2.2. データ抽出（日付より以前）
3. 抽出編（日付期間）
- 3.1. データ抽出（日付期間：比較演算子使用）
- 3.2. データ抽出（日付期間：日付の日でグループ化）
4. 抽出編（時間帯）
5. 抽出編（動的フィルタ）

テスト対象のテーブル

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

商品名	出荷日	出荷時間	検品日時	個数
りんご	2019/5/28	8:41:00	2019/5/28 7:21:00	4
みかん	2019/5/29	9:03:00	2019/5/29 8:40:00	3
めろん	2019/5/30	15:21:00	2019/5/30 14:01:00	5
いちご	2019/5/31	9:11:00	2019/5/31 8:55:00	2
みかん	2019/6/1	20:01:00	2019/6/3 18:05:00	1

抽出編（日付）

データ抽出（日付に一致）

Sub Sample()
 
    ThisWorkbook.ActiveSheet.Range("A1").AutoFilter _
        Field:=2, _
        Criteria1:="=2019/5/29"
        
End Sub

※「 Field:=2 」は 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 を使用します。

結果

実行前のテーブル

数値	日付/日時	記述例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種類の定数が用意されています。

定数	値	説明
xlFilterToday	1	今日
xlFilterYesterday	2	昨日
xlFilterTomorrow	3	明日
xlFilterThisWeek	4	今週
xlFilterLastWeek	5	先週
xlFilterNextWeek	6	来週
xlFilterThisMonth	7	今月
xlFilterLastMonth	8	先月
xlFilterNextMonth	9	来月
xlFilterThisQuarter	10	今四半期
xlFilterLastQuarter	11	前四半期
xlFilterNextQuarter	12	来四半期
xlFilterThisYear	13	今年
xlFilterLastYear	14	昨年
xlFilterNextYear	15	来年
xlFilterYearToDate	16	今年の初めから今日まで
xlFilterAllDatesInPeriodQuarter1	17	第1四半期
xlFilterAllDatesInPeriodQuarter2	18	第2四半期
xlFilterAllDatesInPeriodQuarter3	19	第3四半期
xlFilterAllDatesInPeriodQuarter4	20	第4四半期
xlFilterAllDatesInPeriodJanuary	21	1月
xlFilterAllDatesInPeriodFebruray	22	2月
xlFilterAllDatesInPeriodMarch	23	3月
xlFilterAllDatesInPeriodApril	24	4月
xlFilterAllDatesInPeriodMay	25	5月
xlFilterAllDatesInPeriodJune	26	6月
xlFilterAllDatesInPeriodJuly	27	7月
xlFilterAllDatesInPeriodAugust	28	8月
xlFilterAllDatesInPeriodSeptember	29	9月
xlFilterAllDatesInPeriodOctober	30	10月
xlFilterAllDatesInPeriodNovember	31	11月
xlFilterAllDatesInPeriodDecember	32	12月

2月の英語は February ですが、上記の Februray のままで使用してください。

ディスカッション

コメント一覧

まだ、コメントがありません