処理速度の高速化についてご助言をお願いします。
12枚(月毎)のシートに
約700行と10列の表 (期間、担当者、納入先(D)、商品(E)、売上数量(F)、売上単価(G)、売上金額(H)、売上原価(I)、粗利益額(J)) ⇒上のアルファベットは、列名
納入先名D毎と商品名E毎を基準に検索し、同種のものがあれば、F~Jまでをそれぞれ加算していく。
私のコードは、
1、まず初めに12ヶ月のデータを作成済みのシート「集計」へコピーする。
'----------納入先名、商品名の列判定-----------
Select Case Range("AC1").Value ’納入先名、商品名どちらで処理するかを選択
Case 4
CoN = "D": C = 30 ’”D"は元データの納入先の列と30は合計を入力する列数
Case 5
CoN = "E": C = 31 ’”D"は元データの商品名の列と31は合計を入力する列数
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秒かかります。
2、コピーしたデータを下に納入先(約400)、商品名(約500)を検索しながら計を求める。
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秒かかります。
よろしくお願いします。
A 回答 (3件)
- 最新から表示
- 回答順に表示
No.3
- 回答日時:
>全コード・サンプルを投稿しようとしますと字数が不足し、コードの一部分しか投稿できませんでした。
最近ですが、よそのダウンロードサイトなどを借りて、ワークブックをアップロードする人が出てきましたね。ここの規約が内部でどういう変わったのか分かりませんが、1年ぐらい前は、削除されていたのですが、今は、削除されていません。回答者はしていないのですが、質問者さんはやっているようです。
もし、ここにコードを出す場合は、半角スペースがネックになるようです。半角スペースを1文字と数えるので、すぐに一杯になるようです。
それはとかもく、OSとExcelのバージョンはいくつですか?
この回答への補足
お返事ありがとうございます。
OSは、XPがコード作成には主ですが、操作には、VistaもWin7もあります。
また、Excelについても作成は、2003ですが、同じく操作には2007も2010もあります。
No.2
- 回答日時:
>体感的にもう少しせめて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/HP …
この回答への補足
この掲示板利用が少ないため、このたび回答への投稿が思うようにいきませんでした。回答を下さった方には不快な思いをされたことと申し訳なく思っています。
どのようなことかと申しますと
全コード・サンプルを投稿しようとしますと字数が不足し、コードの一部分しか投稿できませんでした。このような場合の回避方法をご存知でしたら、教えていただけませんか?今後のためにも
Wendy02さん、GOCHSOUdaさん
いろいろアドバイスを頂きありがとうございます。内容については、理解できるもの、すでに利用しているもの、未だ利用したことなく不明なもの等ありますが、
この貴重なアドバイスを頂きましたので、今一度更に最適化出来ないか頑張ってみます。
ありがとうございました。
No.1
- 回答日時:
内容については触れません。
変数をきちんと宣言して、Variantを避ける。
ScreenUpdatingで画面を固定する。
RangeよりCellsの方がすっきりするのでは。
>CoN = "D": C = 30 ’”D"は元データの納入先の・・・・・・
の30~’のスペースを止める。(見た目は犠牲)
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秒以内とかには、むりなのでしょうか?
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) VBAが止まります。 1 2022/09/02 14:51
- Visual Basic(VBA) ファイル全てを .xlsm に変更したところ、プログラムが途中で落ちてしまっています 17 2022/12/07 12:03
- Visual Basic(VBA) コード名シートA列と集計シートA列のコードが一致したら、コード名シートA5からk12の範囲をコピーし 1 2022/08/29 23:46
- Visual Basic(VBA) excel vbaでvlooupの変数がわかりません。 7 2022/05/30 09:35
- Visual Basic(VBA) 前回ご教授いただいたコードに覚えたてのループ処理で品名りんごAから順に20回for nextでループ 7 2023/01/13 22:01
- Visual Basic(VBA) VBAコードが作動せず、どこに問題があるのか教えて下さい。 3 2023/06/13 13:20
- Visual Basic(VBA) 日付を重複させずに数えたい 4 2022/12/04 16:26
- Visual Basic(VBA) 別シートのデータを参照して値を入れたい。 まとめデータシートのC列D列の値を商品一覧シートのコードが 7 2022/08/17 13:20
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Visual Basic(VBA) まとめシートから集計シートへA列のコードが一致したら1行コピーするマクロをネット上で見つけました。こ 1 2022/08/30 14:11
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
エクセルで特定の色の数字だけ...
-
フィルタをしても最下行を常に...
-
EXCELピボットテーブル関数につ...
-
【エクセル】オートフィルタで...
-
VBAで重複する項目を1つにまと...
-
EXCEL 重複データの集計の仕方
-
マクロ実行時にエラーが出てし...
-
ピボットテーブルでは時間の表...
-
Excel : 二通りの合計が合わな...
-
複数のピボットテーブルを一括...
-
Excelの集計結果だけをコピー貼...
-
アンケートの集計
-
ピボットテーブルの逆
-
エクセルで文字が白くなる
-
DATEDIFでマイナス表示をさせたい
-
別のシートから値を取得するとき
-
エクセルで文字を含む式に、カ...
-
エクセルの主軸と第2軸の0を合...
-
エクセルで円グラフに引き出し...
-
エクセルで長い行を5行ごとに1...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
【エクセル】オートフィルタで...
-
エクセルで特定の色の数字だけ...
-
ピボットテーブルでは時間の表...
-
フィルタをしても最下行を常に...
-
VBAで重複する項目を1つにまと...
-
アンケートの集計
-
ピボットを更新すると数式が入...
-
複数のピボットテーブルを一括...
-
ピボットの集計方法「合計」初...
-
EXCEL 重複データの集計の仕方
-
いい機能だけど、毎回めんどく...
-
エクセル、リソース不足エラー...
-
excelで集計の合計を降順に並べ...
-
エクセルの小計を自動的に色づ...
-
VLOOKUP関数とCOUNT関数等の組...
-
ピボットテーブルで同じデータ...
-
エクセル 関数を使った横方向の...
-
Excel : 二通りの合計が合わな...
-
ピボットテーブルのページエリ...
-
EXCELで増減率を計算したいです
おすすめ情報