VLOOKUPの高速化方法と速度比較(VLOOKUP/INDEX+MATCH)

VLOOKUPなどの参照系関数は実務でもよく使いますが、参照先が大きな表だったり、VLOOKUPの数が多かったりするブックは計算待ちが発生しがちです。ここではその待ち時間を少しでも短くする方法を解説していきたいと思います。

どんな理屈で動いているのか興味のない人は「そもそもなぜVLOOKUPは遅いのか?」をスキップしてベンチマーク結果から読み始めてください。

　　　

関連 Excelの再計算を抑制する方法
関連 Excel VBAで再計算を制御する方法

そもそもなぜVLOOKUPは遅いのか?

線形探索(VLOOKUPのFALSE)は遅い

リストの中から何かを見つける処理を「探索」と呼びます。

その中でも、VLOOKUPを普通に使った場合(引数がFALSEの場合)は「線形探索」という方法を使います。最も基本的な方法ではあるのですが、普通に使われる探索方法の中では最も遅い手段なんです(と言っても、バラバラに並んだデータではこれより速い方法は無いんですが)。

線形探索の処理手順

線形探索は値が見つかるまで、先頭から順番にセルの中身を見ていきます。

1. 範囲内の先頭のセルに移動する
2. 対象のセルから値を取り出して検索値と比較する
   1. 一致すれば指定した列から結果を取り出す
   2. 一致しない場合は次のセルに移動して2に戻る

平均すると、データ件数の約1/2の比較が必要になります(1万件のデータの場合、最短で1回、最大で1万回、平均すると約5千回の比較が必要)。

人間が重いと感じるシートは数千万～数億回の比較が発生しています。いくらコンピュータの計算が速いと言っても、これだけの比較をすればさすがに負担になってしまうんです。

比較回数の一例

縦軸:参照先のデータ件数 横軸:VLOOKUPの使用回数 表の中の値:平均比較回数

データ件数	1,000回	5,000回	10,000回
1万	500万回	2500万回	5000万回
5万	2500万回	1億2500万回	2億5000万回
10万	5000万回	2億5000万回	5億0000万回
50万	2億5000万回	12億5000万回	25億0000万回
100万	5億0000万回	25億0000万回	50億0000万回

検索対象をあらかじめ並び替えておくと速く検索できる(二分探索/近似一致)

対象データをあらかじめ並び替えておくことで大幅に高速化ができます。

先に説明したように、VLOOKUPやMATCH関数が遅いのは先頭から順番に比較していくからですが、あらかじめ並び替えておくと二分探索(バイナリサーチ)という方法が使えるようになります。ExcelではVLOOKUPとMATCHの近似一致検索で利用可能です。

二分探索の処理手順

並び替えがしてあるということは、ある値を比較したときに、正解がそれより手前にあるか奥にあるか分かります。二分探索はこれを利用して、1回の比較で対象を半分に絞り込んでいきます。

本のページを探すときに、適当なページを開いて「ここより前(後ろ)」とやって探していくのと同じ要領です。

1. 対象範囲の真ん中に移動する(偶数個の場合は手前の要素を選ぶ)
2. 対象のセルから値を取り出して検索値と比較する
   1. 一致すればあたりなので検索を終了する
   2. 検索値より小さければ、そこから手前をすべて外れにする
   3. 検索値より大きければ、そこから後をすべて外れにする
3. はずれになっていない範囲を対象領域にして1に戻る

元データ	1回目	比較後	2回目	比較後	3回目	比較後
1	1	1	1	外れ	外れ	外れ
2	2	2	2<3	外れ	外れ	外れ
3	3	3	3	3	3=3	3(当たり)
4	4	4	4	4	4	4
5	5>3	外れ	外れ	外れ	外れ	外れ
6	6	外れ	外れ	外れ	外れ	外れ
7	7	外れ	外れ	外れ	外れ	外れ
8	8	外れ	外れ	外れ	外れ	外れ
9	9	外れ	外れ	外れ	外れ	外れ
10	10	外れ	外れ	外れ	外れ	外れ

データが倍になっても比較が1回増えるだけ

線形探索と二分探索は、データ件数が増えると劇的に違いが出てきます。1回の比較で半分に絞り込むということは、データ件数が倍になっても比較が1回増えるだけなんです。

例えば、1万件のデータを2万件に増やすとします。線形探索では5千回も比較が増えてしまいますが、二分探索ではたった1回増えるだけです。これは、10万件でも50万件でも代わりませんから、件数が多くなるとどんどん差が開いていきます。

