SUMIFの間違いによるパフォーマンスの低下について

公開日：2017-06-17　最終更新日：2017-06-17

SUMIFの間違いによるパフォーマンスの低下について

再計算が終わらない・・・
そんな経験をした人は多いと思います、原因はさまざまですが、まずは数式を見直してみましょう。

単純な四則演算が遅いという事はありません、それはもうPCの問題です。
時間のかかる計算としては、
大量データの集計計算を多数使っている場合です。

今回は、集計関数で良く使われる代表的関数の、、
SUMIF関数

・SUMIF関数の書式・SUMIF関数の使用例・ワイルドカード・SUMIF関数の応用例

について検証してみました。

以下の表で検証してみました。

データは、10,000行
数式もL列に10,000行入れます。
入れる数式によって、再計算の時間を測定してみました。

時間計測するために、以下のマクロを使いました。


  
    
      Application.Calculation = xlCalculationManual

      Range("L2:L10001") = "☆ここに数式を入れます☆"
Debug.Print Timer

Application.Calculation = xlCalculationAutomatic

Debug.Print Timer

・手動計算（自動計算をOFF）
・数式を1万行分入れる
・時刻表示←計算開始時刻
・自動計算
・時刻表示←計算終了時刻

使ったPCは、Corei5なので、ごくごく一般的なPCです。
以下の実測時間は、
時間 = 計算終了時刻 - 計算開始時刻
これで計算した概数結果を記載しています。

まずは、良くある間違ったSUMIFから

・=SUMIF(A:J,K2,J:J)

これ間違ってますよね、計算はしてくれますが、明らかに範囲指定の間違いです。
VLOOKUP関数

・VLOOKUP関数の書式・VLOOKUP関数：近似一致（検索方法=TRUE）の使用例・VLOOKUP関数：完全一致（検索方法=FALSE）の使用例

と勘違いして、このような入力をしてしまう事が結構多く見受けられます。
エラーになってくれれば良いのですが、正しく計算されてしまうから困ったものです。

結果は、
9.6秒
かなりかかっています。

では、SUMIF関数を正しく指定して計測してみます。

・=SUMIF(A:A,K2,J:J)

一般的なSUMIF関数の指定です。
範囲を列全体にしています。
結果は、
8.3秒
間違った前記よりは少しは改善されていますが、
とりたてて強調するほどの差は感じられません。

入力する度に10秒も待たされてはたまったものではありません。
手動計算にしてから入力し、入力し終えたら自動計算にする
良くやることですが、それにしても、
たかだか1万行程度で、こんなにかかってしまうものでしょうか。

メンテナンス性等はひとまず置いといて、
再計算を少しでも早くするためには、範囲を限定します。

・=SUMIF(A$2:A$10001,K2,J$2:J$10001)

結果は、
8.3秒
範囲指定をしても変わりません。
Excelは良くできていますね、
データの無い範囲では余分な処理はしていないという事になります。
メンテナンス性を考えると、範囲指定はSUMIFにおいては無駄だという事になります。

以上の結果ですと、
最初の間違ったSUMIFでも、集計があっているのだから大した問題が無いような・・・

検証を進めます。

=SUMIF(A:J,K2,J:J)
この数式を入れた後で、シートを少々いじってみると、とても重いのが分かります。
何も入っていない空白セルで、「Delete」を押しただけで再計算が走ります。
対して
=SUMIF(A:A,K2,J:J)
この数式を入れた後は、SUMIFとは無関係のセルであれば、
セル値を変更しても再計算が走りません。

以下のマクロで検証してみました。


  
    
      Application.Calculation = xlCalculationManual

      Range("L2:L10001") = "☆ここに数式を入れます☆"

Debug.Print Timer

Application.Calculation = xlCalculationAutomatic

Debug.Print Timer

Application.Calculation = xlCalculationAutomatic

Debug.Print Timer

・=SUMIF(A:J,K2,J:J)

結果は、
9.6秒
9.6秒
2回目の計算も1回目の計算と同じだけ時間がかかっています。
毎回、全て再計算されてしまっている感じです。

・=SUMIF(A:A,K2,J:J)

結果は、
8.3秒
0.0秒
2回目の計算が行われていません。
1回目の計算で、計算済として判定されて再計算を行っていないという事です。

そうなんです。
ここが問題なのです。
間違ったSUMIF（=SUMIF(A:J,K2,J:J)）では、SUMIFの計算自体も遅くなってしまいますが、
それ以上に再計算が無関係のセル値変更でも行われてしまうというところにあります。
確認してみたところ、関係のない別のシートの変更でも再計算が走ってしまいます。

番外として

・SUMPRODUCT関数

引数として指定した配列の対応する要素間の積をまず計算し、さらにその和を返します。SUMPRODUCT関数の書式 SUMPRODUCT(配列1,[配列2],[配列3],...) 配列1 計算の対象となる要素を含む最初の配列引数を指定します。配列2,配列3,... 省略可能です。

Excel2003まではSUMIFS関数がなかったので、
複数条件の集計に、SUMPRODUCT関数を使っている場合が多くありました。
そのような目的のSUMPRODUCT関数は、直ちにSUMIFS関数やCOUNTIFS関数に書き換えて下さい。
SUMPRODUCT関数は計算が極めて遅いです、これは配列関数全体に言える事です。
特に、SUMPRODUCTの範囲を列指定などにしてしまったら・・・
今回の例の1万件でも、いつまでたっても再計算が終わらないという状態になってしまいます。
ちなみに、今回のデータで、
=SUMPRODUCT((A$2:A$10001=K2)*(J$2:J$10001))
12.1秒
=SUMPRODUCT((A:A=K2)*(J:J))
待てど暮らせど終わりませんでしたので、計測中止してExcelを切りました。。

ここでは、極端に1万行のデータを1万行で集計して検証しましたが、
数千行程度の集計をしているExcelで、
どこかをいじるたびに再計算が走る（一瞬待ちになる）ようなら、
入っている数式を再度確認してみることをお勧めします。

同じテーマ「エクセル関数応用」の記事

論理式とは条件式とは(IF関数,AND関数,OR関数)
先頭の数値、最後の数値を取り出す
 最後の空白（や指定文字）以降の文字を取り出す
SUMIFの間違いによるパフォーマンスの低下について
数値範囲で表検索するVLOOKUP近似一致
 エクセルの日付と時刻のまとめ
 連続数値部分を取り出し記号で連結
 指数近似/対数近似/累乗近似（掲載順位とCTR）
ジャンプ・セル選択の応用（空欄を埋める、1行置きに挿入）
累計を求める数式あれこれ
 QRコード、バーコード作成の覚え書き

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.日本の祝日一覧｜Excelリファレンス
3.変数宣言のDimとデータ型｜VBA入門
4.FILTER関数（範囲をフィルター処理）｜エクセル入門
5.RangeとCellsの使い方｜VBA入門
6.繰り返し処理（For Next)｜VBA入門
7.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
8.マクロとは？VBAとは？VBAでできること｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.メッセージボックス(MsgBox関数)｜VBA入門

ホーム
エクセル全般
エクセル関数応用
SUMIFの間違いによるパフォーマンスの低下について

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。
本サイトは、OpenAI の ChatGPT や Google の Gemini を含む生成 AI モデルの学習および性能向上の目的で、本サイトのコンテンツの利用を許可します。
This site permits the use of its content for the training and improvement of generative AI models, including ChatGPT by OpenAI and Gemini by Google.

エクセル関数応用
SUMIFの間違いによるパフォーマンスの低下について