2016/10/31

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

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

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


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年を含めたワークシートを取り込むようにする設定、よく見るクロス集計表をテーブル形式に変換する方法などは、今後、官公庁によって作成されるデータを利用するには必須の方法になるでしょう。

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

次回はこちらです。

2016/10/25

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

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


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

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

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

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

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

2016/10/15

[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 を返します。「0から始まる!」のパターンです。もし、ハイフンがなかったら、この関数は -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 と違って、ハイフンの後ろの「長さ」を指定する必要がないのはラクですね。



Powered by Blogger.

自己紹介

自分の写真
1989年新卒で日本IBMに入社しダウンサイジング担当としてホストコンピュータと繋げるオフコン、UNIX、PCサーバーのプロジェクトを担当。1997年 MSKK(現日本マイクロソフト)入社、NT4出荷に伴い企業向けサポート部門のビジネスマネージャーとして Excel 使いとなり、2002年 にMSMVPなどをサポートするユーザーコミュ二ティ部門を設立、部門をリード。2006年にMSKK退職後、企業向けのITトレーニング会社・団体に携わり、2014年頃よりPowerBI勉強会主催メンバーの一人として参画、そのコミュニティ活動で MSMVP for Data Platform PowerBI 2017受賞。https://mvp.microsoft.com/ja-jp/PublicProfile/5002635 同年にMVP Awardを返上し、アマゾン ウェブ サービス ジャパンに入社、コミュニティプログラム担当として現在に至る。