【Excel新関数】もうオートフィルターはいらない？ 「FILTER関数」でデータ抽出を完全自動化：Tech TIPS

[小林章彦，デジタルアドバンテージ]

連載目次

対象：Excel 2021／2024／365

Excel新関数で「FILTER関数」でデータ抽出を完全自動化
Excelで特定のデータだけを抜き出して別表を作る際、いまだにオートフィルターとコピペを繰り返していないだろうか。Excel 2021以降で利用できる「FILTER関数」を使えば、条件に合う行を別セルへ自動的に抽出・表示できる。本Tech TIPSでは、基本構文から複数条件の指定、テーブル機能を活用した効率的な運用術までを解説する。

　膨大なマスターデータから「Microsoft Excel（エクセル）」を使って、「特定の顧客だけ」「特定のステータスだけ」を抜き出して別シートにまとめる作業は意外と多い。これまでは「オートフィルター」をかけてコピー＆ペーストすることが多かったのではないだろうか。

従来のフィルターを使ってデータを抽出する方法
いまだにフィルターでデータを絞り込み、それをコピーして貼り付けていないだろうか。この場合、データが変更になったり、追加されたりした場合、改めてフィルターで絞り込み、コピーと貼り付けをしなければならない。FILTER関数を使えば、こうした作業が自動化できる。

　しかし、FILTER関数の登場により、条件に合致するデータだけを抽出・表示する作業は完全に自動化された。本Tech TIPSでは、FILTER関数の使い方と活用方法を解説する。

FILTER関数の基本構文

　FILTER関数は、Excel 2021以降とExcel 365で利用可能な動的配列関数だ。指定した範囲の中から、設定した条件を満たす行（または列）だけを抽出し、別のセル範囲に自動展開（スピル）させることができる。

　基本的な構文は以下の通り。

=FILTER(配列, 含む, ［空の場合］)

FILTER関数の基本構文

引数名	説明	指定方法
配列	抽出元となる全体のセル範囲	「A1:A10」などのようにセル範囲を指定
含む	抽出条件を指定する論理式（結果が「TRUE」または「0」以外の数値になる行だけを抽出）	B2:B10="営業部"
空の場合（省略可）	条件に一致するデータが1つもなかった場合に表示する値	"該当なし"
FILTER関数の引数

基本的な使い方とエラー回避

　例えば、全社の社員名簿（「A」列〜「C」列）から、「情報システム部」のメンバーだけを別枠に抽出したい場合でFILTER関数の使い方を見ていこう。

　抽出結果を表示したい領域の先頭セル（例：「F2」セル）に、以下の数式を入力する。

=FILTER(A2:D6, B2:B6="情シス")

「情シス」を抽出するFILTER関数

　［Enter］キーを押すと、指定した条件に合致する行が、右方向および下方向へ展開（スピル）される。

FILTER関数の基本的な使い方（1）
抽出した表を入力したい右上のセルを選択し、FILTER関数を入力する。

▼

FILTER関数の基本的な使い方（2）
［Enter］キーを押すと、指定した範囲の中から、設定した条件を満たす行（または列）だけが抽出されて、自動展開（スピル）される。オートフィルによる数式のコピーも不要だ。

【注意】FILTER関数のエラーを回避する

　FILTER関数は、他の動的配列関数と同様、展開先に何らかのデータが入力済みだとエラーになる。展開先には十分に空いたセル範囲が必要な点に注意が必要だ。

展開先には十分な空きセルが必要
FILTER関数は動的配列関数のため、数式を入力したセル以外にもデータが展開される。そのため、展開先となるセル範囲に値が入っていると、「#スピル!」エラーが発生してしまう。

　また、抽出元のセル範囲（配列）と条件のセル範囲（含む）の「行数」が一致していないとエラー（#VALUE!）になる点にも注意が必要だ。引数「配列」のセル範囲を変更した場合、合わせて引数「含む」の行数を変更するのを忘れないようにする。

引数「含む」を使いこなす論理式の作り方

　FILTER関数の肝となるのが、引数「含む」の設定だ。ここでは、単一条件から複雑な複数条件まで、幾つかの論理式のパターンを紹介する。

基本的な比較演算子

　数値や文字列を比較して抽出する、最もシンプルなのは、「=（等しい）」や「>（より大きい）」、「< >（じゃない）」といった演算子だ。

●社歴が5年の人を抽出
=FILTER(A2:D6, D2:D6=5)

●社歴が5年より長い人を抽出
=FILTER(A2:D6, D2:D6&gt;5)

●社歴が5年以外の人を抽出
=FILTER(A2:D6, D2:D6&lt;&gt;5)

FILTER関数の基本的な比較演算子の使い方

基本的な比較演算子による抽出
FILTER関数の第2引数「含む」では比較演算子が使える。例えば、「社歴」が「5より大きい」社員を抽出したい場合は、「D2:D6>5」とすればよい。

AND／OR条件

　複数の条件を使って、全てを満たす場合（AND）やいずれかを満たす場合（OR）で抽出することも可能だ。それぞれの条件式を「( )」で囲み、「(B2:B6="営業")*(D2:D6>=5)」とすると、「営業」かつ社歴5年以上が抽出できる。ANDの場合は「*（アスタリスク）」、ORの場合は「+（プラス）」で条件をつなげればよい。

