1次元配列をワークシートに高速で転記する方法について質問します。
エクセル2000です。
1000万までの範囲で素数を抽出したいと思いました。
そこで下記のようなコードを書きました。
最初は配列にいれず一個ずつセルに転記させたところ664,579個の素数抽出に1分37秒かかったので、配列を用意して下記のようにしたところ1分15秒まで短縮されました。
質問1:配列を使った割には劇的に短縮されないのはなぜでしょうか?
質問2:下記のコードでは最初に取り込んだ1次元外配列をシートに貼るために2次元に変換する際、2次元方向(列)は256で固定、1次元方向(行)は計算で求めたのですが、その結果、要素数が合わず、後の方のデータがない部分が0とシートに出てしまいます。
こうならない方法がありますか?
質問3:一次元配列をワークシートに配置するため二次元配列に変換するのに、もっと良い方法があったらご教示ください。
質問4:配列をワークシートに転記する場合
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ベストアンサー
- 回答日時:
あまり本筋と関係ない箇所も弄ってますが、一例として。
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
>質問2
上記は簡易的に、配列をVariant型にして0値を気にしなくていいようにしてますけど、
メモリ負担が大きいのでLong型&Replaceのほうが良いとは思います。
そのほうが速いですし。
>質問4
配列から一気に書き出す場合は不要..というか、
あまり気にしなくて良いケースが多いと思います。
ただ、配列サイズが極端に大きい場合は効果がある時もありますし、
数式の有無も関係してくるので状況に応じて、ではないでしょうか。
この回答への補足
調子にのって、一桁多い、1億までの素数もやってみました。
5,761,455個でした。35秒で計算&転記できました。
これなら、10億だってできるなあと思ったら・・・。
ワークシートは256列、65,536行・・・・16,777,216個までしか書き込めないですね。残念。
今度エクセル2007を借りてやってみようかな。
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でないものを二次元配列に取り込む。
これがまさに「エラトステネスの篩」の考え方なんですね、勉強になりました。
大変ありがとうございました。
No.6
- 回答日時:
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
- 回答日時:
時間がかかっているのは素数判定の部分ですね。
#2では、奇数だけを素数判定するように変更していますが、
さらに工夫して、除数を素数だけにすればもっと速くなります。
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.2
- 回答日時:
上記コードを試したところ最初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
----コード 終わり---
>質問3
シンプルに2次元配列に格納すれば速くなります。
>質問2
今回は無理やり置き換えてみました。
>質問1
配列にする前より22秒短縮されており、配列に書き込むところは1秒ぐらいしか掛かっていないので今回劇的に変化したいのは、配列以外に原因があるといえます。
参考 変更後のコードでの時間
A00:00:27
B00:00:27
664579個抽出しました。所要時間:00:00:28
C00:00:29
この回答への補足
If 256 * (r - 1) + x >= c - 1 Then
は
If 256 * (r - 1) + x > c - 1 Then
ですね?
ありがとうございました。
劇的に早くなりました。
シンプルに2次元配列に格納したいのですが、列方向が固定で行方向が未定だとReDimがつかえませんよね。
仕方なく1次元配列に入れてから2次元へ変換させたんです。
でもその方法が拙かったようです。
感謝いたします。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) いつもお世話になっております、VBAで教えて頂きたいのですが 2 2022/05/05 22:20
- Visual Basic(VBA) 別シートから年齢別の件数をカウントしたいの続き 5 2023/01/24 00:16
- Visual Basic(VBA) 稀に1円合いません? Sheet1から金額と個数を貼り付ける下記コードで、金額を切り上げるコードを何 3 2022/09/05 15:11
- Visual Basic(VBA) excel vbaでvlooupの変数がわかりません。 7 2022/05/30 09:35
- Visual Basic(VBA) 別シートのデータを参照して値を入れたい。 まとめデータシートのC列D列の値を商品一覧シートのコードが 7 2022/08/17 13:20
- Visual Basic(VBA) 数字が「0」の列を削除するため、下記のコードを実行しましたが、コンパイルエラーSubまたはFunct 3 2022/12/04 00:00
- Visual Basic(VBA) ExcelVBAで、index、match関数を使用して、指定範囲に出力したい 3 2022/10/18 21:53
- Visual Basic(VBA) コード名シートA列と集計シートA列のコードが一致したら、コード名シートA5からk12の範囲をコピーし 1 2022/08/29 23:46
- Visual Basic(VBA) VBAで質問ですが、皆さんはどの様に導き出しているのでしょうか? 6 2022/05/03 21:53
- Excel(エクセル) VBAで組み合わせ算出やCOUNTIFSの処理を高速化したいです。 4 2022/04/07 02:38
このQ&Aを見た人はこんなQ&Aも見ています
関連するカテゴリからQ&Aを探す
おすすめ情報
このQ&Aを見た人がよく見るQ&A
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
VBA 変数名に変数を使用したい。
-
C#でbyte配列から画像を表示さ...
-
vba フィルター 複数条件 3つ以...
-
エクセルでXY座標に並べられた...
-
複数のtextboxの処理を一括で行...
-
VB.NETの配列にExcelから読み込...
-
OutOfMemoryExceptionの回避策...
-
Dir関数で読み取り順を操作でき...
-
大量の変数を定義するにはどう...
-
配列の中の最大値とそのインデ...
-
EXCEL VBA 配列デー...
-
1ビットごとの配列を作りたい
-
Segmentation Fault (メモリ制限?)
-
Excelのメモリ(配列)の上限は2G...
-
8桁文字列を16進数バイト配列に...
-
COBOLの基本的な事なので...
-
C言語によるプログラミング
-
Redim とEraseの違いは?
-
型が一致しませんとエラー
-
コンボボックスのインデックス...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
VBA 変数名に変数を使用したい。
-
vba フィルター 複数条件 3つ以...
-
C#でbyte配列から画像を表示さ...
-
Excel2010のinputboxで複数デー...
-
エクセルでXY座標に並べられた...
-
構造体配列の特定のメンバーをF...
-
定数配列の書き方
-
コンボボックスのインデックス...
-
OutOfMemoryExceptionの回避策...
-
Dir関数で読み取り順を操作でき...
-
CheckBoxの配列化
-
構造体配列内の文字列検索のよ...
-
COBOLの基本的な事なので...
-
Redim とEraseの違いは?
-
VBAで配列引数を値渡しできない...
-
2次元配列の初期値
-
配列の中の最大値とそのインデ...
-
VB6からの移行したいけど、VB.N...
-
大量の変数を定義するにはどう...
-
VB6のメモリ解放に関して
おすすめ情報