こんにちは。Excel開発15年、セルネッツの竹本です。
今回は「VBAプロテスト不合格、設計がダメだとこうなる」について解説をします。
今回学ぶことですが、「ソフトウェアの品質はプログラミング技術ではなく設計で大きな差が生じる」ということをお話していきます。
ここで言う「設計」とは、仕組みをどう構築するか、そのためにどこに着眼するかということ。「仕組み力」というお話になります。
システム仕様でいうと、タケモ塾というセルネッツのサイトで、「ダウンロード」のタブから「実力評価サンプル」を選択していただくと、課題がいくつか掲載されています。
この課題の中で、連想配列処理について、いろんなかたにチャレンジをしていただきました。開発の事例ということで実際に収めているものもありますので、良い結果と悪い結果、両方を解説しながら進めていきます。
まずこのシステムの仕様ですが、サンプルデータ、10万件の会員データがあります。都道府県・性別を読み解いて、都道府県・性別ごとの人数を求めるという課題になります。
たとえば北海道は男性が3010人、女性が390人。一方で青森県・岩手県は男女がいますが、秋田県は女性がいないので1のみです。
この10万件のうち、事前に一部を除いているので35種類ぐらいになりますが、いったん全部の都道府県を出してから該当のものをカウントアップしていった結果、遅くなってしまったものが遅い例です。
これが期待値になるわけですが、68行目まで都道府県ごと・性別ごとの人数がこちらに入っていればOKという例です。
まずは早い例から紹介します。
会員データが10万件あります。これが期待値・結果になります。この結果をいったんクリアして、Bの結果シートに集計結果を出力します。
実際にやってみます。
実行ボタンを押すと確認画面が出てきます。「集計を行います」「よろしいですか」「はい」を押すと一瞬です。ほぼ0秒です。
結果シートに正しく都道府県・性別ごとの人数が求まりました。これが早い例でした。
続いて遅い例です。
同じ結果を出すのに181秒かかっています。実行ボタンを押して、確認画面で「はい」を押すと、ご覧の通りです。
ここだけを見ていると10万件に対して1件ずつ、順調にかなりのスピードで集計しているという印象を持たれるかたもいると思います。23、24秒で1万件になります。
10万件まで相当なスピードでやっていると解釈されるお客様が多いと思いますが、これは相当に遅い例です。
遅い例、早い例、なにをやっているのかというと、集計をやっていることに変わりはありません。
プログラムの流れとしては、先頭データから1件ずつデータを読み込んで、都道府県と性別を判定しながら該当の都道府県のところをカウントアップしていくというやり方でした。
つまり10万件あれば10万回判定をしていることになります。100万件あれば100万件判定をするという仕様設計となっています。
早いほう、最速のほうは一瞬でしたが、どういうアルゴリズム・仕様だったのかというと、まず連想配列でグループの出現ごとにその都道府県の男性・女性が何人いるのかをカウントS関数でやるだけなので、実際は47都道府県ではなく30都道府県に対してその処理結果を出しているだけなので、35回しかしていないということになります。
ここでフローの解説です。
左側が遅いほうです。データを読んで都道府県を判定して、該当データの件数をぐるぐるカウントアップしていきます。非常に遅いです。「全件なめ」という呼び方をすることがあります。
右側は連想配列によってグループを事前に求めてしまい、そのぶんだけ必要な値を求め、そこに出力するやり方です。圧倒的に差が出ます。
まとめです。3つあります。
システムの品質はプログラミングの技術ではなく設計でほぼ決まります。
今の遅い例早い例に関しては、「こういう作り方でやってください」とプログラマーに設計の内容を指示することで、2人とも同じスピード感を出すことができたはずなんです。
つまりプログラミングは設計が重要ということです。
2番目、設計では着眼点が大事になってきます。いかに短いステップで、いかにシンプルに設計できるか、これが技術力になります。
高度なプログラミング技術を使ったとしても、設計がシンプルでないと拡張性、メンテナンス性、慣習性が下がったりしてしまいます。
ワークシート関数はExcelの標準の機能なので、これを併用していかに短いステップで設計できるかが技術力であるとセルネッツでは考えています。
最後です。
スピードに対する高い意識を持っていれば、180秒かかっている時点で、「これじゃまずいな」と気づきがあったはずです。
製品として納入する以上、速度に対しても高い意識を持って取り組んでください。
以上で今回の解説は終了とさせていただきます。毎週金曜日の夜9時に投稿しています。ありがとうございました。
タケモ塾では、今後も皆さんのVBA学習に役立つコンテンツを作成してまいります。
ブログ記事、Youtubeチャンネルのご質問・ご感想・ご要望などお気軽にお問合せください。
お問合せはこちらから ✉
タケモ塾運営：株式会社セルネッツ
