複雑な条件（複数除外等）のオートフィルター（AutoFilter）
オートフィルターはExcelにおいて重要かつ便利な機能ですが、その使い方の理解が不十分な事が多いようです、
単純な条件の場合は問題ないのですが、条件が複雑になった途端に書き方で相談を受ける事が多々あります。
オートフィルタはExcelのデータベースとしての非常に強力な機能を提供してくれています、VBAで、必要なデータだけに絞り込んで他のシートにコピーしたり、不要なデータを一括で削除したりする場合は、とても高速に処理することができます。VBAでオートフィルタを操作するには、以下のメソッド・プロパティおよびオブジェクトを使用します。
Excelマクロの記録で覚える：第18回.オートフィルタフィルタをやりましょう。まあ、表計算らしい機能ではあります。では、マクロの記録です、データは何でも良いでしょう。１．マクロの記録 ２．フィルタ ３．1のみ選択…たまたま1のデータを入れただけです。４．記録終了 作成れたマクロは、2003の場合は、Selection.AutoFilterSelection.AutoFilterField:=1,
Excelマクロの記録で覚える：第19回.オートフィルタ2前回の続きで、フィルタをやります。以下の表で説明します。では、マクロの記録です。まずは、日付の絞り込みです。１．マクロの記録 ２．A1を選択 ３．フィルタ ４．2011/6/5のみ選択 ５．記録終了 作成れたマクロは… （コメント行は省略します） 上は、2010or2007でのマクロの記録です。
上記ページで、オートフィルターについては概ね解説しています。
しかし、より複雑な条件指定になると、どうにもならない場合があります。
リストとして、
"A","B","C","D","E","F"
があるとして、
"A","C"で絞り込む場合、
Rangeオブジェクト.AutoFilter Field:=1, Criteria1:="=A", Operator:=xlOr, Criteria2:="=C"
テキストフィルター→ユーザー定義フィルターの以下に相当します。
Rangeオブジェクト.AutoFilter Field:=1, Criteria1:=Array("A", "C"), Operator:=xlFilterValues
どうしたらよいでしょうか。
Rangeオブジェクト.AutoFilter Field:=1, Criteria1:="<>A", Operator:=xlAnd, Criteria2:="<>C"
= → <>
Or → And
ここは把握しておいてください。
・・・
無理ですよね、画面でもそんな指定はありませんからね。
書くとしてら、
もっとも、画面の場合は全てチェックが付いている状態から、除外するもののチェックを外すという操作になるでしょうけど。
しかし、VBAの場合はどうしようもありません。
Arrayの中に絞り込む全てのリストをいれるしかないのです。
どうしたらよいでしょうか。
・・・
これはもう無理なんです。
フィルターオプションの画面を再度良く見てください。
3つも指定ができないのです。
そのような機能は無いのですからあきらめてください。
実は、これが出来ないと相談されることが非常に多いのです・・・
でも無理なんです。
Rangeオブジェクト.AutoFilter Field:=1, Criteria1:=Array("B", "D", "F"), Operator:=xlFilterValues
しかし、その、
"B","D","F"
これをどうやって生成するのでしょうか。
シートのリストからユニーク（一意）なデータを作らなければなりません。
もちろんその方法はあります。
例えば、
ExcelマクロVBA入門：第90回.フィルタオプションの設定（AdvancedFilter）
ワークシートの操作「フィルタオプションの設定」のVBAの記述になります、便利な機能ではありますが、そもそも、ワークシートの操作が難しいこともあり、あまり有効に使われていないように感じます。フィルタ詳細設定の使い方 「データ」タブ→「並べ替えとフィルター」グループの「詳細設定」この機能を使う場合のVBAになります。
これでユニークなデータは作成できますので、そこから不必要なものを除外すればよいでしょう。もちろん、
"A","C","E"
を残して、
"B","D","F"
を削除したいというのなら、
"A","C","E"
で絞り込んで、別シートにコピーするという手が簡単です。
その都度、指定方法に悩まされていたのでは生産性が悪いです。
もっと汎用的なな方法を考えましょう。
ズバリ、作業列を追加してください。
最終列の横に作業列を追加し、
条件判定して、「対象」「対象外」等の文字を入れて下さい。
そうすれば、単純なオートフィルターで処理が可能です。
この判定文字の出力に時間がかかるのではと思われるかもしれませんが、
Range("B2:B100000").Value = "=IF(OR(A2=""A"",A2=""C"",A2=""E""),""対象"",""対象外"")"
Range("B2:B100000").Value = Range("B2:B100000").Value
一応、式を抜いて値だけにするところまでやっていますが、
これでも1秒かかりません。（テスト機はCorei5）
ちなみに、処理速度に関しては、
エクセルVBAのパフォーマンス・処理速度に関するレポート
ExcelのVBAは遅い・重いと良く言われることが多いようですが、VBAが遅い・重いのではなく、その書かれたVBAコードが遅いのです。正しい高速化・速度対策をしたコードなら、それほど遅くはありません。むしろ、巨大なスプレッドシートを扱っている事を考えれば、驚異的なパフォーマンスとも言えるのです。
こちらを参考にしてください。やり方はいろいろありますが、なるべく単純化するようにして下さい。
特に作業列の活用は常に念頭においておくようにしてください。
私も受託開発で書く場合は、あまり作業列は使わずに処理します。
まあ、大抵は配列で全て処理するようにしていますね。
配列に関しては、等サイト内のあちこちで記載していますので興味があれば参考にしてください。
第111回.静的配列
VBAを学習していくと必ず配列が出てきます。VBA記述自体は簡単なものですが正しく理解せずに使っている場合も見受けられます。ここでは、配列の概要から入り、VBAでの記述方法を解説していきます。配列とは 配列は、値を格納するために多くの区画を持つ1つの変数です、集合住宅やアパートにたとえられますが、
第112回.動的配列（ReDim）VBAにおける配列の説明として最初に静的配列を解説しました、静的配列では要素数は宣言時点で決められていました。しかし、プログラミングをする上で、実行時点で要素数を決めたい場合や、実行途中で要素数を増減させたい場合が多く出てきます。実行時点で（実行途中で）要素数を増減できる配列を動的配列と呼びます。
第113回.配列に関連する関数マクロVBAで配列を使う上で、必要となるVBA関数がいくつかあります。より便利に配列を活用するために必須となるVBA関数、・LBound関数 ・UBound関数 ・Array関数 ・IsArray関数 ・Join関数 ・Filter関数 以上のVBA関数について解説します。
第114回.セル範囲⇔配列セル範囲をVariant型変数に入れる事で、配列を作成することができます。また、配列をセル範囲にまとめて出力する事も出来ます。これは、マクロVBAを高速処理したい時の必須テクニックになります、マクロの処理が遅い場合は、このテクニックが使えないか検討してください。
配列の使い方について配列に関する記事は多数掲載していますが、今回は配列についての基礎知識をまとめました。配列に関する基本的事項のみを解説しています。配列とは まずシートのセルを考えて下さい。縦１列だけを取り出した場合は、1次元の配列です。
動的2次元配列の次元を入れ替えてシートへ出力（Transpose）動的配列を使い様々な処理をした後にシートへ出力しようとしたとき、縦横が違っている為そのまま出力できません、そもそも、動的配列の要素数をRedimで変更できるのは、最下位の次元のみになります。2次元配列の場合、ReDimmyArray(2,10) ReDimmyArray(2,11) これはOKですが、
日付の検索（配列の使用）日付の検索は、いろいろと面倒です。Findメソッドで検索する場合、表示書式に左右されますので、表示書式を変更しただけで、検索されなくなります。これは、手作業での検索においても同様になりますが、マクロとしてはいかにも不便です。
1次元配列の並べ替え（バブルソート,クイックソート）配列（1次元）の並べ替え方法について、バブルソート、挿入ソート、クイックソートのサンプルになります。元来エクセルには、ワークシートの並べ替え機能があります。ワークシートにデータを書き出して、ワークシートの並べ替え機能を使えるのですが、どうしても、配列をワークシートに途中で書き出すと言うのは面倒なものです。
2次元配列の並べ替え（バブルソート,クイックソート）配列（2次元）の並べ替え方法について、バブルソートとクイックソートのサンプルになります。2次元配列の並べ替えと言えば、まさにワークシートの並べ替え機能になります。本来は、ワークシートにデータを書き出して、ワークシートの並べ替え機能を使えば良いのですが、しかし、どうしても、配列をワークシートに処理途中で書き出すと言うのは面倒なものです。
同じテーマ「マクロVBA技術解説」の記事
シートに数式を設定する時のセル参照の指定方法
標準モジュールとシートモジュールの違い
オートフィルタ（AutoFilter）の使い方まとめ
複雑な条件（複数除外等）のオートフィルター（AutoFilter）
クリップボードを使わないセルのCopy
Rangeの使い方：最終行まで選択を例に
フルパスをディレクトリ、ファイル名、拡張子に分ける
Colorプロパティの設定値一覧（カラー定数、XlRgbColor列挙）
VBAを定型文で覚えよう
VBAこれだけは覚えておきたい必須基本例文10
エクセルVBAでのシート指定方法
新着記事NEW ・・・新着記事一覧を見る
import文（パッケージ・モジュールのインポート）｜Python入門（9月24日）
例外処理（try文）とexception一覧｜Python入門（9月23日）
リスト内包表記｜Python入門（9月22日）
Pythonの引数は参照渡しだが・・・｜Python入門（9月21日）
lambda（ラムダ式、無名関数）と三項演算子｜Python入門（9月20日）
関数内関数（関数のネスト）とスコープ｜Python入門（9月18日）
関数の定義（def文）と引数｜Python入門（9月18日）
組み込み関数一覧｜Python入門（9月17日）
辞書（dict型）｜Python入門（9月16日）
入力規則への貼り付けを禁止する｜VBA技術解説（9月16日）
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.RangeとCellsの使い方｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.マクロって何？VBAって何？｜VBA入門
5.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
6.Range以外の指定方法（Cells,Rows,Columns）｜VBA入門
7.繰り返し処理（For Next)｜VBA入門
8.セルに文字を入れるとは（Range,Value）｜VBA入門
9.とにかく書いてみよう（Sub,End Sub）｜VBA入門
10.マクロはどこに書くの（VBEの起動）｜VBA入門
- ホーム
- マクロVBA応用編
- マクロVBA技術解説
- 複雑な条件（複数除外等）のオートフィルター（AutoFilter）
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。