SUMIFの間違いによるパフォーマンスの低下について
再計算が終わらない・・・
そんな経験をした人は多いと思います、原因はさまざまですが、まずは数式を見直してみましょう。
時間のかかる計算としては、
大量データの集計計算を多数使っている場合です。
SUMIF関数
数式もL列に10,000行入れます。
入れる数式によって、再計算の時間を測定してみました。
Application.Calculation = xlCalculationManual
Range("L2:L10001") = "☆ここに数式を入れます☆"
Debug.Print Timer
Application.Calculation = xlCalculationAutomatic
Debug.Print Timer
・手動計算（自動計算をOFF）
・数式を1万行分入れる
・時刻表示←計算開始時刻
・自動計算
・時刻表示←計算終了時刻
以下の実測時間は、
時間 = 計算終了時刻 - 計算開始時刻
これで計算した概数結果を記載しています。
VLOOKUP関数
エラーになってくれれば良いのですが、正しく計算されてしまうから困ったものです。
9.6秒
かなりかかっています。
では、SUMIF関数を正しく指定して計測してみます。
範囲を列全体にしています。
結果は、
8.3秒
間違った前記よりは少しは改善されていますが、
とりたてて強調するほどの差は感じられません。
入力する度に10秒も待たされてはたまったものではありません。
手動計算にしてから入力し、入力し終えたら自動計算にする
良くやることですが、それにしても、
たかだか1万行程度で、こんなにかかってしまうものでしょうか。
再計算を少しでも早くするためには、範囲を限定します。
8.3秒
範囲指定をしても変わりません。
Excelは良くできていますね、
データの無い範囲では余分な処理はしていないという事になります。
メンテナンス性を考えると、範囲指定はSUMIFにおいては無駄だという事になります。
以上の結果ですと、
最初の間違ったSUMIFでも、集計があっているのだから大した問題が無いような・・・
この数式を入れた後で、シートを少々いじってみると、とても重いのが分かります。
何も入っていない空白セルで、「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関数は、直ちに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コード、バーコード作成の覚え書き
新着記事NEW ・・・新着記事一覧を見る
AIがあればVBAはできる：セルに絵文字を入れる｜生成AI活用研究(2025-05-07)
Geminiと100本ノック 15本目：シートの並べ替え｜生成AI活用研究（5月6日）
Geminiと100本ノック 14本目：社外秘シート削除｜生成AI活用研究（5月4日）
Geminiと100本ノック 13本目：文字列の部分フォント｜生成AI活用研究（5月4日）
Geminiと100本ノック 12本目：セル結合の解除｜生成AI活用研究（5月2日）
Geminiと100本ノック 11本目：セル結合の警告｜生成AI活用研究（5月1日）
AI(Gemini)とエクセル数式対決 その2｜生成AI活用研究(2025-04-30)
Geminiと100本ノック 10本目：行の削除｜生成AI活用研究（4月30日）
AI(Gemini)とテーマを決めて議論｜生成AI活用研究(2025-04-30)
Geminiと100本ノック 9本目：フィルターコピー｜生成AI活用研究（4月29日）
アクセスランキング ・・・ ランキング一覧を見る
1.最終行の取得（End,Rows.Count）｜VBA入門
2.繰り返し処理（For Next)｜VBA入門
3.変数宣言のDimとデータ型｜VBA入門
4.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
5.ひらがな⇔カタカナの変換｜エクセル基本操作
6.RangeとCellsの使い方｜VBA入門
7.メッセージボックス(MsgBox関数)｜VBA入門
8.セルのクリア（Clear,ClearContents）｜VBA入門
9.FILTER関数（範囲をフィルター処理）｜エクセル入門
10.条件分岐（Select Case）｜VBA入門
このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。
記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。