FILTER関数 ● 表や配列からデータを抽出する (1)基本の使い方

［Excel 2021以降］

Excelの FILTER（フィルター）関数の使い方を紹介します。

FILTER関数はExcel 2021で追加された関数です。この関数を使うと、セルに入力された表や表形式の配列から条件に合致するデータを抽出できます。［データ］タブにあるフィルターの機能とは異なり、元の表はそのまま、別のセル（FILTER関数を入力したセル）に抽出結果が表示されます。ここでは、FILTER関数による基本的な抽出方法を紹介します。

書式

表や配列からデータを抽出する
=FILTER(配列, 条件, ［見つからない場合］)

［配列］から［条件］に合致するデータを抽出します。条件に合致するデータが存在しない場合は［見つからない場合］に指定した値を表示します。関数式は、動的配列数式として入力されます。

■引数

使用例1…基本的な使い方（完全一致の条件で行単位の抽出）

ここでやること

図1の名簿から所属が「本店」である社員データを抽出します。抽出条件の「本店」はセルF3に入力されているものとします。

図1　セルF3に入力された所属（ここでは「本店」）の社員のデータを抽出したい。

数式を入力する

FILTER関数の引数［配列］に名簿の社員データのセルA3～D9を指定します。［条件］には、「所属」欄のセルC3～C9の値がセルF3の値に等しいかどうかを判定する条件式「C3:C9=F3」を指定します。［見つからない場合］には、「"該当なし"」を指定しました。

=FILTER(配列, 条件, ［見つからない場合］)

セルH3 | =FILTER(A3:D9,C3:C9=F3,"該当なし")

セルH3に数式を入力して［Enter］キーを押すと、セルH3～K5の範囲に数式がスピル（隣接するセルに数式が自動拡張すること）し、3件分の社員データが表示されます。

図2　セルH3にFILTER関数を入力して［Enter］キーを押すと、数式がセルK5までスピルして、「本店」の社員データが表示される。

数式がスピルする範囲は、抽出条件に応じて自動で変化します。セルF3の条件を「青山店」に変更すると、「青山店」の社員のデータ数（2件）の範囲にスピルし直されます。

図3　スピルする範囲は抽出条件に応じて変化する。

［見つからない場合］に「"該当なし"」を指定したので、条件に合致するデータが存在しない場合は、数式を入力したセルH3に「該当なし」と表示されます。

図4　「NY店」の社員はいないので「該当なし」と表示される。

ちなみに図4の式で［見つからない場合］を省略していた場合、セルH3にエラー値「#CALC!」が表示されます。

抽出条件の考え方

［条件］に指定した「C3:C9=F3」は、「C3=F3」「C4=F3」「C5=F3」……「C9=F3」という7つの条件を表します。試しに空いているセルに「=C3:C9=F3」と入力してみましょう。条件式が成立する場合は「TRUE」（真の意味）、成立しない場合は「FALSE」（偽の意味）が7行1列の範囲に表示されます。「TRUE」と表示されている行の社員データが、FILTER関数によって抽出されるデータです。

図5　セルH3に「=C3:C9=F3」と入力するとセルH9まで数式がスピルして、条件の結果を確認できる。

引数［条件］には、「TRUE、FALSE」からなる配列定数を指定することもできます。Excelでは「TRUE」は「1」、「FALSE」は「0」で表せるので、「1、0」からなる配列定数を指定しても構いません。次の2つの数式の結果は図2と同じです。

=FILTER(A3:D9,{TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE},"該当なし")

=FILTER(A3:D9,{1;0;1;0;0;1;0},"該当なし")

また、図6のように［条件］に「TRUE、FALSE」や「1、0」を入力したセル範囲を指定しても抽出を行えます。行を抽出する場合は［条件］に［配列］と同じ行数分の配列を指定する必要があるので、条件の値は縦方向に入力してください。

セルH3 | =FILTER(A3:D9,F3:F9,"該当なし")

図6　「1、0」を入力した列を抽出条件として指定できる。

使用例2…表から特定の列を取り出す

作業セルを使用して列を取り出す方法

表から特定の列を取り出すには、取り出す列に「1」、取り出さない列に「0」を入力したセルを用意します。そのセル範囲をFILTER関数の引数［条件］に指定します。図7では表の2列目と4列目を取り出しています。

セルH3 | =FILTER(A3:D9,A11:D11)

図7　「1」が入力されている2列目の「名前」と4列目の「年齢」が取り出される。

もしくは、［条件］に「{0,1,0,1}」を指定しても、表の2列目と4列目を取り出せます。

セルG3 | =FILTER(A3:D9,{0,1,0,1})

抽出先に入力した見出しに合わせて列を取り出す方法

あらかじめ抽出先に入力しておいた見出しに合わせて列を取り出すには、［条件］に「COUNTIF(H2:I2,A2:D2)」を指定します。

セルH3 | =FILTER(A3:D9,COUNTIF(H2:I2,A2:D2))

図8　COUNTIF関数を利用して条件を設定する。

COUNTIF関数は、［条件範囲］の中に含まれる［条件］の数を返します。「COUNTIF(H2:I2,A2:D2)」は、「COUNTIF(H2:I2,A2)」「COUNTIF(H2:I2,B2)」「COUNTIF(H2:I2,C2)」「COUNTIF(H2:I2,D2)」の4つの値を求めて「{0,1,0,1}」という配列を返します。空いたセルに入力すると、COUNTIF関数が返す配列を確認できます。

=COUNTIF(条件範囲, 条件)

セルA11 | =COUNTIF(H2:I2,A2:D2)

図9　COUNTIF関数の詳解。

メモ

COUNTIF関数の戻り値を手軽に確認するには

FILTER関数の引数［条件］に指定したCOUNTIF関数の式を数式バーでドラッグすると、戻り値を確認できます。

使用例3…表から行と列を同時に取り出す

表から行と列を同時に取り出すには、2つのFILTER関数をネストさせて使用します。図10では名簿から「本店」の社員データの名前と年齢を取り出します。

セルH3 | =FILTER(FILTER(A3:D9,COUNTIF(H2:I2,A2:D2)),C3:C9=F3,"該当なし")

図10　表から「本店」の行、「名前」「年齢」の列を取り出す。

関連記事

• FILTER関数 ● 表や配列からデータを抽出する (2)抽出条件の指定例
• 条件付き書式…スピルした範囲に自動で色や罫線を表示