MENU

Power Queryで作成したテーブルにExcelのFILTERが#CALC!になる原因と対処法｜部分一致（SEARCH/COUNTIF）で抽出する手順

Excel

Power Queryで変換して読み込んだテーブルに、ExcelのFILTER関数を使ったら#CALC!になって抽出できない。そんなときは「Power Queryの結果だから互換性がない」のではなく、条件が0件になっている、または文字の揺れで一致していないケースがほとんどです。完全一致と部分一致の両面から、原因の切り分けと実用的な式をまとめます。

目次

現象の整理：Power Queryの結果テーブル t_Values を FILTER で絞り込みたい

前提は次のような状態です。

  • Power Queryで元データを変換し、ワークシートへ読み込み
  • 結果としてExcelのテーブル（例：t_Values）が作成されている
  • 列は Number / Title
  • 同じシートのテーブル外で、セルL2に入力した文字列を使って Title を抽出したい

ところが、次のような式が常に #CALC!になる。

=FILTER(t_Values, t_Values[Title]=L2)

この現象で一番多い原因は、Power QueryのせいではなくFILTERの「条件が0件」になっていることです。加えて、見た目は同じでも一致しない「余計な空白・改行・不可視文字」が混ざっていると、完全一致はほぼ確実に外れます。

結論：Power Queryテーブルでも FILTER は普通に使える（互換性の問題ではない）

Power Queryで「テーブルとしてシートに読み込んだ」結果は、Excelから見ると通常のExcelテーブルです。つまり、構造化参照（t_Values[Title]のような書き方）ができる時点で、FILTERを含む動的配列関数の対象として扱えます。

逆に言えば、Power Query側で「接続のみ」「データモデルのみ」にしていて、シート上にテーブルが存在しない場合は、そもそも参照できるt_Valuesがありません。質問の状況では「シート上にテーブル t_Values がある」と明記されているため、ここは該当しない想定です。

ポイント：「Power Queryで作ったからFILTERが使えない」ではなく、FILTERのinclude条件がTRUEになっていない（=一致していない）可能性が高い。

#CALC! が出る代表パターンを先に押さえる

#CALC!は「計算はできたが、結果を返せない」系のエラーとして出ます。FILTERの場合、現場で多いのは次の2つです。

状況FILTERの挙動対策
条件に一致する行が0件（includeが全部FALSE）#CALC!（if_empty省略時）第3引数 if_empty を入れる／条件式を見直す
include配列のサイズが合っていない（行数がズレている等）#CALC! または意図しない結果arrayとincludeの行数を揃える（構造化参照の範囲を統一）
include配列にエラーが混ざる（SEARCHが#VALUE!を返す等）#CALC!や別のエラーが連鎖ISNUMBER、IFERROR、CLEANなどで前処理

今回の式は構造化参照として自然なので、サイズ不一致よりも0件が原因であることが多いです。まずは「本当に0件なのか」を見える化してから進めると、最短で解決します。

最初にやるべき動作確認：完全一致で「該当なし」を出す

元の式は「TitleがL2と完全一致する行だけ返す」式です。1件も一致しなければ#CALC!になります。動作確認として、第3引数（if_empty）を入れてください。

行全体（Number/Title）を返す

=FILTER(t_Values, t_Values[Title]=L2, "該当なし")

Title列だけ返す

=FILTER(t_Values[Title], t_Values[Title]=L2, "該当なし")

ここで「該当なし」になるなら、Power Queryテーブルの互換性ではなく一致していないことが確定です。次は「なぜ一致しないか」を切り分けます。

部分一致で抽出したいなら：SEARCH（またはFIND）で判定する

「L2がTitleに含まれていればOK」という部分一致は、比較演算子（=）だけでは実現できません。Titleの中にL2が含まれるかどうかを判定して、そのTRUE/FALSE配列をFILTERのincludeに渡します。

定番：SEARCH + ISNUMBER（大文字小文字を区別しない）

Titleの各セルに対してSEARCHを行い、見つかれば数値（開始位置）が返るのでISNUMBERでTRUEにします。

