Excel VLOOKUP高速化 10分→2秒

初心者さん向けのExcelVBAの記事を書いてます。

興味のある方は是非ご覧ください。

「やさしいExcelVBA」

日々Excel VBAで簡易ツールを独自に作成しています。
本日はExcelのVLOOKUP関数の高速化について、書きたいと思います。

1.VLOOKUP関数とは

Excelの中でも、使用される頻度の高い関数の一つかなーと思います。
基本的な使い方は下の表の様にたくさんあるデータの中から、A列の検索したい値とD列の一致した行のE列(右にあれば離れた列でもOK)にある値を抽出します。

結果を出力したいセルに以下の様な数式をB1に入力します。
=VLOOKUP(A2,$D$2:$E$100,2,0)
この数式をオートフィルでデータのある最後の行までコピーすると、データの価格が抽出出来ます。
ここまでは簡単で、使える人も多いのではないでしょうか。

2.VLOOKUP関数のデメリット

非常に便利な関数なのですが、データ量が多いとどうしても処理時間や、再計算に時間がかかってしまいます。
データ量が10万件×10万件・・・、それ以上となると10分、20分固まるなんて事があります。

それがパターン別だったり、何回も行う必要があるとき・・・。

膨大な時間がかかってしまいますよね。

VLOOKUP関数に限らず、COUNTIFやSUM IF関数なども同様です。

3.自作で高速VLOOKUP関数を作ってみた

自己紹介で書いていますが、僕はちょー面倒くさがり屋なので毎回数式を打ったり、数分、数十分待ったりが大嫌いなので、自作しみました。

そちらをご紹介いたします。

自分のExcelで常に使えるように「アドイン化」しているため、みなさんと画面が少し違うかもしれませんが、最後まで読んで頂けると同じ環境をご提供できます。

自分が働いているチームにも提供しています。

まず、実行結果も併せてご紹介するために、10万行×10万行のサンプルデータを用意しました。

↓の表の右下に表示されているように、A列に10万行の商品列があります。

↓の表の右下に表示されているように、D列に10万行の商品列とE列に価格列があります。(※念のため乱数で並び替えしてD列はぐちゃぐちゃに並べています。)

普通にVLOOKUP関数を使用してB列にE列の価格を反映させようとした場合、自分の環境では10分かかります。

では早速、関数を実行します。

すぐ使えるようにクイックアクセスツールバーに登録しているので、○で囲んだアイコンをポチっと押すだけです。

(3つありますが、他はVLOOKUP以外の関数です。)

↓図の様に、フォームが立ち上がります。

立ち上がったら、検索範囲のテキストボックスがアクティブになっているので、検索したい範囲を選択します。

A2～A100001まで選択してOKですし、A:Aと列指定してもOKです。(列全体指定の場合は若干遅くなります。)

もちろんA1000～A10000と調べたい範囲のみ選択してもOKです。

一部範囲の場合でも出力結果も同じ行範囲に出力されます！
選択すると以下の図の様になります。

次に参照範囲を選択します。

参照範囲のテキストボックスをアクティブにしてから範囲を選択します。

普通のVLOOKUP関数は一番左の列から抽出したい列を数値で指定しますが、このツールは一番左を参照列、1番右を抽出列と判断します。

参照列ももちろん列指定でもOKです。

また、参照範囲は別のExcelファイルでも大丈夫です。

ただし、検索範囲と出力列はフォームを開いたファイルに依存します。

最後に結果を出力したい列をアルファベットで指定します。

小文字でも、AAなどかなり離れた列でも問題ありません。

今回はB列にしました。

3つをそれぞれ指定したら集計ボタンを押します。

指定した出力列に結果が表示されます。

2秒です！

一度も数式入力などせず、ポチ、ポチ、ポチで上記データ量でも2秒で計算結果が出ました。

ちなみに、もし間違えてデータの存在する列を指定してしまった場合・・・。

ちゃんと、アラートが表示されるので、間違えて消してしまう事もフォローしています。

なぜこの機能を付けたかというと、VBAで出力してしまった結果は「戻る」が出来ないんですよね・・・。(致命的・・・)

なので、間違えないようにアラートを出す必要がありました。

さらに重要なデータの場合は使用する前に上書き保存するとかの保険も推奨します。

今回は実行結果を表示させていますが、提供版は表示されません。

如何でしたでしょうか。

どうしてもExcelって手離れ出来ないソフトになっています。

そんな中、単純作業で高速に処理できる環境は非常に助かります。

さらには、関数を苦手と思っている人も関数という概念すら必要なく

使用できるようにしています。

工夫一つでこんな事も出来るんです。

面白いですよね。

コードも書かず、ファイルをダウンロードしていただき、設定するだけでご使用いただける環境を構築できます。

設定方法は記載致しますので、少しでも使ってみたい！効率化したい！と思って頂けましたら、ご購入

して頂けると幸いです。