条件に一致するデータを抽出できるFILTER（フィルター）関数

FILTER関数を使用して条件に合うデータを抽出することができます。

数式は先頭のセルに入力すると、スピル機能によって自動で数式が入力されます。

また、元の表はそのままにして抽出されたデータは別の位置へ表示できます。

FILTER関数は、Microsoft 365のExcelとExcel2021で使用できます。

参考FILTER関数が使用できない環境の場合は、［フィルターオプションの設定］を使用してデータを抽出できます。

FILTER関数で条件を満たすデータを抽出する

表の中から条件に合うデータを抽出したい場合は、［データ］タブの［フィルター］をオンにして使用することが多いと思いますが、［FILTER］関数を使用しても同じように抽出できます。

また、列見出しにあるフィルターボタンを使用すると、その表でデータを抽出しますが、FILTER関数を使用すると、任意のセル位置へ表示できます。

以下のような表を作成しています。

セル範囲［A2:D16］の中から［得意先名］の［レンタルもも会社］のデータを抽出します。

セル［F2］をアクティブにして、数式バーに［=fil］まで入力すると、関数の候補一覧に［FILTER］が表示されます。

ここで［FILTER］関数が表示されない場合は、使用できないバージョンです。

［FILTER］が選択された状態で、［Tab］キーを押すか、ダブルクリックすると数式バーへ入力できます。

数式バーに［=FILTER(］と表示された後に、キーボードから［Ctrl］+［A］、または［Shift］+［F3］を押すとFILTERの［関数の引数］ダイアログボックスを表示できます。

以下の内容を入力して、［OK］ボタンをクリックします。

［配列］には、抽出したいデータ範囲を指定します。ここでは、セル［A2］からセル［D16］です。

［含む］には、抽出条件を指定します。セル範囲（セル［B2］からセル［B16］）の中でセル参照［F3］と等しいという条件です。（ここでは、［レンタルもも会社］と等しいという意味です。）

［関数の引数］ダイアログボックスを表示せずに、数式バーに直接入力してもかまいません。

以下のように入力して確定すると、数式がスピルされます。

［納入月日］はシリアル値が表示されていますので、書式を［短い日付形式］に変更します。

参考数式を編集する場合は、必ず数式を設定したセルで行います。数式バーに薄く表示されているのは、スピルのゴーストです。ゴーストのセルでは、数式を編集したり削除はできません。

FILTER関数の構文

［配列］には、抽出したいデータ範囲を指定します。

［含む］には、抽出条件を指定します。

［空の場合］（省略可）には、該当行がない場合の表示を指定します。省略すると［#CALC!］が表示されます。

引数の［空の場合］には、［該当なし］などの文字列を表示するといいと思います。

設定すると、以下のようになります。［関数の引数］ダイアログボックスで設定する場合は、文字列を［"］（ダブルクォーテーション）で囲む必要はありません。自動で表示されます。

数式バーに直接入力する場合は、［"］（ダブルクォーテーション）で囲むのを忘れないようにしてください。

結果、セル［F3］に該当するものがない場合は、［該当なし］と表示されます。

参考Microsoftの記事も参考にしてください。

FILTER 関数（Microsoft）

データがテーブルの場合

データがテーブルであっても同じです。

［配列］（範囲）には、テーブル名を指定します。

［含む］（条件）の範囲には、テーブル名と項目名を指定します。

データがテーブルの場合は、数式は以下のようになります。

参考FILTER関数は、SORT関数と組み合わせるて使用することもできます。SORT関数もMicrosoft 365のExcelとExcel2021で使用できる新関数です。

FILTER関数の［条件］の設定

［含む］（条件）は、配列指定です。

ここでは、［B2:B16=F3］としていますが、これは、セル［B2］からセル［B16］までを順番にセル［F3］の値と等しいかどうかを判定しています。

セル［F3］の値（レンタルもも会社）と等しい場合は［TRUE］、等しくない場合は［FALSE］を返し、［TRUE］となる行が条件と一致しているとして表示される仕組みです。

数式バーで確認する場合は、［含む］（条件）に当たる箇所を範囲選択して、キーボードから［F9］キーを押します。

数式バーは、以下のようになります。［TRUE］と［FALSE] が表示され、［TRUE］の行が条件と一致するので表示されているわけです。

キーボードから［ESC］キーを押すと、数式は元に戻ります。

比較演算子

比較演算子は、以下のものが利用できます。

• ［＝］（等しい）
• ［>=］（以上）
• ［<=］（以下）
• ［>］（より大きい）
• ［<］（より小さい）
• ［<>］（等しくない）

抽出条件にワイルドカード（*）は使用できません。

複数の条件をAND条件（かつ）でデータを抽出

複数の条件をAND（かつ）で繋ぐ場合は、［*］（アスタリスク）を使用します。

複数の条件をOR（または）でデータを抽出

複数の条件をOR（または）で繋ぐ場合は、［+］（プラス）を使用します。

条件に日付を指定する場合は、シリアル値で指定します。

以下のようにフィルターモードでは抽出できないような抽出もFILTER関数では可能です。

参考フィルターモードでは、上記のような条件の抽出はできませんが、［フィルターオプションの設定］では可能です。

抽出条件を以下のように設定します。

FILTER関数で指定した列を抽出するには

FILTER関数では、行だけでなく列を抽出することもできます。

［商品コード］と［金額］の列を抽出したい場合は、セル［F1］とセル［G1］に項目名を入力しておきます。列の並び順は、表の並びのままにします。

そして、条件に［COUNTIF関数］を使用します。

