エクセルで条件に合うものをすべて抽出する関数

Excel 2021 以降または Microsoft 365 なら、FILTER関数で条件と一致する複数データを簡単に抽出できます。FILTER関数が使えない場合に、VLOOKUP関数で代用して複数データを抽出する方法についても解説しているので、ぜひ参考にしてみてください。

VLOOKUP関数で複数の結果を抽出する方法

VLOOKUP関数は範囲の１列目を検索し、検索値と一致する行を指定した列から返します。後ほど詳しく解説しますが、複数の結果を抽出する方法は以下の通りです。

VLOOKUP関数の構成要素：(検索値, 範囲, 列番号, [検索の型])

COUNTIF関数は、範囲内から条件に一致する要素の個数を返します。

ポイントは $B$2:B2 のように、範囲の開始位置のみ固定することです。これにより数式をコピーしたときに、終点位置のみ１行ずつ下に拡張されます。

COUNTIF関数の構成要素：(範囲, 条件)

COUNTIF関数で取得した番号を元データと結合して、すべてのデータに番号を振ります。

例えば B2&COUNTIF($B$2:B2,B2) なら、野菜1 です。オートフィルで数式をコピーすると、同じ文字列が出現する毎に２、３、４、と番号が振られていきます。

VLOOKUP関数の検索値には、ROW関数で行番号を結合します。番号 1 から検索したいので、ROW関数の範囲には 1 行目の全体またはいずれかのセルを指定してください。

例えば検索値が “果物”&ROW(A1) なら、抽出されるのは果物1の「りんご」です。

数式をコピーすることで行番号も１行ずつ移動するため、検索値が果物2、果物3、となります。

ROW関数の構成要素：([範囲])

IFERROR関数は値がエラーでない場合はそのまま値を、エラーの場合はエラー値を返します。

エラーを非表示にする場合は値にVLOOKUP関数を、エラーの値に空白(“”)を指定してください。

例えば IFERROR(VLOOKUP(“果物”&ROW(A1),$A$2:$C$6,3,FALSE),””) なら検索値が範囲に存在するうちは値を返し、なくなったら空白を返します。

IFERROR関数の構成要素：(値, エラー値)

FILTER関数でデータ抽出するメリットや、使い方をご紹介します。なお Excel 2019 以前のバージョンでは使えないので、VLOOKUP関数で代用してください。

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

例えば FILTER(B2:C7,A2:A7=E2) なら、カテゴリーから果物を検索します。結果を範囲から返すので、抽出されるのは商品名と価格の 2 列です。

FILTER関数の構成要素：(範囲, 条件, [空の場合])

3番目の要素[空の場合]には、条件と一致するデータがない場合の値を指定します

例えば FILTER(B2:C7,A2:A7=E2,”取り扱いなし”) なら、条件と一致するデータが見つからない場合に「取り扱いなし」を返します。省略すると #CALC! を返します。

#CALC!は配列数式を計算した結果、空のデータが発生したことを意味するエラーです。

FILTER関数で別シートを参照する場合の記入例は以下の通りです。例では果物の商品名と価格を、データシートから抽出先シートに抽出しています。

別シートの指定方法：FILTER(‘シート名’!範囲,’シート名’!条件範囲=条件)

VLOOKUP関数の場合：VLOOKUP($A$2&ROW(A1),データ!$A$2:$C$6,3, FALSE)

まず抽出先シートにFILTER関数の開き括弧まで入力し、データシートをクリックします。

データシートに移動したら、抽出対象の範囲を左クリックしながら囲ってください。数式内に自動で範囲が入力されるので、カンマ(,)で区切ります。

次に条件範囲を選択し、数式内にイコール(=)を入力します。抽出先シートに戻り、条件に指定するセルまたは値を指定してください。

最後に[Enter]キーで数式を確定すると、データシートから条件と一致する値が抽出されます。