FILTER関数で「特定の文字を含む・含まない」データを抽出する方法

ExcelのFILTER関数で指定の文字を含むデータを抽出する方法をお探しですね。

Excelで「含む・含まない」を自動抽出!FILTER関数の使い方を初心者向けに解説

Excelで一覧表から「商品名に東京が入っている行だけ」「担当者名に川が入っていない行だけ」を取り出したいとき、普通のフィルター機能でもできますが、条件を変えるたびに操作が必要で少し面倒ですよね。

FILTER関数を使えば、元のデータが更新されても抽出結果が自動で反映されるので、とても便利です。

ただし、FILTER関数では「特定の文字を含む・含まない」を指定するときに、ちょっとしたコツが必要です。

そこで今回は、SEARCH関数やFIND関数と組み合わせた方法を、基本から順番に分かりやすく解説していきます。

FILTER関数で「特定の文字を含む」データを抽出する基本

FILTER関数は、指定した条件に合う行だけを取り出してくれる便利な関数です。

基本の書き方はこんな感じです。

**=FILTER(配列, 条件, [該当なしの場合])**

– **配列**：抽出したい表全体の範囲
– **条件**：どんな行を取り出すかの条件
– **該当なしの場合**：条件に合うデータがなかったときに表示する文字

たとえば、A2:D100に商品一覧があって、B列の商品名に「東京」が含まれる行だけを抽出したいときは、こう書きます。

“`
=FILTER(A2:D100, ISNUMBER(SEARCH(“東京”, B2:B100)), “該当なし”)
“`

この数式の仕組みを順番に見ていきましょう。

1. **SEARCH関数**がB列の各セルから「東京」という文字を探します
2. 見つかったら文字の位置を数値で返し、見つからないとエラーになります
3. **ISNUMBER関数**で、数値が返ってきたセルをTRUE、エラーのセルをFALSEに変換します
4. FILTER関数がTRUEになった行だけを抽出してくれます

つまり、「東京を含む商品名」の行だけが一覧として表示されるというわけです。

SEARCH関数とFIND関数の違い

SEARCH関数の代わりに**FIND関数**を使うこともできます。

“`
=FILTER(A2:D100, ISNUMBER(FIND(“東京”, B2:B100)), “該当なし”)
“`

この2つの違いは、**大文字・小文字を区別するかどうか**です。

– **SEARCH関数**：大文字・小文字を区別しない（「Tokyo」も「tokyo」も同じ扱い）
– **FIND関数**：大文字・小文字を厳密に区別する

日本語だけを扱う場合はあまり違いを感じませんが、英字の商品コードなどを検索するときは注意が必要です。

特に理由がなければSEARCH、厳密に判定したいときはFINDと覚えておくといいでしょう。

検索文字をセル参照にすると便利

条件に使う検索文字をセルから参照すると、抽出条件を簡単に変更できます。

たとえば、F1セルに検索したい文字を入力して、数式をこう書きます。

“`
=FILTER(A2:D100, ISNUMBER(SEARCH(F1, B2:B100)), “該当なし”)
“`

こうすれば、F1の文字を変えるだけで抽出結果がパッと切り替わります。

売上表や顧客リストで検索語を何度も変える場合は、数式内に直接文字を入れるより、条件セルを用意したほうが断然便利です。

FILTER関数で「特定の文字を含まない」データを抽出する方法

「特定の文字を含まない」データを抽出したいときは、「含む」の判定を反対にします。

一番分かりやすいのは、**NOT関数**を使う方法です。

たとえば、B列の商品名に「東京」を含まない行だけを抽出するなら、こう書きます。

“`
=FILTER(A2:D100, NOT(ISNUMBER(SEARCH(“東京”, B2:B100))), “該当なし”)
“`

仕組みはこうです。

1. SEARCH関数で「東京」を探す
2. ISNUMBER関数で「含む」行をTRUEにする
3. **NOT関数**でTRUEとFALSEを反転させる
4. 結果として「東京を含まない行」がTRUEになり、その行だけが抽出される

ISERROR関数を使う方法もある

別の書き方として、**ISERROR関数**を使う方法もあります。

“`
=FILTER(A2:D100, ISERROR(SEARCH(“東京”, B2:B100)), “該当なし”)
“`

SEARCH関数は指定した文字が見つからないとエラーを返すので、そのエラーを利用して「含まない」を判定するという考え方です。

どちらの書き方でも結果は同じですが、初心者には「含む条件をNOTで反転する」方法のほうが理解しやすいかもしれません。

注意点：範囲の行数をそろえよう

FILTER関数を使うときに気をつけたいのが、**条件範囲の行数を抽出元の表とそろえること**です。

たとえば配列がA2:D100なら、条件範囲もB2:B100のように2行目から100行目までにします。

ここがずれると、配列のサイズが一致せずエラーになってしまいます。

また、条件に一致するデータが1件もない場合、最後の引数を省略していると「#CALC!」というエラーが表示されます。

実務で使う表では、「”該当なし”」や「””」を指定しておくと見た目も分かりやすくなりますよ。

「〜で始まる」「〜で終わる」や複数列の条件を指定する

特定の文字を「含む」だけでなく、「その文字で始まる」「その文字で終わる」という条件で抽出したい場合もありますよね。

「〜で始まる」を抽出する（前方一致）

B列のコードが「A」で始まる行を抽出するなら、**LEFT関数**を使います。

