Office TANAKA - Excel VBA Tips[オートフィルタで日付を絞り込む]

ここでは、日付が入力されているセルを、オートフィルタで絞り込むやり方と注意点などを解説します。先にお断りしておきますが、かなり長くて、ややこしい内容です。

Excelのバージョンによる違い

ここでは、下図のようなデータを例にします。言わなくても分かると思いますが、この表にオートフィルタを設定して、A列の日付を絞り込みます。

【Excel 2010】

まず、Excel 2003で操作をマクロ記録してみます。

【Excel 2003】

Sub Macro1()
    Selection.AutoFilter
    Selection.AutoFilter Field:=1, Criteria1:="2010/8/22"
End Sub

そして、記録されたコードを、オートフィルタを設定する前の表に対して、そのまま実行してみます。もちろん、同じ結果になる・・・はずですよね。

なりません。
では次に、Excel 2007で同じことをやってみます。

【Excel 2007】

Sub Macro1()
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$B$7").AutoFilter Field:=1, Criteria1:="=2010/8/22" _
        , Operator:=xlAnd
End Sub

なんか、Excel 2003に比べて冗長なコードが記録されるようになりましたが、これは、Excel 2007でオートフィルタの機能が変わったからでしょうね。まぁ、いずれにしても、Excel 2003で記録されたコードと、やってることは同じです。
では、さっきと同じように、オートフィルタを設定する前の表で、この記録されたマクロを実行してみましょう。

またもや失敗です。操作を記録したはずなのに、実行すると同じ結果になりません。
じゃぁ、Excel 2010でも試してみます。

【Excel 2010】

Sub Macro1()
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$B$7").AutoFilter Field:=1, Criteria1:="=2010/8/22" _
        , Operator:=xlAnd
End Sub

さぁ、今度はどうでしょう。

え？Excel 2010は、記録されたコードで、同じように絞り込めました。データに違いはありませんし、もちろん操作も同じです。

条件は文字列型か日付型か

上のように、Excel 2007からは、冗長なコードが記録されるようになりました。また、操作によって、記録されるコードも変わってきます。しかし、いずれにしても、オートフィルタで絞り込みをするには、次のようにすればいいです。

Sub Sample1()
    Range("A1").AutoFilter Field:=1, Criteria1:=条件
End Sub

絞り込みたいデータが、名前や住所のような文字列だったら、条件にも文字列を指定します。AutoFilterメソッドのヘルプにも、次のように記載されています。

Criteria1：抽出条件となる文字列("101"など)を指定します

では、日付を絞り込むときには、どんな形式の条件を指定すればいいのでしょう。マクロ記録では文字列形式の条件が記録されましたが、セルに入力されているのはシリアル値です。この条件には、日付型(シリアル値)を指定するという考え方もできます。

上で試したように、Excel 2003とExcel 2007では、条件に文字列型を指定しましたが、これを日付型にしたらどうでしょう。

Sub Sample2()
    Range("A1").AutoFilter Field:=1, Criteria1:=DateValue("2010/8/22")
End Sub

【Excel 2003】

今度はうまくいきました。なるほど、日付型で指定すればいいんですね。画像は割愛しますが、Excel 2007でも日付型を指定することで、ちゃんと絞り込めました。じゃぁ、Excel 2010では・・・

【Excel 2010】

げ！Excel 2010では、日付型を指定したら失敗しました。

ここまでを、まとめてみます。

2003/2007	2010
×文字列(yyyy/m/d) ○日付型(DateValue)	○文字列(yyyy/m/d) ×日付型(DateValue)

なお、上ではDateValue関数を使いましたが、もちろん

Criteria1:=DateSerial(2010, 8, 22)
とか
Criteria1:=CDate("2010/8/22")
とか
Criteria1:=#8/22/2010#

でも同じですし、次のように日付型の変数を使っても同じことです。

Sub Sample3()
    Dim buf As Date
    buf = "2010/8/22"
    Range("A1").AutoFilter Field:=1, Criteria1:=buf
End Sub

表示形式による違い

セルに設定されている表示形式によっても、結果が異なります。

セルに「yyyy/m/d」形式の日付データを入力すると、Excelは自動的に「標準の表示形式」を設定します。

[種類]リストの上2つにある「*2001/3/14」と「*2001年3月14日」は、Windowsの設定に依存する表示形式です。コントロールパネルの[地域と言語]-[形式]タブの設定を変更すると、Excelの表示も変わります。

