Kato Ryo Official Site

抽象度とエネルギーを上げる

VLOOKUPよりINDEX・MATCHの組み合わせの方がベターである4つの理由

2017/04/25
 
この記事を書いている人 - WRITER -
Pocket

Excelを使って色々な業務の処理を行う中でよく使う関数にVLOOKUPがある。VLOOKUP関数の構文は以下の通り。

VLOOKUP(検索値,範囲,列番号,[検索方法])

 

VLOOKUP関数では、ある検索値を指定して、それに対応するデータをピックアップすることができる。例えば以下の表は、東京証券取引所のサイトからダウンロードした上場先のリストだが、証券コード(B列)から銘柄名(C列)を抽出する際などにVLOOKUP関数が使える。

 

 

この表のように表が小さく、また検索のキーとなる値(検索値)と検索される値が近い場合は、VLOOKUPの「列番号」の指定も簡単なので、あまり問題もないのだが、VLOOKUPには根本的に以下のような弱点がある。

  • 検索の範囲を指定しなければならない
  • 検索される値が並んでいる列(…縦方向)の番号を、数値で指定しなければならない。

上記のような弱点があるため、検索範囲に列を挿入・削除した場合、エラーになったり、想定した値が返ってこないということになり、式を修正しなければならない事態がよく起こる。

このようなVLOOKUPの弱点を回避できるのが、INDEX関数とMATCH関数の合わせ技だ。INDEX関数、MATCH関数の合わせ技には、VLOOKUP関数にはない、以下のようなメリットがある。

 

  1. 検索対象の配列を指定しなくてもよい
  2. 列を挿入したり削除しても、自動で式が更新される
  3. 他の列も検索したい場合に、式のコピー&ドラッグで対応しやすい
  4. (VLOOKUPは左から右への検索しかできないが)右方向から左方向への検索も可能

 

以上の4点について詳しく見ていく。

①検索対象の配列を指定しなくてよい
例えば下の表(写真)で証券コード(B列)から銘柄名(AA列※)を抽出し、その検索結果をAC列(VLOOKUP関数を使用)及びAD列(INDEX関数とMATCH関数を使用)に書き込む場合を考えてみよう。

 

先ほどの表と異なるのが、証券コードと銘柄名に何列も挿入されていることだ。

今回はサンプルデータなので、無駄に列が挿入されているだけだが、実務で使う場合など、検索値と検索対象がかなり離れているということも珍しくない。

 

<VLOOKUP関数での検索>

=VLOOKUP(B2,B:C,26,FALSE)

VLOOKUP関数での検索では自分で列番号(この場合「26」)を指定しなくてはならない。列番号も小さい数なら数えても手間ではないが、今回のように何十列も離れていると数えるのも大変だ。それに、列が削除や挿入された場合、その度に列番号を手で修正しなくてはならない。

 

<INDEX関数及びMATCH関数(以下、INDEX・MATCHとする)>

=INDEX(AA:AA,MATCH(B2,B:B,0))

それに対してINDEX・MATCHの場合、検索値と検索対象の列がどれだけ離れていても上記のような式で書くことができる。この場合、列として指定しているのは、検索値があるB列と、検索対象となるAA列だけだ。二つの関数を使っているので、一見複雑に見えるが、無駄に範囲指定をする必要がないので(検索のターゲットとなる列だけ指定すればよい)、慣れればこちらの方がわかりやすい。

 

②列を挿入したり削除しても、自動で式が更新される
①の説明時にも少し触れてしまっているが、INDEX・MATCHの場合、列番号の指定、というものがないので、列を挿入したり削除しても、自動で式が更新される。説明上、2番目に来てしまったが、これがおそらくINDEX・MATCHの最大のメリットだ。

 

③他の列も検索したい場合に、式のコピー&ドラッグで対応しやすい
例えば「証券コード」から「市場・商品区分」(AB列)も検索しようとした場合、列番号を指定し直す必要がないので、VLOOKUP関数を使う場合より対応しやすい。

 

④(VLOOKUPは左から右への検索しかできないが)右方向から左方向への検索も可能
これはVLOOKUPではできない動作だ。Excelのスプレッドシートは基本的に上から下、左から右に流れていくので、左から右方向のへの検索はその感覚に反し、実際使用する機会は少ないかもしれない。ただ、データの配置の関係で、左から右への検索を行うこともあるかもしれないし、その場合にはVLOOKUPでは対応できない。

 

【まとめ】
以上、INDEX関数・MATCH関数の複合技が、VLOOKUP関数より、どのように便利なのか見てきた。二つの関数を使うので、慣れるまでは戸惑いを感じるかもしれないが、特に複雑なことをしている訳ではないので、実際に手を動かして使っていれば、すぐに慣れると思う。

この記事を書いている人 - WRITER -

- Comments -

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

Copyright© 抽象度とエネルギーを上げる , 2017 All Rights Reserved.