サービス
サービスを探す
プロ人材を探す
仕事を探す
ブログを探す
FILTERが空を返す・#N/Aになるときに見る順番
記事IT・テクノロジー
条件を絞ったはずなのに、行が1件も返ってこない。逆に、絞ったつもりなのに
全部の行が返ってくる。セルには `#N/A` とだけ表示され、何が悪いのか
手がかりが無い――FILTER関数はこの「原因が分かりにくいエラーの出方」が
特徴です。起きやすい順に、見るべき場所を整理します。
■ FILTERの基本構造
=FILTER(A2:A100, B2:B100="東京")
第1引数が「返す対象の範囲」、第2引数以降が「条件」です。
条件範囲の各行がTRUEかどうかを1行ずつ判定し、TRUEだった行だけを
対象範囲から抜き出して返します。この「1行ずつ対応させて判定する」
という仕組みが、次に挙げる不具合の大半の原因になります。
■ 症状1：#N/A「配列のサイズが一致していません」
=FILTER(A2:A100, B2:B50="東京")
対象範囲が100行、条件範囲が50行というように、行数が一致していないと
即座に `#N/A` になります。これはFILTERが「対象範囲のn行目と条件範囲の
n行目を1対1で対応させる」仕組みだからで、行数がずれていると
どの行を判定すればいいか決められません。
範囲をあとから広げた・狭めたときに片方だけ直し忘れる、というのが
典型的な発生パターンです。エラーが出たら、まず全ての範囲の
開始行・終了行を数え直してください。行数を厳密に揃えるのが面倒であれば、
最初から `A2:A` のように列全体（の2行目以降）で範囲を揃えておくと、
このズレ自体が起きなくなります。
■ 症状2：条件を書いたのに、該当するはずの行が返らない
=FILTER(A2:A100, B2:B100=100)
B列に「100」という数字が見えるのに、この条件で1件も返らないことが
あります。原因は、B列の中身が数値ではなく「100」という見た目の
文字列として入力されていることです。他のシステムからコピーした
データや、CSVの取り込み結果でよく起きます。
セルを選択したとき数値なら右揃え、文字列なら左揃えになるので、
見分けるにはこの揃え方を確認するのが早いです。文字列になっている場合は、
`=VALUE(B2:B100)` で数値に変換するか、対象列をコピーして
「値のみ貼り付け」→「区切り位置」を使って一括で数値化してから
FILTERにかけてください。日付の条件でも同じことが起きます。
セルが「日付に見える文字列」であって実際の日付型でない場合、
日付での比較条件がすべて不一致になります。
■ 症状3：複数条件を組み合わせたら、想定と違う件数になる
複数条件を書くときは、AND（すべて満たす）かOR（いずれか満たす）かで
演算子を使い分けます。
AND条件（すべて満たす行だけ）：
=FILTER(A2:A100, (B2:B100="東京")*(C2:C100>=100))
OR条件（いずれかを満たす行）：
=FILTER(A2:A100, (B2:B100="東京")+(C2:C100="大阪"))
`*`（掛け算）はAND、`+`（足し算）はORです。TRUE/FALSEは
内部的に1と0として扱われるため、掛け算だと両方1のときだけ結果が1
（AND）、足し算だとどちらか1つでも1なら結果が1以上＝TRUE扱い（OR）
になります。この2つを取り違えると、ANDのつもりがORの結果になり、
想定より多くの行が返ってきます。件数が想定より多いときはORとAND
の取り違えを、想定より少ないときは条件の書き方自体（文字列の
前後に余分な空白が入っていないかなど）を疑ってください。
■ 症状4：該当なしのとき、#N/Aのまま表に穴が空く
FILTERは、条件に合う行が1件もないと `#N/A` を返します。これは
仕様であり、それ自体は不具合ではありませんが、他の集計表の中で
使っていると、その1つの `#N/A` が原因で表全体が崩れて見えることが
あります。
=IFERROR(FILTER(A2:A100, B2:B100="該当なし"), "該当なし")
このように外側をIFERRORで包み、該当なしのときの表示を指定して
おくと安全です。ただし、IFERRORで置き換えた後の値（上の例では
"該当なし" という文字列）は配列ではなく単一の値になるため、
後続の数式がFILTERの結果を「複数行の配列」として扱う設計になっている場合、
0件のときだけ挙動が変わってエラーになることがあります。後述の
症状5と合わせて、置き換え後の値の形も意識しておく必要があります。
■ 症状5：FILTERの結果を他の関数にそのまま渡すとエラーになる
=SUM(FILTER(A2:A100, B2:B100="東京"))
FILTERは複数行の配列を返す関数です。SUMのように配列をそのまま
受け取れる関数であれば問題なく計算できますが、単一の値しか
受け取れない関数（1つ目に見つかった値だけを使うタイプの関数など）に
そのまま渡すと、配列の中の意図しない要素だけが使われたり、
エラーになったりします。
また、症状4のようにIFERRORで該当なし時の戻り値を文字列にしていると、
0件のときだけFILTERの出力が「配列」から「単一の文字列」に変わるため、
SUMのように配列を前提にしている関数がエラーを起こすことがあります。
FILTERの結果を他の関数に渡す設計にするときは、0件だったときに
何が返るかまで含めて確認しておくと、あとから該当なしのケースだけ
エラーになる、という事態を避けられます。
■ 症状6：条件に文字列の部分一致を使ったら、想定より多く（少なく）返る
=FILTER(A2:A100, ISNUMBER(SEARCH("東京", B2:B100)))
FILTERの条件は基本的に「完全一致」で評価されるため、"東京都渋谷区"
のような文字列を"東京"で拾いたい場合は、SEARCHやFINDと組み合わせて
部分一致条件を作る必要があります。SEARCHは大文字・小文字を区別せず、
FINDは区別するという違いがあり、全角・半角の違いや表記ゆれ
（"東京都"と"東京"など）まではどちらも吸収してくれません。
想定より多くヒットする場合は関係のない文字列まで部分一致している
ケースを、想定より少ない場合は表記ゆれで一致していないケースを
それぞれ疑ってください。
■ やってはいけないこと
エラーが出るたびに、条件範囲を丸ごと列全体（B:B）に広げて
様子を見ること。行数のズレは解消されますが、症状2や症状3の
原因（型の不一致、演算子の取り違え）はそのまま残るため、
根本原因を見えにくくするだけで解決にはなりません。
■ それでも解決しないとき
・行数を揃えたのに #N/A が消えない
・型を数値に直したはずなのに、条件が一致しない
・複数条件・複数のFILTERを組み合わせていて、どこで想定とずれたか
自分では切り分けられない
このあたりまで来たら、条件を1つずつ外しながら確認した方が早いです。
私はスプレッドシートの数式・関数まわりの調査と修正を承っています。
どの条件でどうずれていたかを突き合わせてお返ししています。
――
この記事の執筆には生成AIを活用しています。内容は実際の挙動を確認して書いています。
このブログを見た人にオススメ
