機能と数式 | VBA | セミナー | オンラインソフト | お問い合わせ | その他
Top > Excel > VBA

オートフィルタで日付を絞り込む



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

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)


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

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

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


ややこしいですね。特に

  1. Excel 2003/2007で、「*2001/3/14」の表示形式が設定されているときは日付型を指定する
  2. 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")」のように「月/日/年」記録されますが、実際に指定するときは「年/月/日」形式でもかまいません。






このエントリーをはてなブックマークに追加