2016/12/29

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

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

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

前回でフィルーターオプションと同じ操作性を持つ機能を使って不要な行を取り除き、必要な行のみを残しました。今回は「列の操作」を中心にご紹介します。

前回、[Column2] 列の見出し行(タイトル)をダブルクリックして [国名] に変更し、第1列の [Name] を同じく [年] に変更しました。


ところで、その横の列が [5月] になっていることに違和感を感じた人も少なくないはずです。その並びは、[5月] [伸率] [6月] [伸率_1] [7月]・・・と続き、[9月] [伸率_4] [1月] [10月] [伸率_5] [11月] ・・・となりました。

この順序は、1行目を見出し行にする前の ColumnX の [X] が原因です。


後に国名となるカラムの名前は [Column1] で、[5月] のカラム名は [Column10] です。[9月] は [Column18] で、その横の伸率は [Column19]、そして [1月] が [Column2] です。この ColumnX の数値 [X] の順番で並んでしまっているのです。1,2,3,4・・・ではなく、1,10,11,12・・・,2,20,21,22・・・ですね。

1月、2月、3月 ~と並べ替えたくなりますが、今回の場合、最後の処理で 「列のピボット解除」 を行って、[月] の列をつくり、その列のデータとして月を入れるので、途中で1月、2月~のように並べ替える必要はありません。

(1) 必要な列を選択して [他の列の削除]


第1列の [Name] を [年]に、第2列の [Column2] を [国名] に変更したら、次は必要な列を残します。不要な列の削除の方法は2つあって、1つはその通りのまま「不要な列をすべて選択して削除する」です。一方で必要な列は [年] と [国名] と [1月] から [12月] までの [月] の列だけなので、それらをすべて選択して [他の列の削除] を選ぶことで、不要な列の削除が可能です。というより、必要な列のみを明示的に指定して残す、と言ったほうがいいでしょう。

Ctrlキーを押しながら、列名をクリックすると、複数選択が可能になります。選択された状態で、右クリックのコンテキストメニューから [他の列の削除] を選べば、選択している以外の列が削除されます。


[列の削除] を選ぶか、[他の列の削除] を選ぶかは、元のデータがどのように追加・変更される可能性が高いかで変わります。

個人的な経験談からすると、Power Query を使い始めた当初は削除する列を選択して [列の削除] を行っていました。ところが、新しいデータに不要な列がたまたま追加されたため、削除する列を明示的に指定しているこの方法では、追加された不要列は削除されずエラーになりました。
必要な列が「変化する」ものではないので、この場合は、必要な列をすべて「指定」して、その他の列を削除する指定方法が最も「再利用に適した」方法でした。

訪日外客数のデータで、必要な列だけを残すと以下のようになります。


(2) 列のピボットを解除する


この [列のピボット解除] の考え方が、もしかしたら「難しい」と感じている人もいるかもしれません。
というのも、非常に残念ながら Excel 入門的なトレーニングで、最初の勉強用の Excel の表として「クロス集計表=ピボットテーブル」を入力するための表として紹介する教材が多いからです。


このような縦にも横にもデータの条件を入れて、そのクロスした位置(セル)にすべての条件を満たす数値を入れる表を「クロス集計表」といいます。たとえば、C2のセルは、「支店が東京で、担当が山田で、4月の数値という条件を満たす合計は 38」という意味です。

人間が見るにはとても見やすい表なので、まずは、空のクロス集計表を作り、罫線をきれいに引いて(罫線の練習)、小計には SUM 関数をつかったり、SUBTOTAL 関数を紹介したり(関数の練習)、見出しセルでは書式の変更でフォントや位置を変えたり、といったことをする教材です。

ただし、これは人間が見やすい「最終形の表」であり、いわゆる「ピポットテーブル」としての「結果の表」です。本来この結果の表を作成するための「元の表」は以下のような、リスト形式またはテーブル形式のようなデータでなければいけません。


 クロス集計表は「結果の表」であり、人間が見やすいように「修飾された表」です。クロス集計表に新しい支店や、新しい担当者、月のデータを追加するのは容易ではありません。それを苦労して修正しているケースを見ることもありますが、追加するデータはリスト・テーブル形式の「追加行」として追加し、このテーブルを元にしてクロス集計表を作る手順が、最も簡単で、最も正確で、最も間違いがない方法です。

