[[20160817154931]]　『関数式の高速化』（T1608）　ページの最後に飛ぶ

『関数式の高速化』（T1608）

お世話になります。
いろいろな関数式が入ったシートの再計算時間の改善に取り組んでいます。
関数式のある列を逐次値コピーなどして時間がかかる「悪玉」を数種類に絞り込み最初に下記式に狙いを定めましたが、改善案が浮かびません。
アドバイスをお願いします。

 AD2セル=SUMPRODUCT(($AP$2:INDIRECT("$AP$"&$AO$1)=B2)*($AF$2:INDIRECT("$AF$"&$AO$1)))+
 COUNTIFS($AP$2:INDIRECT("$AP$"&$AO$1),B2,$AW$2:INDIRECT("$AW$"&$AO$1),"<0.6",$BF$2:INDIRECT("$BF$"&$AO$1),"NI")*0.5+
 COUNTIFS($AP$2:INDIRECT("$AP$"&$AO$1),B2,$AW$2:INDIRECT("$AW$"&$AO$1),"<0.6",$BF$2:INDIRECT("$BF$"&$AO$1),"SE")*0.5+
 COUNTIFS($AP$2:INDIRECT("$AP$"&$AO$1),B2,$AS$2:INDIRECT("$AS$"&$AO$1),"<6",$BD$2:INDIRECT("$BD$"&$AO$1),">9")*0.5
（下へコピー）

説明）
・「AO1」以外のB,AP,AS,..各列は入力データです
・「AO1」はAP列以降の入力データの行数をカウントしています
　（通常4000行～6000行程度、10000行までを想定）
・式は、各データ毎にある条件を満たしたものにポイントを付与し、加算しています
・この式の再計算に約45秒を要し、シート全体で4分弱かかっています
・INDIRECTはOFFSETで可変範囲指定するのが鬱陶しいので使用しています

ネットで調べるとINDIRECT,COUNTIF,SUMPRODUCT,OFFSETなどは処理時間が
かかる．．とあります。　
あきらめるしかないのでしょうか？
返信遅れがちになりますが、よろしくお願いします。

＜使用 Excel：Excel2010、使用 OS：Windows7 ＞

数式を多用すると遅くなります。これはどこか1つのセルでも変更すると、それに関連する部分だけを抽出する式だけ選ぶのは大変なので、全部の式を再評価するためと思われ、仕方がありません。

案1：オプション設定で、自動計算を止め、手動計算にする。必要なときだけF9キーで再計算。
　経験上、数値を変えても再計算するのを忘れ、数字が変わらない事による誤報告する可能性あり。

案2：数式を止め、マクロで計算する。
　簡単な数式は自動計算にしておけるので、効果的。
　ただし、数値を変更した場合は、再実行する必要あり。再実行を忘れて誤報告する可能性あり。

案3：マクロの別案。INDIRECT部分をセル範囲に置き換えた、数式を更新するマクロを作成する。
　今より早くなるが、計算無しよりは遅い。
（???） 2016/08/17(水) 16:35

???様、早々にありがとうございます。

案1→現状「手動計算」に設定し、データ入力後に再計算しています

案2：数式を止め、マクロで計算する。簡単な数式は自動計算にしておけるので、効果的．．
→「マクロでも計算処理の時間はあまり変わらない．．」と、どこかで目にしたことがあり敬遠していましたが、
同種の計算式でもマクロが早くなるということでしょうか？

案3：INDIRECT部分をセル範囲に置き換えた、数式を更新するマクロを作成する．．
→申訳ないです理解がおよびません、少し噛み砕いていただけないでしょうか？

また、提示した関数式を別の式とかに代替する余地はないものでしょうか？
（T1608） 2016/08/17(水) 17:27

