適材適所

PowerShellやVBAなどプログラミング系の話多めで

VLOOKUP関数とINDEX&MATCH関数の熱き戦い

Excelを使ってていて、VLOOKUP関数に1度もお世話になったことない人などいるだろうか。

VLOOKUP関数こそ、最強のワークシート関数だと思っていた時期が俺にもありました。

しかし、

万能だと思っていたVLOOKUP関数が使えない、

そんなときがあるのです。

そしてVLOOKUP関数が使えず、悩んでいるときに

INDEX関数とMATCH関数を組み合わせて使ったことがある人も

また多いのではないでしょうか。

どちらも優秀、最強。

しかし、最強同士が相対したとき、

どちらが本当の漢(おとこ)なのか

決めなくっちゃあいけません。

VLOOKUP関数と、

INDEX関数とMATCH関数の組み合わせは

どちら強い(速い)のか。

一度疑問に思ったら最後、どうしても戦わせてみたくなりました。

ここに異種格闘技戦が開幕!!

なんでもありの、

無制限、一本勝負、ッファイ!!!

選手紹介

東、VLOOKUP関数

VLOOKUP関数は表を縦方向に検索し、

特定のデータに対応する値を取り出す関数。

2つの異なる表からデータを検索するときなど、

シート狭しと縦横無尽に大活躍する。

ワークシート関数界でも指折りの猛者。

構文

=VLOOKUP(検索値, 範囲, 列番号, 検索の型)

引数の「検索値」は範囲の中から検索する値。

「範囲」は、検索する範囲。

そして、「列番番号」は範囲内の何番目の列を戻り値として返すか指定する。

引数の「検索の型」はほとんど完全一致一択。

他の値は使ったことがない。

使い方もよくわからない。

問題点

ビジネスの現場で大暴れしており、

これが使えるかどうかがExcel関数使いの登竜門的関数ではあるが、

実は、大きな弱点がある。

それは、列番号に指定できる値は正の数だけなので、

戻り値として返すことができるのが、

検索して見つかった値の右側にある値に限られるという点だ。

列の左側を返すことができないのだ・

一例を見てもらいたい。

これは問題ないケース。

ちゃんと検索できている。

f:id:shinmai_papa:20200730131216p:plain

これは問題のケース・

検索値が範囲の右側にあるため、

左側にある値を返すことができない。

f:id:shinmai_papa:20200730130822p:plain

列番号に負数を指定できないッ!

f:id:shinmai_papa:20200730131413p:plain

これはVLOOKUP関数の唯一といってもいい弱点だ。

ではこんなときどうしたらいいのか。

そんなときこそ、彼らの出番である。

西、INDEX関数&MATCH関数

MATCH関数

指定した範囲内で探したい値の行や列を返す関数。

これ単体だと、正直使い道がわからない。

INDEX関数と組み合わせて使うべくして生まれてきたとしか思えない。

構文

=MATCH(検査値,検査範囲,[照合の種類])

VLOOKUP関数と風情が似ている。

「検査値」に探したい値を指定する。

「検査範囲」に検査したい範囲を指定する。

そして「照合の種類」。

これは完全一致一択。

INDEX関数

指定された行と列が交差する位置にある値を返す関数。

これ単体で使った経験がない。

どういった場面で使うのか。

やはり、MATCH関数と組み合わされるべくして生まれてきたとしか思えない。

構文

=INDEX(配列,行番号,[列番号])

まさか関数で「配列」という引数が出てくるとは・・・。

普段使いであれば、範囲と同じと考えて差し支えない。

「行番号」は配列=範囲の行番号=行数を指定する。

「列番号」は範囲が2列以上の場合に指定する。

INDEXとMATCH組み合わせる

一見、何も関係がないように見えるこの二つの関数だが、

組み合わせることで科学反応が起きる。

INDEX関数の行番号にMATCH関数で取得した行番号を入れると、

VLOOKUP関数以上に柔軟な検索ができるようになる。

VLOOKUP関数の完全上位互換。大好き。

f:id:shinmai_papa:20200720130703p:plain

両者出揃ったところで勝負開始ッ!!

どちらが強い(速い)のか。

簡単なことだ。

戦わせてみればいいッ!!

ということで、両者の速度を検証してみたいと思います。

ワークシート上では速度の検証ができないので、

VBAを使って検証してみたいと思います。

VLOOKUPは命令が一つだけですが、内部で複雑な処理が行われているのでは?

それに対して、MATCHとINDEX関数は単純な関数の組み合わせのため、

だが、内部的には単純な関数かも知れません。

2つの関数を呼び出すとは言え、

実はこっちの方が速いのでは?

なんて予想をしておりますが、

果たしてその結果やいかに?

環境

Windows10 Pro(64bit) Excel2019 32bit

コード

先ほどのシート構成を対象に調査してみます。

 
Sub vlookupTest()
   Dim startDoubleAs Double: startDouble = Timer
   Dim var As String
   Dim i As Long
   For i = 1 To 100000
      var = WorksheetFunction.VLookup(Range("D2"), Range("A2:B10"), 2, 0)
   Next i
   Dim endDouble As Double: endDouble = Timer
   Debug.Print "vlookup:" & endDouble - startDouble
End Sub
 
Sub indexTest()
   Dim startDoubleAs Double: startDouble = Timer
   Dim var As String
   Dim i As Long
   For i = 1 To 100000
      var = WorksheetFunction.Index(Range("B2:B10"), WorksheetFunction.Match(Range("D2"), Range("A2:A10"), 0))
   Next i
   Dim endDouble As Double: endDouble = Timer
   Debug.Print "index:" & endDouble - startDouble
End Sub

結果

vlookup:1.1796875
index&match:2.125
(単位:秒)

なんと、VLOOKUPの方が2倍近い性能を見せつけました。

まさに圧倒的ッ!!

やはり、MATCHとINDEXは関数を

2つ呼び出しているので2倍なのでしょうか?

ここから言えることは、VLOOKUP関数を使える場面では、

おとなしくVLOOKUPを使った方がパフォーマンスがいい!!

ということですね。

番外編

MATCH関数だけだとどうなんだ?

ということであまり意味はないですが、やってみました。

Sub matchTest()
   Dim startDoubleAs Double: startDouble = Timer
   Dim var As String
   Dim i As Long
   For i = 1 To 100000
      var = WorksheetFunction.Match(Range("D2"), Range("A2:A10"), 0)
   Next i
   Dim endDouble As Double: endDouble = Timer
   Debug.Print "match:" & endDouble - startDouble
End Sub

結果

match:1.80859375

なんと、2秒近く掛かっています。

INDEX関数との組み合わせでは、

ほとんどがMATCH関数の処理で時間を取られていたということが言えそうです。

ここでもやはり、

VLOOKUP関数の性能の良さが浮き彫りになりました。

く、くやしい・・・。

終わりに

VLOOKUP関数とMATCH、

INDEX関数の速度を比較してみました。

大抵の場合はそこまで性能を気にすることはないかと思いますが、

大量のセルを処理する場合には、

パフォーマンスに多少の影響が出ることが考えらえれます。

もし、VLOOKUP関数を使えるのに、

MATCHとINDEX関数を使っている場合は、

VLOOKUP関数への置き換えを検討されてみては・・・。

ということで、ここまでお読みいただきありがとうございました。

Excel関数と戯れる関連記事

www.tekizai.net

www.tekizai.net