※「計算量」や「ループ回数」と呼ぶべきかもしれませんが、ここでは「比較回数」と表すことにします。線形探索とは1回の比較にかかるコストが違うので線形探索の1回と二分探索の1回は違うのですが、比較回数の増え方がまったく違うということを覚えておいてください。

線形探索と二分探索の比較回数

線形探索と二分探索、つまり、VLOOKUPのTRUEとFALSEの場合の比較回数を一覧にすると次の様になります(データ件数は二分探索に合わせて2倍にしていってあります。)。

100万件でも20回程度しか比較が発生しません。

データ件数	線形探索(VLOOKUP FALSE)	二分探索(VLOOKUP TRUE)
2	2	1
4	3	2
8	5	3
16	9	4
32	17	5
64	33	6
128	65	7
256	129	8
512	257	9
1,024	513	10
2,048	1,025	11
4,096	2,049	12
8,192	4,097	13
16,384	8,193	14
32,768	16,385	15
65,536	32,769	16
131,072	65,537	17
262,144	131,073	18
524,288	262,145	19
1,048,576 (Excelの最大行数)	524,289	20

それぞれの方法の速度比較

具体的な手順を紹介する前に、主な4つの方法のベンチマーク結果を貼っておきます。

ベンチマークの概要

各計算方法で共通する条件は次の通りです。

• 計測環境
  • Excel 2016 32bit版(Office365)
  • Windows10 Pro(Creators Update)
  • i5-7200U
  • 16GB RAM
• 計測内容
  • 対象データ: 1,048,575件×6項目(約100万件/項目数はID列を含む)
  • 参照回数: 5000件×6項目(同じくID列を含む)
  • 計測回数: 10回

Excel2007形式の行数上限が1,048,576行なので、今回の1,048,575件はヘッダーも含めると上限いっぱいまで使っています。

この条件で、これから紹介する4つの方法について速度を計測しています。

方法	詳細
VLOOKUP 完全一致	VLOOKUPにFALSEを指定した場合
MATCH 完全一致	INDEX関数とMATCH関数を使う方法でMATCH関数に0を指定した場合
VLOOKUP 近似一致(二分探索)	VLOOKUPにTRUEを指定した場合
MATCH 近似一致(二分探索)	INDEX関数とMATCH関数を使う方法でMATCH関数に1を指定した場合

完全一致は先の項目で説明した線形探索に当たります。近似一致は先に説明した二分探索を利用する方法です。いずれの場合も、検索値と完全に一致することを前提に関数を作成していますので、近似一致の場合は参照結果が完全に一致するかどうかのチェックが入っています。

測定には少し前に作ったExcelシートの計算時間測定プログラムを使いました。

関連 Excelシートの処理時間を測定するVBAコード

使ったデータ

参照結果を確認しやすくするため、次のようなデータを使っています。

商品番号	項目A	項目B	項目C
1	項目A1	項目B1	項目C1
2	項目A2	項目B2	項目C2
3	項目A3	項目B3	項目C3
4	項目A4	項目B4	項目C4
5	項目A5	項目B5	項目C5

検索値は=RANDBETWEEN(1, 商品番号の最大値)を使って生成しました。今回は範囲外の検索値は入れていません。ちなみに、対象データの中に含まれない検索値が多くなると、線形探索の方が不利になります。

計算速度の測定結果

計測した速度を比較すると次の様になります(数値の単位は秒)。

	VLOOKUP 完全一致	MATCH 完全一致	VLOOKUP 近似一致	MATCH 近似一致
1回目	87.30	15.64	0.76	0.73
2回目	89.85	15.64	0.75	0.84
3回目	88.85	15.79	0.79	0.82
4回目	88.23	15.51	0.85	0.83
5回目	88.90	15.78	0.83	0.75
6回目	88.63	15.31	0.73	0.75
7回目	88.25	15.30	0.71	0.72
8回目	91.08	16.04	0.71	0.81
9回目	89.63	15.38	0.73	0.79
10回目	89.90	15.40	0.80	0.79
平均	89.06	15.58	0.77	0.78

近似一致(二分探索)が1秒以下で圧倒的に速い

普通にVLOOKUPを使った場合、処理に1分30秒かかっています。再計算を手動にしないと使い物にならない処理時間ですね。

それに対して、高速化後はINDEX+MATCHの完全一致検索でも15秒程度です。再計算の発生頻度にもよりますが、十分実用範囲です。二分探索を使った場合よりも劣るものの、データに手を加えなくてもいいのがメリットです。

