日付で絞り込む

  1. Home
  2. Excel
  3. VBA
  4. Excel VBA Tips

大量のデータを絞り込むには、オートフィルタが便利です。ここでは、VBAからオートフィルタを使い倒すテクニックをご紹介します。なお、ボリュームがありますので、以下の項目にページを分けて解説します。なお、ここで解説するオートフィルタは、通常のワークシートに設定されているとします。テーブルでオートフィルタを操作するときは、また別の考え方が必要です。VBAでテーブルを扱う方法に関しては「VBAでテーブルの操作」をご覧ください。

  1. 書き方の基本
  2. 文字列で絞り込む
  3. 数値で絞り込む
  4. 日付で絞り込む (←このページ)
  5. 色で絞り込む
  6. 作業列で絞り込む
  7. 絞り込んだ結果をコピーする
  8. 絞り込んだ結果を集計する
  9. 絞り込んだ結果の行を操作する
  10. オートフィルタの状況を判定する
  11. 数式のエラーで絞り込む

「と等しい」で絞り込む

日付を「と等しい」で絞り込むのは超難しいです。というか、いろいろなところに"落とし穴"があります。ここでは、さまざまなケースを解説しますが、ポイントになるのは「文字列で絞り込む」や「数値で絞り込む」に書いた

  • 条件は文字列形式で指定する
  • 見えている文字列で指定する

の2点です。日付はどうしても、セルに入力されている値(シリアル値)と表示形式の関係があります。さらに、日付には"日付型"という独特な仕組みもあります。そのへんを正しく理解していないと、思わぬ原因でマクロが正常に動作しなくなりますので注意してください。なお、後述しますが「何日から何日の間」とか「○月の日付だけ」のような絞り込みをするときには、そうした"落とし穴"は少ないです(ないとは言いませんけどw)。

ここでは、次のような表で解説します。

A列には、次のような日付が入力されています。

まずは簡単なところからいきましょう。「A列を"2018/12/26"と等しい」で絞り込みます。

Sub Macro1()
    Range("A1").AutoFilter 1, "2018/12/26"
End Sub

まぁ、これはいいですね。何も難しくありません。でも、実務ではこうした条件を、マクロのコード中に記述していては大変です。実務でこういうことをするときは、条件となる日付を、何らかの方法で毎回指定することが普通です。それには、さまざまな方法があります。InputBoxを表示して、毎回ユーザーに日付を入力してもらうとか。あるいは、UserFormで日付を選択してもらうなどなど。でも、最も簡単な方法のひとつは、条件となる日付をセルに入力してもらい、そのセルの値を条件として使う方法です。やってみましょう。

セルG1に条件となる日付「2018/12/26」を入力しました。ここを変えたら、マクロの結果も変わるようにしたいです。まず思いつくのは、次のようなコードでしょう。

Sub Macro2()
    Range("A1").AutoFilter 1, Range("G1")
End Sub

実行すると、次の結果になります。

はい、いきなり"落とし穴"です。これ、B列の名前とか、C列の数値だったら成功するんです。

Sub Macro2()
    Range("A1").AutoFilter 2, Range("G1")
End Sub


Sub Macro2()
    Range("A1").AutoFilter 3, Range("G1")
End Sub

なぜ日付だけ失敗するのか、その理由を解説します。多少難しい話になりますから、よく分からない方は"解決編"をご覧ください。

まず、条件に指定した「Range("G1")」ですが、これ本当は「Range("G1").Value」という意味です。さて、セルに日付(シリアル値)が入力されているとき、Valueプロパティは何を返すのでしょう。調べてみます。

Sub Macro3()
    MsgBox TypeName(Range("G1").Value)
End Sub

「Date型」です。日本語で言うなら「日付型」です。Valueプロパティは「日付型」を返します。でも！AutoFilterメソッドの引数Criteria1には「文字列型」を指定しなければいけないんですよね。ひとつめの"落とし穴"はここです。Valueプロパティが返す「日付型」を「文字列型」に変換するには、たとえばCStr関数を使います。

Sub Macro4()
    Range("A1").AutoFilter 1, CStr(Range("G1"))
End Sub

よし！できた。なんて安心するのは早いです。"落とし穴"は、こんなもんじゃありません。確かに「2018/12/26」ではうまくいきました。では、ほかの日付ではどうでしょう。今度はセルG1に「2019/3/5」と入力して、さっきのマクロを実行してみます。

すると

失敗します。

なぜでしょう。そもそもValueプロパティは、どんな値を返しているのでしょうか。調べてみます。

