[[20110502214037]]　『行削除の処理時間を短縮したい』（つつじ）　ページの最後に飛ぶ

 『行削除の処理時間を短縮したい』（つつじ）

 約3万行あるデータの集計を、自動記録でｵｰﾄﾌｨﾙﾀとﾌｨﾙﾀｵﾌﾟｼｮﾝを使用して行削除を
 行い、その後関数で集計作業をしていますが処理時間に10～15分もかかり困ってい
 ます。
 集計をする前にﾃﾞｰﾀの行数は少しでも少ない方が処理が早いと思い行っていますが
 間違いでしょうか？
 ﾏｸﾛの知識がないためにどうかは分からないのですが、ﾏｸﾛを使用すると処理時間は
 短縮できるでしょうか？ 
 ﾃﾞｰﾀは以下のようなものです。日付を3月分だけにするためにｵｰﾄﾌｨﾙﾀとﾌｨﾙﾀｵﾌﾟｼｮﾝで、
 行削除をし、構成の空白行を同じように行削除し、a bそれぞれの個数を求める。
 これからﾏｸﾛを学びたいのですが、教えてください。

 Sheet1

     A 　　　B     C      D       E          F         G   H    I              
 1  日付    型　　個数   構成 　良否       2011/3/1        a    b       
 2  2/23　　a　　 100　　 1  　　1    　　　　        3月　300  0 
 3  4/4     b     200            1
 4  3/30    a     200     1
 5  3/12    a     100     1
 6  3/5     b     400    
 7  2/3     a     200     1      1
 8  1/31    b     100     1
 9  3/27    b     500

 ３万行あるとのことなので、計算時間がどうかはよく分かりませんが。
 作業列を使います。(H列とします)
 H2:=MONTH(A2)&B2
 H30000まで下へコピー
 E2:書式を「0"月"」　入力データは、1,2,･･･,12
 F2:=SUMIF(H2:30000,E2&F1,C2:C30000)
 G2:=SUMIF(H2:30000,E2&G1,C2:C30000)
    (NB)

 ありがとうございます。行削除のﾏｸﾛを教えてください。
 処理時間は短縮できるでしょうか？
 (つつじ)

 まずは現行のマクロを提示してはどうでしょうか。
 時間がかかるのがどこかは、それによると思います。
 (Mook)

 おっと、衝突しちゃいましたが。。。

 ＞行削除の処理時間を短縮したい
 これを主眼にすると、まずは計算方法を手動にして
 現在出来ているマクロを実行してみて下さい。

 これで処理時間が短く成るなら
 　・計算方法を手動にする
 　・計算方法を自動にする
 それぞれマクロの記録でコードが撮れますので
 現在出来ているコードの適当な場所へ入れて行けば良いと思います。

 ただ
 ＞ｵｰﾄﾌｨﾙﾀとﾌｨﾙﾀｵﾌﾟｼｮﾝを使用して
 と言う事なので、現在出来ているマクロが
 無駄の無いマクロに成っているのかが疑問に思います。

 フィルタオプションの設定だけで抽出出来ませんか？
 ↓の様な感じで。
 	 <1>      	 <2>      	 <3>      
[1]	日付      	日付      	構成      
[2]	>=2011/3/1	<2011/4/1 	1

 「オートフィルタと、オートフィルタのオプション」なのかな。。。？

 それから、「行削除等して関数で集計」ではなく
 「そのままのデータでピボットテーブル」で出来そうです。
 使いまわすには、記録後変更が必要になりますが。

 (HANA)

 指導ありがとうございます。
 計算方法を手動にしたら、かなり早くなりました。
 きちんとしたﾏｸﾛを学ぶと同時に、処理時間を高速化したいと考えており
 ます。提示できるようなものではありませんが引き続きご指導お願いします。

　　Sheets("Sheet1").Select

    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=4, Criteria1:="="
    Rows("2").Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter Field:=4
    Range("A1").Select
    Selection.AutoFilter

