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

Excel関数の解説、関数サンプルと高等テクニック
最終更新日:2017-06-17

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

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


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

今回は、集計関数で良く使われる代表的関数の、、
SUMIF関数
範囲の中で、指定した条件を満たすセルの値を合計します。または、範囲の中で、指定した条件を満たすセルに対応する合計範囲のセルの値を合計します。書式 SUMIF(範囲,検索条件[,合計範囲]) ※[]で囲まれている部分は省略可能です。
について検証してみました。

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

Excel関数サンプル画像

データは、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関数
検索値で、セル範囲の最初の列を検索し、その範囲の同じ行にある任意のセルから値を返します。Vは縦方向(vertical)の意味です、つまり縦方向の表に対して使用します。VLOOKUP関数の書式 =VLOOKUP(検索値,範囲,列番号,検索方法) 検索値 表または範囲の左端の列で検索する値を指定します。
と勘違いして、このような入力をしてしまう事が結構多く見受けられます。
エラーになってくれれば良いのですが、正しく計算されてしまうから困ったものです。

結果は、
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(配列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で、
どこかをいじるたびに再計算が走る(一瞬待ちになる)ようなら、
入っている数式を再度確認してみることをお勧めします。




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

選択行の色を変える(条件付き書式,Worksheet_SelectionChange)
他ブックを参照できる関数、他ブックを参照できない関数
時間計算で困ったときの確実な対処方法
VLOOKUP 左側の列を取得(MATCH,INDEX,OFFSET)
SUMIF関数の良くある間違い
論理式とは条件式とは(IF関数,AND関数,OR関数)
先頭の数値、最後の数値を取り出す
最後の空白(や指定文字)以降の文字を取り出す
SUMIFの間違いによるパフォーマンスの低下について
数値範囲で表検索するVLOOKUP近似一致
エクセルの日付と時刻のまとめ


新着記事NEW ・・・新着記事一覧を見る

トランザクション処理|SQL入門(12月11日)
インデックスを作成して高速化(CREATE INDEX)|SQL入門(12月9日)
他のテーブルのデータで追加/更新/削除|SQL入門(12月8日)
データの削除(DELETE)|SQL入門(12月7日)
データの更新(UPDATE)|SQL入門(12月6日)
複数のSELECT結果を統合(UNION,UNION ALL)|SQL入門(12月5日)
テーブルを結合して取得(INNER JOIN,OUTER JOIN)|SQL入門(12月4日)
データベースの正規化とマスタの作成|SQL入門(12月3日)
データベースにおけるNULLの扱い方|SQL入門(12月2日)
オブジェクト変数とは何か|VBA技術解説(12月2日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
3.RangeとCellsの使い方|VBA入門
4.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
5.変数宣言のDimとデータ型|VBA入門
6.繰り返し処理(For Next)|VBA入門
7.マクロって何?VBAって何?|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.空白セルを正しく判定する方法(IsEmpty,IsError,HasFormula)|VBA技術解説
10.ひらがな⇔カタカナの変換|エクセル基本操作



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

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


    記述には細心の注意をしたつもりですが、
    間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
    掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
    掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。



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