掲示場マッピングで知ってると便利なExcel小技 その3 元データ整理をパワークエリで簡略化
グーグルマップ作成にお役立ちのExcel小技集その３です。
今回はパワークエリ(PowerQuery)を使った掲示場データ管理のお話。
実は今回の作成ボランティアをするにあたって、近畿エリアについては最低限の仕様を統一するという話が出ました。
通し番号、投票区、掲示場番号、住所、場所詳細、座標といったところ。座標は任意なのですが、私は前回の手順で入れております。
で、当然なのですが、自治体によって資料はばらばらです。
販売するわけではないので、現時点では完全に仕様を合わせるところに注力はしていませんが、最低限は合わせておきたいところ。市町村議員選挙ならいいですが、国政選挙や都道府県首長選だと複数自治体をまたがって地図を見るので、境界付近であまりにも仕様が違うといらっとしますしね。
そこで便利なのが、Excelのパワークエリ機能。実は1回目の表読み込みも機能の１つですが、今回は読み込んだあとの整理方法の話をメインに。
上のデータから下のデータを作成する場合
○投票区と投票区単位の番号（例に出した宮津市（京都）は投票区単位の番号がないので、通し番号と同じ）を組み合わせて、板番号を作成。
○作成した板番号＋半角スペース＋住所でピンのタイトルを作成
○緯度＋半角コンマ、スペース＋経度でグーグルマップ用座標を作成
○新たに作った列にタイトルをつける
○管理用につけていた番号や仕様変更のために置いていた元データ列削除
以上の作業が必要になります。
1回しかやらないなら、関数組んで式コピペや列カット＆ペーストで作ればいいのですが、同じような作業を毎回やるなら、最初の整理は最小限にとどめて、同じ作業は自動化してしまったほうがいいですよね。あとは勘違いで全然違うデータにしてしまったけど、間違った状態で保存して元データは消えたので最初からってリスクも多々。
ちなみにですが、選管情報としては
＜必ずある情報＞
・投票区番号
・何らかの番号 通し番号ないしは投票区単位の番号。ユニークな板番号になっている場合もあり。
・住所・場所詳細 両方あることが多いですが、たまに片方のみの自治体も
＜まれにある情報＞
・座標 選管がマッピングしている場合、開示している自治体も。
行政コードは私が管理用につけているだけで、選管の資料についているのは今のところ見たことはありません。
というわけで、パワークエリを使ったデータ加工開始。
まずはテーブルにした表の読み込みから。
※webから前々回の手順でダウンロードしていればテーブルになっていますが、掲示場についてはPDFから加工が多いと思いますので、なっていなければ挿入タブ→テーブルで表をテーブル化してください。タブ右端にテーブルデザインタブがあれば、テーブル化されています。
読み込んだものがこちら。PowerQueryエディター画面です。
ここからデータ整理に。
列はドラッグで自由に左右に動かせます。
○列のマージ（結合）
結合する列を選択。隣に持ってくる必要はありませんが、左の列が先に来るので、左右逆であれば先に入れ替え。
列の追加タブ→列のマージを選択。
結合部分に入れる文字は選択肢になければカスタムで作成。カスタムの空白は入力しておけば後ろに文字がなくても反映されます。
今回は4回マージしますが、全部書くと無駄に長くなるので、１つだけ例を。グーグルマップの読み込み形式に座標の書き方を変換するのが下記。
・緯度と経度の列を選択→列のマージ
・区切り記号カスタム→コンマ＋半角空白【, 】
・新しい列名 ※あとで変更できるので決まっていればで。なおこの列名は間違っていたので、後で直しました（笑）。
OKすると下記の通り緯度経度列ができました。この時点では分割した状態の緯度列、経度列も残っています。
同様に他の列もマージで作成。上の方に書いた必要な情報を全部入れて、不要になった列をホームタブ→列の削除で処理。残す列の順番とタイトルを整えます。
今回の例ではありませんが、通常は住所を下記の通り処理しています。
ex. 大山崎町役場
京都府乙訓郡大山崎町字円明寺夏目3
タイトル 【板番号】 円明寺夏目3
住所 大山崎町字円明寺夏目3
住所のほうは市町村名からフル表記にしていますが、タイトルについては長すぎると見づらいので、
・市町村以下を表記
・大字○○、小字×× → ○○ ××
この場合は、ホームタブ→値の変換で、大字、小字を空白に変換（削除）で対応しています。
必要な作業が全部済んだら閉じて読み込む。
これで完成です。修正が出たら、ホームタブ→PowerQueryエディターの起動。これでエディターが何回でも編集可能です。緯度経度→座標にタイトルを直したり（笑）。
※肝心のことを書いていませんでしたが、元の表の見出し以下を同じ仕様で一部＆全部入れ替えたら、同じ修正をした表を書き出してくれます。なので、反復作業に便利ということです（2022.11.26追記）。
もちろん他にもいろいろな機能がありますが、これだけでも結構資料整理が楽になるので、お試しを。
最後に、ざっくり手順でなくてちゃんと内容知りたいんですがって方向け、参考にした資料を。
＜書籍＞
Excelでできるデータの収集・整形・加工を極めるための本
めっちゃ分厚いです。初心者が使う本としては難易度が高いのですが、パワークエリを集中して取り上げた数少ない本で重宝しています。
＜YouTube＞
【Excel】Twitterで話題のPower Query使ってみた！【ゆっくり解説】
こちらはパワークエリって何なの？それおいしいの？って初心者さんでも理解できる内容です。こちらで基礎の基礎を理解したのちに難しい動画を見るといいかなと。
これで予定の3回分が終了しましたが、番外編としていくつかネタもあるので、気まぐれにまた取り上げる予定です・