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

Power BI 関連の話をしていると、たまに「良いサンプルデータがない」ということを聞きます。しかし、世の中には、サンプルとして使える「データ」が「実は」公開されています。
データ分析の素材として、すぐに使える形式のものが少ないのが玉にキズですが、それを補う ETL 機能を持っているのが、Power BI Desktop や Excel の取得と変換です。（ただ、この点は他の会社の BI ツールも同様です）

日本マイクロソフトの Data Platform Tech Sales Blog さんの「Power BI Desktop を使って訪日外国人 (インバウンド) 統計データを可視化する」で紹介されていた、日本政府観光局（JNTO)さんが公開されているデータが、とても興味深いのと、Power BI Desktop で十分に利用できるサンプルなので、それを使って何ができるか、をご紹介したいと思います。

http://www.jnto.go.jp/jpn/index.html

JNTOさんは統計情報として「訪日外客統計」を Web サイトで公開しています。

訪日外客統計の集計・発表

このリンク先のページには、PDFのレポートで、毎月、どの国から、何人訪日しているか、公開されています。
PDFだと、今の Power Query / Power BI Desktop / Excel でも取り込みが難しいのですが、レポートではなく「統計データ」が、Excel ブックとして公開されています。

http://www.jnto.go.jp/jpn/statistics/visitor_trends/index.html

このページで公開されている「国籍/月別 訪日外客数 (2003年～2016年)(Excel)」がとても興味深いデータの集まりで、Power BI などで利用しやすい形式になっています。

http://www.jnto.go.jp/jpn/statistics/since2003_tourists.xls

なんといっても（xlsxではないのは置いといて）、Excel ブックなので、ローカル PC のディスクにダウンロードしないで、直接 Power BI Desktop や Excel から参照できます。

具体的な手順や設定はもちろん大切ですが、まずは、このデータを使ってどんなことを探ってみたくなるのか、考えてみました。

そんなに難しく考えないでいいんです。難しく考えないで、「このデータは出せる？」という思いつきを「簡単にできるかどうか」検討することが、トレーニングとして重要であり、そのデータから「あれは？」「これは？」を素早く、数多く取り出すことができれば、データへの考察が深まるでしょう。（取り出すことに時間がかかるのであれば、それはやるべきではないのかもしれません。しかし、重要なのは、それはそのコストが高いのではなく、その時点で取り出すためのスキルが無い、ということど同意だと思います。もちろん、そのスキルを得るために費やした時間とコストは存在しますが。）

まず思いつくのは公表されているデータの中で「最新」はいつで、その最新のデータから訪日外客数が多い国のトップ5とその数値が欲しい、みたいな感じではないでしょうか。

URLの記述が、/since2003_tourists.xls なので、2003年以降のデータがどんどん追加されていっているようです。実際にブックをダウンロードしたのが以下です。

2003年から「シート」が追加されていくタイプです。
印刷レポートも兼ねることから、この手のデータはクロス集計（ピボット集計）の形式が主流なのは仕方ありません。今は、Power BI Destop / Excel 取得と変換の「ピボットの解除」があるので、涙目になることはありませんね！

このブックから「最新のデータ」を抜き出す方法を考えます。

