Introduction
Ver. 2021 365
Excelで他の表からデータを引っ張ってくる際に使用するXLOOKUP関数の使い方です。
もう少し細かく説明すると、元の表の指定したデータと、他の表のデータを一致させて関連する行データを引っ張ってきます。
言葉で説明するよりも下の画像を見ていただいた方が分かり易いかもしれません。
ここで説明するXLOOKUP関数は、Office365やバージョン2021から使用できる関数で、VLOOKUP関数やHLOOKUP関数がパワーアップしたものです。
バージョンアップということで使いやすく便利になっているのですが、引数（指定する場所）が多くなったので初めて使用する方は混乱するはずです。
XLOOKUP関数・・・他の表から対応するデータを探し出し、一致したデータを求める関数
XLOOKUP（ 検索値 , 検索範囲 , 戻り範囲 , 見つからない場合 , 一致モード , 検索モード ）
- 検索値・・・検索する値（一致させたい場所のセル参照や値、文字列）を指定
- 検索範囲・・・検索する（一致させる）相手のデータ全体を指定
- 戻り範囲・・・取り出したい値の範囲を指定
- 見つからない場合・・・一致するデータが見つからなった時に表示したい文字列を入力［省略可］
- 一致モード・・・一致した際の種類を指定［省略可］
- 「0」 一致データが見つからない場合、「#N/A」エラー、もしくは［みつからない場合］で指定した文字列が表示される（既定の設定）
- 「-1」 一致データが見つからない場合、次に小さい値を表示
- 「1」 一致データが見つからない場合、次に大きい値を表示
- 「2」 * 、? 、˜ をワイルドカードとして使用できる
- 検索モード・・・検索の種類を指定［省略可］
- 「1」 データの先頭から検索する（既定の設定）
- 「-1」 データの後ろから検索する
- 「2」 データの先頭から高速な検索を実行する（昇順で並べ替えが行われていないデータの場合はエラーが表示される）
- 「-2」 データの後ろから高速な検索を実行する（降順で並べ替えが行われていないデータの場合はエラーが表示される）
な、長い・・・。
パワーアップしたぶん指定できる事が多くなっているので、詳細説明を入れるとここまで長くなってしまいます。
しかし、ほとんどの場合、上の３つだけを考えて、後は無視しても大丈夫。
そうなると下のようにスッキリします。
XLOOKUP関数・・・他の表から対応するデータを探し出し、一致したデータを求める関数
XLOOKUP（ 検索値 , 検索範囲 , 戻り範囲 , 見つからない場合 , 一致モード , 検索モード ）
- 検索値・・・検索する値（一致させたい場所のセル参照や値、文字列）を指定
- 検索範囲・・・検索する（一致させる）相手のデータ全体を指定
- 戻り範囲・・・取り出したい値の範囲を指定
では始めましょう。
とにかく便利な関数だにゃ
XLOOKUP関数の使い方
下の図をご覧ください。
今回は左の表の「評価」ABCと、右の表の「区分」ABCを一致させます。黄色く塗った場所です。
そして右の表「出席」のコメントを「備考」へ表示してみます。
まず、表示させたいセルを選択して［関数の挿入］ボタンをクリック。
表示された［関数の挿入］画面では、［関数の分類］を［検索/行列］か［すべて表示］に切り替えて「XLOOKUP」を選んで［OK］をクリック。
［関数の引数］画面が表示されます。
入力する箇所が6つもあるため、圧倒されてしまいますね・・・。
まず、基本的な使い方をマスターするということで、上の3つの場所だけを説明していきます。
この3つの場所は必ず指定する必要があります。
XLOOKUP関数・・・他の表から対応するデータを探し出し、一致したデータを求める関数
XLOOKUP（ 検索値 , 検索範囲 , 戻り範囲 , 見つからない場合 , 一致モード , 検索モード ）
- 検索値・・・検索する値（一致させたい場所のセル参照や値、文字列）を指定
- 検索範囲・・・検索する（一致させる）相手のデータ全体を指定
- 戻り範囲・・・取り出したい値の範囲を指定
最初の［検索値］の場所は、元の表の一致させたいデータの一つのセルを指定します。
今回は「ABC」のデータを一致させたいので「D3」をクリックして指定します。
次の［検索範囲］は今指定したデータと一致させたい相手のデータ範囲を指定します。
今回は「ABC」を一致させるので「G3:G6」を範囲指定します。
表全体のデータを範囲指定するのを忘れずに。
また、範囲指定した後に【F4】キーを押して絶対参照（セル番地に$記号を付ける）にするのを忘れないようにしましょう。
後で入力した式を下にコピーしますが、この範囲は固定しておく必要があります。
※絶対参照の意味に関して不安な方はこちらをご覧ください。
3つ目の［戻り範囲］には結果として表示したい全体の範囲を指定します。
今回は「出席」のデータを表示したいので「H3:H6」。
先ほどと同じく絶対参照「$H$3:$H$6」にするのを忘れないようにしましょう。
この3つの場所を指定できたら残りは無視して［OK］をクリック。
今回完成する式は下のようになります。
=XLOOKUP(D3,$G$3:$G$6,$H$3:$H$6)
これで結果が表示されます。
最初のデータの評価は「A」なので、右の表の「A」と一致した行データの「皆勤」が引っ張られてきます。
後はオートフィル機能を使用して下にコピーすれば終了です。
もしコピー後に下画像のように「0」が表示されるなど予想外の結果になったときは、途中でも説明した絶対参照にしているかをチェックしましょう。
細かい設定に関して
残り3つの下部の引数について説明しておきます。
3つとも何も入れず省略することができます。
あれ？残り2つじゃないの？と思った方は、右のスライドバーを下に移動してください。表示しきれていない場所が1つ残っていますよ。
［見つからない場合］
一致するデータが存在しなかった時に表示するものを指示できます。
下の画像をご覧ください。
「評価」が「E」の場所ですが、右の表に「E」の区分が無いため一致するものが無くエラーが表示されています。
このように一致するデータが無い時に表示したいものを入れておくのがこの場所です。
例えば ”ー” と入力しておけば、
※文字を表示したい時は「””」で囲みます。
エラーではなく「ー」が表示されます。
また、「””」とダブルクォーテーション2つ連続で入力しておくと空欄の意味になるので、
一致するデータが無い時に空欄表示にできます。
［一致モード］
5つ目にある［一致モード］の説明です。
この場所で、データが一致しなかった時の結果を指定することができます。
- 一致モード・・・一致した際の種類を指定［省略可］
- 「0」 一致データが見つからない場合、「#N/A」エラー、もしくは［みつからない場合］で指定した文字列が表示される（既定の設定）
- 「-1」 一致データが見つからない場合、次に小さい値を表示
- 「1」 一致データが見つからない場合、次に大きい値を表示
- 「2」 * 、? 、˜ をワイルドカードとして使用できる
通常、一致するデータが無かった時はエラーを表示するか、すぐ前の［見つからない場合］で指定した文字列を表示したいはずです。省略するとその設定が適用されます。
そのため特別の場合を除いて無視して良い場所です。
しかし、数値を扱っている時には「次に小さい値」「次に大きい値」を表示したい時もあるでしょう。また、ワイルドカードが使いたい時もこちらで「2」を指定する必要があります。
これらの設定ができることは頭の隅には入れておきましょう。
ワイルドカードを使用する際には少しコツが必要です。使い方はVLOOKUP関数と同じなので下のリンク先を参考にしてください。
［検索モード］
最後に［検索モード］です。
- 検索モード・・・検索の種類を指定［省略可］
- 「1」 データの先頭から検索する（既定の設定）
- 「-1」 データの後ろから検索する
- 「2」 データの先頭から高速な検索を実行する（昇順で並べ替えが行われていないデータの場合はエラーが表示される）
- 「-2」 データの後ろから高速な検索を実行する（降順で並べ替えが行われていないデータの場合はエラーが表示される）
このように、検索する際の細かい条件を指定できるのですが、Excelの動作が重くなるほどの大量のデータを扱っていない限りあまり気にする必要はない箇所です。
通常は何も考えず省略すれば良いでしょう。
まとめ
普段からVLOOKUP関数を使用していた方は、「楽になったなー」と感動するはずです。
引数が6つもあるので圧倒されますが、最初の3つだけを使用し、エラー表示を無くしたい時に4つ目も考えるぐらいでよいでしょう。
また、「XLOOPUP」関数は「VLOOKUP」だけではなく、横方向に検索する「HLOOKUP」の代わりとしても使えます。
※下の例は、「ID」を入力すると、右の表を参考に「得意先」が下に表示されるようになっています。
これまで使用する表に応じて２つに分かれていた関数が１つで済んでしまうのもステキなところ。
使用する際に注意しなければならないのは、他のユーザーとの互換性です。
比較的最近登場した関数なので、古いバージョンのExcelを使用している方に渡す予定のファイルでは使用すべきではありません。
その際は昔からあるVLOOKUP関数やHLOOKUP関数を使用しましょう。
互換性には気を付けたいにゃ