ExcelのPower Query(パワークエリ)

WEB上のデータをPowerQueryでデータベースに整える-Excelで都議選予想分析①

更新日:

普段は会社で経理をやっていまして、会社資料でExcelを使うことも多いので、Excelの機能を色々と使って選挙分析でもやってみようかなと思って始めたものです。

かなりの分量になったので記事を分けてますが、こんな内容になるかなと思います。

2017年選挙データからベースデータを作成する

1.サイトからデータをPowerQueryに取り込む

東京都議会議員選挙のサイトから都議会議員選挙(平成29年7月2日)をクリックします。

https://www.senkyo.metro.tokyo.lg.jp/election/togikai-all/

サイトを掘り下げていき、『開票結果』のURLをコピーします

Excelを開き、「データ」→データの取得と変換内の「Webから」をクリックし

コピーしたURLを貼り付けし「OK」

「Webコンテンツへのアクセス」のウィンドウが開くので、「接続」をクリックしてください。

「ナビゲーター」ウィンドウが開くので、一覧表示されたデータから一番上のURLを選択し、「データの変換」をクリックします。

PowerQueryが開きます。

2.データ名を変更する

左側にある「>」をクリックしてクエリを開き、URLをダブルクリックし、メニューの「名前の変更」をクリックし、名称を「ベースデータ」とする。(名称は自由)

3.データを展開する

ヘッダー名が「Data」のものの右端にある「←||→」マークをクリックする。

列データに集約されていたデータ一覧が表示されるので、チェックを外さずそのままOKをクリックします。

→データ列が開きました

4.選挙区を抽出する

ヘッダー名が「Caption」を選択します。

タブ「ホーム」→「列の分割」→「区切り記号による分割」をクリックする

「区切り記号による分割」ウィンドウが開きます。

『区切り記号を選択するか入力してください』の下の設定を

コンボボックス:「カスタム」を選択
入力欄:「選挙区」と入力する
分割:「一番左の区切り記号」にチェックを入れる

→列が分割されました。

5.不要な列を削除する

2列目(Caption.2)~12列目(Data.当落)までを一括で選択する

ホーム→「列の削除」→「列の削除」をクリックする

不要な列が削除されました。

6.不要な行を削除する

Data.氏名の右横の▼ボタンをクリックする

表示される一覧から「(null)」と「合計」のチェックを外します。

不要な行が削除されました。

7.ヘッダー名を変更する

ヘッダーをダブルクリックしてヘッダー名を変更します。

Caption → 選挙区
Data.氏名 → 氏名
Data.所属 → 所属
Data.選挙区計 → 得票数

8.置換機能で不要な文字を消す

得票数の列を選択した状態で、タブ「変換」→値の置換をクリックします。

値の置換ウィンドウが開くので…

検索する値 →「票」
置換後 → 「」(空欄)

9.データ形式を変更する

列の左の文字「ABC」をクリックし、表示されるメニューから「整数」をクリックする

10.年月情報を追加する

タブ「列の追加」→「インデックス列」の横の▼→カスタムをクリックします。

ウィンドウが開きますので、

開始インデックス → 201707
増分 → 0

ヘッダー名が「インデックス」という列が追加されました。

ヘッダー名を「年月」に変更しましょう。

11.重複している行を削除する

選挙区が『島部』のものは同じデータが複数あります。

これは複数の市区町村が合わさって1つの選挙区となっているため、それぞれでの集計値がデータ上残るためです。

全部の列を選択した状態で、

タブ「ホーム」→「行の削除」→「重複の削除」をクリックします。

行数が298から259になりました。

12.保存する

タブ「ホーム」→「閉じて読み込む」→「閉じて読み込む」をクリックします。
※以降は基本「閉じて次に読み込む…」で保存するようにしましょう。

PowerQueryの画面が閉じてExcelシートにデータが表示されます。

定数データを作成し、データ結合する

1.PowerQueryを開き、データを複製する

データを1ヶ所選択した状態でタブ「クエリ」→「編集」をクリックする

PowerQueryの画面が開くので、左側のクエリを「<」クリックして、展開させます。

ベースデータを選択し、右クリックでメニューを開き、複製を選択するとベースデータ(2)が作成されます。

ベースデータ(2)をクリックして「名前の変更」を選択し、「2017定数」に変更します。

2.ステップを削除する

右側にある「適用したステップ」に表示されている処理の左側に表示されている×を押して、「ソース」のみ表示された状態にします。

3.不要な行を削除する

ヘッダー名「Caption」の右端の▼ボタンをクリックし、表示される一覧から「Document」のみチェックを入れる

※(すべて選択)のチェックを外すと全チェックが外れるので、その後Documentにチェックを入れると楽

4.必要なデータを取り出す

ヘッダー名が「Data」のものの右端にある「←||→」マークをクリックする

列データに集約されていたデータ一覧が表示されるので、OKをクリック。

→データ列が開き、さらに現れる「←||→」マークをクリックして、データを開いていきます。

