【Excel】エクセルで部分一致で抽出やカウント・○表示（複数条件・検索・関数など）

エクセルでデータを扱っていると、「特定の文字を含むデータを抽出したい」「部分一致する項目の数を数えたい」「キーワードが含まれていたら○印を表示したい」といった場面に遭遇することはありませんか?

この記事では【Excel】エクセルで部分一致で抽出やカウント・○表示（複数条件も、検索・関数など）方法について解説していきます。

ポイントは

・IF関数とCOUNTIF関数で部分一致を判定して○表示
・COUNTIF関数とワイルドカードで部分一致をカウント
・FILTER関数で部分一致するデータを自動抽出

です。
それでは詳しく見ていきましょう。

エクセルで部分一致で○表示する方法1【IF関数とCOUNTIF関数で判定】

エクセルで特定の文字が含まれているかを判定し、含まれていれば○印を表示する最もシンプルな方法が、IF関数とCOUNTIF関数を組み合わせることです。

COUNTIF関数でワイルドカード「*」を使って部分一致を検索し、IF関数で一致した場合に○印や指定した文字を表示できます。

例えば、以下のようなデータがあるとします。

例として商品名一覧A3:A8に「アボカド」という文字が含まれていたら確認用と記載されている場所の直下（C3）に○を表示し、含まれていなければ空白を表示してみます。

解決方法

具体的な手順を見ていきましょう。

まず、判定結果を表示したいセル(例としてC3)に以下の数式を入力します。

=IF(COUNTIF(A3:A8,”*アボカド*”),”〇”,””)

上記を数式バーに直接入力してもよいですが、難しいかたは関数の意味を考えながら以下の手順の通りに進めてみましょう。

IF関数の設定画面が表示されます。「アボカドという文字が含まれる場合」といった意味が【論理式】部分に、「含まれていた場合は〇を表示する」といった意味が【真の場合】に、「含まれていなければ空白を表示する」が【偽の場合】に入ります。

順番は前後しますが、今回は真の場合と偽の場合の設定が難しくありませんので、先に入力してしまいましょう。

続いて、【論理式】の中にカーソルを入れ、上部の名前ボックスの▼をクリックします。

関数の一覧が表示されます。一覧にCOUNTIFがある場合はそのまま選択、見当たらない場合は【その他の関数】をクリックしましょう。

COUNTIFを選択したことにより、IF関数の設定画面からCOUNTIFの設定画面に切り替わります。

アボカドという言葉を検索するのは、メニュー名が書かれている範囲内のため
A3:A8をドラッグ等で選択します（直接入力でも可）

範囲選択したことにより、【範囲】部分にA3:A8と入力されています。
つづいて【検索条件】内をクリックします。

検索条件内に”*アボカド*”と入力します。

この数式は、A2セルに「アボカド」という文字が含まれていれば○を表示し、含まれていなければ空白を表示します。

検索条件とした「*アボカド*」という部分がポイントで、「*」はワイルドカードと呼ばれ任意の文字列を表しています。メニュー内に存在するのは【アボカドサラダ】のみで、【アボカド】単独のものはありません。

しかし*をつけることにより、他の文字が入っていても同じ単語であると認識し、検索にヒットされるようになります。

尚、検索条件以外の文字列が前後どの位置にあるかで「*」の指定も変える必要があります。今回実際にメニュー内にあるのは【アボカド“サラダ”】となっているので、余分な文字は後ろについていますね。

図は【アボカド】の前後に他の文字がある場合でも検索にヒットする設定としていますが、今回は”アボカド*”としても結果は等しくなります。

数式バーを確認すると、IF関数とCOUNT関数が組み合わさり、前述した数式と同様であることが確認できます。

対象物がない場合、空白ではなく×印を表示したいようであれば、式内末尾で空白を示している””内に×を入力し、以下の形にしましょう。

=IF(COUNTIF(A3:A8,”*アボカド*”),”〇”,”×”)

数式を書き換えたあとに、メニュー内からアボカドとつく2品をBackSpace等で削除すると…

【操作のポイント:COUNTIF関数でワイルドカード「*」を使って部分一致を検索し、IF関数で条件に応じて○印や任意の文字を表示できます】

複数のキーワードで部分一致判定する方法

