エクセル関数応用
【奥義】大量データでの高速VLOOKUP

Excel関数の解説、関数サンプルと高等テクニック
公開日:2013年5月以前 最終更新日:2020-07-16

【奥義】大量データでの高速VLOOKUP


大量データからのVLOOKUPを大量行に設定すると再計算がなかなか終わらなくなります…


そんな経験したことがある人は、少なからずいると思います、

そんな場合に、高速にVLOOKUPを実行する方法です。

高速VLOOKUPに使用するサンプルデータ

以下の表で説明します。

Excel エクセル 大量データ 高速VLOOKUP

Excel エクセル 大量データ 高速VLOOKUP

※Sheet1は、A列で昇順に並び変えておきます。
並べ替えては困る場合は、事前に順序番号を振っておき、後で並べ替えし直して下さい。

Sheet2のA列で、Sheet1のA列を検索し、B列を、Sheet2のB列に取り出します。
それぞれ20万件のデータを入れています。

高速VLOOKUPの数式

Sheet2のB2セルの計算式

行範囲を指定

=IF(INDEX(Sheet1!$A$2:$A$200001,
MATCH($A2,Sheet1!$A$2:$A$200001,1),1)=$A2,
VLOOKUP($A2,Sheet1!$A$2:$B$200001,2,TRUE),NA())

列全体を指定

=IF(INDEX(Sheet1!$A:$A,MATCH($A2,Sheet1!$A:$A,1),1)=$A2,
VLOOKUP($A2,Sheet1!$A:$B,2,TRUE),NA())

この記事を最初に書いた頃はExcelが2003でした。
その頃は、VLOOKUPの近似値検索で列指定をすると長い計算時間がかかりました。
その注意書きをしていましたが、近年のバージョンでは解決されているので列指定で構いません。

高速VLOOKUPの数式解説

少々長い計算式になっています。
列指定の短い方で解説します。

全体としてのロジックの流れ

MATCHで近似値検索する
MATCHの返り値を元に、INDEXで値を取得
INDEXの返り値が、本来の検索値と一致しているかの判定
ここまでが存在確認になります
一致している場合、VLOOKUPを近似値で実行
一致していない場合、#NA

もう少し簡単に書くと、
MATCHの近似値検索→INDEXで値取得→検索値と一致→VLOOKUPを近似値で実行

以下で、個別の関数を分解して説明していきます。

一番外側のIF関数の概要

IF(存在確認,VLOOKUP実行,NA())

最初に検索値の存在確認をしています。
MATCHで近似値の行を取得し、その行の値をINDEXで取得します。
その結果が、検索値と一致しているかを判定しています。NA()は、「#N/A」です。

存在確認

INDEX(Sheet1!$A:$A,MATCH検索値以下,1)=$A2

検索値以下のMATCH関数で探した行の値をINDEXで取得します。
その結果が、本来の検索値と同一かを判定しています。

近似値検索なので、同値がない場合でもMATCHが取得されてしまうので、
完全一致しているかの判定(=$A2)になります。

近似値検索は非常に高速に動作します。
しかし、あくまで近似値検索なので、検索値がない場合の判定が必要になるということです。

MATCH検索値以下
INDEX(Sheet1!$A:$A,MATCH($A2,Sheet1!$A:$A,1),1)=$A2
MATCH($A2,Sheet1!$A:$A,1)
第3引数が1がポイントです、近似値(以下)での検索をしています。
この場合は、検索範囲が昇順に並んでいる必要があります。

この数式を「MATCHの結果」として、次の説明に続きます。

存在確認の完成数式
INDEX(Sheet1!$A$2:$A$200001,MATCH($A2,Sheet1!$A$2:$A$200001,1),1)=$A2

MATCHの結果(何番目か)を使用し、INDEXで値を取り出します。
その結果が、本来の検索値と一致しているかの判定です。
MATCHが近似値検索なので、完全一致の行が存在しない場合は、直前の値が取得されるのです。

VLOOKUP実行

VLOOKUP($A2,Sheet1!$A:$B,2,TRUE)

検索値が存在している場合に、VLOOKUPを実行します。
第4引数がTRUEがポイントです、MATCHと同じく近似値(以下)での検索をしています。
TRUEは1としても構いません。
既に存在確認ができているので、結果として完全一致での検索と同じになります。

高速VLOOKUPの補足

Sheet1の並べ替えを元に戻す必要がある場合は、
Sheet2のB列(計算式を入れた列)は、値の貼り付けで、計算式を削除してから行ってください。

MATCH、VLOOKUPともに、近似値での検索の場合は、二分検索(といわれている)しているので、とても高速に検索されます。
そこで、検索範囲を並べ替えして、近似値のみで実現したのが上記計算式になっています。

上記の処理時間は、私の古いノートPCでも数秒で完了します。
普通にVLOOKUPしたら何分かかるかわかりません。

実際に、やり比べてみれば直ぐに実感できるはずです。



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

セルの参照範囲を可変にする(OFFSET,COUNTA,MATCH)
グラフのデータ範囲を可変にする
セルの個数を数える関数
【奥義】大量データでの高速VLOOKUP
数値を時刻に変換
関数のネスト方法
ワイルドカードが使える関数
グラフで特定の横軸の色を変更し基準線を引く
OFFSET関数 解説・応用・使用例
MATCH関数 解説・応用・使用例
選択行の色を変える(条件付き書式,Worksheet_SelectionChange)


新着記事NEW ・・・新着記事一覧を見る

VBA10大躓きポイント(初心者が躓きやすいポイント)|VBA技術解説(2024-03-05)
テンキーのスクリーンキーボード作成|ユーザーフォーム入門(2024-02-26)
無効な前方参照か、コンパイルされていない種類への参照です。|エクセル雑感(2024-02-17)
初級脱出10問パック|VBA練習問題(2024-01-24)
累計を求める数式あれこれ|エクセル関数応用(2024-01-22)
複数の文字列を検索して置換するSUBSTITUTE|エクセル入門(2024-01-03)
いくつかの数式の計算中にリソース不足になりました。|エクセル雑感(2023-12-28)
VBAでクリップボードへ文字列を送信・取得する3つの方法|VBA技術解説(2023-12-07)
難しい数式とは何か?|エクセル雑感(2023-12-07)
スピらない スピル数式 スピらせる|エクセル雑感(2023-12-06)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.繰り返し処理(For Next)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.ブックを閉じる・保存(Close,Save,SaveAs)|VBA入門
7.並べ替え(Sort)|VBA入門
8.条件分岐(IF)|VBA入門
9.セルのクリア(Clear,ClearContents)|VBA入門
10.マクロとは?VBAとは?VBAでできること|VBA入門




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


記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



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