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

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

FILTER関数の使い方

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

例えば FILTER(B2:C7,A2:A7=E2) なら、条件に従ってカテゴリーを果物の行に絞り込みます。結果を範囲から返すので、抽出されるのは商品名と価格の 2 列です。

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

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

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

※条件に直接文字列を指定する場合は、A2:A7=”果物” のように二重引用符を付けてください。

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

複数の条件を指定する方法について、指定したすべての条件を満たす場合(AND)のデータを抽出する方法と、いずれかの条件を満たす場合(OR)のデータを抽出する方法の2つを解説します。

すべての条件を満たすデータを抽出する場合は、条件同士を乗算演算子 (*) で掛け算します。

指定方法：FILTER(範囲,(条件1)*(条件2)*(条件3)…)

例えば FILTER(B2:C7,(A2:A7=E2)*(C2:C7<200)) なら、カテゴリーが果物かつ価格が200円以下の行に絞り込みます。両方の条件を満たすのはリンゴとバナナです。

比較演算子：以下(<=)、より小さい(<)、以上(>=)、より大きい(>)、等しくない(<>)

複数条件のいずれかを満たすデータを抽出する場合、条件同士を加算演算子 (+)で足し算します。

指定方法：FILTER(範囲,(条件1)+(条件2)+(条件3)…)

例えば FILTER(B2:C7,(A2:A7=E2)+(A2:A7=”惣菜”)) なら、カテゴリーが果物または惣菜の行に絞り込みます。いずれかの条件を満たすのは、リンゴ、バナナ、イチゴ、コロッケ、です。

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

まず表など一連の範囲を選択し、データタブからフィルターをクリックします。

フィルター作成のショートカットキー：[Ctrl]+[Sfift]+[L]

次に絞込みアイコンを開き、[すべて選択]をクリックして一度すべてのチェックを外します。

最後に抽出したい項目にのみチェックを入れ直し、[OK]ボタンを押します。表全体から、カテゴリーが果物の商品名と価格を抽出できました。

数値の大きさで絞り込む場合は、[数値フィルター]から抽出条件を選択してください。

今回は、[指定の値より小さい]を選択しました。

オートフィルターオプションの抽出条件に、任意の数値を入力します。

例えば抽出条件が [300 より小さい] なら、価格が300円より小さいデータを抽出可能です。

VLOOKUP関数は範囲の１列目を検索し、検索値と一致する最初の行を指定した列から返します。つまり検索値と一致する行が複数ある場合も、最初の行しか検索されません。

そこで今回はCOUNTIF関数を使って、新しく一意の検索データを作成します。

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

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

COUNTIF関数で取得した番号を元データと結合することで、一意のデータを作成可能です。

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

検索値にはROW関数を結合することで、数式をコピーしたときに番号順の検索値が作成されます。

複数列を抽出したい場合は、VLOOKUP関数の列番号を変更するだけです。

例えば列番号が 3 なら商品名を、列番号が 4 なら価格を抽出します。なお詳しい解説は VLOOKUP関数で複数の結果を抽出する方法をご参照ください。

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