これを繰り返して選挙区や定数について載っている列まで展開する。

タブ「ホーム」→列の削除→「他の列の削除」をクリックし、不要な列を削除する。

5.必要な行のみ抽出する

右横の▼ボタンをクリックし、表示されるメニューの検索に「定数」と入力し、OKをクリックする

定数〇とある行のみ抽出されました。

6.選挙区と定数にデータを分ける

タブ「ホーム」→「列の分割」→「区切り記号による分割」をクリックする

「区切り記号による分割」ウィンドウが開く

コンボボックス:「カスタム」を選択
入力欄:「選挙区」と入力
分割:一番左の区切記号

1列が2列に分割されました。

ヘッダー名をダブルクリックして変更します

1列目 → 選挙区
2列目 → 定数

7.定数の数値のみ抽出する

置換を使って2段階で不要な情報を削除します。

定数の列を選択した状態で、タブ「ホーム」→値の置換をクリックします。

値の置換ウィンドウが開き、

1段階目  検索する値 → (定数
置換後 → 「」(空欄)

2段階目  検索する値 → )
置換後 → 「」(空欄)

で数値のみになります

8.全角を半角にする 

数値のみになった定数情報ですが、全角だと使えないので半角に変更する。Excelであれば関数で行いますが、今回は置換で行います。

全角になっているデータを拾うために列を選択した上で並べ替え(ZA↓)を行うと上位に表示されるのが全角です。

今回の場合は「1」と「4」が全角になっているのがわかります。

値の置換機能で「1」→「1」、「4」→「4」にします。

9.数を整数に変換する

ヘッダー名の左端の「ABC」をクリックすると表示されるメニューから「123整数」をクリックします。

左寄せに表示されていた数が右寄せに表示されました。

10.クエリを組み合わせる

クエリ「ベースデータ」を選択します。

タブ「ホーム」→「クエリのマージ」をクリックします。

上の枠に現在選択しているクエリ(今回は「ベースデータ」)が表示されます。

下の枠はまだクエリが表示されていません。

上の枠と下の枠の間にあるコンボボックスをクリックすると表示されるクエリから「2017定数」を選択します。

下の枠に2017定数のクエリが表示されます。

それぞれのデータで同じ情報の列データを選択します。

今回の場合は「選挙区」

結合の種類のコンボボックスから
「左外部(最初の行すべて、および2番目の行のうち一致するもの)」
を選択します。

「あいまい一致を使用してマージを実行する」にチェックを入れないでOKです。

『選択範囲では、最初のテーブルと〇〇行中〇〇行が一致しています。』と〇〇の数値が一致していればOKです。

ベースデータのクエリに2017定数のデータが追加されます。

11.データを展開し、定数情報を追加する

ヘッダー名がクエリ名で追加され、データには「Table」と表示されます。

右端にある「←||→」マークをクリックし、データ一覧が表示されるので、「定数」のみチェックを付けてOKをクリックする。

定数のデータのみ表示されました。

ヘッダー名を「2017定数.定数」→「定数」に変更する

12.保存する

「閉じて次に読み込む」をクリックします。

Excelのシートに戻り、データのインポートのウィンドウが開きます。

「接続の作成のみ」にチェックを入れます。

右の「クエリと接続」に表示されるデータには

ベースデータ → 〇〇行読み込まれました
2017定数 → 接続専用

と表示されました。
※この設定をすると2017定数のデータはシートとして追加されません。

定数の列が追加されました。

有権者数と投票者数データを作成し、データ結合し完成させる

1.サイトからデータをPowerQueryに取り込む

東京都議会議員選挙(平成29年7月2日)の『投票結果』のURLをコピーします

https://www.senkyo.metro.tokyo.lg.jp/election/togikai-all/togikai-sokuhou2017/togikai-turnout2017-end/

「データ」→データの取得と変換内の「Webから」をクリックします

コピーしたURLを貼り付けしOK

「ナビゲーター」ウィンドウが開く

一覧表示されたデータから一番下の『都議会議員選挙(平成〇〇~)』を選択し、右下の「データの変換」をクリックします。

PowerQueryが開く

2.データ名を変更する

クエリ名が表記されるように開き、「都議会議員選挙…」を右クリックでメニューを表示して、「名前の変更」をクリックします。

今回は「2017有権者等」と変更します。

3.一部データを残して削除する

棄権者数_投票率_前回投票率の3列を選択した状態で、タブ「ホーム」→列の削除→他の列の削除をクリックします。

3列のみ残して削除されました。

4.人数データを整数に変換する

当日有権者数の列を選択した状態で、タブ「変換」→値の置換

値の置換のウィンドウが開きますので、

検索する値 → 人
置換後 → 「」(空欄)

ヘッダー名左の「ABC」をクリックし、表示されるメニューから「整数」をクリックします。

投票者数の列も同様の処理を行う。

5.クエリを組み合わせる

クエリ「ベースデータ」を選択し、タブ「ホーム」→「クエリのマージ」をクリックします。

