日本の市の人口順位をEXCELにダウンロードして散布図に描く

 過去5年間の人口増減率から自治体の将来を予測するでは日本の都市の運命を占った.その元となるデータをダウンロードして散布図にする方法を述べる.

Wikipediaの日本の市の人口順位

 該当ページはここにある.本来であれば総務省統計局のデータを元に集計すべきだが,ここでは簡易のため Wikipedia の資料で代用する.

 ブラウザの URL をコピーする.

WikipediaのURLをコピー
WikipediaのURLをコピー

EXCELでデータの取得と変換

 EXCELの「データ」タブから「データの取得と変換」「Webから」をクリックする.

「データの取得と変換」の「Webから」
「データの取得と変換」の「Webから」

 URLをペーストする.

URLをペースト
URLをペースト

 ナビゲーターの初期画面.テーブルの候補が表示されている.

ナビゲーターの初期画面
ナビゲーターの初期画面

  最初のテーブルを選択したところ.右側にテーブル構造が表示される.これは目的のテーブルではない.順番にクリックして探す.

最初のテーブルを選んだところ
最初のテーブルを選んだところ

 Table1 に欲しいデータがある.右側にプレビューが表示されている.ここで「読み込み」をクリックするとデータの読み込みが始まる.

目的のデータはTable2にある
目的のデータはTable2にある

 「読み込み」をクリックするとデータの読み込みが始まり,「クエリと接続」で871件のデータが読み込まれたことが表示される.

クエリと接続で読み込まれたデータ件数が表示される
クエリと接続で読み込まれたデータ件数が表示される

データクレンジング

 このテーブルはまだ簡単な方だ.データクレンジングの目的はデータベースにそのまま読み込ませることができる形,すなわち第一正規形に直すことだった.

 2行目はタイトル行と重複しているので不要であり,3行目は東京都の集計行であるため不要だ.まとめて削除しよう.

タイトル行と集計行を削除する
タイトル行と集計行を削除する

文字列型の数字を数値型に変換するには「掛け算」

 ところで「法定人口」や「推定人口」,「増減率」のデータ,いずれも文字列として扱われているのが分かるだろうか.これは数値に直しておきたい.この場合,最も簡単な方法は「数値を掛け算する」である.

 「増減率」の左側に1列挿入する.作業列であるが,これは次で本物のデータに化ける.

「増減率」の左に一列挿入する
「増減率」の左に一列挿入する

 「種別」列の左側に列を新しく「挿入」する.

「種別」の左にも一列挿入する
「種別」の左にも一列挿入する

 数式で 1 をかけ算する.リターンキーを押すと全データが反映される.

計算式で「推定人口」に1をかけ算する
計算式で「推定人口」に1をかけ算する

 文字列型のデータが数値型に変換された.

文字列型が数値型に変換される
文字列型が数値型に変換される

 +記号が邪魔なので「検索と置換」で一括削除しよう.

「検索と置換」で+記号を削除する
「検索と置換」で+記号を削除する

 204 件削除された.

204件が置換された
204件が置換された

 数式で0.01を乗算する.これはパーセンテージを実際の小数に直すためである.

「増減率」に0.01をかけ算して小数に変換する
「増減率」に0.01をかけ算して小数に変換する

 同様に文字列型と数値型の混在したデータが数値型に変換された.リターンキーを押すと全データが反映される.テーブルだとこのあたりが自動化されていて楽である.

文字列型・数値型の混在したデータが数値型に変換された
文字列型・数値型の混在したデータが数値型に変換された

参照関係の解消にはコピーして「値の貼り付け」

 数式のままでも良いのだが,何かのはずみで参照元の列が削除されると参照エラーが発生して都合が悪いため,コピーして値の貼り付けを行うと数式の参照関係を解消できる.

 推計人口の列をコピーする.

変換後のセル領域を選択してコピー
変換後のセル領域を選択してコピー

 そのまま「値の貼り付け」を行う.