もっと言えば、実際のデータはさらに細分化されている場合がほとんどです。
支店が東京で、担当が山田で、月は4月で、製品はAの売上が10、製品のBの売上が8、製品のCの売上が15、製品Dの売上が15で、合計が 38 といった感じです。SUMIFSの関数の題材としては面白いと思いますが、データ分析をするための「元の表」はクロス集計表ではなく、リスト・テーブル形式の表でなければなりません。

今回の題材である日本政府環境局さんのデータは、この「クロス集計表」です。残念ながら、このクロス集計表のままでは、さらなるデータ分析が難しいのです。

そして、このクロス集計表は「人間にとって見やすい」表であり、かつ「印刷に向いている」表でもあるため、官公庁が Web で公開・提供しているデータはクロス集計表であることが多いのも事実です。

このようなクロス集計表をリスト・テーブル形式の表に変換する機能を Power Query / 取得と変換は持っています。それが [変換] タブの [任意の列] グループにある [列のピボットの解除] です。


必要な列を残すときの考え方に似ていて、「解除したい列」を選択して行うか、「解除したくない列」を選択して「他の列を解除する」かを選ぶことができます。

ピボットを解除した表は以下になります。


(3) データ型とデータの変換


第3列の [属性] は見出しの名前を [月] に変更し、第4列の [値] は [訪日外客数] に変更します。

この列の操作ではあと2つの変換を行います。まず、簡単なものは、 数値のデータである [訪日外客数] の列を、「数値である」設定をします。

列見出しのアイコンは、その列のデータの種類を表します。[訪日外客数] は実は数値なのか、テキストなのかわからない状態の [?] アイコンです。このアイコンをクリックすると変換データのタイプが表示されるので [整数] を選びます。


アイコンが [123] に変わり、3桁カンマがなくなります。これで計算が可能な「数値」になりました。
この型の変換は、[変換] タブの [任意の列] グループの [データ型の検出] でも可能です。この場合は自動的に型を検出し設定してくれます。

次に、[月] 列のデータが 4月、5月・・・なのであれば、[年]列のデータも 2003年、2004年とするか、[年]、[月] 両方とも数字にしたいところです。 最終的には、Excel において日付のデータは「シリアル値」として持ちたいところです。テキストか、数字かの、どちらかに揃えておいて、最後にはシリアル値にしておきます。

今回は [年] のデータに文字列の「年」を加えて、2003 を 2003年 にしてみます。

[年] の列を選択し、[変換] タブの [テキストの列] グループの [書式] の [サフィックスの追加] を選択します。
ダイアログが表示されるので、追加する「年」を入力します。


[OK] を押すと、[年] の列のデータすべてに「年」の文字列が追加されます。かつ、この列のデータはアイコンが [ABC] と示すようにテキストとして認識されます。


(4) 新しい列を追加してシリアル値のデータを作成する


最後に、[年] と [月] の列のデータから、[年月] という、最終的に「シリアル値」になるデータ列を追加します。

[列の追加] タブの [カスタム列の追加] コマンドをクリックすると「カスタム列の追加」ダイアログが表示されます。

新しい列名には「年月」を入れ、カスタム列の式: には以下を入力します。

=Date.FromText([年]&[月])


[OK]を押すと、以下のように新しい列が追加され、年月のデータが作成されます。


年月日として「1日」が既定値になりました。データ型は日付です。

さきの「カスタム列の追加」で使った Date.FromText という関数は、Power Query Formula Language (PQFL) と呼ばれるものですが、別名称があり、「M言語」とも呼ばれます。

https://msdn.microsoft.com/en-us/library/mt211003.aspx

Date.FromText は日付を表す文字列から日付型のデータを作成するM言語の関数です。