複数のキーワードのいずれかが含まれていれば○を表示したい場合は、OR関数を組み合わせます。今回はA3:A8の各メニュー名に「サーモン」または「チアシード」のいずれかひとつでも含まれていれば〇、どちらとも含まれていなければ×と表示させる例となります。

表内に入った数式等は削除し、こちらのデータを使用します。

=IF(OR(COUNTIF(A3,”*サーモン*”),COUNTIF(A3,”*チアシード*”)),”〇”,”×”)

この数式はメニュー名が書かれている列の先頭セルA3を対象に「サーモン」または「チアシード」のどちらかが一方が含まれていれば○を表示させます。
いずれも含まれていない場合は×となります。
（*は他の文字列を表しています）

数式入力が難しい場合は、以下の手順の通りに進めてみましょう。

結果を表示させたいセル（今回はB3）をクリックします。

【fx】（関数の挿入）をクリックし、分類を“論理”としてIF関数を挿入します。

先に真の場合（サーモンまたはチアシードのどちらかが含まれているメニュー名）と偽の場合（どちらも含まれていないメニュー名の場合は×）に関する結果の設定を行います。
真の場合の結果は〇、偽の場合の結果は×としましょう。

つづいて【論理式】内にカーソルを入れ、名前ボックスの▼を押します。
先ほどとは異なり「サーモン【または】チアシード」とするため関数の一覧からORを挿入しましょう。見当たらない場合は【その他の関数】を選択します。

こちらがOR関数の設定画面です。分類は【論理】となります。

OR関数を挿入することにより、「サーモン」と「チアシード」
ふたつの数式を立てることができるようになります。
それぞれにCOUNTIFの設定を行います。

論理式1は「メニュー名にサーモンが含まれる場合」です。
再度名前ボックスの▼を押してからCOUNTIFを選択します。

COUNTIFの設定ですが検索を行う範囲をメニュー名が入る列の先頭である【A3】、キーワードを”*サーモン*”として、下図の値を入力します。

つづいて数式バーに入力されている“OR”部分をクリックします。

そうすると、ふたつめの数式を立てられますので、サーモンと同様にCOUNTIFを挿入し、以下の値を入力します。

このふたつの設定により、「A3にサーモンもしくはチアシードを含む場合」といった設定となります。【OK】をクリックしましょう。
関数バーにはこのように表示されています。

ワイルドカードも使用したためA3に「サーモン」や「チアシード」を含む文字が入っていた場合、その前後に他の文字が付属していても検索結果にヒットします。
最後に【OK】をクリックします。ひとつめの結果を表示したあとは下方向に数式をコピーしましょう。
各メニュー名に「サーモン」か「チアシード」が含まれているか否かがわかります。

↓数式をコピーし、〇×を表示させた例

※上図、D3内の【NG品数】はCOUNTIFでB3:B8内の範囲にある〇の数を数えています※

また、複数のキーワードがすべて含まれている場合に○を表示したい場合は、AND関数を使用します。

=IF(AND(COUNTIF(A3:A8,”*サーモン*”),COUNTIF(A3:A8,”*チアシード*”)),”〇”,”×”)

と入力します。
この数式で「*サーモン*」かつ「*チアシード*」の両方が含まれている場合に○を表示するわけです。入力が難しい場合は、次の手順通りに操作してください。

★手順★
〇/×を表示させる列の先頭セルをクリック。ここに数式を入力していきます。

上部の【fx】（関数の挿入）を選択し、分類を“論理”としてIF関数を挿入します。

IF関数の設定画面が表示されます。先に真の場合、偽の場合の設定を行いましょう。
サーモンとチアシードが“含まれていたら〇、含まれていなければ×”といった意味を【真の場合】と【偽の場合】に設定します。

このようになりました。””は自動入力されますので、打ち込むのは〇と×のみです。

つづいて【論理式】部分の入力に移ります。
名前ボックスの▼をクリックし、“AND”を選択します。

ANDを選択します。見当たらないようであれば、【その他の関数】⇒分類を“論理”として探してください。

AND関数の設定画面が表示されます。論理式1の中をクリックします。

論理式1にはCOUNTIF関数が入ります。先ほどと同様名前ボックスの▼ボタンから選択しましょう。

見当たらない場合は、【その他の関数】から下図の設定で探します。