二分探索を利用したものは圧倒的に速いです。1秒かかっていません。参照結果のチェックの都合上VLOOKUPの方が参照回数が多く、遅くなると思ったのですが、MATCHの方と変わりない処理速度です。ちょっと癖があるので一概にこちらの方がいいとは言えないんですが、速度を優先する場合は第1候補として考えてください。

線形探索

基本のVLOOKUP

VLOOKUPはいろいろと不都合もあるのですが、単一の関数で他の表を参照できる基本的な関数です。このページを見ている時点で皆さん使えていると思うので、詳しい説明は省かせてもらいます。

VLOOKUP (検索値, 値を検索する範囲, 値を取得したい列, FALSE(完全一致)/TRUE(近似一致))

完全一致の場合は最後の引数をFALSE(完全一致)にしてください。Trueを完全一致使うためには、いろいろと条件をクリアする必要があります。

INDEX関数+MATCH関数

指定範囲から○番目のセルを取り出すINDEX関数と、検索値が指定範囲の何番目にあるのか調べるMATCH関数を組み合わせて使います。機能的にはVLOOKUPの上位互換になります。

INDEX関数とMATCH関数の詳しい使い方は別のページで紹介する予定です。

複数項目の参照が速くなる

複数項目を参照したい場合はよくありますが、VLOOKUPの場合は項目ごとに検索をはじめからやり直します。そのため、項目を増やしただけ2倍、3倍と時間がかかってしまいます。

INDEX関数+MATCH関数の場合、データの検索と取得を別のセルに分けることができます。そうすると検索処理を1回に抑えることができるので、概ね1/項目数の時間で処理が完了します。ベンチマークでも同様の結果が出ているのが確認できます。

MATCH関数の結果は必ず別のセルに入れて参照するようにしてください。単独のセルでINDEX(MATCH())という使い方をするとVLOOKUPと同じになってしまうので注意しましょう。

INDEX関数+MATCH関数の使い方

今回は速度アップが目的なので、2つの関数を別のセルに入れます。

アドレス	入力内容
A1	検索値
B1	=MATCH(検索値, 検索範囲, 0)
C1	=INDEX(取得したい値の入っている範囲, MATCH関数の結果)

# A1セル(検索値)
123

# B1セル(MATCH関数で検索処理)
=MATCH(A1,商品!A:A,0)

# C1以降(INDEX関数で値の取得)
=INDEX(商品!B:B,B1)
=INDEX(商品!C:C,B1)
=INDEX(商品!D:D,B1)

VLOOKUPにFALSEを指定するのと同じで、MATCH関数の第3引数は必ず0にしてください。

近似一致(二分探索)の使い方

それでは、本命の二分探索のやり方の説明に入っていきます。

データは「数値とテキストを分けて」昇順で並び替えておく

データは検索対象の列を基準に、昇順に並び替えておいてください。

並び替えをすると「次の並べ替えのキーにはテキスト形式の数値が含まれているため、正しくソートできません。操作を選択してください。」というメッセージが出る場合があります。

• 数値に見えるものはすべて数値として並べ替えを行う
• 数値とテキスト形式の数値を分けて並べ替えを行う

二分探索に使う場合は必ず「数値とテキスト形式の数値を分けて並べ替えを行う」を選んでください。

並び順を間違えると検索に失敗します

純粋な数値の場合は問題ないんですが、書式設定が文字列になっている数字や、アルファベットと数字が混在している場合は注意が必要です。

Excelの場合、書式が文字列になっている数字には2通りの並び替え方があります。複数のパターンで昇順に並び替えた例を次に示します。

数値	数字(文字列)	数字(桁揃え)	文字+数字	文字+数字(桁揃え)
1	1	01	item1	item01
2	10	02	item10	item02
3	2	03	item2	item03
4	3	04	item3	item04
5	4	05	item4	item05
6	5	06	item5	item06
7	6	07	item6	item07
8	7	08	item7	item08
9	8	09	item8	item09
10	9	10	item9	item10

※「数字(文字列)」は書式設定を文字列にした数字です。

二分探索の場合はこの並び順でないと正しく検索ができません。「数値」は直感通りの並び順になりますが、「数字(文字列)」は「10」が2番目に来ます。並べ替えをせずに「1から10まで順番に並んでいるからOK」と思っていると、なぜかうまく検索できない事態に陥ります。

