数式のエラーで絞り込む

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

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

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

下図のような表があったとします。

この表を「C列の数式がエラーである」という条件で絞り込んでみます。なお、C列には「A列÷B列」という数式を入力しました。B列のところどころに空欄セルがありますね。空欄セルは0として計算されます。数字を0で割ることはできませんから、「#DIV/0!」というエラーになっています。ちなみにこの「DIV/0!」というのは、Division By Zero(いけね、ついうっかり0で割っちまったぜ！なんてこった！)という意味です。

さて、今回のように、数式結果のエラー値で絞り込むにはどうしたらいいのでしょう。ここは、後のこともありますので、マクロ記録で調べてみます。

Sub Macro1()
    ActiveSheet.Range("$A$1:$C$18").AutoFilter Field:=3, Criteria1:="#DIV/0!"
End Sub

上記のようなコードが記録されました。ゴチャゴチャして読みにくいので、シンプルにまとめると、次のようになります。

Sub Macro1()
    Range("A1").AutoFilter 3, "#DIV/0!"
End Sub

確認したかったのは、条件の指定方法です。なにしろ相手は"数式のエラー"ですから。何か特別な指定をしなければいけないのでは？と。しかし、心配は杞憂に終わりました。「#DIV/0!」というエラー値を、普通に文字列として指定すればいいんですね。これ、大事ですから、ちょっと覚えておいてください。結果は次のようになります。

うむ、問題なさそうです。では念のために、別のエラーでもやってみましょう。今度は「#NAME?」エラーです。

「#NAME?」エラーのところには「SUN関数」を入力しています。そんな関数はありません。この「NAME?」というのは、「はぁ？あんたナニ言ってんの？そんな名前の関数あるわけないでしょ！バカなの？超ウケるんですけど。ぷ～くすくす～(CV:アクア)」という意味です。さあ、この「#NAME?」エラーでの絞り込みですが、まずは手動でやってみましょう。

あれ？全部隠れちゃいました。絞り込みができません。なんじゃこりゃ？って感じです。ちなみに、今回の「数式のエラーで絞り込む」って話は、こんなもんじゃありません。これからも、たくさん"なんじゃこりゃ？"が出てきます。お楽しみにｗ

さて、今の、上手くいかなかった操作をマクロ記録してみると、次のようなコードが記録されます。

Sub Macro2()
    ActiveSheet.Range("$A$1:$C$18").AutoFilter Field:=3, Criteria1:="#NAME~?"
End Sub

条件に指定している文字列をご覧ください。「#NAME?」ではなく「#NAME~?」とチルダがついています。これ、Excelに詳しい方でしたらピ～ンとくるかもしれませんね。そう、セルの検索で「?」や「*」といったワイルドカードを"単なる文字"として検索するときは「~?」や「~*」のようにチルダ(~)をつけます。なお、こうしたワイルドカードとして本来の働きをさせず、「?」や「*」を単なる文字として指定するときに使う、今回のような「~」を"エスケープ文字"と呼びます。この記録されたマクロを実行すると、手動操作と同様に、「#NAME?」での絞り込みは失敗して、全部隠れちゃいます。

ここまでの話をまとめます。

  • 手動操作で、エラーの「#NAME?」で絞り込もうとすると失敗する
  • その失敗する操作をマクロ記録すると、条件には"#NAME~?"が記録される
  • 記録されたコードを、そのまま実行すると、手動操作と同様に失敗する

手動操作で「#NAME?」による絞り込みをしたところ失敗しました。しかし実は、ほかにも「#NAME?」で絞り込む操作があります。セルC1のオートフィルタ矢印ボタン(▼ボタン)をクリックして「数値フィルター」をポイントします。表示されるメニューから「指定の値に等しい」を実行すると、[オートフィルターオプション]ダイアログボックスが開きます。

[抽出条件の指定]ボックスに「#NAME?」と入力して[OK]ボタンをクリックします。実行すると、今度は「#NAME?」で絞り込まれます。

今の、成功した操作をマクロ記録すると、次のコードが記録されます。

Sub Macro3()
    ActiveSheet.Range("$A$1:$C$18").AutoFilter Field:=3, Criteria1:="=#NAME?", Operator:=xlAnd
End Sub

違いが分かりますか？成功したときの条件には「~」がついていません。ちなみに、この記録されたコードでしたら、マクロとして実行したときも成功します。つまり、オートフィルタで、エラー「#NAME?」を条件にして絞り込むとき「~」をつけてはいけないということです。なぜ最初の操作で、条件に「#NAME~?」が記録されるのかは分かりません。手動操作でも失敗するということは、もしかするとバグかもしれませんね。まぁ、昔から、マクロ記録したコードをそのまま実行するとエラーになるケースは他にもありましたから、別に驚きません。マクロ記録なんて、こんなものです。そもそも、エラー「#NAME?」にワイルドカードが含まれているというのが、トラブルの元です。おそらく、大昔にこのエラー値を決めたときに、これを文字列としてフィルタすることを想定しなかったんでしょうね。

何はともあれ「#NAME?」エラーも、そのまま文字列として指定すれば絞り込めると分かりました。しかし、安心するのは早すぎます。ここからは、エラーの種類をさらに増やしましょう。

複数のエラーを一気に絞り込む