別の式に変えるのが、案3です。例えば、現状の数式は、INDIRECTを無くせば、以下の感じですよね。

 =SUMPRODUCT(($AP$2:$AP$10000)=B2)*($AF$2:$AF$10000)))+
  COUNTIFS($AP$2:$AP$10000,B2,$AW$2:$AW$10000,"<0.6",$BF$2:$BF$10000,"NI")*0.5+
  COUNTIFS($AP$2:$AP$10000,B2,$AW$2:$AW$10000,"<0.6",$BF$2:$BF$10000,"SE")*0.5+
  COUNTIFS($AP$2:$AP$10000,B2,$AS$2:$AS$10000,"<6",$BD$2:$BD$10000),">9")*0.5

多めに計算しても良いならば、これに変えるだけ。データのあるぎりぎりの行数にして少しでも速くするならば、10000の部分を現在のデータ末に置き換える。これをマクロで行うということです。

INDIRECTの遅さはこれで無くなるので、今より速くなりますが、SUMPRODUCT等の遅さはそのままです。まずはこれを試してみてください。マクロ化するのは、これで十分速くなると判った後で。

または、作業列を使って、もっと条件を簡単にできませんかねぇ？
（???） 2016/08/17(水) 17:41

 私には理解不能な議論が続いているなぁ。

 ＞ネットで調べるとINDIRECT,COUNTIF,SUMPRODUCT,OFFSETなどは処理時間が 
 ＞ かかる．．とあります。

 そんなの嘘です。
 どこに書いてあったのですか？

（半平太） 2016/08/17(水) 18:52

???様、半平太様、　ありがとうございます。

 >INDIRECTの遅さはこれで無くなるので．．
　↓
はい、これで約20秒ほど稼げるのは確認済みです。（最終行を6500行にしてましたが）

提示式については、INDIRECTを代替するのがベストであればそのようにしたいと考えます。
ただ、　>多めに計算しても良いならば．．<　とはいかない他列のケースがあり、今回を例に
何かヒントを得られないかと思った次第です。（説明不足ですみません）

 >そんなの嘘です。どこに書いてあったのですか？
　↓
いろいろネットを彷徨っていたので、どこに書いてあったのかは定かではありませんが、
このように断言されると何か希望が持てたと思うべきか、逆に手の施しようがないのか
．．．どちらでしょうか？(苦笑)

（T1608） 2016/08/17(水) 21:35

11個のINDIRECTを無くして、短縮できた時間が20秒ほど。 6500行あったということは、約7万個で20秒。ざっくり計算ですが、1回0.3ミリ秒くらいですねぇ。そんなに遅い関数ではないかと。 4分のうち、20秒削れても、いまいちですよね。

別案として、数式だと変数が使えないので、3つのCOUNTIF文で、同じ条件で全検索している部分が無駄に見えます。これを削るとか。

作業列を使って、1行毎にAW列が0.6未満か、BF列は"NI"または"SE"か、という感じで値を埋めておき、これを単純COUNTする感じでいかがでしょうか。
（???） 2016/08/18(木) 09:37

  >4分のうち、20秒削れても、いまいちですよね．．．

提示式のみでは約45秒なので決して小さくはない効果ですが、想いとしては
これぐらいの処理なら5秒程度にならないものかと．．

当初は使用ＰＣのハードの問題かと思っていましたが、別の最新ＰＣでも
同程度の処理時間でした。（Win10, Excel2013）

提示式以外に単独で10秒以上かかる式が他に20列ぐらいあり、式列を
参照している式もあるため全体で4分弱もかかっているのだとは思いますが、
エクセルってこんなに「重い」んですかね、．．．
画像処理ソフトの進化に比べるとずいぶんと遅れている印象です。

愚痴を言っても始まらないので、他列を含め地道に提案いただいたことを
検討させていただきます。
これからもよろしくお願いします。
（T1608） 2016/08/18(木) 13:09

こんにちは

元々の計算式って、B2以下のデータのある範囲だけAD2以下に数式をセットするとすれば

INDIRECTを無くすと

=SUMIF($AP$2:$AP$10000,B2,$AF$2:$AF$10000)+

 COUNTIFS($AP$2:$AP$10000,B2,$AW$2:$AW$10000,"<0.6",$BF$2:$BF$10000,"NI")*0.5+
 COUNTIFS($AP$2:$AP$10000,B2,$AW$2:$AW$10000,"<0.6",$BF$2:$BF$10000,"SE")*0.5+
 COUNTIFS($AP$2:$AP$10000,B2,$AS$2:$AS$10000,"<6",$BD$2:$BD$10000,">9")*0.5

