エクセルのFILTER関数とは？【使えない場合の代用方法】

Excelで条件と一致するデータをすべて抽出できる、FILTER関数の使い方をご紹介します。FILTER関数が使えない Excel 2019 以前のバージョンにおいて、フィルター機能やVLOOKUP関数で代用する方法についても解説しているので、ぜひ参考にしてみてください。

FILTER関数の使い方

FILTER関数は条件に従ってフィルタ処理を行い、結果を指定した範囲から返します。

例えばA列が「産地」、B列が「商品名」のデータから、産地が「岡山」の行をすべて抽出したい場合、数式は以下のようになります。

例：=FILTER(A2:B5,A2:A5="岡山")

このようにFILTER関数使うと、特定の条件と一致する重複する複数の値や、複数列のデータを一度に抽出できるのが特徴です。

FILTER関数の引数：(範囲, 条件, [空の場合])

引数の3つ目である「空の場合」には、条件と一致するデータがない場合の値を指定します。

例えば条件と一致するデータがなかった場合に、「該当なし」を表示させる場合、数式は以下のようになります。

例：=FILTER(A2:B5,A2:A5="東京","該当なし")

この「空の場合」引数を省略すると、条件に一致するデータがなかった際に、空データの発生を意味する「#CALC! 」エラーを返します。

FILTER関数は Excel 2021 以降または Microsoft 365 のバージョンで使用可能です。FILTER関数が使えない Excel 2019 以前のバージョンでの、代用方法をご紹介します。

産地から「岡山」を検索し、一致する全ての「産地」と「商品名」を抽出します。

まず、データの左側に作業列を1列作成し、以下のような数式を入力することで、一意の検索値を作成します。

例：=B2&COUNTIF($B$2:B2,B2)

この数式では、COUNTIF関数を使って「同じ文字列が出現した回数」を取得し、これに元の文字列を結合することで、一意の検索値を作成しています。

COUNTIF関数の引数：(範囲, 条件)

ポイントは$B$2:B2のように、範囲の開始位置（$B$2）のみを固定し、終点位置（B2）は相対参照にすることです。

これにより、数式を下にコピーしたときに、範囲が$B$2:B2、$B$2:B3、$B$2:B4…のように1行ずつ拡張され、現在の行までに元の文字列が何回出現したかを数えられます。

次に、作成した作業列を使い、VLOOKUP関数で指定した「検索値」と一致する行のデータを抽出していきます。数式は以下のとおりです。

例：=VLOOKUP( "岡山"&ROW(A1), $A$2:$C$5, COLUMN(B1), FALSE )

検索値：”岡山”&ROW(A1)
- ROW(A1)は数式を下にコピーしたときに1、2、3…と連番を生成します。これにより、「岡山1」「岡山2」「岡山3」といった検索値を作成します。
範囲：$A$2:$C$5
- 作業列を必ず先頭の列に含めた、抽出したい範囲を指定します。$マーク（絶対参照）を使って、数式をコピーしても範囲がずれないように固定します。
列番号：COLUMN(B1)
- COLUMN(B1)は2を返します。これは$A$2:$C$5という範囲の中で、2番目の列（B列＝産地）のデータを抽出するという意味になります。この数式を右にコピーすると、COLUMN(C1)は3を返し、3番目の列（C列＝商品名）のデータを抽出できます。