上図のC列では、8種類のエラーが発生しています。エラーの種類はともかく、とにかくエラーになっているセルだけを"すべて"抽出したいです。実務では、こうした「数式がエラーになっているデータ」だけをまとめて処理したいことが多いです。しかし、どんな種類のエラーが発生しているかは、事前に分からないです。というよりも、"分からない"という前提で考えた方がいいです。もしかすると「エラー値はすべて#で始まっているのだから、ワイルドカードを使って"#*"みたくすれば、できんじゃね？」と考える方もいらっしゃるでしょう。それは失敗します。なぜなら、ワイルドカードは、セルに文字列が入力されているときしか使えないからです。数式の結果が"#で始まっている"だけで、これは文字列として入力されているのではありません。だから、さっきの「#NAME?」もワイルドカードとは認識されません。「~」をつけてワイルドカードをエスケープさせる必要がない、というのもそういう理由です。

今回のデータでは、C列のエラー値だけで正しく絞り込めれば、A列に太字の10～80が並ぶはずです。何となく予想はできますが、今回も、まずはマクロ記録してみましょう。

Sub Macro4()
    ActiveSheet.Range("$A$1:$C$18").AutoFilter Field:=3, Criteria1:=Array( _
        "#DIV/0!", "#N/A", "#NAME?", "#NULL!", "#NUM!", "#REF!", "#SPILL!", "#VALUE!"), _
        Operator:=xlFilterValues
End Sub

上記のようなコードが記録されました。ちょっと、読みやすく整理します。

Sub Macro4()
    Range("A1").AutoFilter 3, _
        Array("#DIV/0!", "#N/A", "#NAME?", "#NULL!", "#NUM!", "#REF!", "#SPILL!", "#VALUE!"), xlFilterValues
End Sub

リストをオートフィルタで絞り込むには、RangeオブジェクトのAutoFilterメソッドを使います。AutoFilterメソッドには、次のような引数が用意されています。

AutoFilter Field, Criteria1, Operator, Criteria2

条件を指定する引数は、引数Criteria1と引数Criteria2の2つです。2つしかありません。3つめや4つめを指定する引数はありません。条件が2つまでなら、引数Criteria1と引数Criteria2に指定すればいいのですが、1つの列に対して3つ以上の条件を指定する場合は、マクロ記録されたように、指定したい条件を配列形式で、引数Criteria1に指定します。上記のArray関数は、配列を返す関数です。

エラー値の指定にはワイルドカードを使えませんので、このようにすべてのエラー値を、ひとつずつ指定しなければなりませんね。ああ、もちろん、数式がエラーになっているかどうかを判定する"作業列"を作る方が簡単かもしれません。その発想については、下記のページをご覧ください。

作業列で絞り込む

複数のエラー値で絞り込むには、上記のように、すべてのエラー値を配列形式で指定すればいいと。マクロ記録では、そう記録されました。なるほど。じゃ、このマクロ記録されたコードを実行してみましょう。

A列の連番を、よ～く見てください・・・お分かりいただけただろうか・・・30がありません。つまり「#NAME?」が漏れています。またお前かよ！ったく。

今回の、不思議な現象も整理してみましょう。

  • エラー値で絞り込むには、エラー値を文字列として指定する
  • 1つの列を3つ以上の項目で絞り込むときは、条件に配列を指定する
  • 複数のエラー値で絞り込む操作をマクロ記録すると、そのとおりに記録される
  • しかし、記録されたコードを実行すると「#NAME?」だけが対象外となる

検証のために、条件を減らしてみましょう。

Sub Macro5()
    Range("A1").AutoFilter 3, _
        Array("#DIV/0!", "#N/A", "#NAME?"), xlFilterValues
End Sub

結果は下図のとおり。やはりダメでした。

では、もうひとつ減らして、条件を2つにしてみましょう。このような配列で指定する方法は、何も「必ず条件を3つ以上してしなければいけない」わけではありません。配列として指定する要素は、別に1つでも2つでも同じことです。次のコードで試してみます。

Sub Macro6()
    Range("A1").AutoFilter 3, _
        Array("#DIV/0!", "#NAME?"), xlFilterValues
End Sub

おおっ！出ました。「#NAME?」で絞り込めました。これ、条件が2つなら成功します。でも、3つ以上指定すると「#NAME?」での絞り込みは失敗します。ひとつずつ減らして確認しました。でも、待ってください。これって

Sub Macro7()
    Range("A1").AutoFilter 3, "#DIV/0!", xlOr, "#NAME?"
End Sub

と同じことですよね。もちろん、この書き方でも成功します。つまり、条件に配列を指定するとき、配列の要素数が3以上だった場合、その要素内に「#NAME?」が含まれていても無視されてしまう、ということです。最後も、まとめます。

  • エラー値で絞り込むにはエラー値を文字列として指定する
  • 「#NAME?」の絞り込みをマクロ記録すると「"NAME~?」と記録されるが、この「~」は不要
  • "すべてのエラー値"など複数の条件で絞り込むには、エラー値をひとつずつ配列形式で指定する
  • ただし「#NAME?」は同時に3つ以上の条件を指定すると失敗する
  • 「#NAME?」で絞り込むときは、条件を2つまでしか指定できない

ということです。

今回のように「すべてのエラー値」で絞り込むのは難しいです。犯人は「#NAME?」です。こいつが足を引っ張っています。なので、上にも書きましたが、作業列を使った方が簡単です。最後にオマケで、そのコードを書いておきます。

Sub Macro8()
    Range(Range("C2"), Cells(Rows.Count, 3).End(xlUp)).Offset(0, 1) = "=ISERROR(C2)"
    Range("A1").AutoFilter 4, True
End Sub