「エクセル使いこなせていますか？」
サラリーマン歴13年で培ったエクセルスキルを基に、エクセルの使いこなし方を紹介していきます。
今回は、VLOOKUP関数に置き換わる新しい関数「XLOOKUP関数」の【応用編】です。
例えば、次のような人にぜひ読んでもらいたいです。
- XLOOKUP関数のもっと便利な使い方はないの？
- XLOOKUP関数とスピルの組み合わせって言われてもよくわからない！
それでは一緒に見ていきましょう！
【応用編】XLOOKUP関数をスピルと一緒に活用しよう！
今回は【応用編】ということで、XLOOKUP関数のもっと便利な使い方を解説していきます。
そもそも、皆さんはExcel2019および365から実装された「スピル」機能はご存知でしょうか？
「知らない」という方は以下の記事もぜひご覧くださいね。
スピルを一言で説明するとすれば、
ある1つのセルに複数の値を返す数式を入力した時に、それらの値を隣接する複数のセルに返す機能
ということです。そして、このスピル機能をXLOOKUP関数で活用できるんですよね。
ということで、スピルを用いたXLOOKUP関数の使い方について解説していきます。
これで、さらに便利にXLOOKUP関数を活用いただけるようになりますよ！
XLOOKUP関数でのスピルの使い方：基本
まずは基本から押さえていきましょう。XLOOKUP関数の引数と戻り値について復習すると、次のような内容でした。
※なお、ここでは後ろ3つの引数は考えないものとします。つまり、値は必ず見つかるものとし、完全一致かつ上から順に検索していきます。
| 書き方 | =XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード]) |
| 引数「検索値」 | 検索する値を指定します。 |
| 引数「検索範囲」 | 検索対象の配列または範囲を指定します。 |
| 引数「戻り範囲」 | 戻り値の配列または範囲を指定します。 |
| 戻り値 | 範囲または配列(「検索範囲」)を検索し、最初に見つけた一致に対応する項目を返します。 |
ここでポイントは、戻り範囲は範囲を指定できるというところです。
実は、戻り範囲として2×2以上の範囲を指定すると、戻り値もまた範囲が返ってくるんです。
それでは早速、具体例を見てみましょう。
例えば、次のような店舗リストがあったとして、店舗コード(A2セル)を検索値に店舗名～URLまでの値をXLOOKUP関数でB2セル～F2セルに反映してみましょう。
通常であれば、B2セル～F2セルにそれぞれXLOOKUP関数の式を入力しようとするかと思います。すなわち、次の図ようなイメージです。
一方で、スピルを使う場合は、次のようにB2セルにのみXLOOKUP関数を入力するのです。
=XLOOKUP(A2,A5:A11,B5:F11)
このように入力してEnterキーを押すと、次のようにスピル機能がはたらき、B2セル～F2セルに値が反映されます。B2セル～F2セルが青線で囲まれていますね。
ここでのポイントは、戻り範囲として1列や1行ではなくB5:F11という7×5の範囲を指定しているというところです。
こうすることで、検索値を検索範囲から探して、その位置に対応した値を返すときに範囲を返すという形になります。(この例だと、5行目の位置の範囲を返すという形)
いかがでしょうか？スピルを活用することで、関数をたくさん入力したりコピペする手間が省けますね。
XLOOKUP関数でのスピルの使い方：応用
XLOOKUP関数での基本のスピルの使い方を応用すると、2次元の表での値の検索が可能になります。
従来の2次元の表での値検索といったら「MATCH関数とINDEX関数の組み合わせ」が有名ですが、XLOOKUP関数を使っても同じことを実現出来ます。
では、早速具体例で方法を解説していきましょう。
例えば、次のような表があったときに、発車駅と到着駅に対応する運賃をXLOOKUP関数を使って求めたいとしましょう。
XLOOKUP関数を使って検索する場合は、次のような数式となります。
=XLOOKUP(J2,A2:A8,XLOOKUP(K2,B1:H1,B2:H8))
どのような仕組みで値が反映されているのか、順を追ってみてみましょう。
- 内側のXLOOKUP関数によって、K2セルの値(F駅)をB1:H1範囲から検索→G1セルが該当
- 次に、B2:H8範囲から同じ位置にある範囲を返す→スピルによってG2:G8範囲が返る
- 外側のXLOOKUP関数によって、J2セルの値(C駅)をA2:A8セル範囲から検索→A4セルが該当
- 次に、G2:G8範囲から同じ位置にあるセルを返す→G4セル(\380)が返る
いかがでしょうか？ポイントは、外側のXLOOKUP関数の戻り範囲が、内側のXLOOKUP関数のスピルによって可変になっているという点です。
しかも、MATCH関数&INDEX関数だとMATCH2つ+INDEX1つに対して、XLOOKUP関数だとXLOOKUP2つしか使っていないので、関数の記述や修正が楽になりますね。
XLOOKUP関数はとても便利な関数！
ということで、XLOOKUP関数のスピルとの組み合わせ方について解説いたしました。
スピルと組み合わせることで、かなり便利に使えそうですね！
バリバリ使えるようになりましょう！！
コメント