「値の貼り付け」
「値の貼り付け」

 同様に増減率の列をコピーする.

数式の入ったセル領域を選択して「コピー」
数式の入ったセル領域を選択して「コピー」

 そのまま「値の貼り付け」を行う.

「値の貼り付け」
「値の貼り付け」

 参照元だった列は不要となるため,削除してしまおう.

参照元だった列は不要となるので削除
参照元だった列は不要となるので削除
参照元だった列は不要となるので削除
参照元だった列は不要となるので削除

データ系列を指定して散布図を描く

 おそらくSheet2 にデータテーブルが挿入されているはずだ.Sheet1 には何もデータが入っていないだろう.Sheet1 に移動する.このワークシートにチャートを挿入しよう.

 「挿入」タブから「散布図」を選ぶ.

「挿入」タブ「グラフ」の「散布図」
「挿入」タブ「グラフ」の「散布図」

 空白のチャートを右クリックして「データの選択…」を選ぶ.

「データの選択...」
「データの選択…」

 「データソースの選択」が現れる.「追加」をクリックする.

データソースの選択
データソースの選択

系列の編集

 「系列の編集」では「系列名」「系列 X の値」「系列 Y の値」をそれぞれ指定することになる.

 「系列名」にカーソルを入れる.

「系列の編集」
「系列の編集」

 「系列名」の右側のボックスをクリックするとワークシートのセルが参照できるようになる.

「系列の編集」でセル参照
「系列の編集」でセル参照

 Sheet2 のセル「推計人口 増減率」を選択する.リターンキーを押す.

系列名のセルは複数指定できる
系列名のセルは複数指定できる

 系列名が指定されたところである.

系列名にセル参照が入った
系列名にセル参照が入った

 タブキーで次の「系列 X の値」に移る.この状態でワークシートのセルが参照できるので,Sheet2 の「増減率」のデータ列を選択してリターンする.選択にはシフトキーと矢印キーをうまく使おう.なお,タイトルのセルは含めてはいけない.

「系列Xの値」にはX軸にあたるデータ領域を指定する
「系列Xの値」にはX軸にあたるデータ領域を指定する

 タブキーで「系列 Y の値」に移り,「推計人口」の全データを指定してリターンする.ここでもタイトルのセルは含めない.

「系列Yの値」にはY軸にあたるデータ領域を指定する
「系列Yの値」にはY軸にあたるデータ領域を指定する

 「OK」をクリックすると系列が一つできた.ポイントによって色を分けたい場合などは「追加」をクリックしていくのだが,今回はそこまではしないため,さらに「OK」をクリックしてこのダイアログを抜ける.

「系列の編集」ですべてのセル参照が終わったところ
「系列の編集」ですべてのセル参照が終わったところ

散布図のレイアウトの変更

 散布図の初期状態はお義理にも見やすいとは言い難い.そこでここから色々変更していく.

 まず,場所を左上にドラッグして寄せる.個人的には罫線に合わせて正方形にすると格好いいと思う.

 「グラフのデザイン」タブが現れており,好みのグラフスタイルに変更できる.個人的にはブルーバックの白ポイントが好きだ.

「グラフのデザイン」で好みのものに変更できる
「グラフのデザイン」で好みのものに変更できる

X軸の書式設定

 X 軸を選択して「軸の書式設定…」を選ぶ.

X軸を選択して「軸の書式設定」
X軸を選択して「軸の書式設定」

 初期画面.ここで変更するのは「最小値」「最大値」「縦軸との交点」「表示形式」である.

「軸の書式設定」の初期状態
「軸の書式設定」の初期状態

 「最小値」「最大値」は大きい方に合わせて 0 が中心に来るようにする.「自動」のままにしておくとフィルターをかけた際に勝手にレイアウトが変更されてしまうため,値を変えない場合でも一旦別の値を入力してから元の値に戻すこと.

 「縦軸との交点」には「最小値」で入力した値をそのまま入力する.これで Y 軸が左側に来る.

 「表示形式」には「パーセンテージ」を選ぶ.「小数点以下の桁数」は実際の数値を見て増やすかそのままにするか判断する.ここでは変更していない.