=FILTER(t_Values[Title], ISNUMBER(SEARCH(L2, t_Values[Title])), "該当なし")

行全体を返したい実用形はこちらです。

=FILTER(t_Values, ISNUMBER(SEARCH(L2, t_Values[Title])), "該当なし")

大文字小文字を区別したい：FIND

英数字を扱うケースで「ABC」と「abc」を区別したいならFINDを使います（日本語中心ならSEARCHで十分なことが多いです）。

=FILTER(t_Values, ISNUMBER(FIND(L2, t_Values[Title])), "該当なし")

別解：COUNTIFのワイルドカードで部分一致（シンプルで覚えやすい）

SEARCHが「検索して位置を返す」のに対して、COUNTIFは「ワイルドカード（*）で部分一致カウント」を作れます。includeにはTRUE/FALSEが必要なので、>0で判定します。

=FILTER(t_Values, COUNTIF(t_Values[Title], "*"&L2&"*")>0, "該当なし")

COUNTIFは式が短く、説明もしやすいのでチーム運用で好まれることがあります。一方で、L2に「*」「?」などのワイルドカード文字が入ると意図が崩れるため、検索文字列が自由入力の場合はSEARCHの方が安全です。

方法長所注意点おすすめ度
SEARCH + ISNUMBERワイルドカードの影響を受けにくい／日本語でも直感的見つからないと#VALUE!（ISNUMBERで回避しやすい）◎
FIND + ISNUMBER大文字小文字を区別できる英字のケースでのみメリットが出やすい○
COUNTIF + “*”&L2&”*”式が短い／説明が簡単L2に*や?が含まれると挙動が変わる（エスケープが必要）○

「常に#CALC!」が続くときの定番チェック

部分一致の式にしても結果が出ない場合、原因はたいてい「データの見た目と実体が違う」ことです。Power Queryで読み込んだデータは、元のCSV・Web・PDF・システム出力など由来によって、余計な空白や改行、ノーブレークスペース（見えない空白）が混ざりがちです。

L2が空欄のとき、全部出てしまう／逆に何も出ない問題

SEARCHは検索文字が空文字の場合、常に1を返すため「全部TRUE」になり全行が返ります。運用上それが困るなら、先に空欄ガードを入れるのが安全です。

=IF(LEN(TRIM(L2))=0,"",FILTER(t_Values,ISNUMBER(SEARCH(TRIM(L2),t_Values[Title])),"該当なし"))

前後スペースが混ざって完全一致しない（最頻出）

見た目が同じでも、末尾に半角スペースが1つあるだけで完全一致は外れます。Power Query側でTrimしているつもりでも、列の途中に不可視文字が残っているケースもあります。

まずExcel側でTRIMを噛ませて完全一致を試すと、原因が空白かどうかがすぐ分かります。

=FILTER(t_Values, TRIM(t_Values[Title])=TRIM(L2), "該当なし")

部分一致でも、検索文字（L2）側に余計な空白があるとヒット率が落ちます。TRIMは部分一致でも効きます。

=FILTER(t_Values, ISNUMBER(SEARCH(TRIM(L2), TRIM(t_Values[Title]))), "該当なし")

改行・タブ・制御文字が混ざっている（CLEANで落とす）

Web由来のデータや複数行テキストには、CHAR(10)（改行）やCHAR(9)（タブ）などが混ざることがあります。CLEANで制御文字を除去し、SUBSTITUTEで改行をスペースに変換するのが定番です。

=FILTER(
  t_Values,
  ISNUMBER(
    SEARCH(
      TRIM(CLEAN(L2)),
      TRIM(CLEAN(SUBSTITUTE(t_Values[Title],CHAR(10)," ")))
    )
  ),
  "該当なし"
)

WordPress貼り付けの都合で改行の見た目が気になる場合は、1行にまとめた実務形にしてもOKです。

=FILTER(t_Values,ISNUMBER(SEARCH(TRIM(CLEAN(L2)),TRIM(CLEAN(SUBSTITUTE(t_Values[Title],CHAR(10)," "))))),"該当なし")

