【エクセルで情報収集】エクセルをお持ちの方はプログラムをダウンロードして試して下さい。できればこの機会にプログラム開発をチャレンジしたらどうでしょうか？開発の進め方も簡単に説明しています。開発しない人もマクロの良さを感じて欲しいです。

プログラムのダウンロード

地方公共団体(市役所、区役所、町村役場)情報取得.xlsm

545 KB

ファイルダウンロードについて

ダウンロード

注）バージョンはEXCEL2016以降で動作します。
　　またMACでは動作しませんのでご注意下さい。
注）EXCELをダウンロードして開くとたまに以下のような表示がでることがあります。

その場合お手数ですが「デスクトップ」にコピーして動作させるか、以下の対処を行ってください。

Excel(エクセル)でマクロを有効化出来ないトラブルの対処法 - 会計事務所と中小企業のDXを支援する｜株式会社OA推進センター

スクレイピングって何？

• 「こすること」「けずること」という意味ですが、システム的にはクラウド(ウェブサイト)から必要な情報を取得することです。

• 例えば「競馬情報」をJRAサイトからEXCEL表に取得して、すぐに分析し予想ができる…ということをワンクリックで実現できることも可能です。

• 目的のホームページがあれば「株価情報」「商品情報」「賃貸情報」「求人情報」「病院・施設情報」「弁護士・会計士情報」等、さまざまな情報を収集することができます。

スクレイピング開発の進め方

※私がプロの経験からプログラム・作成手順をご説明します。
　当然今回の説明だけでは開発無理です。しかし「完成するまでの手順」や
　「何の知識」が必要かを理解して頂けると思います。

■スクレイピングする目的を明確にする。

ネットからどういう情報を取得したいかを決めることから始まります。
今回は例として「全国の市役所、町村役場」の名前や住所・電話番号・FAX番号を取得することを目的とします。

■目的達成のために情報があるウェブサイトを探す。

目的を探すキーワードを決め、Google等の検索サイトを使って目的のサイトを探すことになります。
※今回のキーワード例「地方公共団体一覧」「北海道庁　電話番号」
　もし複数がみつかった場合、情報量やわかりやすさ…等で
　サイトを決定して下さい。

「地方公共団体一覧」で検索した結果、次のサイトに決定とさせて頂きました。

全国地方公共団体一覧｜所在地・地図・地方公共団体コード

■最終的に作成するプログラムを動画でイメージしてください。

■プログラム言語を決める。

