１次元配列をワークシートに高速で転記する方法について質問します。

質問

１次元配列をワークシートに高速で転記する方法について質問します。
エクセル2000です。
1000万までの範囲で素数を抽出したいと思いました。
そこで下記のようなコードを書きました。
最初は配列にいれず一個ずつセルに転記させたところ664,579個の素数抽出に1分37秒かかったので、配列を用意して下記のようにしたところ1分15秒まで短縮されました。

質問１：配列を使った割には劇的に短縮されないのはなぜでしょうか？

質問２：下記のコードでは最初に取り込んだ1次元外配列をシートに貼るために2次元に変換する際、2次元方向（列）は256で固定、1次元方向（行）は計算で求めたのですが、その結果、要素数が合わず、後の方のデータがない部分が0とシートに出てしまいます。
こうならない方法がありますか？

質問３：一次元配列をワークシートに配置するため二次元配列に変換するのに、もっと良い方法があったらご教示ください。

質問４：配列をワークシートに転記する場合
　　Application.ScreenUpdating = False
　　Application.Calculation = xlCalculationManual
はあってもなくとも速度に変化がありませんでした。このような貼り付け（配列から一度に転記）には不要なのでしょうか？

たくさん質問して申し訳ありません。
宜しくお願いいたします。

Sub test()
　　Dim t As Date
　　Dim a As Long, b As Long, c As Long, Num As Long, r As Long, i As Long, x As Long, y As Long
　　Dim buf As Boolean
　　Dim myPrm() As Long, myRng() As Long

　　t = Now()
　　c = 0
　　For Num = 2 To 10000000
　　　　a = Int(Sqr(Num)) '平方根算出
　　　　buf = True
　　　　For b = 2 To a '除数
　　　　　　If Num Mod b = 0 Then '割切れたら
　　　　　　　　buf = False '素数じゃない
　　　　　　　　Exit For
　　　　　　End If
　　　　Next b
　　　　If buf Then '割切れなかったら
　　　　　　ReDim Preserve myPrm(c) '添字追加
　　　　　　myPrm(c) = Num
　　　　　　c = c + 1 '素数カウント
　　　　End If
　　Next Num

　　r = Application.WorksheetFunction.RoundUp((UBound(myPrm) + 1) / 256, 0) '必要行数取得

　　ReDim myRng(1 To r, 1 To 256) '2次元配列のサイズ変更

　　For i = LBound(myPrm) To UBound(myPrm) '2次元配列に格納
　　　　x = IIf((i + 1) Mod 256 = 0, 256, (i + 1) Mod 256)
　　　　y = Application.WorksheetFunction.RoundUp((i + 1) / 256, 0)
　　　　myRng(y, x) = myPrm(i)
　　Next i

　　Application.ScreenUpdating = False
　　Application.Calculation = xlCalculationManual
　　Cells(1, 1).Resize(r, 256).Value = myRng() 'セル範囲に転記
　　Application.Calculation = xlCalculationAutomatic
　　Application.ScreenUpdating = True

　　MsgBox c & "個抽出しました。" & vbNewLine & "所要時間：" & Format(Now() - t, "hh:mm:ss")
End Sub

回答No.5

end-u · ベストアンサー

あまり本筋と関係ない箇所も弄ってますが、一例として。

Sub try()
　　Const MX As Long = 10000000
　　Dim flg(2 To MX) As Boolean
　　Dim cnt As Long
　　Dim cx　As Long
　　Dim i　 As Long
　　Dim j　 As Long
　　Dim r　 As Long
　　Dim c　 As Long
　　Dim t　 As Single

　　Sheets.Add
　　cx = Columns.Count
　　ReDim v(1 To MX \ cx + 1, 1 To cx) 'As Long
　　t = Timer
　　For i = 2 To MX
　　　　If Not flg(i) Then
　　　　　　For j = i + i To MX Step i
　　　　　　　　flg(j) = True
　　　　　　Next
　　　　End If
　　Next
　　r = 1
　　For i = 2 To MX
　　　　If Not flg(i) Then
　　　　　　cnt = cnt + 1
　　　　　　'シンプルにIf分岐で。
　　　　　　If c = cx Then
　　　　　　　　c = 1
　　　　　　　　r = r + 1
　　　　　　Else
　　　　　　　　c = c + 1
　　　　　　End If
　　　　　　v(r, c) = i
　　　　End If
　　Next
　　Rows(1).Resize(r).Value = v
　　'Rows(r).Replace What:="0", Replacement:="", LookAt:=xlWhole
　　Erase v
　　Debug.Print cnt, Timer - t
End Sub

>質問２
上記は簡易的に、配列をVariant型にして0値を気にしなくていいようにしてますけど、
メモリ負担が大きいのでLong型＆Replaceのほうが良いとは思います。
そのほうが速いですし。

>質問４
配列から一気に書き出す場合は不要..というか、
あまり気にしなくて良いケースが多いと思います。
ただ、配列サイズが極端に大きい場合は効果がある時もありますし、
数式の有無も関係してくるので状況に応じて、ではないでしょうか。

お礼