　　Sheets("Sheet1").Select

    Range("A1").Select
    Selection.AutoFilter
    Selection.AutoFilter Field:=5, Criteria1:="1"
    Rows("2").Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter Field:=5
    Range("A1").Select
    Selection.AutoFilter

    Sheets("Sheet1").Select
    Range("A1").Select
    Selection.AutoFilter
    A = Sheets("Sheet1").Range("F1")
    Selection.AutoFilter Field:=1, Criteria1:="<" & A & "", Operator:=xlAnd
    Rows("2").Select
    Range(Selection, Selection.End(xlDown)).Select
    Selection.Delete Shift:=xlUp
    Selection.AutoFilter Field:=1

    Cells(2, 8) = "=SUMPRODUCT'以下略
    Cells(2, 9) = "=SUMPRODUCT'以下略
    行削除はまだあるのですが、だいたいこんな感じです。
 (つつじ)

 HANA さんのコメントの部分はできたでしょうか。処理の最初と最後に

 Sub Azalea
     「計算の手動」 のコード
     現在のコード
     「計算の自動」 のコード
 End Sub
 だけ大幅に変わりそうです。

 ついでに前後に
 Application.ScreenUpdating = False
    ：
 Application.ScreenUpdating = True
 を付け加えると、もうちょっと早くなるかもしれません。
 (Mook)

 お手数かけます。色々とやってみましたが旨くいきません。何処に「計算の手動、
 自動のコードを入れたらよいですか？
 また、ﾏｸﾛ全体を見てどのようにお感じになりましたか？
 お聞かせ頂いたら幸いです。
 (つつじ)

 上に書いたように挿入位置は
 Sub ・・・
 End Sub
 があれば、Sub の直後と End Sub の直前です。

 マクロ全体に関しては、Select が多いとは思いますが、これがパフォーマンスに
 大きな影響を与えているとも思えません。

 「以下略」としてしまっている部分が気になりますが、まずは Calculation を制御
 してみてからでよいと思います。

 ちなみに行削除をしない場合、集計の計算時間はどのくらいなのでしょうか。
 こちらで40000行程度のデータで、
=SUMPRODUCT((YEAR(A1:A40000)=2011)*(MONTH(A1:A40000)=3),C1:C40000)
 で計算したら、一瞬でしたが。
 (Mook)

 ＞ きちんとしたﾏｸﾛを学ぶと同時に、処理時間を高速化したいと考えております。
 と言う事なので、まずはコードから離れて少しだけ。。。

 記録マクロでも、色々な事が可能です。
 下手なコードを書くよりも、高速処理が可能な事もあります。

 心配しなくても、最終的には【それとは別に】ご希望のコードが載せられると思います。
 まずは、一般機能・数式・マクロの記録とその改造　で
 高速処理が出来る方法を探してみられるのが良いと思います。

 記録で出来たコードだけだと、無駄な部分もありますしね。
 　　でも、記録で出来たコードが「きちんとしたマクロでない」なんて事は
 　　決して無いと思いますよ。

 その方が、これから色々なマクロが作れる様になると思いますし
 コードを直接書いた時に、同じ落とし穴が楽々回避できる様に成ると思います。

 まずは、計算方法の切り替えと、画面更新の制御　ですね。
 ちゃんと見てますので、がんばってください。

 (HANA)

 HANAさん、MOOKさん暖かい指導有難うございます。ｴｸｾﾙの学校や教本を見なが
 らﾏｸﾛの学習を始めました。Select が多いとのことですが 、どこが不要なのかも
 分からずこれから学んでいきたいと思っています。指導頂いた項目を今からやって
 みます。最後に画面更新の制御とありますが、どういったことでしょうか？
 (つつじ)

 横入り失礼します。

 >最後に画面更新の制御とありますが、どういったことでしょうか？

  '画面再描画停止
  Application.ScreenUpdating = False

  '処理

  '画面再描画再開
  Application.ScreenUpdating = True

 以下は参考です。

 Office TANAKA - VBA高速化テクニック(Selectするな！)
http://officetanaka.net/excel/vba/speed/s2.htm

 (カリーニン)

 計算の制御は、マクロの記録からご自身でたどり着いてほしかったところですが、
 これを機会に覚えてください。
 カリーニンさんがすでにコメントしてくださってましたので、ぶつかりましたが
 一応そのままあげます。

 最初と最後に以下のようにコード（★のコメントの部分）を追加してくださいというのが、
 HANAさんと私からのコメントでした。

 '--- マクロの記録なら Macro1() など
 Sub Azalea()

 '--- ★画面の更新の停止
    Application.ScreenUpdating = False
 '--- ★計算の自動更新の停止
    Application.Calculation = xlCalculationManual

 '--- 以下 現在のコード
 　Sheets("Sheet1").Select

    Range("A1").Select
    Selection.AutoFilter
        ：
        ：
    Cells(2, 9) = "=SUMPRODUCT'以下略
 '   行削除はまだあるのですが、だいたいこんな感じです。
 '---   ここまで、現在のコード

 '--- ★計算の更新を自動に変更
    Application.Calculation = xlCalculationAutomatic
 '--- ★画面の更新を有効
    Application.ScreenUpdating = True
 End Sub
 (Mook)

 うっかりしてましたが。。。。
 どんな感じでしょう？

 ご報告が無いと
 ＞最終的には・・・
 までたどり着けませんので
 是非書き込みをお願いします。

 (HANA)

コメント返信：

[ 一覧(最新更新順) ]

[[20110502214037]] 『行削除の処理時間を短縮したい』（つつじ） ページの最後に飛ぶ

[[20110502214037]]　『行削除の処理時間を短縮したい』（つつじ）　ページの最後に飛ぶ