「軸の書式設定」で「最小値」「最大値」「縦軸との交点」「表示形式」を変更する
「軸の書式設定」で「最小値」「最大値」「縦軸との交点」「表示形式」を変更する

Y軸の書式設定

 そのまま Y 軸を選択すると切り替わる.ここで変更するのは「対数目盛を表示する」「最小値」「最大値」「表示単位」である.

Y軸の「軸の書式設定」で「対数目盛を表示する」「最小値」「最大値」「表示単位」を変更する
Y軸の「軸の書式設定」で「対数目盛を表示する」「最小値」「最大値」「表示単位」を変更する

 まず最初に「対数目盛を表示する」にチェックを入れる.「最小値」を 1000 に,「最大値」を 1000 万に変更する.「表示単位」を「万」にするのは桁数を減らすためである.

Y軸の「軸の書式設定」で「対数目盛を表示する」「最小値」「最大値」「表示単位」を変更する
Y軸の「軸の書式設定」で「対数目盛を表示する」「最小値」「最大値」「表示単位」を変更する

 Y 軸を選択したまま右クリックして「補助目盛線の追加」を行うと対数グラフらしくなる.

Y軸に「補助目盛線の追加」
Y軸に「補助目盛線の追加」

データ系列の書式設定

 最後にデータポイントの書式設定を行う.データポイントを適当にクリックして右クリックし「データ系列の書式設定」を選ぶ.

「データ系列の書式設定」
「データ系列の書式設定」

 初期状態では下図のようになっている.

「データ系列の書式設定」では「系列のオプション」が初期状態で見えている
「データ系列の書式設定」では「系列のオプション」が初期状態で見えている

 「バケツのアイコン」をクリックすると「線」と「マーカー」の書式を設定できるようになる.

「データ系列の書式設定」で「バケツのアイコン」をクリックすると「線」と「マーカー」が現れる
「データ系列の書式設定」で「バケツのアイコン」をクリックすると「線」と「マーカー」が現れる

 ここで変更するのは「マーカーのオプション」「塗りつぶし」「枠線」である.

「データ系列の書式設定」の「マーカー」の初期状態
「データ系列の書式設定」の「マーカー」の初期状態

 「マーカーのオプション」は「自動」だと大きすぎるため,「組み込み」に変更してサイズを小さくする.「塗りつぶし」を「自動」から「白」に変更する.また「枠線」を「自動」から「線なし」に変更する.

「データ系列の書式設定」の「マーカー」で「マーカーのオプション」「塗りつぶし」「枠線」をそれぞれ変更する
「データ系列の書式設定」の「マーカー」で「マーカーのオプション」「塗りつぶし」「枠線」をそれぞれ変更する

 これで完成である.

散布図が完成した
散布図が完成した

まとめと課題

 Wikipedia の日本全国の市の人口順位から市勢を散布図に表現する方法を解説した.

 思うところとして,これは単年度の横断的なデータであり,経時的な推移をアニメーションで表現してみたい.おそらく三角形の左辺付近の都市は滝が流れ落ちるように滑り落ちていくと推測される.

 もう一つ.人口規模に反比例するように人口増減率は収斂していくのであるが,おそらく都市の消滅閾値も三角形の左辺に沿っているものと推測される.

 それを表現するためには,グラフを極座標系に変換する必要があると思われる.EXCEL の機能ではそこまで表現できないが,何らかの方法は模索したい.

“日本の市の人口順位をEXCELにダウンロードして散布図に描く” への2件の返信

コメントを残す

メールアドレスが公開されることはありません。 * が付いている欄は必須項目です

このサイトはスパムを低減するために Akismet を使っています。コメントデータの処理方法の詳細はこちらをご覧ください