

数値計算するマクロを作っていて思うことがあり質問しました。
ワークシートに3万セルのデータがあり、それを元に計算し、結果を別シートに記入します。
(1)計算をすべてワークシート上で行う方法
(2)データを全て配列に読み込み、配列上で計算し、結果のみ配列に書き出す方法
どちらがいいの?
考えや情報が色々と頭の中でぐるぐる回ってます。
・せっかくの「表計算」ソフトなのだから当然(1)?
・シート上の方が色々な関数を使える
・配列上の方が計算速いと聞く
・3万セルごときどちらも同じ?
・10万セルの配列読み込み11秒(For Next)2秒(Range型変数使用)の書籍記事
・100万セルの配列読み込みコンマ数秒(For Next)コンマゼロ数秒(Range型)のweb上記事
今まで私自身が体感したのは、例え3万個といえど
・行削除が多いと遅い
・コピペが多いと遅い(5千回で10秒)
・ファイル数が多いと遅い(open/close1個1秒弱)
・シート上だと関数はやっぱり豊富
・セルへのアクセスは不明??
どっちがいいですか?100万セルでも同じですか?
どっちでやってますか?
No.1ベストアンサー
- 回答日時:
基本のその1は「セルを読み書きする回数」が少ないほど早い、です。
基本のその2は、エクセルのワークシート関数で計算する方が、VBAで同じ計算をするよりも遥かに速い、です。
たとえば、VBA初心者さんが非常に多く陥りがちな例として
sub macro1()
dim r as long
for r = 1 to 9999
cells(r, "B") = cells(r, "A") * 2
next r
end sub
みたいな具合に、対象のセル「全部を一つずつ舐めまわすように」一個一個丁寧に、合計9999回の出し入れをします。
これを
sub macro2()
range("B1:B9999").formula = "=A1*2"
end sub
みたいに合計9999個のセル範囲に「一回で」処理を行うことで、各段の高速化が図れます。
これに加えて、セルを触る(行削除・行挿入等含む)ことで、シート上に既存の関数セルの内で「再計算を余儀なくされる数式」の多寡が問題となるケースも考えられます。
たとえば
=SUM(C:C)
のような関数がシート上のどこかに(しかも沢山)記入されていたとすると、マクロが行の挿入削除を行えば、関数はマクロの実行に「割り込んで」再計算を走らせます。
その関数が一個二個なら実用上問題ありませんが、リストの上から下まで漏れなくこういった関数が記入されていたり、それぞれの関数式がまた効率が悪くて計算が遅かったりすると(こういう状況は非常にしばしば発生します)、目も当てられない反応の遅さに直結します。
ご質問の直接の回答として
>(1)計算をすべてワークシート上で行う方法
>(2)データを全て配列に読み込み、配列上で計算し、結果のみ配列に書き出す方法
>どちらがいいの?
これはワークシートで行う計算式の、効率次第です。
数式が単純なら、ワークシート上で計算すべきです。
しかし何某か非常に複雑な計算式を頑張って作成していた場合などでは、マクロによって配列で計算を行い、「結果を一発で転記する」ことで高速化する事も十分あり得る話となります。
>・せっかくの「表計算」ソフトなのだから当然(1)?
単純な計算ならその通りです。
>・シート上の方が色々な関数を使える
シート上で使える関数のほとんどは、VBAでも利用できます。しかしその関数単独であれば、シート上で計算したほうが高速です。
>・配列上の方が計算速いと聞く
>・3万セルごときどちらも同じ?
計算の内容次第です。
たとえば
Sub 比較()
Dim a As Variant, res As Variant, x As Variant
'準備
Range("A1:A50000").Formula = "=ROW()"
Range("A1:A50000").Value = Range("A1:A50000").Value
'ワークシート計算
Range("C1") = Timer
Range("B1:B50000").Formula = "=A1*2"
Range("D1") = Timer
'配列に一括取得
Range("C2") = Timer
a = Application.Transpose(Range("A1:A50000").Value)
res = a
For x = 1 To UBound(a)
res(x) = a(x) * 2
Next x
Range("B1:B50000") = Application.Transpose(res)
Range("D2") = Timer
End Sub
といった具合に比較してみると、ワークシートで計算したほうが3倍高速であるといった結果になります。
>・10万セルの配列読み込み11秒(For Next)2秒(Range型変数使用)の書籍記事
>・100万セルの配列読み込みコンマ数秒(For Next)コンマゼロ数秒(Range型)のweb上記事
前述した通り、10万セルを「一個ずつちんたら読み込み」してたのでは、VBAだの配列を使う以前の問題です。
ありがとうございます。
配列計算ではループで回してるので、セル計算でもループで回してみたら、40倍遅くなりました。
セルへのアクセスは一気にやった方がいいようですね。
ただ、回答いただいたセル上で一気に書く方法はFormulaで"=A1*2"のように単純なので表現出来ましたが、長い式になると可読性の問題が出そうです。総合的には配列に読み込み計算し一気にセルに記述するのがよさそうです。
お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!
似たような質問が見つかりました
- Visual Basic(VBA) 3つのプロシージャをまとめたら実行時エラー発生で対応不能 6 2022/05/17 01:47
- Visual Basic(VBA) ファイル全てを .xlsm に変更したところ、プログラムが途中で落ちてしまっています 17 2022/12/07 12:03
- Visual Basic(VBA) vba 等間隔の列に対しての計算 6 2022/05/17 20:15
- Excel(エクセル) VBAで組み合わせ算出やCOUNTIFSの処理を高速化したいです。 4 2022/04/07 02:38
- Visual Basic(VBA) Excel のユーザー定義関数でソルバーが動作しない 1 2022/09/05 19:51
- Excel(エクセル) エクセルで時間の合計 5 2022/06/04 10:54
- Excel(エクセル) VBA オリジナル関数で選択セルの合計を作成したい 3 2023/03/19 19:45
- Visual Basic(VBA) まとめシートから集計シートへA列のコードが一致したら1行コピーするマクロをネット上で見つけました。こ 1 2022/08/30 14:11
- Excel(エクセル) ユーザー定義について質問です。 2 2023/06/28 13:21
- Excel(エクセル) エクセル VBAでセル内容を別の列の最下行に転記したい 2 2022/11/29 08:47
関連するカテゴリからQ&Aを探す
おすすめ情報
- ・漫画をレンタルでお得に読める!
- ・一番好きなみそ汁の具材は?
- ・泣きながら食べたご飯の思い出
- ・「これはヤバかったな」という遅刻エピソード
- ・初めて自分の家と他人の家が違う、と意識した時
- ・いちばん失敗した人決定戦
- ・思い出すきっかけは 音楽?におい?景色?
- ・あなたなりのストレス発散方法を教えてください!
- ・もし10億円当たったら何に使いますか?
- ・何回やってもうまくいかないことは?
- ・今年はじめたいことは?
- ・あなたの人生で一番ピンチに陥った瞬間は?
- ・初めて見た映画を教えてください!
- ・今の日本に期待することはなんですか?
- ・集中するためにやっていること
- ・テレビやラジオに出たことがある人、いますか?
- ・【お題】斜め上を行くスキー場にありがちなこと
- ・人生でいちばんスベッた瞬間
- ・コーピングについて教えてください
- ・あなたの「プチ贅沢」はなんですか?
- ・コンビニでおにぎりを買うときのスタメンはどの具?
- ・おすすめの美術館・博物館、教えてください!
- ・【お題】大変な警告
- ・洋服何着持ってますか?
- ・みんなの【マイ・ベスト積読2024】を教えてください。
- ・「これいらなくない?」という慣習、教えてください
- ・今から楽しみな予定はありますか?
- ・AIツールの活用方法を教えて
- ・最強の防寒、あったか術を教えてください!
- ・歳とったな〜〜と思ったことは?
- ・モテ期を経験した方いらっしゃいますか?
- ・好きな人を振り向かせるためにしたこと
- ・スマホに会話を聞かれているな!?と思ったことありますか?
- ・それもChatGPT!?と驚いた使用方法を教えてください
- ・見学に行くとしたら【天国】と【地獄】どっち?
- ・これまでで一番「情けなかったとき」はいつですか?
- ・この人頭いいなと思ったエピソード
- ・あなたの「必」の書き順を教えてください
- ・14歳の自分に衝撃の事実を告げてください
- ・人生最悪の忘れ物
- ・あなたの習慣について教えてください!!
デイリーランキングこのカテゴリの人気デイリーQ&Aランキング
-
数式の計算結果により表示され...
-
Excel 関数? 文字列に...
-
ヘッダー
-
EXCELの関数に関する質問です
-
AかBに入力があった時Cに反映、...
-
エクセルで、時間の計算をした...
-
Excelの文字入力にタイムラグが...
-
エクセル・計算式をテキストデ...
-
TEXTCALCの使い方
-
IF関数で、時間を条件にしたい...
-
Excel(エクセル)でフィルター...
-
IF関数で出した数値をSUM関数で...
-
エクセルで足し算と掛算を混ぜ...
-
エクセルの使い方 1日=7時間45...
-
エクセルで同じ計算式を入れて...
-
計算結果が「0」のセルだけを「...
-
エクセルで 足し算や割り算の...
-
エクセルで数値を50単位で切...
-
excel 参照しているセルに色が...
-
エクセル 月数を0.5ヶ月単位で...
マンスリーランキングこのカテゴリの人気マンスリーQ&Aランキング
-
ヘッダー
-
数式の計算結果により表示され...
-
Excel 関数? 文字列に...
-
エクセルで、時間の計算をした...
-
AかBに入力があった時Cに反映、...
-
IF関数で、時間を条件にしたい...
-
EXCELの関数に関する質問です
-
エクセル・計算式をテキストデ...
-
Excelの文字入力にタイムラグが...
-
エクセルでの時間計算(2時間30...
-
エクセルの使い方 1日=7時間45...
-
エクセル 月数を0.5ヶ月単位で...
-
エクセル 1万時間を越える際の...
-
エクセルで 足し算や割り算の...
-
計算結果が「0」のセルだけを「...
-
TEXTCALCの使い方
-
IF関数で出した数値をSUM関数で...
-
エクセルで時間の差の出し方は?
-
エクセルで足し算と掛算を混ぜ...
-
【エクセル】あるセルのみ自動...
おすすめ情報