VBA 高速化

質問

以下のコードを改良して早く処理できるようにしたいです。素因数分解をして、素因数の数を数えるプログラムです。
Sub 素因数を数える()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim I As Long, j As Long, cnt As Long, wS As Worksheet, k As Long,
Set wS = Worksheets("Sheet2")
k = Worksheets("Sheet1").Range("A100010").End(xlUp).Row - 1
For dd = 3 To k
Call aaa(Range("A" & dd))
Next dd
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Private Sub aaa(ByVal Target As Range)
Set wS = Worksheets("Sheet2")
If Intersect(Target, Range("A:A")) Is Nothing Or Target.Count > 1 Then Exit Sub
With Target
If Not IsNumeric(.Value) Then Exit Sub
If .Value Mod 1 = 0 Then
Range("D1") = .Value
For I = 1 To wS.Cells(Rows.Count, "A").End(xlUp).Row
Do While Cells(Rows.Count, "D").End(xlUp) Mod wS.Cells(I, 1) = 0
cnt = cnt + 1
Cells(Rows.Count, "D").End(xlUp).Offset(1) = Cells(Rows.Count, "D").End(xlUp) / wS.Cells(I, 1)
Loop
If Cells(Rows.Count, "D").End(xlUp) = 1 Then Exit For
Next I

End If
.Offset(, 1) = cnt
End With
Range("D:D").Clear
End Sub

以前教えていただいたコードを使って書きました。
どういう状況で使っているかといいますと、シート1のＡ列に自然数を2から順番に並べ、シート2のＡ列に素数を並べておき、プログラムを実行すると、Ｂ列に素数の数が表示されるという具合です。
大変役に立っていたのですが、10萬ほどのデータを扱おうとすると、自分のコンピュータでは時間がかかりすぎます。そこでコードを改良して高速化をしたいのです。
いま考えられる改良点は、
1、Ｄ列を使って行っている処理をメモリで行うようにして、セルへのアクセスを省けないか
2、aaaの5行目の、"wS.Cells(Rows.Count, "A").End(xlUp).Row"この処理を簡単な変数の処理で代用できないか
ということです。他にも高速化できる方法があれば教えてください。
よろしくお願いします。

回答No.3

skksk

たとえば以下のようなユーザー定義関数で解決できないでしょうか？

Function CountFactorial&(N&)
Dim cnt&, fct&, lim&
cnt = 0
fct = 2
lim = Sqr(N)
Do Until fct > lim
If N Mod fct = 0 Then
N = N / fct
cnt = cnt + 1
If N = 1 Then Exit Do
Else
fct = fct + 1
End If
Loop
If cnt = 0 Then cnt = 1
CountFactorial = cnt
End Function

計算にセルを使わない分効率的です。
最終行を取得するなど面倒なことをしなくてすむので、使うのも簡単だと思います。
私のPCでは、60060（素因数7個）をセル上で1万回計算するのに30秒弱でした。
上のはとくに工夫もなにもないアルゴリズムですが、工夫次第ではもっと速くすることも可能だと思います。

回答No.2

nishi6

質問に掲示されているモジュールを見ての感想ですが、解答者が動かそうとしてもなかなか大変そうです。

最初の「Dim　～　」は何かが抜けているようです。

このモジュールが動いているとすると、「Option Explicit（変数の宣言を強制する）」がOffのようです。そのため、最初のモジュールのDim宣言が無意味になっています。結局、変数宣言していないに等しいモジュールです。ザッと見て「素因数分解」よりも、どのセルを読んで、どのセルに書き込むかをEnd(xlUp)を駆使して書いてあるように思えます。

また、意味のない設定や判定が見受けられます。数値が因数かどうかはその数値の平方根まで調べればいいはずですが、そのあたりの制限が見受けられません（多分）。

質問のモジュールでは、I/Oについて、データ10万件に対し、40～50万回の出力と10万回の消去、最悪、10万回×10万回（ふるいがあれば少なくなる）の入力を行っているように思えます。このアルゴリズムではなかなか速くならないでしょう。

それらの理由で質問者様はモジュールの変更について質問されたかもしれませんが、如何せん、Sheet1とSheet2に何が設定されているか不明で解答のしようがありません。「素因数分解」ということになると、「素数一覧」いわゆる「ふるい」をSheet2に設定されているのでしょうか。またSheet1はA列のどこから入力されているかもよくわかりません。A3あたりから2から1おきの数値でしょうか。

時間がかかるということなので独自のモジュールを作ってみました。
A列に2から100,000（200,000、300,000）までの数値を入力して横に因数の個数と素因数分解の式を表示するようにしてみました。「ふるい」などは使っていません。
　100,000件・・・約2秒　(1秒)
　200,000件・・・約5秒　(2秒)
　300,000件・・・約7秒　(3秒)
　　パソコンは 1.70GHz、実装メモリ4.00GB、Windows7(32ビット)、Excel2010。これが速いのか遅いのかよくわかりません。「ふるい」を使えばもっと速くなるでしょう。 (　)の秒数は3.40GHz、実装メモリ16.00GB、Windows7(64ビット)です。

ちなみに「999,999,999,999,999」を素因数分解してみると、因数8個、素因数分解:=3*3*3*31*37*41*271*2906161 でした。

質問のモジュールですが、「Option Explicit」を宣言し、変数管理を正しくして考えるべきだと思います。また、効率のいいアルゴリズムを採用すべきでしょう。ちなみに、自分で作ったモジュールではEnd(xlUp)は一切使っていません。変数宣言を除けば数行のモジュールで書けるはずです。シートとのインプット/アウトプットは各々1回だけです。Excelもすごくなりました。

ご参考に。

回答No.1

WindFaller

こんにちは。

これをアップされた方は、アルゴリズムは他の方の作かもしれませんが、きちんとしたVBAを知っている方のプログラムで、私は、同じ掲示板で、これに手を加えるほど、無神経ではありません。ここの常連で、無遠慮にする方はいるかもしれませんが、あえて手を入れて劇的に速くなるとは思えません。
（別に釘を打つ目的ではありませんが、率直な感想です)

>1、Ｄ列を使って行っている処理をメモリで行うようにして、セルへのアクセスを省けないか

あえて言うなら、その都度、セルに書きださず、まとめて出力すれば速くはなります。

>2、aaaの5行目の、"wS.Cells(Rows.Count, "A").End(xlUp).Row"この処理を簡単な変数の処理で代用できないか
100,000個という数で比較したわけではありませんが、見た目には、そう思えても、それをやっても効果はありません。

質問全体的にみて言えるのは、Excelの限界かと思います。
もし、劇的に速くさせるには、他の言語をお使いください。
Excel VBA自体は、インタプリタ言語ではありませんが、その都度、中間言語に翻訳されて働いていますから、どうしても、オーバーヘッドが掛かってしまいます。

もちろん、VBA自体で他の方法がないわけはありませんが、100,000個という処理ですと、ざっとみた感じでは、10分は越えてくるのかな、という印象です。

それから、前回の質問(#8552832)の回答者には、なんのレスもせずに、1ヶ月近く放り出したまま、その回答に要求をつける質問を出すのは、マナー自体に問題がありますから、元の回答者さんは分かりませんが、少なくとも私は、点が辛くなるのはやむを得ないです。お礼を書くなら書いて、前の質問を締めるなどの対処をしてください。