カテゴリー: IT

この記事は、週末プログラミングの一環です。

データ分析がブーム的に盛り上がっていますが、今でも Excelはビジネス現場での重要なツールですよね。

そして、あまり知られていませんが、マイクロソフトはどんどんExcelを進化させていて、データ分析しやすくしています。PowerBIというBIツールも提供していますしね。

無料でデータ分析するならMicrosoftのPower BI Desktopがおすすめ

そして今回紹介したいのは、データ分析を簡単にしてくれるPowerQuery(パワークエリ)というExcelアドイン機能です。これを知っておくと、データの加工処理が劇的に楽になります。

データを集計、加工するときの問題

大量にあるデータをクロス集計したりするためには、ピボットテーブルが便利です。

しかし問題は、ピボットテーブルを作る前の段階にあります。元のデータを綺麗に整形しなきゃいけないのです。。。

例えば、元のデータに余分な行が含まれていたり、複数のデータを組み合わせなきゃいけなかったり。データが加工しやすいきれいな状態で存在している、ということの方がレアですから。

データ分析作業の大半は、前処理の労力に占められるとも言われます。こんな本もあるぐらいです。

この前処理をいかに効率的に行うかが、データ分析の生産性に大きく左右されるのです。

データの取り込みと加工はPowerQueryを使う

そして本題に入りますが、その前処理を簡単にしてくれるのが、Power Queryです。

この機能をExcelで使う場合は、Officeのバージョンに注意してください。Office2016では普通に入ってます。それ以前のバージョンの方はアドインをインストールすると使えるようになります。

手抜きで申し訳ありませんが、導入方法や基本的な使い方はこちらをご覧ください。

ExcelでPower Queryを使い始めるには – Qiita

ExcelでPowerQueryを使ってデータ収集分析 – Qiita

PowerQueryで一番良いところはピボット解除ができる

データを集計、加工したい場合、クロス集計データであれば単純集計に変換した方が良いです。その方がデータをいろいろ加工しやすくなりますよね。

クロス集計の例

↓

単純集計に変換

このクロス集計→単純集計への変換が、Excelでは結構面倒です。でも、PowerQueryであれば簡単にできてしまいます。

①データを一度読み込む
②読み込んだデータの対象の列を選択して、「ピボット列の解除」を行う
③列を選択して、右クリックから列を解除

これで以上です。これだけでリスト形式のデータが出来上がりました。最初これを知った時はほんとに感動しましたよ。

データに空白がある場合は注意が必要

データが空白の場合は、ピボット解除のときに消えるので注意してください。その場合は、先に空白のデータを適当なデータで埋めて、ビボット解除した後もう一度置換すれば良いです。

Solved: Re: Unpivot removes rows with no/null values – how… – Microsoft Power BI Community

PowerQueryは他にもいいところがたくさんあります。

• 膨大なデータを軽い動作で取り込めること
• WebやExcel、CSVなど多様なデータを取り込めること
• 取り込むデータを絞り込めること
• 取り込むときにデータを好きなように加工できること
• 加工したデータの変換方法を覚えておけること

Excelでのデータ処理を効率化したい人には、ぜひ試して欲しいですね。

PowerQueryを勉強するには

PowerQueryの情報は日本語だと少ないのですが、英語だとあります。

まとまって勉強するなら、Udemyのこちらのコースがおすすめです。英語ですが、字幕も出るので比較的わかりやすいですよ。英語の勉強も兼ねて、どうでしょう。

Microsoft Excel – Excel Power Query, Power Pivot & DAX

本だとこちらですかね。

ちなみに、PowerQueryは最新のExcelであれば統合されているので、すぐに使うことができます。

週末プログラミングとしてやったことを書いておきます。

Google Apps ScriptでLINE botを作れたので、GASを勉強中です。

GoogleAppsScriptとLINE Botで自分用の「文字起こし君」を作ってみた

スプレッドシートとGoogle Apps Scriptを組み合わせることができるのも、GASのメリットです。

スプレッドシートで作成したグラフを操作してみます。

グラフを取得して画像を保存する

まず、スプレッドシートにすでにグラフがあるとします。それをGASで取得して、Googleドライブに保存します。

これも先駆者がいますので、真似るだけでした。

Google Apps ScriptでスプレッドシートのグラフをGoogleドライブに保存する

上記のスクリプトを実行するだけで、スプレッドシート上にあるグラフが、問題なく同じフォルダに保存されました。

GASでグラフの軸の範囲を修正する

ただ、GASを通してグラフを取得すると、スプレッドシート上の設定がリセットされて、グラフの軸がデフォルト状態に戻ってしまうようです。

