VLOOKUP/XLOOKUPが異常なほど遅くなる危険なアンチパターン

公開日：2025-12-25　最終更新日：2025-12-25

VLOOKUP/XLOOKUPが異常なほど遅くなる危険なアンチパターン

エクセル関数に関する以下の調査アンケートを実施しました。

以下の2つの数式について、その処理速度を比較した説明として適切と思うものを選んでください。
=VLOOKUP(C:.C,A:B,2,0)
=XLOOKUP(C:.C,A:A,B:B)
※検索値は全て範囲内に存在します。
※ここでの「遅い」は、処理時間に1.5倍以上の差があることを指します。
・VLOOKUPの方が遅い
・XLOOKUPの方が遅い
・ほぼ同程度
・にゃんともいえない（PC環境による）

これを検証しようと、VLOOKUP/XLOOKUPの比較テストを繰り返しました。
この検証過程で不思議な現象が確認されました。
本記事は、その顛末を記したものです。
検証環境は、Windows11 + Microsoft 365

ページ内目次

検証に使用したデータとVBA
VLOOKUP/XLOOKUPのパフォーマンス比較テスト
パフォーマンス異常を発生させる条件
非スピル数式（結果が単一値）の場合は？
発生メカニズムの技術的考察

検証に使用したデータとVBA

A列が検索範囲：A000001～A200000
B列が戻り範囲：B000001～B200000
C列が検索値：B000001～B200000
それぞれ20万行あります。

このデータを検索値、検索範囲をそれぞれランダム順にしたり、昇順にしたりしながら、処理時間を実測しました。

計測に使ったVBA

Sub test()
    Dim s As Single, i As Long
    Dim ary(1 To 5, 1 To 2) As Double
    
    For i = 1 To 5
    
        Columns("D").Clear
        DoEvents
        s = Timer
        Range("D1").Formula2 = "=VLOOKUP(C1:C200000,A:B,2,0)"
        ary(i, 1) = Timer - s
        
        Columns("D").Clear
        DoEvents
        s = Timer
        Range("D1").Formula2 = "=XLOOKUP(C1:C200000,A:A,B:B)"
        ary(i, 2) = Timer - s
        
    Next
    
    Columns("D").Clear
    Range("F1:G5").Value = ary
End Sub

スピル数式（動的配列数式で、結果が複数セルになる数式）なので、数式を入れるプロパティは「.Formula2」にしています。

・Rangeオブジェクトに追加されたプロパティ・セルに数式を設定する場合・1次元配列を返すユーザー定義関数・2次元配列を返すユーザー定義関数・JAG配列を返すユーザー定義関数・スピルのVBAでの活用について

実行結果は、以下のように、F1:G5セルに秒数が出力されます。

VLOOKUP/XLOOKUPのパフォーマンス比較テスト

検索値：ランダム順、検索範囲：ランダム順

VLOOKUP	XLOOKUP
2.6406250	2.8593750
2.4843750	2.7656250
2.5156250	2.7890625
2.5390625	2.7734375
2.6093750	2.7421875

ほんのわずか、108.9%程度ですが、XLOOKUPの方が時間がかかっているようです。
とはいえ、20万件でこの差異は、実用上は全くないに等しいと言えます。
なお、XLOOKUPは検索モードや一致モードの柔軟な指定、戻り範囲の分離など多機能である分、内部的な処理のオーバーヘッドがVLOOKUPよりわずかに大きい可能性が考えられます。

検索値：昇順、検索範囲ランダム順

VLOOKUP	XLOOKUP
4.4921875	5.1562500
4.5234375	5.2109375
4.5234375	5.1640625
4.6562500	5.3906250
4.5468750	5.3984375

ほんの少し、115.7%程度ですが、XLOOKUPの方が時間がかかっているようです。
それにしても、検索値：ランダム順より明らかに時間がかかっています。

検索値：ランダム順、検索範囲：昇順

VLOOKUP	XLOOKUP
10.9453125	12.6953125
10.8437500	12.7500000
10.8828125	12.7265625
10.9062500	12.8281250
10.9062500	12.9062500

ほんの少し、117.3%程度ですが、XLOOKUPの方が時間がかかっているようです。
何故でしょうか、検索範囲：ランダム順よりかなり遅くなっています。

検索値：昇順、検索範囲：昇順

VLOOKUP	XLOOKUP
66.21679688	78.92919922
65.93896484	79.25585938
66.10205078	78.43896484
66.07373047	79.04687500
65.89208984	79.02294922

明らかに多大な時間がかかっています。
この時間のかかり方は異常です。
それと、やはりXLOOKUPの方が間違いなく遅いですね。

件数による時間比較：検索値：昇順、検索範囲：昇順

	平均時間
=VLOOKUP(C1:C20000,A:B,2,0)	0.740234375
=VLOOKUP(C1:C50000,A:B,2,0)	4.421875000
=VLOOKUP(C1:C100000,A:B,2,0)	16.916015630
=VLOOKUP(C1:C150000,A:B,2,0)	37.221679690
=VLOOKUP(C1:C200000,A:B,2,0)	65.793945310