上の枠と下の枠の間にあるコンボボックスから「2017有権者等」を選択し、上の枠「ベースデータ」は『選挙区』、下の「2017有権者等」は『開票区』を選択します。

結合の種類のコンボボックスは「左外部(最初の行すべて、および2番目の行のうち一致するもの)」のまま、「あいまい一致を使用してマージを実行する」にチェックを入れないでOKです。

『選択範囲では、最初のテーブルと〇〇行中〇〇行が一致しています』
と〇〇の数値が一致していればOKです。

ベースデータのクエリに2017定数のデータが追加されます。

6.データを展開し、当日有権者数、投票者数の情報を追加する

ヘッダー名がクエリ名「2017有権者数」と表示され、データにははTableと表示されますので、右端にある「←||→」マークをクリックし、表示されるデータ一覧のうち、「開票区」のチェックを外し、OKをクリックします。

ヘッダー名をダブルクリックして変更します。

2017有権者等.当日有権者数 → 当日有権者数
2017有権者等.投票者数 → 投票者数

7.PowerQueryを閉じます

タブ「ホーム」→「閉じて読み込む」→「閉じて次に読み込む…」をクリックします。

Excelのシートに戻り、データのインポートのウィンドウが開きます。

「接続の作成のみ」にチェックを入れます。

当日有権者数と投票者数のデータが追加されました。

右の「クエリと接続」に表示されるデータには

ベースデータ → 〇〇行読み込まれました
2017定数 → 接続専用
2017有権者等 → 接続専用

以上で2017年のデータが完成しました。

2001,05,09,13年も同様に3つのデータを組み合わせてデータを作成する

平成25年(2013年)、平成21年(2009年)、平成17年(2005年)、平成13年(2001年)も同様な処理をしてデータを作成します。

ただこの中で平成13年(2001年)の『投票結果』のデータが他と一部違うものがあるので注意が必要です。

2001年のみ個別修正を行う

『投票結果』と『候補者別得票数』を見比べると「北多摩1区」と「北多摩第一」といった違いがあり、「クエリの追加」を行ったときに、上手くデータが作成されないということが生じます。

これを置換で修正するという方法を紹介します。

1.エラーの内容の説明

どのようなエラーが発生するのかを丁寧に説明すると

タブ「ホーム」→「クエリのマージ」をクリックし

上の枠に「2001ベースデータ」で選挙区を選択し、下の枠に「2001有権者等」設定し開票区を選択します。

一番下のメッセージが
『選択範囲では、最初のテーブルと244行中226行が一致しています』
と一致している行数が異なっているのがわかります。

選挙区「北多摩第〇」を確認すると「クエリのマージ」で追加した有権者等のデータを見ると「null」と表示されています。
つまりこれが「クエリのマージ」で一致しなかったデータにあたります。

クエリ「2001有権者等」の開票区を確認すると「北多摩〇区」で、ベースデータの選挙区と異なります。
これを置換して一致するようにします。

2.置換機能で内容を修正する

タブ「変換」→「値の置換」をクリックし、ウィンドウが開きますので

置換する値 → 置換後
1区     → 第一

置換されました。

クエリ「2001ベースデータ」を確認すると「有権者等」「投票者数」がnullから数値になっています。

同じように置換します。

置換する値 → 置換後
2区     → 第二
3区     → 第三
4区     → 第四

マージでのメッセージでの行数も一致しています。

北多摩のどの選挙区もnullになっている箇所はありません。

全データを1つのデータにまとめる

クエリから「ベースデータ」を選択し、タブ「ホーム」→「クエリの追加」

ウィンドウが開くので「3つ以上のテーブル」にチェックを入れます。

「利用可能なテーブル」にクエリやExcelのシート名が表示されます。

そのうち「…ベースデータ」を選択し、真ん中の「追加」をクリックすると、右側の「追加するテーブル」に表示されます。

左下の行数が259行から…

999+行と変わっているのがわかります。

これでPowerQueryでの操作が終わりです。

PowerQueryを閉じて、Excelシートに戻りましょう。

タブ「ホーム」→「閉じて読み込む」→「閉じて次に読み込む…」をクリックします。

Excelシートに戻ったタイミングではすぐにデータには反映されませんが、裏側で処理が終わると…

データができあがります。

左の「クエリと接続」のベースデータの行数が多くなっています。

以上でベースとなるデータが完成しました。

全体の構成

①WEB上のデータをPowerQueryでデータを作成する
②PowerQuery作成データにデータを修正・追加する
③ピボットテーブルの基本と注意点
④ピボットテーブルの機能や細かい設定を紹介
⑤ピボットテーブルで高度な集計可能なメジャー関数
⑥ピボットグラフの作成

都議会議員選挙サンプル.xlsx(実際に作成できるデータとは異なります)

03投稿記事下_関連コンテンツ

-ExcelのPower Query(パワークエリ)

Copyright© 経理と総務の効率化 , 2021 All Rights Reserved.