Sub Macro5()
    MsgBox Range("G1").Value
End Sub

お分かりいただけただろうか。Valueプロパティが返す「日付型」は、"yyyy/mm/dd"形式なんです。なぜ「2019/3/5」が失敗したかというと、A列を絞り込む条件に「2019/03/05」を指定したからです。「数値で絞り込む」にも書きましたが、ドンピシャ、イコールで絞り込むには「セルに表示されている文字列」を指定しなければいけません。A列に「2019/03/05」と表示されているセルはありません。だから失敗したんです。こうなると、CStr関数で文字列型に変換すればいいという話ではなくなります。

この場合に限っては、じゃ、どーすればいいかというと。要するに、セルG1のValueプロパティを使ったから失敗したんです。Valueプロパティは「セルに入っている値」です。そうではなく、この場合「セルに表示されている文字列」を取得できれば事件解決です。そんなときは、ValueプロパティではなくTextプロパティを使います。

Sub Macro4()
    Range("A1").AutoFilter 1, Range("G1").Text
End Sub

もちろん、この考え方でうまくいくのは「A列の表示形式とセルG1の表示形式が同じ」ケースです。もし、A列とセルG1の表示形式が違っていたら。

もう「セルに表示されている文字列」を表すTextプロパティを使えません。だってA列には「2019年3月5日」と表示されているセルなどないのですから。こうなったら、セルG1の日付を、A列の表示形式に変換してやります。それにはFormat関数を使います。Format関数に関しては「VBAの関数一覧[Format関数]」をご覧ください。

Sub Macro4()
    Range("A1").AutoFilter 1, Format(Range("G1"), "yyyy/m/d")
End Sub

いやいや、待ってください。ここでは「"yyyy/m/d"」と、A列の表示形式があらかじめ分かっていたからいいですが、もしA列の表示形式が分からなかったら？しかたない、そんなときは調べましょう。表示形式はNumberFormatLocalプロパティで取得できます。

Sub Macro4()
    Range("A1").AutoFilter 1, Format(Range("G1"), Range("A2").NumberFormatLocal)
End Sub

ややこしいですか？でも、Excelで日付を扱うというのは、これくらい難しいんですよ。いずれにしても、Format関数を使えば最終的に何とかなりますね。では最後に、年月日の数値が別のセルに分割して入力されているケースをご紹介します。実務では、よく見かける(間違った)使い方です。

まず、絶対にやってはいけないのは「Range("F1") & "/" & Range("G1") & "/" & Range("H1")」のように、文字列結合で日付のような文字列を作るという発想です。これは絶対にダメです。Excelでは、日付・時刻を文字列や数値として扱ってはいけないんです。余談ですけど、だから時間の計算で、60で割ったり24で割ったりしては絶対にいけません。ネットでよく見かけますけど、確実に誤動作します。さて、こんなときはDateSerial関数を使います。DateSerial関数は、年月日の数値からシリアル値を生成する関数です。DateSerial関数に関しては「VBAの関数一覧[DateSerial関数]」をご覧ください。DateSerial関数で生成したシリアル値をFormat関数で加工します。もし、1行で書くのなら次のようになります。

Sub Macro6()
    Range("A1").AutoFilter 1, Format(DateSerial(Range("F1"), Range("G1"), Range("H1")), Range("A2").NumberFormatLocal)
End Sub

ただ、これを1行で書くと、さすがに可読性が劣りますので、無理をせず変数を使った方がいいでしょうね。

Sub Macro6()
    Dim A As Date, B As String
    A = DateSerial(Range("F1"), Range("G1"), Range("H1"))
    B = Range("A2").NumberFormatLocal
    Range("A1").AutoFilter 1, Format(A, B)
End Sub

日付を「と等しい」で絞り込むのは難しいです。ポイントは、次の2点です。

  • 条件は文字列形式で指定する
  • 見えている文字列で指定する

期間や範囲で絞り込む

繰り返しますが、日付を絞り込むとき「表示されている文字列」を指定しなければいけないのは、ドンピシャ、イコールの「と等しい」で絞り込むときです。ここから解説する期間や範囲で絞り込むときは、Excelが日付と認識できる形式を条件に指定すればいいです。

さて、まずは簡単に「A列の日付が"2018年12月"である」という絞り込みをやってみましょう。このとき陥りがちなのは、次のような発想です。

