自分のExcelで使用しているvlookupが遅い場合の解決策として、検索方法（４つ目の引数）にTrueを指定するというものがあります。
しかし、「あいまい検索」をすることになり、結果が大きく変わってしまう可能性を秘めている方法ですので、あまり使用したくありません。
別解として、検索列をソートするというのがあります。
こちらは悪くない方法だと思うのですが、頻繁に変更が入るテーブルの場合、少しメンテナンスが面倒だったりします。
それよりも簡単な方法で、「テーブルとして書式設定」を使用して速度を上げるという方法があるので紹介したいと思います。
テーブルとして書式設定とは？
テーブルとして書式設定とは、範囲内にあるセルをテーブルとして扱うことで一括で書式を設定する事ができる機能です。
この機能を使用することで、その範囲は１つのテーブルとして扱われることになるので、色々メリットがあります。
使い方ですが、まず対象のテーブルをドラッグします。
次にホームタブにある「テーブルとして書式設定」を選択します。
自分が好みのテーブルの書式を選択します。この書式は自分の好みに合わせてカスタマイズすることも出来るので、レイアウトが制限されてしまうこともありません。
テーブルをクリックするとテーブルデザインタブが新しく出現します。
ここでテーブルの名前を変更することが出来ます。あとは、レイアウトなどを変更したりピポットテーブルを作成したりすることが出来ます。
テーブル名を指定してvlookup出来るようになる
上記のテーブル書式設定をvlookupで参照される先のテーブルに設定することで、vlookupの速度が劇的に早くなります。
「書式を設定しただけで早くなるの？」
と疑問に思われる方も多いかと思いますが、テーブル書式設定をすることでvlookupにおける範囲の設定方法を変更することが出来ます。
その範囲設定方法が速度改善につながっているようです。（何故そうなるのかは考えたことがないですが…）
自分が作成したテーブルが「table」という名前だったと仮定すると、vlookupを以下のように記載します。
=VLOOKUP(1,table,4,FALSE)引数の２番目をテーブル名としています。
この指定をすることで自動的に、テーブル全体がVLOOKUPの検索範囲となり、１列目のNo列がキー列になります。
テーブル書式設定で保守性もアップする
この方法の良いところは、仮にテーブルの行数がアップした場合に自動的に範囲を拡張してくれることです。
普通にVLOOKUPを書いた場合、以下のような記載になるかと思います。
=VLOOKUP(1,$A$1:$D$20,4,FALSE)A1～D20を選択範囲として指定しています。もし、テーブルの21行目にデータが追加された場合は、vlookupの式を変更しなければいけません。
それを防ぐために…
=VLOOKUP(1,A:D,4,FALSE)という書き方にすることが多いのです。つまりA～D列を全て選択範囲にしてしまうということです。
ただ全行が対象になるため、気をつけないと、意図しないデータまで検索範囲としてしまう可能性があります。
一方でテーブル書式設定では行数を増やすと、Excelが自動でテーブルの範囲を広げてくれるので上記のような考慮が全く不要になります。
また、列数の指定について以下のように書くことが出来ます。
=VLOOKUP(1,table,COLUMN(table[値段])-COLUMN(table[No])+1,FALSE)COLUMN(table[値段])で上記画像の値段列の列番号５が返却されます。
また同様にNo列の値を取ることも出来るので、減算することで列の相対位置を取得することが出来ます。
仮に列の入れ替えなどが発生した場合も、自動で対応できるので保守性が高い書き方と言えるかと思います。
（関数は長くなりますが、列名を指定したほうが直感的にわかりやすいというのもあります。）
その他の方法
色々と調べていて、目からウロコだったのが下記の記事で紹介されている方法です。
代わりにINDEX()とMATCH()を使ってこのように書いてください。
=INDEX(在庫[在庫数], MATCH(“オレンジ”, 在庫[品名], 0))
これだけで、再計算が軽くなりメンテナンスしやすくなります。
https://qiita.com/sakaikosuke/items/d6f78140bd8f05483772
INDEXとMATCHを指定する方法は、表を逆に検索する時とかによく使うテクニックですが、なんとなく遅くなるイメージがありました。
しかし実際には早くなるようなので、こちらを使用するのも手ですね。
まとめ
- 参照したい表にテーブル書式設定をすると範囲指定方法が変更となり、速度がアップする
- 更に保守性もアップするのでかなりオススメです
- INDEXとMATCHを使用する方法も速度アップになるようなので使用するのも手です
コメントを残す