2
0
テーマ、知りたいこと
関数や特定のセルの値を参照する式が入力されているセルがあるファイルを、
VBAにて同条件の仕様を満たすものを作成したとして高速化できるものなのかを
有識者の皆様に意見いただければと思っております。
背景、状況
社内で多くのユーザが使用しているエクセルファイルなのですが、関数や参照セルが多いためか
動作が重くなることがあるようで軽量化・高速化の要望が出ています。
ネットのサイト等で高速化の情報を探していた際に、関数や条件付き書式を止めて
VBAで再現したほうが高速になるという記述を見つけ、今回VBAでの改修を検討している状態です。
今使用しているエクセルファイルには関数等の情報が入力されているセルが1シートに約2000セル、
1ブックに約10シートほどで1つのファイルになっています。
入力されている関数等の情報は
[=$○$○]といったように特定の別セルに入力された情報を表示させるものが約1割
IFで条件分岐で結果を表示させているセルが約9割弱
SUMIFやFINDなどその他が若干数存在しているといった状況です。
全て参照先は同シート内で他シートを参照することはなく、各シート毎に独立した情報になっています。
ユーザとしては入力された情報がリアルタイムに反映されることが望ましいようなので
VBAに置換した場合はWorksheetのChangeイベントやWorkbookのSheetChangeイベント
などで対応することになると考えています。
自分は然程VBAに明るくは無いのですが、類似の内容の物を1から作成した実績はあるので
VBAで今の仕様と同内容を実現することは可能だと思っています。
しかしVBAにするだけで本当に高速化の効果があるのかがわかっておりません。
感覚的な話で申し訳ないのですが、VBAにしても内部的にすることは基本同一で
Changeイベントなどで常にセル情報を監視しているという状況なので
高速化しないのではないか、せっかく作っても無駄骨にならないかという不安があります。
回答8件
#1
総合スコア117615
投稿2023/11/08 02:01
これは意見交換でよいのですか？
自動計算させるならVBAより式の方が確実です
#2
総合スコア25426
投稿2023/11/08 02:13
編集2023/11/08 02:22VBAでの高速化の殆どが配列として扱う事によるものです。
ですので、スピル範囲演算子やスピル利用が可能な関数、配列数式等により、配列で扱うようにする事でも高速になると思います。
また、Power Queryも選択肢の一つだと思います。
#3
総合スコア358
投稿2023/11/08 03:07
データや計算式の内容で違ってきそうな気がしますし、理論的に高速化するかも
しれないけど、10秒→9.9秒とかだと体感で意味ないですよね。
「類似の内容の物を1から作成した実績はある」のであれば、今回題材にしている
Excelブックから計算式をいくつかピックアップして本番規模程度にコピペした物と、
VBAで記述した物をざっくり比較(速度とか使い勝手とか)してみれば良いのでは。
#4
総合スコア86281
投稿2023/11/08 04:49
編集2023/11/08 05:28確実に早くなるのはファイルを開くのに掛かる時間ですね。
10シートくらいだから手作業で出来ると思いますが、新規ブックに、値と書式をコピペして、見た目は同じだが式じゃなくて値が入っているシートを作ってみて比べてみてください。
ファイルサイズが大幅に減り、セルの内容量も違うので、開くのは速くなります。
（実際にはVBAプログラムサイズが追加されますが、誤差の範囲でしょう）
値と書式は一度にペーストできないのでそれぞれ分けてペーストになると思います。
これのうれしさは、
・朝開いたら夕方まで開きっぱなし
・1日に何度も「開いては更新して閉じる」を繰り返している
など使用方法で違いますが。
あと、メモリー使用量も減るはずなので、物理メモリーが少ない場合は各種操作の反応が良くなるかと思います。
リアルタイム反映必須なら、セル関数で他の方のお書きのような工夫をするのが良いと思います。
VBAだと「変更したセルの影響が及ぶセルだけ再計算する」ようなコードを書くのは結構大変かと思うので（★）、
安易には「シート全体を再計算する」ようなコードになってしまい、
その場合は、1セルの更新が反映し終わる時間はセル関数の方が速いでしょう。
ただ、１つの更新でシートのほぼ全体のセルに影響するようなケースだと、
VBAのほうが速いケースも多いかと思います。
1セル更新すると、その更新がシート全体にもわーっと伝わっていくのが目に見えるようなケースだと、
リアルタイム反映をあきらめてVBA更新（or自動更新止めて再計算ボタン利用）にすると、さらさら入力できるので、操作感は良い気もします。
（★ これは私が知らないだけで、あまり大変でない解決方法があるのかも知れませんが）
リアルタイム反映必須でないとしても、「何をもって速くなったと判断するか」が難しいですね。
シートの自動更新を止めておいて、セルをいくつか更新して、
再計算ボタンを押してセル関数が更新反映する時間と、VBAで全体再計算する時間なら比較可能ですが、
それはご希望の高速化比較ではないと思いますし。
別の観点だと、作業ミスの防止ですね。
関数の入っているセルを入力できないようにロックしているのかどうか。
書式が決まっていて行や列の追加が発生しない場合はロックすればいいのですが、
ロックすると行をコピー追加などが出来なくなるので、必要に応じて行を追加する業務の場合は、
ロックするのは厳しいですね（都度解除して行追加してまたロックするというのは実用的でない）。
ロックしてないと、数式を壊して値を入れてしまうミスが防げませんが、
セル関数を使わず、VBAだけならそういう心配は無いです。
#5
退会済みユーザー
総合スコア0
投稿2023/11/08 07:33
基本的には
#2 で指摘されてるとおり、
VBAでの高速化の殆どが配列として扱う事によるもの
なのでその恩恵を受けれるかどうかが主題というか、全てであり、
質問にある
高速化しないのではないか、せっかく作っても無駄骨にならないかという不安
は、環境やデータへの依存も強く、実測しない限り払拭できるものではありません。
つまり、
他人に聞くくらいなら測れ
が答えになります。
もしノウハウを残すべく他の人のために聞くのであれば、最低限環境を明確にし、
具体的なExcelファイルの1つも用意する
必要があります。
#6
総合スコア1059
投稿2023/11/08 09:13
社内で多くのユーザが使用しているエクセルファイルなのですが、関数や参照セルが多いためか
動作が重くなることがあるようで軽量化・高速化の要望が出ています。
動作が重くなっている原因は本当にそれだけかな、という印象。
- そのブックは一体いつ頃に作られた（いつ頃から使い続けられている）のか。
- そのブックのファイル保存形式は何なのか。
- そのブックのファイルサイズは現時点でどのぐらいあるのか。
- 普段どのぐらいの頻度でそのブックを編集しているのか。
感覚的な話で申し訳ないのですが、VBAにしても内部的にすることは基本同一で
Changeイベントなどで常にセル情報を監視しているという状況なので
高速化しないのではないか、せっかく作っても無駄骨にならないかという不安があります。
仮にVBAによって高速化を図れたとして、そのブックの保守業務はどなたがされるつもりなのでしょうか。
もしその案件に関わっているのが VBAamateur さんお一人（のまま）であるなら、将来的に「無駄骨」よりも厄介な事態になってもおかしくないのですが。
#7
総合スコア116
投稿2023/11/13 05:42
今、あなたが言ってることは｢目の前に餅があるのにそれが美味いかどうか｣と言っている様なものです。
我々にはそれがどういう材料で作られているかわからないので、一般的なお話しかできません。
なので、一度食べてみて(作成してみて)はどうです？
時間の無駄になるかも知れませんが、知識の無駄にはなりません。
私個人から言わせればVBAの方が早いです。
数式で遅いと感じるという事はある程度データがデカくなっているのでしょう。
Changeイベントなんかは人間が感じないほど早く処理しますし、
必要なところだけVBAで組めばいいと思います。
恐らくそれくらいなら、ここにコメントしている人は30分位で組めます。
そんな大掛かりなものではありませんので、チャレンジしてみましょう。
ただ、必ず作ったコードがちゃんと合っているか確認しましょう。
#8
総合スコア9
投稿2023/11/14 16:19
どのような最終の値を求めているかによります。
もちろん関数はそれを実現するためですが、
あまり意味のない参照やただ単に関数を使うための引数を関数でしかもそればかりシート内に作成したりしていたらそれ自体がボトルネックとなります。
まずは何の値を求めたいのか。そのためにどのような関数を使用すするのがベストかを私なら考えます。また、参照させる必要のない行まで関数行を延ばしているなど無駄な計算列や行を調べます
その上でVBAにたどり着くのではないかと。ありがちですが、安易にVBAにしてセルに同じ様に関数を打ち込む記述があったら悲しいです。
バッドをするには、ログインかつ
こちらの条件を満たす必要があります。