え～と、2018年12月の日付つーことは、たとえば
2018/12/1
とか
2018/12/24
だろ。ってことは
2018/12/*
じゃね？

みたいなw これはダメです。失敗します。セルに数値や日付が入力されている列には、ワイルドカードを使えないからです。そもそも、日付を文字列として考えています。その発想がアウトです。

日付というのは連続している概念です。2018年12月15日の翌日は2018年12月16日であり、2019年5月4日の前日は2019年5月3日です。したがって「2018年12月の日」というのは、次の2つの条件で表せます。

  • 2018年12月1日以上
    であり、なおかつ(AND)
  • 2018年12月31日以下

この2つを条件に指定します。

Sub Macro7()
    Range("A1").AutoFilter 1, ">=2018/12/1", xlAnd, "<=2018/12/31"
End Sub

これは日付を「と等しい」のように指定しているのではなく、2つの日付をExcelに与え、Excelがその範囲を内部で調べているので、条件に指定した">=2018/12/1"や"<=2018/12/31"は、A列の表示形式(見えている文字列)である必要はなく、Excelが日付と認識できればいいです。

期間や範囲を指定する基本的な考え方は以上です。あとはこれを応用し、何とかして開始日と終了日を作るだけです。例として、セルに年と月の数値だけを入力して、その年月の日付で絞り込むコードをご紹介します。

Sub Macro8()
    Dim S As Date, E As Date
    S = DateSerial(Range("F1"), Range("G1"), 1)
    E = WorksheetFunction.EoMonth(S, 0)
    Range("A1").AutoFilter 1, ">=" & S, xlAnd, "<=" & E
End Sub

EOMONTH関数に関しては「この関数はこう使え[EOMONTH 関数]」をご覧ください。

特定の期間や日付で絞り込む

オートフィルタには「今日」や「今月」、あるいは年を問わず「3月」などの条件で日付を絞り込む機能があります。

たとえば「(年は問わず)1月の日付だけ」絞り込むには次のようにします。

Sub Sample9()
    Range("A1").AutoFilter 1, xlFilterAllDatesInPeriodJanuary, xlFilterDynamic
End Sub

この「日付フィルタ」で絞り込むときには、引数Criteria1に次の定数を指定し、引数Operatorに定数xlFilterDynamicを指定します。

定数 値 説明
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月」を表す定数xlFilterAllDatesInPeriodFebrurayは、最後Februrayのスペルが間違って定義されています。Microsoftが、Excel 2007でこの定数を実装するときにスペルを間違えたようですね。定数で指定するときは、みなさんも間違ったスペルを指定するようにしてください。ちなみに、おそらく、このスペルミスってる定数を出すのが恥ずかしかったのでしょうね。Excel 2016からは、この機能をマクロ記録すると、定数ではなく22などの数値が記録されるようになりました。

3つ以上の条件で絞り込む

日付も、文字列や数値と同じように、3つ以上の条件を指定することができます。

このように、3つ以上の条件を指定するときは、たとえば次のようにします。

Sub Macro10()
    Range("A1").AutoFilter _
                Field:=1, _
                Operator:=xlFilterValues, _
                Criteria2:=Array(1, "2019/1/31", 1, "2019/3/31", 2, "2018/12/2")
End Sub

このときの条件は、引数Criteria1ではなく引数Criteria2に指定します。引数Criteria1は使いません。つまり、引数の指定が飛び飛びになります。したがって、この場合は名前付き引数名「○○:=」を省略できません。

引数Operatorには定数xlFilterValuesを指定するのですが、問題は引数Criteria2です。ここには、配列形式で次のように指定します。

Array(数値1,日付1,数値2,日付2,…)

数値には0から5を指定します。この数値は、それぞれ次の意味です。

0：後ろに指定した日付の年
1：後ろに指定した日付の月
2：後ろに指定した日付の日
3：後ろに指定した時刻の時
4：後ろに指定した時刻の分
5：後ろに指定した時刻の秒

上記のコードで指定している配列は、次の意味です。

配列内で指定する日付は、有効な日付形式でなければいけません。年や月だけを指定するからといって「Array(0, "2019")」や「Array(1, "2019/3")」のように指定したり、あるいは「Array(0, "2019/2/31")」など存在しない日付ではエラーになります。ただし、指定する年月日ではない部分は、いつでもいいです。実際に、セルに入力されている日付でなくても、有効な日付形式ならOKです。また、この操作をマクロ記録すると、日付部分が「Array(1, "12/2/2018")」のように「月/日/年」記録されますが、実際に指定するときは「年/月/日」形式でもかまいません。