Synapse Diary

Excelでピボットテーブル使う人に全力でPowerQuery(パワークエリ)をお勧めしたい

mcmurryjulie / Pixabay

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

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

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

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

 

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

 

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

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

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

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

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

 

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

 

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

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

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

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

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

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

 

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

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

クロス集計の例

データ 国A 国B 国 C
人口 100 200 300
面積 10 20 30
GDP 50 100 300

単純集計に変換

データ
人口 国A 100
人口 国B 200
人口 国C 300
面積 国A 10
面積 国B 20
面積 国C 30
GDP 国A 50
GDP 国B 100
GDP 国C 300

 

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

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

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

 

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

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

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

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

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

 

PowerQueryを勉強するには

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

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

Microsoft Excel – Excel Power Query, Power Pivot & DAX

 

本だとこちらですかね。

 

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

モバイルバージョンを終了