ExcelVBAプログラミング言語この物語は、入社して一年目の新人社員、田中君が業務を自動化していく様を記したものです。物語はフィクション(時々ノンフィクション)ですが、自動化の手法は実際にパソコンやスマートフォンで実践できるものですので、物語と自動化作業の両方を楽しめます。僕自身、ようやくテレワークにも慣れてきたところなのだが、新人の僕がPCに明るいだろうと社内の先輩方のサポートに東奔西走する日々が続いていた。社内のみんなの役にたっている気はするのだが、体よく使われているだけの気もする。部署のオンライン飲み会で、信頼する美人上司の太田先輩に相談すると「何言っているの!新人なんだから一つでも役立てることがあるなら、それだけでスゴイことだと思うわ。プログラミングを覚えればもっと役立てるわ。いうことではないのだけど、「引き続きプログラミングも覚えるように頑張ります」と答えた。僕が楽しかった先日のオンライン飲み会に思いを馳せていると、笹川さん(4話参照)からLINE通話がかかってきた。僕たちの部署では笹川さんはいつも嵐のように緊急案件を持ってくることで有名だ。「田中君、お願いがあるの!」やっぱりだ。「何でしょうか?」僕はあきらめ顔で続きを促すと笹川さんは早口で語り出した。「先日のキャンペーンにたくさんの人が応募してくれて、かなり名簿が集まったのよ。「それはスゴイですね、良いキャンペーンでしたものね。「それが、オマケの景品目当ての人ばかりで、名簿のかなりの住所がでたらめみたいなの。名簿を部長に提出したら、住所が東京なのに郵便番号が横浜であることを指摘されて。「あらら、それは困りますね。「それで急で悪いのだけど、明日資料を発送することになっているので、今夜中に数百件ある名簿を調べて無効なものを削除したいの。「田中君ならできるわよね。信じているわ!」こんなとき、太田先輩ならなんて答えるだろうか。プログラミングで何とかしましょう!って、太田先輩なら言いそうなんですけど、どうしましょうか。」と、僕が途中まで言いかけたところで、笹川さんが言った。「さすが田中君、それではお願いします。メールでExcelの名簿送るのでよろしくお願いします。「あっ、まだやるなんて言ってないのに。電話が切れて数秒も経たないうちに笹川さんからメールが届いた。拒否権なんてなかったようだ。それで僕はすぐに太田先輩に泣きついた。太田先輩はニコニコして、「田中君もプログラミングでできることが分かってきて偉いわ。」と言って、どんなプログラムを作れば良いのか教えてくれた。太田先輩、頼りになります。実際にプログラムを作るのは僕がやらなくてはならないみたい。今夜、何時になったら寝られるんでしょうか。(続く)【プログラム】さて、今回のプログラムですが、郵便番号と住所の不一致を検出して、デタラメな住所を探すというものです。郵便番号と住所が正しく一致していたとしても、それが正しい名簿であるとは言い切れません。それでも、キャンペーンに応募するために適当に書いたものであれば、弾くことができるかもしれません。どのようなプログラムを作れば目的が達成できるでしょうか。郵便番号から住所を調べるプログラムが必要です。郵便番号に対応する住所のデータは郵便局のWebサイトで配布されています。そのため、この郵便番号データを利用する事で住所の不一致を検出できます。名簿データとライブラリをダウンロードところで、郵便番号データは郵便局のWebサイトから無料でダウンロードできるようになっています。こちらからダウンロードできます。言え、ダウンロードできるCSVファイルは非常に巨大でExcelでは扱いづらいものとなっています。本連載の姉妹連載である「ゼロからはじめるPython」の68回目で郵便番号データをSQLiteデータベースに保存する方法を紹介しました。データベースに保存したデータであれば、とても手軽に扱えるというメリットがあります。今回、この郵便番号データベースをVBAから手軽に利用してみましょう。こちらのページから、笹川さんから送られた名簿データ(※本連載用に作成した架空の名簿)と今回田中君が作成したプログラムをダウンロードできるようにしました。サンプルを解凍すると、以下のような笹川さんが送ってきた名簿データ「meibo.xlsx」があります。笹川さんが送ってきた名簿データそして、今回のプログラムでは、VBAからSQLiteのデータベースを操作する必要があります。そこで、SQLiteForExcelというライブラリを利用します。上記のページのサンプルには、SQLiteForExcelをlibフォルダに同梱しています。名簿データを開いてみようまた、上記のサンプルには、SQLiteを使うための「SQLite3.bas」と、郵便番号データベースを利用する「ZipNo.bas」というモジュールファイルが入っています。笹川さんが送ってきた名簿Excelファイル「meibo.xlsx」にこれらのライブラリを取り込みましょう。Excelファイルを開いたら、[Alt]+[F11]キーを押してVBAエディタを起動します。ファイルのインポート」をクリックします。このとき、「SQLite3.bas」と「ZipNo.bas」の二つのファイルをプロジェクトに追加します。一度に一つずつしか追加できないので、二度追加します。実行に際してSQLite3を利用するために、libフォルダをExcelファイルと同じフォルダに配置した状態で実行してください。画面左上のプロジェクトエクスプローラーから「Sheet1」を選んでダブルクリックすると、Sheet1に関するVBAを記述する画面が表示されます。最初に関数Zip2Addrが使えるかテストしてみましょう。Subのすぐ上においた状態で[F5]キーを押してみましょう。Subすると郵便番号データから住所を調べてイミディエイトウィンドウに、該当する住所を表示します。ウィンドウ」をクリックすると表示されます。郵便番号から住所を得たところもしもSQLiteデータベースやSQLに詳しい方であれば、上記のサンプルプログラムにある標準モジュールの「ZipNo.bas」を開いて内容を確認してみてください。SQLiteのデータベースを開いて、該当する郵便番号をSELECT句で検索して返しているだけの簡単なモジュールです。適当な住所を赤色にマーキングしようさて、郵便番号から住所が得られるようになったので、名簿の郵便番号と住所が合致しないものがあれば赤色にマーキングするようにしましょう。先ほどと同じくSheet1のコードに以下のようなプログラムを記述します。Sub先にプログラムを確認してみましょう。(*1)の部分では郵便番号と住所が記述されている範囲を取得します。(*2)の部分ではFor文を使って一行ずつ郵便番号と住所を確認していきます。(*3)の部分で実際に入力されている郵便番号と住所を得ます。(*4)の部分では入力されている郵便番号から正しい住所を取得します。その後、入力された住所とデータベースから得た住所を照合してあっているか確認します。(*5)の部分では間違っていた場合にセルの背景を赤色に塗ります。そのすぐ右隣に本来の住所を書き込みます。Subの上にカーソルをおいた状態で[F5]キーを押してプログラムを実行してみましょう。実行したところすると、適当に入力されただろう住所が赤色にマーキングされます。そのすぐ右隣に本来あるべき住所が表示されます。サンプルの名簿は少し前に機械的にランダムに作られたものです。そのため、最新の郵便番号データベースでチェックすると、市町村合併などのために住所が微妙に変わってしまっている住所も赤色にマーキングされてしまっています。結局のところ最終的には人間の目でチェックしないといけませんが、チェックの手間はずいぶん減らせるはずです。まとめ以上、今回は大量の顧客名簿の整理にも使えそうな郵便番号から住所のチェックプログラムを紹介しました。VBAから郵便番号を扱う方法はほかにもいろいろありますが、ライブラリを取り込むだけで使えるので機会があれば使ってみてください。くじらはんどにて、プログラミングの楽しさを伝える活動をしている。テキスト音楽「サクラ」など。共通点は○○だったAI時代に「必要とされる人材」とは??」4月1日より...71%の人が仕事を辞めたいと思った経験あり。※本記事は掲載時点の情報であり、最新のものとは異なる場合があります。システムやソフトウェアの開発に携わるエンジニアに向けた最新情報など仕事に役立つ話題などを提供していきます。Corporation。