つづいてCOUNTIFの設定画面が表示されますので、範囲部分にカーソルを入れます。

条件部分には、まずひとつめとして【”*サーモン*”】と入力します。
※今回も前後に他の文字が入っていてもヒットするように、【*】をつけています※

つづいて、数式バーに入力されている現在の数式の中の【AND】部分のみをクリックします。

AND関数を挿入したことにより、数式を追加することができるようになります。

先ほど立てた式は【論理式1】に記載されていますので、次は【論理式2】の中にカーソルを入れてから同様にCOUNTIFの式を作成してください。

このとき検索の条件を”*チアシード*とします。

A3に【サーモン】とあっても×になるのはAND検索を使用しているからです。
例え下に数式をコピーしても【サーモン】と【チアシード】の両方がセル内に入っていなければ〇とはなりません。

ひとつセルを追加し、メニュー名を【サーモンチアシード】とします。

セルを追加し、【サーモンチアシード】としたあとは、
〇や×が表示されるセル内の数式を数式を上からコピーし直してください。
【サーモンチアシード】のメニューのみ〇表記となります。

エクセルで部分一致をカウントする方法2【COUNTIF関数とワイルドカードで集計】

特定の文字を含むデータの個数を数えたい場合もあるでしょう。

そんな時に役立つのが、COUNTIF関数とワイルドカードを使った部分一致のカウントです。

COUNTIF関数を使えば、指定した文字列を含むセルの個数を簡単に集計できます。

例えば、以下のようなデータで特定の文字を含む商品数を知りたい場合を考えてみましょう。今回は例としてC3にメニュー内に“サラダ”とつく品が何品あるのかを求めていきます。

解決方法

集計結果を表示したいセルC3をクリックして、ホームタブを選択します。

C3セルに以下の数式を入力します。

=COUNTIF(A3:A10,”*サラダ*”)

数式の入力が難しい場合は、以下の順番通りに操作を行いましょう。

分類を【統計】として、COUNTIF関数を挿入します。

この数式は、A3からA10の範囲で「サラダ」という文字を含むセルの個数をカウントするものです。

COUNTIF関数の構文は「=COUNTIF(範囲,検索条件)」となっており、検索条件に「*サラダ*」と指定することにより部分一致で検索されるわけです。

結果として「2」が表示され、「アボカドサラダ」「サーモンサラダ」の2つがカウントされます。

複数のキーワードで一致する条件の数を調べることが予測される場合は、あらかじめどこかのセル内に検索ワードを入力しておき、COUNTIFの検索条件をセル参照としておくと、都度数式を書き換える必要がなくなります。

キーワードで検索したい場合は、セル参照を使用します。

C2に検索したいキーワード(例として「アボカド」)を入力し、E2セルに以下の数式を入力します。

=COUNTIF(A3:A10,”*”&C2&”*”)

「&」で文字列を結合することで、C2セルの値の前後にワイルドカードを付けて検索できるでしょう。

&は【アンパサンド】と呼ばれ、別々のセル内に記入されている文字列同士を結合する役目をしてくれます。今回はC2に入る予定のキーワードと【*】（ワイルドカード）をつなげてくれています。

前項で数式を使用し「アボカド」を含むメニューを探す際には、前後に*をつけていましたが、セル参照の前後に直接【*】の使用はできません。【*】を使用する場合は、セル番地と【*】の間に【&】で繋ぐ必要があります。

それでは次の手順を使い、キーワード入力欄に特定の言葉（サラダやサーモン）を入れると、メニュー内に該当の品数があるのかを表示させる数式を立ててみましょう。
※関数の意味を考えながら行いたいため、今回の例は直接入力ではありません※

より実践的な使い方として、このような表を用意しました。
キーワード入力欄であるC2にアボカドなどといれると、C3の空欄に検索条件にあてはまったメニューの個数が表示されるようにします。

数式は、ヒット数が表示されるC3に立てますので、はじめにC3をクリックしておきます。
使用する関数は、「COUNTIF」となります。

範囲には、メニュー名が書かれているA3:A10を選択し、指定します。

ここではじめての操作ですが、検索条件欄は、キーワードが入力される予定の【C2】とします。

