スプレッドシートで作業していると、共通のセルを持つ2つの異なるワークシートのデータを一致させたい場合があります。例えば、従業員名とID番号のリストと、名前と住所のリスト、あるいはトラフィックデータと著者名が別のシートに分かれているWeb記事のリストなどです。そんな時、Excelの新しいXLOOKUP関数が役立ちます。
2020年2月にExcelの最新リリースバージョンに初めて追加されたXLOOKUPは、人気のVLOOKUP関数を改良したものです。VLOOKUP関数も異なるシートのデータを結合できますが、機能は限定的です。また、検索したデータと同じ列にあるデータを検索するHLOOKUP関数の機能もすべて備えています。
VLOOKUPに対するXLOOKUPの利点
- 複数の列を一度にコピーできます。
- 参照セルが左の最初のセルにある必要はありません
- デフォルトでは完全一致のみになります（VLOOKUP ではそうではありませんでした）
- 不完全な一致にはワイルドカードを使用できる
- 一致するものがない場合に「見つかりません」というテキストを指定できます
- 必要なパラメータは3つだけ
XLOOKUPは最大6つのパラメータを受け入れますが、必須なのは最初の3つだけです。書式は次のとおりです。
=XLOOKUP(検索値,検索配列,戻り配列,[見つからない場合],[一致モード],[検索モード])
まず、簡単な XLOOKUP クエリを実行する方法を説明します。
シンプルなXLOOKUPクエリの実行方法
1.結果を表示する最初のセルに「 =XLOOKUP(」と入力します。
2.参照値を含むセルをクリックし、カンマを入力します（セルアドレス（例：C2）を入力することもできます）。これが、両方のシートで照合する値です。この例では、参照値はセルC2で、姓が含まれています。値を引用符で囲んで入力することもできますが、その場合、複数の行にコピー＆ペーストして、それぞれ異なる一致を取得することはできません。
Tom's Hardware の最高のニュースと詳細なレビューをあなたの受信箱に直接お届けします。
3.参照値を検索するセル範囲を選択し、カンマで区切ってください。データが入っているセルだけをハイライトするのではなく、列全体を選択することを強くお勧めします。そうすることで、コピー＆ペーストした場合でも、範囲全体が同じになります。
範囲を手動で入力することもできますが、マウスでハイライトするのがおそらく最も簡単です。この列は、同じExcelファイルの別のタブ、あるいはコンピュータ上の全く別のファイルにある場合もあります。今回の場合は、ファイルの「住所」タブにある列B全体を選択します。これは、姓も含まれているためです。
4.返したいセル範囲を選択し、閉じ括弧を追加して関数呼び出しを完了します。繰り返しになりますが、列全体を選択することをお勧めします。複数列分のデータを選択した場合、最初の列以降のすべての列がXLOOKUP式に隣接するセルにコピーされます。
今回のケースでは、「住所」シートのC列からE列までを選択し、従業員のメールアドレス、性別、IPアドレスを継承できるようにしました。最終的な数式は以下のようになります。
=XLOOKUP(C2,住所!B2:B1001,住所!C2:E1001)
最終結果は、下の画像のようなものになります。
5.数式を他のセルにコピー＆ペーストして、行全体に適用します。数式をデータのある最後の行までドラッグすると、Excelは自動的に参照値セルを適切な行番号に置き換えます。つまり、最初のセルがC2で、数式を行500にコピーすると、C500になります。
ただし、検索範囲または戻り値範囲のパラメータで列全体を選択していない場合は、コピー＆ペーストする前にセル範囲に$記号を追加する必要があります。そうしないと、下の行に貼り付ける際に検索対象のセル範囲が変わってしまいます。$記号を追加する最も簡単な方法は、数式内の該当する部分を選択してF4キーを押すことです。
XLOOKUPの「見つからない場合」パラメータ
XLOOKUP関数の4番目のパラメータを指定しないと、検索に失敗したセルはすべて#N/Aと表示されます。ただし、セルのメッセージをカスタマイズしたい場合（または空白のままにしたい場合）、見つからないというメッセージを引用符で囲んで追加するだけです。
この例では、「申し訳ありませんが、見つかりませんでした」というテキストを使用しました。式は次のようになります。
=XLOOKUP(C2,住所!B2:B1001,住所!C2:E1001,"申し訳ございません。見つかりませんでした。")
XLOOKUPの一致モード
XLOOKUPはデフォルトで完全一致のみを返すため、最後の「Symmonds」を検索していて、別のシートでは「Simmonds」と綴られている場合、一致するものは見つかりません。また、「300」のような数値を検索していて、それより大きい数値やより小さい数値がある場合も、一致するものは見つかりません。
ただし、5番目のパラメータでは「一致モード」を選択できます。Excelはワイルドカード、次に大きい数値、または次に小さい数値を検索します。デフォルトのモード0は完全一致です。-1を入力すると、完全一致が見つからない場合に次に小さい項目を検索します。つまり、300を検索しているときに200と400が見つかった場合、200が返されます。一致モード1では、次に大きい数値（400）が返されます。
マッチモード2では、ワイルドカード文字を使用して検索できます。ワイルドカード「*」は任意の数の文字に一致しますが、「?」は1文字のみに一致します。つまり、「Symmonds」または「Simmonds」のいずれかを検索したい場合は、マッチモード2で「S?mmonds」と入力します。Sで始まる名前を検索したい場合は、マッチモード2で「S*」と入力します。
XLOOKUP 検索モード
6番目で、おそらく最も重要度が低いXLOOKUPパラメータは、Excelの検索方法を制御します。ほとんどの人や状況では、このパラメータはおそらくあまり変化しないため、完全に省略することをお勧めします。
検索モードには4つのオプションがあります。デフォルトのモード1では、検索範囲の最上行から検索を開始し、最初の一致を見つけます。モード-1では、下から上へ検索します。モード2（先頭から最後へ）と-2はバイナリ検索です。バイナリ検索では、データがソートされている必要があり、システムは各セルを列の中央値と比較し、一致しない場合は検索範囲をさらに絞り込みます。正直なところ、なぜこれが必要なのか分かりませんが、大規模なデータセットでは高速化される可能性があります。
Avram Piltchは特別プロジェクト担当の編集長です。仕事で最新ガジェットをいじったり、展示会でVRヘルメットを装着したりしていない時は、スマートフォンのルート化、PCの分解、プラグインのコーディングなどに取り組んでいます。技術的な知識とテストへの情熱を活かし、Avramはノートパソコンのバッテリーテストをはじめ、数多くの実環境ベンチマークを開発しました。
