適材適所

PowerShellやVBAなどのプログラミングに関すること、キャリア、子育ての3本で書いていきます

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

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

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

万能だと思っていたVLOOKUP関数が使えない、そんなときがあるのです。

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

INDEX関数とMATCH関数を組み合わせて使ったことがある人もまた、多いのではないでしょうか。

どちらも優秀。

では、VLOOKUP関数と、INDEX関数とMATCH関数の組み合わせはどちら強い(速い)のか。

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

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

なんでもありの、無制限、一本勝負、ッファイ!!!

選手紹介

東、VLOOKUP関数

VLOOKUP関数は表を縦方向に検索し、特定のデータに対応する値を取り出す関数。

2つの異なる表からデータを検索するときなど、シート狭しと縦横無尽に大活躍する。

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

構文

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

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

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

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

問題点

ビジネスの大暴れしている関数ではあるが、実は、列の左側を返す方法がない。

これは問題なし。

f:id:shinmai_papa:20200730131216p:plain

これは×。検索値が範囲の右側にある場合、左側を返せない。

f:id:shinmai_papa:20200730130822p:plain

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

f:id:shinmai_papa:20200730131413p:plain

解決策

すぐに白旗をあげることはない。

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

西、INDEX関数&MATCH関数

MATCH関数

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

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

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

構文

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

VLOOKUP関数の検索の型と同じような照合の種類という引数があるが、完全一致一択。

INDEX関数

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

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

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

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

構文

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

組み合わせる

INDEX関数の行番号にMATCH関数で取得した行番号を入れると、柔軟な検索ができる。

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関数への置き換えを検討されてみては・・・(そんなケースないと思うけど・・・)。

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