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

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

データ分析がブーム的に盛り上がっていますが、今でも 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
人口100200300
面積102030
GDP50100300

単純集計に変換

データ
人口国A100
人口国B200
人口国C300
面積国A10
面積国B20
面積国C30
GDP国A50
GDP国B100
GDP国C300

 

このクロス集計→単純集計への変換が、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であれば統合されているので、すぐに使うことができます。