業務でExcelを使う人なら、社内データの傾向を読み解いたり、今後の方針をExcelで分析する必要にせまられることがあります。そんな時に便利なピボットテーブル、使い方さえマスターすれば、手軽にオリジナル・マーケティング資料の作成に役立てることができます。
この記事では、まだ「ピボットテーブルには取っ付きにくい」と思っている人に、練習も兼ねて、「国勢調査」の生データ(Excel)をピボット分析する方法を、数回にわけて解説します。
これを自社データと付け合わせてみるだけでも、十分説得力のある分析資料が出来あがりますよ。
国勢調査とは?
統計法に基づいて、5年に一度、日本国内の全世帯(もちろん外国人世帯も含みます)に対して行われる最大規模の統計調査。2020年10月に実施された最新調査は、一部先行公開の情報を除いて集計途中のため(*2021年3月現在)、ネットから広く利用できる最新資料は2015年に行われた調査のものです。
e-Stat(政府統計の総合窓口) 国勢調査 | すべて | 統計データを探す | 政府統計の総合窓口
から、誰でも無料で、Excel形式の調査結果資料をダウンロードすることができます。
国勢調査から、ターゲット人口をあぶり出します
たとえば、日本全国で展開する高年齢女性向け通信販売会社が、販売促進を強化すべき地域を抽出するためにピボット分析をすると仮定します。(ご自身のビジネス・フィールドにあわせて、男女・年代層などのセグメントを変えて分析してみてください。)
まず、元データとなる総務省統計局の統計資料 2015.xls をダウンロードします。
ダウンロード済のファイルを、Excelで開いて分析を開始しますが、これはこのままは使えません。
なぜでしょう?
「汚れたデータ」だからです。ピボットテーブルにExcelの［セル結合］はご法度なのです。
分析対象とするシート全体を選択して、書式設定から［セルの結合］チェックを外しておきましょう。事前に引かれた罫線やフィルタなども全て外しておくと、後の操作がしやすくなります。
さらに、シート内で分析をかける範囲を［テーブル］としておくと便利です。今回は「高年齢女性」人口に関する分析なので、全国総人口と、「女性、かつ65歳以上」の人口が記載された列までの範囲に［census］というテーブル名をつけておくことにしました。
ピボットテーブルで、都道府県別の65歳以上女性人口を総人口と比較
それでは早速、目的の分析を行うために［挿入］→［ピボットテーブル］をクリックしてみましょう。
統計データが記載されたシートとは別に、［table］というシートを1枚追加して、そこにピボットテーブルを作成することします。
国勢調査が最強の統計資料である理由は、Excelで無料で使える上に、【全区市町村】に細分化された情報が取得できることです。
ただし、この記事では、分かりやすくするために、一旦【47都道府県】での分析とします。
［ピボットテーブルのフィールド］ダイアログボックスで、都道府県コードを行にドラッグ&ドロップ、総数(人口総数)と65歳以上女性人口を列にドラッグ&ドロップすることで、わずか数秒で【都道府県別・総人口おおび65歳以上女性人口クロス集計表】が完成しました。
行に「都道府県名」(北海道、青森県…)を選択してしまうと、都道府県名の読み仮名降順(つまり「あいうえお順」あいち→あおもり→あきた…)と並んでしまいますが、気にならないのであれば実際の都道府県名を行に入れても構いません。
なお、どうしても「都道府県コード」では分かりづらいと思ったら、裏技ですが、コード順の都道府県リストを事前に登録してしまう、という手もあります。
［ファイル］→［オプション］→［詳細設定］→［ユーザー設定リストの編集］をクリックしてください。
ここに、あらかじめ都道府県の表示順位を決めたユーザー独自リストを設定しておけば、ピボットテーブルで(コードではなく)都道府県名をすえても、ユーザーが意図した順番通りで集計リストが作成されます。
分析に便利なフィルター機能
ピボットテーブルは強力な分析ツールなので、単にクロス集計表を手早く作成できるばかりでなく、さらに様々に細分化された分析を行うことができます。その第一歩として、フィルター活用事例をみてみましょう。
国勢調査では、都道府県・区市町村の人口、世帯数が分かるだけでなく、一部の行政区に「大都市圏」というフラグが立っています。これは、居住地と通勤・通学圏が異なる場合に、「どの大都市圏の商圏ととらえるべきか」を示唆(しさ)する符号です。札幌、関東、近畿、北九州福岡など、全国で11の大都市圏フラグが立っています。
たとえば、先の都道府県別高齢女性人口を、「大都市圏」ごとに比べてみたいと考えた場合、［ピボットテーブルのフィールド］ダイヤログボックス、フィルターのフィールドに「大都市圏」を入れてみましょう。
都道府県別総人口、65歳以上人口のクロス集計表の上部に「フィルター」があらわれ、11の大都市圏を選んで人口多寡(たか)を比較できるようになりました。試しに「大都市圏：仙台」を選択すると、宮城県の他に、一部福島県のデータが同時に抽出されます。これは、福島県北部が、ほぼ仙台商圏に含まれる、ということを現しています。このように、単に「都道府県」の区切りではわからないマーケットの大きさを比較するために、「大都市圏」というフィルターも役に立つのです。
今回は文字列をフィルター条件として選択しましたが、フィルター抽出条件が数値の場合には、規定の数値(金額)以上／以下、もしくは数値の上位5%、10位以内、といったフィルターがけも可能です。
まとめ
ピボットテーブルと国勢調査で、分析しましょう! 売上を上げましょう!
この記事でご紹介したピボットテーブルの使い方はまだ「序の口」で、ここから更に深く分析の幅を広げていくことができます(より深いご説明は、次の機会に)。
そして、Webから無料で利用できる国勢調査、便利だと思いませんか? 使用料無料、というのは、調査が税金でまかなわれているからであって、日本人なら使わない手は無い、とも言い切れる重要指標です。Excelで加工すれば、自社売上データと比較して「自社の強いところ、弱いところ」などの分析も簡単に行うことができます。
無料で使えるデータも、知恵と工夫で自社ビジネスにフル活用して、業務改善につなげていきましょう!
