複雑な条件（複数除外等）のオートフィルター（AutoFilter）

公開日：2015-06-17　最終更新日：2021-03-08

複雑な条件（複数除外等）のオートフィルター（AutoFilter）

オートフィルターはExcelにおいて重要かつ便利な機能ですが、その使い方の理解が不十分な事が多いようです、
単純な条件の場合は問題ないのですが、条件が複雑になった途端に書き方で相談を受ける事が多々あります。

まず、オートフィルターの一般的な解説としては以下を参考にしたください。

ExcelマクロVBA入門：第89回.オートフィルタ（AutoFilter）

・Range.AutoFilterメソッド・AutoFilterModeプロパティ・AutoFilterオブジェクト・オートフィルタのVBA使用例・日付のフィルタ・オートフィルタまとめ

Excelマクロの記録で覚える：第18回.オートフィルタ

フィルタをやりましょう。まあ、表計算らしい機能ではあります。では、マクロの記録です、データは何でも良いでしょう。１．マクロの記録２．フィルタ３．1のみ選択…たまたま1のデータを入れただけです。４．記録終了作成れたマクロは、2003の場合は、Selection.AutoFilterSelection.AutoF…

Excelマクロの記録で覚える：第19回.オートフィルタ2

前回の続きで、フィルタをやります。以下の表で説明します。では、マクロの記録です。まずは、日付の絞り込みです。１．マクロの記録２．A1を選択３．フィルタ４．2011/6/5のみ選択５．記録終了作成れたマクロは… （コメント行は省略します）上は、2010or2007でのマクロの記録です。

上記ページで、オートフィルターについては概ね解説しています。
しかし、より複雑な条件指定になると、どうにもならない場合があります。

まずオートフィルターの機能はVBAでは以下の2種類に大別されます。

リストとして、
"A","B","C","D","E","F"
これらが、どこかの列に入っているとします。

"A","C"で絞り込む場合

方法1

Rangeオブジェクト.AutoFilter Field:=1, Criteria1:="=A", Operator:=xlOr, Criteria2:="=C"

これは、シート操作では、
「テキストフィルター」→「指定の値に等しい」の以下に相当します。

方法2

Rangeオブジェクト.AutoFilter Field:=1, Criteria1:=Array("A", "C"), Operator:=xlFilterValues

これは、シート操作では、以下に相当します。

"A","C"以外で絞り込む場合

さて、どうしたらよいでしょうか。

方法1

Rangeオブジェクト.AutoFilter Field:=1, Criteria1:="<>A", Operator:=xlAnd, Criteria2:="<>C"

否定形の作成なので、
= → <>
Or → And
ここは把握しておいてください。

方法2

・・・
無理ですよね、画面でもそんな指定はありませんからね。
書くとしたら、

Rangeオブジェクト.AutoFilter Field:=1, Criteria1:=Array("B", "D", "E", "F"), Operator:=xlFilterValues

ということになってしまいます。
画面で選択する場合も当然全てをチェックしなければなりません。
もっとも、画面の場合は全てチェックが付いている状態から、除外するもののチェックを外すという操作になるでしょうけど。
しかし、VBAの場合はどうしようもありません。
Arrayの中に絞り込む全てのリストをいれるしかないのです。

"A","C","E"以外で絞り込む場合

どうしたらよいでしょうか。

方法1

・・・
これはもう無理なんです。

フィルターオプションの画面を再度良く見てください。
3つも指定ができないのです。

そのような機能は無いのですからあきらめてください。
実は、これが出来ないと相談されることが非常に多いのです・・・
でも無理なんです。

方法2

Rangeオブジェクト.AutoFilter Field:=1, Criteria1:=Array("B", "D", "F"), Operator:=xlFilterValues

と書けばよいですよね。
しかし、その、
"B","D","F"
これをどうやって生成するのでしょうか。
シートのリストからユニーク（一意）なデータを作らなければなりません。
もちろんその方法はあります。

例えば、
ExcelマクロVBA入門：第90回.フィルタオプションの設定（AdvancedFilter）

・フィルター詳細設定の使い方・Range.AdvancedFilter メソッド・フィルターオプションの設定の関連記事

これでユニークなデータは作成できますので、そこから不必要なものを除外すればよいでしょう。
もちろん、
"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回.静的配列

・配列とは・静的配列と動的配列・配列の宣言・多次元配列・要素の下限の変更・配列について

第112回.動的配列（ReDim）

・ReDimステートメント・要素数の変更について・配列について

第113回.配列に関連する関数

・LBound関数とUBound 関数・Array関数・IsArray 関数・Join関数・Filter関数・Eraseステートメント

第114回.セル範囲⇔配列

