[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版

Read More

Amazon QuickSight - ストーリーボードが面白そう

Amazon が BI ツール市場に参入しましたね。（というか一般公開）

Amazon QuickSightが一般提供開始（日本はプレビュー）
https://aws.amazon.com/jp/blogs/news/amazon-quicksight-now-generally-available-fast-easy-to-use-business-analytics-for-big-data/

去年の10月の Amazon のイベントで Amazon QuickSight が披露されて、いくつかの記事になっていました。

@IT AWSのセルフサービスBI、「Amazon QuickSight」とは何か
http://www.atmarkit.co.jp/ait/articles/1510/15/news033.html

このブログでは Microsoft の Excel と Power BI を扱っていますが、Power BI のエリアでは、Microsoft のみならず、多くの BI サービスを提供するベンダーがしのぎを削っています。

で・・・正直いって、それほど各社に大きな「差」があるわけではないと感じています。

ガートナーさんはお得意のマジック・クオドランドでポジショニングしてますが。
http://it.impressbm.co.jp/articles/-/13288

Magic Quadrant for BI and Analytics Platforms 2016 出典：米Gartner

ローカルPCの世界では「Excel」という巨人がいるので、多くの BI ツールは「クラウドサービス」として差別化をしているように思えます。
クラウドサービスにするメリットは多くあります。なんといっても、レポートやダッシュボードなど、分析結果の「共有」はクラウドならではのメリットを享受することができます。ここは Excel の不得意なところですからね。

Amazon QuickSight は、他の BI ツールとはちょっと違うアプローチをしているように思えました。それは「ストーリーボード」です。

(おおよそ、SPICE で言っている、超高速とか、パラレルとか、インメモリとかは、だいたい同じようなコンセプトとテクノロジーで各社が実装しています）

ストーリーボードが面白いのは「データでストーリーを語る」と言い切っていることです。

Power BI Services でダッシュボードを作って、他のメンバーと共有したとします。もちろん、テキストボックスなどはあるので「ここのポイントは～」などというコメントは入れられますが、あくまで「補助的」なものです。

ダッシュボードの共有の「目的」は、たしかにリアルタイムもしくは定期的に、決められたKPIやデータを時系列や、最大・最小、全体に対する割合などでチェックすることですが、それでもダッシュボードを単純に見るだけで、それを見た人が問題を把握するなんてことは理想にすぎないと思いませんか。

データドリブンなプレゼンテーションができたら・・・なんて思っていたところに、この QuickSight がストーリーボードのコンセプトを持ってきて、ちょっとわくわくしました。

以下のビデオが AWS  QuickSight を紹介している最近のものかなぁ、と思います。（AWS Summit Series 2016 | Chicago) この動画の中でストーリーボードのデモが 35分ころから始まります。いわゆる、全体から特異点を見つけ出してドリルダウンしていく過程です。（ごめんなさい。言えるほど自分がすごいわけではないですが、それほど面白い、わかりやすいデモではありません＞＜ でも、全体を通してみてみる価値はあります。）


1年間のお試し無料枠があるので、すでにAWSアカウントを持っていれば、無料で利用可能です。（日本はプレビュー）

たぶん、PowerBI から見ても無視できない存在になりそうです。

で・・・

今週末に Power BI コミュニティで登壇しますが・・・満席でした＞＜

https://connpass.com/event/43908/

今回は、日本政府観光局さんのデータを使って、ハンズオン的に以下をご紹介したいと考えています。

（１）ブックに存在する複数のワークシートからデータを一気に取得する
（２）クロス集計表を「ピボット解除」を使ってテーブル形式に変換する
（３）なんらかの結果を Power BI Services を使って共有する

(1)、(2)は Excel の取得と変換でも、Power BI Desktop でも操作は一緒です。Excel 2016 や Office365 ProPlus Excel をお持ちの方は Excel で、持っていない方は Power BI Desktop で一緒に操作しましょう！

Read More

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

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

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

前回までの手順で、訪日外客数の14年分のデータを含む xls ブックから、必要なワークシートのみをクエリエディタで選択するところまで紹介しました。

Power BI Desktop で年別ワークシートをすべて選択する

ここまでの手順は Excel の Power Query もしくは「取得と変換」でも、ほとんど同じです。

Excel2016取得と変換で年別のワークシートをすべて選択する

今回は Excel のクエリエディタの画面で、各ワークシートのデータを展開する手順を紹介します。若干の UI の違いはありますが、Excel の Power Query / 取得と変換と、Power BI Desktop の外部データからデータを取得する機能はほとんどが同じであり、かつ同じ操作手順です。

フォルダのアイコンから編集を選び、不要な印刷範囲を取り除いたクエリエディタには [Name] と [Data] の2つの列があります。
Name 列は「ワークシート名」です。 Data 列は [Table] というオブジェクトへのリンクがあり、その [Table] をクリックすると、ワークシートが展開されます。

Table をクリック

クエリエディタで2003ワークシートの中が表示された

[Name] の 2003 の行にある [Data] 列の Table は 2003 ワークシートだけのデータです。他のワークシートの Table を展開していません。

すべてのワークシートの、Table を展開し、14年分のデータを1つのワークシートで持ちたいので、2003 年のデータの展開前に状態を戻します。

[ワンポイントアドバイス]
状態を 2003 ワークシートの Table クリックの展開前に戻したい場合、向かって右側にある作業ウィンドウ [クエリの設定] の [適用したステップ] にリストされているステップを消すことで、前の状態に戻すことができます。各ステップ名の前にある [ X ] でステップの消去が可能です。
[変更された型] と [2003] のステップを削除することで、Table 展開前にもどります。

Data列にある、それぞれの「Table」をクリックすると、1つのワークシートのみを展開しますが、Data列のヘッダーにある[展開ボタン]をクリックすると、すべてのワークシートを展開します。複数のシートを一気に展開するにはこの [展開ボタン] を使います。

テーブル(リスト)形式ではないデータなので、特定の列名はこの時点ではありません。また、元の列名を使うこともないので、[元の列名をプレフィックスとして使用します] のチェックをはずします。

クエリエディタで Data 列の展開ボタンを押し、[元の列名をプレフィックス・・」のチェックをはずす

この操作を可能にするのは、14枚のワークシートすべてのフォーマットが同じである、という条件が必須です。逆に、フォーマットが同じであれば、この展開ボタンによる複数シートの展開がもっとも楽です。

[OK] をクリックすると、Name の列を残して Data 列が複数の新しい列に展開され、2013ワークシートから2016ワークシートの14枚のワークシートの内容が表示されます。

2003ワークシートの後に2004ワークシートのデータが展開されている

 このデータをクエリエディタの機能を使った「整形」していきます。この整形作業が、今回のデータ取り込みの最大のポイントです。

整形作業はおおまかに以下を行います。

• データとして要らない「行」の削除
• データとして要らない「列」の削除
• クロス集計表をテーブル形式に変換（列のピボット解除）
• データの種類（型）の正しい設定
• 必要な追加列の設定
• ワークシートもしくはデータモデルへ保存

これらの操作を簡単に確実に行うために、クエリエディタでは多くの機能が提供されています。

長くなったので、今回はここまでとして、次回は上述の「整形作業」を手順を追って紹介します。
お楽しみに。

次回はこちらです。

Read More

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

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

では、JNTOさんが公開している訪日外客数データを元に、インバウントのインサイトを探すジャーニーに出発しましょう（笑)。

まずは、元データですが、xls 形式の Excel ブックで、1枚のワークシートに1年分のデータが登録されています。2013年から2016年14枚のワークシートが格納されています。

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

これを Power BI Destop または Excel の取得と変換の Web からで、このリンクを利用します。Excel ブックじゃないことに注意です。

Power BI Desktop

Excel 取得と変換

Power BI Desktop を例に手順を追ってみていきます。

なお、Power BI Desktop で xls ブックを開こうとすると「エラー」になる場合があります。（たぶん、多くの人はエラーになると思います）
エラーになった場合は以下を参照してください。Access Database Engine のインストールが必要です。
https://powerbi.microsoft.com/ja-jp/documentation/powerbi-desktop-access-database-errors/

通常、複数のワークシートからデータを取り込みたい場合、取り込みたいワークシートをチェックします。

Power BI Desktop のナビゲーターで複数ワークシートを選択

この方法だと、新しくシートが追加された場合は指定しなおさない限り取り込まれません。
また、それぞれをチェックすると、チェックした数のクエリが作成され、その数の分だけ「編集作業」をしなくてはなりません。

実は、Excel の取得と変換(Power Query)では、フォルダーのアイコンを選択して、[編集] をおして、次の設定画面に進むことができます。

ところが、Power BI Desktop (バージョン: 2.40.4554.421 64-bit (2016年10月)) の場合、フォルダーのアイコンを選択すると、[編集] ボタンがグレーになり、押すことができません。

Power BI Desktop [編集] ボタンを押せない

Excel ではできるのに、なぜ Power BI Desktop ではできないのか、と思いましたが、フォルダのアイコン上で右クリックメニューを出すと [編集] がでてきます。このトラップはびっくりしました。

Power BI Desktop の [編集]ボタン

このフォルダ アイコン（＝ブック）を編集ボタンで取り込むと、クエリエディタの以下の画面が表示されます。

Power BI Desktop クエリエディタ 複数シート取り込み

それぞれのワークシートの名前の他に、'2005$'Print_Area という項目があります。これは「印刷範囲の指定」をした範囲を表します。テーブルがある場合は「テーブル1」といったテーブル名が表示されます。

業務上は、なるべく表を「テーブル形式」にしておくことで、Excel ブックのワークシートから必要な「データのみ」を取り出すことができます。そのようなときは、このクエリエディタではワークシートを選ばず、テーブルのみを選びます。今回は、テーブルではないので、ワークシートを選びます。

ここでの注意は「必要なものを選択しない」です。ポイントは「不要なものを外す」です。

今回は印刷範囲をはずしたいので、この列のフィルターオプションで「Print_Area を含まない」を設定します。

テキスト フィルター

行のフィルター ダイアログボックスで 「指定の値を含まない」 で 「Print_Area」 を設定します。

[OK] を押すと、クエリエディタでは、ワークシートのみが残った表になります。

ここまでの手順や設定は、Excel の取得と変換もほとんど同じです。
このあと、Data 列を展開することで、すべてのワークシートのデータを 「クエリの追加(Append）」をすることなく、1つにすることができます。

長くなったので、データの展開以降は次回になります。お楽しみに。

Read More

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

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

次回はこちらです。

Read More

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

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

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

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

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

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

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

Read More

[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 と違って、ハイフンの後ろの「長さ」を指定する必要がないのはラクですね。

Read More