Excel フィルタオプション機能～複数条件を設定してかなり複雑な抽出もできる～

Excelのオートフィルタ機能は、データの中で条件に合うものだけをフィルタリングし表示してくれる便利な機能だが、基本的にあまり複雑な条件設定はできない。

上図のオートフィルタでの条件設定ボックスは、2つしか条件を設定できなくて、これをもっと増やしてほしいという要望は非常に多い、はず。

そしてExcel2007以降は、任意の値を一覧からチェックボックスで選択できるようになったのだけど、これで利便性が大きく向上したのは確かだ。

しかしこれは補助機能のようなものであり「田中を含む AND 佐藤を含む and 鈴木を含む OR 山田を含む」といった複雑な設定はできない。

「フィルタオプション」機能

ではExcelにそういうフィルタリングの機能はないのかというと、一応きちんとある。

たぶん知らない人が多いと思うけど、リボンの「データ」タブ→「詳細設定」というボタンで実行する機能だ。
この機能はExcelスキル2003まで「フィルタオプションの設定」という名前の機能だったのだが、普通にこの名前のままの方が分かりやすかったはずだと思う。

だから、以降の解説ではこの機能のことを「フィルタオプション」と呼ぶことにする。
私の頭の中でも完全に「フィルタオプション」の名前で通ってるしね。

ここまで書いといて何だが、この「フィルタオプション」は設定が面倒で、私としてもそう積極的に使いたい機能でもない。
でも知ってれば、周りに差を付けられることは確かだ。

優先度は上げなくても良いので、ぜひ理解に努めてみよう。

例題(1)会社コードで単純にフィルタリング

まず例題だ。

Excelの「データ」シートに、会社ごと売上データをまとめてある。
その中から、会社コードが次に挙げる重要なやつのいずれかと一致するデータだけにフィルタリングしてくれ。

1077,1813,3261,4731,4763,5576,5699,6036,7291,8947,9406

これら会社コードは「条件」シートに縦に並べてあるんでよろしく。

と指示されたとする。上記の会社コードはランダムで、何の法則性とかもない。

オートフィルタで、これらに該当するデータだけ抽出しろなんて言われたら、無理ゲーだ。

まあこの問題に限って言えば、COUNTIF関数と作業列を使うとかいう方法(解説はしない)もあるかもだけど、ここではフィルタオプション機能による方法を解説する。
後々もっと複雑な例題も扱うしね。

条件シートの作成

ではまず、元データが入った「データ」シートで、会社コードってやつのタイトルが「会社CD」となっているのが確認できる。
次に、「条件」シートで会社コードが縦に羅列してあるけどその先頭に、タイトル「会社CD」を正確に転記しよう。

次に、当たり前のようなことだが注意を書いておく。

「条件」シートのタイトル文字「会社CD」を間違うなよ絶対だ。これが最も重要なところ。
条件となる会社コードの文字列を、縦に隙間なく1列に並べること。
タイトル「会社CD」文字を、会社コードの文字列群と離したりせず、引っ付けておくこと。

フィルタオプション機能の使用

さてここまで来たら、「元データ」シートを選択し、データの表を選択した状態で
リボンの「データ」タブ→「詳細設定」
を押そう。

次に出てくる画面で、幾つかチェックボックスなどあるが、ひとまず次のようにしよう。

①「リスト範囲」
→元データの表の範囲を指定。
今回の場合、「元データ」シートのA1:C31の範囲が表になっているので、そこを指定。

②「検索条件範囲」→文字通り、検索条件を列記している範囲を指定。
今回の場合、「条件」シートのA1セルにタイトル「会社CD」を入れてて、検索するコードをA2～A12まで入れているのだけど、必ずタイトル文字から始まって選択しなければならない。

図の通り「条件!$A$1:$A$12」というように、タイトル文字まで含めて過不足なく選択する。
タイトル文字によって、どの項目をフィルタリングするのか判別しないといけないから、何度も言うが必ずタイトル文字まで含めること。

そうして「OK」ボタンかEnterキーを押せば、指定した条件に従って「元データ」シートのデータがフィルタリングされる。

このとき、もともと「元データ」シートの表に設定していたオートフィルタのボタンとかは消えるけど、これは仕方がない。
言ってみればオートフィルタを超えたフィルタリングをしてるわけだからね。

例題(2)金額の条件を加えたフィルタリング

では次の例題。
先程の例題(1)に、1つ条件を加える。次の文章の赤文字の部分だ。

Excelの「データ」シートのデータを、会社コードが次に挙げるもののいずれかと一致するデータだけにフィルタリングする。

1077,1813,3261,4731,4763,5576,5699,6036,7291,8947,9406

