MOS365の出題範囲が発表になり、Excelの科目では新たにXLOOKUP関数が出題範囲に入ることになりました。XLOOKUP関数は最近追加された関数で、VLOOKUP関数の仲間で、VLOOKUP関数の欠点を補うために作られたのですが、HLOOKUP関数を含め、あらゆる使い方に対応しているすごい関数です。
ネット上には多くの情報はあるのですが、書籍の情報が少ない状態で、MOSの出題範囲に入りそのまま試験が開始してしまう見込みとなっています。
そのため、緊急でXLOOKUP関数の存在と使い方を広める必要がありますので、MOSの出題範囲として考えたときのXLOOKUP関数の使い方を解説します。XLOOKUP関数はとても複雑な使い方ができるので、MOSで、試験問題で表現できないような使い方もできますが、その範囲までの解説ではなく、あくまで理解しやすい、試験に出るだろうなと予想できる範囲の解説を進めていきます。
XLOOKUP関数とは
まず、VLOOKUP関数のお話をしましょう。VLOOKUP関数は、ある値を一覧表の最左列から探しそれに一致した行の指定した列の値を求める、という関数ですね。
ざっくり言っちゃうと、探したコード番号に対応した商品とか単価のデータを求めてよ、という風に言うことができるでしょう。
VLOOKUP関数の書式は次の通りです。
=VLOOKUP(検索する値,一覧表の範囲,列番号,完全一致か近似一致か)
検索する値は、一覧表の一番左の列の中で探す値
一覧表の範囲は、検索される一覧表の範囲、
列番号は、最終的に求めるのは何列目か
完全一致か近似一致か、はちょっと難しくて、検索値と全く同じものを探す場合は完全一致でFALSEを指定、検索値に一番に近くて検索値よりも小さい値を探す場合にはTRUEを指定となります。近似一致の場合は検索される値は数値で昇順にならんでいけなければなりません。
Excel関数の中でも強力な部類に入るVLOOKUP関数ですが、最大の欠点は、検索される値が、検索される表の一番左になければならないということです。たいていの検索される表はマスターデータと呼ばれるもので、本来、マスターデータの最左列にはコード番号が入っているもので、逆に言うと、マスターデータはそうでなければなりません。
=””>
しかし、何を間違ったか、コード番号が2列目、3列目、下手をすると最右列にあるケースがあります。
=””>
この表は罪深いと思うのですが、VLOOKUP関数の検索値がどうしても一覧表の右に入っている値を検索値にしたいという場合も実際にはありえます。これは私は正当な理由だと思います。例えば最右列にはコード番号は入っているのですが、最右列には基準の点数が入っていて、その基準点の近似値となるコード番号を探すというようなケースです。
=””>
そうなるとせっかくの強力なVLOOKUP関数では対処できなくなります。そこでVLOOKUP関数の代わりになる方法として、検索値が何番目に入っているデータなのかを調べるMATCH関数と、範囲の中で指定した番目のデータを求めるINDEX関数の組み合わせで対処するということを、実はもう20年以上もやってきたのです。
INDEX関数とMATCH関数の組み合わせの書式は次の通りです。
=INDEX(最終的に求める値,MATCH(検索値,検索される範囲,0)
しかし、このINDEX関数とMATCH関数の組み合わせは、「ごまかし」だとも捉えることができるもので、そのような使い方が必要なのだから、一つの関数で済むようにできることが本来の姿だったのです。
最近になってやっとそのXLOOKUPが登場しました。実装されるのが遅すぎで、世の中には大量のINDEX、MATCHの組み合わせの数式がどれだけ作られてしまったのでしょうか。
実はその最左列ではなくても検索できる関数が昔からあったのです。「LOOKUP関数」と言います。頭になにもついていないLOOKUPなのでなんでも対応できるLOOKUP関数のように感じます。
書式は次の通りです。
=LOOKUP(検索値,検索値を探す一覧表,最終的に求める値の一覧表)
実はXLOOKUP関数はVLOOKUP関数ではなく、このLOOKUP関数から発展したものだと私は考えます。上記の欠点はLOOKUP関数では実際にはクリアされます。しかしLOOKUP関数にも欠点があって、完全一致か近似一致かどうかの指定ができず近似一致となります。
存在しないコード番号を指定すると、たとえ文字だったとしても近似一致となるので、一番近いコード番号を示してしまい、意図とは違う動作になりかねません。
それにもう一つ、LOOKUP関数の最も弱かったことはVLOOKUP関数ばかり注目されてしまったので、存在していることが知れ渡っていないことだと感じています。
実は今までもLOOKUP関数で解決できたことでもINDEX関数とMATCH関数の組み合わせで計算式を作成していたのではないかと思います。2つの関数を組み合わせるより1つの関数で済んだ方いいと私は考えます。
そんなこんなで登場したXLOOKUP関数なのですが、LOOKUP関数に比べてかなり多くの機能をてんこ盛りしてしまったので、MOSの試験で出題されるのはどこまでなのかというところにも注目していきたいですね。
XLOOKUP関数はLOOKUP関数と同じ3つの引数さえ指定していれば動作します。
=XLOOKUP(検索値,検索値を探す一覧表,最終的に求める値の一覧表)
この後が3つ引数を指定できます。指定しなくてもいいところは「,,」のように指定しなくて動作しますがMOS試験的にどうかというところは心配でもあります。
=XLOOUP(検索値,検索値を探す一覧表,最終的に求める値の一覧表,検索して見つからなかった場合,完全一致か近似値か,検索の方法)
4つ目以降の引数は次の通りです。
検索して見つからなかった場合は、探して見つからなかったときに出す値で今までは空白だったら空白みたいにしていた機能を関数の中で実健しました。完全一致モードで見つからないだけではなく、近似値モードで「より小さい」を指定したときにその値よりも小さい値が見つからなかったときにもこの値を指定できます。
完全一致か近似値かは、完全一致なのかと近似一致も一番近い値でその数字より大きい場合と小さい場合で指定できるのが画期的なところです。また、文字データの検索であれば文字の一部だけで指定できるワイルドカード検索も選択することができます。
検索の方法は、検索するときに上から探すのか下から探すのかのほかに、バイナリ検索という手法を用いた検索をする方法が選べます。実はXLOOKUP関数は、並べ替えていなくても検索ができるのです。これは従来のLOOKUP関数でもVLOOKUP関数でもできなかったことで、これができないために自動化を諦めたことがあったくらいで、私としてはXLOOKUPの一番の注目点だと思います。ただここでバイナリ検索モードにすると絶対に並べ変わっていなければならないのです。その代わり検索する動作がものすごく早くなります。バイナリ検索には昇順で並べられている場合と降順に並んでいる場合とでの選択肢があります。
XLOOKUP関数の予想問題文
予想される問題はいくつかパターンがあります。単純にLOOKUP関数でできることならXLOOKUP関数で操作する必要はないので、XLOOKUPならではであり、かつINDEX関数とMATCH関数の組み合わせでもできないパターンで、XLOOKUP関数というキーワードを出さずにXLOOKUP関数にたどり着ける問題になるかと思います。
- M列の対戦者欄に、グループAのうち、グループBのそれぞれの勝率より高くて一番近い勝率の参加者の参加者IDを関数を使って求めて下さい。見つからない場合は「なし」と表示し、検索方法は指定しません。
今回は検索値がF列ですが、見てみると勝率なので完全一致のデータではありません。またF列はきちんと昇順や降順で並んでいないことに注目します。それ以前に、求める値がB列で検索するのがF列なので、これは検索値が右側にあるのでXLOOKUP関数です。あとF列が昇順または降順で並んでいないのでINDEX関数＋MATCH関数でも無理ですね。
セルM4に入る数式は次の通りです。
=XLOOKUP(L4,$F$4:$F$13,$B$4:$B$13,"なし",1)
3つ目の引数まではわかると思います。4つ目は見つからない場合は「なし」にする指定がありました。5つ目は一番近くて大きいものを探すので「完全一致または次に大きいもの」である1にします。検索方法は特に指定しなくてよいそうです。
もしも検索方法が不要と書いていなかったら、ちょっと悩みますね。この問題であれば昇順に並んでいないので、バイナリ検索はないので上から探すか下から探すかの2択になりますが、もしきれいに並んでいたらどの選択肢が正解になるのか。よく判断しなければなりませんね。並べ替えられた一覧が昇順か降順に並んでいてその順番で選択できるかもしれません。
- セルD1に、セル範囲B2からB4までの範囲の中で「宮*?市」の項目を関数を使って求めて下さい。
この問題はよく考えられていて、XLOOKUP関数は「*」や「?」の文字を使ったワイルドカードは5つ目の引数でワイルドカードを使った指定をしなければ使えません。
今回は、探す文字列、探される文字列にワイルドカードで使う文字である「*」「?」が使われているのでワイルドカードではなく、真の完全一致なのです。
実はこれは、VLOOKUP関数では無理で、VLOOKUP関数はワイルドカードかどうか指定できないのですが、ワイルドカードを使った検索になっているので、真の完全一致ができません。
そこで、XLOOKUP関数という判断になります。
=XLOOKUP("宮*?市",B2:B6,B2:B6)
XLOOKUP関数は指定しなければワイルドカードは使えませんので、3つの引数だけ指定します。
こうやって関数を眺めてみると、何も考えずにXLOOKUP一直線で考えたら本当に何も考えずに正解になるという答えですね。
よく考えるとひっかかり、さらによく考えるとひっからなくなるという、従来、VLOOKUP関数を使ってワイルドカード検索していた人をひっかけるような問題ですね。
XLOOKUP関数が出題される試験
XLOOKUP関数が出題されるのはMOS365ExcelExpert（上級）です。MOS365ExcelAssociate（一般）では出題範囲に入っていません。またMOS365以前のバージョンでも当然出題されません。
XLOOKUP関数は問題文の作り方によって幅広い課題に対応できると思いますので、サーティファイの表計算試験でも問題文でXLOOKUP関数でしかできないようなことを出題されそうな気がしています。
日商PCデータ分析では、どんな関数や機能を使ってもいいので、受験者次第ですね。正直、混乱するのであれば従来のVLOOKUP関数やINDEX関数＋MATCH関数を使った方がいいでしょう。
まとめ
どうしてもVLOOKUP関数を使ったことがある方向けの解説になってしまいましたが、そうしないとXLOOKUPの何が便利なのかを伝えられないので、思い切ってこのような解説にしました。
XLOOKUP関数はとても奥が深いのですが、問題文になったときに他の関数でできちゃうことになってしまうと、どう解答したらいいか迷う問題になってしまうので、そのような問題文にならないような出題されるというのが事前準備のヒントなのではないかと思っています。もしかしたら見つからなかった場合の指示はまあまあ確実に指定される気がします。
Web上ではXLOOKUP関数が出たときにかなりバズっていたので、関連記事もWeb検索すれば出てくるようになりました。今、やっと書籍でも扱われ始めているので、勉強の機会が増えると思いますが、まだちょっと時間がかかりそうなので、MOS試験に主眼を置いて深堀しすぎないように記事を作ったつもりですが、ちょっとマニアックになってしまいましたね。
もし、この記事がMOSの勉強に役立つ、と思えたら、ぜひ周りのこれからMOSの最新版を受験したいと言っている人。最新のExcelに興味のある人に、印刷したものでも構いませんので、この記事をお渡しください。
関連リンク
- MOS公式サイトのMOS365ページ
https://mos.odyssey-com.co.jp/outline/mos365.html
コメント