・大量の取引先リストから、会社名の一部だけで探したい
・登録されている顧客情報に表記揺れがあって上手く検索でいない
・プロジェクト名やイベント名が長くて覚えきれない
業務中に、このようなことで困ったことはありませんか？
そんな時は、
「検索用セルに入力した文字で、部分一致検索」
の機能を再現しましょう！
まずはサクッと使いたい！
「部分一致検索」の機能は、以下の関数式で再現できます。
=FILTER(
[対象リストの記載範囲],
REGEXMATCH( [対象リストの記載範囲],
"(?i)" & REGEXREPLACE( [検索ワード用セル] ,"([][.^$*+?{}()|\])","\\$1"))
)※青文字、赤文字の部分を書き換えてご利用ください
[対象リストの記載範囲] = D2:D12
[検索ワード用セル] = B3
関数が入力されているセル = B5
各範囲が上記の状態で動かしたサンプルが以下です。
数式の仕組みを分解解説
どのように動いているのか確認してみましょう。
使用している関数は以下の3点です。
① FILTER関数：条件に合った行だけを返す
②REGEXMATCH関数：文字列が正規表現にマッチするか判定
- (?i)：大文字小文字を区別しないオプション
③REGEXREPLACE関数：検索文字に正規表現の特殊文字が含まれても安全に処理する
FILTER関数
FILTER関数は、[範囲] の中から [条件] に合致する行を返す関数です。
=FILTER(A2:B26, A2:A26 > 5, D2:D26 < 10)上記の例では、
[範囲] A2からB26の範囲から
[条件1] A列が5より大きい
[条件2] D列が10未満
を、結果として出力します。
※「なんで条件にD列を指定できるの？」と疑問に感じた人も居るかもしれません。
これがFILTER関数の柔軟なポイントで、行が一致していれば[範囲]外の列も条件として指定可能です。
REGEXMATCH関数
対象のテキストに
「正規表現を使ったワードに一致するテキストが含まれるか」
を検証する関数です。
=REGEXMATCH("Spreadsheets", "S.r")上記の例では
[対象のテキスト] Spreadsheets
[正規表現を使ったワード] S.r
となります。
「正規表現」について詳しい説明は割愛しますが、
「文字列の中から、特定のパターンに合うルールを記号化したもの」です。
上記の例では ドット( . ) が 「任意の1文字」を表す記号となっています。
つまり[対象のテキスト]の中に
「S [何かしら1文字] r」 に合致する文字の並びがあればTRUEを返します。
REGEXREPLACE関数
対象のテキストに対して
「正規表現を使って一部を置換する」
関数です。
=REGEXREPLACE("Spreadsheet", "S.r", "スプレ")こちらの例では、
・「Spreadsheet」 というテキストの
・「S [何かしら1文字] r」 に合致する文字列を
・「スプレ」に書き換える
という処理を行っており、
結果は「スプレeadsheet」という文字列が返ります。
なぜ置換が必要？
検索文字に「+ 」や「 . 」 など正規表現で意味を持つ文字があると関数が上手く動かなくなる可能性があるため、REGEXREPLACEで特殊文字を「ただの文字」として扱えるようにしています。
よくある応用3選
今回ご紹介した内容に少し工夫を加えると、
もう少し応用的な使い方をすることもできます。
複数リストを返す
FILTER関数が複数列を返せる仕組みであることを応用して、
「企業名の部分一致から付随する情報」を取得することができます。
IFERRORで エラーを回避
FILTER関数は
・一致する結果が無い
・そもそも検索ワードが指定されていない
このような場合、「エラー」を返します。
この場合は IFERROR を使用して、エラーパターンをカバーしましょう。
=IFERROR(FILTER($D$3:$D$12,regexmatch($D$3:$D$12,"(?i)"®EXREPLACE(B3,"([][.^$*+?{}()|\\])","\\$1"))),"結果がありません")
大文字小文字を区別したい場合は (?i) を外す
冒頭でご紹介した関数では、REGEXMATCH関数に
(?i) という「アルファベットの大文字/小文字を区別しない」オプションを入れています。
このため、検索ワードがAでもaでも
「株式会社ABCホールディングス」が結果に出力されます。
これを敢えて”区別したい“場合には、以下の式のように
(?i) のオプションを削除すればOKです。
=FILTER($D$3:$D$12,REGEXMATCH($D$3:$D$12,REGEXREPLACE(B3,"([][.^$*+?{}()|\\])","\\$1")))
↑検索ワードが大文字なので
「株式会社ABCホールディングス」がヒットします
↑小文字のaを含む企業名は無いため、結果はエラーとなります
まとめ
本記事では、FILTER と REGEXMATCH を組み合わせて「部分一致抽出」を行う方法を紹介しました。
さらに REGEXREPLACE を加えることで、特殊文字によるエラーを避け、より安定した検索が可能になります。
この仕組みを応用すれば、実務でよく使う「検索ボックス」をスプレッドシート上に簡単に作ることができます。
大量のデータから必要な情報をすぐに取り出せるようになるので、日常業務の効率化にとても役立つテクニックです。
ぜひご自身のファイルでも試してみてください！
コメント