セルに「yyyy/m/d」形式の日付データを入力すると、自動的にこの「*2001/3/14」が表示形式として設定されます。ちなみに、「m/d」形式の日付を入力すると、自動的に「m"月"d"日"」の表示形式が設定されます。

実は、今まで検証したデータは、すべて「*2001/3/14」の表示形式でした。

表示形式	2003/2007	2010
標準書式 (*2001/3/14)	×文字列(yyyy/m/d) ○日付型(DateValue)	○文字列(yyyy/m/d) ×日付型(DateValue)

では、日付のセルに、別の表示形式が設定されていたらどうでしょう。最初は、Excel 2003でやってみます。

もうひとつの標準表示形式である「*2001年3月14日」を設定してみました。

Sub Sample4()
    Range("A1").AutoFilter Field:=1, Criteria1:=DateValue("2010/8/22")
End Sub

【Excel 2003】

条件に、DateValue関数を使った日付型を指定したら、失敗しました。では、文字列を指定してみます。

Sub Sample5()
    Range("A1").AutoFilter Field:=1, Criteria1:="2010年8月22日"
End Sub

Excel 2007でも同じ結果になります。

【Excel 2007】

Sub Sample4()
    Range("A1").AutoFilter Field:=1, Criteria1:=DateValue("2010/8/22")
End Sub

Sub Sample5()
    Range("A1").AutoFilter Field:=1, Criteria1:="2010年8月22日"
End Sub

Excel 2007も、Excel 2003と同じ結果になりました。では、Excel 2010ではどうでしょう。

【Excel 2010】

Sub Sample4()
    Range("A1").AutoFilter Field:=1, Criteria1:=DateValue("2010/8/22")
End Sub

Sub Sample5()
    Range("A1").AutoFilter Field:=1, Criteria1:="2010年8月22日"
End Sub

Excel 2010では、表示形式に関係なく、条件に文字列形式を指定したときだけ成功します。

表示形式	2003/2007	2010
標準書式 (*2001/3/14)	×文字列(yyyy/m/d) ○日付型(DateValue)	○文字列(yyyy/m/d) ×日付型(DateValue)
標準書式 (*2001年3月14日)	○文字列(yyyy年m月d日) ×日付型(DateValue)	○文字列(yyyy年m月d日) ×日付型(DateValue)

では、標準ではない、その他の表示形式が設定されていたらどうでしょう。

【Excel 2003】

標準ではない「3月14日」という表示形式を設定しました。このデータに対して、先と同じ"文字列型"と"日付型"の条件で絞り込んでみます。もう、面倒くさいので画像は割愛しますが、結果は次のようになります。

表示形式	2003/2007	2010
標準書式 (*2001/3/14)	×文字列(yyyy/m/d) ○日付型(DateValue)	○文字列(yyyy/m/d) ×日付型(DateValue)
標準書式 (*2001年3月14日)	○文字列(yyyy年m月d日) ×日付型(DateValue)	○文字列(yyyy年m月d日) ×日付型(DateValue)
任意書式 (3月14日)	○文字列(m月d日) ×日付型(DateValue)	○文字列(m月d日) ×日付型(DateValue)

以上のことをまとめると、次のようになります。

Excel 2010は、表示形式にかかわらず、条件に文字列型を指定する
Excel 2003/2007で、「*2001/3/14」の表示形式が設定されているときは日付型を指定する
Excel 2003/2007で、「*2001/3/14」ではない表示形式が設定されているときは文字列型を指定する

設定されている表示形式を調べる

ややこしいですね。特に

Excel 2003/2007で、「*2001/3/14」の表示形式が設定されているときは日付型を指定する
Excel 2003/2007で、「*2001/3/14」ではない表示形式が設定されているときは文字列型を指定する

という仕様は、とても使いにくいです。だからExcel 2010では、表示形式にかかわらず文字列型で統一したのでしょうか。しかし、世の中Excel 2010ばかり使っているわけではありませんし、世間でExcel 2003/2007が使われなくなる日は、当分こないでしょう。では、Excel 2007で次のようなデータが入力されているとき、どんなマクロで対応したらいいでしょう。

【Excel 2007】

Excel 2007で「yyyy/m/d」形式の日付ですから、これは日付型を指定するんでしたね。やってみます。

Sub Sample6()
    Range("A1").AutoFilter Field:=1, Criteria1:=DateValue("2010/8/22")
