XLOOKUP関数の衝撃 DL可
えくせるサイトウ
目次
はじめに
本記事では、Excel 2019に登場の新関数XLOOKUPに関して、取り上げます。
Excel 2019以前のバージョンでは使用できない点はご留意ください。
「XLOOKUP関数」は、
「VLOOKUP関数」「HLOOKUP関数」の上位互換と言って、差し支えない。
「VLOOKUP関数」「HLOOKUP関数」の課題点が解決されているんだ。
そもそも、「VLOOKUP関数」「HLOOKUP関数」が存在するにも関わらず、
「XLOOKUP関数」という新関数が登場したということは、今まで出来なかった事や課題が解決されている可能性が高いです。
是非、新しい関数に取り組んでいきましょう。
アルベルト・アインシュタインも、『知識は現状を変える力を持ちます。いつも新しいものにチャレンジし、可能性を広げることが重要です』と言っています。
本記事では、XLOOKUP関数の衝撃をお伝えできればと思います。
具体的な話は、今後の記事で取り上げます。
XLOOKUP関数の可能性は無限大のため、とても1記事で語りつくせないためです。
皆様を、XLOOKUP関数の旅にいざないます。
XLOOKUP関数の使い方
XLOOKUP関数だけでなく、姉妹関数とも言えるVLOOKUP関数とHLOOKUP関数と合わせて説明します。
VLOOKUP関数とHLOOKUP関数の詳細は、
『【VLOOKUP関数】使い方と解説]』『【HLOOKUP関数】使い方と解説を別途ご確認ください。
関連記事の一覧
- XLOOKUP
- 垂直方向、水平方向の両方(X)の調べ(Lookup)、一致する値を返します。
- HLOOKUP
- 「Horizontal Lookup」の略語で、水平(Horizontal)方向にテーブル内のデータを調べ(Lookup)、一致する値を返します。
最初の行にキーが含まれ、それに対応する列にある指定された行からデータを返します。
- 「Horizontal Lookup」の略語で、水平(Horizontal)方向にテーブル内のデータを調べ(Lookup)、一致する値を返します。
- VLOOKUP
- 「Vertical Lookup」の略語で、垂直(Vertical )方向にテーブル内のデータを調べ(Lookup)、一致する値を返します。
最初の列にキーが含まれ、それに対応する行にある指定された列からデータを返します。
- 「Vertical Lookup」の略語で、垂直(Vertical )方向にテーブル内のデータを調べ(Lookup)、一致する値を返します。
VLOOKUP関数やHLOOKUP関数は、垂直(Vertical )なのか、水平(Horizontal)でしたが、
XLOOKUP関数は、垂直と水平の両方です。LOOKUPは調べるという意味です。
Xと名付けたのは、垂直と水平≒無限（Ｘ）と考えたのかもね。
無限（Ｘ）に調べる（LOOKUP）だね
つまり、縦横無尽だね。
簡略化すると、次の通りです。
- 水平(Horizontal)に調べる(LOOKUP)であれば、HLOOKUP関数
- 垂直(Vertical )に調べる(LOOKUP)であれば、VLOOKUP関数
- 垂直と水平の両方≒無限Xに調べる(LOOKUP)であれば、XLOOKUP関数
XLOOKUP関数の文法
XLOOKUP関数の文法を解説します。冒頭でお伝えしたように、具体的な使用法は、別記事で紹介します。
ただ、VLOOKUP関数やHLOOKUP関数の馴染みのある方は、本記事だけでも十分に理解してしまうのではないでしょうか。
型
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])
検索範囲から、検索値を探し、指定した戻り範囲に指定した値を返します。
また、省略可能な引数として、[見つからない場合][一致モード][検索モード]の3つがあります。
データ分析ドットコムでは何度もお伝えしていますが、
Excel関数にはぞれぞれ型があり、型通りに物事が進んでいきます。
そのため、さほど難しくないのです。
型の重要性と型の調べ方は、次の記事で解説しています。
引数
引数が6つもあるけど、思考停止しないでね。
むしろ、それだけ応用が効くということ。
メリットなんだよ。
なお、下記は、Microsoft社公式の『XLOOKUP 関数 – Microsoft サポート』よりデータ分析ドットコムが改変したものです。
- 検索値：どのデータを検索するかを指定
- 検索範囲：検索する範囲または配列を指定
- 戻り範囲：検索範囲内から返される値の範囲
- 「VLOOKUP関数」「HLOOKUP関数」には無い機能
- [見つからない場合]：検索値が見つからない場合に表示される値を指定します。省略すると、「#N/A」が返されます。
- 「VLOOKUP関数」「HLOOKUP関数」には無い機能
- [一致モード]：一致の種類を指定
- 「VLOOKUP関数」「HLOOKUP関数」には無い機能
1-と1は近似一致の感じがしますが、Microsoft社の公式は完全一致となっています。
| 設定値 | 説明 |
|---|---|
| 0、または、省略 | 完全一致。見つからない場合は「#N/A」と表示されます。(既 |
| -1 | 完全一致。見つからない場合は次に小さい項目が表示されます。 |
| 1 | 完全一致。見つからない場合は次に大きい項目が表示されます。 |
| 2 | ワイルドカードとの一致 (*、?、~（チルダ）など) |
なお、覚える必要はありません。
XLOOKUP関数の入力していくと、ヒントが表示され、ぞれぞれ解説も記載されます。
- [検索モード]：使用する検索モードを指定
- 「VLOOKUP関数」「HLOOKUP関数」には無い機能
1-と1は近似一致の感じがしますが、Microsoft社の公式は完全一致となっています。
| 検索モードのオプション | 説明 |
|---|---|
| 1、または、省略 | 先頭の項目から検索を実行します。これが既定の設定です。 |
| -1 | 末尾の項目から逆方向に検索を実行します。 |
| 2 | 昇順で並べ替えられた検索範囲を使用してバイナリ検索^1を実行します。並べ替えられていない場合、無効な結果が返されます。 |
| -2 | 降順で並べ替えられた検索範囲を使用してバイナリ検索^1を実行します。並べ替えられていない場合、無効な結果が返されます。 |
^1 バイナリ検索： 検索対象のデータが昇順または降順で並べ替えられていることを前提に、検索範囲を半分ずつ絞り込みながら効率的に検索を行うアルゴリズムです。バイナリ検索を使用することで、大量のデータを高速に検索することができます。ただし、検索対象のデータが並べ替えられていない場合は正しい結果が得られません。
要は、バイナリ検索とは、「高速に検索できるよ」ってこと。
バイナリ検索を使用すると、重いデータでも検索できるんだ。
XLOOKUP関数で重いデータを扱うと、フリーズするケースがあるからだよ。
なお、覚える必要はありません。
XLOOKUP関数の入力していくと、ヒントが表示され、ぞれぞれ解説も記載されます。
「VLOOKUP関数」「HLOOKUP関数」に対する「XLOOKUP関数」の優位性
「VLOOKUP関数」「HLOOKUP関数」に対する「XLOOKUP関数」の優位性をざっと記載します。
記事公開日付時点のもので、まだまだあるかもしれません。
各箇条書きの具体的な紹介は今後に実施したいと考えています。
- 検索条件の位置を自由に設定できる
- VLOOKUP関数やHLOOKUP関数のように、検索キーが必ず左端や上端になければならないという制約がありません
そのため、表の任意の位置から検索を行うことができる
- VLOOKUP関数やHLOOKUP関数のように、検索キーが必ず左端や上端になければならないという制約がありません
- 複数の検索値、複数の検索範囲、複数の戻り範囲を指定して検索ができる
- 関数の組み合わせが不要になった
- エラー時の処理のため、VLOOKUP関数やHLOOKUP関数とIFERROR関数を併用する必要がなくなった
- INDEX関数とMATCH関数を併用する必要がなくなった
- 行列番号取得のため、VLOOKUP関数やHLOOKUP関数では、MATCH関数を併用する必要がなくなった
特に、4番がすごいよね。
今まで、様々な関数を併用していた処理が、XLOOKUP関数の一つで実現できるになったんだ
ただし、依然、複数条件に該当したケースには、対応できておらず、一番最初の値を返す仕様のままです。
解答付きサンプルデータ
上記の優位性が分かるExcelファイルです。
詳しい解説は、別途記事を公開しますので、しらばくお待ちください。
最後に
本記事では、次の3点に関して取り上げました。
今後、それぞれに関して、具体的にアプローチしていきますので、よりイメージが掴みやすくなると思います。
- XLOOKUP関数とは何か
- XLOOKUP関数の説明
- VLOOKUP関数とHLOOKUP関数と比較した優勢性
XLOOKUP関数は、垂直方向、水平方向の両方(X)の調べ(Lookup)、一致する値を返す関数でした。
従来のVLOOKUP関数やHLOOKUP関数では実現できなかった機能が多くなり、複雑な検索条件を持つ表データの処理に便利です。
従来は、VLOOKUP関数やHLOOKUP関数とMATCH関数、IFERROR関数等を併用する必要がありました。
この記事をきっかけに、自分がどのような業務に使うか考えながら、関数の使い方をマスターしていきましょう。
参考記事
関連記事
【VLOOKUP関数】使い方と解説
本記事では、Excel関数の中でも代表的な検索/行列関数のひとつであるVLOOKUP関数について解説します。 VLOOKUP関数は、垂直方向にテーブル内のデータを検索し、指定した…
データ分析ドットコム 
【HLOOKUP関数】使い方と解説
本記事では、Excel関数の中でも代表的な検索/行列関数のひとつであるHLOOKUP関数について解説します。 HLOOKUP関数は、水平方向にテーブル内のデータを検索し、指定した…
Excel関数の原理原則系の記事
本記事の読みやすさをさらに向上させるために、Excel関数に関する原理原則についてまとめた記事を紹介します。
数式の原則｜Excel関数を学ぶ前に
Excelほど、馴染みがあり、広く浸透しているツールもないでしょう。 さらに、Excel関数はExcel作業を便利するパッケージであり、習得することで、大きな進歩につながりま…
数式の原則｜Excel関数の種類は大まかに知っておこう
冷静に、スムーズに仕事ができるため、Excel関数の種類を大まかに知っておくことが大切です。 Excel 2019では、Excel関数が全486関数もありますが、Excel機能を上手に活用…
数式の原則｜Excel関数にはそれぞれ構文がある
Excel関数には構文があります。それが、引数と戻り値です。構文に沿えばいいだけです。言わば、数学の公式のようなものです。理論を知らなくても、公式に当てはめれば答え…
数式の原則｜Excel関数の引数とは
Excelの引数は、料理を完成させるための材料のようなものです。しかも、材料に種類があります。その種類を理解すると、Excel関数を上手く使いこなせるようになります。
数式の原則｜Excelの検索ダイアログから、関数を探す
Excelの検索ダイアログに使えば、目的の関数を発見しやすくなります。WEB検索する前に、Excel関数の検索ダイアログを使ってみましょう。
