オートフィルタは横の列とのOR条件が苦手か？
個人的にはオートフィルタはイケてる機能だと思っています。私の場合、直近でこの機能を使う見込みがなくてもとりあえず「この設定だけする」ということを結構な頻度でやっていたりします。
ただ、一方で今回挙げているような複数列上の「または」あるいは「OR」と呼ばれる条件での抽出は、あまり記憶に無く、今回問い合わせがあったので考えてみた次第です。
オートフィルタの複数列のOR条件とは
今回の説明では以下のことを想定しています。
ランチ表なる簡単なサンプルを用意し、そこから今回のOR条件を使って表示するというものです。
上記のランチ表から、以下の条件でデータを抽出します。
条件1．昼食：サンドイッチ
条件2．飲料：お茶
上記の場合は2つの列で「どちらか一方に該当する」データを表示するということに対応します。
普通に抽出するとどちらか一方が選択できなくなる
まずは一般的な方法(感覚)で、この2つの条件で実際に抽出しようとすると以下のようになってしまい、表示ができない状態となります。
ちなみに本ページを見てる方は、まさにここでハマってしまっているのではないかと思います。
作業列を使えばOR条件で抽出できる
このように普通の操作では抽出できないため、このOR条件に対応するために、「作業列」を使たいと思います。
作業列とは文字通り、よく関数などで使用される、元データと最終結果をつなぐための「作業用」の列です。
この作業列は、そもそも、本来の表には存在していない列であり、この作業のために意図的に1列(または複数列)分を追加します。
この作業列とは、関数などを使用するために必要で、あくまでも「作業用の列」です。しかし最終結果には直接関与していなくても、途中経過的に使用しているためこの作業用の列を削除することはできません。
そして、このことは今回のオートフィルタの複数列のOR条件での使用でも同様です。
今回は、いくつかの方法で複数列のOR条件に対応する説明をしますが、いずれも「作業列」は必須になります。
もっとも簡単な方法は「手作業」で準備する
今回の複数列のOR条件対応では、いくつかの方法で説明しますが、個人的な感覚ではこの方法がもっとも分かりやすく手軽ではないかと思います。
この方法のポイントは、最初の段階で条件1と条件2でそれぞれ最初に抽出し、目印を付けておくことです。
そして、この「目印をつける先(セル)が作業列」になります。
このように、それぞれの列で該当するデータに「◯」を入れました。準備はこれで完了です。
この時、条件1を選んだ後に一旦「フィルタを解除して条件2」を入れます(というかそうせざる得ないはずです)。
そして、この検索用列(作業列)を使って実際にデータを抽出します。
はい、できました。
なお、注意点としては、以下の場合です。
条件2．飲料：お茶
↓
条件2．飲料：オレンジジュース
この条件にして、再度手作業を行うと、以下のような状況に出くわします。
この時、既にある「◯」の扱いについて、2種類の対応があります。
1．そのまま残す
2．削除する
「1」の「◯を削除しないでそのまま残す」場合だと、OR条件だけでなく、AND条件も追加されます。
つまり、「条件3」が新しく追加されることになります。
条件3．「昼食：サンドイッチ」でかつ「飲料：オレンジジュース」
これは、「どちらか一方」だけでなく、それに加えて「両方とも満たす」的な条件が付与されることになります。
もっとも、これが良いのか、悪いのかは、皆さんの作業内容次第とも言えます。従って、状況に応じて決める必要があります。
「これはNG」という場合は上記の「2」の「◯を削除する」ことでフィルタ条件にヒットしなくなります。本来の条件1、条件2の状態になります。
作業列1列に関数を入れる
もしかしたら勘の良い方なら気づいたかもしれませんが、上記以外の方法でも可能です。
それは、「作業列に関数を入れる」方法です。
「IF関数とOR関数の組み合わ」せです。
IF関数とOR関数を使うことで「2つの列の打ちどちらかに指定の値があれば◯を入れる」という条件にしています。
そうすることで上記の「手作業」と同じ状況を作り出します。ここまでくれば後は同様ですね。
複数の作業列に関数を入れる
上記の作業列は「1列」での対応でした。しかし「複数列」を使うこともできます(というか1列でも可能ですが、ここでは説明の便宜上、複数の列の方が作りやすく、かつ説明しやすいからという意味もありますが)。
先程はIF関数でしたが、ここでは「COUNTIF関数とSUM関数」を使います。
COUNTIF関数では、指定(セル)範囲に指定した文字があれば、見つかった「セルの数」を返します。そしてこの数値を返した結果を元にSUM関数で合算します。
ところで「なぜCOUNTIF？」と思った方もいらっしゃるかもしれませんね。
理由は2つあります。
1つは、「数値を返す」からです。
ここでは、指定するセル範囲は1個です。この結果、返す値は「該当文字があれば1」「該当文字なければ0」の2種類だけです。
そして、その後SUM関数で合計値を算出しています。
ただ、この時点ではCOUNTIF関数の意味がまだ分からないと思います。
そこで、ここで条件2の列の検索条件を先程の「条件3：お茶→オレンジジュース」に変更してみたいと思います。
すると、以下のようになります。
ここで先ほどと異なるのは、SUMの合計値が「2」の部分ですね。
この「2」は、先ほど「手作業」で説明した部分に該当します。そうです、つまり「昼食：サンドイッチ」でかつ「飲料：オレンジジュース」ということです。
どういうことかというと、このCOUNTIF関数を使うと、条件3まで抽出できることになります。
条件1と条件2でよければ「1」を選択、条件3を含めるなら「2」を選択すれば良いことになります。
また、「数値」なのでフィルタの条件も使いやすくなります。
そして、COUNTIF関数を使うもう一つの理由が、「部分一致検索ができる」です。
COUNTIF関数の検索は、セルの値と必ずしも全文一致である必要がありません。
例えば、条件1の検索条件を「バーガー」で終わる文字とした場合、以下のようになります。
ワイルドカード「*」を入れることで、「○○で始まる」や、「○○を含む」なども可能になります。
このような検索ができるため、データの内容によっては応用的に使うことができるというメリットがあります。
オートフィルタの複数列のOR条件は「作業列」で何とかなる
いかがでしたでしょうか？一見すると難しいオートフィルタの複数列のOR条件ですが、作業列を使うことで色々と対応できることが分かるかと思います。
この抽出のポイントは「作業列」です。この列の存在自体が気になるのがたまにキズですが作業列の存在で難解なOR条件での抽出を解消できることも事実です。
また、今回は試していませんが、ORの条件が3個以上でも対応できると思います。
そしてこの要領で考えると、高度な抽出ができる「フィルタオプション」を使わないでも対応できることもあると思います。
皆さんの表でもぜひ試してみてください。