大字・町・丁目レベルの人口・世帯数データを活用する (エクセル編)

投稿日: 2021年1月27日 2021年1月27日
投稿者: morita
カテゴリー: data, エクセル
タグ: データ編集

国や自治体が公表してくださっている人口や世帯数に関するデータ。マーケットを分析するうえでの基礎データです。経営・営業戦略の練り直しや新規出店エリアの検討など、さまざまなシーンで活用できます。本稿は「大字・町・丁目」レベルの人口・世帯数・年齢・高齢化などをみえる化する手法について解説てしています。

データ

データについて

＜データ出典＞
神戸市ホームページ
＞住民基本台帳に基づく人口(町丁目別・年齢別)
https://www.city.kobe.lg.jp/a89138/shise/toke/toukei/jinkou/juukijinkou.html
令和２年(2020年)　11月のエクセルファイルを使用させていただきました。

データのビュー

　エクセルブックには複数のシートがあります。
・シート「神戸市」は神戸市合計、区別合計のデータです。
　神戸市全体を区別に分析したいときはこのシートがオススメです。

　今回は「大字・町・丁目」の分析を行いたと考えているので、各区別になっている複数シートのなかの「東灘区」を活用させていただきます。

　使用するのは「東灘区」です。

データの内容

＜ディメンション＞
区名、町名(丁目まであり)

＜メジャー＞
・人口
　年齢別(1歳ごと)、性別（男女の別）、年齢と性別のクロス集計はないため、たとえば、60歳の男女別人口は算出できません。
・世帯数
　世帯ごとの男女比や世帯主の年齢等のデータ含まれていません。

データ編集

データを編集するまえに

　データをダウンロードして保存します。データを編集するときは、原本を編集せず、複製してから編集する、あるいは、原本を参照するようなかたちで編集・分析を行います。
　原本を編集してしまうと編集後の値などが本当に正しいのか？を、検証することができなくなるリスクがあります。

　今回のデータは原本をそのまま保存しておいて、編集・分析するシートだけを複製します。

・シート名のタブを右クリック
・「移動またはコピー」を選択します。

・ドロップダウンから「新しいブック」を選択
・「コピーを作成する」へチェックし、OKです。

・名前をつけて保存します。

・シート名のタブを右クリック
・「末尾へ移動」をクリック
・「コピーを作成する」をチェックしてOKです。

　同様の手順でシートを複製します。
　ここから、複製した２枚のシートを編集します。

世帯数・人口

　まず、「世帯数」、「男」、「女」、「合計」の列(ピンクの部分)を取り出します。

・1行目はタイトルです。行を削除します。
・3行目は合計(各列の値のタテ合計)です。これも行を削除します。(合計の行は分析時に集計すればよいのでデータの段階では不要です)

・I列から右側の列をすべて削除します。
　「取り出す」と書きましたが、不要な部分を削除すれば取り出したのと同じ結果になりますね。

　バッサリと削除しましょう。失敗しても大丈夫です！原本が残っていますから。

　シート名を「世帯数・人口」へ変更しました。

・カーソルの位置はセルA1です。
・「挿入」タブの「テーブル」をクリックします。
・窓が開きます。
　データ範囲は自動で指定されます。一応、最終行までが指定されているのかを確認してください。
　「先頭行をテーブルの見出しとして使用する」にチェックされていると思います。そのままOKです。

　1行目にフィルターが自動で設置されて、シマシマのデザインでなっていればテーブルへ変換されています。
　しかし、列名が見えない、シマシマが中途半端です。

　シートへ何らかの書式設定がなされているときにシマシマが乱れることがあります。

・シートの角をクリック(これでシート全体を選択できます)
・「ホーム」タブの「塗りつぶしの色」をクリック
・パレットの「塗りつぶしなし」をクリックします。

・「デザイン」タブ
・テーブル名を書き換えます。「世帯数・人口」にしました。
　テーブル名をタイプしたらエンターキーを押してください。エンターキーを押さないまま、別の個所をクリックしたりするとテーブル名が変更されていないことがよくあります。

年齢・人口

　複製しておいたもう一方のシートへ移動します。今度は、列Iから右側の列を取り出します。

・1行目と3行目を削除します。
・列E・F・G・Hを削除します。

　右端の列「再_65歳以上」は年齢65歳以上の人口の合計です。行政系のデータに「再」とか「再掲」とかの行列がありますが、これらは概ね小計・合計です。

