【ExcelVBA】オートフィルタでデータ抽出(日付・期間・時間帯・動的フィルタ)
目次
テスト対象のテーブル
テスト対象テーブルを使いたい場合は、↓テーブルをコピーしてエクセルの 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列 です。
結果
実行前のテーブル
日付指定の注意点
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日より後の日付
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時台のデータが取得できます。でも、それって出荷時間のフィルタではなく、検品日時のフィルタなんですけどね・・・。
グループ化するには、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種類の定数が用意されています。
定数 | 値 | 説明 |
---|---|---|
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 のままで使用してください。
ディスカッション
コメント一覧
まだ、コメントがありません