と結果は同じですか？

SUMPRODUCTはSUMIF で済みそうなんですけど。

後は、VBAで一括処理するとしたら、

Sub test()

    Dim r As Range
    Dim t As Range
    Dim w As Range
    Dim v As Variant
    Dim i As Long
    Dim z As Variant

    Dim 最終行 As Long

    最終行 = Range("AO1").Value

    Set w = Range("AF2:AF" & 最終行)

    Set r = Range("AD2", Range("B" & Rows.Count).End(xlUp).EntireRow.Range("AD1"))
    v = r.Value

    With WorksheetFunction
        For Each t In r
            i = i + 1
            z = t.EntireRow.Range("B1")
            v(i, 1) = .SumIf(w.EntireRow.Columns("AP:AP"), z, w)
            v(i, 1) = v(i, 1) + .CountIfs(w.EntireRow.Columns("AP:AP"), z, _
                                            w.EntireRow.Columns("AW:AW"), "<0.6", _
                                            w.EntireRow.Columns("BF:BF"), "NI") * 0.5
            v(i, 1) = v(i, 1) + .CountIfs(w.EntireRow.Columns("AP:AP"), z, _
                                            w.EntireRow.Columns("AW:AW"), "<0.6", _
                                            w.EntireRow.Columns("BF:BF"), "SE") * 0.5
            v(i, 1) = v(i, 1) + .CountIfs(w.EntireRow.Columns("AP:AP"), z, _
                                            w.EntireRow.Columns("AS:AS"), "<6", _
                                            w.EntireRow.Columns("BD:BD"), ">9") * 0.5
        Next
    End With
    r.Value = v
End Sub

こんな感じでしょうか？

