Excel VBA で集計合計を高速化するには

質問

処理速度の高速化についてご助言をお願いします。
１２枚（月毎）のシートに
　約７００行と１０列の表 （期間、担当者、納入先(D)、商品(E)、売上数量(F)、売上単価(G)、売上金額(H)、売上原価(I)、粗利益額(J)）　⇒上のアルファベットは、列名
納入先名D毎と商品名E毎を基準に検索し、同種のものがあれば、F～Jまでをそれぞれ加算していく。

私のコードは、
１、まず初めに１２ヶ月のデータを作成済みのシート「集計」へコピーする。

'----------納入先名、商品名の列判定-----------
Select Case Range("AC1").Value　　’納入先名、商品名どちらで処理するかを選択
Case 4
CoN = "D": C = 30　　　　　　　’”D"は元データの納入先の列と３０は合計を入力する列数
Case 5
CoN = "E": C = 31　　　　　　　’”D"は元データの商品名の列と３１は合計を入力する列数

End Select
LaRow = Worksheets("集計").Cells(Rows.Count, C).End(xlUp).Row
Worksheets("集計").Range("AN2:AS" & LaRow + 1).ClearContents
'----------集計-----------
' For Sn = 1 To 12　　　’各月のデータをシート「集計」へコピー
SnLaRow = Worksheets(Sn).Range(CoN & Rows.Count).End(xlUp).Row
Pst = Worksheets(Sn).Range("A2:" & "J" & SnLaRow)
LaRow = Worksheets("集計").Range(CoN & Rows.Count).End(xlUp).Row
LaRow = LaRow + 1
Worksheets("集計").Range("A" & LaRow & ":" & "J" & LaRow + SnLaRow - 1) = Pst

Next Sn

これも遅いときは、約3秒かかります。

２、コピーしたデータを下に納入先（約400）、商品名（約５００）を検索しながら計を求める。

SnLaRow = Worksheets("集計").Range("A" & Rows.Count).End(xlUp).Row　　’コピー後のデータの最終行
LaRow = Worksheets("集計").Cells(Rows.Count, C).End(xlUp).Row

For NS = 3 To LaRow　　　’ LaRowは、納入先または商品名の最終行
With Worksheets("集計")
For K = 6 To 10　　　合計したデータはSumIf関数によってAN～ASの列に
.Cells(NS, K + 35).Value = Application.WorksheetFunction.SumIf(.Range(Cells(2, CoN), Cells(SnLaRow, CoN)), Cells(NS, C).Value, (.Range(Cells(2, K), Cells(SnLaRow, K))))
Next
End With
Next NS

こちらの速さは、約4秒かかります。

よろしくお願いします。

回答No.3

Wendy02

>全コード・サンプルを投稿しようとしますと字数が不足し、コードの一部分しか投稿できませんでした。

最近ですが、よそのダウンロードサイトなどを借りて、ワークブックをアップロードする人が出てきましたね。ここの規約が内部でどういう変わったのか分かりませんが、1年ぐらい前は、削除されていたのですが、今は、削除されていません。回答者はしていないのですが、質問者さんはやっているようです。

もし、ここにコードを出す場合は、半角スペースがネックになるようです。半角スペースを1文字と数えるので、すぐに一杯になるようです。

それはとかもく、OSとExcelのバージョンはいくつですか？

補足

お返事ありがとうございます。
OSは、XPがコード作成には主ですが、操作には、VistaもWin7もあります。
また、Excelについても作成は、2003ですが、同じく操作には2007も2010もあります。

回答No.2

Wendy02

>体感的にもう少しせめて0.5秒以内とかには、むりなのでしょうか？

こういうのは、物理的・スペック的な問題にも含まれます。私としては、もうこういう掲示板の質問には不向きかもしれないなって思います。10分以上掛かるものを、もう少し早くしてほしいという話なら、相談には乗れるけれども、数秒の問題を1秒以内にしてほしいという要求は、難題の部類に入ります。ただ、そういう問題を好きな人たちもいますから、本来、コードを全部と、どんなデータかサンプルがでていれば、掲示板で相談に乗る人も出てきます。今回は、直接の指摘は無理です。