Power Query / 取得と変換においてはこの「M言語」の知識が必要になる場面が出てきます。ただし、Excel のワークシート関数のような頻度で使うものではないと思います。(もしそうだとしたら、かなり苦行になります(笑) 理由は後ほど。)

これまでのクエリ エディターの拡張や、[詳細エディター]での入力支援の「無さ」から考えると、M言語を直接記述して何かやる、というより、リボンのコマンドを選択して、その順番を記録して、「適用したステップ」と fx の数式バーで修正する方向に行くような感じがします。

ここまでの作業で、データを取得して、必要な変換はすべて終わっています。
次回は、最後のほうで触れた M言語について紹介したいと思います。その後で、このデータを使って、訪日外客数のデータを利用した分析やレポートを作っていきたいですね。

では、次回をお楽しみに。

[PR] M言語(Power Query Formula Language)について書いている書籍はコレ!
 

2016/12/26

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

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

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

各年の複数ワークシートで構成されている訪日外客数のブックを取り込んだところまできました。
今の状態は、クロス集計表で表現されている「セルの範囲」が複数年、縦に連なっている状況です。第4回で説明したように、個々の列の情報は同じものという大前提があります。


ここから「不要な情報を取り除く」作業をする、または「必要な情報のみを残す」作業をします。
そのために最初はちょっと違和感があるかもしれませんが、この複数年のクロス集計が縦に連なったセル範囲を「テーブル」のように扱います。テーブルのように扱うことで、見出し行を追加し、「オートフィルター」機能が使えるようになります。そのフィルター機能を使って「不要な行」を取り除く、もしくは「必要な行だけを残す」操作をします。

(1) タイトル行のベースになる行を探して、タイトル行にする 


これらの操作は、現実のデータを見て検討するので、必ず以下で設定する内容であったり、順番であったりが正しいわけではないことに注意してください。扱うデータは「ナマモノ」であり、調理方法(加工方法)はさまざまです。その時その素材にあった方法を見つけるしかありません。

重要なのは、その操作が「再利用可能か?」を常に意識したほうが良い、ということです。この「再利用可能か?」は所々で解説したいと思います。

今回扱っている訪日外客のデータで、テーブルに変換したときにタイトル行になって欲しいのは、4月、5月、6月といった月であり、その行にあるべき(表示はされていませんが)国名などになります。

一切並べ替えをしないで取り込んだ直後のデータは以下のようなものです。


この時、最初の行は 2003 年の昔のデータなので、基本このワークシートは「変更されない」と「仮定」します。そうすると、「タイトル行」にしたいデータ行は4行目にあります。

このように「最初の3行はとりあえず使わない、要らない」というのがわかっているのであれば、「上位の行の削除」を使って、上から何行を削除する、という操作が可能です。


クエリ エディターの[ホーム]タブは、データを取り込んだ直後によく使う基礎的な機能/コマンドをまとめています。[行の管理]や[列の削減]のグループにはデータ取り込み直後に良く使う機能があり、その中に [行の削除] の [上位の行の削除] があります。

どの列が選択状態になっていても、ここでの指定は「行全部」となります。上位の行の3行を削除、という指定は、すべての列を含む3行を削除します。
また、今回の訪日外客のデータ展開で、第1列目の[Name]にはテーブル名に相当する 2003 などの年が「必ず」入っているので、列全てが null になる「空白行」は存在しません。そのため、(くどいですが)「今回の訪日外客のデータ」では全ての列が空白になる「空白行の削除」は該当しません。

また、この時点で当然疑問に思いますが、2003年のデータの[5月] や [6月]の行をこれから「先頭行」として使う設定を行いますが、2004年以降のデータの[5月]や[6月]は、データとして残っています。これはどうなるんだ?と思われるでしょう。これは、後ほどの作業で結果的に「消す」作業をします。

上位3行を消すと、 [5月] [伸び率] [4月]・・・のデータを含む行が1行目になるので、この行を [見出し行] として使います。そのまま [ホーム] タブの [変換] グループにある [先頭の行を見出しとして使用] をクリックして、1行目を見出し行(タイトル行)に変換します。