ノーブレークスペース（CHAR(160)）が混ざるとTRIMが効かないことがある

Webページからのコピー、HTML由来、システムによっては、見た目は普通の空白でも実体がノーブレークスペース（NBSP）になっていることがあります。これはTRIMだけでは取り切れないことがあるため、SUBSTITUTEで置換してからTRIMするのが安定します。

=LET(
  k, TRIM(SUBSTITUTE(L2,CHAR(160)," ")),
  t, TRIM(SUBSTITUTE(t_Values[Title],CHAR(160)," ")),
  FILTER(t_Values, ISNUMBER(SEARCH(k,t)), "該当なし")
)

LETを使うと「何を整形しているか」が式から読み取りやすくなり、後から保守する人に優しくなります。Excelの業務ファイルでは、こうした可読性がそのまま障害対応スピードに直結します。

全角・半角、英字の大小、表記ゆれ（AとＡ、ｶとカなど）

日本語データは、全角半角の揺れが混ざりやすいです。完全一致だけでなく部分一致でも、入力側（L2）とデータ側（Title）の文字種がズレるとヒットしません。

  • 英数字の全角/半角が混ざる → ASC（全角→半角）やJIS（半角→全角）で寄せる
  • 英字の大小が混ざる → SEARCHは大小無視だが、FINDは区別する（意図に合わせる）
  • カナの表記ゆれ → 可能ならPower Query側で正規化（後述）

例：英数字は半角に寄せて部分一致する（環境によっては効果が大きい）

=LET(
  k, ASC(TRIM(L2)),
  t, ASC(t_Values[Title]),
  FILTER(t_Values, ISNUMBER(SEARCH(k,t)), "該当なし")
)

原因切り分けを速くする「検査用」ミニ式

現場では「どこでズレているか」を短時間で特定するのが重要です。以下は、調査の初動で役立つ式です（テーブル外の空きセルに入力して確認します）。

完全一致が本当に0件か確認する

まず、完全一致が何件あるか数えます。0なら#CALC!は仕様どおりです。

=COUNTIF(t_Values[Title], L2)

COUNTIFが0なのに「見た目は同じ」と感じる場合、余計な空白や不可視文字がほぼ確定です。

L2の前後スペース疑いを数値で見る

=LEN(L2)
=LEN(TRIM(L2))

2つの値が違えば、L2に余計なスペースがあります。

Title列側のLENを一覧で見る（動的配列でスピルさせる）

Title列の文字数をスピル表示し、異常に長い/短い行がないかを見ます。

=LEN(t_Values[Title])

「同じはずの項目だけLENが1大きい」といった場合、末尾スペースやNBSPの可能性が高いです。

不可視文字の正体を突き止める（UNICODEで確認）

対象セルの末尾付近が怪しい場合、MIDで1文字ずつ抜き出してUNICODEでコードを見ると、CHAR(160)のような原因を特定できます。

=UNICODE(MID(A1, LEN(A1), 1))

A1は調べたいセルに置き換えてください。末尾が32（通常スペース）ではなく160（NBSP）などになっていれば、SUBSTITUTEで置換する根拠になります。

実務で安定させるコツ：Power Query側で「整形してから」読み込む

Excel数式での部分一致は即時性が強みですが、データの汚れが大きいと数式が複雑化しがちです。運用を安定させるなら、Power Query側で「検索に使う列」を整形しておくのが有効です。

たとえば、Titleをトリム＆クリーンし、NBSPも通常スペースに寄せるステップを追加します。Power Query（M）の考え方としては次のようになります（クエリの状況によりステップ名は変わります）。

// Title列の前後空白や不可視文字を整形する例（概念）
= Table.TransformColumns(
    #"Previous Step",
    {{"Title", each Text.Trim(Text.Clean(Text.Replace(_, Character.FromNumber(160), " "))), type text}}
  )

この形にしておくと、Excel側はシンプルな式で運用できます。