前後に他の文字が含まれているものも検索結果にヒットさせたいため、C2の前後に
”*”を入力⇒”*”C2”*”とします。さらにC2と”*”の間に&を入れ、最終的な条件を”*”&C2&”*”としてください。

これで設定が完了しました。最初はキーワードの欄が【キーワード入力欄】となっているため、メニュー名内のヒット件数は0です。

キーワード入力欄内の文字を【アボカド】に変えると…

このようにあらかじめキーワードの入力欄を検索条件としておくことにより、
条件を変えて他のものを探したい場合でも、数式そのものの書き換え操作が不要になります。
“操作のポイント:COUNTIF関数の検索条件でワイルドカード「*」を使用することで、部分一致するセルの個数を簡単にカウントできます】

複数条件で部分一致をカウントする方法

複数のキーワードのいずれかを含むセルをカウントしたい場合は、複数のCOUNTIF関数を四則演算子の「+」を使って合計します。C4に以下の数式を入力しましょう。

=COUNTIF(A3:A10,”*アボカド*”)+COUNTIF(A3:A10,”*サーモン*”)

結果はこのようになります。

この数式は「*アボカド*」を含むセルと「*サーモン*」を含むセルの合計数を返したものです。

手順としては、C4にCOUNTIFを使用してアボカドの数式をたてたあと、数式バーに「+」をつけたしてからアボカド同様にサーモンの数式を立てます。
数式の直接入力が難しい場合は、以下の順番通りに進めてみましょう。

計算結果を出したいセル（今回はC3）を選択し、関数の挿入（fx）をクリックしましょう。

分類を【統計】とし、COUNTIFを選択します。

範囲はA3:A10を範囲選択すると、自動で入力されます。
検索条件はひとつめのアボカドを含む商品名が検索されるように設定します。
このとき「”*アボカド*””」といったようにワイルドカードを使用するのを忘れないようにしましょう。

アボカドを含む商品数である【2】が表示されます。

現在の数式は…

となっています。つづけて2個目である「サーモン」の設定をするため、現在の数式の末尾に【+】を入力しましょう。

+のあとはアボカドの設定時と同様にCOUNTIF の関数を入れ、
検索条件を「”*サーモン*”」とします。
下図が設定時の画面になります。

【OK】をクリックしたあと上部の数式を見てみましょう。
=COUNTIF(A3:A10,”*アボカド*”)+COUNTIF(A3:A10,”*サーモン*”)
となっていますね。
つまり、2つのCOUNTIFの結果を足した数がC3に表示される設定となっています。

全ての設定を終えると下図のように、メニュー名からアボカドとサーモンを含んだメニューの合計数が表示されます。今回は計4個となります。

注意点として、同一セル内にキーワードを複数書き込んだとしても重複されます。
例え2つのキーワードを書き込んだとしても、答えは【1】となってしまうのです。
※【サーモンサラダ】と【アボカド　サーモン】を付け足しています※

これまで使用していた「”*アボカド*”」といったようなワイルドカードの設定では【アボカドサラダ】といったように条件とした文字の後ろに他の文字が付属するものも、【特盛アボカド】といったように、条件とした文字の前に他の文字が付属するもの、どちらもカウントすることができました。

一方で、条件とする文字で始まるセルのみをカウントしたい場合は、前方のワイルドカードを省略します。

=COUNTIF(A2:A10,”アボカド*”)

この数式は「アボカド」で始まるセルだけをカウントします。
数式を変更したものが下図になります。

反対に、条件とする文字で終わるセルのみをカウントする場合は、後方のワイルドカードを省略します。

=COUNTIF(A2:A10,”*サラダ”)

この数式は「サラダ」で終わるセルだけをカウントするわけです。
※今回は【アボカド】が末尾につくメニューはないため、“0”と表示されています。

エクセルでFILTER関数を使って部分一致で抽出する方法3【動的な検索抽出】

Excel 365やExcel 2021を使っている場合は、FILTER関数を使った部分一致抽出が非常に便利です。

FILTER関数と検索関数を組み合わせることで、特定の文字を含むデータを自動的に抽出して別の場所に表示でき、元データの更新にも自動対応します。

例えば、以下のようなデータがあるとします。

表内にあるメニューの中から、特定の文字が含まれている品のみを抜き出し、検索結果として別の箇所に部分にまとめていきます。検索結果を表示させる場所はE3からとしています。