・この列を削除します。(分析過程で集計するのでデータとしては不要です)

パワークエリエディターで編集

　シート名を「年齢・人口」にしました。

　ここからはエクセルの「パワークエリエディター」という機能を使用して編集します。
　聞きなれない機能かもしれませんが安心してください。マウス操作と簡単な入力だけです。

・カーソルをA1へあわせます。
・データタブ
・「テーブルから」をクリックします。

　テーブル化したときと同様の窓が開きます。データの範囲と見出しのチェックを確認してOKです。

　数秒後に画像のようなビューが開きます。

　「0歳」から右側の列(「100歳以上」まで)すべてを選択します。
　列の選択方法は、
・「0歳」の列の列名をクリック
・表の右端が見えるとこまで下のスクロールバーバーをずらします。
・パソコンの「shift」キーをを押して右端の列「100歳以上」の列をクリックします。

・「変換」タブをクリック
・「列のピボット解除」をクリックします。

　画像のように「属性」「値」の２列ができていれば成功です。

・「列の追加」タブ
・「カスタム列」をクリックすると窓が開きます。
　窓が開いたときに「カスタム列の式」の「＝」の後ろでカーソルがピコピコしていると思います。

・「使用できる列」の「属性」をダブルクリックします。
　＝[属性]　このようになります。

・OKで閉じます。

　「属性」の列が「カスタム」という列名で複製できました。

・列「カスタム」をクリック(列が緑色になります)
・「ホーム」タブをクリック
・「列の分割」の▼三角(ちっちゃいの)をクリックします。

・「数字から数字以外による分割」を選択します。

　列を数字と漢字に2分割できました。

・「カスタム2」の列をクリック
・「ホーム」タブ
・「列の削除」をクリックします。
　（列を右クリックして削除することもできます）

　列名のセルをダブルクリックすることで名称を変更できます。
・「属性」を「年齢」
・「値」を人口
・「カスタム1」を「年齢 (値)」へ変更しました。

・「年齢 (値)」列名へ表示されている「ABC」のアイコンをクリック
・「整数」を選択します。

　ふたたび
・「列の追加」タブ
・「カスタム列」をクリックします。

開いた窓の
・「新しい列名」へ「年齢集計」と入力
・＝の後ろへカーソルをあわせて、「人口」をダブルクリック
・「*」を入力して
・「年齢 (値)」をダブルクリックします。

