この関数がエクセルの再計算を遅くする。エクセル高速化テクニック

エクセルの関数をたくさん使うと、再計算に時間がかかってしまうのは仕方がありません。
でも、少し、元データや関数を工夫することで、再計算を高速化できる場合があります。

再計算を高速化する方法として「計算量を減らす」「再計算の邪魔をしない」の2つがあります。

※下記を再生しても音は出ませんので、音が出せない環境でもご安心ください。

エクセル関数の高速化テクニック(計算量対策)

エクセルで計算速度が遅くなる第一の要因として、
「エクセルの計算量が多くなってしまう計算式」
を使っている場合があります。

vlookup関数は、近似値検索(true)を使う

vlookup関数で、
完全一致検索(4つめの引数にfalseを入れる方法)を使うと、
動作スピードは一気に遅くなります。

vlookup関数を多用する場合には、
元データをソート(並べ替え)したうえで、
近似値検索を使いましょう。

完全一致検索をしたいんだ!という場合でも、
近似値検索を2回使うと、完全一致検索ができます。


例えば、
私の手元で、
実際に試してみると、
参照元・参照先がそれぞれ約80万行あるvlookup関数
(=vlookup関数を80万行入れた状態)でも、
数十秒で再計算が完了します。

詳細は、
vlookup関数が遅い理由は、あなたの使い方が悪いからをご覧ください。

countif関数、sumif関数の乱発は避ける

countif関数、sumif関数もかなり時間がかかる関数の一つです。

countif関数、sumif関数を乱発している場合、
他の手段で計算できないか、検討しましょう。

この場合も、多くの場合は、
元データをソート(並べ替え)することで、
もっと、時間のかからない単純な計算式に
置き換えられることが多いです。

実際の例は、
重複を除いた件数の集計方法3選をご覧ください。

圧倒的に計算速度が変わることがわかると思います。

セル範囲の指定で「$X$Y:XY」の形を使わない

計算式を入れる際に、、

  • C3セルに「=sum($A$3:A3)」と入れてC100セルまでコピー
  • C3セルに「=countif($A$3:A3,1)」と入れてC100セルまでコピー

といったような、
セル範囲の始めのセルを動かさず、終わりのセルだけを動かす
ようなセルの指定がよくあります。

こういう場合は、
たいてい、もっと時間がかからない計算式の入れ方があります。

今回の場合であれば、

C3セルに入れる計算式を、
次のような計算式で代替可能です。

=sum($A$3:A3)

=A3+C2
=countif($A$3:A3,1)

=if(A3=1,C2+1,C2)

こういうちょっとした工夫で、
再計算が爆速になります。

なぜ、これらの計算式が遅いのか?

さて、
上で挙げた1~3までの計算式ですが、
その再計算が遅くなる理由がわかるでしょうか?

それは、
参照先のセル全てを見ないと計算ができない処理だから
です。

例えば、
sum、countif関数については、
「常に」最初の行から最後の行までを見ないといけません。

vlookup関数の完全一致検索も、
該当する行がないか、
最初の行から最後の行まで「順番に」見る必要があります。

このように、
vlookup(完全一致)、sum、countif関数等は、
表の大きさに「比例して」実行時間がかかってしまう関数なのです。

実は、
コンピュータの処理としては、
表の大きさに「比例して」処理が増えるような処理は、
「効率が悪い」ケースが多いのです。

そして、このような場合には、
もっと効率が良い手段があるケースがほとんどです。

例えば、
vlookup関数の「完全一致検索」は、
表の大きさが1000行から1,000,000行に増えると、
実行時間は1,000倍(=1,000,000÷1,000)になります。

ところが、
vlookup関数の「近似値検索」だと、
表の大きさが1000行から1,000,000行に増えても、
実行時間は2倍程度にしかなりません

全然効率が違いますよね?

このように、
うまい処理を選べば、
表の大きさが大きくなっても、
実行速度をほとんど変えずに済むことが多いのです。

エクセル関数の高速化テクニック(再計算対策)

エクセルでは計算量自体は大したことはないが、再計算の効率が悪い関数というのも存在しています。
こういう関数を使うと、エクセルの再計算が遅くなる可能性があります。

indirect、offset関数はできるだけ避ける

よほどエクセルに慣れた人でないと
使わないと思いますが、
indirect、offset関数も、
再計算を遅くする原因となります。

というのは、
エクセルで再計算を行う場合、

普通は、
「変更されたセルに関係ありそうなセル」が変更された場合だけ、
再計算が行われます。

ところが、
indirect関数、offset関数を使うと、
その式が入力されたセルは、常に再計算がされてしまいます。

その結果、
これらの関数の使用箇所が多くなると、
再計算にも、多くの時間がかかるようになります。

ですから、
indirect関数、offset関数は、
どうしても必要なとき以外は、使うのを止めましょう。

もし、
これらの関数を使いたい場合でも、
下記のような類似関数で代替できるのであれば、
それらを使ったほうがいいです。

  • indirect → choose
  • offset → index

使っているPCが古い場合には設定の見直しをしてみよう

使っているPCが古い場合には、そもそもパソコンのハードウェアが足を引っ張っている可能性があります。

ほとんどの場合は、どうしようもありませんが、
古いPCで「エクセルが重い」症状を解決する4つの設定 まとめ
に書かれていることを実行すると、少しはマシになるかもしれません。

まとめ

エクセルの再計算の速度は、入力する計算式次第で大きく変わります。

できるだけ計算量の少なさそうな、再計算時に邪魔にならない関数・計算式を使って、再計算を高速化させましょう!

エクセル基礎講座 「無料」動画マニュアル

「経理事務のためのエクセル基礎講座(初級編)」(動画マニュアル 総収録時間162分)を無料プレゼント中です!

このマニュアルで解説していることを一通り学べば、経理事務を行う上で最低限必要となる知識が得られます。

ご登録者の方には、合わせて、公認会計士が実体験を通して身に付けたエクセルを使う技をメールにてお伝えしていきます!

無料動画講座 登録フォーム

※ご登録頂いたメールアドレスに、エクセルを使いこなすための情報を配信するメールセミナー「エクセル倍速講座」も合わせて配信させていただきます。