数式で表すと、
時間 = 0.164 * 件数^2
これ以上件数を増やしたら再計算が終わらなくなってしまいます。

パフォーマンス異常を発生させる条件

前節の比較テストの全体傾向をみてみると、

平均	VLOOKUP	XLOOKUP
検索値：ランダム順、検索範囲：ランダム順	2.56	2.79
検索値：昇順、検索範囲ランダム順	4.55	5.26
検索値：ランダム順、検索範囲：昇順	10.90	12.78
検索値：昇順、検索範囲：昇順	66.04	78.94

昇順の度合いを増していくにしたがって、徐々に処理時間が遅くなっているのが分かります。
特に、検索値と検索範囲の両方が昇順で、基本的な並びが全て同じ場合に多大な時間がかかるようです。

先のテストでは、検索値と検索範囲が完全に一致していましたが、多少の違いくらいでは計算時間は全く変わりません。

この程度の違いでは、まったく計算時間は変わりません。

しかし・・・

1件でもエラーがあると爆速になります。

VLOOKUP	XLOOKUP
0.701171875	0.732421875
0.692382813	0.718750000
0.683593750	0.732421875
0.689453125	0.708007813
0.683593750	0.729492188

この結果は驚きです。

パフォーマンス異常を発生させる条件

・検索値が全て存在しエラーなし
・検索範囲の順番と検索値の順番が、かなり近しい状態

この2つの条件が重なると、極端にパフォーマンスが落ちて、とても遅くなってしまいます。

パフォーマンス異常を発生させずに、むしろ爆速になる条件

検索値なしのエラーデータが、なるべく先頭の方に存在すると爆速になります。
これは、データの並び順に関係なく、エラーデータが1件あるだけで処理速度が劇的に向上します（爆速になります）。

1件でもエラーがあれば爆速になります。

ただし、検索値なしのエラーデータは、データの最初の方にないと意味がありません。
データの最後の方にエラーがあっても速くはなりません。
つまり、エラー発生以降の処理が速くなるということです。

非スピル数式（結果が単一値）の場合は？

同じデータでスピルしない数式（結果が単一値）に変更して確認してみました。

テスト用VBA

Sub test()
    Dim s As Single, i As Long
    Dim ary(1 To 5, 1 To 2) As Double
    Application.Calculation = xlCalculationManual
    
    For i = 1 To 5

        Columns("D").Clear
        Application.Calculate
        DoEvents
        s = Timer
        Range("D1:D200000").Formula = "=VLOOKUP(C1,A:B,2,0)"
        Application.Calculate
        DoEvents
        ary(i, 1) = Timer - s

        Columns("D").Clear
        Application.Calculate
        DoEvents
        s = Timer
        Range("D1:D200000").Formula = "=XLOOKUP(C1,A:A,B:B)"
        Application.Calculate
        DoEvents
        ary(i, 2) = Timer - s
        
    Next
    
    Columns("D").Clear
    Range("F1:G5").Value = ary
    Application.Calculation = xlCalculationAutomatic
    Application.StatusBar = False
End Sub

20万行の数式再計算なので、VBAを少し変更して、より厳密に計測できるようにしました。

検索値：ランダム順、検索範囲：ランダム順

VLOOKUP	XLOOKUP
4.5390625	5.9921875
4.7187500	5.9687500
4.5078125	6.0781250
4.5234375	5.8828125
4.5625000	5.7890625

検索値：昇順、検索範囲ランダム順

VLOOKUP	XLOOKUP
6.5625000	8.2187500
6.4531250	8.2734375
6.6796875	8.1562500
6.4531250	8.1796875
6.5781250	8.4296875

検索値：ランダム順、検索範囲：昇順

VLOOKUP	XLOOKUP
3.0468750	4.2578125
3.0156250	4.3203125
3.0703125	4.2343750
3.1015625	4.4062500
3.1093750	4.3984375

検索値：昇順、検索範囲：昇順

VLOOKUP	XLOOKUP
79.7031250	91.3828125
76.9296875	94.8750000
80.1250000	95.9218750
81.1328125	92.7187500
79.8203125	94.9843750

先頭1件が検索値なしのエラーデータ

VLOOKUP	XLOOKUP
3.0625000	4.2734375
3.2343750	4.1640625
3.0234375	4.2265625
3.0781250	4.1875000
3.1171875	4.1562500

非スピル数式（結果が単一値）の総括

検索値と検索範囲の並び順の組み合わせについて、一貫した傾向は見られませんでした。
検索値と検索範囲が完全に一致している場合のみ、異常ともいえるほどに極端に時間がかかる事だけは間違いないようです。
各行ごとに数式が入っているので、データ順がこんなに影響するとは・・・
そして、
検索値と検索範囲が完全に一致しているデータで、先頭1件を検索値なしのエラーデータにしただけで速度が改善しました。
各行ごとに数式が入っているのに、1件のエラーがこんなに影響するとは・・・

同時に20万行に数式を入れるので、再計算も同時に行われるので、スピルと同様の影響があるようです。

発生メカニズムの技術的考察

