【Excel】もうオートフィルターはいらない?! FILTER関数が便利すぎる★
こんにちは、HARUです！
Excelにまとめた表から特定の条件を満たすデータだけを表示するためには、「オートフィルター」機能を使います。
（下図は東京の拠点に在籍する担当者の売上実績に絞り込んだ状態）
オートフィルターはとても便利な機能ですが、フィルターメニューの操作に工数がかかったり、複数条件のいずれかに該当するOR条件での絞り込みにカスタム設定が必要だったりします。
そこでこの記事では、様々な条件に該当するデータだけを一覧化してくれる「FILTER関数」について解説します。（オートフィルターの代わりとなる関数なので、名前が覚えやすいですね！）
FILTER関数をおさえておけば、これまでのフィルター操作では成し得なかった複雑な絞り込みと抽出作業をたった１件の数式を構築するだけで実現できます。
他の関数と組み合わせた使用例もあわせてご紹介しますので、ぜひ最後までご一読ください！
FILTER関数はMicrosoft365またはExcel2021で使える関数です。
以前の記事で取り上げた「スピル」動作に触れておくことでより理解が深まりますので、まずそちらをご覧いただくことをおススメします！
↓スピルの挙動と活用術はこちら↓
FILTER関数の基本動作
担当者別の売上実績がまとまった表から、東京の販売拠点に在籍するメンバーの情報を抽出します。
FILTER関数の入力方法
①FILTER関数を挿入し、第１引数「配列」に取得したいデータが含まれる範囲をすべて参照する。
②第２引数「含む」で、絞り込みの条件を指示する。
今回は所属拠点の列から東京を取得するので、下図のように入力する。
③第３引数「空の場合」では、該当のデータが見つからなかったときの表記を指示する。
今回は""と入力し、条件を満たすデータがない場合に空白を返す設定とする。
結果、東京の販売拠点に所属する担当者の売上実績がスピルで表示されます。
FILTER関数を含む動的配列数式は、書式設定などのセル情報をすべて取得するのではなく、セルに入力されたデータを返します。
そのため、通貨表記や日付の表現など値の表示形式がリセットされるので、データを取り出す範囲に任意の表示形式を設定しておきましょう。
（下図は販売数量、売上金額、売上単価の列を、[Ctrl]+[Shift]+[1]で桁区切りの通貨表記にした状態）
ドロップダウンリストの参照
先ほどは"東京"を数式に直接記述しましたが、ドロップダウンリストから対象の拠点名を選択し、それに連動して取得するデータが切り替わるととても便利です。
今回はスピルの解説記事で触れた、重複データをダブりなく取り出す「UNIQUE関数」をリストの値として活用しましょう。
①表の欄外にUNIQUE関数を挿入し、所属拠点のデータが含まれる範囲を選択する。（下図はUNIQUE関数をM3セルに入力した状態）
所属拠点が１つずつ表示される。
②ドロップダウンリストを挿入するセルをアクティブにした状態で、[Alt]→[D]→[L]と順に押し「データの入力規則」を呼び出す。
③入力値の種類から「リスト」を選択する。
④「元の値」の欄にUNIQUE関数を入力したセル番地と#記号を入力する。
所属拠点を選択できるリストが挿入されるので、前述でFILTER関数で直接拠点名を入力した部分をドロップダウンリストのセル参照に変更しましょう。
これにより、リストとFILTER関数で取得する条件が連動します。
単一の列のみを表示する
FILTER関数の第２引数「含む」で指示するデータ範囲は、必ずしも第１引数「配列」の中に含まれている必要はありません。
たとえば指定の拠点に所属する担当者名だけを表示したいとき、第１引数「配列」には担当者名が含まれる範囲のみを参照します。
これにより、東京の拠点に所属する担当者名だけを取得できます。
条件に合致するデータの項目をすべて表示する必要がないケースは意外と多いので、こうした参照方法も積極的に活用していきましょう！
離れている複数の列を表示する
たとえば、東京に在籍する担当者の「担当者名」「販売数量」「売上金額」の列を並べて表示したいとなったとします。
ここまで同じように第１引数「配列」を参照すると、「所属拠点」も含んで抽出されます。
こんなときは、FILTER関数の「配列」にCHOOSECOLS関数を入れ込みます。
CHOOSECOLS関数はCHOOSEROWS関数とともにFILTER関数より後に実装された関数で、それぞれ指定した列または行のデータを返します。
「選ぶ」を意味するchooseに、複数の列または行を指すcolumns・rowsをつけた造語です。
CHOOSECOLS関数を挿入すると、引数のガイダンスが英語表記となっています。（本記事執筆時点でMicrosoft365ユーザー向けに仮実装されている新しい関数であり、本実装の段階で引数の表現は変わる可能性があります）
第１引数の"array"は「配列」を意味します。
そのため、元データの範囲をまとめて参照します。
第２引数以降の"col_num1","col_num2"……は、「列番号1」「列番号2」……を意味しており、配列内で何番目のデータを返すか指示します。
今回は配列内で左から"1"列目、"3"列目、"4"列目を取得したいので、以下のように入力します。
値を直接記述するのではなく、MATCH関数などで見出しを参照して半自動化するのもおすすめです。
（数式が長くなるので今回は割愛します）
これにより、指定項目のデータだけを並べて表示できます。
非常に便利ですよね！
複数条件に該当するデータの抽出
絞り込む条件が複数ある場合も、FILTER関数で柔軟に対応できます。
▶AND条件（AかつB）
まずは東京の拠点に所属していて、かつ販売数量が100個以上の担当者データを取得します。
①FILTER関数を挿入し、第１引数「配列」に対象のデータ範囲を参照する。
②第２引数「含む」に所属拠点を含む列を選択し、先に設定したドロップダウンリストを参照する。
③これが１つ目の条件であると認識させるためにカッコで囲む。
④*(アスタリスク)をつけて２つ目の条件となる販売数量の範囲を参照し、">=100"で100以上であるかを判定する。
※２つ目の条件もカッコで囲む。
これにより、東京に在籍する販売数量100個以上の担当者情報が表示されます。
▶OR条件（AまたはB）
次に販売数量が100個以上、または売上金額が200万円以上のデータを取得します。
①前述と同じ要領で、１つ目の条件（販売数量100個以上）を指示してカッコで囲む。
②"+"(プラス)をつけて２つ目の条件となる売上金額の範囲を参照し、2000000以上であるかを判定する。
これにより、販売数量100個以上、売上金額200万円以上のいずれかに該当するデータが表示されます。
オートフィルタ―機能で複数条件を指示する場合はデフォルトで「AかつB」のAND条件で絞り込まれ、「AまたはB」のOR条件での抽出はカスタム設定が必要です。
対してFILTER関数なら、AND条件とOR条件の絞り込みを容易に切り替えてデータを取得できるのです。
▶AND条件とOR条件の融合
ここまでの解説で、「AかつB」の場合は条件を"*"（＝かけ算）でつなぎ、「AまたはB」の場合は条件を"+"（＝たし算）でつなぐことがわかりました。
それでは東京の拠点において、販売数量100個以上または売上金額200万円以上に該当する担当者情報を取得してみます。
試しに下図のようにFILTER関数を構築し、結果を見てみます。
すると、東京以外の拠点や販売数量100個以下のデータまで表示されてしまいました。
こうなってしまう背景には、四則演算（たし算、ひき算、かけ算、わり算）の優先順位が影響しています。
突然ですが皆さんは、以下を計算した結果がわかりますか？
【３０×５＋１＝？】
答えは、「１５１」です。
数式の中に四則演算が混在している場合、たし算・ひき算よりも、かけ算・わり算の方が優先して処理されます。
サンプルの場合、３０×５を実行したあとに１が加算されるのです。
では、先に５＋１を実行してからそのあと３０をかけ合わせるように指示するにはどのように記述すればよいでしょうか。
【３０×（５＋１）】
そう、優先して演算したい部分をカッコで囲むのでしたね。
これにより、５＋１で得られる６に３０をかけた結果、答えは「１８０」となります。
考え方はこれと同じです。
前述のFILTER関数に戻り、先に判定したい「販売数量100個以上または売上金額200万円以上」の部分をさらにカッコで囲みます。
結果、求めていたデータだけを取得することができました。
（①東京）×（②数量100個以上）＋（③売上200万以上）とした場合、①×②が先に処理されますが、あとから③が加わることで、①②条件に合致するかは関係なく、売上200万以上のデータが単純追加されてしまいます。
そのためこのようなケースでは以下のように指示します。
（①東京）×（（②数量100個以上）＋（③売上200万以上））
先に②③のいずれかに当てはまるかを判定したあとに、その結果と「所属拠点：東京」を照合することで、マッチする担当者情報だけを取得できるのです。
指定条件「以外」の抽出
実務では、指示した条件に該当しないデータをすべて表示させたいことがあります。
たとえば所属拠点が東京「以外」のデータを取得したいとなったとします。
今回は東京の他に大阪・名古屋しかないので、前述の「AまたはB」（所属拠点：大阪）＋（所属拠点：名古屋）と同じように指示しても良いでしょう。
ただし指定条件に当てはまらないデータが増えれば増えるほど、記述に膨大な手間がかかります。
こんなときは「所属拠点が東京であること」を指示した条件の部分から、１を差し引きます。
これにより、東京以外データがすべて表示されます。
メール種別ごとにTO/CC対象アドレスを抽出
組織内のメンバー（数十人～数百人）向けに特定の情報を一斉メールでアナウンスすることがあります。
ただし、人事関連情報や製品の技術的な仕様情報、商品の販売に関わる情報では対象となる階層や部門が異なり、それぞれ宛先とするか写しとするかの情報展開強度もすみ分ける必要があります。
マスターの宛先リストから１つずつ対象アドレスをピックアップし、宛先・写しの判断をしながらメールアプリや社内の基幹ワークフローシステムのアドレスボックスにインプットしていくのは相当時間がかかりますよね。
こんなときは次のステップで柔軟に取得しましょう。
①メールの種別ごとに宛先の種類（TO、CC、－(送付不要)）を追記する。
②メールの種別と宛先の種類をそれぞれリスト化する。
③FILTER関数を挿入し、第１引数「配列」にメールアドレスの範囲を参照する。
④第２引数「含む」にXLOOKUP関数を挿入し、検索値としてメール種別のリストを参照する。
⑤XLOOKUP関数の第２引数「検索範囲」に表中のメール種別見出しを参照し、第３引数「戻り範囲」に宛先種類の範囲を参照する。
⑥XLOOKUP関数のカッコを閉じ、宛先種類のリストを参照する。
⑦第３引数「空の場合」に該当データがないときに表示する値や文字列を指示する。（下図は""で空白を返すよう設定した状態）
これにより、指定したメールの種別と宛先の種類に該当するアドレスが抽出できます。
リスト選択だけで対象のアドレスが瞬時に切り替わります。
あとはメールアプリや社内一斉送信用のワークフローシステムのアドレスボックスにコピー＆ペーストするだけです。
表にフィルターを設定し、メールの種別ごとにつど宛先種別を絞り込んでも良いですが、フィルターメニュー操作が求められたり、表示されたアドレスだけをコピーするために可視セル選択のステップを踏む必要があったりします。
FILTER関数でこの工程を簡略化できるのは大変ありがたいですよね。
※取引先等の組織外メンバーが対象の場合は、BCCで宛先がわからないようにする、メーリングリストを活用する、基幹システムで一元管理する等、コンプライアンスに十分注意しましょう。
いかがでしたか？
今回はFILTER関数についてご紹介しました。
オートフィルター機能の場合、
＊条件はフィルターメニューから絞り込む。
＊複数条件の絞り込みは「AかつB」のAND条件のみとなる。
（OR条件での抽出はカスタム設定が必要）
＊データ範囲が更新された際にフィルターをかけ直す必要がある。
＊コピー＆ペーストする際に可視セルだけを選択する必要がある。
など、いくつかの作業工程や成約条件が加わります。
対してFILTER関数は、
＊リスト選択や他の関数との組み合わせで操作がシンプル。
＊「AかつB」「AまたはB」の複数条件に対応できる。
＊データ範囲が更新されても動的配列数式に自動反映される。
＊抽出されたスピル範囲をそのままコピーできる。
というように、効率的かつ直感的なデータ取得が可能です。
ぜひ様々なシーンで実践してみてくださいね！
【コラム】なぜ"＊"と"＋"を使うのか
FILTER関数で複数条件を指示するとき、条件同士を"*"(アスタリスク)や"+"(プラス)でつなげました。
さらに指定条件「以外」のデータをすべて取得したいときに、設定した条件から"1"を差し引きました。
どうしてこのような考え方が採用されているのか、気になりますよね！
最後にこの理由を解説します。
特定の関数で検索・演算・抽出対象となる条件を指示したとき、Excelは検証範囲のすべてのデータにおいて、ユーザー指定の条件にマッチするかどうかをものすごいスピードで判定します。
IF関数の引数が「値が真の場合」「値が偽の場合」となっている通り、判定のポイントは「正しいか」「正しくないか」の２択です。
プログラム上はそれぞれ「１(0以外)」「０」の値で処理され、結果として真偽を表す「TRUE」「FALSE」が返されます。
この前提条件をおさえた上で、先ほど取り上げた担当者別の売上実績データを例に解説します。
もし、東京の拠点には在籍しているが販売数量が100個に満たないデータの場合、それぞれの処理は以下のようになります。
（①東京）×（②数量100個以上） ←【AND条件】AかつB
（①東京）+（②数量100個以上） ←【OR条件】AまたはB
▼
（①：１）×（②：０）←【AND条件】AかつB
（①：１）+（②：０）←【OR条件】AまたはB
東京の拠点に在籍しているという条件を満たしているので、①はTRUE(1)、販売数量が100個に満たないので、②はFALSE(0)です。
最後に、それぞれの計算結果を見てみます。
（①：１）×（②：０）＝０ ←【AND条件】AかつB
（①：１）+（②：０）＝１ ←【OR条件】AまたはB
【AかつB】
設定した複数条件のうち１つでも該当しなければ、たとえ他の条件にマッチしていても対象外のデータとしてはじかれます。
結果、「東京の拠点には在籍しているが販売数量が100個に満たないデータ」は対象外となります。
０に何をかけても０なので、すべての条件を満たす必要があるAND条件での絞り込みに「"*"(かけ算)」が用いられるのです。
【AまたはB】
設定した複数条件のうち１つでも該当していれば、たとえ他の条件にマッチしていなくても対象のデータとして判定します。
結果、「東京の拠点には在籍しているが販売数量が100個に満たないデータ」は対象となります。
０＋１＝１ですし、１＋１＝２なので０以外に該当します。そのため、いずれかの条件を満たせばよいOR条件での絞り込みに「"+"(たし算)」が用いられるのです。
ここまでの解説で、指定条件「以外」のデータをすべて取得するときに設定した条件から"1"を差し引いた理由がなんとなく想像つきますね。
東京に該当するかどうかを条件とした場合、本来なら「１（TURE）」判定となる東京のデータが抽出されますが、"1"を引くことで１－１＝０となります。
これにより、東京に該当するという条件を満たさず「０（TURE）」判定となる東京「以外」のデータが、すべて取得できるということなのです。
小学校で習った「四則演算」の重要性を再認識しますね！
Excelは本当に奥が深い…..。
↓↓Excel操作をとにかく高速化したい方へ↓↓
この記事が気に入ったらサポートをしてみませんか？