解決方法

「ホーム」タブをクリックして、抽出結果を表示したい場所(例としてE3セル)に移動します。

E3セルに以下の数式を入力します。

=FILTER(A3:C8,ISNUMBER(SEARCH(“アボカド”,A3:A8)),”なし”)

この数式は、A3:A8のメニュー名の範囲を元に「アボカド」という文字を検索し、当てはまるものがあればそのメニューの価格、カテゴリの項目を含んだ行部分をA3:C8までの範囲内から抽出します。
もしキーワードに該当するメニュー名がなければ【なし】と表示させるようにもなっています。

このときのSEARCH関数は大文字小文字を区別せずに検索し、見つかれば位置を、見つからなければエラーを返すため、ISNUMBER関数でエラーかどうかを判定しているといったわけです。

数式を入力後は、Enterキーを押しましょう。
すると“アボカド”というキーワードが含まれる「アボカドサラダ」と「アボカドトースト」の行全体が自動的に抽出されることが確認できます。

元データであるメニュー表のデータ（A3:C8）が書き替えられた場合は、数式を挿入している検索結果部分（E3）の内容も自動的に更新されるため、この数式を使用することにより常に最新のデータを確認できます。
さらに便利な使用例として検索条件を文字列（今回は”アボカド”）とするのではなく、セル参照を用いる方法もあります。

例として抽出条件とするキーワードの入力欄を付け足した、こちらのデータを使用します。
（さきほど抽出した結果は削除した状態です）

改めて、検索結果を表示させるE3を選択し、次の数式を入力します。

=FILTER(A3:C8,ISNUMBER(SEARCH(I2,A3:A8)),”なし”)

この数式は、抽出条件であるキーワード部分ががセル参照になっています。
つまり、I2に入力する値が変わると結果も変化します。

I2のセルに「アボカド」と入力した場合

I2のセルに「サラダ」と入力した場合

このように検索条件をセル参照とした場合は、指定したセル内（今回はI2）のキーワードを変更すれば、抽出結果も自動的に変わります。

【操作のポイント:FILTER関数とSEARCH関数、ISNUMBER関数を組み合わせることで、部分一致するデータを自動的に抽出できます】

複数条件で部分一致抽出する方法

複数のキーワードのいずれかを含む行を抽出したい場合は、OR条件を使用します。

再度こちらの状態のデータを使用します。検索結果を表示させる側の表内は【クリア】し、セル内に何も入っていない状態とします。結果を表示させるセル、E3に以下の数式を挿入します。

=FILTER(A3:C8,ISNUMBER(SEARCH(“アボカド”,A3:A8))+(ISNUMBER(SEARCH(“サーモン”,A3:A8))),”なし”)

指定したように「アボカド」または「サーモン」のどちらかをメニュー名に含む行が抽出されます。

「+」は論理和(OR)を表すため、1つでも条件が真であれば抽出されるわけです。

また、複数のキーワードがすべて含まれている行を抽出したい場合は、AND条件を使用します。

※検索結果に入っている数式は全てクリアします※

その上で、E3に次の数式を入力します。

=FILTER(A3:C8,(ISNUMBER(SEARCH(“アボカド”,A3:A8)))*(ISNUMBER(SEARCH(“サーモン”,A3:A8))),”該当なし”)

この数式は「商品名にアボカドを含む」かつ「商品名にサーモンを含む」という2つの条件を両方満たす行を抽出します。現在のメニュー内にアボカドとサーモンの両方が入るメニュー名はありませんので、【該当なし】と表示されます。

メニュー内に、「アボカドサーモン」といった商品を付け足し、数式のA3:C8部分をA3:A9に、またメニュー名の範囲A3:A8をA3:A9に修正すると下図のようになります。

このように「*」は論理積(AND)を表すため、両方の条件が真の場合に抽出されます。

大文字小文字を区別して検索したい場合は、SEARCH関数の代わりにFIND関数を使用します。
※検索結果に入っている数式は全てクリアし、【アボカドサラダ】を【potato salad】に、【サーモン寿司】を【POTATO SARAD】に書き換えます。カテゴリは両方とも【サラダ】としておきましょう。続いて、E3をクリックし、下記の数式を入力します。