=FILTER(t_Values, ISNUMBER(SEARCH(TRIM(L2), t_Values[Title])), "該当なし")

「データ品質はPower Queryで担保」「ユーザー操作はExcelで検索」の役割分担にすると、ファイルの寿命が伸びます。

どちらでフィルターするべき？ Excel数式とPower Queryの使い分け

同じ“絞り込み”でも、向いている場面が違います。意思決定をしやすいように整理します。

観点Excel（FILTER/SEARCH）Power Query（クエリ側でフィルター）
反映の速さセル入力と同時に即時反映（強い）更新（Refresh）が必要
データ整形式が複雑化しやすい（TRIM/CLEAN/SUBSTITUTE等）整形ステップとして明示でき、再現性が高い
大量データ再計算コストが増えやすい（PC性能に依存）比較的安定（ただし更新時間はかかる）
共有運用式の理解が必要（編集で壊れやすい）手順が固定化しやすい（壊れにくい）

検索窓（L2）に入力してすぐ結果を見たいならExcel数式が便利です。一方で、毎月更新されるCSVのように「元データが汚い・揺れる」ならPower Queryで整形し、Excel側の式を簡単に保つ方がトラブルが減ります。

実用レシピ集：よくある“もう一歩”をまとめて解決

複数列を返したい（Number/Titleを同時に）

arrayをテーブル全体（t_Values）にしておけばOKです。

=FILTER(t_Values, ISNUMBER(SEARCH(TRIM(L2), t_Values[Title])), "該当なし")

一致したTitleを重複なしで一覧にしたい

=UNIQUE(FILTER(t_Values[Title], ISNUMBER(SEARCH(TRIM(L2), t_Values[Title])), "該当なし"))

条件を2つ以上にしたい（AND条件）

ANDは掛け算（*）で表現すると分かりやすいです（TRUE=1、FALSE=0）。

=FILTER(
  t_Values,
  (ISNUMBER(SEARCH(TRIM(L2), t_Values[Title]))) * (t_Values[Number]>=N2),
  "該当なし"
)

N2は数値条件を入れるセルとして例示しています。

どれかに当てはまればOK（OR条件）

ORは足し算（+）で表現し、0より大きいかで判定します。

=FILTER(
  t_Values,
  ((ISNUMBER(SEARCH("東京", t_Values[Title]))) + (ISNUMBER(SEARCH("大阪", t_Values[Title]))))>0,
  "該当なし"
)

検索語が短すぎるとノイズが多いので、2文字以上のときだけ動かしたい

=LET(
  k, TRIM(L2),
  IF(LEN(k)<2,"",FILTER(t_Values,ISNUMBER(SEARCH(k,t_Values[Title])),"該当なし"))
)

最後に：今回の要点（#CALC!を“仕様どおり”に扱えるようにする）

Power Queryで作成したテーブルでも、ExcelのFILTER関数は問題なく使えます。#CALC!が出るのは互換性よりも、条件が0件、または見えない文字の混入によって一致していないことが主因です。

  • まずは if_empty を入れて「0件なのか」を確定させる
  • 部分一致は SEARCH/FIND（またはCOUNTIFのワイルドカード）で判定配列を作る
  • 一致しないときは TRIM/CLEAN/SUBSTITUTE(CHAR(160)) を疑う
  • 運用を安定させるなら、Power Query側で整形してExcel側の式を簡単に保つ

この流れで切り分ければ、「なぜ#CALC!なのか」が説明でき、同じトラブルを再発させない設計にもつなげられます。

Excel
Excel 部分一致 filter Power Query #CALC! SEARCH

この記事を書いた人

佐藤健司のアバター 佐藤健司

実務の現場で詰まりがちなポイントを地図にするITブログ「IT trip」を運営。Windows／Office（Teams・Excel）からSQL、サーバ運用、ガジェットまで、再現性のある手順と“なぜそうなるか”を丁寧に解説します。読んだらすぐ試せること、そして迷った人の次の一歩が見えることを大切にしています。

関連記事

コメント

コメントする

目次