【Excel】特定の文字を含むデータを瞬時に抽出

※IT予備メンバーに加入して連携すると、
一部の広告が非表示になります。

以下は、問い合わせ管理表のサンプルになります。

この表から、指定した「会員No」の文字列が含まれるデータを数式で抽出する仕組みを作っていきます。

📘 Excel小技１００選（５００円）

指定した文字を含んでいるかどうかを判定する

まずは、抽出用のシートを用意します。
こちらでは、「検索」というシートを別で用意しています。

このシートのセルB2に入力した文字が、元の表の「会員No」に含まれているかどうかを判定していきます。

特定の文字が含まれているかどうかは、SEARCH関数で判定できます。
まずは、以下の数式を追加した「検索」シートのセルA2に入力します。

=SEARCH(B1,問い合わせ一覧!B2:B1000)
// B1：検証する文字が入力されたセル
// 問い合わせ一覧!B2:B1000：元の表の「会員No」の項目全体（大きめに指定）

入力して確定すると、各行に数値が表示されます。
こちらの場合は、A列は日付の書式設定が設定されているため「1900/1/1」と表示されています。

次に、検索欄であるセルB1に適当な文字を入力します。
こちらでは「m9」と入力しています。

そうすると、エラーの箇所と数値が表示された箇所が現れました。
この各行の位置は元の表の位置と一致します。

エラーになった行は、元の表の「会員No」に検索値が含まれていない行です。
数値が表示された行は、元の表の「会員No」の検索値が含まれている行で、検索値が含まれている位置が表示されます。
SEARCH関数は大文字と小文字を同じ文字として判定します。

こちらでは、A列に日付の表示形式が設定されているので「1900/1/1」と表示されてしまっていますが、実際は検索値の「m9」が含まれている位置である１番目の「1」が表示されます。

次に、検索値が含まれている行のセルに「TRUE」、含まれていない行のセルに「FALSE」と表示していきます。
このように判定するには、数値かどうかを判定すればよいので、ISNUMBER関数で囲った以下のような数式にします。

=ISNUMBER(SEARCH(B1,問い合わせ一覧!B2:B1000))

入力して確定することで、以下のようにTRUE/FALSEで検索値を含んでいるかどうかを判定することができます。

TRUEの行のデータのみを抽出

次は、先ほど数式で求めた結果のTRUEの行のデータのみを抽出していきます。
そのような時は、FILTER関数を使います。

先ほどの数式に、以下のようにFILTER関数を組み合わせます。

=FILTER(問い合わせ一覧!A2:C1000,ISNUMBER(SEARCH(B1,問い合わせ一覧!B2:B1000)),"")
// 問い合わせ一覧!A2:C1000：抽出元の表全体（SEARCH関数に指定した範囲と同じ行数）
// ISNUMBER(…)：抽出条件
// ""：抽出結果が見つからなかったときに表示する値