この変換により、重複した列名は許されない、もしくは必ず列名がなければいけませんから、[伸率]、[伸率_1] などの名前の自動変更が行われます。


ここからは、そのデータにあった対応を行っていきます。JNTO の訪日外客のデータの場合は、以下の方法で不要なデータを取り除くことができました。これらの方法や考え方は、他のデータでも十分に適用可能だと思います。

(2) フィルターをつかって不要な行を取り除く


今、この状態は、複数のワークシートを追加(Append)し、2003年のデータの上から3行分を削除し、4行目を見出し行にしたに過ぎません。不要なデータはまだ山のようにあります。以下はスクロールしていくと見つけることができる不要なデータの例です。(行が多いときはスクロールせずに、フィルターオプションから、重複しないデータの確認ができます)

「総数」や「アジア計」、「ヨーロッパ計」といった、小計、合計の行は必要ありません。

小計行もいりませんが、コメント・注釈行もいりません。

2003年の上位3行で消した表のタイトルは当然次の年のデータでは出てきます。これらの行は必要ありません。

もし、必要となるデータの国が決まっているのであれば、[Column2] のオートフィルダーで国の名前を直接指定するのも一つの方法です。以下であれば、Column2 に「中国」、「台湾」、「米国」、「韓国」、「香港」がある「行だけ」を選択して絞り込むことができます。


ただ、実際には国のデータは昔のデータにはなかった国のデータが追加されることも多く、必要な国を指定して、国を固定するより、不要なデータを削除するほうが「再利用性」が高いと言えます。今後どんな国が増えるかわからない、という状況は、「今後どんな製品が増えるかわからない」や、「今後どんな営業所が・・・」「今後どんなお客様が・・・」に通じるものがあると思います。

この場合に使いたいフィルターオプションは「指定の値を含まない」フィルターオプションです。


このテキストフィルター オプションを使えば、新しい国が入ってきても対応可能です。もちろん、「新しい不要な行」が入ってきたら、その対応をするのには変わりません。新しい不要な行と、新しい国のどちらが追加される可能性が高いかを実データから想定する必要はあります。

・漢字の「計」がある国名はない、という前提で「計」の文字を含まないものにする
>アジア計、ヨーロッパ計、アフリカ計、北アメリカ計など、地域の小計が排除される

・漢字の「注」がある国名はない、という前提で「注」の文字を含まないものにする
>注1:、注2の行が排除される。もちろん漢字1文字の「注」に問題がありそうならば、その行に含まれる「本資料」や「表中の」などを指定すると確実になります

・「総数」」を含む行を外す
>「2004年 訪日外客数(総数)」といったデータのタイトルを表す行と「総数」の計算行が排除される

・「出典:」を含む行を外す
>表の下にある「出典:日本政府観光局(JNTO)」を含む行が排除される

・「本表で」を含む行を外す
>「*本表で、通年の・・・」のコメント行が排除される

このような設定を繰り返して、すべての不要な行を削除します。確認はオートフィルターのリストを見るとが一番いいでしょう。


Column2 は「国」または「地域」のデータ列になったので、見出し行の [Column2] をダブルクリックすると編集モードになるので [国名] などに変更します。また、第1列目の [2003] は年々のデータかを表すので [年] に変更しておきます。


これで不要な行の削除が終わりました。
長くなりましたので、次回は、列について操作します。
お楽しみに。

[PR] M言語(Power Query Formula Language)について書いている書籍はコレ!
 

2016/12/20

[Power BI Desktop] エクセル複数ブックの取り込みが簡単になりました

データベースサーバーや、クラウドサービスをデータソースにするだけではなく、Power BI Desktop や Excel の取得と変換や Power Query は「Excel のブック」や「Excelのワークシート」をデータソースとして活用するシーンも十分に考えられます。

すでに、あるフォルダーにある複数の Excel ブックを取り込む方法を紹介してきましたが、もっと簡単になりました、というブログ記事が 2016年12月19日の Microsoft Power BI Blog にて公開されました。

Microsoft Power BI Blog - December 19, 2016 by The Power BI Team
Combining Excel Files hosted on a SharePoint folder with Power BI



