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

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

「と等しい」で絞り込む

まずは、数値を「と等しい」で絞り込みます。「より大きい/小さい」とか「○から×の間」などの範囲で絞り込むのとは異なりますから、注意が必要です。ここでは、下図のような表を例にします。

この表を「2列目が"100"と等しい」で絞り込むには次のようにします。

Sub Macro1()
    Range("A1").AutoFilter 2, "100"
End Sub

実際には、条件を指定する引数Criteria1に、次のように数値をそのまま(ダブルコーテーションで囲まずに)指定してもうまくいきます。

Sub Macro1()
    Range("A1").AutoFilter 2, 100
End Sub

しかし、引数Criteria1には文字列形式の値を指定するのがルールなのですから、ここは省略せずダブルコーテーションで囲みましょう。

さて、問題はここからです。下図は、B列に表示形式を設定しました。

セルの中には純粋の数値である「100」などが値として入力されていますが、表示形式の結果、セルには「100円」などと表示されています。

このとき、B列を下図のように絞り込むには、どうしたらいいでしょう。

これ、2通りの考え方ができます。まずは、表示形式を設定したとはいえ、セルの中には純粋の数値「100」が値として入っているのだから、引数Criteria1には"100"を指定すればいいと。

Sub Macro1()
    Range("A1").AutoFilter 2, "100"
End Sub

あるいは、別の考え方もできます。セルの中には値として「100」が入っているのだけど、セルに見えている文字が「100円」なのだから、引数Criteria1には"100円"と指定しなければいけないと。

Sub Macro1()
    Range("A1").AutoFilter 2, "100円"
End Sub

実際にやってみましょう。まずは最初の考え方である「セルに入力されている値"100"を指定する」コードです。結果は次のとおり…

うまくいかないんです。正解は「セルに表示されている文字列"100円"を指定する」です。この件、ほとんど知られていませんので、よ～く覚えておいてください。Excelのオートフィルタは、セルに入力されている値ではなくセルに見えている文字列で絞り込むんです。これ、実は手動操作のときは、それほど意識をしません。なぜなら、▼ボタンをクリックすると

リストには、表示形式が反映された「見えている文字列」がリストアップされるからです。手動操作では、ただそれを選択するだけなので、自分で選択したものが"入力されている値"なのか"見えている文字列"なのかを意識しません。しかし、VBAでオートフィルタを使うときは、両者が大きな違いとなります。この件、しっかり認識してください。

これは、実務でよくやる「数値の桁区切り」でも同じです。

上図のように桁区切りの表示形式が設定されているケースでは「1,000」のようにカンマ(,)まで含めて指定しなければ失敗します。

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

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

この「オートフィルタは"見えている文字列"で絞り込む」という大原則を理解していないと、次のマクロが正常に動作しないとき、その原因を突き止めるのは困難です。

Sub Macro3()
    Dim A As String
    A = InputBox("数値を入力してください")
    Range("A1").AutoFilter 2, A
End Sub

この場合でしたら、次のようにしなければなりません。

Sub Macro3()
    Dim A As String
    A = InputBox("数値を入力してください")
    Range("A1").AutoFilter 2, Format(A, "#,##0")
End Sub

「より大きい/小さい」や範囲で絞り込む

「100より大きい」とか「100から200の間」などで絞り込む方法です。ただし！ここで注意が必要です。上記で解説したように、表示形式が反映された「見えている文字列」を指定するのは「と等しい」のときだけです。指定した数値ドンピシャ、イコールで絞り込むときは、表示形式が反映された「見えている文字列」を指定します。ドンピシャのときだけです。「100より大きい」とか「100から200の間」など範囲を条件に指定するときは、その限りではありません。表示形式は関係ないです。Excelが数値と認識できるものを指定すればいいです。この違いを、正しく理解してくださいね。

上図の表で解説します。まずは「B列が"300より大きい"」で絞り込むには次のようにします。

Sub Macro4()
    Range("A1").AutoFilter 2, ">300"
End Sub

もちろん"300以上"でしたら、次のように書きます。

Sub Macro4()
    Range("A1").AutoFilter 2, ">=300"
End Sub

演算子(記号)の順番に注意してください。不等号の右側にイコール(=)です。イコール(=)は右側に書きます。これを間違えて次のように書くと

Sub Macro4()
    Range("A1").AutoFilter 2, "=>300"
End Sub

正しく動作しません。

では、次のように表示形式が設定されていたら。

先にも書きましたが「見えている文字列」を条件に指定しなければいけないのは、その数字ドンピシャのときだけです。今回のように「より大きい」とか「○から×の間」のような範囲を指定するときは、Excelが内部でその範囲を計算してくれますので、Excelが理解できる形式の条件を指定すればいいです。「B列が"3000より大きい"」でしたら、次のどちらでも正常に動作します。

Sub Macro5()
    Range("A1").AutoFilter 2, ">3000"
End Sub

Sub Macro5()
    Range("A1").AutoFilter 2, ">3,000"
End Sub

まぁ、一般的には「>3000」でしょうね。ここにカンマ(,)を書くのは美しくありません。

「○から×の間」のような範囲を条件に指定するときは、引数Criteria1と引数Criteria2に2つの条件を指定します。「B列が"3000より大きい"かつ"6000より小さい"」は、次のように書きます。

Sub Macro6()
    Range("A1").AutoFilter 2, ">3000", xlAnd, "<6000"
End Sub

このように、引数Criteria1と引数Criteria2に2つの条件を指定するときは、3番目の引数Operatorに「xlAnd」や「xlNo」を記述するのですが、よく、次のようなコードを散見します。

Sub Macro6()
    Range("A1").AutoFilter 2, ">3000", xlAnd
End Sub

まぁ、これくらいシンプルには書いていないんですけど、要するに1つの条件(引数Criteria1)しか指定していないのに、次の引数Operatorを指定しているコードです。これ、意味ないです。意味ないんですけど、すごくよく見かけます。なんで多くの人がそんな間違いをするんだろう？って、ずっと疑問だったのですけど、最近になって原因が分かりました。たとえば「B列が"3000より大きい"」で絞り込む動作をマクロ記録すると、次のようなコードが記録されます。

Sub Macro7()
    ActiveSheet.Range("$A$1:$B$7").AutoFilter Field:=2, Criteria1:=">3000", _
        Operator:=xlAnd
End Sub

これですね。この、マクロ記録したコードを意味も分からず、何も考えずに、調べることもせず、そのまんま使うから変なマクロになるんです。私のセミナーで毎回言っていますけど、マクロ記録で得られるコードは"正解"では決してありません。あくまで"ヒント"です。記録されたコードには無駄も多いですし、マクロとしては意味のない記述も多く含まれます。マクロは、みなさん自身が、みなさんなりの思いを込めたストーリーです。「手動操作の高速化」ではありません。だから、マクロ記録したコードをそのまま使わないでください。