以下は、生成AI（Gemini）の協力を得て、計測データに基づき計算幾何学およびアルゴリズムの観点から考察したものです。

技術的考察：検索関数のパフォーマンス異常と計算量退化

実測データにおいて、
計算時間 y が件数 x に対して y = 0.164 * x^2 という近似式で表されることは、計算量が O(n^2)（二乗時間）に増大（悪化）している
本来、Excelの検索エンジンはインデックスの活用により O(n) またはそれ以上の効率で処理するよう設計されていますが、特定の条件下でこの最適化が「負の連鎖」を引き起こしていると考えられます。

検索値と検索範囲の一致性によって計算時間が遅延する理由（O(n^2) への退化）
通常、Excelは同一範囲への繰り返し検索に対して「一時的なインデックス」を構築し高速化を図ります。
しかし、以下の要因でこの最適化が逆効果を生んでいると推測されます。
- インデックス再構築のオーバーヘッド：
  検索範囲が昇順で、かつ検索値も同じ順序で連続する場合、再計算エンジンが「データの依存関係や連続性」を過度に保守的に見積もる可能性があります。
  その結果、検索のステップごとに内部インデックスの整合性確認や再構築が発生し、処理コストが累積していると考えられます。
- リニアスキャン（逐次検索）への退化：
  最適化パスがバイパスされた結果、1件目の検索に n 回、2件目に n 回……という全件走査が発生します。
  これにより、データ量 n に対して全体の処理時間が n の二乗に比例して増大する「最悪計算量」のケースに陥っています。
1件のエラー（不一致）によって計算が劇的に高速化する理由
最適化パスの強制切り替え
検索の初期段階で不一致（エラー）が検出されることで、Excelのエンジンが「このデータセットは一様な規則性（または連続的な依存関係）を持たない」と早期に判断します。
これにより、停滞の原因となっていた複雑なインデックス維持ロジックを破棄し、より軽量で標準的な検索アルゴリズム（あるいはエラーキャッシュ）へ処理ルートが即座にスイッチするため、劇的な高速化が実現すると考えられます。

結論

この現象は、「良すぎる条件（昇順・全件一致）」がExcelの高度な最適化ロジックをかえって攪乱し、アルゴリズムを最悪の計算量（二乗時間）に陥らせるという、一種の最適化のジレンマと言える挙動です。

参考資料

Microsoft Learn: Excelのパフォーマンスを最適化するためのヒント
"Excel Tips for Optimizing Performance Obstructions" https://learn.microsoft.com/en-us/office/vba/excel/concepts/excel-performance/excel-tips-for-optimizing-performance-obstructions
概要:
Excelの再計算エンジンの仕組みや、計算速度を低下させる要因（ボトルネック）について解説した技術ドキュメントです。
本件に関連する重要点:
Office 365（バージョン1809以降）で、VLOOKUP、HLOOKUP、MATCH関数の完全一致検索が大幅に高速化されたことが明記されています。
高速化の仕組みとして、同一のデータ範囲に対して繰り返し検索を行う際、内部的に「一時的なインデックス」を作成して検索効率を高める機能が導入されました。
今回のパフォーマンス異常は、この「良かれと思って導入された最適化ロジック」が、特定のデータ並び順によって予期せぬ挙動を示したものと推測されます。
Microsoft Tech Community: XLOOKUPの発表 (2019)
"Announcing XLOOKUP"
https://techcommunity.microsoft.com/blog/excelblog/announcing-xlookup/811376
概要:
VLOOKUPの後継として導入されたXLOOKUP関数の機能と利点について、開発チームが公開した公式ブログ記事です。
本件に関連する重要点:
XLOOKUPが、検索範囲と戻り範囲を分離して指定する設計になっており、従来のVLOOKUPよりも柔軟かつ効率的な検索が可能であることが示されています。
特に「検索モード」パラメータ（第6引数）によって、二分探索（バイナリサーチ）を明示的に指定できる点が重要です。
今回の遅延問題に対し、この引数を使ってアルゴリズムを固定することが有効な回避策として推奨されています。

同じテーマ「エクセル関数応用」の記事

セル参照を戻り値とする関数
 REDUCE+VSTACKが遅い理由と解決策
 HSTACKは速い？遅い？実際に試してみた結果
 条件付きMEDIAN関数を作る｜LAMBDA関数で汎用〇〇IFSを実現
 複数列の直積（デカルト積、クロスジョイン）
フィボナッチ、トリボナッチ、テトラナッチ数列を1数式で作成
 表データから複数条件による複合抽出 (横AND/縦OR)
配列を自在に回転させる数式
 掛け算(*)を使わない掛け算｜足し算(+)を使わない足し算
 2段階の入力規則リスト作成：最新関数対応
VLOOKUP/XLOOKUPが異常なほど遅くなる危険なアンチパターン

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
エクセル全般
エクセル関数応用
VLOOKUP/XLOOKUPが異常なほど遅くなる危険なアンチパターン

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

エクセル関数応用
VLOOKUP/XLOOKUPが異常なほど遅くなる危険なアンチパターン