End Sub

あれ？「2010/8/22」は3つあるのに、1つしか表示されません。では、文字列型を指定したらどうでしょう。

Sub Sample6()
    Range("A1").AutoFilter Field:=1, Criteria1:="2010/8/22"
End Sub

もう、何が何だか分かりませんね。実はこのデータには、次のような表示形式が設定されていました。

セル範囲A2:A4には、標準書式の「*2001/3/14」を設定し、セル範囲A5:A7には標準書式ではない「2001/3/14」を設定していたんです。でも、見た目は変わりません。複数のブックからデータを寄せ集めたり、異なる人が入力したデータなどでは、こういうこともあります。なんつっても、見た目は変わらないですし「何か問題でも？」というようなデータです。でも、マクロで操作するときには大問題なのです。

セルに表示されている表示形式を調べてみましょう。表示形式は、NumberFormatプロパティかNumberFormatLocalプロパティで取得できます。両者は、日本語版(という表現は正しくありませんが)のExcelで作成したブックを、別の言語版Excelで使うようなとき違いがあります。

Sub Sample7()
    Dim msg As String
    msg = "A3:" & Range("A3").NumberFormat & vbCrLf & _
          "A5:" & Range("A5").NumberFormat
    MsgBox msg
End Sub

[NumberFormatプロパティ]

Sub Sample7()
    Dim msg As String
    msg = "A3:" & Range("A3").NumberFormatLocal & vbCrLf & _
          "A5:" & Range("A5").NumberFormatLocal
    MsgBox msg
End Sub

[NumberFormatLocalプロパティ]

まぁ、なんとなく違いを見つけることはできそうです。しかし、いずれにしても、日付の表示形式が統一されていないと、正しい絞り込みはできません。こうなったら、オートフィルタで絞り込む前に、表示形式を統一してしまいましょうか。

標準の表示形式にするのなら

Sub Sample8()
    Range(Range("A2"), Cells(Rows.Count, 1)).NumberFormat = "m/d/yyyy"
    Range("A1").AutoFilter Field:=1, Criteria1:=DateValue("2010/8/22")
End Sub

または

Sub Sample8()
    Range(Range("A2"), Cells(Rows.Count, 1)).NumberFormatLocal = "yyyy/m/d"
    Range("A1").AutoFilter Field:=1, Criteria1:=DateValue("2010/8/22")
End Sub

標準ではない表示形式にするのなら

Sub Sample8()
    Range(Range("A2"), Cells(Rows.Count, 1)).NumberFormat = "yyyy/m/d;@"
    Range("A1").AutoFilter Field:=1, Criteria1:="2010/8/22"
End Sub

みたいな感じですかね。

いずれにしても、オートフィルタで日付を絞り込むのは大変だということです。

Excel 2007の新機能で絞り込む

さて、ここから話は変わります。
Excel 2007から、オートフィルタの機能が変わりました。今回のように、日付データを絞り込むとき、特定の期間を簡単に指定できるようになったんです。

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

Sub Sample9()
    Range("A1").AutoFilter Field:=1, _
                           Criteria1:=xlFilterAllDatesInPeriodJanuary, _
                           Operator:=xlFilterDynamic
End Sub

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

定数	値	説明
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月

また、Excel 2007からは、3つ以上の条件で絞り込むことが可能になりました。たとえば、文字列のデータを絞り込むには、次のようにします。

これをマクロで実現するには、次のようなコードを使います。

Sub Sample10()
    Range("A1").AutoFilter Field:=1, _
                           Criteria1:=Array("田井中", "秋山", "中野", "平沢"), _
                           Operator:=xlFilterValues
End Sub

引数Criteria1に、絞り込みたい条件を配列で指定します。そして、引数Operatorには定数xlFilterValuesを指定します。
では、このやり方で、日付を絞り込むにはどうしたらいいでしょう。

このときは、次のようにします。

Sub Sample11()
    Range("A1").AutoFilter Field:=1, _
                           Operator:=xlFilterValues, _
                           Criteria2:=Array(1, "2009/11/1", 1, "2010/1/1", 1, "2010/3/1")
End Sub

引数Operatorに定数xlFilterValuesを指定するのは同じですが、条件の配列は、Criteria1ではなくCriteria2に指定します。配列の書式は次の通りです。

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

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

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

上記のSample11プロシージャでは、次の意味になります。

もちろん、次のような指定も可能です(結果はともかく)。

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