（ウッシ） 2016/08/18(木) 14:28

 ＞いろいろネットを彷徨っていたので、どこに書いてあったのかは定かではありませんが、
 なんとも検証しにくいです。

 ＞このように断言されると何か希望が持てたと思うべきか、逆に手の施しようがないのか
 ＞．．．どちらでしょうか？(苦笑)

 おー！　いい質問ですね。ですが、今の情報ではどちらか決めかねます。

 どんなに速い関数でも、無駄なことをさせれば遅くなるのは道理です。それが改善できれば当然速くなります。
 でも、無駄と呼べる事をやっていないのであれば、必然の速度なので手の施しようは無いです。

 ただ、「無駄」と的確に判断するのは、そんなに容易なことではないです。

 ※１
 よくあるパターンは、同じ計算を何度も行うロジックの数式です。
 そういうのは、１回何処かのセルに算出して、後はそれを参照していく形にすれば格段に改善します。

 ※２
 計算速度が遅いという現象が起きるには、数式が大量にないと始まりません。
 どんなに遅い数式だとしても、２つ、３つなら、速度の問題は起きないです(よね？）

 なので、アドバイスをするには、大量にある数式を種類別に全部掲示して頂く必要があります。
 (その数式たちが入力されているセル範囲の情報、およびサンプルデータも添えて）

（半平太） 2016/08/18(木) 14:37

ウッシ様、半平太様、　ありがとうございます。

SUMIF案：約28秒、マクロ案：約5秒という驚きの結果でしたっ！
この差は一体どこから出てくるのでしょうか？　
計算処理回数が劇的に減っているのでしょうか？（半平太様の※１より）
半平太様のコメントではありませんが、ネットを鵜呑みにして
マクロを敬遠していたのが悔やまれます。
（こちらが真意を理解してなかった可能性が大ですが．．）

当初は全ての式を提示するつもりでしたが、うまく説明する自信がなく
今回のご回答をヒントに他へ展開するつもりでした．．お許しください。

コードの理解はこれからになります、よろしくお願いします。
（T1608） 2016/08/18(木) 15:32

作業列の使用例です。

[AAA:AAD]を作業列とします。

AAA2
=(B2=AP2)*AF2

AAB2
=(B2=AP2)*($AW2<0.6)*($BF2="NI")

AAC2
=(B2=AP2)*($AW2<0.6)*($BF2="SE")

AAD2
=(B2=AP2)*(AS2<6)*(BD2>9)

AAA2:AAD2の数式を下までコピペします。
(セルao1の値[最終行]を超えても構いません)

AAA1
=SUM(AAA2:INDIRECT("AAA" & AO1))

AAB1
=SUM(AAB2:INDIRECT("AAB" & AO1))*0.5

AAC1
=SUM(AAC2:INDIRECT("AAC" & AO1))*0.5

AAD1
=SUM(AAD2:INDIRECT("AAD" & AO1))*0.5

AD2(下までコピペ)
=(B2=AP2)*SUM($AAA$1:$AAD$1)

もとの数式をAD列に入れたときと同じ戻り値になると思います。
（カイル） 2016/08/18(木) 17:35

カイル様、　ありがとうございます。

式をコピペしてトライしましたが、一部を除き「０」が表示されて
うまくいきませんでした。

時間がとれしだい、原因をさがします．．

「=(B2=AP2)*AF2 」のような式中の「=」は初めて目にしました。
なるほど．．とは思いましたが、私には思いつかないですね。。。
（T1608） 2016/08/18(木) 18:30

ご提示のマクロについて教えていただけないでしょうか？

「 Range("AD2",Range("B" & Rows.Count).End(xlUp).EntireRow.Range("AD1"))」
は、AD2からAD列の最終端（＝B列最終端）の範囲を意味していると思うのですが、
「.Range("AD1")」の記述のしかたがよくわかりません。

「（ｵﾌﾞｼﾞｪｸﾄ）.EntireRow」の記述はわかるのですが、このように後ろに付く
「Range」は構文的？にはどのような意味？になるのでしょうか？

よろしくお願いします。
（T1608） 2016/08/19(金) 13:49

こんにちは

Range("AD2", Range("B" & Rows.Count).End(xlUp).EntireRow.Range("AD1"))

は、

Range("AD2:AD" & Range("B" & Rows.Count).End(xlUp).Row)

でもいいです。

Range("B10").EntireRow.Range("A1").Select

とか、

Range("B10").EntireRow.Range("D1").Select

とか、試してみると理解し易いです。

EntireRowの1行分の中で、A1、B1～XFD1 とかで相対位置が指定出来るというだけの事です。

Range("AD2:AD" & Range("B" & Rows.Count).End(xlUp).Row)

の方が分かりやすかったですね。

（ウッシ） 2016/08/19(金) 14:00

ウッシ様、　ありがとうございます。

 >相対位置が指定出来る．．．

自分なりにいろいろやっていて働き？はわかったのですが、
このご説明で理解できたように思います。

やることは単純なんですが、マクロでのセルの指定のしかたって
難しいもんですねぇ、つくづく思います。
（みなさんどうやって身につけているんでしょうか．．）
特に「Range」の概念？（構文？）がわからないです。
やはり、プロパティとかメソッドをキチンと理解してないと
ムリなんでしょうね．．

これからもよろしくお願いします。
（T1608） 2016/08/19(金) 16:47

カイル様

その後、入力ミスがないか何度も確認しましたが、ないようです。

でも、式を見ていても原因がわかりません．．

入力ミス以外には考えられないのですが、．．．スミマセン（涙）
（T1608） 2016/08/20(土) 19:53

すみません。いろいろ検証してみましたが、前に挙げた数式では計算できないようです。あの数式は気にしないでください。
（カイル） 2016/08/21(日) 05:26

カイル様、　承知しました。

どこがいけないんでしょうねぇ～
（わかるようになりたいものです）

これからもよろしくお願いします。

（T1608） 2016/08/21(日) 08:57

コメント返信：

[ 一覧(最新更新順) ]

[[20160817154931]] 『関数式の高速化』（T1608） ページの最後に飛ぶ

[[20160817154931]]　『関数式の高速化』（T1608）　ページの最後に飛ぶ