excel関数で抽出結果を別の場所に表示する (FILTER 関数)
Microsoft 365 の最新版の Excel で利用できる FILTER 関数について紹介します。
おそらくこの関数については書くべきことがたくさんあるので、複数回に分けてご紹介する必要があるかなと思っています。
今回は入口ってことで。
なお、Excel 2016 とか、Excel 2019 とか、いわゆる買い切りタイプの Excel にはこの関数は搭載されていないのでご注意を。
たとえば FILTER 関数を使うと、下図のような青枠の範囲から B 列の値が「A」のレコードを緑枠のように抽出できます。
FILTER 関数はスピルありきの関数ですね。(スピルについてはこちらの記事を参照してください。)
"excel2021511-490-1"
excel FILTER 関数について
FILTER 関数の説明をヘルプで確認すると、「FILTER 関数を使用すると、定義した条件に基づいてデータの範囲をフィルター処理できます。」と書いてあるけれど、“定義” とかっていう単語が出てきたとたん難しく感じるのは私だけかしら。
厳密にいうと違うけれど感覚的には「表 (データの範囲) から指定した条件に一致するデータを抽出できます。」でしょうか。
こういう使い方をするかどうかは別として、図のように見出しが左端にくるようなセル範囲であってもデータの範囲として指定できるので、“データの範囲” の部分を “テーブル” とか “リスト” のような言い方に限定せずにおきます。
"excel2021511-490-2"
excel構文はこんな感じ。ヘルプの書き方とは変えてあります。
=FILTER(対象データの範囲 , 条件 , [抽出結果がない場合の処理])
対象データの範囲 (必須)：抽出したいデータが含まれる範囲、または配列を指定します。
条件 (必須)：抽出する条件を指定します。
[抽出結果がない場合の処理] (省略可能)：該当データがなかったときにどうするのかを指定します。
ということで、たとえば下図の A 列から C 列の対象データの範囲から、B 列の値が「A」のデータを抽出したいときの数式は「=FILTER(A2:C31 , B2:B31=”A”)」です。
下図では見えていないけれどデータは 31 行目まで入っているので A2:C31 が範囲です。
なお、このとき見出しのセルはフィルター対象の “データ” ではないので範囲に含めません。
この数式を、結果を表示したい場所 (の左上となるセル) に作成します。
条件に一致するデータが抽出されて表示されます。スピルが機能しているので結果の外側に青い枠が表示されています。
"excel2021511-490-2"
おそらく実務では、条件の部分をどれだけ工夫できるかが大切になってくるでしょう。
たとえば数値を条件にするときは、「500」のようなジャストの値ではなくて、「500 以上」なんていう指定をすることのほうが多いように思います。たとえば下図の A 列から C 列の対象データの範囲から C 列が「500 以上」のデータを抽出したいのなら、比較演算子を使って「=FILTER(A2:C31 , C2:C31>=500)」という数式を作成します。
"excel2021511-490-3"
なお、今回は書かないけれど、条件の部分に日付を指定するときにはちょっとテクニックが必要というか、しっかり本質を理解しないといけません。
おまけ 1：抽出結果を各シートに表示する
もちろん条件をセル参照することができます。また、抽出結果をデータの範囲とは異なるワークシートに表示したいこともあるでしょう。
たとえば下図は、[Data] シートの A2 から C31 を対象データの範囲とし、条件は抽出結果を表示するワークシートのセル F1 に準備している例です。
Data!A2:C31 の部分が対象データの範囲、Data!B2:B31=F1 の部分が条件です。
"excel2021511-490-4"
[Data] シート [エリア] 列 (B 列の値) ごとに、抽出結果のワークシートを分けたいのなら、↑ のワークシートを作成して、条件部分を書き換えるのも 1 つのやり方かな、と。
"excel2021511-490-5"
抽出結果がないときの処理
第 3 引数の [抽出結果がない場合の処理] を指定していないときに、条件に一致する抽出結果がないと、「#CALC!」というエラーが表示されます。
"excel2021511-490-6"
たとえばこちらの図では、「対象データの範囲に F のデータなんかないよ」ってことで #CALC! エラーが表示されています。
抽出条件に一致するデータがなかったらこうしたい、という処理を第 3 引数に指定でき、たとえば下図では「該当なし」という文字列を表示するように指定しています。別途エラー処理を組み合わせてもよいのでしょうけれど、エラーの原因は 1 つではないから、抽出条件に一致するデータがなかった場合の対応はこちらに書いて対応できるのならそうしたいです。
もちろん、「"該当なし”」の部分を「"”」 (ダブル クォーテーション 2 つ) と指定すれば、空白を返すことができます。
"excel2021511-490-7"
おまけ 2：対象データの範囲はテーブルでも OK
前の手順では、あえてテーブルに変換していないセル範囲でご紹介しましたが、もちろん対象データの範囲や条件に一致しているかどうかを判断するための値が格納されている範囲 (列) は、テーブルの構造で指定できます。
データの追加や削除を含む更新のことを考えるのなら、対象データの範囲はテーブルのほうが扱いやすいし、特に理由がないのならテーブルにしておくべきでしょう。
たとえば下図では、テーブルに「AreaData」というテーブル名を設定してあります。
"excel2021511-490-8"
こちらは、「エリアが A のレコードを抽出したい」をかなえるための数式です。
対象データの範囲にテーブル名 (AreaData) を、条件にテーブルの列名を使用した指定 (AreaData[エリア]=”A”) をしています。
"excel2021511-490-9"
実務では、途中でもご紹介したような「〇〇ごとにシートを分けて表示したい」なんていう作業がよくあって、継続的にこの作業を行いやすくする仕組みを作るのは、なかなか標準機能だけでは厳しかったのですが、FILTER 関数の登場によってガラリと変わる気がします。
基本的な動きを理解できたら、日付でフィルターしたいとか、もう少し複雑な条件を指定したいとか、抽出した結果を並べ替えて表示したいなんて欲が出てくるでしょう。(というか出てきてほしい。)そのあたりを満たす？お話は徐々に書いていく予定です。
