XLOOKUP関数入門：複雑なデータも簡単検索

漫才で覚える Excel XLOOKUP関数のポイント

「どうもーどうも みるくブラザーズですー」

「お願いしますー　ありがとうございますー」

げんじ「あー ありがとうございますー　ねっ　今 スプレッドシートの隠しコマンドをいただきましたけどもね」

ボケ＆げんじ「ありがとうございますー」

げんじ「こんなん　なんぼあっても良いですからね」

ゆめた「いきなりですけどね　うちのオカンがね　好きな 関数 があるらしいんやけど」

げんじ「そーなんや」

ゆめた「その名前をちょっと忘れたらしくてね」

げんじ「好きな関数を忘れて、どうなってんの。なんか特徴言うてなかった？」

ゆめた「特徴は “特定の値を検索して対応する値を返す” って言うねんな」

げんじ「おー　XLOOKUPやないかい　その特徴はもう完全に XLOOKUP やがな」

ゆめた「 XLOOKUP なぁ。いや俺も XLOOKUP と思うてんけどな」

げんじ「いやそうやろ？」

ゆめた「オカンが言うには　 “ピアノの鍵盤で曲を弾く” って言うねんな」

げんじ「あー　ほな XLOOKUP と違うかぁ　 XLOOKUP でピアノの鍵盤を弾くって、どういうことや？ 関数で音楽なんか出せへんやろ！」

ゆめた「そやねん」

げんじ「 XLOOKUP でピアノの鍵盤を弾くって、マジで無理ゲーやん。データ検索と全然関係ないし。」

ゆめた「そやねんな」

げんじ「あれほなもう一度詳しく教えてくれる？」

ゆめた「 “複数の条件で検索できる” らしいねん」

げんじ「 XLOOKUP やないかい　”特定の値を検索して対応する値を返す、それに加えて複数の条件で検索できる”　これぞXLOOKUPの醍醐味やんけ！」

ゆめた「まあねー」

げんじ「 “複数の条件で検索できる” って、まさにXLOOKUPの特徴やん。XLOOKUPでデータをサクサク見つけ出すのに便利やしな」

ゆめた「分からへんねんでも」

げんじ「何が分からへんのこれで」

ゆめた「俺も XLOOKUP と思うてんけどな」

げんじ「そうやろ」

ゆめた「オカンが言うには　 “時間を逆戻りできる” って言うねんな」

げんじ「ほな XLOOKUP ちゃうやないかい　XLOOKUPで時間逆戻り？ そんな機能あったら僕らタイムトラベラーや！」

ゆめた「そやねんそやねん」

げんじ「 XLOOKUP ちゃうがな　ほな　もうちょっとなんか言ってなかった？」

ゆめた「 “見つからない時に返す値を指定できる” らしいねん」

げんじ「 XLOOKUP やないかい　”特定の値を検索して対応する値を返す、それに加えて見つからない時に返す値を指定できる”　これもXLOOKUPの素晴らしい機能やん！」

ゆめた「分からへんねんだから」

げんじ「なんで分からへんのこれで」

ゆめた「俺も XLOOKUP と思うてんけどな」

げんじ「そうやろ」

ゆめた「オカンが言うには　 XLOOKUP ではないって言うねん」

げんじ「ほな XLOOKUP ちゃうやないかい　オカンが XLOOKUP ではないと言うんやから　 XLOOKUP ちゃうがな」

ゆめた「そやねん」

げんじ「先ゆえよ　 “関数を使って何かを検索する時のドキドキ感” について話してた時どう思っててん」

ゆめた「んでオトンが言うにはな」

げんじ「オトン？」

ゆめた「 VLOOKUP ちゃうか？って言うねん」

げんじ「いや絶対ちゃうやろ　もうええわー」

はじめに

• XLOOKUP関数（Microsoft 365使用可能）はExcelで追加された新しい検索関数です。VLOOKUPやHLOOKUPよりも柔軟で強力です。
• この関数を使いこなすことで、データ検索や参照作業がより簡単かつ効率的になります。
• より少ない引数で直感的な操作が可能になり、エラーの発生率を減らすことができます。

関数の概要と一般的な使い方の例

XLOOKUP関数は、検索値を指定し、その検索値が含まれる範囲を検索して、対応する値を別の範囲から返す関数です。VLOOKUPやHLOOKUPと比べて、検索方向が自由であり、戻り値の範囲を自由に指定できます。