プログラムを動作する場所がクラウド(WEB)なのかクライアント(パソコン)なのかによりプログラム言語が変わってきます。
　クラウド：Java、C言語、Python、Visual Basic .NET、PHP　等
　　　　　「Google App Script」ならEXCELのような表も作成可能
　クライアント：Python、VBA、C言語　等
※今回はパソコンで動作するEXCEL+VBA(マクロ言語）を使用します。
　理由は私が得意とするプログラム言語であり、EXCEL表とVBAが一体
　としていますのでデータ収集には最適なことです。

■情報を取得するネットへのアクセス方法を決める。

大きく２つの方法があります。
「ブラウザを経由してデータ取得」「ブラウザ経由しないでデータ取得」

▼「ブラウザを経由してデータ取得」
　数年前はIE(Internet Explorer)を使用しEXCELのマクロで直接制御できた
　のでそれが主流でした。残念ながらIEが消滅した今は使えません。
　現在は「Google Chrome」「Microsoft Edge」を使った方法が主流にな
　っているようです。
　ただ私は以下の理由でブラウザ経由は使わないようにしています。
　　・「ブラウザ」をプログラムで直接制御できません。
　　　そのためインタフェース・アプリ（EXCELとブラウザの橋渡し）の
　　　「Selenium」をインストールする必要があります。
　　　「Seleniumを導入し、VBAでchromeを自動操作する設定手順」を参照
　　　※開発者は当然ですが、使用するユーザもインストールする必要が
　　　　ありますので、お客様にお手数を掛けることになります。　
　　・ブラウザは画像等全て取得し表示するため、取得時間がかかります。
　　・ブラウザは表示時エラーになる確率が結構高いです。大量に情報取得
　　　する時に特に向かないと思われます。

▼「ブラウザを経由しないでデータ取得」
　・Microsoft社が提供するクラスライブラリ(プログラム部品）を使用
　　します。
　・エラーの確率がかなり低いです。
　・画像を取得しない分（テキストだけしか取得しない）アクセスが早
　　いです。

■ウエブサイトのデータ収集方法(ページ)を分析する。

一般的に「URL」を使用し取得したいページを指定します。

それでは具体的にURLを調べましょう！！
▼地図の北海道をクリックしてみましょう

ブラウザの一番上のURL欄には
https://www.kurunavi.jp/municipality/01.html 　と表示されます。
また、ページには「北海道庁」「赤平市役所」…等々表示されますので、
「北海道」の地方公共団体が取得できることが確認されます。

次に地図の沖縄県をクリックしてみましょう

ブラウザの一番上のURL欄には
https://www.kurunavi.jp/municipality/47.html 　と表示されます。
また、ページには「沖縄県庁」「石垣市役所」…等々表示されますので、
「沖縄県」の地方公共団体が取得できることが確認されます。

北海道のURLと沖縄県のURLを比較し「何が違うか」を確認します。
北海道：https://www.kurunavi.jp/municipality/01.html　　
沖縄県：https://www.kurunavi.jp/municipality/47.html　　　
※数字が違うことがわかります。
この数字は何となくわかるかと思いますが一般的に使われる県コードです。
北海道：01、青森県：02、・・・　鹿児島県：46、沖縄県：47のように
番号を変えてデータ取得すれば全国データを取得できることになります。

■ウエブサイトのデータ収集方法(ページ内容)を分析する。

ページ内にある情報をどうやって取り出すかを検討します。
青森県(02)で具体的に検討します。
Google Chromeには開発者支援するツールがあります。
Chromeデベロッパーツール　を使用します。

青森県庁の表示内容と対応するHTMLの内容です。
注）画像が小さいのでクリックすれば多少大きくなります

HTMLの内容からわかること
・表「TABLE」タグを使用すれば青森県庁を取得できる
・「TABLE」タグのclassの名前は「style5」と分かる

各市町村役場の表示内容と対応するHTMLの内容です。
注）画像が小さいのでクリックすれば多少大きくなります

HTMLの内容からわかること
・表「TABLE」タグを使用すれば青森市役所を取得できる
・「TABLE」タグのclassの名前は「style5」と分かる

■HTMLの分析からマクロ(VBA)でデータを取得する概要（イメージ）です。

「TABLE」もclassの「style5」も複数存在します。
そのためプログラムでは「TABLE」を繰り返すのか、classの「style5」を繰り返すのかですが、今回は「style5」を繰り返し取得します。
以下のコードは全ての県庁・市役所等を取得できるように、「表」（表番号を変数【T】)と「表中の行」（行番号を変数【R】)を繰り返しています。
注）１行目（変数【R】が０）はタイトル行なので無視し２行目以降を
　　取得対象としています。
注）「NET_DOC」はネットから取得した結果が入っているオブジェクト
　　変数です。

  For T = 0 To NET_DOC.getElementsByClassName("style5").Length - 1
    With NET_DOC.getElementsByClassName("style5")(T)
      For R = 1 To .Rows.Length - 1

      Next R
    End With
  Next T

問題は【青森県庁】と【青森市役所】が表の形式が違うことです。
その違いは【表の列数】で判断すればよいことを見れば分かるかと思います。
.Rows(R).Cells.Length　・・・　行(変数【R】)の列数を取得する

  For T = 0 To NET_DOC.getElementsByClassName("style5").Length - 1
    With NET_DOC.getElementsByClassName("style5")(T)
      For R = 1 To .Rows.Length - 1
        Select Case True
          Case .Rows(R).Cells.Length = 3                                  ' 県庁
　　　　　　　　　　：　　（県庁のデータをEXCEL表に取得）
          Case .Rows(R).Cells.Length = 5                                  ' 県庁以外
　　　　　　　　　　：　　（県庁のデータをEXCEL表に取得）
          Case Else
        End Select
      Next R
    End With
  Next T