(1) VBAは使わない(シートの名前から「年」を判断しない）
(2) すべての国のデータが入っているわけではないが、データが入っている月を最新とする
(3) Power BI Service のダッシュボードやレポートでメンバーと共有し、モバイルで確認できるようにする

つまり、Power BI Desktop や Excel の Power BI アドインとVBAを除いた機能や関数で、最新データの年・月を出してみよう、ということです。もちろん、データソースを目視すると最新データの年月はわかりますが、データソースの更新をするだけで、抜き出す「最新の年月」も「更新される」イメージです。

この「最新データの最新とは何日か？」というのは、レポートを作成する上で必要になるのですが、意外に簡単ではありません。



このような月次データだと、あまり「更新された最新日」に重要性を感じられないかもしれませんが、いずれ題材となるであろう「気象庁 地震データ」などは、いつのデータなのかが非常に重要な要素となります。 このあたりはバランス感覚が必要ですが、データの分析においては「いつなのか」という日付・時刻が大切になることが多々あるので、ぜひ意識してください。

ということで、JNTOの訪日外客数ブックから、Power BI Desktop や Excel へ分析しやすい形でデータを取り込む必要があります。

ポイントは以下です。

• ブックにあるワークシート名を指定しないで、必要なワークシートをすべて取り込む
  (ワークシートが追加された場合、削除された場合の対応）
• クロス集計表のピボット列をピボット解除し、テーブル形式に変換する
• 必要な行だけを取り込む
• 必要な列だけを取り込む
• 必要な列を追加し、値を作成する

ブックにある、2003年から2016年までの14枚のワークシート、来年には2017年を含めたワークシートを取り込むようにする設定、よく見るクロス集計表をテーブル形式に変換する方法などは、今後、官公庁によって作成されるデータを利用するには必須の方法になるでしょう。

次回は、具体的なブックの取り込み方、テーブル形式への変換を手順を追って説明することになるでしょう。お楽しみに。

次回はこちらです。

[ピボットテーブル] 日付 時刻 の自動グループ化を無効にする

過去に何度か取り上げているこの話題ですが、いつの間にか Excel 2016 のオプションで、この日付/時刻列の自動グループ化のオフ/オンの設定が可能になっていました。

以前は、グループ化された直後に Ctrl+Z で解除をしていました。

https://road2cloudoffice.blogspot.jp/2016/04/excel-2016.html

または、レジストリでオフにする方法が紹介されていました。

ピボットテーブルで時間グループ化をオフにする

Excel 以外のデータソースに接続して、データモデル経由でピボットテーブルを利用する場合は必要な機能ですが、ワークシートだけの利用だと、ちょっと使いづらく、やはりコンテキストメニューからのグループ化のほうが使いやすいんですよね。

[Power Query / 取得と変換] FIND と Text.PositionOf、そして Text.Middle

セルA1 に "AB-345" の文字列がある場合、"-"(ハイフン）の位置を調べるワークシート関数は、

=FIND("-", A1)

で、3 を返してくれます。ちなみに、もし、ハイフンが「無かったら」#VALUE エラーになるので、IFERROR を組み合わせますよね。もしなかったら、-1 を返す、という数式が以下です。

=IFERROR(FIND("-",A1),-1)

同じことを Power Query Formula Language (M言語）でやろうとすると、以下になります。

Text.PositionOf([文字列],"-")

この関数は 2 を返します。「０から始まる！」のパターンです。もし、ハイフンがなかったら、この関数は -1 を返してくれます。

ちなみに、ワークシート関数の FIND は大文字・小文字は区別されますが、SEARCH は区別されません。 PositionOf は FIND 同様に区別します。

おおよそ、文字の位置がわかったら、そこから前の部分を抜き出すとか、そこから後ろを抜き出す、といった使い方をします。

AB-345 からハイフンの前にある"AB"を抜き出すには LEFT 関数を、後ろにある 345 を抜き出すなら MID 関数を使うことになります。

エラー処理をしないワークシート関数を使った単純な数式が以下です。A1セルに"AB-345"が入力され、A1を参照し、ハイフンの前と後ろを抜き出す例です。

=LEFT(A1,FIND("-",A1)-1)

=MID(A1,FIND("-",A1)+1,LEN(A1))


同じことを Power Query のクエリ エディターでやると以下のような数式になります。

Text.Start([文字列],Text.PositionOf([文字列],"-"))

Text.Middle([文字列],Text.PositionOf([文字列],"-")+1)

ワークシート関数の MID と違って、ハイフンの後ろの「長さ」を指定する必要がないのはラクですね。