以前から、めっちゃ便利なVLOOKUP関数。皆さんも絶賛使っていますよね。本当に便利で使い勝手が良い関数なので、一番使っている関数では無いかと思うくらいですが、その、私たちが大好きなVLOOKUP関数のアップグレードバージョンの XLOOKUP 関数が365から実装されているのを皆さんも、ご存じですよね。 XLOOKUP 関数は VLOOKUP 関数の弱点を大幅に改善して使いやすくなってます。
今回は、以前紹介した、「VLOOKUP関数を使って、Excelが名前で名簿を検索して、部署名と内線番号を一瞬で表示する仕組みを作る」という記事をこの新しい関数 XLOOKUP 関数で書き換えたらどうなるか、やってみました。
実際に XLOOLUP 関数って使ってみると、本当に超便利ですね。 VLOOKUP関数の便利さを更に機能アップした関数と言うことで、使い方が簡単になってあっというまに出来てしまいました。
どういう風に変わったか、使い方がどのように簡単になったかを、この記事で確認してみてください。また、皆さんも XLOOLUP 関数、使ってみてくださいね。
では、やってみましょう！
今回は、以前紹介したVLOOKUP関数によるツールを、EXCELエクセルに新しく追加された XLOOKUP 関数に置き換えて、ツールを作ります。
XLOOKUP 関数では、VLOOKUP 関数の弱点であった、セル範囲の指定方法の煩わしさが改善され、指定がしやすくなっています。
XLOOKUP を使用すると、1 つの列の中で検索語を検索し、戻り列が右側か左側のどちら側にあるかに関係なく、その列の同じ行から結果を返すことができます。
それでは、始めます。
まずは、 XLOOKUP 関数の仕様から簡単に説明しておきますね。
もう知っているよと言う方は読み飛ばしてください。（ここをクリック）
XLOOKUP 関数の仕様
XLOOKUP（検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード]）
| 検索値 | 検索する文字列や値です。 今回の検索ツールでは、名前の文字列にあたります。 |
| 検索範囲 | 検索対象とするデータの範囲です。 検索値を検索する列の範囲を指定します。 |
| 戻り配列 | アウトプットしたい値の列を、範囲指定します。 複数列を戻したい場合は、複数列を範囲指定します。その場合、スピルで返されます。 |
| 見つからない場合 | 有効な一致がない場合は、[見つからない場合]の文字列を返します。 |
| 一致モード | 一致の種類を指定します 0が完全一致で指定がない場合はこのモードになります。 |
| 検索モード | 検索のモードを指定します 1が先頭から検索をするモードで、指定がない場合はこのモードになります。 |
XLOOKUP 関数の検索範囲と戻り配列の行番号は一致している必要があります。
データを準備する。データベースとも言います。
実際にやってみましょう。
今回のケースでは、名前を入れたら完全一致でデータベースを検索し、部署名と内線番号を表示するため、名前・部署名・内線番号の表を作成します。
今回は、各部署を代表する5名を選抜して名簿を作りました。
これで、データベースの準備ができました。
XLOOKUP 関数で検索するフォームを作成する
次のような入力フォームを作ります。黄色の枠に名前を入れると、その下の枠に部署名と内線番号が表示される仕組みです。
部署名を表示するB6へ、＝ XLOOKUP (B3, E3:E7, F3:G7, ””)と入力します。B3の値（文字列）をE3:E7の検索範囲から検索し、F3:G7の2列の値を返す。という意味です。
したがって、例の田中太郎を入力すると、F3の営業とG3の102が返されるというわけです。
これで終わりです。
なんて簡単になったのでしょうか。感動です。
VLOOKUP 関数では、範囲の2列目を部署名、3列目を内線番号と別の数式を書かなくてはいけませんでした。
XLOOKUP関数では、検索範囲と戻り配列で指定する方法と、365から導入された スピル spill で返されるという大変強力な機能で、作業が超効率化されます。
そして、また検索がエラーだった場合に、 IFERROR 関数を使っていましたが、見つからない場合の返す値もパラメータで指定できるのも、とても強力です。
エラーの処置はどうか
でも、ちょっと待ってください。入力ミスした場合はどうなるでしょうか。”山田 みどり”と入力してみます。エラーが出ず、空白になりました。
これは、見つからない場合に””（空文字）を指定しているからです。
このように、 IFERROR で回避していたことも、同時にできるのです。
まとめ
- データベースの検索は、 VLOOKUP 関数から XLOOKUP 関数に変えることで、いっぺんに複数列の値を返すことが出来る
- エラー表示を消すために、 IFERROR 関数を使う必要がなく、[見つからない場合]パラメータにしていするだけでOK。
- 非常に強力なため、是非 XLOOKUP 関数に乗り換えてください。これを使うと、もう VLOOKUP 関数には戻れません
今回は以上です。
超便利なので、一度作成してみてください。
コメント