業務課題をHackせよ! プログラミングで仕事自動化物語(11) でたらめな住所を探せ！数百件のExcel名簿から郵便番号と住所の不一致を探す

この物語は、入社して一年目の新人社員、田中君が業務を自動化していく様を記したものです。物語はフィクション（時々ノンフィクション）ですが、自動化の手法は実際にパソコンやスマートフォンで実践できるものですので、物語と自動化作業の両方を楽しめます。

【物語】

「はぁ。今日もなんとかやり過ごせた・・・」

僕自身、ようやくテレワークにも慣れてきたところなのだが、新人の僕が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ファイルと同じフォルダに配置した状態で実行してください。

VBAエディタでモジュールを挿入

画面左上のプロジェクトエクスプローラーから「Sheet1」を選んでダブルクリックすると、Sheet1に関するVBAを記述する画面が表示されます。最初に関数Zip2Addrが使えるかテストしてみましょう。

以下のプログラムを記述したら、カーソルをEnd Subのすぐ上においた状態で[F5]キーを押してみましょう。

Sub TestZip()
    Debug.Print Zip2Addr("105-0011")
End Sub

すると郵便番号データから住所を調べてイミディエイトウィンドウに、該当する住所を表示します。イミディエイトウィンドウを表示するには、メニューの「表示＞イミディエイトウィンドウ」をクリックすると表示されます。

郵便番号から住所を得たところ

もしもSQLiteデータベースやSQLに詳しい方であれば、上記のサンプルプログラムにある標準モジュールの「ZipNo.bas」を開いて内容を確認してみてください。SQLiteのデータベースを開いて、該当する郵便番号をSELECT句で検索して返しているだけの簡単なモジュールです。

適当な住所を赤色にマーキングしよう

さて、郵便番号から住所が得られるようになったので、名簿の郵便番号と住所が合致しないものがあれば赤色にマーキングするようにしましょう。先ほどと同じくSheet1のコードに以下のようなプログラムを記述します。

Sub CheckAddr()
   ' 各種変数を宣言
    Dim FRange As Range
    Dim zipno As String, addr As String
    Dim addr2 As String, s As String
    Dim i As Integer
    ' Sheet1の郵便番号と住所の範囲を得る --- (*1)
    Set FRange = Sheet1.Range("E2:G999")
    ' 一つずつ郵便番号を確認していく --- (*2)
    For i = 1 To FRange.Count
        zipno = FRange.Item(i, 1).Value ' 郵便番号 --- (*3)
        addr = FRange.Item(i, 2).Value ' 住所
        If zipno = "" Then GoTo ContinuePoint
        addr2 = Zip2Addr(zipno) ' 正しい住所を得る --- (*4)
        s = Mid(addr, 1, Len(addr2)) ' 住所の共通部分を取り出す
        If s <> addr2 Then ' 間違っていたら赤色に塗る --- (*5)
            Debug.Print "error:" & zipno & "-" & addr & "<>"; addr2
            FRange.Item(i, 2).Interior.Color = RGB(255, 0, 0)
            FRange.Item(i, 3).Value = addr2
        End If
ContinuePoint:
    Next
End Sub

先にプログラムを確認してみましょう。(＊1)の部分では郵便番号と住所が記述されている範囲を取得します。(＊2)の部分ではFor文を使って一行ずつ郵便番号と住所を確認していきます。(＊3)の部分で実際に入力されている郵便番号と住所を得ます。

(＊4)の部分では入力されている郵便番号から正しい住所を取得します。そして、その後、入力された住所とデータベースから得た住所を照合してあっているか確認します。(＊5)の部分では間違っていた場合にセルの背景を赤色に塗ります。そして、そのすぐ右隣に本来の住所を書き込みます。

同じように、End Subの上にカーソルをおいた状態で[F5]キーを押してプログラムを実行してみましょう。

実行したところ

すると、適当に入力されただろう住所が赤色にマーキングされます。そのすぐ右隣に本来あるべき住所が表示されます。

なおサンプルの名簿は少し前に機械的にランダムに作られたものです。そのため、最新の郵便番号データベースでチェックすると、市町村合併などのために住所が微妙に変わってしまっている住所も赤色にマーキングされてしまっています。結局のところ最終的には人間の目でチェックしないといけませんが、チェックの手間はずいぶん減らせるはずです。