g718｜Excel XLOOKUP関数：データ追加に完全対応！列全体指定とテーブルで参照範囲を自動拡張

Excelでデータを管理する際、データが頻繁に追加されることはよくあります。従来のVLOOKUP関数では、参照範囲を固定で指定していたため、データ追加のたびに数式を修正する必要がありました。しかし、XLOOKUP関数では、列全体指定やテーブル機能と組み合わせることで、データ追加に自動的に対応する数式を作成できます。この記事では、XLOOKUP関数とこれらの機能を組み合わせたデータ追加への対応について、詳しく解説します。

XLOOKUP関数とは？（復習）

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

基本的な構文（復習）

Excel

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

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

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

＜Excelサンプルデータダウンロード＞
Excel XLOOKUP関数：データ追加に完全対応！列全体指定とテーブルで参照範囲を自動拡張

1. 列全体指定（例：A:A）による自動拡張

XLOOKUP関数の検索範囲と戻り範囲に列全体を指定することで、その列に追加されたデータも自動的に検索対象に含まれるようになります。画像例を基に解説します。

画像例の解説

画像では、発注検索を行っており、セルG3に入力されている数式は以下の通りです。

Excel

=XLOOKUP(G2,A:A,C:C,"該当なし")

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

1. G2: 検索値（発注番号）が入力されています。
2. A:A: 検索範囲としてA列全体を指定しています。
3. C:C: 戻り範囲としてC列全体を指定しています。
4. "該当なし": 見つからない場合の値を指定しています。

数式の動作とメリット

この数式では、A列とC列全体を参照しているため、発注履歴表に新しい行が追加され、発注番号と発注者が入力された場合、その追加されたデータも自動的にXLOOKUPの検索対象に含まれます。これにより、データ追加のたびに数式を修正する必要がなくなります。

注意点: 列全体を指定する場合、検索対象の列に余計なデータ（例えば、表の下に合計行など）が含まれていると、意図しない結果になる可能性があります。検索対象の列は、検索対象のデータのみを含むように管理することが重要です。

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

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

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

2. テーブル機能と構造化参照による自動拡張

より高度な方法として、Excelのテーブル機能と構造化参照を組み合わせる方法があります。テーブルに変換された範囲は、データが追加されると自動的に拡張されるため、XLOOKUPの数式を変更する必要がなくなります。

テーブルへの変換方法

1. データ範囲を選択します。
2. 「挿入」タブの「テーブル」をクリックします。
3. 「テーブルの作成」ダイアログボックスで、範囲と先頭行をテーブルの見出しとして使用するかどうかを確認し、「OK」をクリックします。

構造化参照の使用

テーブルに変換後、XLOOKUPの数式でテーブル名と列名を指定することで、構造化参照を使用できます。画像の「Memo」に記載されている例を見てみましょう。

Excel

=XLOOKUP(G2,テーブル1[受注番号],テーブル1[客名],"該当なし")

この数式では、

• テーブル1[受注番号]: テーブル「テーブル1」の「受注番号」列を参照しています。
• テーブル1[客名]: テーブル「テーブル1」の「客名」列を参照しています。

このように構造化参照を使用することで、テーブルにデータが追加されると、XLOOKUPの参照範囲も自動的に拡張されます。

テーブルのメリット

• 自動拡張: データ追加時に参照範囲が自動的に拡張されます。
• 可読性の向上: テーブル名と列名で参照するため、数式の意味が理解しやすくなります。
• 書式設定の維持: テーブルには自動的に書式が適用され、データの見やすさが向上します。

まとめ

XLOOKUP関数でデータ追加に柔軟に対応するためには、

• 列全体指定: 簡単な方法ですが、余計なデータが含まれないように注意が必要です。
• テーブルと構造化参照: より高度で推奨される方法です。可読性も高く、データ管理が容易になります。

データが頻繁に追加される表でXLOOKUPを使用する場合は、これらの方法をぜひ活用してみてください。

この解説で、XLOOKUP関数におけるデータ追加への対応について、より深く理解できたかと思います。この情報を活用し、Excelでのデータ分析をより効率的に行いましょう。