=XLOOKUP(検索値, 検索範囲, 戻り値範囲)

例えば、以下のように商品IDを検索して、対応する商品名を返す簡単な使用例があります。

• 検索値：商品ID “002”
• 検索範囲：A2:A5（商品IDのリスト）
• 戻り値範囲：B2:B5（商品名のリスト）

この場合、XLOOKUP("002", A2:A5, B2:B5) の式を使うと、商品ID “002” に対応する商品名が返されます。

なぜこの関数を学ぶのか？

• データベースから特定の情報を素早く見つけ出す必要がある場合に役立ちます。
• 従来のVLOOKUPやHLOOKUPよりも柔軟で使いやすいため、様々なデータ検索シーンでの活用が期待できます。
• 複数の条件でデータを検索する際の複雑さを解消し、効率的なデータ管理を実現できます。

一般的な書き方のサンプル

1. 単純な検索
   • 検索値： “001”
   • 検索範囲： A2:A5
   • 戻り値範囲： B2:B5
   • サンプルコード： =XLOOKUP("001", A2:A5, B2:B5)
   • 実行結果： “商品A”
2. 見つからない値の扱い
   • 検索値： “004”
   • 検索範囲： A2:A5
   • 戻り値範囲： B2:B5
   • 第4引数（見つからない場合の値）： “未登録”
   • サンプルコード： =XLOOKUP("004", A2:A5, B2:B5, "未登録")
   • 実行結果： “未登録”
3. 範囲外の値を検索
   • 検索値： “005”
   • 検索範囲： A2:A5
   • 戻り値範囲： B2:B5
   • サンプルコード： =XLOOKUP("005", A2:A5, B2:B5, "データなし")
   • 実行結果： “データなし”

=XLOOKUP("003", A2:A5, C2:C5, "製品が見つかりません")

関数の基本構文

XLOOKUP関数の基本構文は非常にシンプルで、主に3つの引数を使用します。これらの引数を理解することで、関数の応用範囲が広がります。

1. 検索値：これは関数が検索範囲内で見つけようとする値です。テキスト、数値、または日付など、任意の形式のデータが可能です。
2. 検索範囲：この範囲内から検索値を探します。検索値と一致するデータをこの範囲から見つけ出すことが目的です。
3. 戻り値範囲：検索値に対応するデータをこの範囲から返します。検索範囲内で検索値が見つかった場合、その位置に基づいて戻り値範囲から対応するデータを取得します。

関数の定義と主な引数

XLOOKUP関数では、上記の基本引数の他に、追加の引数を指定することで、より高度な検索が可能になります。

1. 見つからない場合の値：検索値が検索範囲内で見つからない場合に返す値を指定します。この引数はオプションです。
2. 検索方向：検索を行う方向を指定します。既定では最初に見つかった値を返しますが、最後に見つかった値を返すようにすることも可能です。

サンプルコードとして、商品IDを検索して対応する価格を返す例を見てみましょう。

• 検索値： “003”
• 検索範囲： A2:A5（商品IDのリスト）
• 戻り値範囲： C2:C5（商品価格のリスト）
• 見つからない場合の値： “価格不明”

=XLOOKUP("003", A2:A5, C2:C5, "価格不明")

このコードは、商品ID “003” の価格を返します。もし “003” が検索範囲内になければ、”価格不明” という結果を返します。

具体的な使用例

XLOOKUP関数を用いた具体的な使用例を通して、その便利さと柔軟性を紹介します。

1. 異なるシートからのデータ検索
   • あるシートには従業員のIDと名前が、別のシートにはそのIDに基づく給与が記載されています。XLOOKUP関数を使って、特定の従業員の給与を簡単に見つけ出すことができます。これにより、データが分散していても迅速に情報を取得できます。
2. 複数の条件を満たすデータの検索
   • 製品リストから特定のカテゴリーに属し、かつ特定の価格範囲内の製品を検索する場合、XLOOKUP関数を連続して使用することで、これらの複雑な条件を満たす製品を簡単に見つけることが可能です。
3. データの置換
   • 顧客リストにおいて、特定の顧客の情報（例えば、住所）を更新する必要がある場合、XLOOKUP関数を使用して対象の顧客を簡単に見つけ出し、その情報を新しいデータで置換することができます。これにより、大規模なデータベース内での情報更新作業が大幅に効率化されます。

これらの使用例から分かるように、XLOOKUP関数はデータ検索と加工において非常に強力なツールであり、多様なシナリオでの活用が可能です。

