こんにちは。Excel開発15年、セルネッツの竹本です。
今回は「その求め方はNG、同じ結果なのに100倍遅い書き方」について解説をします。
たとえばですが、今見ていただいているExcelのシートの表があったとします。データ範囲で言うと1万行×100です。
すべてのセルに10と入っていた場合、答えはわかっていますが、1万×100×10になるので、合算した結果は1000万です。
この答えを求めるやり方について解説をします。
3つの方法をお話します。左から〇・△・✕となっています。
まず一番遅いやり方です。すべてのセルを演算で求めている、しかもセル操作によるものです。
実際にやってみます。結果のところに時間が入ります。押しました。2.516秒。これが早いか遅いか、このあとお話していきます。
真ん中の△、Bはすべてのセルを演算によって求めますが、セル操作ではなく、いったん配列に入れてメモリー上で結果を求めるやり方です。
ボタンを押します。0.235秒です。これだけでも10倍近く早くなっています。
一番早いやり方、ここに結果が入ります。〇、A、セル範囲をSUM。ワークシート関数を使った場合です。やってみます。0.016秒と出ました。
この3つの方法についてプログラムソースをお見せします。
0.01秒の10倍が0.1秒、100倍が1秒になるので、これを基準にした場合は100倍どころではなく、200倍ぐらい早くなっています。
この矢印の下ですが、データ範囲をSUMで算出しています。実際に中身を見ていきます。
まずセル範囲をSUMでやった場合です。3つのモジュールを分けてみました。今ここのボタンに入っています。これはここに入ります。順番に見ていきましょう。
Aのセル範囲の合計を算出するパターンは、やっていることはたったこれです。
22シートに対して、Bの2からCW列の1万1行目までを一瞬で求めているのはワークシート関数、SUMを使っています。時間を単純に求める開始と終了なので、それをログということで出力しただけです。これが圧倒的に早いです。
そして真ん中のパターン、Bです。
データ範囲をいったん配列に格納して、そのあとですべての行、すべての列、1万行×100列なので100万回です。100万回やった場合、かろうじてメモリー上でやっているのでこのぐらいのスピード感です。
一番遅かったのがこちらです。
やっていることはBと同じですが、メモリーに格納することなくセル操作をしています。ベタでセルそのものに値を入れる、読み書きをしているということです。これがやたら遅いんです。
3つの方法を見ていただきましたが、この〇になっているAのセル範囲をサマリーする、1行、1文でデータ範囲・値を取得することができるのでこの方法を使ってください。
まとめに入っていきたいのですが、21、22、23という入力シートがあります。
今回、22の入力シートに対してデータの集計をしました。1万1行目までありますが、ここが終点で、始点になっているところはBの2です。
ここからここまで足したらとやったのですが、ちっちゃく見えています、1万8000と入っています。
Excelの標準の機能ですが、囲ったところの合計が下のほうでリアルタイムに見えるようになってますのでここで確認することができます。
うしろのほうのデータは特になにも入っていない状態ですが、ここでまとめに入ります。
プログラムソース1行で処理内容がわかりやすくなっている書き方がとても重要です。事務系のVBA初心者でも全容が読み解ける書き方です。
VBAの活用方法にもいろいろあって、データベース系の別言語で開発をしている人は当てはまらないケースがありますが、Excelでの業務をExcelVBAで低料金で開発をしていくことになるので、作り方が重要になります。
一番危惧するのは作った人がいなくなったときのことです。別の人が手を出せないようなコーディングはリスクしかないので、重要なことは対象エリアの明示化です。
たとえばこの書き方であれば、O22のRange、Bの2からCW10001となっているので、22シートのセル範囲の合計を求めてるのだなと、なにをやっているかがわかります。
いろいろなシートがありますが、22番というシートの名前もわかるので、このシートのBの2から下のほうまで合算していることが読み解けます。この一文でやっていることが十分に把握できますね。
そして3つのポイントとして、セル範囲の集計ループ、集計は回さないということです。先ほどの例だと100万回回していましたが、その必要はありません。
2番目、任意セル範囲の集計は可視化が原則です。可視化されている状態がこの状態です。Bの2からどこからどこまでを足しているのかがわかるような書き方です。
最後、高速処理が基本になるので、あまりに遅い場合は作り方をまず疑ってください。この書き方で遅い場合は、おそらくデータ量が膨大だったりします。
妥当なスピードなのかという考え方において、データ件数が少ない場合はセル操作で良いのですが、セル操作はとても遅いので、データの件数に影響が出るようなケースでは、必ず先ほどのBのパターン、バッファーの中でメモリー上でやるか、Excelの関数を使ってコーディングステップ数を減らしてください。
ちなみに最後に配列を使った良くない例も解説しておきます。
この算出、A、B、Cに関しては今説明した通りですが、配列でやる例です。
プログラミングの経験が5年10年あるような方々の多くはこういう書き方をすることが多いと思いますが、セルネッツではこういう書き方はしないようにしてもらっています。
VBAの事務系の初心者がわかりづらいことと、メモリー上でやっているので、どのシートのどのセル範囲をというのがこのプログラムソースから読み解くことができないんです。
下のほうまで全部そうなんですが、これだとさっぱりわかりませんので、こういう書き方をもって、どのシートに対してなにをしているのかが一文でわかるような書き方を心がけるようにしてください。
解説は以上となります。
少しでも参考になったかたはぜひチャンネル登録・高評価をお願いします。毎週金曜日の夜9時に投稿しています。ご視聴ありがとうございました。
タケモ塾では、今後も皆さんのVBA学習に役立つコンテンツを作成してまいります。
ブログ記事、Youtubeチャンネルのご質問・ご感想・ご要望などお気軽にお問合せください。
お問合せはこちらから ✉
タケモ塾運営：株式会社セルネッツ
