ExcelマクロVBA技術解説
複雑な条件(複数除外等)のオートフィルター(AutoFilter)

ExcelマクロVBAの問題点と解決策、エクセルVBAの技術的解説
最終更新日:2015-06-17

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


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


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


ExcelマクロVBA入門:第89回.オートフィルタ(AutoFilter)
オートフィルタはExcelのデータベースとしての非常に強力な機能を提供してくれています、VBAで、必要なデータだけに絞り込んで他のシートにコピーしたり、不要なデータを一括で削除したりする場合は、とても高速に処理することができます。VBAでオートフィルタを適用したり解除,Excelマクロの基礎と応用
Excelマクロの記録で覚える:第18回.オートフィルタ
フィルタをやりましょう。まあ、表計算らしい機能ではあります。Excelマクロの自動記録を使って、エクセルVBAの初心者向け入門解説
Excelマクロの記録で覚える:第19回.オートフィルタ2
前回の続きで、フィルタをやります。以下の表で説明します。Excelマクロの自動記録を使って、エクセルVBAの初心者向け入門解説

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

まずオートフィルターの機能はVBAでは以下の2種類に大別されます。
リストとして、
"A","B","C","D","E","F"
があるとして、
"A","C"で絞り込む場合

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

これは、シート操作では、
テキストフィルター→ユーザー定義フィルターの以下に相当します。
Excelサンプル画像


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

これは、シート操作では、以下に相当します。
Excelサンプル画像


では、

"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)
ワークシートの操作「フィルタオプションの設定」のマクロ記述になります、便利な機能ではありますが、そもそも、ワークシートの操作が難しいこともあり、あまり有効に使われていないようです。データ→フィルタ詳細設定 この機能を使う場合のVBAになります。Excelマクロの基礎と応用
これでユニークなデータは作成できますので、そこから不必要なものを除外すればよいでしょう。
もちろん、
"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コードが遅いのです。正しい高速化・速度対策をしたコードなら、それほど遅くはありません。ExcelマクロVBAの問題点と解決策、エクセルVBAの技術的解説
こちらを参考にしてください。

要は、処理方法次第なのです。
やり方はいろいろありますが、なるべく単純化するようにして下さい。
特に作業列の活用は常に念頭においておくようにしてください。

ただし、開発プロの方の場合は話が別です。
私も受託開発で書く場合は、あまり作業列は使わずに処理します。
まあ、大抵は配列で全て処理するようにしていますね。
配列に関しては、等サイト内のあちこちで記載していますので興味があれば参考にしてください。
第111回.静的配列
配列は、値を格納するために多くの区画を持つ1つの変数です、集合住宅やアパートにたとえられますが、要は、変数の箱を複数つなげたものと理解すれば良いでしょう。1つの箱には、1つの値しか入れられませんが、その箱が複数つながっていますので、結果として、複数の値をいれられるのが,Excelマクロの基礎と応用、エクセルVBAの入門・初級・初心者向け解説
第112回.動的配列(ReDim)
静的配列では、配列の要素数は宣言時点で決められていました、しかし、プログラミングをする上で、実行時点で要素数を決めたい場合や、実行途中で増やしたい場合が多くあります。実行時点で要素数を増減できるのが、動的配列になります。Excelマクロの基礎と応用、エクセルVBAの入門・初級・初心者向け解説
第113回.配列に関連する関数
配列を使う上で、必要となるVBA関数がいくつかあります、より便利に配列を活用するために必須となるVBA関数について解説します。LBound関数 配列の指定された次元で使用できる最小の添字を、長整数型(Long)の値で返します。Excelマクロの基礎と応用、エクセルVBAの入門・初級・初心者向け解説
第114回.セル範囲⇔配列
配列の使い方について
動的2次元配列の次元を入れ替えてシートへ出力(Transpose)
日付の検索(配列の使用)
1次元配列の並べ替え(バブルソート,クイックソート)
2次元配列の並べ替え(バブルソート,クイックソート)




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

シートに数式を設定する時のセル参照の指定方法
標準モジュールとシートモジュールの違い
オートフィルタ(AutoFilter)の使い方まとめ
複雑な条件(複数除外等)のオートフィルター(AutoFilter)
クリップボードを使わないセルのCopy
Rangeの使い方:最終行まで選択を例に
フルパスをディレクトリ、ファイル名、拡張子に分ける
Colorプロパティの設定値一覧
VBAを定型文で覚えよう
VBAこれだけは覚えておきたい必須基本例文10
エクセルVBAでのシート指定方法

新着記事NEW ・・・新着記事一覧を見る

エクセルの日付と時刻のまとめ|エクセル関数超技(3月6日)
Excelシートの複雑な計算式を解析するVBA|VBAサンプル集(2月18日)
VBAクラスの作り方:独自Rangeっぽいものを作ってみた|VBA技術解説(2月16日)
VBAクラスの作り方:列名のプロパティを自動作成する|VBA技術解説(2月14日)
VBAクラスの作り方:列名の入力支援と列移動対応|VBA技術解説(2月11日)
クラスを使って他ブックのイベントを補足する|VBA技術解説(2月6日)
Excelアドインの作成と登録について|VBA技術解説(2月3日)
参照設定、CreateObject、オブジェクト式の一覧|VBA技術解説(1月20日)
VBAでファイルを規定のアプリで開く方法|VBA技術解説(1月20日)
ドキュメントプロパティ(BuiltinDocumentProperties,CustomDocumentProperties)|VBA技術解説(1月19日)

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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数とデータ型(Dim)|ExcelマクロVBA入門
6.マクロって何?VBAって何?|ExcelマクロVBA入門
7.とにかく書いて見よう(Sub,End Sub)|VBA入門
8.繰り返し処理(For Next)|ExcelマクロVBA入門
9.セルに文字を入れるとは(Range,Value)|VBA入門
10.ひらがな⇔カタカナの変換|エクセル基本操作



  • >
  • >
  • >
  • 複雑な条件(複数除外等)のオートフィルター(AutoFilter)

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


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






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

    本文下部へ