これは困りました。グラフの原点は揃えるのが重要です。原点が変動してしまうと、視覚的に誤解を生んでしまう可能性が出てきます。

データ視覚化のポイント#7
グラフの基本中の基本、棒グラフの原点は0にする。

棒グラフは視覚的な比較を棒の面積に依存しているので、データの差を際立たせようとして原点をいじってしまうと嘘グラフになる。

印象操作と取られると信頼を失ってしまう事もあるので注意が必要。#goando_datasketchpic.twitter.com/8Mo0lNEu6h

— Go Ando / THE GUILD (@goando) 2018年5月23日

ので、こちらを参考に、取得したグラフを再度変更することにしました。

GASで棒、円、折れ線など各種グラフを作成、変更、削除するための基本 (1/2)：Excel VBAプログラマーのためのGoogle Apps Script入門（8） – ＠IT

グラフのオプション指定として、

.setOption(‘vAxis.viewWindow.min’, 0)

とすれば、Ｙ軸をゼロにできます。

Google Spreadsheet, Google Drive, Google Apps Scriptでレポーティングの簡単自動化 – Reports

それで軸の開始をゼロにすることができました。

が、2系列使っている場合、両方の軸がゼロにセットされてしまいました。2系列の場合は、どちらのシリーズであるかを明確に指定する必要があります。こちらの記事に、系列ごとの指定が書いてありました。

こんな感じです。

.setOption(‘vAxes’, {0: {title: ‘€’, format: ‘short’, textStyle: {fontSize: 10}, titleTextStyle: {fontSize : 8}, viewWindow: {min: 79000000}}})

How do I change the number format of the vertical axis? (when using the EmbeddedChartBuilder in Apps script spreadsheet service) – Stack Overflow

これでグラフの修正が完了です。

GASは簡単なスクリプトで作成できること、Googleドライブと連携できること、ウェブサーバーに使えることが良いですね。

いろいろデータ分析していると、売上予測などしたくなりますよね。で、とても簡単にできる方法があるので、データ分析クラスターは知っておいて損はないんじゃないかと。

Facebookが、比較的簡単に時系列分析ができるオープンソースを公開していて、名前は「Prophet」といいます。

Prophet | Prophet is a forecasting procedure implemented in R and Python. It is fast and provides completely automated forecasts that can be tuned by hand by data scientists and analysts.

PythonとR言語でパッケージが公開されており、誰でも使うことができます。

使い方は先程の公式サイトにコマンドが載っていますが、英語ではとっつきにくいという方はこちらのSlideShareのプレゼンテーションが分かりやすいと思います。ここではR用を貼っておきますが、Pythonバージョンもあります。

時系列分析の難しさは、いろいろ試して思いました。最初に試したのは、こちらの本を読んでから。

ただ、いろんな時系列モデルを知って、誤差を評価したり云々など、専門知識が求めらるし、モデルを構築するまでちょっと大変で、頑張って計算してみたけど思ったような数字が得られなかったということもありました。

奥が深いだけあって、簡単に予測の数字を作るのが難しいんですよね。

でも、Prophetはほんの数行でそれっぽく精度が良い時系列予測をしてくれます。デフォルトで使いながら、オプションで予測モデルをチューニングすることもできます。

専門的な知識をそれほど必要せず、PythonかRをちょっとかじったことがある人なら、時系列予測でそれっぽい数字が出ます。それがこのパッケージの特徴です。

ビジネスの現場で思うのは、そりゃ精度が高い予測モデルがあれば越したことはありませんが、ベンチマークとなる「それっぽい」数字があるだけでも、組織の取り組み意識が変わったり、新しい改善策が思い浮かんだりします。

実際これを使って、季節性をグラフでちゃんと捉えたり、翌月の売上金額がどの程度かを議論することができた事例もあります。

Prophetで導き出される数字は、ある程度の時系列データがあれば、それなりに出る印象はあります。これを数字として持っておくと、今後の予測を組織内で議論するきっかけとして使うことができるでしょう。

そして何より大きいのは、ほんの数行で予測データを得られることです。

要はこういうツールは使い方次第ですし、Prophetはさくっと「それっぽい」予測をしてみる分には非常に使えるんじゃないかと思います。

Power BIは、複数のデータソースと連携しやすいのが特徴ですが、その一つにウェブからのデータ取得があります。

これを使うと、ウェブサイト上にあるデータを簡単に取得し、データ分析に活用することができるようになります。

今回は、WikipediaからJリーグのクラブ成績を取得して、データ分析をしたいと思います。

J1リーグ – Wikipedia

Webからのデータ取得はこんなに簡単

まず「Get Data」で「Web」を選択します。