・セル範囲⇔配列の基本VBA ・使用例・配列およびマクロVBAの高速化に関するページ

配列の使い方について

・配列とは・１次元の配列・2次元の配列・3次元以上の配列・動的配列・動的配列・動的配列の要素数の取得・配列使用時の注意

動的2次元配列の次元を入れ替えてシートへ出力（Transpose）

動的配列を使い様々な処理をした後にシートへ出力しようとしたとき、縦横が違っている為そのまま出力できません、そもそも、動的配列の要素数をRedimで変更できるのは、最下位の次元のみになります。2次元配列の場合、ReDimmyArray(2,10) ReDimmyArray(2,11) これはOKですが、

日付の検索（配列の使用）

日付の検索は、いろいろと面倒です。Findメソッドで検索する場合、表示書式に左右されますので、表示書式を変更しただけで、検索されなくなります。これは、手作業での検索においても同様になりますが、マクロとしてはいかにも不便です。

1次元配列の並べ替え（バブルソート,クイックソート）

・検証方法・バブルソート・挿入ソート・クイックソート・最後に

2次元配列の並べ替え（バブルソート,クイックソート）

・検証方法・バブルソート・クイックソート・複数キーでの並べ替えについて・ワークシートを使って並べ替え・・・番外編

同じテーマ「マクロVBA技術解説」の記事

シートに数式を設定する時のセル参照の指定方法

シートに計算結果ではなく、計算式を設定する場合の、セル参照の記述方法について解説します、マクロVBAでは多くの場合、計算結果をセルに入れる事が多いのですが、時に計算式を設定する必要があります、その時の、セル参照の記述が以外に面倒なものです。下の表で説明していきます。

標準モジュールとシートモジュールの違い

・書かれている場所と概要・シートを省略してRangeやCellsと記述した時・他モジュールから使う時・デバッグ時の違い・標準モジュールとシートモジュールの使い分け方

オートフィルタ（AutoFilter）の使い方まとめ

オートフィルタはエクセルの中でもデータ処理において非常に強力なものです、特に大量データの処理には書くことのできない機能となっています。しかし、使い方が難しく、またバージョン違いの影響が大きく、使いずらい物となっていて、問い合わせを受ける事も多いです。

複雑な条件（複数除外等）のオートフィルター（AutoFilter）
クリップボードを使わないセルのCopy

・セルのコピーの基本・「マクロの記録」のコピー・Destinationを指定したコピー・クリップボードを使わないセルのCopy ・値と書式のコピー・書式以外のコピー・クリップボードを使わないセルのCopyのまとめ

Rangeの使い方：最終行まで選択を例に

Rangeの使い方・書き方について、データ最終行まで選択する場合を例に説明します、Rangeの書き方なので、RangeオブジェクトではなくRangeプロパティの解説という事になります。最近続けざまに、以下のようなコードを見かけました。Range("A2",Range("A2"…

フルパスをディレクトリ、ファイル名、拡張子に分ける

ファイルのフルパスを、ディレクトリ、ファイル名、拡張子に分けます。FileSystemObjectを使う方法と、VBA関数（InStrRev）で分けるVBAのサンプルコードになります。FileSystemObject 順に、C:\Users\hogehoge\Desktop サンプル.txt サンプル txt と表…

Colorプロパティの設定値一覧（カラー定数、XlRgbColor列挙）

・Excelのカラーについて・ColorIndex、カラー定数、XlRgbColor列挙、RGB値・システムカラーの定数・サイト内の色関連関連ページ

VBAを定型文で覚えよう

・サンプルVBA ・サンプルVBAの解説・VBAを定型文で覚えるための課題・VBAを定型文で覚えることの目標

VBAこれだけは覚えておきたい必須基本例文10

・1.最終行まで処理・2.後ろから逆順ループ・3.コレクション処理・4.分岐処理・5.セルのコピー・6.VBA関数とワークシート関数・7.オブジェクト変数とWith ・8.ブックを開く・閉じる・9.ファイル一覧・10.テキスト読み書き・VBA必須基本例文10を覚えた後は

エクセルVBAでのシート指定方法

・シートのインデックス番号で指定・シートの名称で指定・シートのオブジェクト名で指定・ブックの保護・VBAでのシート指定方法

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.ひらがな⇔カタカナの変換｜エクセル基本操作
5.繰り返し処理（For Next)｜VBA入門
6.変数宣言のDimとデータ型｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.Findメソッド（Find,FindNext,FindPrevious）｜VBA入門

ホーム
マクロVBA応用編
マクロVBA技術解説
複雑な条件（複数除外等）のオートフィルター（AutoFilter）

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

VBA技術解説
複雑な条件（複数除外等）のオートフィルター（AutoFilter）