【Excel】キーワード検索で「霞が関」でも「霞ヶ関」でもヒットさせるには：Tech TIPS

Excelで大量のテキストデータに対し、複数のキーワードのどれかが該当するものを探したい場合はないだろうか。そのような場合、SEARCH関数やFIND関数ではうまく検索できない。そこで、簡単に複数キーワードで検索できる方法を紹介しよう。

[塩田紳二，著]

連載目次

対象：Office 2013／2016／2019／365

SEARCH関数ではキーワードが多いと長い式が必要になる
SEARCH関数ではセル内の文字列検索が可能だが、見つからない場合にエラーになることから、複数キーワードの検索が複雑になってしまう。

　大量のテキストデータから複数のキーワードの中のどれかを持っているデータを探したい、という作業を行う場合もあるのではないだろうか。例えば、住所録のようなものから、「霞が関」でも「霞ヶ関」でも該当するものを見つけたいというような場合だ。このような場合、データを探した後の集計処理などを考えると、「Microsoft Excel（エクセル）」で済ませてしまいたいと考えることもあるだろう。

　ExcelでSEARCH関数やFIND関数を使えば、文字列検索は可能だ。しかし、これらの関数は、1つの単語を検索することしかできず、多数のキーワード（検索対象文字列）に対して検索を行う場合、複雑で長い数式を作らねばならなくなる。

　そこで、COUNTIF関数とSUBSTITUTE関数を使って、複数のキーワードで検索する方法を紹介しよう。

検索方法と検索の数式

　文字列検索には、複数の方法がある。今回使うCOUNTIF関数とSUBSTITUTE関数では、ワイルドカードの可否や大文字／小文字の区別に違いがある。検索機能の点ではSEARCH関数とFIND関数の関係に似ている。

関数	ワイルドカード	半角英数の大文字／小文字	全角文字／半角文字
COUNTIF	利用可	常に同一視	常に区別
SUBSTITUTE	不可	常に区別	常に区別
COUNTIF関数とSUBSTITUTE関数の違い

　なお、どちらも全角／半角の区別を行う。全角片仮名と半角片仮名を同一視して検索を行わせることも可能で、数式側ではなくキーワード定義で対応する。こちらについては後で説明する。

COUNTIF関数とSUBSTITUTE関数の使い分け方

　COUNTIF関数は、ワイルドカードを指定できるため、文字列検索で前方一致、後方一致、途中一致のどれでも検索が可能だ。前方一致は、特定のキーワードから始まる文字列の検索、後方一致は、特定のキーワードで終わる文字列の検索だ。

　これに対して、SUBSTITUTE関数を使う場合には、途中一致でしか検索ができない。これでは、キーワードが対象文字列のどこにあっても検索してしまう。しかし、一般的には、途中一致による検索でも十分であることが多い。

　また、COUNTIF関数は、ワイルドカードが使える半面、大文字／小文字を区別しない検索しか行えない。場合によっては、大文字／小文字を区別して「Search」だけを探し「SEARCH」や「search」を除外したいという用途もあるだろう。このような検索が行いたい場合にはSUBSTITUTE関数を使うことになる。

COUNTIF関数の使い方

　COUNTIF関数を使う場合には、下図のような構造を使う。検索したいキーワードをセル範囲（図では、A列を「キーワード」の名称でセル範囲指定している）で指定しておき、COUNTIF関数を使う数式を「CSE配列数式」として入力する（意味や方法は後述）。数式の最後に比較演算があるため、True（真）かFalse（偽）で計算結果が戻る。

COUNTIF関数の使い方
COUNTIF関数を使えば、キーワードをセル範囲で定義（ここではA列を「キーワード」の名称でセル範囲指定している）し、簡単な数式で複数キーワードの文字列検索が可能になる。

SUBSTITUTE関数の使い方

　SUBSTITUTE関数でも似たような構造を使う。こちらもCSE配列数式とすることで、キーワードセル範囲と検索対象セルにSUBSTITUTE関数が適用される。置き換えが行われると元の文字列とは一致しなくなるため、比較演算子「<>」の結果がOR関数で集計されて戻される。