COUNTIF(F1:G1,A1:D1) は、［{0,0,1,1}］という配列が返されるので表の3列目と4列目が抽出されます。

条件指定の箇所を範囲選択して、キーボードから［F9］キーを押してみてください。解除は、［Esc］キーです。

COUNTIF 関数（Microsoft）

セル参照のセルを入力規則のドロップダウンに変更

セル参照のセル［F3］をドロップダウンから選択できるようにすると便利です。ドロップダウンのリストを作成する場合、UNIQUE関数を使用すると便利です。

FILTER関数が使用できる環境であれば、UNIQUE関数も使用できます。

セル［H1］に［得意先リスト］と入力しておきます。そして、セル［H2］に以下のように入力します。

リストは、別シートに作成してもかまいません。

これで、得意先リスト（重複しない値リスト）が作成できます。UNIQUE関数もスピルによって、下のセルにも数式が表示されます。

UNIQUE関数の［関数の引数］ダイアログボックスを表示すると、以下のようになっています。

UNIQUE関数の［配列］には、リストにしたい範囲を指定します。

セル［F3］に入力規則を設定します。

セル［F3］を選択して、［データ］タブの［データツール］グループにある［データの入力規則］をクリックします。

［データの入力規則］ダイアログボックスが表示されますので、［設定］タブの［入力値の種類］で［リスト］を選択します。

［元の値］には、以下のように入力して、［OK］ボタンをクリックして閉じます。

［#］は、［スピル範囲演算子］と呼ばれるものです。［=H2#］とすると、UNIQUE関数で求められた配列全体を指定できます。

得意先リストの位置を変更しても、入力規則内のスピル演算子を変更する必要はありません。自動で修正されます。

これで、セル［F3］をクリックすると、ドロップダウンから選択できるようになります。

参考UNIQUE関数については、以下の記事で解説しています。

［を含む］などの文字列のデータを抽出

FIRTER関数では、ワイルドカードが使用できないので、ちょっと複雑な抽出を行いたい場合は他の関数を使用して作成することになります。

例えば、得意先名に［りんご］が付く会社のみ抽出（［りんごを含む］会社を抽出）したいとします。

FILTER関数の条件に［FIND関数］か［SEARCH関数］を使いますが、エラーを回避するために［IFERROR関数］も使用します。

FIND 関数、FINDB 関数（Microsoft）

SEARCH 関数、SEARCHB 関数（Microsoft）

IFERROR 関数（Microsoft）

［で始まる］の場合はLEFT関数、［で終わる］の場合はRIGHT関数を使用するなど、ワイルドカードの代わりとなる文字列関数を知っておく必要があります。

［フィルターオプションの設定］で抽出する場合との違い

データ抽出は、関数を使用しなくても行うことができます。

フィルターモードで抽出

例えば、フィルターモードがオンの場合は、フィルターボタンをクリックして、［テキストフィルター］から［指定の値を含む］を選択します。

［カスタムオートフィルター］ダイアログボックスに［りんご］と入力して［OK］ボタンをクリックします。

［りんご］を含む得意先名が抽出されます。

参考フィルターモードでの抽出条件は、データの種類によって異なります。

［フィルターオプションの設定］で指定した範囲へ抽出

指定したセル位置にデータを抽出したい場合は、［フィルターオプションの設定］を使用します。

先に抽出条件となるセルに［*りんご*］と入力しておきます。ここでは、セル［G2］ですね。セル［G1］には［得意先名］と入力しておきます。

［データ］タブの［並べ替えとフィルター］グループにある［詳細設定］をクリックします。

［フィルターオプションの設定］ダイアログボックスが表示されます。

［抽出先］で［指定した範囲］を選択し、［リスト範囲］などは以下のように設定します。どれもドラッグで指定できます。

• リスト範囲：$A$1:$D$16
• 検索条件範囲：$G$1:$G$2
• 抽出範囲：Sheet1!$F$5

以下のように抽出できます。

少し複雑な抽出条件の場合

頻繁に抽出条件を変更するのであれば、［FILTER］関数は便利です。スピルによって、数式をコピーする必要もありません。

1回入力すれば、完了です。

しかし、複雑な条件で抽出しようとすると、ワイルドカードが使用できないので長い数式になってしまいます。

例えば、以下の記事内で［フィルターオプションの設定］を使って、［かつ］と［または］のデータ抽出の方法を解説していますが、

同じ抽出をFILTER関数で行うと以下のようになります。比較してみてください。

同じサンプル表を使用していますので、分かりやすいかと思います。［かつ］なので［*］（アスタリスク）で条件をつないでいます。

以下は、日付もセル参照で設定していますが、直接指定する場合は、シリアル値にします。

• 納入月日が---「2014/12/12」より前
• 得意先名が---「りんご」という文字を含む会社
• 商品コードが---「4」で始まるコード

頻繁に抽出条件を変更するならFILTER関数が便利ですが、1回のみや月ごとのデータ抽出であれば［フィルターオプションの設定］で抽出してもいいと思います。

［フィルターオプションの設定］は条件が変われば、そのたびに作業が必要になりますが、操作は簡単です。

同じ条件で［フィルターオプションの設定］を使用するのであれば、以下のように設定します。

検索条件は、比較演算子やワイルドカードを使って以下のようにします。

後は、［抽出先］で［指定した範囲］を選択し、各範囲をドラッグで指定します。

以下のようにデータが抽出されます。どういう内容の条件で抽出したかも分かりやすいですね。

参考［フィルターオプションの設定］ダイアログボックスには、［重複するレコードは無視する］という便利な機能もあります。

特定の列のみの抽出と重複レコードの削除