「数字(文字列)」の場合、「数値に見えるものはすべて数値として並べ替えを行う」で並べ替えをしてしまうと「数値」の並び順になってしまいます。そうなるとうまく検索できませんから、必ず「数値とテキスト形式の数値を分けて並べ替えを行う」で並べ替えをしてください。

二分探索の完全一致検索がない

Excelには二分探索で完全一致検索をする関数が用意されていません。VLOOKUP関数もMATCH関数も、近似一致検索しかできません。近似一致なので、完全に一致するものがなかった場合に、それに近い値を正解として処理してしまいます。

そこで、完全一致が必要な時に二分探索を使う場合は、検索結果が完全に一致するかどうか判定する処理が必要になります。表の中にある検索値でしか参照しないのであれば判定処理を省略することもできますが、人間はミスをするものなのであまりおすすめしません。

例えば、参照先が文字列なのに、検索値に数値を指定すると結果がおかしくなります。次の状態だと検索値は数値として認識されます。

• 引数に直接指定するときにダブるクォートで囲っていない(正：VLOOKUP(“123”) 誤：VLOOKUP(123))
• 検索値の入っているセルの書式が文字列になっていない(標準や数値になっている)

VLOOKUPでの使い方

VLOOKUP (検索値, 値を検索する範囲, 値を取得したい列, TRUE)

二分探索の場合は、最後の引数がTRUE(近似一致)に変わります。

これに参照結果のチェックを付けたのが次の数式です。

アドレス	入力内容
A1	検索値
B1	=検索値=VLOOKUP(1列目を指定)
C1	=IF(一致したかどうか,VLOOKUP(),検索値が存在しない場合)

# A1セル(検索値)
123

# B1セル(存在判定)
=A1=VLOOKUP(A1,商品!A:A,1,TRUE)

# C1セル(値の取得)
=IF(B1,VLOOKUP(A1,商品!A:B,2,TRUE),NA())

1セルに詰め込むとこうなります。

=IF(VLOOKUP(A1,商品!A:A,1,TRUE)=A1,VLOOKUP(A1,商品!A:B,2,TRUE),NA())

ごちゃっとしていて分かりにくいですが、1つの値を取得するのに2回VLOOKUPをやっています。

1回目のVLOOKUP(B1セル)で「検索値」と「参照先の検索値の入っている列」を参照した結果を比較しています。検索値と同じものが存在すればTRUE、存在しなければFALSEが返ってきます。

この結果を基に(C1セル)、TRUEなら今度は値を取得するためにVLOOKUPを実行して、FALSEなら存在しない場合の処理をします。この例ではNA()を返していますが、必要に応じて0や””(空文字)を使ってください。

INDEX関数+MATCH関数での使い方

INDEX関数+MATCH関数も基本的な考え方は同じです。

=MATCH(検索値,検索範囲,1)

データを昇順に並び替えている場合は1を指定してください。

アドレス	入力内容
A1	検索値
B1	=MATCH(検索値,検索範囲,1)
C1	=検索値=INDEX(MATCHと同じ範囲,MATCHの結果)
D1	=IF(比較結果,INDEX(値の入っている列,MATCHの結果),検索値が存在しない場合)

# A1セル(検索値)
123

# B1セル(検索処理)
=MATCH(A1,商品!A:A,1)

# C1セル(一致判定)
=A1=INDEX(商品!A:A,B1)

# D1セル(値の取り出し)
=IF(C1,INDEX(商品!B:B,B1),NA())

C1セルの条件をD1セルに埋め込んで、IF(A1=INDEX(商品!A:A,B1),INDEX(商品!B:B,B1),NA())の様にすればC1セルを省略できます。

範囲を絞るのは意味がない

範囲指定をするときに「列指定(A:C)をすると遅くなるから具体的な範囲(A1:C10000)を指定すべき」と書いてあるページがいくつもあります。確認した限りではどちらも速度は変わりません。特に理由がない限り列指定か構造化参照を使ってください。

ほとんどの関数は使用範囲を自動認識する

VLOOKUPやSUMIF関数など、多くのExcel関数は最終行を自動的に認識するようになっています。列ごと指定したとしても、使われている範囲のみ処理対象になるので、速度には影響ありません。

実際に時間を測ってみても同じ

10万件のデータで時間を測ってみましたが、処理時間はまったく変わりませんでした。試したのは下の6通りの組み合わせです。

• 参照方法
  • 列指定(A:C)
  • 構造化参照
  • 具体的な範囲の指定(A1:C100000)
