画像: PixieMe/Shutterstock
フィルタリングは多くのMicrosoft Excelシートで重要な役割を果たしており、幸いなことに、その方法は数多くあります。Microsoft 365の新しいFILTER()関数は、強力なアップグレードです。この新しい動的配列関数を使うことで必ずしも異なる結果が得られるわけではありませんが、単一の関数で同じ結果を得ることができます。この記事では、組み込みのフィルター機能について確認した後、新しいFILTER()関数の使い方を学びます。
Windows 10 64ビットシステムでMicrosoft 365（デスクトップ版）を使用しています。組み込みのフィルター機能はリボンバージョンでのみ利用可能です。FILTER()は、Microsoft 365、Excel 2021、Web版Excel、iPadおよびiPhone版Excel、Androidタブレットおよびスマートフォン版Excelで利用可能です。ご参考までに、デモ用の.xlsxファイルをダウンロードしていただけます。この記事は、基本的なExcelスキルをお持ちの方を前提としていますが、初心者の方でも手順に沿って操作すれば問題なく使用できるはずです。
参照:すべてのユーザーが習得すべき Excel のヒント 83 選(TechRepublic)
Microsoft Excelのフィルター機能の使い方
Excelのフィルター機能を使えば複数の値でフィルターできるので、FILTER()関数が全く必要ない場合もあります。そのため、簡単な例を使って、適切な判断を下せるように説明します。図Aは、フィルターを適用したシンプルなデータセットを示しています。フィルターを適用するには、「データ」タブをクリックし、「並べ替えとフィルター」グループの「フィルター」をクリックします。
図A
フィルターを使用するには、ヘッダーセルの適切なドロップダウン矢印をクリックするだけです。「Region（地域）」のドロップダウンをクリックして試してみてください。表示されるペインでは、様々な方法でフィルター処理できます。フィルターが設定されていない場合は、すべての値にチェックマークが付きます。1つまたは複数の値で並べ替えるには、「(すべて選択)」項目のチェックを外します。次に、表示したい項目にチェックマークを付けます。図Bでは、「Northwest（北西）」と「Southwest（南西）」が選択されています。その後、図Cに示すように、フィルター処理されたセットには、これら2つの値のレコードのみが表示され、「Central（中央）」のレコードは表示されません。
図B
図C
この機能はほとんどの状況に対応できるほど柔軟ですが、少なくとも1つの制限があります。フィルタリングされたセットを別の場所に表示することはできません。この機能は、レコードをその場でフィルタリングするだけです。新しいFILTER()関数のおかげで、フィルタリングはフィルター機能を使用するのとほぼ同じくらい簡単です。
ExcelのFILTER()関数について
フィルタリングされたセットを別の場所に表示すると、レポート作成が容易になり、特にダッシュボード設定で効果的です。FILTER() が導入されるまでは、フィルタリングされたセットを別の場所に表示するには、多少の手間とスキルが必要でした。
FILTER() は、Microsoft 365 の新しい動的配列関数の 1 つです。この関数は、スピル範囲と呼ばれるものをサポートしています。スピル範囲とは、複数の値を返す動的配列数式の結果であり、出力は入力セルを超えて広がります。簡単に言うと、スピル範囲とは、1 つの数式から計算された結果の範囲です。スピル範囲内のセルを選択すると、Excel は範囲全体を青い枠線で強調表示します。数式は常に、その範囲の最上部のセルに表示されます。
この関数は構文
FILTER(配列, 含める, [空の場合])
ここで、array はソース データを識別し、include はフィルター処理されたデータ セットに表示する値を識別し、オプションの if_empty は結果が空のセットの場合に表示する値を指定します。
FILTER() 関数を使うと、単一または複数の列を返すことができます。さらに、単一または複数の列でフィルタリングすることも可能です。それでは、FILTER() 関数を使ってみましょう。
ExcelでFILTER()関数を使う方法
FILTER() を使用するには、1つの条件があります。フィルター対象範囲で同じヘッダーを使用する必要があります。B2:F14 に設定された元のデータがソースデータです。ヘッダーを別の場所にコピーするだけです。
必須ではありませんが、include引数に入力セルを使用すると、関数の柔軟性が向上します。図Dに必要な設定を示しています。I列を使用して、より柔軟で動的な関数を作成します。フィルタリングされたデータセットは、K列から0列に表示されます。
図D
1つの列のみのフィルタリング結果を表示したいことは稀ですが、可能です。簡単な例を見てみましょう。具体的には、関数「Southwest」を入力して、Southwest地域の人員を表示します。
=FILTER(D3:D13,E3:E13=I5,”結果なし”)
図Eに示すように、M3に「Southwest」と入力します。次に、地域入力セルI5に「Southwest」と入力します。（関数は単一のセルに入力され、結果は隣接する範囲に渡されるため、絶対参照は必要ありません。）
図E
関数の引数を確認することで、結果を簡単に解読できます。
- 配列引数 D3:D13 には、人事列 (列 D) の一致する値のみが表示されます。
- 含める引数 E3:E13=I5 は、セル I5 の値 (Southwest) でリストをフィルターします。
この関数は、南西部地域の人事値のみを返します。
表示値とフィルタリング値を追加して、少し複雑にしてみましょう。関数を入力します。
=FILTER(B3:F13,(D3:D13=I4)*(E3:E13=I5),”結果なし”)
K3では、図Fに示すようになります。（FILTER()関数では適切な書式設定ができないため、適切な書式設定を適用しました。）
図F
包含引数 (D3:D13=I4)*(E3:E13=I5) は、あまり馴染みのない表現かもしれません。* 文字は AND 演算子として機能します。簡単に言うと、この引数は I4 の個人値とI5 の地域値でフィルタリングします。結果として得られるフィルタリングされたセットには、Southwest 地域の James のレコードが 1 件だけ含まれます。包含引数で OR 演算子を適用するには、* 文字の代わりに + 文字を使用します。
結果を改善する方法がもう1つあります。フィルタリングされたセットに複数のレコードが含まれている場合は、SORT()を使用して結果を並べ替えると良いでしょう。
ExcelでSORT()関数を追加する方法
SORT() 関数は、もう一つの新しい動的配列関数です。ソートされた配列を返し、以下の構文を使用します。
SORT(配列,[ソートインデックス],[ソート順序],[列順])
ここで、arrayは唯一の必須引数であり、並べ替える範囲を指定します。オプションの引数は以下のとおりです。
sort_index: 並べ替えの基準となる行または列を識別する数値オフセット値
sort_order: 昇順の場合は1、降順の場合は-1。デフォルトは1です。
by_col: 行の並べ替えの場合は論理値 TRUE、列の並べ替えの場合は FALSE。TRUE がデフォルトです。
現在のFILTER()は1件のレコードしか返さないので、I4からJamesを削除し、南西部地域の降順でソートされたフィルタリングされたセットを値列で返します。そのためには、次の関数を入力します。
=SORT(FILTER(B3:F13,E3:E13=I5,”結果なし”),2,-1)
図Gに示すように、K3にあります。
図G
SORT() の配列引数は、FILTER() 関数全体です。sort_index の値が 2 の場合、配列の2番目の列である Value 列でソートされます。sort_index の値は -1 で、降順ソートが適用されます。結果は、Southwest 地域の最大値を先頭に表示する、ソートされたフィルター済みセットになります。
注目すべき点
FILTER() をご自身の作業に適用する際には、いくつか考慮すべき点があります。まず、サンプルシートには、ソースデータセットの各列に対応する入力セルが含まれています。ただし、入力セルは FILTER() 関数の include 引数で参照される列のみに含めることをお勧めします。フィルターされたデータセットを更新しない入力セルを提供すると、ユーザーを混乱させる可能性があります。
組み込みのフィルター機能は、データセットをテーブルオブジェクトに変換します。FILTER() 関数は、引き続き同じソーステーブルを参照します。関数を入力する際に、選択メソッドを使用して参照を挿入すると、私が使用している実際の範囲（配列）参照ではなく、Excelのテーブル構造参照が使用されていることがわかります。心配しないでください。すべて正常に動作します。
参照：Windows 10：音声認識とディクテーションの音声コマンド一覧（無料PDF）（TechRepublic）
ソースデータはTableオブジェクトであるため、すべてが動的です。ソースデータにレコードを追加、削除、または変更すると、フィルタリングされたデータセットもそれに応じて更新されます。
実際のレポートやダッシュボードでは、データ検証を使用して入力セルにドロップダウンリストを作成することを検討してください。これにより、入力値が無効な場合のエラーを防ぐことができます。
FILTER() 関数を SUM()、AVERAGE() などの集計関数で囲むことができます。そうすることで、FILTER() でフィルタリングされた結果を集計した結果が1つだけ返されます。
乞うご期待
FILTER() を使って複数のセルにまたがる複数の値を一致させるのは複雑になりがちです。実際、この点では組み込みのフィルター機能の方がはるかに使いやすいです。今後の記事では、FILTER() を使ってこの要件に取り組みます。続いて、Value や Commission などの値列のフィルタリングについての記事で考察を深めます。
