FILTER関数の使い方｜複数条件の書き方と#CALC!・#SPILL!の直し方

FILTER関数は、条件に合う行をまとめて別の場所へ取り出す関数です。VLOOKUPが1件しか返せないのに対し、該当する行を全件返します。使えるのはMicrosoft 365とExcel 2021以降で、2019以前では #NAME? になります。

この記事でわかること

---

FILTER関数の構文と引数

構文

=FILTER(配列, 含む, [空の場合])

引数	指定するもの	注意点
配列	取り出したい元のデータ範囲	見出し行は含めない
含む	条件式（TRUE／FALSEの並び）	配列と行数を揃える。ずれると #VALUE!
空の場合	該当0件のときに返す値	省略すると #CALC! になる

FILTERは動的配列関数です。数式を入れるのは左上の1セルだけで、結果は必要な行数・列数へ自動的に広がります。この広がりをスピルと呼びます。

⚠️ 使えるバージョン

FILTER関数はMicrosoft 365とExcel 2021以降で利用できます。Excel 2016・2019では使えません。2019以前で開くと、関数名の前に接頭辞が付いた文字列として表示され、結果は #NAME? になります。古い環境に配布するブックでは使わない、という判断が要ります。

---

基本の使い方

A列に支店、B列に商品、C列に数量、D列に金額が並ぶ表を例にします。大阪支店の行だけを取り出す式です。

=FILTER($A$2:$D$100, $A$2:$A$100="大阪", "該当なし")

数式は結果を出したい場所の左上のセルだけに入力します。下の行にも同じ式をコピーする必要はありません。むしろコピーすると、スピルの邪魔になります。

第3引数の "該当なし" は省略できますが、省略すると該当0件のときに #CALC! が表示されます。集計表に組み込むなら、必ず指定してください。

条件をセル参照にする

条件を直接書かず、入力セルを参照させると使い回せます。F1に支店名を入力する形です。

=FILTER($A$2:$D$100, $A$2:$A$100=$F$1, "該当なし")

F1を書き換えるだけで、抽出結果がその場で入れ替わります。元データを更新しても自動で追従するため、オートフィルターのかけ直しが不要になります。

---

実務でよく使う4パターン

① 複数条件（AND・OR）

FILTERにはAND関数もOR関数も使いません。条件式を掛け算と足し算でつなぎます。

つなぎ方	意味	書き方
*	かつ（AND）	($A$2:$A$100="大阪")*($D$2:$D$100>=10000)
+	または（OR）	($A$2:$A$100="大阪")+($A$2:$A$100="京都")

実際の式です。各条件をかっこで囲むのを忘れないでください。

=FILTER($A$2:$D$100, ($A$2:$A$100="大阪")*($D$2:$D$100>=10000), "該当なし")

AND関数を使うと、条件全体がTRUEかFALSEの1個の値に潰れてしまい、意図した絞り込みになりません。ここが動的配列関数に特有の書き方です。

② 「〜を含む」で絞る

部分一致にはSEARCH関数を組み合わせます。

=FILTER($A$2:$D$100, ISNUMBER(SEARCH("ペン", $B$2:$B$100)), "該当なし")

SEARCHは見つかった位置を返し、見つからなければエラー。それをISNUMBERでTRUE／FALSEに変換する仕組みです。SEARCHは大文字と小文字を区別しません。区別したい場合はFIND関数に置き換えます。

③ 必要な列だけを取り出す

FILTERを二重にすると、行と列の両方を絞れます。外側の「含む」に、列数と同じ要素数の0と1の配列を渡す形です。

=FILTER(FILTER($A$2:$D$100, $A$2:$A$100="大阪"), {1,1,0,1}, "該当なし")

内側で行を絞り、外側で2列目までと4列目だけを残しています。{1,1,0,1} は横方向の配列定数で、カンマ区切りが列の並びに対応します。

④ 並べ替え・重複除去と組み合わせる

同じ動的配列のSORT関数・UNIQUE関数と入れ子にできます。抽出したうえで金額の降順に並べる式です。

=SORT(FILTER($A$2:$D$100, $A$2:$A$100="大阪", ""), 4, -1)

SORTの第2引数4は「4列目で並べる」、第3引数-1は降順を表します。重複のない一覧が欲しいなら =UNIQUE(FILTER(...)) の形です。

---

うまくいかないときの原因と直し方

エラー表示で原因はほぼ特定できます。

表示	原因	直し方
#CALC!	条件に合う行が1件もない	第3引数に "該当なし" や "" を指定する
#SPILL!	結果が広がる先に別のデータがある	下・右のセルを空ける。結合セルも解除する
#SPILL!	数式をテーブルの中に入れている	テーブルの外のセルへ移す
#VALUE!	配列と「含む」の行数が違う	どちらも同じ行番号で始め、同じ行番号で終える
#NAME?	Excel 2016・2019で開いている	使えない。オートフィルター等で代替する
結果が1行だけ	「含む」を1つの値にしてしまっている	AND関数をやめ、* でつなぐ
該当0件なのに空欄が1つ残る	第3引数の "" が返っている	仕様どおり。件数はCOUNTIFSで数える

#SPILL!が消えないとき

#SPILL! は「結果を置く場所が足りない」というエラーです。見た目には空欄でも、スペースが1文字だけ入っているセルが邪魔をしていることがあります。

エラーの出ているセルを選ぶと、広がろうとしている範囲が破線で表示されます。その破線の内側をまとめて選択し、Deleteキーで消してから再計算してください。

結合セルも原因になります。スピル先に結合セルがあると、FILTERは結果を展開できません。

結果のセルを直接編集できない

スピルした結果のうち、左上以外のセルは編集も削除もできません。「配列の一部を変更することはできません」という警告が出ます。

修正するときは左上のセルの数式を直す、消すときは左上のセルをDeleteする。これが原則です。

---

似た機能・関数との使い分け

手段	対応バージョン	選ぶ場面
FILTER	365／2021〜	該当行を全件取り出し、自動更新させたい
オートフィルター	2016〜	その場で一時的に絞って見るだけ
フィルターの詳細設定	2016〜	複雑な条件で別の場所へ抽出する（手動実行）
XLOOKUP	2021〜	一致する1件だけを引く
VLOOKUP	2016〜	同上。2016・2019の相手にも渡す
UNIQUE・SORT	365／2021〜	重複除去・並べ替え。FILTERと入れ子にする
ピボットテーブル	2016〜	明細ではなく集計結果を見たい

判断の軸は2つです。1件か全件か、そして自動更新が要るか。

1件でよければXLOOKUPかVLOOKUP。全件なら、その場で見るだけならオートフィルター、シートに残して自動更新させたいならFILTER、という並びになります。

---

よくある質問

---

まとめ

あわせて読みたい