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

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

スポンサーリンク

FILTER関数の使い方

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

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

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

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

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

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

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

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

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

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

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

スポンサーリンク

FILTER関数が使えない場合の代用方法

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

VLOOKUP関数で代用する方法

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

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

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

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

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

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

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

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

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

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

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

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

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

VLOOKUP関数の引数:(検索値, 範囲, 列番号, [検索の型])

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

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

エラーを非表示にするには、次のようにIFERROR関数で囲みます。

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

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

IFERROR関数の「エラーの場合の値」に空白を表す""を指定することで、エラーを非表示にしています。

IFERROR関数の引数:(値, エラーの場合の値)

フィルター機能で代用する方法

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

まずデータ範囲を選択した状態で、「データ」→「フィルター」をクリックします。

次に、絞込みのアイコン(▼)を開き、「すべて選択」を押して一旦すべてのチェックを外します。最後に、抽出したいアイテムにのみチェックを入れ直し、「OK」で絞り込みます。

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

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

タイトルとURLをコピーしました