• 検索値
  • 検索値の最小値～最大値(=RANDBETWEEN(1,100000))
  • 検索値の最小値～最大値の2倍(=RANDBETWEEN(1,200000))

それぞれ10回測定した時間の平均は次の通りです。

検索値	列選択	構造化参照	具体的参照
最大値まで	23.21	23.23	23.26
最大値の2倍まで	35.40	35.42	35.50

範囲を絞るとミスが増える

具体的な範囲を指定してしまうとミスが発生しやすくなるのでおすすめしません。

• 新しいデータを追加したときに範囲外に出てしまう
• 行を削除すると範囲が縮んでしまう
• 絶対参照になっていなくてオートフィルで指定範囲がずれる

ゴミデータがあると遅くなる可能性も

ただし、下の方にゴミデータが入っているとそこまで検索しに行ってしまいます。検索対象のシートはなるべくデータだけ入力されている状態にしておきましょう。

Ctrl+ENDキーで使用されている範囲の最終セルを確認できます。余分な内容を消すには、意味のあるデータのとなりから適当なところまで行(列)を全選択して、右クリックで削除を選んでください。

とは言え、検索速度に影響があるほど下の方に何か入力されているという事は少ないと思います。

その他の小技

近似一致(二分探索)を使えば必要ありませんが、完全一致(線形探索)を使わざるを得ないときに少しでも速くする方法です。

これ以外にも思いつくものはあるんですが、そこまでするならVBAなり外部ツールなりを使った方がいいかなと思います。

検索しなくてもいい場合はIf除外する

Excelで計算ツールを作る場合に、VLOOKUPをあらかじめズラズラッと並べておくことがあると思います。その時に、空白セルなど検索する必要のない行を除外することで、無駄な参照を省くことができます。

=IF(検索値="",空白だった時の値,VLOOKUP())

単純に入力件数が不明でVLOOKUPを並べている場合、テーブルを使えば数式の入っている列も自動拡張されるので、テーブルが使える場合はそちらをおすすめします。

同じ検索値が続く場合は再利用する

参照先の表ではなく、参照元で同じ検索値が並んでいることがあります。その場合は、上の項目の検索結果を再利用することで、参照回数を減らすことができます。

=IF(A1=A2,B1,VLOOKUP())

よく参照する項目、新しい項目を先頭に持ってくる(線形探索)

冒頭で説明した平均比較回数というのは、あくまでも「表の並び順と検索値に偏りがない場合」にのみ当てはまります。

次のように、新しい項目を後ろに追記していくケースはよくあると思います。このような場合、よく参照する項目が後ろに偏りますから、先頭から順番に見ていく線形探索だと理論値よりかなり時間がかかるようになります。

• 現在在籍していない人も含めて社員・生徒をが登録順に並んでいる
• 廃盤になった商品も含めて商品が登録順に並んでいる

これを逆手に取れば、登録の新しいもの、参照回数の多いものを先頭に持ってこれば比較回数は少なくて済みます。

Excel関数にこだわらない

Excel関数のみで考えるとどうしても限界はあるので、他の手段も考えてみてください。

Excelとは別の知識が必要になってきますが、データベースを使うのが高速で、ある程度集計処理もやってしまえます。表の結合であれば、GUIが付いていて、Excelとの連携もしやすいAccessが一番お手軽ですね。会社によっては一緒にインストールされていることもあるので確認してみてください。データ集計はPostgreSQLが強いらしいです(うちの会社でインストールできないのであまり調べてません)。

単純に表をつなぎ合わせるだけでなくて集計を行いたい場合はBI(Business Intelligence)ツールの使用を考えてみてください。ExcelだとPower Pivotという機能が付いてきます。上位エディションにのみ付いてくるので、これから購入する場合は注意が必要です。

Power Pivotの含まれる製品

Excel単体で完結させるのならVBAを使うことになりますが、効率を考えてプログラムを組む必要があるので、これから始めようという人にはちょっと難しいと思います(あくまでも「ある程度前提知識が必要なので、この処理をすぐに自分で考えるのが難しい。」という話であって、他の処理であれば簡単なプログラムで劇的に効率化できることは多いです。)。

もし、VBAを使うのであれば、ADOを使ってExcelシートを擬似的にデータベースのテーブルとして扱う方法がおすすめです。この場合、簡単なSQLの知識が必要になります。

• データベース
  • Microsoft Access
  • Microsoft SQL Server
  • SQLite
  • MySQL
  • PostgreSQL
• BIツール
  • Power Pivot
• VBA