end-uさま、鮮やかなお手並み、畏れ入りました。
な、なんと3秒！比較にならない高速化ですです！

解読するのに時間がかかりました。

> ReDim v(1 To MX \ cx + 1, 1 To cx)

これは、素数の数から必要な配列のサイズを求めるのではなく、最初から最大限の範囲で設定しておけば、行方向の増減は気にせず、直接二次元配列に素数を取り込めるということですね？

そして一次元配列で素数でない数（合成数）にフラグをたてる。

　　For i = 2 To MX '2から目標値までの間に
　　　　If Not flg(i) Then '合成数フラグがTRUEでなければ
　　　　　　For j = i + i To MX Step i 'その倍数（当然合成数）に
　　　　　　　　flg(j) = True '合成数フラグをTRUE
　　　　　　Next j
　　　　End If
　　Next i

という理解でよいでしょうか？

それを最後に合成数フラグがTRUEでないものを二次元配列に取り込む。
これがまさに「エラトステネスの篩」の考え方なんですね、勉強になりました。

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

補足

調子にのって、一桁多い、1億までの素数もやってみました。
5,761,455個でした。35秒で計算＆転記できました。
これなら、10億だってできるなあと思ったら・・・。
ワークシートは256列、65,536行・・・・16,777,216個までしか書き込めないですね。残念。

今度エクセル2007を借りてやってみようかな。

回答No.6

end-u

2007でも10億は無理だと思います。
Excelで使用できるメモリ量には限界があります。
（#じゃ限界量は幾つ？って訊かないでくださいネ...プロじゃないので解かりません）
Dim flg(2 To 1000000000) As Boolean なんてやってしまうと
Boolean型変数のサイズが2Byteですから単純計算でも2GB近くなります。

Byte型変数を使っても、私の環境では480百万弱が限界です。
ちょっと試してみましたが、450百万まで 23,853,038個 130.82秒でした。

>という理解でよいでしょうか？
はい。
> ReDim v(1 To MX \ cx + 1, 1 To cx)
これは考え方の一例で、あえて最大サイズとってます。
他のケースで応用する時の参考にしていただければ。
（今回のケースでは素数の数だけですのでムダが大きいです）

また、先のコードは速度的に大差なかったのでシンプルな書き方にしてますが、
実際には
>For j = i + i To MX Step i
ここは
For j = i ^ 2 To MX Step i
でいいみたいです。
ただ、i ^ 2がオーバーフローしないように事前判定する、などの処理が必要になってきます。
...その辺りは宿題というか、機会があれば工夫してみてくださいね :D

お礼

> Excelで使用できるメモリ量には限界があります。

やはり・・・。
実はワークシートに転記しないで検索の上限までの最大の素数とその個数を求めようとやってみて実行時エラーになってしまいました。
これは別途質問を立ててしまいました。

今回もほんとうにありがとうございました。
宿題がんばります。

回答No.4

nag0720

時間がかかっているのは素数判定の部分ですね。

＃２では、奇数だけを素数判定するように変更していますが、
さらに工夫して、除数を素数だけにすればもっと速くなります。

c = 2
ReDim Preserve myPrm(1) '添字追加
myPrm(0) = 2
myPrm(1) = 3
For Num = 5 To 10000000 Step 2
a = Int(Sqr(Num)) '平方根算出
buf = True
i = 1
b = myPrm(i) '除数
Do While b <= a
If Num Mod b = 0 Then '割切れたら
buf = False '素数じゃない
Exit Do
End If
i = i + 1
b = myPrm(i) '除数
Loop
If buf Then '割切れなかったら
ReDim Preserve myPrm(c) '添字追加
myPrm(c) = Num
c = c + 1 '素数カウント
End If
Next Num

お礼

> 除数を素数だけにすればもっと速くなります。

「エラトステネスの篩」ですね。
そうやりたかったのですが、うまくコードが思い浮かばす、総当りしかできませんでした。
助かりました。
No2のgt-tさんのコードにあわせ下記のようにしたところ、劇的に高速化しました。
なんと25秒です！！

Sub Eratosthenes() '最速
　　Dim t As Date
　　Dim a As Long, b As Long, c As Long, Num As Long, r As Long, i As Long, x As Long, y As Long, z As Long
　　Dim buf As Boolean
　　Dim myPrm() As Long, myRng() As Long

　　t = Now()
　　c = 1
　　ReDim Preserve myPrm(c)　'添字追加
　　myPrm(0) = 2
　　For Num = 3 To 10000000 Step 2
　　　　a = Int(Sqr(Num)) '平方根算出
　　　　buf = True
　　　　i = 0
　　　　b = myPrm(i) '除数
　　　　Do While b <= a
　　　　　　If Num Mod b = 0 Then '割切れたら
　　　　　　　　buf = False '素数じゃない
　　　　　　　　Exit Do
　　　　　　End If
　　　　　　i = i + 1
　　　　　　b = myPrm(i) '除数
　　　　Loop
　　　　If buf Then '割切れなかったら
　　　　　　ReDim Preserve myPrm(c) '添字追加
　　　　　　myPrm(c) = Num
　　　　　　c = c + 1 '素数カウント
　　　　End If
　　Next Num