AND条件でデータを抽出する
各条件を「（）」で囲み、「*」でつなげることでAND条件によるデータ抽出も可能だ。部署が「営業」でかつ社歴が「5年以上」の社員を抽出したい場合は、「(B2:B6="営業")*(D2:D6>=5)」とすればよい。

OR条件でデータを抽出する
各条件を「（）」で囲み、「+」でつなげるとOR条件によるデータ抽出となる。部署が「営業」か社歴が「5年以上」の社員を抽出したい場合は、「(B2:B6="営業")+(D2:D6>=5)」とする。

　Excelの論理演算ではTRUEは「1」、FALSEは「0」として扱われる。両方の条件が「TRUE」の場合、AND（*）では「1*1=1」となった行だけが抽出対象となる（片方がFALSEだと「1*0=0」となる）。

　OR（+）の場合は、条件のいずれかが「TRUE」となっていれば、「1+0=1」で条件を満たし抽出対象となるわけだ。

部分一致検索

　「特定の文字を含む」という条件を記述する方法は幾つかあるが、COUNTIFS関数を使うのが分かりやすいだろう。

　例えば、氏名に「藤」が含まれる社員を抽出したい場合は、「COUNTIFS（A2:A6,A2:A6,A2:A6,"*藤*")」とする。単に「COUNTIF(A2:A6,"*藤*")」でもいいように思えるが、この場合「3」という数値が1つ返って来るだけで、各セルについて個別に判定されないことからエラーになってしまう。この点、少し分かりにくいので注意してほしい。

COUNTIFS関数と組み合わせて特定の文字列を含むデータを抽出
特定の文字列を含むデータを抽出する方法は幾つかあるが、COUNTIFS関数を使うのがシンプルだろう。氏名に「藤」が含まれる社員を抽出したい場合は、「COUNTIFS（A2:A6,A2:A6,A2:A6,"*藤*")」とする。注意点は、COUNTIFS関数の1つ目の範囲と条件に「A2:A6,A2:A6」を指定することだ。これで各行に文字列が含まれているかどうかの比較が実行できるようになる。COUNTIFS関数を使うとワイルドカードを使った文字列指定もできるので応用範囲が広くなる。

【推奨】FILTER関数で指定する「配列」はテーブル名にする

　FILTER関数の引数「配列」には、通常のセル範囲（A2:C100など）の代わりに「テーブル名」が使用できる。実務では、データが増減しても数式を書き直す必要がない「テーブル」と組み合わせるとよい。

　元データを「テーブル」にして、名前を「社員名簿」とした場合の例を以下に示す（表を「テーブル」にする方法は、Tech TIPS「Excelで表を『テーブル』にしたけど数式の自動反映や行／列の自動拡張が働きません」参照のこと）。

　テーブルを使う最大のメリットは、新しくデータが追加された場合でもFILTER関数の「配列」や「含む」といった引数を修正する必要なく、結果が即座に更新される点にある。

　例えば、「木村 愛」が入社し、情報システム部門に配属された場合を考えてみよう。先ほどの数式では、引数「配列」が「A2:C6」、引数「含む」が「B2:B6="情シス"」となっていため、「7」行目に追加された「木村 愛」はFILTER関数の対象とならず抽出されない。

　一方、以下のようにテーブル名を使って引数「配列」と「含む」を指定すると、「木村 愛」のデータが追加されると自動的に「社員名簿」のセル範囲も拡大されることから、「木村 愛」がFILTER関数の対象となり抽出される。

=FILTER(社員名簿, 社員名簿[部署]="情シス")

テーブル名を使ったFILTER関数の例

表をテーブルにして「配列」をテーブル名で指定する（1）
表をテーブルにして、「社員名簿」という名前を付ける。

▼

表をテーブルにして「配列」をテーブル名で指定する（2）
FILTER関数で第1引数「配列」をテーブル名「社員名簿」、第2引数「含む」のセル範囲を「社員名簿[部署]」でそれぞれ指定する。

▼

表をテーブルにして「配列」をテーブル名で指定する（3）
表をテーブルにしておくことで、データが追加されても、それが自動的に反映されるようになる。FILTER関数のセル範囲を変更する必要がなくなる。

　引数「配列」と「含む」の行数を合わせる作業も不要になり、メンテナンスが不要なシートとなる。

　またどのような条件（含む）で抽出しているのかが分かりやすくなり、数式の可読性も高くなる。

抽出結果が「空の場合」の処理

　FILTER関数で抽出した結果、指定した条件を満たすものがなかった場合（検索結果がゼロだった場合）、既定では「#CALC!」エラーが表示されてしまう。これを防ぐには、第3引数に文字列（「該当なし」など）を指定しておけば、その文字列が表示されるため、分かりやすい表が作成できる。

　例えば、以下の数式の場合、社歴15年以上の社員がいないため、第3引数に指定した「該当なし」がセルに表示される。

=FILTER(社員名簿, 社員名簿[社歴]>15,"該当なし")

抽出結果が「空の場合」に指定文字列を表示させるFILTER関数の例

抽出結果が「空の場合」の処理
引数「含む」の条件によっては満たすものがない場合もあるだろう。そのような場合、「#CALC!」エラーが表示されてしまう。これを防ぐには第3引数「空の場合」に「該当なし」などの文字列を指定しておけばよい。満たすものがない場合、「該当なし」となりエラーが表示されなくなる。

　該当者がゼロでもエラーにならないため、表としての体裁が保たれる。