
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関数が使えない場合の代用方法
FILTER関数は Excel 2021 以降または Microsoft 365 のバージョンで使用可能です。FILTER関数が使えない Excel 2019 以前のバージョンでの、代用方法をご紹介します。
VLOOKUP関数で代用する方法

産地から「岡山」を検索し、一致する全ての「産地」と「商品名」を抽出します。
まず、データの左側に作業列を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」といった検索値を作成します。
- ROW(A1)は数式を下にコピーしたときに
- 範囲:$A$2:$C$5
- 作業列を必ず先頭の列に含めた、抽出したい範囲を指定します。
$
マーク(絶対参照)を使って、数式をコピーしても範囲がずれないように固定します。
- 作業列を必ず先頭の列に含めた、抽出したい範囲を指定します。
- 列番号:COLUMN(B1)
COLUMN(B1)
は2
を返します。これは$A$2:$C$5
という範囲の中で、2番目の列(B列=産地)のデータを抽出するという意味になります。この数式を右にコピーすると、COLUMN(C1)
は3
を返し、3番目の列(C列=商品名)のデータを抽出できます。
VLOOKUP関数の引数:(検索値, 範囲, 列番号, [検索の型])

この数式を、抽出結果を表示したい行数分だけ下にコピーします。 さらに、抽出したい列の数だけ右にコピーします。 条件に合致するデータがなくなった場合、#N/A
エラーが表示されます。
エラーを非表示にするには、次のようにIFERROR関数で囲みます。

例:=IFERROR(VLOOKUP( "岡山"&ROW(A1), $A$2:$C$5, COLUMN(B1), FALSE ),"")
IFERROR関数の「エラーの場合の値」に空白を表す""
を指定することで、エラーを非表示にしています。
IFERROR関数の引数:(値, エラーの場合の値)
フィルター機能で代用する方法

まずデータ範囲を選択した状態で、「データ」→「フィルター」をクリックします。
次に、絞込みのアイコン(▼)を開き、「すべて選択」を押して一旦すべてのチェックを外します。最後に、抽出したいアイテムにのみチェックを入れ直し、「OK」で絞り込みます。

- メリット
- 数値や日付のフィルターを使えば、「以上」「以下」の条件でも簡単に抽出できます。
- デメリット:
- フィルターをかけると、元のデータが一時的に非表示になります。
- 絞り込んだ結果を他の場所で使いたい場合、コピー&ペーストの手間がかかります。

「以上」「以下」の条件で絞り込む場合は、「数値フィルター」を選択します。