こんにちは。
Excel開発15年、セルネッツの竹本です。
今回は「絶対ダメ。プロならこうするセルの色塗り」について解説をします。
たとえば例ですが、Excelシートにこんな受注データがあったとします。
いつ誰がなにをいくらで何個注文したか。
お客様からの要求仕様ということで2つあります。
1つ目は金額をD求めてほしい。
これは単純に単価×数量なので問題ありません。
2つ目、数量に応じて色を塗ってほしい、わかるようにしてほしいというリクエスト・要求があった場合。この例で解説をしていきます。
受注データはこれを使います。
金額を算出するプログラムの書き方、A・B・Cと3種類ご紹介します。
ピンクのコメント欄にユーザー要求仕様ということで、1、2番の下、留意すべき設計を解説します。
3つありますが、1つ目。
まず大量のデータのケースを考慮できているかが超重要です。
今はサンプルなので15件程度ですが、これが5000件だったり7000件であったりすることも想定しなければいけないので、プログラムを書いていく場合はそのあたりも考慮して設計をしていく必要があります。
2つ目、初心者あるあるですが、マクロ記録そのままはダメです。
なぜかというとソース量が増える、そして可読性が下がる、速度も下がるということで、マクロ記録そのままはダメです。
3つ目、テーマにもなってきますが、ハードコーディングは極力減らしてください。
理由は拡張性が下がってしまう。そして改修性、メンテナンス性が下がってしまうからです。
この3つに留意をしながらプログラムを作っていきます。
Aのパターンを先にやってみたいと思います。
初期化ボタンで条件付き書式と塗りの色を全部外したプレーンな状態となっています。
この状態からAのボタンを押します。16行目までです。
色を塗っているところが4か所ありますが、20以上なら赤、10以上ならピンクになります。結果として正常動作ができています。期待値が得られたということです。
サクッと解説をします。
最終行を求めてOKであれば下に行きます。
まず色を塗っているのでいったん初期化をしたあとで、その次にif文、表が2つありますが、これが大ループになります。
20以上だったら赤、10以上だったらピンクということで、色を塗っています。
ひとつ前のステップで単価×数量を求めて、それをそのまま入れてしまっているのがこのパターンです。
ここを見ていただくとわかりますが、金額欄、数式ではなく固定の値が入っています。
Aのパターンを見ていただきました。
ステップ数、コメント記述を除いた行数だと思ってください。29行書いています。
塗り色は今のようにカラーの固定値、マクロ既読したものを書いています。金額は値になっています。
次にBのパターン。いったん初期化をしてBを実行します。
見た目はなにも変わらないです。正常値を得られた同じ状態です。
違いはここです。G列には数式が入っています。
数式が入っていることによって、数量が変われば自動的に変わるという動き方になります。
Excelは表計算ソフトなので、ここが値になってしまうとExcelのメリットが半減してしまいますので注意が必要です。
これがBのやり方です。確認をしていきます。
違いはここです。
2行目から最終行まで計算式を作って、フォーミュラーです。
ここで計算式を書いています。その下で98書式、セルネッツでは98シートと呼んでいますが、ここが書式を適用しているところです。
やっていることは、先ほどのようにプログラムの中で固定で色を塗っていくわけではなく、98書式というシートのこのエリアをコピーして、この範囲に書式をそのまま反映適用しているだけです。
なので、ステップ数は先ほどより減りました。
塗り色はマクロで記録した固定値を書いているわけではなく、条件付き書式でやっています。
金額欄は先ほどは値そのままでしたが、今回は数式が入っています。
式に関しては数式で金額を求める。それがBです。
最後、Cがセルネッツが推奨しているやり方です。
もう一度初期化をして金額を算出しています。
結果は同じですが、ステップ数が全然違います。
実際に見ていただくと、98の書式を適用するというだけです。
金額欄に数式をセットしたら書式を適用して、クリップボードを開放したら終わりです。
Cがマルになっていますが、金額欄のところは数式です。
シンプル記述ということで98の書式マスターを活用した例です。
3つの方法について解説をしていきたいと思います。
なぜ条件付き書式が重要なのかという5つの理由ですが、
1番目、仕様変更はつきものということになります。
これはシステム開発の常識と言われていることになると思いますが、仕様の変更追加は日常的に起こりうるものと考えるので、どういう仕様追加の依頼が来るかわからないので、拡張性がとても大事になってきます。
拡張性が下がってしまうことを回避するために条件付き書式は重要になります。
2番目はマクロ記録をするとカラー番号が入ってきますが、これではわからないですよね。
ソース記述がたくさんあると複雑化して見にくいです。
それによって不具合が起きやすい。起きたときに直しにくい。良いことはほぼないです。
なによりも5番目、ExcelVBAの強みが消されてしまいます。
ExcelVBAはExcelデータを操作するにあたって最強の開発言語であると考えています。ExcelVBAの強みを消さないようにしてください。
5つの理由の下の矢印ですが、求めていることは可読性と改修コスト、処理速度です。
これらに影響が出てしまうので、5つについて理解をしながらコーディングする必要があります。
セルネッツ流は可読性をなにより重視です。可読性とは読みやすさのことです。
たとえばQAチェックでプログラムソースの記述をすべて検索したり、ツールを使ってチェックをしますが、.colorというコーディング記述があった場合、QAチェックでは基本的に不合格にしています。
その記述でなければならない理由がある場合は仕方ないですが、書式でできる場合は書式でやるべきという考え方をしていますので、QAチェックでは不合格になります。
それでは順番にA・B・Cをサクッと解説をします。
ソースAはステップ数が29ありました。
まずはマクロ記録によるカラーリセットです。
なぜカラーリセットが必要になるのかというと、固定で色を塗っているので必ずやらないといけないのです。
そして、計算した数式ではなく、計算した結果を放り込んでいますので、Excelのメリットが半減、もしくは激減かもしれません。
20以上なら何色、10以上なら何色と、色を固定で塗っていますが、なにが良くないかというと、マクロ既読したそのままなんです。
パッと見たときに29ステップも書いてしまっていますが、第三者が分析するのは結構大変です。
Bの場合、少し減ります。
11行に減っています。だいぶ減りました。
2行目から最終行まで数式を入れていますので、Excelのメリットが半減することはありません。
演算値をセットしているわけではありませんが、ハードコーディングしています。
プログラムでそのまま書いてしまっているので、計算式が変わったり、書式関係になにかあった場合はプログラム修正が発生します。
プログラム修正をするということは必ず修正による影響があるかないか、影響調査が必要になりますので、コストは上がる一方です。
結局ハードコーディングはよろしくありませんということになります。
下が98書式、先ほどお見せした98シートの書式をそのままコピーして貼り付けてクリップボードを開放するやり方です。
ちゃんとやっていますが、マクロ記録をそのままにしていることが良くないです。なので11ステップになっています。
最後、Cのパターンですが、わずか4行です。
4行と言っているのは数式です。
数式が入ったセルを98のGの8を2行目から最終行までそのままペーストしています。
その次に書式です。書式の行をコピーして、貼り付けて、クリップボードを開放しまう。
わずか4行、4ステップです。これだけの違いがあります。
習慣化が一番怖いのですが、この書き方をしている人は常にこの書き方をします。
ということで、今回はたった15行程度のデータの数量を判定して色を塗るだけだったのですが、ツール1本について書かれるソースの量が膨大になってきます。
Aは✕。Bは△、Cが〇になります。
最後、98の書式について、別で詳しく解説が必要と思われるので、今回は構成だけお話をします。
5行目と6行目はなんのためにあるのかというと、列ナンバーを可視化したり、ユーザーフォームの列幅を自由に設定できるようになっています。
この書式マスターを使うメリットは3つあります。
記述を減らす、可視化を図る、改修性を高めるという3つの大きな役割があります。
詳しくは別で紹介をします。
最後、まとめになります。
まずハードコーディングは少なめにしてください。
なんでもかんでもプログラム記述は絶対にダメです。
実務の中で自分が使うぶんにはお任せというか、わかってやっているので大丈夫だと思いますが、プロの開発の現場では誰が利用するのかわからないということと、作った人ではない人に修正依頼が行くこともあります。
なので作った人でない人がわかるようなコーディングが絶対に必要になってきます。
そのための方法論として条件付き書式を有効に使ってください。
2番目、マクロ記録そのままはダメです。
正常動作を得られたとしても、セルネッツでは「無責任コーディング」という言い方をしています。
とりあえず動くようになったという感じで次のステップに行かれてしまうと、とてもリスクが高くなります。
マクロ記録そのままは基本的にNGです。
3番目、とても重要ですが、見える化です。
可読性、わかりやすさ、読みやすさ。
属人化の防止という観点で、外から見える可視化がとても重要なのはシステム開発もまったく同じです。
ということで、この98書式を使うことでどういう書式が適用されていたのか、ホームタブから見ると条件付き書式が入っています。
これはお客様のほうで書体やフォントなどが自由に変えられるということです。
処理結果に影響なく変えることが可能です。
最後、提案力を磨くこと。
はじめのユーザーの要求仕様では、お客様は数量が変わったら色を塗ってわかるようにしてほしいということです。
意図するところ、狙いを考えます。
20以上が赤、10以上がピンクとしていますが、担当者が変われば赤がきつすぎるとか、30以上の場合も知りたいとか、いろいろ出てきます。
そのときに都度プログラム修正となると、お客様のためになりません。
なんのためにその要求が出てきたのか、意図するところ、狙いを考えるところが設計の上流ではすごく重要になってきます。
ということは言われた通りなら良いのかというと、決してそうではありません。
なぜそうする理由があるのか、目的はなんなのかをヒアリングする必要があります。
お客様は他の方法をたぶん知らないので、「赤く塗ってください」「ピンクにしてください」と言いますが、自由に変えられる方法があるのであれば、それも説明をして複数提示します。
長所短所、説明できるスキルを磨いていくことが重要です。
解説は以上となります。
少しでも参考になったらぜひチャンネル登録、高評価をお願いします。
毎週金曜日夜9時に投稿しています。ご視聴ありがとうございました。
タケモ塾では、今後も皆さんのVBA学習に役立つコンテンツを作成してまいります。
ブログ記事、Youtubeチャンネルのご質問・ご感想・ご要望などお気軽にお問合せください。
お問合せはこちらから ✉
タケモ塾運営：株式会社セルネッツ