初歩的なテクニック

XLOOKUP関数を使う際の簡単な使い方やコツ、そして他の基本関数との組み合わせ方を紹介します。

• 簡単な使い方: XLOOKUP関数は、直接値を検索するだけでなく、条件に一致する最初の項目を返すためにも使用できます。例えば、特定の条件に一致する最初の商品名を見つけるために使用できます。
• コツ: XLOOKUP関数を使用する際のコツは、検索範囲と戻り値範囲が正しく対応していることを確認することです。また、検索範囲内に検索値が複数存在する場合、XLOOKUP関数は最初に見つかった値を返します。これを理解しておくと、より効率的に関数を使用できます。
• 他の基本関数との組み合わせ:
  1. IF関数との組み合わせ: XLOOKUP関数の結果に基づいて特定のアクションを実行するために、IF関数と組み合わせることができます。例えば、特定の条件を満たす値が見つかった場合に特定のメッセージを表示するなどです。
  2. CONCATENATE関数（または&演算子）との組み合わせ: 複数の列からデータを引き出して結合する際に便利です。例えば、顧客の名前と姓を別々の列から引き出してフルネームを作成する際に使用できます。
  3. SUMIF関数との組み合わせ: XLOOKUP関数で特定の条件に一致するデータを見つけた後、そのデータに基づいて合計値を計算する際にSUMIF関数と組み合わせることができます。
• 相性の良い他の関数:
  • IF
  • CONCATENATE (または&演算子)
  • SUMIF
  • INDEX
  • MATCH

これらの初歩的なテクニックや組み合わせにより、XLOOKUP関数の使用範囲を広げることができ、日々の作業をより効率的に進めることが可能になります。

高等的なテクニック

XLOOKUP関数を用いた高度な使用例と、他の関数との組み合わせアイデアを紹介します。

1. 複数条件での検索
   • XLOOKUP関数自体は一つの条件での検索に特化していますが、複数のXLOOKUP関数を組み合わせることで、複数条件に基づく検索を実行することが可能です。たとえば、製品名とカラーの2つの条件で在庫数を検索する場合、一つ目のXLOOKUPで製品名に基づいて範囲を絞り、二つ目のXLOOKUPでその範囲内からカラーに一致する在庫数を検索します。
2. 動的な範囲参照
   • XLOOKUP関数を使用して動的に範囲を指定することも可能です。これにより、データの追加や削除があった場合でも、手動で範囲を更新する必要がなくなります。例えば、XLOOKUP関数で検索範囲を指定する際に、INDIRECT関数を使用して範囲を動的にすることができます。
3. 配列を返すXLOOKUP
   • XLOOKUP関数は単一の値だけでなく、配列を返すこともできます。これにより、一度に複数の関連するデータを取得することが可能になります。例えば、顧客IDを検索して、その顧客に関連する名前、住所、電話番号を一度に取得することができます。
4. 他の高度な関数との組み合わせアイデア
   • FILTER関数との組み合わせ: 特定の条件を満たすデータのみを抽出したい場合、XLOOKUP関数で検索した結果をFILTER関数に渡すことで、条件に一致するデータセットを簡単に取得することができます。
   • SORT関数との組み合わせ: XLOOKUP関数で取得したデータセットをSORT関数を用いて並び替えることで、分析やレポート作成の効率を大幅に向上させることができます。
   • UNIQUE関数との組み合わせ: XLOOKUP関数で取得したデータから重複を排除したい場合、UNIQUE関数と組み合わせることで簡単にユニークなデータセットを作成することができます。

これらの高等的なテクニックを駆使することで、XLOOKUP関数の潜在的なパワーを最大限に引き出し、より複雑なデータ処理や分析を効率的に行うことが可能になります。

便利なシーンでの事例

XLOOKUP関数はビジネスや学業で多岐にわたるシナリオで役立ちます。具体的なケーススタディを通じて、その便利さを探りましょう。

1. 売上データの分析
   • 企業の財務部門では、多数の製品にわたる売上データを分析する必要があります。XLOOKUP関数を使用して、特定の製品IDに基づいて売上データを迅速に抽出し、その製品のパフォーマンスを評価することができます。これにより、戦略的な意思決定を支援し、効率的な在庫管理を実現することが可能になります。
