FILTER関数とXLOOKUP関数などの組み合わせはすごい［Excelシート付］

2022年5月2日

この記事では、Microsoft365のExcelで使えるFILTER関数と、XLOOKUP関数やSORT関数などの他の関数を組み合わせて業務効率を格段に上げる方法を紹介します。

→記事で紹介した例題をそのまま掲載した［練習用Excelシート］

FILTER関数とは

FILTER関数の書式

FILTER関数は、指定した範囲の中で条件に該当するデータを抽出・フィルター処理する関数です。
第1引数に「範囲・配列」を、第2引数に抽出の条件「含む」を、第3引数に条件に該当するデータがない場合の処理「空の場合」を指定します。
第3引数は省略可能です。

書式	FILTER( 範囲・配列, 含む（条件） [, 空の場合 ] )

FILTER関数の第1引数「範囲・配列」には、フィルター処理したいデータを指定します。
表頭部分を除くデータ部分のみを指定します。

第2引数「含む」には、抽出条件を指定します。
「日付」を抽出条件にする場合には、「シリアル値」で指定しないとうまくいきませんので注意してください。

第3引数には、条件に該当するデータがない場合のエラー値を表示したくないときには、空白を意味する「""（ダブルクォーテーション）」などを指定します。

日付の場合はシリアル値で指定する

では具体的にみてみましょう。
例えば、「2022/4/1」で抽出したいとします。
そこで、E2セルに =FILTER(A2:C11,A2:A11="2022/4/1") と入力してみます。

すると、エラー値が返ってきます。
これは、「"2022/4/1"」の部分が文字列だからです。
日付の文字列をシリアル値に変える最も簡単な方法は、DATEVALUE関数を使うことです。

次のように、DATEVALUE関数を使うとうまくいきます。

ですが、日付がシリアル値で返されていて見にくいですよね。
そこで、E列のいずれかのセルで、［Ctrl ＋ Space］と押し、列全体を選択した後、日付表示にするショートカットキー［Ctrl ＋ Shift ＋ 3］と押して、E列を日付表示にします。
これで見やすくなりました。

氏名（文字列）で絞り込む

以下の例で、「佐藤蓮」で絞り込みたいときは、E2セルに =FILTER(A2:C11,B2:B11="佐藤蓮") と入力します。
これで、「佐藤蓮」のデータが抽出できます。FILTER関数は、スピルするので絶対参照にする必要はありません。

売上（数値）で絞り込む

次は、「売上」で絞り込みます。
C列の売上にはない「25,000未満」という条件で抽出してみます。
E2セルに、 =FILTER(A2:C11,C2:C11<25000) と入力すればOKです。

月で絞り込む

次は、「4月」のような「月」で絞り込んでみます。
以下のように日ごとのデータになっているときには、日付から「月」を抽出するMONTH関数を使います。
「4月」のデータを抽出するには、E2セルに、 =FILTER(A2:C11,MONTH(A2:A11)=4) と入力します。

SORT関数で並び替え

MONTH関数で4月のデータを抽出しましたが、日付がバラバラになっていて見にくいですよね。
こんなときは、SORT関数を使うと簡単に並び替えることができます。今、使ったFILTER関数の式をSORT関数で囲みます。

E2セルに、=SORT(FILTER(A2:C11,MONTH(A2:A11)=4)) と入力します。
これで日付順になって見やすくなりました。

複数の条件で絞り込む

FILTER関数は、複数の条件を指定することができます。複数の条件を指定するには、次のようにします。

（条件1）または（条件2）のときは、［（条件1）+（条件2）］と［+］で結びます。

（条件1）かつ（条件2）のときは、［（条件1）*（条件2）］と［*］で結びます。

例えば、「4月」または「佐藤蓮」という条件で抽出してみましょう。
E2セルには、=FILTER(A2:C11,(MONTH(A2:A11)=4)+(B2:B11="佐藤蓮")) と条件を［+］で結んで入力します。

「4月の日付」のデータと「5月の佐藤蓮」のデータが抽出されます。

今度は、「4月」かつ「佐藤蓮」という条件で抽出してみましょう。
E2セルには、=FILTER(A2:C11,(MONTH(A2:A11)=4)*(B2:B11="佐藤蓮")) と条件を［*］で結んで入力します。

「4月の佐藤蓮」のデータだけが抽出されます。

XLOOKUP関数で特定の列だけを抽出する

日付、氏名、売上といった列がある表から、「日付の列だけを取り出したい。
しかも、FILTER関数で特定の条件を満たすデータだけを抽出したい」とします。
こんなときは、FILTER関数とXLOOKUP関数を組み合わせます。

例えば、「4月の日付だけ」を抽出したい場合には、E2セルには、=XLOOKUP(E1,A1:C1,FILTER(A2:C11,MONTH(A2:A11)=4)) します。
さきほど紹介したFILTER関数の式をXLOOKUP関数で囲んだ形です。

今度は、「4月の氏名だけ」を抽出します。XLOOKUP関数の第1引数になっているE1セルを「日付」から「氏名」に変えます。
すると、4月の氏名だけが抽出できます。

同様に、「氏名」を「売上」に変えると、「4月の売上」になります（表示形式を日付から通貨に変更しています）。
FILTER関数もすごいですが、XLOOKUP関数もすごいですね。

まとめ

この記事では、FILTER関数をDATEVALUE関数やMONTH関数、SORT関数、XLOOKUP関数と組み合わせて使うパターンを紹介しました。
FILTER関数のすごさが少しは伝わったでしょうか。

実務ではここで紹介した組み合わせだけでなく、LEFT関数といった文字列操作関数などとも組み合わせて使うことで、FILTER関数の真価が発揮されると思います。

またの機会に、他の関数との組み合わせ例を紹介しますね。

参考文献

Excel のヘルプとラーニング