また、金額が、コード「1813」「4731」については200万円以上、他のコードについては500万円未満のものだけにフィルタリングしてくれ。

会社コードだけでなく、金額まで条件に加わったわけだ。
さてここで、先程の例題(1)で「条件」シートにおいて、縦にずっと会社コードを列記していったわけだが、これはOR条件での列記になる。

フィルタオプション機能では、条件を縦に書き並べるとOR条件、横に書き並べるとAND条件になるのだ。

今回の問題(2)については、各会社コードについて金額の条件が、AND条件で結びついてくる。

そして金額については、「○○円に等しい」ではなく、「以上」「未満」といった条件がついてくる。これをどう指定するか。

答えとしては上図のように、不等号を組み合わせて書くことになる。
200万円以上という条件は「>=2000000」、500万円未満という条件は「<5000000」と書けば良い。

そして、各会社コードと横に隣接させて書いているが、これにより各会社コードとAND条件で結びついていることを示す。

この「条件」シートに書かれた条件を、文章で無骨に述べていくと、

会社CD=1077で

且つ

金額 < 500万円

または

会社CD=1813で

且つ

金額 ≧ 200万円

または

会社CD=3261で

且つ

金額 < 500万円

・・・・・・・

となる。

ではこの状態で、また先程と同様にフィルタオプション機能を使う。

上図に示したとおりで、

「リスト範囲」→例題(1)に同じ
「検索条件範囲」→「金額」項目まで含めた「条件!$A$1:$B$12」の範囲。当然ここでも、タイトル文字まで含めて過不足なく選択する

こと。

結果の図は省略するが、これにより目的の抽出ができる。

例題(3)名称検索を加えたフィルタリング

最後に、私としては一番使い道が多いと考える例題を示す。
今度は、例題の表を次のように変えてみる。

そして上司からの指示としては、

この中から会社名で探して、会社名が「NTT」関係のものだけにフィルタリングしてくれ

というものを想定する。
ひとまずこれは、NTTという単語で検索すればいいってくらいの意味で捉えることにする。

図で、この指示に該当する箇所には前もってピンクの色を付けておいたけど、もちろん実際の業務ではこんな都合よく色は付いていない。

そしてNTTといっても、「エヌティティ」なんてカタカナで登録してあることもあるし、㈱とか株式会社とかいうのも付いてたりする。
また「エヌ・ティ・ティ」とか半角全角とか、様々なバリエーションを考える必要があるだろう。

こういった、表記のブレなども考慮して、色々な検索パターンを試して名称検索していく作業を「名寄せ」とかいうけどね。

検索条件の書き方

では「条件」シートに検索条件を書いていく。

まず試しに、全角と半角で「NTT」「エヌティティ」という2つの条件を書いて、「リスト範囲」「検索条件範囲」を先程の例題と同様に設定する。するとどうなるか。

結果は下図の通り、最初の文字が「NTT」「エヌティティ」で始まるものだけ抽出される。

このフィルタオプション機能、このように「NTT」など言葉を入れて検索したとき、それは前方一致「○○で始まる」条件で検索されるのだ。
Excel2002までのバージョンでは完全一致「○○と等しい」条件だったらしいけどね。

もしこれを完全一致で検索したかったら
=”=㈱NTTドコモ”
みたいに「=」演算子を付けたものを更にダブルクォーテーション””で囲むという指定をすれば良い。

・・・と一応書いてみたが、こんな完全一致検索とか前方一致検索なんて、実際のところ需要あるのだろうか。

実は私自身、この語句検索がデフォルトでは前方一致検索をするものだなんて、この記事を書いてて詳細を調べてて初めて知った。
デフォルトでは完全一致検索だとばかり思ってたんだよね。

「○○を含む」条件を記述する

で、私が実務で用があるのは部分一致検索「○○を含む」検索ばかりなので、ここからはそのやり方を述べる。

要はこの例題では、「NTTを含む」「エヌティティを含む」とか「○○を含む」条件で色々なバリエーションを付けて検索すれば良いわけだ。

その「○○を含む」という検索には、ワイルドカードというやつで「*」記号を使う。
ちなみに記号「?」は任意の1文字を表し、記号「*」は任意の文字数(文字数0も含む)の文字を表すってことだけど、大抵の場合「*」記号にしか用はあるまい。

慣れないうちはひとまず「*」記号の方だけ覚えておこう。

で、「NTTを含む」だったら
*NTT*
と「*」記号で両端を囲んで記述すれば良いわけね。

これで、「NTT」語句の左側・右側に何らかの文字(文字数0のものも含む)が含まれているという条件になる。