=[人口]*[#"年齢 (値)"]

　カスタム列の式がこのようになっていればOKです。
　「人口」と「年齢 (値)」の掛け算です。

・「名前」の窓へタイプします。「年齢・人口」にしました。
・「ホーム」タブ
・「閉じて読み込む」をクリックします。

　画像のように新しいシートができます。シート名を変更します。画面右側の「ブッククエリ」は「×」で非表示にします。

パラメータ的なデータを作成

新規シート

・新規シートを作成します。
・列名は、
「年齢 (値)」「65歳」「70歳」「75歳」

・列Aへ0～100までの値を1行ごとに入れます。

・列Bは列Aの64までが「65歳_未満」65から下の行が「65歳_以上」
・列Cは69を境に未満と以上
・列Dは74を境に未満と以上

テーブルへ変換

・「挿入」タブ
・「テーブル」をクリックしてテーブル化します。
・「デザイン」タブ
・「テーブル名」をタイプ
・シート名を変更します。「年齢2分割」にしました。

テーブルのリレーションシップ

リレーションシップ

・「データ」タブ
・「リレーションシップ」をクリックします。

・開いた窓の「新規作成」をクリックします。

　ドロップダウンの中から選択します。ここまでの手順で設定したテーブル名と列名がドロップダウンのなかに表示されます。
・「テーブル」は「年齢2分割」と「年齢_人口」をそれぞれ選択　
・「列」は上下ともに「年齢 (値)」
・OKです。

リレーションシップ追加

・前の画面でOKをクリックすると画像の画面へ戻ります。
・新規作成をクリックします。

・「テーブル」は「世帯数・人口」と「年齢_人口」をそれぞれ選択　
・「列」は上下ともに「町コード」です。
・OKです。

　妙なワーニングが表示されることがありますが、そのままOKです。

・閉じます。

ピボットグラフ

テーブルへ接続

・新規シートを挿入します。
・シート名を変更します。
・「挿入」タブ
・「ピボットグラフ」の▼三角をクリック
・「ピボットグラフ」を選択します。

　まず、画像右側の窓が開きます。
・「外部データソースを使用」のラジオボタンをクリックします。
・「接続の選択」をクリックします。

　左側の窓が開きます。実際は画像のようにではなく覆いかぶさるように開くと思います。
・「テーブル」のタブをクリック
・「ブックのデータモデルのテーブル」をクリックして
・「開く」をクリックします。
　左の窓が閉じられます。

・既存のワークシートでOKです。

ピボットグラフ初期設定

・「分析」タブ
・「スライサーの挿入をクリック
・開いた窓の「すべて」のタブをクリック
・「世帯数・人口」のなかの「町名」へチェック
・OKです。

　横長ディスプレイのパソコンのとき、リボンを折りたたむとピボットの操作性がよくなります。

　グラフエリアやスライサーをレイアウトします。
　あらかじめスラ―サーで、分析したいエリアだけを選択しておきます。

　このシートを複製してどんどんグラフを作成すれば、
・すべて同一サイズのグラフをつくれる
・スライサーを毎回設定しなくてもよい
　これらのメリットがあります。

　ところが、どうも上手くいかないことがあります。

使用しないテーブルへの接続を自動的に切るのかなぁ？

　ここからは、私が所有しいるパソコンのエクセルに限った事象なのかもしれません。
　あくまでも私見と想像ですが・・・

　テーブルへ接続してピボットグラフを作成します。画像のようにテーブルデータが表示されます。
　枠線で囲ってある3つのテーブルはリレーションしてあります。

　この状態で上書き保存して、エクセルを終了します。

　閉じたエクセルをもういちど開きます。
　データテーブルの一部しか表示されません！

　現状のシートで使用しているテーブルデータはスライサーに設定している「世帯数・人口」だけです。
　つまり、このシートのピボットで使用しているデータがあるテーブルにのみ接続しています。

　画像のように３つのテーブルからデータを使用しているピボットの接続は、エクセルを閉じて開いても、つながったままです。

　想像ですが、メモリの使用率を下げるとか、動作スピードを上げるとかの理由があって、使用していないテーブルへの接続を自動的に切るようなしくみになっているのではなかろうかと・・・合理的だと思います。

　画像のように３テーブルからデータを使用した状態の原紙を作成すれば接続状態は保たれます。

　私見と想像はここまでにして、先へすすみます。

ピボットグラフを作成する

世帯数と人口

　初期状態ではデータ項目が折りたたまれています。
・テーブル名の先頭にある白い三角をクリックします。

・「世帯数・人口」のテーブル
・「町名」の☑チェックをクリック
・「世帯数」の☑チェックをクリック
・「合計」の☑チェックをクリック
　これで完成です。

・グラフエリアの右肩にある̟⊞プラスをクリック
・「グラフタイトル」、「データテーブル」をチェックします。

・「グラフタイトル」をダブルクリック
・名称を変更します。

　データテーブルの値の書式を変更しましょう。

・「合計」の▼三角をクリック
・「値フィールドの設定」を選択します。

・開いた窓の「表示形式」をクリック

・設定方法は通常のエクセルと同じです。
・OKで閉じます。

・グラフエリアをクリックします。

・「ファイル」タブ
・「印刷」
・各種印刷設定をしてください。
　グラフの部分を印刷できます。タイトルやデータテーブルが入っていれば、即配布できます。

・エクスポートをクリック
　ファイルを保存する窓が開いたら保存場所とファイル名を指定して保存します。

　PDFが開きます。

　印刷したとときに、ボタンが気になります。
・ボタンを右クリック
・右クリックしているボタンだけか、すべてかを選択してボタンを非表示にします。

メジャーを追加

　メジャーの追加というのは、データ表にある値を使用してデータ表にはない値を追加することです。
　エクセルのシートでよくやる、列や行を追加してセルの計算式を挿入、前年対比とかを計算するようなことです。

・「世帯数・人口」のデータ名を右クリック
・「メジャーの追加」をクリックします。

　窓が開きます。
・「メジャーの名前」をタイプします。

　数式をタイピングするのですが、エクセルのセル計算式とは少しの違いがあります。
　作成する計算式は「=SUM( A ) / SUM( B )」　割り算です。
・「=SUM(　」このあたりまでタイプすると画像のように窓が開きます。
・[合計]をダブルクリックします。
・「)」「/」入力して手で入力します。

「=SUM([合計])/」までできました。