「エクセル使いこなせていますか？」
サラリーマン歴13年で培ったエクセルスキルを基に、エクセルの使いこなし方を紹介していきます。
今回は、VLOOKUP関数に置き換わる新しい関数「XLOOKUP関数」の【基礎編】です。
例えば、次のような人にぜひ読んでもらいたいです。
- XLOOKUP関数なんて聞いたことない！
- VLOOKUP関数より使いやすい関数ってないの？
それでは一緒に見ていきましょう！
【基礎編】XLOOKUP関数の使い方
最初は【基礎編】です。関数の意味や構文、基本の使い方について押さえましょう。
さて、そもそも「XLOOKUP関数」ってご存知でしょうか？恐らく聞いたことないという方もいらっしゃるでしょうね。それもそのはずで、このXLOOKUP関数はOffice365で初めて実装された新関数なんです。
ただ、VLOOKUP関数と同じく「LOOKUP」とついていますので、値を検索する関数ということはわかりますね。早速どのような関数なのか具体的に説明していきます。
XLOOKUP関数の構文
まずは、XLOOKUP関数はどのような引数を取って、どのような戻り値を取るのか説明します。
| 書き方 | =XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード]) |
| 引数「検索値」 | 検索する値を指定します。 |
| 引数「検索範囲」 | 検索対象の配列または範囲を指定します。 |
| 引数「戻り範囲」 | 戻り値の配列または範囲を指定します。 |
| 引数「見つからない場合」 | 有効な一致が見つからない場合は、指定した「見つからない場合」テキストを返します。(省略可) |
| 引数「一致モード」 | 検索範囲内の値に対する検索値の一致方法を指定します。 ・0または省略 – 完全一致。 見つからない場合は、#N/A が返される。 ・-1 – 完全一致。 見つからない場合は、次の小さなアイテムが返される。 ・1 – 完全一致。見つからない場合は、次の大きなアイテムが返される。 ・2 – *、?、および~が特別な意味を持つワイルドカードの一致。 |
| 引数「検索モード」 | 使用する検索モードを指定します。 ・1または省略 – 先頭の項目から検索を実行。 ・-1 – 末尾の項目から逆方向に検索を実行。 ・2 – 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行。 ・-2 – 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行 |
| 戻り値 | 範囲または配列(「検索範囲」)を検索し、最初に見つけた一致に対応する項目を返します。 |
これだけだといまいちピンとこないですよね。
ここで押さえておきたいのは、後ろ3つの引数は省略可能だということです。大事な引数は検索値、検索範囲、戻り範囲の3つです。ここさえ押さえておけばOKです。
基本の使い方
では早速、基本の使い方を確認しましょう。
例えば、次のような店舗リストと販売リストがあったとします。そして、店舗コードを検索値としてE列に住所を「XLOOKUP関数」で返してみましょう。
まずは、E2セルに関数を入力することを考えてみましょう。（E3セル～E16セルはコピペすればいいですからね）
また、この例では、後ろ3つの引数は考えないものとします。(値は必ず見つかるものとし、完全一致かつ上から順に検索していきます)
検索値の考え方
ここでの検索値は、VLOOKUP関数の検索値と同様に考えればOKです。
すなわち、D列に店舗コードが入力されていますので、E2セルに関数を入力するのであれば、検索値はD2セルの値ですね。セル参照でもOKですので、そのまま「D2」と入ります。
=XLOOKUP(D2,「検索範囲」,「戻り範囲」)
検索範囲の考え方
検索値が店舗コードですから、検索範囲は「店舗コードの値の範囲」ということになります。つまり、店舗リストのA列に店舗コードが入力されていますから、範囲としては「店舗リスト!A1:A8」となります。
ただし、E2セルに入力した関数をそのあとE3セル～E16セルにコピペすることを考えて、絶対参照にしておくと良さそうですね。つまり、「店舗リスト!$A$1:$A$8」が入ります。
=XLOOKUP(D2,店舗リスト!$A$1:$A$8,「戻り範囲」)
戻り範囲の考え方
戻り範囲は、戻り値として戻ってきてほしい値の範囲なので、「住所の値の範囲」ということになります。つまり、店舗リストのD列に住所が入力されていますから、範囲としては「店舗リスト!D1:D8」となります。
ただし、検索範囲と同様に、コピペしたときにセル参照がずれないように絶対参照にしておきましょう。つまり、「店舗リスト!$D$1:$D$8」が入ります。
=XLOOKUP(D2,店舗リスト!$A$1:$A$8,店舗リスト!$D$1:$D$8)
XLOOKUP関数はどのように値を返しているのか？
ということで、あっという間にXLOOKUP関数が書けてしまいました。しかも、下の画像のように、正しく値が反映されていますね。
でも、XLOOKUP関数はどうやって目的の値を返してくれているのでしょうか？仕組みとしては以下の図の通りです。
- 検索範囲から検索値を探す
- 戻り範囲から同じ位置にある値を返す(検索範囲の中での検索値の位置と同じ位置(例の場合は上から2行目))
どうでしょうか？思ったより単純ですね。
さて、ここまで見てきたように、VLOOKUP関数とだいぶ異なった印象を持たれるかと思います。違いはいろいろとあるのですが、主に次のような違いがあります。
- 範囲の考え方が根本的に異なる
- VLOOKUP関数では、検索値と目的の値の両方を範囲に含めなければならず、縦方向の検索しかできなかった。
- XLOOKUP関数では、「検索値を含む範囲」と「目的の値を含む範囲」を別々に考える。縦方向も横方向も検索できる。
- 検索方法の指定が異なる
- VLOOKUP関数では、既定値は近似値検索。
- XLOOKUP関数では、既定値は完全一致検索。
こうやって見ると、XLOOKUP関数はとても便利そうじゃないですか？
XLOOKUP関数を使おう！
ここまでXLOOKUP関数の基本の使い方について紹介しました。VLOOKUP関数を使える人にとっては、XLOOKUP関数の便利さに気づかれたのではないでしょうか？
次の【実践編】では、もう少し突っ込んだXLOOKUP関数の使い方を紹介します。バリバリ使えるようになりましょう！
コメント