この記事には広告を含む場合があります。
記事内で紹介する商品を購入することで、当サイトに売り上げの一部が還元されることがあります。
こんにちは。
Excelで顧客名簿等のデータリストのなかから[keikou]特定の文字を含む顧客データ[/keikou]を検索したい場合、どうしていますか？
たとえば、名称で「喫茶店」を含む個人事業主を検索したい、あるいは「宇宙」を含む株式会社を検索したいというような場合、Excelではどうやって検索することができるでしょうか？
VLOOKUP関数は便利ですが、顧客コードなど検索対象項目となる数値を覚えておくのは、なかなか難しいです。マクロ機能を使ってvbaでプログラムをするなら慣れていれば簡単な作業ですが、マクロを知らない人は抵抗がありますよね。
そこで私は、VLOOKUP関数、IF関数、AND関数、IFERROR関数、FIND関数、SEARCH関数など複数の関数を組み合わせて検索する方法を考えてみました。
データリストの一部を別シートにコピーするなど、ちょっとした手間もあり、あまりうまい方法ではないかも知れませんが、名称に使われている[keikou]文字の一部で検索することができ[/keikou]、ほぼ思い通りの検索ファイルを作成することができました。（6万件データリストでも使えます。）
今日は、Excelの関数を使って、データリストから特定の文字を含むデータを検索する方法について記載します。
参考
参考 Office Support；検索/行列関数 (リファレンス)
参考 Office Support；VLOOKUP関数
参考 Office Support；IF関数
参考 Office Support；AND関数
参考 Office Support；IFERROR関数
参考 Office Support；FIND関数、FINDB関数
参考 Office Support；SEARCH関数、SEARCHB関数
参考 Office Support；MATCH関数
Excelの関数を使って、データリストのなかから特定の文字を検索する
次のようなデータリストを用意しました。
このデータリストのなかから、『フリガナに「マル」という文字を含む顧客の顧客コードを調べたい』場合、あなたならどうしますか？（最終的には1つの顧客コードに絞り込みたいのです。）
マクロ機能を使わず、Excelの関数だけで検索するとしたら・・・。
最初にExcelの関数の機能を確認する意味で、関数を1つだけ使って『フリガナに「マル」という文字を含む顧客の顧客コード』の検索ができるかどうかを検証してみました。
検証の前に次の2つのシートを用意しました。
● 検索用データリストの作成
検証用に、上の顧客データの「フリガナ」（顧客データのB列）と「顧客コード」（顧客データのA列）を含む次のような検索用のデータリストを作成しました。（「フリガナ」を対象とした検索になるため、「フリガナ」をA列にしてあります。）
VLOOKUP関数だけを使用した場合
最初にVLOOKUP関数を使って、検索用データリストの「フリガナ」（A列）のなかから、特定の文字を検索してみます。
関連記事 条件に一致するデータを探すExcelのVLOOKUP関数
検索したい文字をB3セルに入力します。（下の画像のとおり、この例では『マル』）
（横長の表を作ってしまったので、上下に画像を分けました。）
H3セルには次のような式を入力しました。
[aside type=”boader”]
=VLOOKUP(B3,ﾃﾞｰﾀ1!検索用1,2)[/aside]
「B3セルに入力された文字（『マル』）を検索対象のデータリストで検索し、『顧客コード』を返す」という式です。
この式ではうまくいきません。
上の画像のとおり、「18010346」という「マル」がまったく含まれていない「ビジネスホテル♢♢」の顧客コードを返してきました。
※ VLOOKUP関数だけでは『フリガナに「マル」という文字を含む顧客の顧客コード』の検索には向かないようですが、VLOOKUP関数は、次の引用画像のとおり、[keikou]検索するデータの左端の列を英字または数値順に並べ替えておく必要がある[/keikou]ので、フリガナの列を昇順で並び替えて再度、検索してみました。
（Office Support；VLOOKUP関数＞よくある質問より。赤い下線は筆者追記。）
検索用データリストのA列を昇順で並べ替えてみましたが、結果は同じでした。
（ちなみに、A列を昇順にして、完全一致するフリガナ（例えば「カブシキガイシャ マルマルブッサン」）で検索をした場合は、正しい顧客コードが戻り値として表示されます。）
MATCH関数だけを使用した場合
次にMATCH関数だけを使って、検索用データリストの「フリガナ」（A列）のなかから、特定の文字（『マル』）を検索してみます。
検索用データリストの横にD列からG列に「検索文字、MATCH関数、FIND関数、SEARCH関数」という列を設定し、結果を表示するようにしました。（IFERROR関数を使用して、エラーとなる場合は、空白となるようにしました。）
E2セルにはMATCH関数を使って、次のような式を入力しました。（E3セル以下のE列は何も式を入力していません。）
[aside type=”boader”]
=IFERROR(MATCH(D2,A$2:A$17,0),””)[/aside]
「D2セルに入力された文字が含まれたデータを検索対象のデータリストのなかから探し、そのデータの位置を返す」という式です。
検索用データリストのなかには、今個の検索する文字『マル』に完全一致するデータはないため、IFERROR関数によって空欄（””）が返され、E2セルには何も表示されていません。
※ ちなみに『カブシキガイシャ ホシホシホシリョカン』で検索したところ次のように「6」が表示されました。（赤い丸囲み部分）「カブシキガイシャ ホシホシホシリョカン」A7セルに含まれていて、データとしては上から6個目のため、「6」が戻り値となります。
FIND関数だけを使用する場合
次にFIND関数だけを使って、検索用データリストの「フリガナ」（A列）のなかから、特定の文字（『マル』）を検索してみます。
F2セルには次のような式を入力しました。（F3セル以下も各行のセルに合わせた同じ式を入力しています。）
[aside type=”boader”]
=IFERROR(FIND(D2,A2),””)[/aside]
「D2セルに入力された文字をA2セルに入力された文字のなかから検索し、（左端から）初めてその文字が出てくる位置を返す」という式です。（開始位置を省略していますので、左端から数えた値を返します。）
A2セルに入力した文字列「カブシキガイシャ サンカクサンカクオフィス」には、「マル」が含まれていないため、IFERROR関数により空欄（””）が返され、F2セルには何も表示されていません。
A7セルなどには、「マル」が含まれるため、開始位置からの最初に出てくる文字の位置である「10」や「1」が表示されました。
※ FIND関数とSEARCH関数は同じほぼ機能となりますが、違いは次の通りです。
（Office Support；FIND関数、FINDB関数より。下線は筆者追記。）
SEARCH関数
G2セルにはSEARCH関数の式を入力しました。FIND関数と似ていますので、省略します。
Excelの関数を複数、使って、データから特定の文字を検索する方法
前置きが長くなりましたが、いよいよ今日の本題です。
前述のとおりFIND関数とSEARCH関数を使うと、特定の文字を含むデータの（その文字の位置）を戻り値として取得することができます。
その機能を利用して顧客コードを探し、最終的に1つの顧客データに絞り込むことができないかと考え、次のような対策をしました。
[aside type=”boader”]□ 検索対象項目を増やす
□ IF関数、AND関数、FIND関数又はSEARCH関数でデータを絞り込む[/aside]
[topic color=”blue” title=”FIND関数とSEARCH関数の使い分け”]FIND関数とSEARCH関数について、私は次のように使い分けています。
● データの量が多い場合・・・SEARCH関数（ワイルドカード文字を利用することが想定される場合）
● データ量が少量の場合・・・FIND関数（大文字と小文字を区別する必要がある場合を含む）[/topic]
検索対象項目を増やし、検索シートを改良
特定の文字だけで検索をかけると、同じ文字を含んだデータが複数ある場合は、（最終的に）1つの顧客コードを探すという目的が果たせないため、検索対象項目を増やして絞り込みをしてみました。
今回は、顧客データの「形態」と「種別」を検索対象項目に加え、検索用データリストを次のように変更しました。（以下このリストを「検索用データリスト2」と記載します。）
また、検索シートにも「形態」と「種別」を選択できるように改良しました。
IF関数、AND関数、FIND関数で文字列を絞り込む
検索用データリスト2の横に次のような列（F列~K列）を用意しました。
それぞれの列はつぎのような設定になっています。
F列からH列（検索条件の表示）
検索シートで入力した「形態」、「種別」、「検索したい文字」（上の例ではそれぞれ『法人、株式、マル』）が表示されるようにしました。
I列（検索条件に一致する顧客コードの表示列）
IF関数、AND関数、FIND関数を使って次のような式を入力しました。
[aside type=”boader”]
=IF(AND(A2=F2,B2=G2,IFERROR(FIND(H2,C2),0)>0),D2,””)[/aside]
式を分解して解説します。
① 『形態』については、検索シートで選択したもの（上の例では『法人』）と検索用データリスト2の同じ行のデータが一致するかを確認しています。
② 『種別』についても、①の『形態』と同じく検索シートで選択したもの（上の例では『株式』）と検索用データリスト2の同じ行のデータが一致するかを確認しています。
③ 『検索する文字』については、FIND関数と使って検索用データリスト2の同じ行のデータにその文字が含まれているかどうかを確認しています。文字が含まれている場合、左端からの位置を返すため[ephasis]プラスの数値が返される[/ephasis]ことなります。
そこで、「>0」という式を記述して「（戻り値が）0より大きいか？」かをチェックするようにしました。
（IFERROR関数によって、戻り値がエラーとなる場合は「0」となるようにしました。つまりエラーとなる場合（検索する文字が含まれない場合）は「＞0」という条件を満たさなくなります。）
④ IF関数とAND関数によって、①、②、③の3つの条件を満たす場合は、顧客コードを表示し、満たさない場合は、空白（””）を表示するようにしています。
結果は上の画像のとおり、3つのデータが条件を満たし、顧客コードを表示しています。（検索結果が複数でも顧客コードを表示することができますが、下の「新潟県の法人番号データ（約6万件）でテストしてみました」の章で解説していますので、そちらで確認してください。）
⑤ I19セルではI列の合計値を表示します。（最終的に検索結果を1つに絞り込むことができれば、1つ分の顧客コードが表示されるはずです。）
J列（検索結果が複数あるかどうかのチェック列）
J列ではCOUNT関数を使って、I列で顧客コードが表示されている列をカウントしています。（I列でも確認したとおり、この例では3つのデータが条件を満たしています。）
K列（絞り込みをチェックするための参考列）
K列ではFIND関数によって、検索文字（上の例では『マル』）が検索用データリスト2の各データに含まれている場合の戻り値を表示しています。（参考のために表示するようにしています。）
『マル』を含むセルは6つありますが、「法人」、「株式」の要件を満たすのはそのうち3つという[keikou]検索結果の絞り込み[/keikou]ができています。
検索シートで結果を確認
上記の検索結果を検索シートで確認すると次のとおりです。
E3セルに「3件あります。・・・」というメッセージが表示され、検索結果が複数あることが確認できます。（E3セルでは検索用データのJ19セルの数値を使って、文字列を表示しています。簡単なので式は省略。IF関数を使って検索結果が「1」のときは顧客コードを表示するようになっています。）
検索条件を変更してテストしてみました
上の画像のとおり、形態が「法人」、種別が「株式」、検索文字が「マル」の場合は、3件該当してしまうため、検索条件を次のように変更しました。
[aside type=”boader”]□ 形態・・・変更なし
□ 種別・・・「有限」に変更
□ 検索文字・・・変更なし[/aside]
結果は次のとおり、1件の顧客コードが表示されました。
B9セルからE9セルにVLOOKUP関数を使って、顧客データから顧客コードに対応する情報を表示するようにしてあります。
新潟県の法人番号のデータ（約6万件）でテストしてみました
上記の検索デ方法をより大きなデータリストでテストしてみました。
データリストは、法人番号検索サイトから新潟県のデータをダウンロードしてみました。約6万7千件ありました。
このデータを使って、住所と名称に含まれる文字から該当するデータを検索し、該当するデータの法人番号を表示するようなシートを作成してみました。
検索ページは次のようにしました。
フリガナのデータはなかったため、「市区町村」、「住所」、「名称」で検索するようにしました。
また、検索結果が複数だった場合、リストの上から数えて5件だけ、結果が表示されるようにしました。
ワイルドカード文字で検索できるSEARCH関数を使用
検索データリストを次のようにしました。
住所の一部しかわからないような場合に備えて、検索対象項目のすべてにおいて、ワイルドカード文字（「?」か「*」）で検索することができるSEARCH関数を使っています。
検索結果（法人番号）が表示されるH2セルには次のような式を入力しました。
[aside type=”boader”]=IF(AND(IFERROR(SEARCH(E2,A2),0)>0,IFERROR(SEARCH(F2,B2),0)>0,IFERROR(SEARCH(G2,C2),0)>0),D2,””)[/aside]
検索結果が複数ある場合の表示
検索結果が複数ある場合の表示は次のようにしました。
I列では、COUNT関数によって検索結果を数えていますが、J列でI列の数値を累計する式を記入し、増えるごとに別の列（K列以降）に検索結果の法人番号が表示されるようにしました。1つの列には1つの法人番号しか記入されないため、検索結果が複数であっても、設定した列の数だけ検索結果を表示することができます。
検索例
ちょっとふざけた例ですが、住所に「山」という字を1つ使っていて、名称に「山」を2つ使っている法人の番号を探してみました。（住所で検索する2つの山は、検索条件にワイルドカード文字を使って「山*山」と入力して検索しました。）
次の画像のとおり、1件だけ該当していました。
まとめ
Excelの関数使い複数の検査対象項目を設定すれば、大きなデータでも、一部分の文字を検索するだけで目的のデータを検索することができることがわかりました。
しかし、大きなデータは、やっぱりvbaの方が楽なのでは・・・と思っています。データ量が増え行数等に変更があっても、vbaであらかじめ行数等が増加した場合の対応を自動化しておけば、シートを手直しするような手間が省け、作業はかなり楽になりますよね。
■□◆◇ 編集後記 ◇◆□■
昨日、今日は関東は春の嵐ですね。昨日は友人のライブを聞きに六本木へ。バンドのメンバーが変わりながらも20年。すごいですね。
