Excel XLOOKUP関数の使い方：5つの実践例で徹底解説 | ユースフル

2024 9/07

2024年9月7日

はじめに：Excel XLOOKUP関数の威力

Excelユーザーの皆さん、データ検索や参照で苦労していませんか？複雑なVLOOKUPやINDEX/MATCH関数を使って悪戦苦闘していませんか？そんなあなたに朗報です。ExcelのXLOOKUP関数を使えば、データ検索がこれまでよりもずっと簡単になります。

XLOOKUP関数は、従来のVLOOKUPやHLOOKUP、さらにはINDEX/MATCH関数の機能を1つにまとめた、まさにExcelのデータ検索における新しいスーパーヒーローと言えるでしょう。この記事では、XLOOKUP関数の基本から応用まで、5つの実践例を通じて詳しく解説していきます。

XLOOKUP関数を使いこなせば、以下のような悩みも解決できます：

複数の条件でデータを検索したい
検索結果が見つからない場合のエラー処理に困っている
大量のデータから効率的に情報を抽出したい
縦方向だけでなく横方向のデータも検索したい
完全一致だけでなく、部分一致や近似値での検索もしたい

それでは、XLOOKUP関数の魅力に迫っていきましょう。

XLOOKUP関数の基本

まずは、XLOOKUP関数の基本的な構文を見てみましょう。

=XLOOKUP(検索値, 検索範囲, 戻り値の範囲, [検索失敗時の値], [一致モード], [検索モード])

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

検索値：探したい値
検索範囲：検索値を探す範囲
戻り値の範囲：検索値が見つかった場合に返す値の範囲
検索失敗時の値：（オプション）検索値が見つからなかった場合に返す値
一致モード：（オプション）完全一致、部分一致、ワイルドカードなどの検索方法を指定
検索モード：（オプション）検索の方向や方法を指定

XLOOKUP関数の大きな特徴は、従来のVLOOKUP関数と比べて以下の点が優れていることです：

検索方向に制限がない（左から右だけでなく、右から左への検索も可能）
複数の条件での検索が容易
検索失敗時のエラー処理が簡単
近似一致や部分一致など、柔軟な検索オプションがある

これらの特徴を活かした具体的な使用例を見ていきましょう。

例1：エラー処理 – 検索結果が見つからない場合の対処法

最初の例では、XLOOKUP関数を使ってエラー処理を行う方法を見ていきます。特に、検索結果が見つからない場合にどのように対処するかを学びます。

以下のような従業員データがあるとします：

従業員名	年間給与	ボーナス率
田中太郎	5,000,000	10%
鈴木花子	4,500,000	8%
佐藤次郎	6,000,000
山田優子	5,500,000	12%

このデータを使って、従業員名からボーナス率を検索する関数を作成しましょう。

=XLOOKUP(A2, 従業員名の範囲, ボーナス率の範囲, 0, 0)

この関数の特徴は以下の通りです：

A2セルの従業員名を検索値として使用
検索失敗時の値として0を指定（ボーナスなしを意味する）
一致モードは0（完全一致）を指定

この関数を使用すると、以下のような結果が得られます：

田中太郎：10%
鈴木花子：8%
佐藤次郎：0%（データがないため、指定した検索失敗時の値が返される）
山田優子：12%

このように、XLOOKUP関数を使えば、データが見つからない場合でもエラーを表示せず、指定した値（この場合は0）を返すことができます。これにより、後続の計算でエラーが発生するのを防ぐことができます。

エラー処理の重要性

エラー処理は単なる見た目の問題ではありません。適切なエラー処理を行うことで、以下のようなメリットがあります：

データの整合性の維持：エラー値が後続の計算に影響を与えるのを防ぐ
ユーザビリティの向上：エンドユーザーにとって理解しやすい結果を提供
デバッグの容易さ：問題の特定と修正が容易になる

XLOOKUP関数のエラー処理機能を活用することで、より堅牢で信頼性の高いExcelシートを作成することができます。

例2：ワイルドカードを使用した部分一致検索

次は、XLOOKUP関数でワイルドカードを使用して部分一致検索を行う方法を見ていきます。これは、完全な情報がない場合や、特定のパターンに一致するデータを探す場合に非常に便利です。

例えば、従業員の姓だけを覚えていて、フルネームを検索したい場合を考えてみましょう。

=XLOOKUP("*" & B2, 従業員名の範囲, 従業員名の範囲, "見つかりません", 2)