URLを入力すると、そのページ内にあるテーブルデータの一覧が候補として抽出されます。もうこの時点で非常に便利ですね。テーブル一覧から取得したい対象のデータを選択し、必要に応じてデータを加工します。

もうこれだけでデータの取得を完了してしまいました。

後は自由にデータを使って分析をしましょう。

今回取得した、J1クラブの通算成績を使って、各クラブの滞在期間をみてみます。

オリジナル、と言われるクラブの中で、いまだにJ2の降格経験がないのは横浜Fマリノスと鹿島アントラーズの2チームです。そのあと、ガンバ大阪、名古屋グランパス、浦和レッズ、清水エスパレスと続きます。

このグラフを作るまで、5分もかかっていません。URLを指定して、データを少し加工したら、あとはグラフにデータをプロットしておしまいです。

Power BIって本当便利ですね。

Power BIを本格的に勉強するなら、Udemyがおすすめです。英語にはなりますが、動画で字幕つければ結構わかります。日本語だと情報も少ないですし。

Microsoft Power BI Masterclass – Expand Excellence

BIツールの1つのメリットは、分析したいデータを、ドリルダウンやドリルアップなどいろんな観点から自在に確認できることでしょう。

例えば時系列データであれば、最初に年次単位で大きなトレンドを確認し、気になるところがあれば四半期単位や月次単位で確認する、といった具合です。

ただし、時系列データをドリルダウン分析するためには、1つのテクニックが必要になります。それが「カレンダーテーブル」を作っておくことです。

カレンダーテーブルを作る目的

時系列データを分析したいときに、元データには日付単位のものがあるとしましょう。日次の売上データなどをイメージすれば良いです。

グルーピングしたい単位を予め自由に定義できる

四半期毎や年単位、あるいは会計年度などでグルーピングしたい場合、それらの時系列のグルーピングを、まとめて定義するのがカレンダーテーブルです。

また、会計年度の定義にも便利です。企業によって決算月は異なりますし、集計単位を変えたい場合もあるでしょう。その場合に、カレンダーテーブルで定義しておけば、決算年度ごとに集計することが簡単にできるようになります。

複数のデータでも共通の時系列単位を定義できる

カレンダーテーブルを作っておくことで、各データごとに日付のグルーピングをする必要がなくなります（売上データ、仕入データなどの複数データを、共通の時系列単位で分析できる）。カレンダーテーブルと、各データの日付データとのリレーションを構築するだけでOKです。

カレンダーテーブルの作り方

基本的には、Microsoftが紹介しているこちらのリンクを見ていただければと思いますが、

[Power BI] DAX入門(2) カレンダーテーブルの作成 – Microsoft Japan Data Platform Tech Sales Team Blog

主要なものだけ示しておきます。

カレンダーの作り方
カレンダー = CALENDAR(“2015/1/1″, TODAY())

年
年 = YEAR([Date])

月番号
月番号 = MONTH([Date])

四半期
四半期 = IF([月番号]<4,”Q4″,IF([月番号]<7,”Q1″,IF([月番号]<10,”Q2″,”Q3”)))

会計年度
会計年度 = IF([月番号]<4,[年]-1,[年])

あとは、作成したカレンダーテーブルと、分析したいデータの日付カラムを、リレーションで結んでおきます。リレーションの構築方法は、こちらの記事をご覧ください。

Power BI Desktop でのリレーションシップの作成と管理 – Power BI | Microsoft Docs

例

手頃な日次データとして、日銀の統計データからドル円レートの推移を使います。

主要時系列統計データ表

データを読み込んで、カレンダーテーブルを作ります。そのあとは、忘れずに日銀データとカレンダーテーブルで、日付カラムのリレーションを構築しておきましょう。

そして、実際にグラフを表示してみましょう。

軸には日付を設定するのですが、このときにカレンダーテーブルの項目を設定することと、階層ごとに順番に設定することが肝です。

年単位でグラフ化したのがこちら。

このうち、どこかの年を選択してドリルダウンを行うと、月単位のグラフに変化します。

さらに月を選択してドリルダウンを行うと、日単位のグラフに変化します。

ドリルアップを選べば、上の階層に戻すこともできます。

このように、カレンダーテーブルを作っておけば、自由に時系列データの階層をいったりきたりすることができるのです。時系列データを分析するには必須といえるでしょう。

Power BIを勉強するなら、Udemyがおすすめです。日本語だと情報が少ないですし、Udemyであれば情報がまとまっているので、効率的に学べます。

Microsoft Power BI Masterclass – Expand Excellence

Excel　＆　Power　BIによる企業データ分析入門

posted with ヨメレバ

平井明夫/内野明彦 日経BP 2014年07月

Amazon

Kindle