こういうのが・・・10000件くらいあるとするじゃないですか。
んで、担当者ごとのグループの数と個人の数の合計を知りたかったんすよ。
(グループ番号ってのは別のファイルから引っ張ってきてる。)
そんで今までやってたマクロでは
グループ番号順に並び変えて
- 担当者コードで抽出
- 別シートにグループ番号コピー
- ①重複削除して数える
- ②グループ番号空欄を数える
- ①+②
で出してました。
メイン処理こんな感じで
Sub Macro1() Debug.Print "===START===:" & Timer Sheets("Sheet2").Select Dim myCode: myCode = Sheet2.Range("A2:A32") Call Sheet1.最終行設定 Debug.Print "===SORT===:" & Timer Call Sheet1.グループ番号でソート Debug.Print "===LOOP===:" & Timer Dim outputRow As Long: outputRow = 2 Dim code For Each code In myCode Call Sheet1.担当者コードで抽出(CLng(code)) Call Sheet3.重複削除 Cells(outputRow, 2).FormulaR1C1 = _ "=COUNTIFS(Sheet1!C[-1],"""",Sheet1!C[1]," & code & ")+COUNT(Sheet3!C[-1])" Cells(outputRow, 2).Copy Cells(outputRow, 2).PasteSpecial Paste:=xlPasteValues outputRow = outputRow + 1 Next Debug.Print "===END===:" & Timer End Sub
処理 | timer | かかった時間 |
---|---|---|
===START=== | 68871.06 | 0.02 |
===SORT=== | 68871.08 | 0.86 |
===LOOP=== | 68871.94 | 10.16 |
===END=== | 68882.1 | 11.04 |
実際にはもっとデータ複雑で120秒くらいかかってたの(´Д⊂グスン
そこで教えて偉い人!!
グループが空欄の場合、ダミーのコード(例えば "DUMMY"&ROW()など)を発生させてから、グループと担当者番号を連結して重複削除した数を数えれば一発で住むのでは?
— ことりちゅん@えくせるちゅんちゅん (@KotorinChunChun) 2020年3月11日
現状の遅いコードを見てみないと、速度が改善されるかわからないけど。
そもそもVBAなのか、関数なのか、作業列は使えるのか
おぉ、目から鱗。ふむふむふむふむ?←おいw
Sub Macro4() Debug.Print "===START2===:" & Timer Sheets("Sheet2").Select Dim myCode: myCode = Sheet2.Range("A2:A32") Call Sheet1.最終行設定 Debug.Print "===SORT2===:" & Timer Call Sheet1.数値に置き換えてソート ' Debug.Print "===SET_FORMULA===:" & Timer Call Sheet1.数式設定 Sheets("Sheet2").Select Range("C2").FormulaR1C1 = "=COUNTIF(Sheet1!C[2],RC[-2])" Range("C2").AutoFill Destination:=Range("C2:C32") Debug.Print "===END===:" & Timer End Sub
これで
処理 | timer | かかった時間 |
===START2=== | 74775.55 | 0.02 |
===SORT2=== | 74775.57 | 0.15 |
===SET_FORMULA=== | 74775.72 | 0.64 |
===END=== | 74776.36 | 0.81 |
ポイントはソートする前に数値に置き換えることと
countifの範囲を極力狭くすること。
実際のものは120秒→20秒くらいにスピードアップ!! やったね!!
ちゅんちゅん様!! ありがとうございます!!