この関数の特徴は以下の通りです：

“*” & B2：ワイルドカード（*）とB2セルの値（姓）を組み合わせて検索値とする
一致モードとして2を指定（ワイルドカードの使用を許可）

例えば、B2セルに「田中」と入力すると、「田中太郎」のようなフルネームが返されます。

ワイルドカード検索のテクニック

ワイルドカードを使用する際は、以下のポイントに注意しましょう：

*（アスタリスク）：0文字以上の任意の文字列に一致
?（クエスチョンマーク）：任意の1文字に一致
~（チルダ）：ワイルドカード文字そのものを検索したい場合に使用（例：~* は * を検索）

これらのワイルドカードを組み合わせることで、より柔軟な検索が可能になります。例えば：

“田中*”：「田中」で始まる全ての名前
“*子”：「子」で終わる全ての名前
“??太郎”：名字が2文字で、名前が「太郎」の人を検索

ワイルドカードを使用したXLOOKUP関数は、大量のデータから特定のパターンに一致する情報を素早く抽出するのに非常に有効です。特に、顧客データベースや製品カタログなどで、部分的な情報から完全な情報を取得する場合に威力を発揮します。

例3：横方向の検索（HLOOKUP の代替）

XLOOKUP関数の優れた点の一つは、縦方向だけでなく横方向の検索も簡単に行えることです。これは従来のHLOOKUP関数の代替として使用できます。

以下のような横方向のデータがあるとします：

項目	1月	2月	3月	4月
売上	1,000,000	1,200,000	1,100,000	1,300,000
経費	800,000	850,000	820,000	900,000
利益	200,000	350,000	280,000	400,000

この表から特定の月の売上を取得する関数を作成しましょう。

=XLOOKUP(E2, B1:E1, B2:E2, "データなし", 0)

この関数の特徴は以下の通りです：

E2セルの月名を検索値として使用
B1:E1（月の範囲）を検索範囲として指定
B2:E2（売上の範囲）を戻り値の範囲として指定

E2セルに「3月」と入力すると、この関数は3月の売上である1,100,000を返します。

横方向検索の利点

XLOOKUP関数を使用した横方向検索には、従来のHLOOKUP関数と比較して以下のような利点があります：

列番号を指定する必要がない：HLOOKUPでは戻り値の列番号を指定する必要がありましたが、XLOOKUPではその必要がありません。
右側のデータも簡単に参照可能：HLOOKUPは左から右への検索に限られていましたが、XLOOKUPは右側のデータも簡単に参照できます。
より直感的：検索範囲と戻り値の範囲を直接指定するため、関数の意図がわかりやすくなります。

この横方向検索の機能は、特に財務データや時系列データを扱う際に非常に有用です。例えば、月次レポートや年間予算の分析など、横方向に展開されたデータを効率的に操作することができます。

例4：二次元検索（行と列の組み合わせ）

XLOOKUP関数の強力な機能の一つに、二次元検索があります。これは、行と列の両方の条件を使ってデータを検索する場合に非常に便利です。従来はINDEX関数とMATCH関数を組み合わせて実現していましたが、XLOOKUPを使えばより簡単に実装できます。

以下のような表があるとします：

	基本給	ボーナス	総支給額
田中太郎	300,000	50,000	350,000
鈴木花子	280,000	45,000	325,000
佐藤次郎	320,000	55,000	375,000

この表から特定の従業員の特定の項目（基本給、ボーナス、総支給額）を取得する関数を作成しましょう。

=XLOOKUP(G2, A2:A4, XLOOKUP(H2, B1:D1, B2:D4, "項目なし", 0), "従業員なし", 0)

この関数の特徴は以下の通りです：

外側のXLOOKUP：G2セルの従業員名を検索
内側のXLOOKUP：H2セルの項目名（基本給、ボーナス、総支給額）を検索
二つのXLOOKUPを組み合わせることで、行と列の両方の条件を満たすセルの値を返す

例えば、G2に「田中太郎」、H2に「ボーナス」と入力すると、この関数は50,000を返します。

二次元検索の活用シーン

二次元検索は以下のようなシーンで特に有用です：

複数の製品カテゴリーと期間にまたがる売上データの分析
従業員の勤務表から特定の日付と従業員の勤務時間を抽出
学生の成績表から特定の学生の特定の科目の点数を取得

