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

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

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

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

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

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

vlookup関数は、近似値検索（true）を使う

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

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

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

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

詳細は、下記をご覧ください。

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

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

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

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

実際の例は、下記をご覧ください。
圧倒的に計算速度が変わることがわかると思います。

セル範囲の指定で「$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)

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

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

さて、上で挙げた１～３までの計算式ですが、その再計算が遅くなる理由がわかるでしょうか？

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

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

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

このような、表全体を1行1行見ないといけない処理をしていると、動作速度は遅くなります。

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

　

そして、「効率が良い手段」を選ぶと、表が大きくなっても、実行時間はあまり変わらなくなります。

例えば、vlookup関数の「完全一致検索」は、表が大きくなると実行時間が明らかに遅くなります。

ところが、vlookup関数の「近似値検索」にすると、表の大きさが大きくなっても、実行時間はほとんど変わりません。

実行時間のイメージとしては、仮に、表のデータ量が1,000倍になったとき、

  • vlookup関数の「完全一致検索」は、動作にかかる時間は1,000倍になる
  • vlookup関数の「近似値検索」だと、動作にかかる時間はほとんど変わらない

くらい、動作速度に、顕著な違いが出ます。

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

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

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

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

indirect、offset関数は、再計算を遅くする原因となります。

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

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

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

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

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

  • indirect　→ choose
  • offset → index

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

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

ほとんどの場合は、どうしようもありませんが、


に書かれていることを実行すると、少しはマシになるかもしれません。

まとめ

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

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

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

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

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

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

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

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

再計算の高速化offset関数, 相対参照, 絶対参照, 動画, sum関数, countif関数, 近似値検索, 完全一致検索, ソート, indirect関数, vlookup関数, 並べ替え