実際の項目を取得はCellを指定し、文字(innerText)を取得します。
実際のプログラムとは違いますが、下記のような使い方になります。

  For T = 0 To NET_DOC.getElementsByClassName("style5").Length - 1
    With NET_DOC.getElementsByClassName("style5")(T)
      For R = 1 To .Rows.Length - 1
        Select Case True
          Case .Rows(R).Cells.Length = 3                                  ' 県庁
            地方公共団体 = .Rows(R).Cells(0).innerText
            所在地 = .Rows(R).Cells(1).innerText
            電話番号 = .Rows(R).Cells(2).innerText
          Case .Rows(R).Cells.Length = 5                                  ' 県庁以外
            地方公共団体 = .Rows(R).Cells(0).innerText
            よみがな = .Rows(R).Cells(1).innerText
            所在地 = .Rows(R).Cells(2).innerText
            電話番号 = .Rows(R).Cells(3).innerText
        End Select
      Next R
    End With
  Next T

■VBAの参照設定(ネットにアクセスするため　他）

Microsoftが提供しているクラスライブラリ（プログラム部品）を参照設定することにより、VBAが使用することができます。

Public htmlDoc            As Object
Public NET_DOC            As HTMLDocument
Public Whttp              As WinHttpRequest

Microsoft HTML Object Library 　…　HTMLをタグ等で取得できる
　⇒VBAの指定：Public NET_DOC As HTMLDocument
Microsoft WinHTTP Services Version5.1　…　ネットアクセス用オブジェクト
　⇒VBAの指定：Public Whttp As WinHttpRequest
Microsoft ActiveX Data Object 2.8 Library …　文字コード変換(UTF-8 等）

■その他、プログラムで対応すること

• 所在地の内容が【郵便番号】と【住所】に分ける必要があります。
  改行で分ければできるかと思います。

• 電話番号も【電話番号】と【FAX番号】に分ける必要があります。
  所在地の分け方と違い、電話番号だけとかもありますので工夫が必要です。

■上記で分析した結果を踏まえたプログラムをダウンロードした
　プログラム・ソースで確認してください。

ネットのアクセス方法、都道府県ごとのURL対応、【郵便番号】【住所】分け、【電話番号】【FAX番号】分け、等々実際の処理を含んでいます。

■スクレイピングに関する知識は以下の通りです。

• EXCELのVBAの知識

• Microsoftのクラスライブラリ(ネットアクセス等々）の使い方

• HTML（主にタグ）の知識

• 文字コード(UTF-8,Shift-JIS,EUC等々）の基本知識

• GETとPOSTの違い
  今までの説明はURLだけでページを読んでいます。それが「GET」です。
  「POST」はURLの他にサイトに対して別のリクエスト情報を送り、必要なページを読み込むことです。

• できれば正規表現(パターンマッチ）
  文字列の中から郵便番号・電話番号の文字パターン認識をすれば簡単に取得できたり、入力データのチェックも簡単にできます。
  できれば知っていて欲しいクラスライブラリです。

■「習うより慣れよ」を実践してください。

• VBAの命令を覚えようとすると「山ほど」命令があり、また実践でほとんどは使わない命令が多いです。
  まずは今回私が作成したプログラムをベースに、皆さんが色々と試して下さい。もし必要な命令がありましたら自分で調べて色々な命令を試して知識を増やしてください。

• VBAは処理の途中で止めることができます。
  命令を止めた時点で変数の値がどうなっているのかや、処理の動きがみえると思います。プログラムの動作を知ることで、プログラム開発能力を高めることができます。

最後に・・・

サイトごとに取得方法の難易度ががあります。取得するために「パズルを解く感覚」で考えて対応して下さい。
ただGoogle等はアクセス制限（ロボット：プログラムで取得禁止）があったり、サイトにより「プログラム取得できないように仕組んでいる？」こともあります。そういう場合はあきらめるしかないと思います。

有料で申し訳ありませんが、様々なジャンルのプログラムを私が作成しましたのでご利用して頂ければ嬉しく思います。

最後までお読み頂きありがとうございました。