[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 にテーブル１、Sheet2にテーブル２を作成しましたが、列の順番や、テーブルの配置場所は変えたものにしました。テーブル１は「名前」、「区分」、「数値」で A1セルから開始、テーブル２は「区分」、「数値」、「名前」の順に変え、B3セルから始まっています。

テーブル1

テーブル2

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

列名を認識しているクエリ エディター

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

展開ボタンによるテーブルの追加

列の順序はクエリ エディターで最終形に修正することができます。

いかがでしょう。

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

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

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

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