　　Debug.Print "A：" & Format(Now() - t, "hh:mm:ss")

　　z = Columns.Count
　　r = Application.WorksheetFunction.RoundUp(c / z, 0) '必要行数取得
　　ReDim myRng(1 To r, 1 To z) '2次元配列のサイズ変更
　　i = 0
　　For y = 1 To r
　　　For x = 1 To z
　　　　myRng(y, x) = myPrm(i)
　　　　i = i + 1
　　　　If y = r Then
　　　　　If z * (r - 1) + x > c - 1 Then
　　　　　　Exit For
　　　　　End If
　　　　End If
　　　Next
　　Next
　　Debug.Print "B：" & Format(Now() - t, "hh:mm:ss")
　　Cells(1, 1).Resize(r, z).Value = myRng() 'セル範囲に転記
　　Debug.Print "C" & Format(Now() - t, "hh:mm:ss")
　　Rows(r).Replace What:="0", Replacement:="", LookAt:=xlWhole '不要0データ消去
　　Debug.Print c & "個抽出しました。" & vbNewLine & "所要時間：" & Format(Now() - t, "hh:mm:ss")

回答No.3

gt-t

#2です。
置き換えの部分にミスがありました。
>　Application.ScreenUpdating = False
　Cells.Replace "0", ""
　Application.ScreenUpdating = True
の部分と質問２に対する回答は無視してください。

お礼

ありがとうございます。
Rows(r).Replace What:="0", Replacement:="", LookAt:=xlWhole
で、うまくいきました。

回答No.2

gt-t

上記コードを試したところ最初1分7秒でした。
どこで時間がかかっているかを調べるために
A:素数検索部分
B:配列格納部分
C:残りにわけました。
結果
A00:00:48
B00:01:05　(+17秒）
664579個抽出しました。所要時間：00:01:07（+2秒）
これを次のように変更しました。
----以下　コード-----

Sub test()
　Dim t As Date
　Dim a As Long, b As Long, c As Long, Num As Long, r As Long, i As Long, x As Long, y As Long
　Dim buf As Boolean
　Dim myPrm() As Long, myRng() As Long

　t = Now()
　c = 1
　ReDim Preserve myPrm(0)
　myPrm(0) = 2
　　For Num = 3 To 10000000 Step 2
　　a = Int(Sqr(Num)) '平方根算出
　　buf = True
　　For b = 3 To a Step 2 '除数
　　　If Num Mod b = 0 Then '割切れたら
　　　　buf = False '素数じゃない
　　　　Exit For
　　　End If
　　Next b
　　If buf Then '割切れなかったら
　　　ReDim Preserve myPrm(c) '添字追加
　　　myPrm(c) = Num
　　　c = c + 1 '素数カウント
　　End If
　Next Num
　r = Application.WorksheetFunction.RoundUp(c / 256, 0) '必要行数取得
　ReDim myRng(1 To r, 1 To 256) '2次元配列のサイズ変更
　Debug.Print "A" & Format(Now() - t, "hh:mm:ss")
　i = 0
　For y = 1 To r
　　For x = 1 To 256
　　　myRng(y, x) = myPrm(i)
　　　i = i + 1
　　　If y = r Then
　　　　If 256 * (r - 1) + x >= c - 1 Then
　　　　　Exit For
　　　　End If
　　　End If
　　Next
　Next
　Debug.Print "B" & Format(Now() - t, "hh:mm:ss")
　Cells(1, 1).Resize(r, 256).Value = myRng() 'セル範囲に転記
　Debug.Print c & "個抽出しました。" & vbNewLine & "所要時間：" & Format(Now() - t, "hh:mm:ss")
　Application.ScreenUpdating = False
　Cells.Replace "0", ""
　Application.ScreenUpdating = True
　Debug.Print "C" & Format(Now() - t, "hh:mm:ss")
End Sub

----コード　終わり---
>質問３
シンプルに2次元配列に格納すれば速くなります。
>質問２
今回は無理やり置き換えてみました。
>質問１
配列にする前より22秒短縮されており、配列に書き込むところは1秒ぐらいしか掛かっていないので今回劇的に変化したいのは、配列以外に原因があるといえます。

参考　変更後のコードでの時間
A00:00:27
B00:00:27
664579個抽出しました。所要時間：00:00:28
C00:00:29

お礼

ありがとうございました。
劇的に早くなりました。
シンプルに2次元配列に格納したいのですが、列方向が固定で行方向が未定だとReDimがつかえませんよね。
仕方なく1次元配列に入れてから2次元へ変換させたんです。
でもその方法が拙かったようです。
感謝いたします。

補足

If 256 * (r - 1) + x >= c - 1 Then
は
If 256 * (r - 1) + x > c - 1 Then
ですね？

回答No.1

cafe_au_lait

まずは、「Next Num」の時点と「Next i」の時点での所要時間を出力してみてください。
こちらで試したところでは、セル転記にはほとんど時間がかかっていません。

お礼

ありがとうございます。
問題は転記時間ではなかったのですね。