「高速化」として、Office Tanaka の田中亨氏のようなナンセンスなコードを出して、コンマ何秒速いとか、自分のサイトで出していますが、そういうのは、ベテランの人には顰蹙ものだと思います。

また、高速化という言葉は、Microsoft VBAにはありませんが、「最適化」という言葉があります。
VBAは、スクリプト型とコンパイル型の両方の要素を持っていますから、その特徴を良く理解して使うことです。

後は、ご自身で考えてほしいですが、以下は、100%を守る必要がないけれども、経験的に、一般論として、こういう処理方法があります。

最適化テクニック
1.With ステートメントを使って、メソッドやプロパティに与える命令や値を最短にする。
　（文字列を減らす。不要なコメントは入れない。）
2.シートやブックは、オブジェクト変数を使用する。Set wb =Workbooks("Book1.xls")
3.For Each ... Next ループを利用できれば使う。
4.Loop に与えるプロパティの値を、プロパティで与えないこと。
　　For i = 1 to Worksheets("Sheet2").Cells(1,5).Value　'←予め変数を取り、変数で与える
5.自動キャストを使わない。Variant型出力を、文字、数値に指定する。明示的にプロパティを指定する。
　例： Cells(1, "A"), Range("A" & i ) ....Cellsの引数は数値。Rangeの引数は、文字か、Cellsプロパティ
6.コレクション(Sheets, Workbooks)は、名前よりも、インデックスが速い
7.ワークシートのメソッドは、VBAでは必ずしも必要としない。代表的なものは、Select, AutoFill
8.マクロ記録で不要な部分を、そのままにしない。Default値のままのものは、その部分を削除する。
9.変数は、Variant型は控えめにする。固有のオブジェクト型を使う。定数を利用する。配列を利用してメモリから値を取り出す。文字列関数で、文字列を与えるなら、文字列出力にさせる。使わない変数は削除する。
10.画面を更新させない。割り込み(Event, DDE)はさせない。
11.ワークシート内のセルをループさせない方法はないか探す。
12.メイン・プロシージャーは短めに、サブルーチンを使用する。
13.実行時バインディングをやめて、事前バインディングを使う。
14.コードを改編しない場合、VBEオプションを「バックグラウンド コンパイル」にしておく。(ただし、コードを良く改編する人には向きません。）

以下はAccessのヘルプですが、こういう内容が出ています。

参考URL：

http://office.microsoft.com/ja-jp/access-help/HP005186823.aspx?redir=0

お礼

Wendy02さん、GOCHSOUdaさん
いろいろアドバイスを頂きありがとうございます。内容については、理解できるもの、すでに利用しているもの、未だ利用したことなく不明なもの等ありますが、
この貴重なアドバイスを頂きましたので、今一度更に最適化出来ないか頑張ってみます。

ありがとうございました。

補足

この掲示板利用が少ないため、このたび回答への投稿が思うようにいきませんでした。回答を下さった方には不快な思いをされたことと申し訳なく思っています。
どのようなことかと申しますと
全コード・サンプルを投稿しようとしますと字数が不足し、コードの一部分しか投稿できませんでした。このような場合の回避方法をご存知でしたら、教えていただけませんか？今後のためにも

回答No.1

GOCHISOUda

内容については触れません。

変数をきちんと宣言して、Variantを避ける。
ScreenUpdatingで画面を固定する。
RangeよりCellsの方がすっきりするのでは。

＞CoN = "D": C = 30　　　　　　　’”D"は元データの納入先の・・・・・・
の３０～’のスペースを止める。（見た目は犠牲）
CoN = "D": C = 30
’”D"は元データの納入先の・・・・・（次の行とか）

With Worksheets("集計")は
SnLaRow = Worksheets("集計").Range("A" & Rows.Count).End(xlUp).Row　　’コピー・・・
の前からで
SnLaRow = .Range("A" & Rows.Count).End(xlUp).Rowのようにする。

補足

GOCHISOUda さん　早速の回答ありがとうございます。
予め、すべてを質問すればよいものを一部分を投稿し、失礼しました。申し訳ございません。
実は、変数もScreenUpdatingも設定しています。ただ、
「RangeよりCellsの方がすっきりするのでは」の件は、そのときに使いやすいほうを今までは使っていました。このコードについて再考します。

体感的にもう少しせめて0.5秒以内とかには、むりなのでしょうか？