g714｜Excel XLOOKUP関数：スピルで複数データの一括取得！「縦横検索」表引き検索をさらに効率化

ExcelのXLOOKUP関数は、従来のVLOOKUPやHLOOKUPを置き換える強力な検索関数です。特に、XLOOKUPと組み合わせて使用することで真価を発揮する「スピル」機能は、複数のデータを一度に取得する際に非常に便利です。この記事では、XLOOKUPとスピルの連携に焦点を当て、その使い方と利点を詳しく解説します。

XLOOKUP関数とは？

XLOOKUP関数は、指定された範囲または配列で値を検索し、最初に見つかった一致に対応する項目を返す関数です。

基本的な構文（復習）

Excel

=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])

各引数の意味は以下の通りです。

• 検索値（必須）: 検索する値を指定します。
• 検索範囲（必須）: 検索を行う範囲を指定します。
• 戻り範囲（必須）: 結果として返す値が含まれる範囲を指定します。
• 見つからない場合（省略可能）: 検索値が見つからない場合に返す値を指定します。
• 一致モード（省略可能）:
  • 0（既定値）：完全一致。
  • -1：完全一致。見つからない場合は、次に小さい項目を返します。
  • 1：完全一致。見つからない場合は、次に大きい項目を返します。
  • 2：ワイルドカード文字（*、?、~）を使用した部分一致。
• 検索モード（省略可能）:
  • 1（既定値）：先頭から末尾へ検索。
  • -1：末尾から先頭へ検索。
  • 2：昇順で並べ替えられた範囲を使用したバイナリ検索。
  • -2：降順で並べ替えられた範囲を使用したバイナリ検索。

スピルとは？

スピルとは、数式が自動的に複数のセルに展開される機能です。従来のExcelでは、数式をコピー＆ペーストして複数のセルに適用する必要がありましたが、スピル機能を使用することで、数式を1つのセルに入力するだけで、結果が自動的に周囲のセルに表示されます。

XLOOKUPとスピルの連携：複数データの一括取得

XLOOKUP関数は、戻り範囲に複数の列を指定することで、スピル機能を利用して複数のデータを一度に取得できます。画像例を基に解説します。

＜Excelサンプルデータダウンロード＞
Excel XLOOKUP関数：スピルで複数データの一括取得！表引き検索をさらに効率化

画像例の解説

画像では、商品リスト（I3:L7）から、品番号に基づいて「商品名」「分類」「単価」をまとめて取得しています。セルC3に入力されている数式は以下の通りです。

Excel

=XLOOKUP(B3,$I$3:$I$7,$J$3:$L$7,"")

この数式を分解して解説します。

1. B3: 検索値（品番号）が入力されています。
2. $I$3:$I$7: 検索範囲（物品リストの品番号列）を指定しています。$マークは絶対参照を表し、数式をコピーしても参照先がずれないようにしています。
3. $J$3:$L$7: 戻り範囲として複数の列（商品名、分類、単価）を指定しています。これがスピル機能の鍵となります。
4. "": 見つからない場合の値を指定しています。ここでは空白文字列を指定しています。

この数式をセルC3に入力すると、XLOOKUP関数はB3の品番号を商品リストから検索し、対応する「商品名」「分類」「単価」をC3、D3、E3の3つのセルに自動的に展開します。これがスピルです。

さらに、セルC3を下の行（C4、C5、C6など）にコピーすると、それぞれの行の品番号に基づいて同様の処理が行われ、複数の商品の情報を一度に取得できます。

Excelのサンプルデータ【ダウンロード】

以下は、上記画像のExcelデータですので、ダウンロードして練習などに使用してください。

Excel-g714-1.xlsx  （ダウンロード）

スピルのメリット

• 効率的なデータ取得: 複数のデータを一度に取得できるため、作業効率が大幅に向上します。
• 数式の簡略化: 複数のセルに同じような数式を入力する必要がなくなります。
• 動的な更新: 元データが変更された場合、スピルされた結果も自動的に更新されます。

スピルの注意点

• スピル範囲の確保: スピル先のセルにデータが入力されていると、スピルは正しく機能しません。スピル先のセルが空白であることを確認してください。
• スピルの表示: スピルされた範囲は、薄い青色の枠線で囲まれて表示されます。
• 編集: スピルされた範囲の一部のセルを直接編集することはできません。数式が入力されている最初のセル（上記の例ではC3）を編集する必要があります。

縦方向のスピル

画像の下部には、検索範囲に1列分のセルを指定することで、縦方向にスピルさせる例も紹介されています。ただし、縦横同時にスピルさせることはできません。

まとめ

XLOOKUP関数とスピル機能を組み合わせることで、複数のデータを効率的に取得できます。この機能は、データ分析やレポート作成など、様々な場面で役立ちます。スピルの利点と注意点を理解し、XLOOKUP関数を最大限に活用しましょう。

これらの情報も合わせて理解することで、XLOOKUPとスピルの使い方をより深く理解できるでしょう。

この解説で、XLOOKUP関数のスピル機能についてより明確になったかと思います。この情報を活用し、Excelでのデータ分析をより効率的に行いましょう。