Office TANAKA - Excel VBA Tips[オートフィルタ[書き方の基本]]

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

まずは、オートフィルタを操作するマクロの基本的な書き方を解説します。

対象には1つのセルを指定する

オートフィルタを操作するには、AutoFilterメソッドを使います。

最初のポイントは「対象のセル範囲」に、どこを指定するかです。たとえば、下図のような表を「1列目を"田中"」で絞り込むとき、その操作をマクロ記録すると、次のようなコードが記録されます。

Sub Macro1()
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$C$10").AutoFilter Field:=1, Criteria1:="田中"
End Sub

階層構造を省略したRangeオブジェクトは、アクティブシートのセルを表しますので「ActiveSheet」は不要です。それより問題なのは、オートフィルタを設定する表を「Range("$A$1:$C$10")」のように、アドレスで特定している点です。しかも、意味のない絶対参照で。ちなみに、Rangeのヘルプにちゃんと書いてあります。Rangeで指定するアドレスを絶対参照にするのは意味がないって。いずれにしても「Range("$A$1:$C$10")」というのは、表全体を表しています。もちろんこれでも動作します。しかし、ここには、表全体のアドレスではなく、表内のセルを1つだけ指定することをお勧めします。もし、対象の表がセルA1から始まっているのでしたら「Range("A1")」と書けばいいです。

手動操作でオートフィルタを設定するとき、毎回いちいち表全体を選択しませんよね。表内にアクティブセルを移動すれば、Excelが表全体を自動的に認識して、オートフィルタを設定してくれます。手動操作でそれをやっているのですから、マクロでやって悪いことはありません。それに、この対象に表全体を指定すると、後で面倒になります。なぜなら、オートフィルタは"絞り込んで終わり"ではないからです。オートフィルタは必ず、絞り込んだ後で何かをします。コピーしたり編集したり。だったら、そこまでを想定して記述するべきです。この対象には、表の左上であるセルA1(Range("A1"))を指定する方が、後でメリットがあります。

ではもし、次のように、表の中に空の行が含まれていたら。

本来であれば、その場合は「空の行を削除する」のが正解です。そもそも、オートフィルタで絞り込む元表に、なんで空の行があるんですかと。そもそもそれが、おかしいです。Excelの正しい使い方ではありません。Excelには「入力→計算→出力」という"正しい使い方"があります。その流れを無視すると、とたんにExcelは牙をむきます。難易度が上がります。難しくなります。オートフィルタというのは「入力」の"元データ"に設定する機能です。空の行が存在するというのは、一般的に「そこで区別して"見たい"から」という理由でしょう。"見る"というのは「出力」の表です。空の行が含まれているということは、「出力」に対してオートフィルタを設定しようとしているんです。だから難しくなります。でも、まぁ、そんなこと言っても、そのように間違った使い方をしている人が多いので、一応書いておきます。もし、間に空の行が存在するようなときは、たとえば

    Range(Range("A1"), Cells(Rows.Count, 3).End(xlUp)).AutoFilter

みたく何とかしてください。ね、難しくなったでしょ。

ああ、あと「タイトル(見出し)が複数行あったら？」というのも同様です。タイトルは1行にしてください。オートフィルタを設定する元表は、データベースなんですから。なんでタイトルが2行も3行もあるんですかと。それ、"見る"ためでしょ。それも、Excelの使い方が間違っています。

まず▼ボタンは出さなくていい

先のマクロ記録したコードですが、1行目に

Sub Macro1()
    Selection.AutoFilter
    ActiveSheet.Range("$A$1:$C$10").AutoFilter Field:=1, Criteria1:="田中"
End Sub

「Selection.AutoFilter」が記録されています。これは、表のタイトルにオートフィルタ矢印ボタン(▼ボタン)を表示しろという命令です。手動操作でオートフィルタを設定するとき、一般的には「まず▼ボタンを表示」します。次に、その▼ボタンをクリックして、リストから絞り込みたい項目を選択します。その操作をマクロ記録すると、上のように「まず▼ボタンを表示する」が記録されます。しかし、マクロでオートフィルタを設定するときは、この「まず▼ボタンを表示する」は不要です。やってみれば分かりますが、オートフィルタで絞り込むと、自動的に▼ボタンが表示されます。

名前付き引数名は省略する

上記のように、対象のセルとしてRange("A1")1つだけを指定して、先頭の「Selection.AutoFilter」を消すと、次のようになります。

Sub Macro1()
    Range("A1").AutoFilter Field:=1, Criteria1:="田中"
End Sub

オートフィルタを設定するAutoFilterメソッドには、次の引数があります。

本当は5番目の引数としてVisibleDropDownというのがあるのですが、実務でこの引数を使うことはないでしょうから、気にしないでいいです。ちなみに、VisibleDropDownにFalseを指定すると、その列の▼ボタンが非表示になります。非表示にしてどーすんだ！って話ですね。

さらに、Excel 2016からは6番目の引数としてSubFieldという引数も追加されました。これは、Excel 2016で追加された新しいデータ型「株式」の[量]フィールドや「地理」の[人工]フィールドなどに条件を指定するときに使う引数です。

これも、使う人はいないでしょう。

引数Fieldには、オートフィルタで絞り込む列の位置を数値で指定します。ABCなどの列文字は指定できません。また、引数Fieldに指定する位置は、表の中で左から数えます。引数Criteria1と引数Criteri2には、絞り込む条件を指定します。条件は2つまで指定できます。引数Operatorには、2つの条件を指定したときの「なおかつ(xlAnd)」や「または(xlOr)」などを指定します。

さて、上記のコードでは

Sub Macro1()
    Range("A1").AutoFilter Field:=1, Criteria1:="田中"
End Sub

のように、名前付き引数名が「○○:=」と記述されています。もちろん、これでもいいのですが、この名前付き引数名は省略することをお勧めします。理由は2つあります。1つは「省略できるから」という特性です。AutoFilterメソッドは、引数を左から順番に指定します。飛び飛びで指定することはありません。

引数の1番目に指定するのは必ずFieldであり、2番目は常にCriteria1です。このように、指定する位置が決まっているときは、名前付き引数名「○○:=」を省略できます。この件、詳しく知りたい方は「今さら聞けないVBA[引数名って書かなくていいの？]」をご覧ください。

本当のことを言うと、ごくごく希な使い方として、引数を飛び飛びで指定するやり方もあります。ですが、そんなこと、ほとんどの人はしません。だったら「必ず順番に指定するんだ」と思っていていいです。

名前付き引数名を省略した方がいい理由の2つめは"可読性"です。「1列目が"田中"または"広瀬"である」という条件で絞り込むコードで、名前付き引数名を律儀に書いたパターンと、省略したパターンの両方をお見せします。

Sub Macro1()
    Range("A1").AutoFilter Field:=1, Criteria1:="田中", Operator:=xlOr, Criteria2:="広瀬"
End Sub

Sub Macro1()
    Range("A1").AutoFilter 1, "田中", xlOr, "広瀬"
End Sub

どちらが読みやすいですか？どう見ても、省略した方が読みやすいです。AutoFilterメソッドの引数名は、どれもそこそこ文字数があります。律儀に引数名を記述すると、そのコードで「何をしているのか」がボヤけます。一生懸命読まなければ、何をしているのかが分かりにくいです。対して、引数名を省略すると、何をしているのかが目に飛び込んできます。両者を比べれば、一目瞭然ですね。ですから、名前付き引数名は省略することをお勧めします。