エクセル関数超技 | 【奥義】大量データでの高速VLOOKUP | Excel関数の解説、関数サンプルと高等テクニック



最終更新日:2015-03-09

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


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

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

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

以下の表で説明します。






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

Sheet2のA列で、Sheet1のA列を検索し、B列を、Sheet2のB列に取り出します。

それぞれ20万件のデータを入れています。

では、
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())


少々長い計算式になっています。

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

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

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

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

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

上記の存在確認

INDEX(Sheet1!$A$2:$A$200001,
MATCH($A2,Sheet1!$A$2:$A$200001,1),1)=$A2

近似値検索のMATCH関数で探した行の値をINDEXで取得します。
その結果が、本来の検索値と同一がを判定しています。
近似値検索なので、同値がない場合でもMATCHが取得されてしまうので、
完全一致しているかの判定になります。

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

MATCH($A2,Sheet1!$A$2:$A$200001,1)

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

INDEX(Sheet1!$A$2:$A$200001,MATCHの結果,1)=$A2

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

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

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


【注意】
範囲指定は、列指定(A:Bのような)はしてはいけません!

列指定した途端に時間がかかるようになってしまいます。


Sheet1の並べ替えを元に戻す必要がある場合は、

Sheet2のB列(計算式を入れた列)は、値の貼り付けで、計算式を削除してから行ってください。


MATCH、VLOOKUPともに、近似値での検索の場合は、二分検索(といわれている)しているので、とても高速に検索されます。

そこで、検索範囲を並び替えし、近似値のみで実現したのが上記計算式になっています。

上記の処理時間は、私の古いノートPCでも数秒で完了します。

普通にVLOOKUPしたら何分かかるかわかりません。

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




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

数値を時刻に変換
関数のネスト方法
ワイルドカードが使える関数
グラフで特定の横軸の色を変更する
OFFSET関数 解説・応用・使用例
MATCH関数 解説・応用・使用例
選択行の色を変える(条件付き書式,Worksheet_SelectionChange)

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

大量VlookupをVBAで高速に処理する方法について|ExcelマクロVBA技術解説(12月12日)
オセロを作りながらマクロVBAを学ぼう|ExcelマクロVBAサンプル集(11月26日)
ScreenUpdating=False時にエラー停止後にシートが固まったら|ExcelマクロVBA技術解説(11月21日)
データクレンジングと名寄せ|ExcelマクロVBA技術解説(10月20日)
SUMIFの間違いによるパフォーマンスの低下について|エクセル関数超技(6月17日)
条件式のいろいろな書き方:TrueとFalseの判定とは|ExcelマクロVBA技術解説(6月15日)
空白セルを正しく判定する方法2|ExcelマクロVBA技術解説(5月6日)
フルパスをディレクトリ、ファイル名、拡張子に分ける|ExcelマクロVBA技術解説(4月15日)
テキストボックスの各種イベント|Excelユーザーフォーム入門(4月9日)
フォルダ(サブフォルダも全て)削除する、Optionでファイルのみ削除|ExcelマクロVBAサンプル集(4月4日)

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

1.最終行の取得(End,Rows.Count)|ExcelマクロVBA入門
2.RangeとCellsの使い分け方|ExcelマクロVBA入門
3.変数とデータ型(Dim)|ExcelマクロVBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|ExcelマクロVBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|ExcelマクロVBA入門
6.定数と型宣言文字(Const)|ExcelマクロVBA入門
7.マクロって何?VBAって何?|ExcelマクロVBA入門
8.CSVの読み込み方法|ExcelマクロVBAサンプル集
9.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
10.ひらがな⇔カタカナの変換|エクセル基本操作



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

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


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

    ↑ PAGE TOP