2016/11/22

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/

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

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

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

2016/11/11

[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ワークシートのデータが展開されている
 このデータをクエリエディタの機能を使った「整形」していきます。この整形作業が、今回のデータ取り込みの最大のポイントです。

整形作業はおおまかに以下を行います。
  • データとして要らない「行」の削除
  • データとして要らない「列」の削除
  • クロス集計表をテーブル形式に変換(列のピボット解除)
  • データの種類(型)の正しい設定
  • 必要な追加列の設定
  • ワークシートもしくはデータモデルへ保存
これらの操作を簡単に確実に行うために、クエリエディタでは多くの機能が提供されています。

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

2016/11/03

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

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

では、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つにすることができます。

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

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



2016/09/17

Power BI 勉強会 @ 品川マイクロソフト 登壇します

登壇します!と、思ったら、すでに満席でした・・・

http://connpass.com/event/38446/

Excelのユーザーのための、とうたっていますが、「取得と変換」の話になります。
逆説的なお題ですけど、以下のように思うんですよね。

  • CSVファイルの取り込みで「取得と変換」使わない理由がわからない 
  • 複数のワークシートからデータまとめる仕事なのに「取得と変換」を使わない理由がわからない
  • 複数のブックからデータをまとめるのに「取得と変換」を使わない理由がわからない
  • その、ピボットテーブルの表を活用するのに、「取得と変換」を使わない理由がわからない
  • VLOOKUPを使った数式を1万行とかのセルにコピーして「遅い」とかいうのはきつい・・・
「取得と変換」を Power Query に置き換えてもらっても同じですけどね。

データ分析結果を共有するなら、Power BI Desktop & Power BI Service です。ここで Excel にこだわる必然はないですよ。Power BI Publisher for Excel はありますが、そのダッシュボード作ることができるスキルがあれば、Power BI Desktop でレポート作っちゃった方が幸せになれると思います。

Excel 中心の作業や業務であれば、Power Query = 取得と変換 は今後必須になるでしょう、というか、いまどき CSV ファイルのインポートでテキスト ファイル ウィザードを使い続けるのはいかがなものか・・・です。取り込む時点で、データの絞り込みやら、細かくできないんですから。
そこから VBA もありですが、その前に取得と変換やってみましょうよ、と思いますね。

リレーションシップも取得と変換によるクエリがベースになります。Power Pivot は使わなくても、ほぼ同様のことを取得と変換でできます。今となっては Power Pivot の必要を感じません。
DAXをやってメジャー作るなら必要か、と思っていたら、Excel の実装はすごくて、データモデルとして登録すれば、Power Pivot を使わなくても、データモデルのピボットテーブルで、メジャーを DAX で作れるんだから、すごいよね。


みたいな話をしたいのですが、いかんせん50分くらいしかないので、絞り込まなきゃいけません。
少しでも参考になる情報を共有できれば!と思っています。
Powered by Blogger.

自己紹介


一般社団法人実践ワークシート協会の理事をしている 沼口 です。
代表理事 田中亨 の薫陶を受け、協会の活動として「正しい Excel の利用方法」を広めるお手伝いをしています。
土日に開催している実践ワークシート協会主催の Excel VBA セミナーは多くの方からご好評をいただき、東京会場実施については 2014 年はすべて満席(当日キャンセルあり)になりました。
このセミナー実施報告は協会の Facebook ページで写真付きで公開しています。 https://www.facebook.com/jppwa  一般社団法人実践ワークシート協会 http://www.pwa.or.jp/

実践ワークシート協会では Excel を最大限に活用できよう様々な情報を提供していきますが、昨今 Office 365 としてクラウドサービスと Excel (Office製品)が連携する方向性が打ち出されています。 PWA Director 「Road to Cloud Office」ブログでは、Excel を中心に Office 365 の活用方法を模索した結果をお伝えしています。

実際に実践ワークシート協会のさまざまな業務では Office 365 と Excel の連携を使い処理をしています。