お客様より「Excelファイルが遅くて困っている」という相談を受けました。
試行錯誤の結果、XLOOKUPの範囲指定を変更したら改善しました。
それをうけて、今回XLOOKUP関数の速度について検証をしてみました！
1.XLOOKUP関数の範囲指定とは？
XLOOKUP関数は下記のように記載できます。
(Excel関数の講座で使用しているスライドより）
「$B$2:$B$30001」や「$C$2:$C$30001」が範囲指定になります。
この場合はN5セルと同じ値をB2からB30001の間で検索して、C2からC30001の間で該当する場所の値を返します。
範囲指定は以下のようにも書けます。
=XLOOKUP(N5,B:B,C:C)
「B:B」や「C:C」が範囲指定に該当します。
この場合はN5セルと同じ値をB列で検索して、C列で該当する場所の値を返します。
私は後者の行を指定する方法をオススメしてきました。
いちいち、セルを指定するよりも検索元のデータが増えても対応できるので後者の方が便利だと思っているからです。
しかしながら、今回はこの行を使って指定したのが遅さの原因でした。
問題のお客様のファイルは、セルを指定した方法に変更した結果、数分待ったものが一瞬で実行されました。
2.問題のファイルの再現
問題のファイルはXLOOKUPの関数の数が推定で約20,000個使用していました。
<再現のためのファイル>
お客様のファイルでの時間の計測は行っていないので、ここからは自分のPCで再現したものになります。
検証のためのファイルは下記のように作りました。
・検索行数200行 × 36か月 × 4シート ＝ 28,800個のXLOOOKUP関数
・XLOOKUPの検索先は別のファイルで1か月ごとにシートが異なるため、36シート作成
【XLOOKUPの時間の比較】
＜列で指定＞
=XLOOKUP(B2,[元データ.xlsx]1′!$B:$B,[元データ.xlsx]1′!$AD:$AD,”-“,0)
＜セルで指定＞
=XLOOKUP(B2,[元データ.xlsx]1′!$B1:$B2000,[元データ.xlsx]1′!$AD1:$AD2000,”-“,0)
VBAマクロを用いて再計算の秒数を図ったところ、下記のようになりました。
|
|
列で指定 |
セルで指定 |
|
1回目 |
0.203 |
0.043 |
|
2回目 |
0.207 |
0.102 |
|
3回目 |
0.207 |
0.035 |
|
4回目 |
0.188 |
0.102 |
|
5回目 |
0.125 |
0.043 |
|
6回目 |
0.203 |
0.098 |
|
7回目 |
0.145 |
0.051 |
|
8回目 |
0.203 |
0.098 |
|
9回目 |
0.156 |
0.039 |
|
10回目 |
0.211 |
0.098 |
|
平均 |
0.185 |
0.071 |
改善前の平均が0.185秒に対して、改善後の数値の平均はなんと0.071秒！！！
こうして数値にするとなんとなく、速くなったような気がしますが、正直、体感ではわかりませんでした。
お客様のファイルはもっと複雑だったのと、ファイルの参照のネットワークやパソコンのスペックなど他の要因も重なったのかもしれません。
3.まとめ
XLOOKUPの列指定は便利ですが、多用するとファイルのパフォーマンスの悪化の原因になるかもしれません。
もし、これを読んでくださった方のお役に立てば幸いです。
Excel関数の講座はこちらになります。
もしよろしかったら、こちらをご参照ください。