2. 学術研究でのデータ処理
   • 学術研究では、大量のデータを処理し分析する必要があります。XLOOKUP関数を用いることで、研究に必要な特定のデータポイントを簡単に見つけ出し、研究結果の精度を高めることができます。例えば、特定の条件を満たすサンプルデータを迅速に抽出し、分析に用いることが可能です。
3. 在庫管理
   • 小売業では、効率的な在庫管理が業務の成功に直結します。XLOOKUP関数を使用して、特定の商品コードに基づく在庫レベルを即座に確認することができます。これにより、過剰在庫や品切れのリスクを最小限に抑え、顧客満足度を高めることができます。

これらの事例は、XLOOKUP関数がいかにビジネスや学術研究におけるタイムセーブや効率向上に貢献できるかを示しています。データの迅速な検索と分析を通じて、より賢明な意思決定と効率的な業務遂行が可能になります。

注意点

XLOOKUP関数を使用する際に注意すべき点、一般的なミスやエラー、そして類似の関数や代替の関数との違いを解説します。

1. 注意点
   • 検索範囲と戻り値範囲のサイズが一致している必要があります。不一致がある場合、予期せぬ結果やエラーが発生する可能性があります。
   • XLOOKUPはExcelの新しいバージョンでのみ使用可能です。旧バージョンのExcelを使用している場合、この関数は利用できません。
2. よくあるミス
   • 検索値が検索範囲内に存在しない場合のデフォルトの動作は、#N/Aエラーを返すことです。見つからない場合の値を指定しないと、エラーに直面することがあります。
   • 検索範囲が正しくない順序である場合（例えば、昇順ではなく降順になっている場合）、XLOOKUP関数は正確な結果を返さない可能性があります。
3. エラーが出る例
   • 引数が不完全または不正確な場合、XLOOKUP関数はエラーを返します。例えば、検索範囲または戻り値範囲が指定されていない場合です。
4. 類似の関数や代替の関数との違い
   • VLOOKUP/HLOOKUP: これらの関数もデータ検索に使用されますが、XLOOKUPに比べると柔軟性が劣ります。特に、VLOOKUPは縦方向、HLOOKUPは横方向の検索のみに対応しており、検索値が最初の列や行になければならないという制約があります。
   • INDEX/MATCH: INDEXとMATCHの組み合わせは、XLOOKUPと同様の機能を提供しますが、2つの関数を組み合わせて使用する必要があります。XLOOKUPはこれらを1つの関数で実現できるため、よりシンプルで直感的です。

XLOOKUP関数を使用する際には、これらの注意点やミスを避けるために、関数の引数と動作を正確に理解し、適切に適用することが重要です。

使いこなし例

悩める登場人物例: 田中さんは、中規模の製造業で働く営業マネージャーです。彼は毎月、製品ごとの売上データを分析して、営業チームのパフォーマンスを評価し、将来の販売戦略を計画する責任があります。しかし、膨大なデータの中から特定の製品の情報を見つけ出し、それに基づいて分析を行うのに多くの時間を費やしていました。

具体的な悩み: 田中さんの主な悩みは、製品IDを手動で検索して対応する売上データを見つけるのに時間がかかりすぎることです。この作業は繁雑であり、時には誤ったデータを分析してしまうリスクもありました。また、新しい製品が追加されるたびに、このプロセスを繰り返さなければならず、効率的な作業が困難でした。

使いこなせることによるビフォーアフターのストーリー:

• ビフォー: 田中さんは、ExcelのVLOOKUP関数を使用して製品IDに基づいて売上データを検索していましたが、この方法は時間がかかり、時々間違ったデータを分析してしまうことがありました。また、検索対象の列を変更する場合、関数の書き換えが必要でした。
• アフター: XLOOKUP関数の使いこなしを学んだ田中さんは、検索が格段に簡単かつ迅速になりました。複数の条件で柔軟にデータを検索できるようになり、間違いのリスクを大幅に減少させることができました。さらに、XLOOKUP関数は検索対象の列が変わっても、関数を書き換える必要がなく、新しい製品データが追加されても瞬時に対応できるようになりました。
• 結果: 田中さんは、XLOOKUP関数を使いこなすことで、毎月の売上データ分析作業の時間を大幅に短縮しました。これにより、より多くの時間を営業チームの指導や販売戦略の策定に費やすことができるようになり、チームの売上実績にも肯定的な影響を与えました。田中さんは、XLOOKUP関数のおかげで作業効率が向上し、仕事の満足度も高まったと感じています。