【Excel】キーワード検索で「霞が関」でも「霞ヶ関」でもヒットさせるには：Tech TIPS
Excelで大量のテキストデータに対し、複数のキーワードのどれかが該当するものを探したい場合はないだろうか。そのような場合、SEARCH関数やFIND関数ではうまく検索できない。そこで、簡単に複数キーワードで検索できる方法を紹介しよう。
対象：Office 2013／2016／2019／365
大量のテキストデータから複数のキーワードの中のどれかを持っているデータを探したい、という作業を行う場合もあるのではないだろうか。例えば、住所録のようなものから、「霞が関」でも「霞ヶ関」でも該当するものを見つけたいというような場合だ。このような場合、データを探した後の集計処理などを考えると、「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（偽）で計算結果が戻る。
SUBSTITUTE関数の使い方
SUBSTITUTE関数でも似たような構造を使う。こちらもCSE配列数式とすることで、キーワードセル範囲と検索対象セルにSUBSTITUTE関数が適用される。置き換えが行われると元の文字列とは一致しなくなるため、比較演算子「<>」の結果がOR関数で集計されて戻される。
キーワードをセル範囲と定義する理由
キーワードをセル範囲として定義し、数式内に入れないのは、追加や削除を簡単にするためだ。こうした分析では、はじめからキーワードの全てが判明していることは少ない。作業している間に、キーワードを追加していくことになる。しかし、そのたびに数式を書き換えていたのでは効率が悪い。
そこでキーワードは、セル範囲で指定し、これを数式から参照している。なお、キーワードのセル範囲が大きくなることを考え、セル範囲には、「名前」を付けておく。こうすることで、セル範囲が拡大しても、数式を変更する必要がなくなる。
COUNTIF関数による複数キーワードの検索
ここでは、サンプルとしてテレビの番組名から映画番組のようなものを探す場合を考える。映画番組が持つと思われるキーワードを指定し、番組名からキーワード検索を行い、映画番組らしい番組を探すことを考えてみる。
まずは、検索キーワードを用意する。COUNTIF関数の場合、「*（任意文字数のワイルドカード）」「?（1文字のワイルドカード）」をキーワードに含めてセル領域に記述しておく。この時点では、縦1列に並べることにする。これは後で隣の列に全角／半角に変換したキーワードを置くためだ。キーワードより少し長めの範囲を選択し、ここに「名前」を付ける。この例では「キーワード」とするが、好きに名前を付けてよい。
セル範囲に名前を付ける方法については、Tech TIPS「Excelの『名前の定義』でセルの範囲に名前を付ける」を参考してほしい。
検索キーワード範囲に名前を付けたら、数式を入力する。ここでは、検索対象データが1つの列に縦に並んでいるとする。その隣にセルにCOUNTIF関数を使った数式を入れ、最後に［Ctrl］＋［Shift］＋［Enter］キーで入力を終える。こうすることで、入力した数式は「CSE配列数式」になる。数式を見ると全体が「{ }（波括弧）」で囲まれていることが分かる。これがCSE配列である。CSEとは「Ctrl Shift Enter」の略で、数式入力時のキー操作から来た名称だ（コラム参照）。
=SUM(COUNTIF($C2,キーワード))>0
この形式にすると、指定した範囲（この数式では検索キーワード）を順次適用して数式を計算していく。つまり、検索対象文字列にキーワード文字列を1つ1つ適用した関数を計算する。このとき数式には、SUM関数があるため、複数の結果が合計されて1つの値になる。COUNTIF関数は、検索を行い、そのヒット数を報告するものなので、キーワードを見つけると「1」、見つけなければ「0」を結果として戻す。これを足し算して「0」でなければ、どれかのキーワードが見つかったことを示す。
CES配列数式の動作
CSE配列数式は、セル範囲が指定された引数を順に代入した数式を計算する。このとき数式にはSUM関数があるため、それらの合計が計算される。COUNTIF関数は、対象セルに条件を満たすセルが幾つあるのかを返すため、合計が「0」よりも大きい場合、キーワードのどれかが見つかったことを示す。
先頭のセルに対して数式を入力したら、後はフィルハンドルで必要なだけ数式をコピーすればよい。注意するのは数式を変更した後に、必ず［Ctrl］＋［Shift］＋［Enter］キーで数式を確定させることだけだ。なお、Office 365とOffice 2016以前では挙動が異なるので注意してほしい（コラム参照）。ただし、この方法であれば、どちらで作成したブックも、他方で読み込んで正しく結果を返す。
キーワードで大文字／小文字を区別させたい場合などには、SUBSTITUTE関数も同様にCSE配列数式として入力する。数式とキーワードのワイルドカードの有無が違うだけで、基本的なやり方は同じだ。
=OR(SUBSTITUTE($C2,キーワード,"")<>$C2)
CSE配列数式に利用できる関数は他にもあるが、エラー値を返す関数（例えば、SEARCHやFIND関数）はCSE配列で利用しにくい。このためExcel 2016までで利用でき、文字列検索が可能で配列計算に利用できる標準関数はごく限られている。
全角半角を同一視させる
COUNTIF関数もSUBSTITUTE関数も、全角と半角の片仮名は区別して扱う。しかし、旧来のシステムからのデータでは、いまだに半角片仮名が混在していることがある。また、システムによっては、ASCIIコード相当の英数記号を全角文字として出力するものもある。簡易には、キーワードに手入力で半角片仮名や全角英数記号のキーワードを追加することも可能だが、キーワードが大量にあると作業はかなり大変だ。
このような場合、数式で半角片仮名、全角英数記号を用意してキーワードに含めることで対応が可能だ。半角片仮名への変換にはASC関数、全角英数への変換にはJIS関数を使う。キーワードを定義した列の横にこれらの関数を使って、半角キーワードと全角キーワードを作成する。これは数式のみで行えるため、ユーザーがキーワードを見て判断する必要はない。
COUNTIF関数で英数記号や片仮名の全角半角を区別しない検索をする方法
COUNTIF関数で英数記号や片仮名の全角半角を同一視（区別しない）して検索する場合、数式を使って、キーワードを半角化、全角化しておき、それをキーワードの範囲に加えて名前を指定する。今回の数式では、キーワード範囲は、複数行、複数列でも動作できる。
キーワード列の横にASC関数やJIS関数を使った数式で半角化、全角化したキーワードを作る。ただしJIS関数で全角化を行う場合、ワイルドカード文字は半角でのみ有効なので一回別の文字（絵文字などのキーワードには現れない文字）に置き換えて、JIS関数を適用してから、元の「*（半角アスタリスク）」と「?（半角クエスチョンマーク）」に戻している。この変換したキーワードを含めたセル範囲に名前を付ければ、前記のCOUNTIFやSUBSTITUTE関数の数式は自動的に対応してくれる。これがセル範囲に名前を付けるメリットでもある。
複数キーワード検索は、意外に利用することが多い。特にPC以外の機器などから入手してきたテキストデータなどは、複数キーワード検索を使うことで分類整理が可能になり、Excelでも処理が行えるようになる。
Excelの配列
Excelにはもともと「配列」と呼ばれる機能があった。これは数式を波カッコでくくることで、セル範囲などに一括して数式を適用（配列数式）する機能だった。Excel 2019までは、数式の入力時に［Ctrl］＋［Shift］＋［Enter］キーを押し、通常の数式入力と区別していた。
これに対して2018年9月のアップデート以降、Excel 365では、関数の引数を見て自動的に配列かどうかを判断する「動的配列」が導入され、Excel 2016までの配列を「CSE配列」と呼んで区別するようになった。両者は計算としてはほぼ同じだが、波括弧の有無など数式表現としては違うものだ。
今回紹介したCOUNTIF関数やSUBSTITUTE関数の数式は、動的配列に対応したExcel 365では［Enter］キーの入力だけでも正しく動く。しかし、Excel 2019以前の場合には、［Ctrl］＋［Shift］＋［Enter］キーで入力する必要がある。Excel 365はCSE配列にも対応しており、［Ctrl］＋［Shift］＋［Enter］キーを使って入力しても正しく動作する。また、Excel 365で作成したブックをExcel 2016で読み込むと、自動的に動的配列はCSE配列に変換されるため、互換性の問題はない。
Copyright© Digital Advantage Corp. All Rights Reserved.