ExcelのXLOOKUP()関数でワイルドカードを使用する方法 - TechRepublic

検索関数は、ある値と一致する値を見つけるのに最適です。XLOOKUP() を使えば、この作業はこれまで以上に簡単になり、ワイルドカードもサポートされます。この記事では、アスタリスク（*）ワイルドカードを使って、より柔軟な検索値を作成する方法を紹介します。完全一致やほぼ一致を検索する代わりに、ワイルドカードを使用することで、よりシンプルな検索文字列で操作できます。この設定は、検索文字列の一部は覚えているけれど、全体は覚えていないという場合に便利です。この記事では、XLOOKUP() 関数の条件引数、つまり検索文字列でワイルドカードを使用する方法を紹介します。

参照: ソフトウェアインストールポリシー (TechRepublic)

VLOOKUP() を使うこともできますが、いくつか注意点があります。戻り値は検索値の左側にあるため、ソースデータを再構成する必要がありますが、VLOOKUP() ではこれを処理できません。ただし、私は今後は XLOOKUP() を使うつもりです。古い検索関数を使う特別な理由がない限り、それらを使うメリットはないと思います。XLOOKUP() について詳しく知りたい場合は、「Excel で新しい動的配列関数 XLOOKUP() を使う方法」をご覧ください。

Windows 10 64ビットシステムでMicrosoft 365を使用しています。ExcelのXLOOKUP()関数は、Microsoft 365、Excel 2021、そしてWeb版Excelで利用できます。ご参考までに、デモ用の.xlsxファイルをダウンロードしていただけます。この記事では、基本的なExcelスキルをお持ちの方を前提としていますが、初心者の方でも手順に沿って操作すれば問題なく使えるはずです。

準備

高級食品販売業者の製品管理にExcelを使用するとします。図Aに示すように、製品名が少し複雑で、正確に覚えておくのは難しいかもしれません。検索時には、フィルター処理やデータ入力規則の設定を検討することもできますが、どちらの場合も製品リストが長くなり、扱いにくくなります。次のステップとして、XLOOKUP()関数を使用するのが考えられます。XLOOKUP()関数は、検索値（この場合は製品名）に基づいて単一の値を返します。

=XLOOKUP($I$2,Table2[ProductName],Table2[UnitPrice])

=XLOOKUP($I$2,$D$3:$D$47, $C$3:$C$47)

製品名は必ずフルネームで入力してください。例えば、「Uncle Bob's Organic Dried Pears」を検索する際に「Pears」とだけ入力しても、期待外れの結果になってしまいます（図A）。（Tableオブジェクトを使用しない場合、Tableオブジェクトは構造参照を使用するため、参照結果が異なって見えることに注意してください。）

図A

エラー値は良い手がかりになります。#N/A は、関数が I2 の値を見つけられないことを意味します。#VALUE エラーが表示された場合は、関数自体に何らかの問題があります。D 列の値を I2 に貼り付けると、関数は期待通りに動作するので、関数ではなく入力値であることが分かります。これで間違いありません。XLOOKUP() は完全一致を求めますが、現在の設定ではそれができません。しかし、ワイルドカードを使用すれば可能です！

Excelでワイルドカードを追加する方法

ワイルドカードの使い方に慣れていなくても心配はいりません。でも、本当に便利なので、ぜひ覚えておきましょう。ここでは、任意の数の文字を検索できるアスタリスク文字（*）を使用します。例えば、「pears」だけを検索値として商品名全体を検索するには、検索文字列として「*pears*」を使用します。

ただし、アスタリスク文字をそのまま挿入することはできません。このように関数にワイルドカードを追加する場合は、区切り文字と参照を連結する必要があります。ここで言う連結とは、Excelが評価する要素を1つの文字列に結合することを意味し、区切り文字はデータ型を識別することでこの処理を支援する文字です。要素の連結にはドル記号（$）を使用します。

さて、元のXLOOKUP()を振り返ってみましょう。

=XLOOKUP($I$2,Table2[ProductName],Table2[UnitPrice])

$I$2 は検索値、Table2[ProductName] は検索値が一致する必要がある列、Table2[UnitPrice] は一致が見つかった場合の戻り値です。簡単に言うと、この関数は UnitPrice 列から、ProductName の値が I2 の値と一致する値を返します。

必要な変更は、検索値$I$4だけです。具体的には、ワイルドカードを2つ追加し、二重引用符を文字列の区切りとして使用します。

"*" & $I$4 & "*"

これは*$!I$4*と評価されます。アスタリスク文字を囲む文字列区切り文字が必要です。修正された関数

=XLOOKUP("*"& $I$4 &"*",Table2[ProductName],Table2[UnitPrice],2,2)

図 Bに示すように、ワイルドカードを正しく処理します。

=XLOOKUP("*"& $I$4 &"*",Table2[ProductName],Table2[UnitPrice],2,2)

図B

ここで一つ指摘しておきたいのは、この関数は単一の値を返すということです。I4に「Sir」と入力してみてください。図Cに示すように、この関数は$81を返します。これはD21の「Sir Rodneys' Marmalade」と一致します。問題が分かりますか？D22の値にも「Sir」という文字列が含まれています。これが問題になる可能性のある唯一の点です。

図C

Excelにおける重複に関する警告
XLOOKUP() を使用して単一の値を検索する場合、ワイルドカードを使用できますが、検索文字列が複数の値に存在する場合、関数が正しい値を返さない可能性があります。そのため、一致するレコードが他にも存在することをユーザーに警告することをお勧めします。

このアラートには様々なアプローチがありますが、最も簡単な方法は条件付き書式を使用することです。この場合、検索文字列が2つ以上のレコードに一致する場合にユーザーにアラートを表示する必要があります。まず、何を強調表示するかを決める必要があります。J4の結果か、実際の製品かです。どちらか一方、または両方を強調表示することもできます。前者の場合は、次の式を使用します。

=COUNTIF(Table2[ProductName],"*" & $I$4& "*")>1

セルK4に入力します。この関数は、検索文字列（現在はSir）がProductName列に複数回出現する場合はTRUEを、そうでない場合はFALSEを返します。この補助関数をユーザーに表示したくない場合は、列幅を狭くするか、列を非表示にしてください（後者は関数が忘れやすく、後で見つけやすいため、あまりお勧めしません）。これで、条件付き書式ルールを作成する準備が整いました。

I4:J4を選択します。「ホーム」タブの「スタイル」グループで「条件付き書式」をクリックし、表示されるドロップダウンから「新しいルール」を選択します。
表示されるダイアログの上部ペインで、「数式を使用してどのセルをフォーマットするかを決定する」をクリックし、下部ペインのコントロールに次のように入力します。
=$K$4=TRUE
「フォーマット」をクリックします。
「フォント」タブをクリックし、「色」ドロップダウンから赤などの明るい目を引く色を選択して、「OK」をクリックします。図Dは関数と書式を示しています。
「OK」をクリックしてシートに戻ります。検索文字列「Sir」が「ProductName」列に複数回出現するため、ルールでは「Sir」と「$81」に赤いフォントが使用されていることに注目してください。

図D