SUBSTITUTE関数の使い方
SUBSTITUTE関数は、ワイルドカードは利用できないが、大文字／小文字を区別した検索が可能になる。COUNTIF関数とキーワードの性質によって使い分ける。

キーワードをセル範囲と定義する理由

　キーワードをセル範囲として定義し、数式内に入れないのは、追加や削除を簡単にするためだ。こうした分析では、はじめからキーワードの全てが判明していることは少ない。作業している間に、キーワードを追加していくことになる。しかし、そのたびに数式を書き換えていたのでは効率が悪い。

　そこでキーワードは、セル範囲で指定し、これを数式から参照している。なお、キーワードのセル範囲が大きくなることを考え、セル範囲には、「名前」を付けておく。こうすることで、セル範囲が拡大しても、数式を変更する必要がなくなる。

COUNTIF関数による複数キーワードの検索

　ここでは、サンプルとしてテレビの番組名から映画番組のようなものを探す場合を考える。映画番組が持つと思われるキーワードを指定し、番組名からキーワード検索を行い、映画番組らしい番組を探すことを考えてみる。

COUNTIF関数による複数キーワードの検索の例
説明のため、テレビ番組名の並びから映画番組のような番組名を判定する数式を考える。

　まずは、検索キーワードを用意する。COUNTIF関数の場合、「*（任意文字数のワイルドカード）」「?（1文字のワイルドカード）」をキーワードに含めてセル領域に記述しておく。この時点では、縦1列に並べることにする。これは後で隣の列に全角／半角に変換したキーワードを置くためだ。キーワードより少し長めの範囲を選択し、ここに「名前」を付ける。この例では「キーワード」とするが、好きに名前を付けてよい。

セル範囲に名前を付ける（1）
まずキーワードをセル範囲に入れておき、これに名前を付ける。名前の範囲を少し大きめにしておくことで、キーワードの追加が楽になる。

▼

セル範囲に名前を付ける（2）
［数式］タブの［名前の定義］を選択する。

▼

セル範囲に名前を付ける（3）
［新しい名前］ダイアログでセル範囲の名前を付ける。「範囲」は、処理対象のデータと同じシートならば、そのシート名を選択する。別シートならば「ブック」を選ぶ。

　セル範囲に名前を付ける方法については、Tech TIPS「Excelの『名前の定義』でセルの範囲に名前を付ける」を参考してほしい。

　検索キーワード範囲に名前を付けたら、数式を入力する。ここでは、検索対象データが1つの列に縦に並んでいるとする。その隣にセルにCOUNTIF関数を使った数式を入れ、最後に［Ctrl］＋［Shift］＋［Enter］キーで入力を終える。こうすることで、入力した数式は「CSE配列数式」になる。数式を見ると全体が「{ }（波括弧）」で囲まれていることが分かる。これがCSE配列である。CSEとは「Ctrl Shift Enter」の略で、数式入力時のキー操作から来た名称だ（コラム参照）。

=SUM(COUNTIF($C2,キーワード))>0

COUNTIF関数を使った場合

COUNTIF関数による複数キーワードの検索手順（1）
数式を入力する。

▼

COUNTIF関数による複数キーワードの検索手順（2）
数式を入れた後、［Ctrl］＋［Shift］＋［Enter］キーで確定させる。

▼

COUNTIF関数による複数キーワードの検索手順（3）
先頭セルの数式を入れたら、後はオートフィルでコピーする。

　この形式にすると、指定した範囲（この数式では検索キーワード）を順次適用して数式を計算していく。つまり、検索対象文字列にキーワード文字列を1つ1つ適用した関数を計算する。このとき数式には、SUM関数があるため、複数の結果が合計されて1つの値になる。COUNTIF関数は、検索を行い、そのヒット数を報告するものなので、キーワードを見つけると「1」、見つけなければ「0」を結果として戻す。これを足し算して「0」でなければ、どれかのキーワードが見つかったことを示す。