Excelのパフォーマンス改善（重たい処理の高速化）方法

むらかみ

2024年3月1日 18:29

※この記事は中級者～向けです。
また例として想定しているシチュエーションは、「（飲食店や雀荘などで）1台のPCをみんなで使う」、「会社の中でその人だけが触る集計用ファイル」といったものです。

みんなで使うファイルはシンプルに

１．検索用の連番取得

INDEXや〇LOOKUP系などで、重複する値を全部検索したいときに使うアレ。顧客名簿に登録されている3人の村上を全部抜き出すとか。

この記事を読んでいる層ならば特段説明の必要はないと思うが、数式は上記の通り。
そしてこの手の処理が重くなる原因は検索関数部分ではなくて、むしろ**連番側にある。**

・遅いパターン
検索用の連番生成にMAX関数を使っていると遅くなる。

探索セル数は1行目は1個、2行目は2個・・・となる。行数がnのときにn(n+1)/2個のセルを探索することになるので、例えば10000行なら約5千万個の数値を探索することになる。

MAXは指定範囲の全セルを探索するので、計算量はO(n^2)となり、PCのスペックや環境にもよるが、5000~20000行あたりで目に見えて遅くなる。
試しにマスタに20000行（数式も20000個あるということ）入った状態で検索を行ったところ、筆者の環境ではおおむね1.5秒ほどかかった。

O記法（オーダー記法｜オー記法）について
ざっくりとした計算回数を表すときに使う記法で、ある処理の計算回数を多項式で表したときに、最高次数以外の項と定数を無視して、「だいたいこのぐらいの計算量になるよ」としたもの。
当然
log(n) → n → n log(n) → n√n → n^2 → n^3 → ・・・ a^n → n!
といった順で早い。
Excelの場合は「何回セルへアクセスするか」ぐらいに考えておいて良い。

興味がある場合、以下の記事が非常にわかりやすくておすすめ。

計算量オーダーの求め方を総整理！〜どこから log が出て来るか〜
https://qiita.com/drken/items/872ebc3a2b5caaa4a0d0

・早いパターン
作業列を2列使い、
①「条件一致すれば1を返すセル」と
②「①のセルの”その行までの和”を出すセル」とすることで、処理を格段に速くすることができる。

「処理用2」が連番列。どのみち完全一致で検索するので**連番が複数個あっても問題ない**のが肝。

遅い例では1万行を処理するのに内部では約5000万回の計算が必要だったが、こちらは2万回（ (値の比較と加算)*1万回）の計算で済んでいる。

※「計算回数」というと語弊があるが、簡単のためこのような表現にした。

先ほどと同様にマスタを2万行にして検索を行ったところ、ノータイムで表示された。12万件にしても1秒未満であった。

２．ランキング集計などで大量にSUMIFSがあって重い

ランキングやSUMIFSに限った話ではいが、

・処理に時間のかかる計算を
・全員分処理しなくてはいけないが、
・特に処理の必要が無い人が相当いる

ような場合に高速化できることがある。

イメージ１。ランキング集計なので全員分計算する必要があるが、**期間中来ていない人**も相当いて、それらは別に処理しなくても良いような場合

イメージ２。顧客ごと×日付ごとに集計する必要があるが、**「前日が0なら処理しなくても良い」**みたいな場合。

こういう場合は「処理する必要がない場合、IFで処理するしないを分岐させる」ことで早くなる。

集計フラグ列の数式は「何を除外したいか」によって変わる。
この例の場合は集計フラグの判定にCOUNTIFSを使うと意味が無い（結局重い）ので、「ある期間中に1回でも来ているかどうか」みたいな判定をすることになる。
軽量に計算するためには処理が複雑となってメンテナンス性や可読性は下がるが、今回やりたいことはパフォーマンス改善なので仕方のないところだ。

左のセルの値によって処理をする必要がなくなることがある場合はもう少し簡単だ。これは単に以下のようにすれば良い。

左のセルが0なら0を出力して終わる

IF文条件式がTRUEならFALSE側、条件式がFALSEならTRUE側を「#N/A」として計算を打ち切る。数式タブの「数式の検証」で動きを見てみると、以下のようになっている。

３．エラーの原因が明らかなら、IFERRORではなくIFを使う

以下の図のような場合だ。
この関数がエラーになるケースと言えば、もうほとんど「顧客IDが空白」の場合に限られるだろう。

来客時にカードなんかを出してもらって、会員番号を入力すると氏名や会員情報が出るようなイメージ。

こういうエラーの原因がはっきりしているような場合、IFERRORではなく単に

　　　　　　　　　　=IF(エラーの原因,"",メイン処理)

としてしまった方が早い。

２で述べたようにIFは条件に合致しなかった処理を行わないが、IFERRORは「全部計算してからエラーだった場合に分岐」するので、こういうちょっと重い処理が大量に入っているような場合にパフォーマンス改善になることがある。

４．〇〇IFSを〇〇IFで使う

複数条件の〇〇IFS関数を大量に使用する場合、複数の条件セルを文字結合しておいて、結合後の文字列の単一条件で〇〇IFとすることでパフォーマンスが改善することがある。

”ことがある”の意味がこれまでと違って、この方法は環境によってはパフォーマンスが全く変わらないことがあるという意味だ。（おそらく、Excelのバージョン依存？）
〇〇IFS系でやってみたらとてもじゃないが使えないレベルの計算速度だったものを〇〇IFに変換したらまあまあ使えるレベルになったということもあるが、特に変わらないということもある。

Excel2016では結構変わったが、office365で試してみたら特に変わらなかった