エクセル再計算：高速化
世の中、AIだのIOTだの言われて久しいが、実際のところ現場ではやはりWord やExcelが広く使われているのではないかと思う。特にExcelはごく小規模のデータベースとして使われることが多い。一つにはとっつきやすいということ。ほとんどの人は、実用業務では数千行のデータを使うこともほとんどなく、また、対外の会社や行政機関でもデフォルトになっているので、使い手が多いというのもポイントの一つであろう。特に会社上げてのシステムを構築するわけではなく、ある部署の特定の業務に特化した小規模システムの場合、構築からメンテまでその部署で行う必要があり、誰でも使えるというのは重要なことだと思う。
エクセル側もこれを意識しており、外部データを読み込みこれを加工して使える簡易版のクエリー機能（Power Queryが装備されている。
これだけの大きなデータを扱えるようになると、VlookupやIndexを使った検索をする場合にはかなり工夫が必要になる。特にデータ数が多くなり、検索式が多くなる場合、作業に支障をきたすほど、場合によっては固まって動かなくなることも。
そういった場合に高速化する方法は以下に紹介されています。
この中でも言われていますが、できるだけ検索する範囲を少なくすることが最も重要なことです。
そのためには、上記のサイトではOffset関数はできるだけ使わないようにとなっていますが、Offset関数を上手に使えば、ぐっと検索時間を短くすることができます。
まずは、実例ですが（仕事上のファイルなので、そのものをお見せできません）、ある日それまで作っていたエクセルシートが落ちました。そして、開かなくなりました。幸い、古いバージョンから開けたので事なきを得ましたが、まず、ファイルがデーター量が多すぎて重くなりすぎていました。
そこで、メインのデータを独立させて、先ほど紹介したPower Query で接続しました。これで、開けないということはなくなりましたが、再計算にすごく時間がかかるとい症状は残ったままでした。それまでも、遅くなってきたなという感覚はあったので、自動計算させないようにしていたので、気が付かないうちにだいぶ遅くなっていました。
そこで、以下のコードを使って測定すると、7.8秒程度もかかっていました。毎回の再計算でそこまでかかられると、イライラして仕事になりません。
そこで、いくつかの方法を試しました。
1）Match（）で行番号を習得する。
同じ行に含まれるデータを参照する場合に、これまではすべてのセルでMatch関数を使って、行番号を取得させていましたが、これを改め、まず対象の行を求め、その行番号を記載したセルをIndexで参照させるようにしました。
これにより、2秒程度改善しました。
2）検索範囲を絞る。
データはあるカテゴリーごとにソートされていたので、そのカテゴリーの最初のデータと最後のデータのある位置を求め、その範囲をOffset関数で絞って検索させる方法です。結果的にはこれはてきめんで、これにより再計算時間は0.55秒になりました。これなら、耐えられる。
具体的には、最初のデータはmatch(検索語, テーブル名[#列名],0)で求めます。最後のデータは、これが最適かどうかはわかりませんが、以下のような式を使います。
{=index(検索範囲, match(large((検索範囲=検索内容）⋆1*Row(検索範囲）, 1), Row(検索範囲), 0), 1)}
ややこしいですね。｛｝は配列指定で、｛｝を除いてセルに打ち込んだ後、Shift+Ctrl+Enterで指定します。こちらの詳細はこちら。
範囲を求められたら、offset関数を使います。
Offset(基準のセル、行数、列数、高さ、幅）
基準のセル：検索対象列の最初のセルを指定します。
行数：基準のセルから何行（縦方向）にずらすかを指定します。ここでは先ほど求めた範囲の最初の行数を指定します
列数：基準のセルから何列（横方向）にずらすかを指定します。ずらす必要はないので、０を入れます。
高さ：範囲の行数を指定します。ここでは先ほど求めた検索対象範囲の最初と最後の差に１を加えた値を入力します
幅：範囲の列数を指定します。ここでは一行なので、１を入力します。
このOffset関数をMathの検索範囲に指定します。基準セルや幅を調整することでindexやvlookupの範囲に指定することもできます。
難点は式が複雑になってしまい、他人（自分もか。。）の可読性が極端に悪くなることです。
それでも、この速度改善は捨てがたい。
よろしければサポートをお願いします。