2016/12/12

[Power BI] 日本政府環境局 JNTO さんのデータからのインサイト(4)

第3回からの続きになります。

日本政府観光局が公開している訪日外客数のデータを使って、Power BI Desktop や Excel、Power BI Service でインバンドのインサイトを探してみよう、という試みの第4回目です。

前回、複数ワークシートを [Data] の 展開ボタンを使って全て展開する、という手順まで紹介しています。



その時「フォーマットが同じであれば有効です」という前提条件を伝えていますが、この「フォーマットが同じ」については、とても重要なので、今回はちょっと寄り道をしてフォーマットの件を説明したいと思います。

今回対象としているデータソースは、日本政府環境局さんが公開している「国籍/月別 訪日外客数(2003年~2016年)(Excel)」というブックです。

このブックをダウンロードして、Excel ブックを開くと年別の「ワークシート」にデータが「クロス集計表」の形式で保存されています。

2016年、2015年、2013年のワークシートは以下のようになっています。

2003年

2015年

2016年
 このワークシートをみても、数字が違ったり、A列にある国が違ったりしています。また、A3セルに記入されている説明・注釈文も、2016/2015と2013で違います。

フォーマットの観点から言うと、これらの違いは問題ありません。フォーマットが同じ、という意味は、参照したいデータの「列」の順番(Excelの場合は A,B,C の列番号)が同じ、ということです

A列に国の名前が「すべてのワークシート」で使われている
B列は1月の数値が「すべてのワークシート」で使われている
D列は2月の数値が「すべてのワークシート」で使われている
F列は3月の数値が「すべてのワークシート」で使われている
・・・・
X列は12月の数値が「すべてのワークシート」で使われている

もし、2016年のシートと他のシートで「列番号」が違うデータの「配置」になっていたら、「すべてのワークシートのフォーマットが同じ」という条件にならないので、展開ボタンを使って一括してワークシートをクエリ エディタに取り込んではいけません。

ワークシートに記入されている行数(=国の数)や、A3の例の注釈、さらに、データの行番号などはシート毎に異なっていても大丈夫です。
例えば、韓国のデータは各シートとも 7 行目から始まっていますが、全部のシートで韓国のデータが7行目から始まらなくても大丈夫です。中国と韓国が入れ替わっていても問題ありません。

もし、あるシートだけ列番号がずれていたら、一括処理から除外して、列番号を合わせる変換を行ってから、取り込まなければなりません。

ただし、テーブルは列番号が違っていても大丈夫です。

Excel のデータはテーブルで保存・再利用する

Excel で表をテーブルに変換すると、構造化参照に代表されるように、セル、行と列の扱い方が変わります。

このブログでもいくつか取り上げています。

テーブルのすすめ 構造化参照
https://road2cloudoffice.blogspot.jp/2014/11/blog-post.html

テーブルのすすめ VLOOKUP関数
https://road2cloudoffice.blogspot.jp/2014/10/vlookup.html

テーブルのすすめ 入力規則
https://road2cloudoffice.blogspot.jp/2014/10/blog-post.html

テーブルとExcel VBA
https://road2cloudoffice.blogspot.jp/2016/02/excel-vba.html

Office TANAKA の VBA セミナー ベーシック他でも、「テーブル(機能)を使わない理由がありません」と紹介しているイチオシ機能ですが、2007年に搭載されてもう少しで10年を迎えるにも関わらず、浸透しているとは言えない状況です。

データの取得と変換機能や Power Query からみても、データソースが Excel の場合は「テーブル」形式の表になっていると、取り込みの際に「ラク」ができるんです。

サンプルのデータは Sheet1 にテーブル1、Sheet2にテーブル2を作成しましたが、列の順番や、テーブルの配置場所は変えたものにしました。テーブル1は「名前」、「区分」、「数値」で A1セルから開始、テーブル2は「区分」、「数値」、「名前」の順に変え、B3セルから始まっています。

テーブル1
テーブル2
このテーブルをこれまで紹介してきた同様の操作でクエリ エディターで取り込むと以下のように、テーブルで定義されている列名の「名前」、「区分」、「数値」を認識して、それらを取り込みますよ、という処理をします。

列名を認識しているクエリ エディター
よって、ワークシート上のテーブルの位置が違っていても、テーブルの内の列の順序が違っていても、「名前」の列は「名前」の列で、「区分」の列は「区分」の列で、そして「数値」の列は「数値」の列でまとめることができます。

展開ボタンによるテーブルの追加
列の順序はクエリ エディターで最終形に修正することができます。

いかがでしょう。

ちょっと横道にそれましたが、データの取得と変換、Power Query は何もデータベースサーバーのデータや、クラウドサービスの API 経由のデータ取得のためだけのものではなく、ExcelやCSVのデータを対象とすることができます。その時、Excel で扱っているデータが「テーブル形式」になっていると、あたかも Excel ブックを「データベースのテーブル」のように扱うことができます。

表計算ソフトの柔軟性ゆえに苦労していたことが、テーブルを扱うことで解消されることが本当に多いので、今一度テーブル機能の利用を検討してはいかがでしょう。

次回は不要な行の削除の方法を紹介します。

[PR] VBAセミナー受講後は、これさえあれば何もいらない
  Excel VBA逆引き辞典パーフェクト 第3版

0 コメント:

コメントを投稿

Powered by Blogger.

自己紹介


PowerBI コミュニティ勉強会の 沼口 です。
https://powerbi.connpass.com/
最近の Excel は Office 365 のクラウドサービスと 連携する方向性が打ち出されています。この「Road to Cloud Office」ブログでは、Excel ユーザーの視点から Power BI Service や、Office 365 の活用方法を模索した結果をお伝えしています。
Microsoft MVP for Data Platform 2017-2018