“`
=FILTER(A2:D100, LEFT(B2:B100, 1)=”A”, “該当なし”)
“`

LEFT関数は文字列の左側から指定した文字数を取り出す関数です。

2文字で判定したい場合は「LEFT(B2:B100, 2)=”AB”」のようにします。

「〜で終わる」を抽出する（後方一致）

文字列が特定の文字で終わる行を抽出したい場合は**RIGHT関数**を使います。

たとえば、B列の値が「済」で終わる行を抽出するなら、こう書きます。

“`
=FILTER(A2:D100, RIGHT(B2:B100, 1)=”済”, “該当なし”)
“`

「済で終わらない」行を抽出したい場合は、等号を不等号に変えます。

“`
=FILTER(A2:D100, RIGHT(B2:B100, 1)<>“済”, “該当なし”)
“`

前方一致や後方一致はSEARCH関数でも工夫できますが、LEFT関数やRIGHT関数を使うほうが読みやすく、あとから数式を見直すときにも意図が伝わりやすいです。

複数列のいずれかに含む（OR条件）

複数列のどれかに特定の文字を含む行を抽出したい場合は、**OR条件**を作ります。

FILTER関数では、OR条件を「+」で表すのが基本です。

たとえば、B列の商品名またはC列の備考のどちらかに「至急」を含む行を抽出するなら、こう書きます。

“`
=FILTER(A2:D100, ISNUMBER(SEARCH(“至急”, B2:B100))+ISNUMBER(SEARCH(“至急”, C2:C100)), “該当なし”)
“`

この数式では、B列で「至急」を含む判定と、C列で「至急」を含む判定を足し合わせています。

どちらか一方でもTRUEであれば、FILTER関数が抽出対象として扱ってくれます。

反対に、B列にもC列にも「至急」を含まない行を抽出したい場合は、最後に「=0」を付けます。

“`
=FILTER(A2:D100, (ISNUMBER(SEARCH(“至急”, B2:B100))+ISNUMBER(SEARCH(“至急”, C2:C100)))=0, “該当なし”)
“`

対象列が2〜3列程度なら、この方法が分かりやすくて実務でも使いやすいですよ。

実務で失敗しないための注意点と応用テクニック

ワイルドカードは使えない？

FILTER関数で文字列を条件にするとき、「ワイルドカードをそのまま使える」と思い込まないように注意しましょう。

COUNTIF関数では「”\*東京\*”」のように書けますが、FILTER関数の条件式に「B2:B100=”\*東京\*”」と書いても、「東京を含む」という意味にはなりません。

FILTER関数は、条件式がTRUEまたはFALSEの配列を返す必要があるからです。

そのため、部分一致にはSEARCH関数やFIND関数、前方一致にはLEFT関数、後方一致にはRIGHT関数を組み合わせるのが基本になります。

表記ゆれに注意

抽出条件を安定させるには、検索対象のデータの**表記ゆれ**にも注意が必要です。

たとえば「東京」「東京都」「東京」のように余分なスペースが混じっていると、意図した結果にならないことがあります。

– **前後の余分なスペースを除去**：TRIM関数
– **全角・半角の違いを整える**：ASC関数やJIS関数
– **文字を置き換える**：SUBSTITUTE関数

FILTER関数そのものの問題ではなく、元データの品質が抽出結果に影響する点は、実務で特に見落とされやすいポイントです。

REGEXTEST関数も使える（新しい環境のみ）

Microsoft 365の新しい環境では、**REGEXTEST関数**を使って正規表現で判定できる場合もあります。

“`
=FILTER(A2:D100, REGEXTEST(B2:B100, “東京”), “該当なし”)
“`

正規表現を使えば、「^東京」は東京で始まる、「東京$」は東京で終わる、というように柔軟な条件を指定できます。

ただし、REGEXTEST関数はすべてのExcel環境で使えるとは限らないため、共有ファイルや職場の標準環境で使う場合は注意が必要です。

互換性を重視するなら、SEARCH・FIND・LEFT・RIGHTを使った書き方を優先すると安心です。

よく使う形のまとめ

実務でよく使う形を整理すると、こんな感じです。

**含む**
“`
=FILTER(A2:D100, ISNUMBER(SEARCH(“文字”, B2:B100)), “該当なし”)
“`

**含まない**
“`
=FILTER(A2:D100, NOT(ISNUMBER(SEARCH(“文字”, B2:B100))), “該当なし”)
“`

**〜で始まる**
“`
=FILTER(A2:D100, LEFT(B2:B100, 1)=”文”, “該当なし”)
“`

**〜で終わる**
“`
=FILTER(A2:D100, RIGHT(B2:B100, 1)=”字”, “該当なし”)
“`

FILTER関数は、一度数式を作っておけば、元データが追加・修正されたときに抽出結果も自動で更新されます。

さらに、検索語を別セルに入力する形にすれば、毎回数式を書き換えずに条件変更ができます。

文字列の抽出は、売上管理、顧客リスト、問い合わせ一覧、在庫表など、幅広い業務で使えます。

「含む」「含まない」の基本式を押さえておくと、作業時間を大きく減らせますよ。

複数条件や並べ替えまで行いたい場合は、FILTER関数にSORT関数やUNIQUE関数を組み合わせると、より実用的な検索・集計表を作成できます。

ぜひ試してみてくださいね!