概要
「欠損している行だけ一覧にして、まとめてチェックしたい」
「どこが未入力なのかを、別シートに抽出したい」
こういうときにぴったりなのが
FILTER 関数による「欠損行の抽出」です。
FILTER は「範囲」と「条件」を指定して
条件に合う行だけを抜き出す関数です。
ここでは、空白セル＝欠損として、
欠損行だけをきれいに取り出すパターンをやさしく解説します。
FILTER 関数の基本
FILTER の書式は次のとおりです。
=FILTER(配列, 条件)
配列
… 抽出したい元データの範囲（行ごとに抜き出す）
条件
… TRUE / FALSE のかたまり（TRUE の行だけ残る）
欠損行抽出では、
「どこかが空白か？」という条件を作って、
TRUE になった行だけを FILTER で抜き出します。
単一列の欠損行を抽出する基本形
例えば、A列に「メールアドレス」が入っているとします。
メールアドレスが欠損している行だけ抽出したい場合はこう書きます。
=FILTER(A2:A100, A2:A100="")
A2:A100 が配列、
A2:A100=”” が条件（空白なら TRUE）です。
結果として、
メールアドレスが空白の行だけが一覧として返ってきます。
もし、メールだけでなく「行全体」を取りたいなら、
配列を A2:D100 のように広げます。
=FILTER(A2:D100, A2:A100="")
これで、メール欠損の人の行全体（氏名や部署なども含む）が抽出できます。
複数列のどれかが欠損している行を抽出する
実務で多いのは
「氏名・メール・電話のどれか一つでも欠けていたら要確認」
というパターンです。
例えば、A列＝氏名、B列＝メール、C列＝電話の場合。
どれかが空白なら TRUE になる条件は次のように書けます。
=FILTER(A2:C100, (A2:A100="") + (B2:B100="") + (C2:C100="") > 0)
(A2:A100=””) は「氏名が空白なら TRUE」
(B2:B100=””) は「メールが空白なら TRUE」
(C2:C100=””) は「電話が空白なら TRUE」
TRUE を 1、FALSE を 0 と見なして足し算し、
合計が 0 より大きければ「どれかが空白」と判定しています。
これで、
氏名・メール・電話のいずれかが欠損している行だけが抽出されます。
COUNTBLANK と組み合わせて「欠損項目数」で抽出する
COUNTBLANK は「空白セルの数を数える」関数です。
行ごとに COUNTBLANK を使うと、
その行でいくつ欠損しているかを数字で判定できます。
例えば A2:C2 に 1人分の情報があるとき、
欠損項目数はこう求められます。
=COUNTBLANK(A2:C2)
これを FILTER の条件にすると、
「欠損が 1 つ以上ある行だけ抽出」ができます。
=FILTER(A2:C100, COUNTBLANK(A2:C100)>0)
COUNTBLANK(A2:C100) は
スピル対応の Excel だと行ごとに評価され、
0 の行（欠損なし）は除外されます。
結果として、
何らかの欠損がある行だけがフィルタリングされます。
特定列だけ必須項目として欠損行を抽出する
「A列と B列は必須、C列は任意」というように、
必須項目だけをチェックしたい場合もよくあります。
例えば、A＝氏名、B＝メールを必須とするなら
A または B が空白の行だけ抽出したいので、条件は次のようになります。
=FILTER(A2:C100, (A2:A100="") + (B2:B100="") > 0)
C列の欠損は無視して、
必須項目の欠損行だけにフォーカスできるわけです。
欠損行抽出結果を別シートに出す
実務的には、
元データのシートとは別のシートに
「欠損行一覧」を出すことが多いです。
例えば、
元データが Sheet1!A2:C100 にあるとして、
Sheet2 の A2 に次のように書きます。
=FILTER(Sheet1!A2:C100, COUNTBLANK(Sheet1!A2:C100)>0)
これで Sheet2 に、
「欠損が 1 つ以上ある行」だけがスピル表示されます。
元データを更新すれば、
欠損行一覧も自動で更新されるので、
データチェック用のビューとしてとても便利です。
例題
問題1
A2:A100 にメールアドレスが入っています。
メールアドレスが空白の行だけを抽出し、
A列だけを結果として表示したいです。
FILTER 関数の式を書いてください。
解答例
=FILTER(A2:A100, A2:A100="")
問題2
A2:C100 に「氏名・メール・電話」があります。
このうちどれか 1 項目でも空白の行だけを、行全体ごと抽出したいです。
FILTER の条件部分に (列範囲=””) を組み合わせて式を書いてください。
解答例
=FILTER(A2:C100, (A2:A100="") + (B2:B100="") + (C2:C100="") > 0)
問題3
A2:C100 に 1人分 3項目のデータがあります。
COUNTBLANK を使って「欠損が 1 つ以上ある行だけ」を FILTER で抽出する式を書いてください。
解答例
=FILTER(A2:C100, COUNTBLANK(A2:C100)>0)
問題4
A2:C100 に「氏名・メール・電話」があり、
氏名(A列)とメール(B列)を必須項目とします。
A列または B列が空白の行だけを抽出する FILTER 式を書いてください。
解答例
=FILTER(A2:C100, (A2:A100="") + (B2:B100="") > 0)
問題5
元データが Sheet1 の A2:C100 にあります。
別シート Sheet2 の A2 に、
「何らかの欠損がある行だけ」を抽出して表示したいです。
COUNTBLANK と FILTER を組み合わせた式を書いてください。
解答例
=FILTER(Sheet1!A2:C100, COUNTBLANK(Sheet1!A2:C100)>0)
まとめ
欠損行抽出 → FILTER のポイントは、次の 2 つです。
1つ目は「空白かどうか」を条件として TRUE / FALSE を作ること。
2つ目は、その条件を FILTER に渡して「TRUE の行だけ抜き出す」こと。
型としては、次の 2 パターンを覚えておくと応用しやすくなります。
=FILTER(元データ範囲, 列範囲="")
=FILTER(元データ範囲, COUNTBLANK(行方向の範囲)>0)
欠損チェック用のビューを作っておくと、
大きなデータでも「どこに問題があるか」が一瞬で見えるようになります。