始めたきっかけ
VBAを始めたのは、障害者枠で再就職したのがきっかけでした。眼が大分悪くなっていたので、あまり複雑な仕事はやりたくなくて、excelを使った仕事ということで、excelなら前職でも日常的に。使っていたのでまあいいかと思い、勤め始めました。
excelを使った仕事というのは、会社で管理しているアパートの共用部で使われる公共料金の帳簿を付けることでした。
毎月の公共料金(電気、ガス、水道、電話)の支払い情報(支払日、使用量、支払金額)をexcelで作られた料金簿へ入力し、集計してゆきます。入力件数は千件程度で、必要に応じて期間、費目、案件種別ごとにデーターを集計、加工してレポートを作成します。案件数としては、大した件数ではありませんが、紙の領収書や明細書のデーターを読み取り入力しなければならないので、弱視の私には結構負担でした。これは何とかしなければ、続かないと思いました。
弱視の私には、目の負担が大きい紙書類を読み取る作業はできるだけ減らしたいです。パソコンの文字は明るさ、倍率、コントラストの設定で読みやすくできます。紙データを電子データで入手できるものはそれを活用すべきと思いました。
電子データがないものは、紙データを拾ってexcel表に手入力するしかありません。excelの表は項目数が多いと横に拡がってゆくので、拡大率の大きい状態では横スクロールしながらセルに入力してゆかなくてはならなくなります。縦スクロールはマウスのホイールでできますが、横スクロールはexcelの場合左ボタンを押さえながら横移動させる動作になりますが、縦スクロールほど自在にスクロールできずストレスが溜まります。
データを料金簿へ入力後のデータ加工やレポート作成は、別表に料金簿の中から該当データをカット安堵ペーストしてゆきますが、目が悪いと、セル選択を間違ったりしてスムーズにゆきません。
これらの私にとってやり辛い毎月の作業を、excelの機能やvbaを使って目の負担が少しでも軽くなるようにしたいと思いました。
電子データの活用とvlookup関数
毎月送られてくる事業者への支払い請求書や領収書は、インターネットから入手できることが多いです。例えば、私の会社の場合、電気契約は数百件あります。これらの支払いデータを一括してcsv形式でダウンロードできます。この支払いデータのリストを会社の料金簿に転載する際、1件1件手入力でコピーアンドペーストでやるのはばかげています。大抵はexcelの関数を使うのですが、よく使われるのがvlookup関数です。この関数を料金簿の所定の入力セルに記述して、csvデータをexcel上の定めた場所に貼り付け、契約番号で検索し紐づいた支払いデータをコピペさせます。これで数百件の支払いデータは自動で料金簿へ転載できます。ただcsvデータのの貼り付け位置がずれると、関数が契約番号を検索できなくなります。目が悪いと決まった位置に貼り付けられないことが度々あるので、この貼付け作業はvbaでマクロを作成してやらせました。
ピボットテーブル
月ごとに1000件以上の公共料金のデータを料金簿に蓄積してゆくので、年間1万件以上のデータが溜まります。このデータを事業の資料として活用するには加工する必要があります。例えば地域、期間、用途(電気、水道、ガス、電話)別に集計したり、監査資料にしたり、ピンポイントの条件でデータを抽出したりします。この作業はexcelのピボットテーブルやデータベースソフトが大変有効です。
料金簿の表の行頭の項目行にはエリア情報(地域名、アパート名、部屋名)、契約情報(種別、契約番号)、支払日、支払金額、使用量などがあり、縦方向に下へその項目のデータが記載されて行きます。
ピボットテーブルでは、ピボットテーブルかする表の行頭にある表項目を、「行ラベル」、「列ラベル」、「値」に設定します。料金簿の場合、「行ラベル」にはエリア情報と契約情報、「列ラベル」には支払日、「値」には支払金額と使用量を設定すると、「行ラベル」と「列ラベル」の2次元の表に変換し、行列の交錯位置のセルに「値」の集計値を入れてくれます。を「列ラベル」が日付データだと月、期、年とまとめて集計することができるのでたちまち月別の支払い資料に変換されます。また「行ラベル」は並べた順番で階層化し展開、集がさせて表示させることができるので、エリア情報別、契約情報別の資料にも簡単に編集できるのです。
ピボットテーブルの作成は、挿入メニューのピボットテーブルではじめてゆきます。「行ラベル」「列ラベル」「値」の設定はフィールドリストを表示させて行うのですが、視覚障害があるとこれはちょっと使い難いかもしれません。マウス操作が多く、リストのレイアウトが見えにくいところがあります。コツはフィールドリストのサイズを拡大し、作業ごとにリストレイアウトの設定を切り替えながら作業をしてゆく方法が良いと思います。一度作ってしまうとその後の編集やデータの更新が格段に楽になるので苦労の甲斐があります。
データベースソフト
会社のofficeではmicrosoft accessが付いていて興味もあって、使ってみました。データベースソフトを使うとデータの管理がとてもやりやすくなります。excelでデータを管理してると使いこんで行くにつれ、管理するデータ項目を追加修正したくなります。例えばエリア情報をもっと細かくしたり契約情報の項目を増やしたり修正したくなるのですが、これをexcel表でやろうとすると大変な作業になります。支払いデータの一つ一つに追加修正した項目を適用しなければなりません。データベースソフトではエリア情報、契約情報の表(テーブル)を別々に作っておいてそこで追加修正すれば、支払い情報の表(テーブル)と連結することが簡単にできるのです。そこで私は、月毎の支払い 情報をexcel表に入力し、accessの支払い情報テーブルに貼り付け、エリア情報契約情報のテーブルをaccess上で連結するようにしました。連結したテーブルをexcel表に貼り付けることでexcelのピボットテーブルの資料とします。accessでもvbaが使用できexcel vbaとの連携ができるのでaccessとexcel間のデータのやり取りをマクロ化しました。
私のVBAの学習法
VBAの学習サイトで学んだくらいですぐにVBAコードは書けないです。そこで私は、excelVBAのマクロの自動記録機能を利用することを考えました。もちろんこの機能は万能ではなく極簡単な作業しか記録できないことは知っていたので、実務でのexcel作業を細分化して記録することから始めました。そして記録した作業のコードを見てどのようになっているかをwebで検索して調べながら不要なコードを取り除いたり、改造したりしました。こうしたマクロコードが増えてくると、それを使ってもう少し複雑な作業のマクロコードが書けるようになり自然と増殖進化してゆきました。excelVBAの利用者は大勢いて、ネット上には自分のやらせたい作業のコードはほとんどみつけることができます。結局、上記に書いたexcel上VBAのマクロは、ネット上の大勢の方に助けられながら、3か月程度で形にできたと思います。その後さらにexcelVBAでできることが分かってくるとあれもこれもやらせたいと加速度的にマクロコードが増え、ブラウザを操作したり、excelと親和性のあるデータベースソフトACCESSと連携させたりするところまでたどり着きました。この間2年半ほどでしたが、文法や構文については、体系的理解できておらず、あくまでかろうじて実務で使えるマクロを作れるということです。プロとしてやって行こういうのでなければ、これで十分です。