この機能を使いこなすことで、複雑なデータ構造から必要な情報を素早く抽出することができ、データ分析の効率が大幅に向上します。

例5：近似一致を使用した検索

最後の例では、XLOOKUP関数の近似一致機能を使用する方法を見ていきます。この機能は、完全に一致する値が見つからない場合に、最も近い値を返すのに役立ちます。

例えば、以下のような給与テーブルがあるとします：

給与範囲	ボーナス率
300,000以下	5%
300,001-500,000	7%
500,001-800,000	10%
800,001以上	15%

この表を使って、特定の給与額に対するボーナス率を求める関数を作成しましょう。

=XLOOKUP(J2, A2:A5, B2:B5, "範囲外", -1)

この関数の特徴は以下の通りです：

J2セルの給与額を検索値として使用
一致モードとして-1を指定（完全一致または次に小さい項目）

例えば、J2に450,000と入力すると、この関数は7%を返します。これは450,000が300,001-500,000の範囲に入るためです。

近似一致の活用シーン

近似一致機能は以下のようなシーンで特に有効です：

税率表や保険料率表からの適切なレートの抽出
商品の価格帯に応じた割引率の決定
学生の成績に応じた評価（A, B, C等）の割り当て

この機能を使うことで、段階的な値を持つデータから適切な値を自動的に選択することができ、複雑な条件分岐を簡略化できます。

XLOOKUP関数を使いこなすためのヒントとコツ

XLOOKUP関数をより効果的に使用するために、以下のヒントとコツを覚えておきましょう：

検索範囲の固定： 検索範囲や戻り値の範囲を指定する際に、絶対参照（$記号）を使用することで、関数をコピーしても正しく機能するようになります。
エラー処理の活用： 「検索失敗時の値」引数を適切に設定することで、エラーを回避し、より読みやすいシートを作成できます。
複数のXLOOKUPの組み合わせ： 複雑な検索条件がある場合は、複数のXLOOKUP関数を組み合わせることで、より柔軟な検索が可能になります。
大文字小文字の区別： 必要に応じて、EXACT関数と組み合わせることで、大文字小文字を区別した検索を行うことができます。
パフォーマンスの最適化： 大量のデータを扱う場合は、検索範囲を必要最小限に絞ることで、計算速度を向上させることができます。

XLOOKUP vs その他の検索関数

XLOOKUP関数は非常に強力ですが、状況によっては他の関数が適している場合もあります。以下に、XLOOKUP関数と他の主要な検索関数の比較を示します：

機能	XLOOKUP	VLOOKUP	HLOOKUP	INDEX/MATCH
縦方向検索	○	○	×	○
横方向検索	○	×	○	○
双方向検索	○	×	×	○
逆順検索	○	×	×	○
複数の戻り値	○	×	×	○
近似一致	○	○	○	○
ワイルドカード	○	○	○	○

XLOOKUP関数は多くの場面で最適な選択肢ですが、以下のような場合は他の関数を検討する価値があります：

古いバージョンのExcelとの互換性が必要な場合はVLOOKUPやHLOOKUPを使用
非常に複雑な条件や多次元の検索が必要な場合はINDEX/MATCHの組み合わせを検討
大量のデータを扱う際にパフォーマンスの問題が発生する場合は、他の最適化された関数やPower Queryの使用を検討

まとめ：XLOOKUP関数でExcelスキルを次のレベルへ

XLOOKUP関数は、Excelのデータ検索と分析を大きく改善する強力なツールです。この関数を使いこなすことで、以下のようなメリットが得られます：

データ検索作業の効率化と簡素化
複雑な検索条件にも柔軟に対応
エラーの少ない、より信頼性の高いスプレッドシートの作成
データ分析の質と速度の向上

本記事で紹介した5つの実践例は、XLOOKUP関数の可能性のほんの一部に過ぎません。実際の業務でXLOOKUP関数を活用することで、さらに多くの使用方法を発見できるでしょう。

Excelスキルを向上させたい方、データ分析の効率を上げたい方には、XLOOKUP関数の習得をぜひおすすめします。この関数を使いこなすことで、あなたのExcel作業は新たな次元に到達するはずです。

さらにExcelスキルを磨きたい方は、ユースフルのExcelPro講座の無料トライアルをぜひお試しください。XLOOKUP関数を含む高度なExcel技術を、実践的なレッスンを通じて学ぶことができます。

気に入ったらシェア！

URLをコピーしました！