原文は「SharePoint フォルダーの複数のExcelファイル(ブック)を Power BI で結合する」という内容です。SharePoint フォルダーを例にしていますが、SharePoint でなく、ローカルのフォルダなどでも同様の操作になります。

これまで、Excel.Workbook 関数を手で入力する方法を紹介してきたわけですが、それをせずとも、複数ブックを結合することができるようになりました。まだ、Power BI Desktop (2016/11版) でしか確認できていませんが、そのうち Excel の取得と変換や Power Query でも利用可能になるでしょう。

以下は、SharePoint フォルダーではなく、ローカルのフォルダーで試してみての結果です。

・ フォルダーを指定して [編集] ボタンでクエリ エディターを起動


クエリ エディターでは、フィルダーにあるブックの情報(属性)が表形式で表示されています。ここから、名前や拡張子などを使って「オートフィルター」のフィルターオプションと同様の操作で絞り込みました。

・ [バイナリの結合] ボタンで Binary からオブジェクトを取り出す

これまでは、[Content] 列にある [Binary] の中にあるオブジェクト(シートやテーブル)を取り出すために、カスタム列の追加と Excel.Workbook を使って、ブックの中にあるシートやテーブルを抜き出していましたが、今回の拡張から、[Content] 列のバイナリの結合ボタンを押すと、新しいダイアログウィンドウが表示されるようになりました。

[Content] 列にあるバイナリの結合ボタン
バイナリの結合ウィンドウ
この後の操作はこれまでのものと似ているのですが、ここで例えば「4月」のシートのアイコンを選ぶと、4月シートのみが展開されますが、「サンプル バイナリ パラメーター」のフォルダアイコンを選択して、[OK] を押すと、各ブックの Binary にあるシートやテーブルのオブジェクトが抜き出されます。

Data列が追加され、Binaryの中のTableが抜き出された状態
Data列の展開ボタンをクリックすることで、4月、5月、6月のワークシートのデータを結合し、展開された状態になります。

・ テーブルだと本当に楽です

ただし、テーブルでなく「範囲」だと、不要な行を削除したりする必要があります。もちろん、もとのブックに含まれるデータが「テーブル」であれば、不要な行に対する処理は必要ありません。

現時点では Excel 2016 の Office Insider スロー更新チャンネルの「取得と変換」では、上記の機能は確認できていませんが、近いうちに Excel でも利用可能になるでしょう。

2016/12/14

Excel BI - Power BI コミュニティ 登壇の報告

日本マイクロソフト株式会社さんで先月行われた Power BI コミュニティ 勉強会の模様が公開されていました。

Microsoft Japan Data Platform Tech Sales Team Blog
2016/11/26 Power BI 勉強会 – 第二回 が開催されました
https://blogs.msdn.microsoft.com/dataplatjp/2016/11/28/20161126-power-bi-community-2nd/

当日の様子(登壇しているのは私です)
Power BIまわりの情報提供はマイクロソフトさんもがんばってやっていらっしゃいますが、最近は Excel の視点からの情報が少なくなりつつあります。

その代り、Power Query が Excel の標準機能になるなど、Power BI と Excel の関係は変化・進化しているのが現状で、私もひとりで追いかけるのが非常に大変なのが実情です。

Power BI コミュニティ勉強会主催の かがたさんや、日本マイクロソフトの土井さん、コミュニティ勉強会に参加してくれた方と情報交換することが、情報の理解、キャッチアップに非常に役立っているのは疑う余地がないのが現状です。

上記の土井さんのブログには登壇者のパワーポイントも公開しているので、時間があれば Excel ユーザーでもぜひチェックしてほしい内容です。

2016/12/12

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

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

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

展開ボタンによるテーブルの追加
列の順序はクエリ エディターで最終形に修正することができます。

いかがでしょう。

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

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

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

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

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

長くなったので、データの展開以降は次回になります。お楽しみに。
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を返上し、アマゾン ウェブ サービス ジャパンに入社、コミュニティプログラム担当として現在に至る。