各機関で二次利用可能なオープンデータの公開が進んできていますが、

Qlik Senseに取り込んですぐに使える形式かというとそうとは限りません。


ここでは以下で公開している「二次医療圏別の将来推計人口」のデータを例に分析前の下準備についてご紹介します。

ただ、もっと簡単なやり方やQlik Senseのスクリプトで書く方法などあるかと思いますので、よいやり方があれば教えてください。

二次医療圏別の将来推計人口の比較(人口ピラミッドなど)

 

【やること】

都道府県別にExcelファイルに分割され、さらに、市区町村別にExcelシートに分割された

年齢階級別の推計人口のデータをQlik Senseで読み込めるようにします。

 

【利用するデータ】

下記のURLにある都道府県別のExcelファイルを利用します。

都道府県のExcelファイルの中には市区町村別に推計人口が掲載されたシートがあります。

また、推計人口は年齢階級別、男女別で掲載されています。

例えば、北海道の場合、1つのExcelファイルの中に190近くの市区町村別のシートがあります。

 

〇国立社会保障・人口問題研究所

男女・年齢(5歳)階級別データ--『日本の地域別将来推計人口』(平成25年3月推計)

http://www.ipss.go.jp/pp-shicyoson/j/shicyoson13/3kekka/Municipalities.asp

 

【手順】

1. Excelのシートの形式を揃える(必要とするデータのみに加工します)

2. Excelのシートを統合する(市区町村別に複数に分かれているシートを1つに統合します)

3. Excelファイルを統合する(都道府県別に複数に分かれているファイルを1つに統合します)

4. クロステーブルを変換する(2010年、2015年、2020年など複数列で持っている推計人口を1列に変換します)

 

最終的には以下のような形式に変換してQlik Senseに取り込みます。

Excelファイルの加工.png

1. Excelのシートの形式を揃える

①全てのシートは同じ形式で推計人口が掲載されているため、一括で形式を揃えるために全てのシートを選択します。

②「男女計」「総数」「(再掲)」など不必要なデータを削除します。明細データのみあればQlik Senseでダイナミックに集計ができるため、これらのデータは不必要となります。

その1-1.png

③最終的には以下にようにします。

その1-2.png

2. Excelのシートを統合する

①シートが複数に分かれているので一括で統合したいです。検索してみると、運よくマクロを使う方法が見つかりました。

エクセル複数のシートを1枚のシートにまとめる方法を探しています。 -... - Yahoo!知恵袋

②マクロの内容はわかりませんが、どうもシート上のセルの範囲を指定する必要がありそうなので一部変更し実行します。

 

***ここから***

Sub CombSh()

Dim i As Integer

Dim eRow As Long

Dim mySh As Worksheet

 

ActiveWorkbook.Sheets.Add before:=Sheets(1)

ActiveSheet.Name = "統合"

Set mySh = ActiveSheet

mySh.Range("A1:J39").Value = Sheets(2).Range("A1:J39").Value

For i = 2 To Sheets.Count

Sheets(i).Select

eRow = Cells(Rows.Count, "A").End(xlUp).Row

Range(Cells(2, "A"), Cells(eRow, "J")).Copy Destination:=mySh.Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)

Next

mySh.Select

Set mySh = Nothing

End Sub

***ここまで***

 

③うまくいき、一気に統合出来ました。

ここでは、札幌市は中央区など各区のデータの合計として表せるため必要ありません。そのため予め削除しておく必要があります。

また、マクロの実行でなぜか1つ目のシートはダブってリスト化されるため、ダブった部分も削除します。

最終的に、統合シート以外の必要のないシートを削除したら完成となります。

その2-1.png

3. Excelファイルを統合する

①都道府県別に47のExcelファイルがあるため、これを一つに統合します。

その3-1.png

②Qlik Senseでアプリを新規作成し、01.xlsを取り込みます。

データロードエディタに以下のスクリプトが自動で記載されますので、ファイルの指定先を「*」(ワイルドカード)にして

指定のフォルダに格納された複数のExcelファイルを一気にロードします。

その3-2.png

4. クロステーブルを変換する

①データは読み込まれましたが、推計人口が2010年、2015年、2020年、、と独立した項目として読み込まれてしまうため

複数年を横断的に集計する際は非常に不便となります。こうした場合にクロステーブルの変換を行います。

②上述の「変更後のスクリプト」に改めて以下のスクリプトを追加してデータをロードします。

追加するスクリプト → CrossTable(年, 推計人口, 3)

 

上記のスクリプトの意味は、

 年 → 2010年、2015年などの項目をまとめる項目名称として利用します(任意に指定可能)。

 推計人口 → データの値の名称として利用します(任意に指定可能)。

 3 → ロードした項目の順番で3番目までは変更しないという意味です。

 

その4-1.png

③取り込んだデータをデータモデルビューワで確認すると、きちんと取り込まれていました。

その4-2.png

最後は少しわかりづらかったかもしれませんが、下図のように横にもっていた年のデータを縦に変更できたイメージになります。

Excelファイルの加工.png

以上、1~4の手順でオープンデータをQlik Senseで利用しやすい形式に変換してみました。

 

なお、以下のCommunityの記載(英文)を参考に、ロードスクリプトで全てを実行することも可能だと思いますので

(私はキャパオーバーでできませんが)ご関心のある方はお試しください。

〇Excelの複数のシートをまとめる

https://community.qlik.com/docs/DOC-4452

Excelの複数のファイルをまとめる

https://community.qlik.com/thread/231916

〇クロステーブルの変換

https://community.qlik.com/thread/45425