今回はExcelワークシート関数の新関数の一つであるFILTER関数を紹介します。
FILTER関数とは何か
FILTER関数はサブスクリプションのOfficeや、購入型のOffice2020以降で使用できます。FILTER関数はエクセルのワークシート関数で、特定の条件に基づいて大きなデータセットからデータの部分集合を動的に抽出することができます。
この関数の構文は次のとおりです。
FILTER(配列, 含む, [空の場合])
“配列“は、フィルタリングしたいデータの範囲です。”含む“は、データをフィルタリングするために使用したい基準で、キーワードなどを設定します。[空の場合]引数は、一致するデータが見つからない場合に返されるオプションの値で、設定しなくても使えます。
関数の具体的使い方
まずは最も簡単な使い方として、(配列, 含む)の2つの引数を使います。
今回使用するのは以下のデータテーブルです。シート名は”元データ”です。
今回はこのテーブルから、担当者が”田中”のデータのみ、更に必要な列だけに絞って抽出します。FILTER関数を使用した後のイメージ図は以下の通りです。元テーブルから、4つの列のみ抽出しました。
この抽出したシートの中で、関数は3か所入力されています。1か所目はA2セルです。A2セルの数式には”元データのF列の値が”田中”の場合は元データのA列を引用”するという内容のFILTER関数が組み込まれています。
この時、FILTER関数には”スピル“機能があるので、A2セルにしか数式を入力していないのに、シートの下の方までデータが入っています。
Excelにおけるスピル機能について簡単に説明します。英単語のスピル(spill)は「こぼれる」とか「溢れる」という機能です。スピル機能は、1つのセルに数式を入力すると、自動的に隣接するセルに「こぼれ」る機能と言うことができます。上の例では、A2セルに、入力した数式から、A列のA3以降のセルにもA2セルと同等の数式による結果が自動的に表示されています。これにより、大量のデータを扱う際に、各セルに手動で数式をコピー＆ペーストすることなく計算でき、更にExcelの計算量も削減することができます(Excelは1万個のセルに1万個の数式が入っていると、保存などをする度に1万個の数式の計算結果を更新しようとする)。
数式が入力されている2か所目はB2セルです。B2セルの数式には”元データのF列の値が”田中”の場合は元データのC-D列を引用”するという内容のFILTER関数が組み込まれています。ここからもわかる通り、FILTER関数は、引用元のデータテーブルの連続する列を纏めて引用することができます。これも、スピル機能の一種です。
数式が入力されている3か所目はD2セルです。D2セルの数式には”元データのF列の値が”田中”の場合は元データのI列を引用”するという内容のFILTER関数が組み込まれています。
エラー処理を設定する
以上、最も基本的な使い方を紹介しました。
ただ、冒頭で紹介したFILTER関数の構文には、[空の場合]という引数も存在しました。この引数の有効な使い方も簡単に紹介します。
[空の場合]という引数はエラー処理に使うことができます。例えば以下のように、担当者として存在しない”高橋”が指定されると、エラーが表示されます。
この時、以下のようにエラーの場合は例えば”存在しません”と表示させることができます。もちろん、空欄とすることもできます。
Excelの新関数に多い特徴ですが、以前であればIFERROR関数を使わないといけないエラー処置を、関数内の引数で設定することができて便利ですね。
FILTER関数の存在意義
元々Excelにもフィルター機能は存在しますが、その違いは何でしょうか。
FILTER関数が特に便利なのは、元のデータセットを変更することなく、特定の情報を動的にフィルタリングして表示することができる点です。大規模なデータセットを扱う際に、必要な情報を手動でフィルタリングし、並べ替えることが困難で時間のかかる場合に特に有効です。
まずは”元のデータセットを変更することなく“の部分から説明します。元々のフィルター機能は、当然ながら、元データ自体にフィルターを設定するので、元データに変更を生じさせてしまうか、テーブル全体を別シートにコピーして作業することでデータ量が2倍になってしまいます。
次に、”動的にフィルタリングして表示“を説明します。例えば以下の受注番号”S-20″の商品名”いちご”が間違っていて、”メロン”に修正されたとします。
元データでは以下のようになっています。この元データを、以下のように修正します。
すると以下のように、フィルター関数の結果も自動的に”メロン”に変更されました。これが、”動的”の意味です。
これがフィルター関数とフィルター機能の最大の違いです。
さらに、フィルター機能は、SUM、COUNT、AVERAGE などの他のワークシート関数と組み合わせて使用することができ、フィルターされたデータに対してより複雑な分析を実行することができます。これらは元々のフィルター機能では実現できないので、フィルター関数の存在意義となります。
スピル機能の意義
上述の説明でも出てきましたが、FILTER関数はスピル機能を有しています。英単語のスピル(spill)は「こぼれる」とか「溢れる」という機能ですが、Excelのスピル機能は、1つのセルに数式を入力すると、自動的に隣接するセルに「こぼれ」る機能と言うことができます。
上述の通りそのメリットは、大量のデータを扱う際に、各セルに手動で数式をコピー＆ペーストすることなく計算でき、更にExcelの計算量を大幅に削減することができます。
ではどの程度人間の作業量とExcelの計算量を削減できているのでしょうか。まずは人間の手間を考えてみます。人間の場合、スピル機能が無かったら、A2、B2、(スピル機能により上記では不要だが)C2、D2セルに数式を入力後、それらをオートフィルなどを使って最下行までコピーします。この手間が省かれるので、半分程度になります。この観点での人間の方の作業量の削減はそれほどでもなさそうですね。一方、数式のどこかでミスが発生している場合などは、スピルにより確認が円滑になります。何しろ数式は最低限の数しか存在しないので、その数式だけを確認すればよいです。シート全体にわたって確認する手間が省けます。
一方、Excelの計算量はというと、ここは数式の種類やPCの環境によってもまちまちです。ケース次第では逆に動作が遅くなるような事象も観測されているようですが、恐らく今後改善され、スピルが標準的に使われるようになるかと思います。
なお、スピルについては以下の記事も参照ください。
応用編
以下では、FILTER関数の応用的な使い方をいくつか紹介します。
日付のフィルター設定方法
FILTER関数を使って日付でフィルターを設定する方法については以下を参照ください。
複数条件の設定方法
FILTER関数を使った複数条件の設定方法については以下を参照ください。
コメント