VLOOKUP関数が400倍速くなった

---

これ、ほとんど知られていないのですけど、Excel 2016は"みんな大好き"VLOOKUP関数とMATCH関数の計算速度が、約400倍速くなりました。実際に速度を計測してみました。もうね、笑っちゃいますw

速度計測は、次のようにしました。まず、A列とB列に元データを作りました。10万件です。

この、セル範囲A2:B100001に対してVLOOKUPします。D列には、VLOOKUP関数で調べる"A-100000"を1万個入れてあります。ワークシートの再計算を"手動"にしてから、隣のE列(セル範囲E2:E10001)に「=VLOOKUP(D2,$A$2:$B$100001,2,FALSE)」的な数式を1万個入れます。もちろん、VLOOKUP関数の第1引数は、D2・D3・D4・・・となるようにしています。ちなみに、VLOOKUP関数の第4引数に"FALSE"を指定すると"完全一致"で表引きしますので、元表の一番最後で見つかると、最も時間がかかります。さて、この1万個のVLOOKUP関数を再計算して、どれくらい時間がかかるかを、Excel 2013とExcel 2016で計測しました。結果は次のとおりです。

回数	2013	2016
1	185.282	0.453
2	188.453	0.422
3	180.922	0.438
4	183.141	0.438
5	180.656	0.422
6	194.454	0.438
7	211.766	0.422
8	185.453	0.422
9	189.843	0.437
10	185.891	0.438
平均	188.586	0.433

ね、笑っちゃうくらい速くなったでしょ。再計算に3分以上かかっていたのが、0.5秒程度です。

クラウドの時代になって、Excelで扱うデータは大量になりました。基幹システムやサーバーから、数万件～数十万件のデータが降りてくるのは、もはや珍しいことではありません。そして実務では、そこにVLOOKUP関数を使います。みんな大好きですからw すると、再計算でExcelが固まります。ステータスバーに表示される「再計算 ○○%」という表示を見ながら、"ため息"をついた人は少なくないでしょう。Excel 2016からは、もう"ため息"をつく間もありません。上記のように、私の環境では約435倍の高速化でした。

あともうひとつ、MATCH関数も速くなっています。上記の検証で使った表に対して、VLOOKUP関数の代わりに「=MATCH(D2,$A$2:$A$100001,0)」的な数式を1万個代入したときの再計算速度は、次のとおりです。

回数	2013	2016
1	181.485	0.593
2	167.437	0.563
3	177.218	0.766
4	169.984	0.906
5	162.078	0.766
6	175.828	0.641
7	172.797	0.672
8	164.687	0.719
9	170.797	0.610
10	172.297	0.656
平均	171.461	0.689

MATCH関数も劇的に速くなっています。VLOOKUP関数の代わりに、MATCH関数＋INDEX関数を使った数式も、めちゃくちゃ速くなるわけです。ただし、残念ながら、速くなったのは、VLOOKUP関数とMATCH関数だけです。SUMIF関数やCOUNTIF関数は従来のままです。まぁ、使い方にもよりますけど、もし大量のSUMIF関数やCOUNTIF関数で計算するのでしたら、ピボットテーブルを検討してください。ピボットテーブルでできることは、要するにSUMIF関数とCOUNTIF関数ですから。

VBAでは注意が必要

"みんな大好き"VLOOKUP関数が高速になったのは、嬉しいです。よくぞやってくれました。すごいぞ＞MS、えらいぞ＞MS！ただし、注意しなければならないのは、VLOOKUP関数が高速になるのは、セルにVLOOKUP関数を代入したときだけです。VBAの、WorksheetFunctionでVLOOKUP関数を呼び出したときは速くなっていません。ここでは、次のようなコードで試してみました。

Sub Macro1()
    Dim i As Long, A(9999, 0) As String
    For i = 2 To 10001
        A(i - 2, 0) = WorksheetFunction.VLookup(Cells(i, 4), Range("A2:B100001"), 2, False)
    Next i
    Range("E2:E10001") = A
End Sub

上記の表に対して、セルにVLOOKUP関数を代入するのではなく、マクロの中からWorksheetFunctionで呼び出しています。やっていることが違うので一概には比較できませんが、それでもExcel 2016だったら、相当速くなっているはずです。もちろん、VLOOKUP関数の結果を、1セルずつ代入するなどという低速なことはしません。一度、結果をすべて配列に入れて、配列を一括代入しています。こうした、配列による高速化に関しては「VBA高速化テクニック」の「配列を使う」や「Line Inputは遅くない」をご覧ください。結果は次のとおりです。

回数	2013	2016
1	171.656	203.875
2	165.187	185.625
3	176.297	195.718
4	173.266	186.234
5	162.140	215.031
6	176.141	191.016
7	169.187	198.141
8	165.047	184.984
9	178.531	192.594
10	176.688	199.813
平均	171.414	195.303

う～ん、逆に2016の方がちょっと遅いですけど、まぁ、これくらいは大きな差ではないでしょう。要するに重要なことは、VBAのWorksheetFunctionからVLOOKUP関数を呼び出したときは、従来どおりのスピードだと。高速化はされていないと。そういうことです。

正直、この結果には疑問が残ります。WorksheetFunctionというのは、Excelの中に書かれているVLOOKUP関数を呼び出しているだけのはずです。Microsoftの公式資料にもそう書いてありましたし、それが合理的です。でも、セルの中で使うとメッチャ速くて、VBAから呼び出すとクソ遅いということは、もしかして「(Excelの内部に)VLOOKUP関数が2つある」のかもしれません。従来のVLOOKUP関数を高速化したのではなく、新しく鬼速いVLOOKUP関数を作ったと。そして、WorksheetFunctionからは、何らかの理由で鬼速VLOOKUPを呼び出すことができず、あいかわらずカメVLOOKUPを呼び出していると。もちろん想像ですし、今後どうなるかは分かりません。

VBAのWorksheetFunctionでVLOOKUP関数を呼び出すのは、セルに"数式"ではなく"数式の結果"を入れたいときです。でも、だったら、一度セルにVLOOKUP関数を代入して、その計算結果を同じセルに入れ直してやった方がいいです。

Sub Macro1()
    Dim i As Long, A(9999, 0) As String
    For i = 2 To 10001
        A(i - 2, 0) = WorksheetFunction.VLookup(Cells(i, 4), Range("A2:B100001"), 2, False)
    Next i
    Range("E2:E10001") = A
End Sub

ではなく

Sub Macro2()
    With Range("E2:E10001")
        .Value = "=VLOOKUP(D2,$A$2:$B$100001,2,FALSE)"
        .Value = .Value
    End With
End Sub

みたいに。やってみましょう。

回数	2013	2016	Value = Value
1	171.656	203.875	0.578
2	165.187	185.625	0.484
3	176.297	195.718	0.485
4	173.266	186.234	0.484
5	162.140	215.031	0.485
6	176.141	191.016	0.500
7	169.187	198.141	0.484
8	165.047	184.984	0.485
9	178.531	192.594	0.484
10	176.688	199.813	0.469
平均	171.414	195.303	0.494

ほらね。いずれにしても、VLOOKUP関数とMATCH関数が、これほど高速になったということは、そもそも根本的にExcelの使い方を検討した方がよいということですね。