こんにちは、Excel開発15年、セルネッツの竹本です。今回はVBAでできること、大量シート精査チェック事例について解説をします。
目次ですが、今回はこの6項目、上から順番にお話をします。
使用するデータですが、e-Statというサイトがありまして、政府の白書のデータをダウンロードできるポータルサイトです。
このe-Statに入って、トップページの「地域」からダウンロードしたデータを使います。
ご参考までに触れておきますと、560項目、たくさんの項目が選べますが、今回は4つの項目を選択して、Excelでダウンロードしたデータについて、その中のシートの構成を分析していく流れをお話します。
利用するデータはこのデータです。シートの数が46枚あります。
46枚のシートが多いか少ないかということに関して、入札関係の案件では少ないほうかなという気がしています。2、300枚だったりすることもあります。
次、ブックの点検・精査の目的です。
そもそもなぜ精査・点検・チェックをきちんとやらないといけないのか、今回の例では一般競争入札などで見積もりを出すという体で、十分なチェックをする必要があるケースを紹介します。
基本的に1番に書かれている通り、一般競争入札などではやり直しができないので、最低価格で落札したと同時に取引はスタート、手続きが始まっていくことになります。
のちにデータをすべて精査したときに「ちょっとこれだと」というのはなしですので、見積もりの誤りは致命的だったりします。
特に注意が必要なポイントがあります。
シートが非表示になっていないか、行が非表示になっていないか、列が非表示になっていないか、残骸がどこかに潜んでいないか、表記の揺れがないかというあたりになります。
基本的な項目をチェックしていけば良いのですが、見積もりの作成では値の精度、フォーマットチェックが重要な作業になります。
たとえば46枚のシートの中で、すべてのシートが統一フォーマットという前提で調べていくと、そうでないものが入っていることも珍しくありません。
担当者の理解では統一フォーマットですが、実際はそうでないことが業務の中では往々にして起こりえます。
そういうケースにも対応できるように情報を正しく整理・理解する必要があります。
4番目、見積もり作成で留意すべきこと。
大量シートの場合はこういう項目になります。シート番号、シート名称、最終データ行数、最終データ列数。
3番と4番で有効データ範囲が決まってきます。
5、6番についてはどういう要素が存在していて、基本情報はどこに入っているかが大まかに見える必要があります。
一般競争入札などは原則入札・落札したらそれで決まり、値上げなどはできないので、しっかりやっていく必要があります。
手作業では非効率なのでシステムの力を借りることになります。
生産性の問題もありますが、実は正確性です。システムでやったほうの手作業より効率が良いこともありますが、正確でないとのちに失敗になる可能性があるので気をつけてください。
早速デモをやっていきます。
ダウンロードしたときのデータはこういう状態です。
シートの枚数もわからないので調べていきます。
今ここをカチカチやりましたが、Ctrlキーを押しながら押すとここにジャンプします。
今46枚目のシートが表示されている状態で、Ctrlキーを押しながら押すと先頭に来ます。
もう一度Ctrlキーを押しながらここをクリックするとうしろのほうに行きます。
うしろのシートから順番に見ていくと、まず項目の名前がすべて表示がされていなかったりするので、統一していきたいですよね。
ここからは手作業でも良いと思いますが、やってみます。
先頭シートに行って、ここからCtrlキーを押しながら最後のシートに行って、すべてのシートを選択します。
すべてのシートが選択された状態です。ここで見やすくするためにメモリ線を消したり、センタリングだと上下の位置が隠れたりするので、上詰めにして5、6行目を折り返しの表示にしたりします。一度列の幅を狭くしてから自動調整するとか。
こういうところまでやっておくと全体俯瞰ができるので便利です。ここまでが手作業です。
先頭シートに行きます。
先ほどの状態とちょっと違っていると思います。もう見出しが見えています。
1枚目のシート、G列が最終。2枚目もG列が最終。3枚目も4枚目も5枚目も同じです。
6枚目、「なんじゃこりゃ」となりますが、シートの中の列数、これは項目ですが、項目の数が違っているものがあります。
7枚目はG列なのにということで、手作業でやっていたら大変なので、マクロ、VBAを使った例をご紹介します。
46枚のシートがある中で、一番うしろのシートに結果というシートをくっつけます。
実行前にこのボタンを押すとなにをやるのかということだけサクッと紹介します。
この精査・点検ボタンを押すとここからここまでシートの総数を求めて、先頭シートから順番にシートの番号、シートの名称、特定セル番地に入っている値2つ、最終データ行、最終列数を求めて、どんどんここに書いていくというプログラムになっています。
ボタンを押してみましょう。精査・点検。
ここでは罫線を省略しているので見やすいように罫線を入れます。
ここにオートフィルターをかけて、シートナンバー、単純な連番なので46枚ということがわかります。
シート名もこのデータではそのままシート番号がシート名称になっていることがわかります。公開日とは46枚目のシートでいうとBの2に「公開日」が入っていたので、ここの値は調べたほうが良いと思って取ってきています。
うしろの3枚を見ると3月4日になっていますが、すべてがそうなのかどうかを知りたかったので、そのセル番地を指定した次第です。
ここで見ると1種類しかありません。ここでなにがわかったのかというと、全部同じ値だったということです。
調査年はCの3から取ってきました。1975年、76年。
これを見ていくと毎年やっていることがわかりますよね。
そしてデータの行数と列数です。行数はすべて58行。列数は2種類登場しました。
一番下に行って、一番左に行って、そのまま一番上にカーソルを持っていくと、今カーソルが停止した行が最終行と見直しをしていますが、58行目、ここから3行は説明です。
実質すべて58行目でカーソルが止まっていますが、4をマイナスした54行目がデータとしての有効データ範囲になります。
今は見た目でわかっていますが、これが実際の統計データの値です。ここから下は説明です。
次に列数、2種類ありましたが、7列のものと13列のもの、2パターンしか存在しなかったということでホッとします。
13列はどうなっているのかを調べたいですよね。
13列のシート6を調べたいのですが、毎回マウス操作は大変なので、これもマクロを入れています。
カチカチとやるとそのシート名にジャンプして、シート名が赤くなります。
今見ているのが「13列あったよ」と言われているシートです。
戻ると、今調べたところがオレンジ色になります。
どこを調べ終わったのかがわかるようにしたいので順番に行きます。
ちょっと飛ばして41番目のシートをクリックすると、これも13列ありました。M列まであることがわかります。
ちなみに参考までにダブルクリックしたときのやっていることですが、今ダブルクリックした行に色を塗って、そのシートの名前を取得して、そのシートを選択したらついでにそのシートの色を赤くする、この255は赤です。
もともとのダウンロードしたときの状態を1枚ずつ調べて一覧表を作るのはとても大変な作業であると同時に、間違いが介在するリスクがあります。
VBAを活用することで時間をかけることなくファイル内の正しいシートの数、有効データの数、取りたい情報を規則的に取り出すことができるようになります。
今回このボタンではこれだけしかやっていないのと、ダブルクリックしたときにやっているのはこれだけです。
このプログラムソースはどのExcelファイルにもそのまま実装して必要なところだけを直すことができます。VBAの使い方という意味ではとてもわかりやすい例ではないかと思っています。いかがだったでしょうか。
最後まとめです。3つ書きました。
Excelブック精査・点検の基本は俯瞰することです。
1番目、全体を見渡すことがとても重要で、木を見て森を見ずは事故の元です。
2番目、規則性があるならサクッとVBAが効率的です。
規則性があるならということですが、どこに規則性を見いだせるかにもよってくるので、ここは経験だったりするところもあります。
だいたい同じことやっていそうだなということがわかってきた場合はVBAでやってしまったほうが圧倒的に効率的です。
最後、先ほどダブルクリックしたらそのシートにジャンプしましたが、これはよく使います。
シートのチェンジイベントと言いますが、ダブルクリックしたらダブルクリックした行の何列目の値をもとになにかのアクションをするというものです。よく使います。
こういう使い方をすることで大量シートであったとしても精査・チェック・点検がちゃんとできるようになっています。
今回は46枚のシートを例にお話しましたが、何十枚でも何百枚でも基本的にはプログラムの処理は同じです。こういう形でブック内の大量シートの精査・点検をするととても有効ではないでしょうか。
今回の解説は以上になります。
少しでも参考になったかたはぜひチャンネル登録・高評価をお願いします。
ありがとうございました。
タケモ塾では、今後も皆さんのVBA学習に役立つコンテンツを作成してまいります。
ブログ記事、Youtubeチャンネルのご質問・ご感想・ご要望などお気軽にお問合せください。
お問合せはこちらから ✉
タケモ塾運営：株式会社セルネッツ
