Excelが重い原因はその作り方にある！ ～シート一面「VLOOKUP」だらけ 編～

全国の事務屋さんが一番使用しているであろう必須アイテム"Excel"。会社のExcelトークというのはホントにネタが尽きないです。そして一番多く聞かれる話は「Excelが重い！」というもの。中には何か操作するとフリーズしてしまうというかなり重症なファイルもよく見かけます。

けど、よくお話を聞いてみると「他のエクセルはまだましだけど、このファイルは動きがマジで遅い」とファイルによってばらつきがあることが多いです。

この場合原因はパソコンではなく「その重いエクセルファイルの作り方」に問題があります。

今まで見てきた激重、フリーズに繋がる原因を見ていきましょう。の第2回目。今回の事例はこちら。

シート一面「VLOOKUP」だらけ

得てしてこんな流れのExcelで作った帳票出力システムでよく固まってるのではないでしょうか。

さらに、「何行データが入ってきてもいいようにVLOOKUPの式は多めにコピーしてある」となると尚更です。

「参照」という行為は結構リソースを食います。中でもVLOOKUPは他の関数に比べて結構リソースを食う命令文です。

もし仮に10列×500行にVLOOKUPが書かれていれば、セルの値が何処か変わるたびに10列×500行=5000カ所の再計算が実行されます。

私が見たなかの記憶で強烈だったのは150列×3000行×バリエーション違いで20シート=900万カ所の再計算が毎回実行され、更に参照元のデータ範囲は1万行ほど、という戦慄のExcelファイルですね。セルの値を1つ打ち換える度にCPU分散処理がフル回転します。

office2016以降、VLOOKUPの処理速度が劇的に速くなったそうですが、それでも計算する箇所が多いと時間は掛かります。

しかも、「データは昇順でなければならない」とか「参照値は検索値より右側になければならない」とか縛りもきついです。

実際どのくらい処理が遅いのかは、こちらのサイトが詳しく解説しています。また丸投げです。

VLOOKUPの高速化方法と速度比較(VLOOKUP/INDEX+MATCH) | 蒼月書庫 (aotsuki.org)

こちらのサイトのデータで先ほどの戦慄のExcelファイルで計算すると約450億回の比較計算がセルを打ち換える度に掛けられたことになります。

この手の帳票出力システムはデータベースソフトであるAccessの方が断然優位です。とっつきにくいソフトではありますが、覚えてしまえば、ERPや業務システム系のデータベースからも直接データ参照できたり、CSV取込をテンプレート化したりするので夢はいろいろ広がります。

ちなみにAccessでは上のフローはこのように置き換わります。

• 元データ用テーブルにデータを入力。若しくはCSVとか外部リンクでデータが入ってくる
• 「クエリ」を作成して必要な条件を設定してデータを絞り込む
• （印刷するなら）フォームを作って、コンボボックスを配置する。元データは先ほど作ったクエリのフィールドを設定する

流れは先ほどのExcelファイルと同じですが、Accessであれば何万行あっても数秒で処理が終わりますし、数人でデータ共有掛けている場合は同時に作業ができます。

1表から必要分を抜き出す、というVLOOKUPの代わりに使うだけならAccessでも難易度は低いと思うので、今後の効率化を考えると覚えておいて損ではないスキルだと思います。

この書籍は実務で応用できそうな技がとても分かりやすく書かれていておススメです！

それでもAccessの購入が許可されないとかExcelでなければいけない理由があるならば、VLOOKUPを「index関数とMatch関数の組み合わせ技」に書き換えるだけでも効果が上がります。

VLOOKUPより軽くて使える！index関数とMatch関数の組み合わせ技

=INDEX(参照したいデータがある範囲, MATCH(検索する値, 検索に使う範囲, 0), 表示させたいデータ範囲の何列目か？)

2つの関数を入れ子で使っているので見た目とっつきにくそうですが、VLOOKUPを使える方なら「設定する順番が違うだけ」なので、すぐに覚えられると思います。

利点1：何より処理が速い!

2倍速いとか100倍速いとかいろいろなサイトで実測実験結果を掲載しておりますが、VLOOKUPより倍以上処理速度が速い事は確かなようです。VBA組込の場合はVLOOKUPに軍配が上がる、という書き込みも見かけましたが、VBAで書く場合は別ロジックを考えた方が安全かつ速い気がします。（処理速度の面でもロジック組立の面でも）

利点2：データの並び順に左右されない

「わが社に代々伝わる数十年の入力データ」みたいな、既に入力してある膨大なデータ表ってあったりしませんか？そんな表をVLOOKUPが効かないからって昇順にソートし直したらめっさ怒られたとか外部リンク先のExcelがことごとくエラー起こして、方々からクレームの嵐とかで泣く泣くVLOOKUPに使うことを諦めるという場面、あるかと思います。

この方法であれば、行の並び順がどうであれ検索値にヒットすれば答えを返してくれます。

VLOOKUPで同じ書き方をすると3行目以降はエラーになります。なので、書き方は合ってるはずなのに検索されない!?というドツボにはまりやすい状態です。

利点3：検索列の左側のデータも参照できる

「わが社に代々伝わる数十年の（以下略）をVLOOKUPが効かないからって列の並びを入れ替えたらめっさ怒ら（以下略）

この方法であれば、列の並び順がどうであれ検索値にヒットすれば答えを返してくれます。

メジャーな関数なのにこういう融通が利かない裏事情がサクッと隠れているので「VLOOKUPってうまく使えない。難しい。」というイメージがまとわりつく原因のような気がします。MicrosoftさんがVLOOKUPをブラッシュアップしてくれると神関数に化けると思うのに。それまではindex,Match方式を使うことをお勧めします。