「PowerQueryとPowerPivotってどのような違いがあるのかな？」
「ExcelでPowerPivotという機能があるようだけど、どんなことが出来るのかな？気になってネットで調べてみたら、画面の中に表やグラフがたくさん並んで・・・。とても便利そうなので、作ってみたいけど作り方が分からない。」
- PowerPivotと PowerQueryの違いを知りたい方
- PowerPivotというツールがあることを知っているけれども使い方を知らない方
こちらの方々に限定して今回の記事を書きました。
もし、この記事を目にされた方がPowerPivotを使ったことが無い方でしたら、まずは過去にPowerQueryについて記載した記事をご覧になってこちらに戻ってください。
PowerQueryとPowerPivotをある程度使ったことのある方は、ここからでも大丈夫です。
この記事を書いている私は、PowerPivotと約1年間向き合い、社内で分析ツールを作り続けてきました。
PowerPivotは本当に今までのExcelの使い方を根本から変えてくれる素晴らしいツールだと感じています。
やっとPowerPivotの脱初心者になりつつある今こそ、PowerPivotについて未経験者の方々に分かり易く解説できます。
PowerPivotとPowerQueryの違い
PowerPivotを正しく理解するためには、PowerQueryとの違いを知っておかなければなりません。
一言で両者の違いを言うと、「前工程がPowerQuery、後工程がPowerPivot」です。
PowerQueryは「①取込み」から「②加工」までを行い、データモデルを作ります。PowerPivotは、PowerQueryで加工されたデータモデルを「③集計」し、「④見える化」するための機能になります。
Excelにはデータを分かり易く見せるための機能が充実しています。
ピボットテーブルやピボットグラフが代表的なものです。
これらの機能は、PowerPivotが標準装備されたExcel2016から更に進歩しています。
つまり、PowerPivotは「進化したピボットテーブルやピボットグラフ」であるとも言えます。
他にも、スライサー・条件付き書式・スパークラインなどのExcelのデータを見やすくする機能を組み合わせて使います。
参考に動画付きツイートを添付しておきます。
PowerPivotの三大特長
PowerPivotの3大特長として、以下のようなものが挙げられます。
- メジャーを作る(DAX)
- スライサーで操作性向上
- 複数の表とグラフを連動
今回は、e-STAT(政府の統計窓口)の「業種別の民間給与実態統計」からダウンロードしたデータを使ってPowerPivotの特長を紹介します。
ダウンロードする時にファイル形式を選択できるのですが、よりデータベース的な加工をしやすい「列志向形式」を選択しました。
メジャーを作る(DAX)
メジャーというのは、Excelではピポットテーブルとピボットグラフで使える独自の集計方法です。
通常のピボットテーブルでは、
- 数値を合計した場合に「合計/」などの余計な文字が入る
- 書式設定が更新によってリセットされる
- ピボットテーブル外に関数をセットすると更新時に手直しが必要
などの問題があります。
メジャーを使うと、これらの問題をすべて解決できます。
通常のピボットテーブルの場合は、下のようにデータを表示する列の見出しが冗長になります。
書式設定はセル上で行っても、データを更新する時に元に戻ってしまいます。
メジャーを作った場合は、このように見出しをスッキリさせることができます。
メジャー自体に書式設定出来ますので、更新しても元に戻りません。
更に、桁数が多いと見づらいので、人数を千人単位にしています。
メジャーを設定するためには、データモデルを設定する必要があります。
データモデルの設定については、前回の記事に記載しています。
ピボットテーブルのフィールドリストで、データモデルを右クリックすると、「メジャーの追加」というメニューが表示されます。
メジャーの設定画面で、見出し名・関数・書式設定を設定することができます。
ここでは、メジャーを使って合計を設定してみました。
桁数が多い場合は、1000で割って千単位で表示できます。
書式設定では、3桁区切り、小数点の桁数、パーセントなどの設定が出来ます。
設定したメジャーをピボットテーブルに設定すると、設定した見出し名がそのまま表示されます。
数値の表示も桁区切りで表示されます。
メジャーの機能で特に重宝するのが、割り算で係数を表示させる場合です。
ここでは、所得を人数で割った平均給与を計算してみます。
展開や折りたたみを行っても、項目に合わせて自動で計算してくれます。
Excelのワークシート関数を使って表を作る場合は、表示したいセル全てに関数を入れてあげないといけません。
Accessのクエリは、関数で列全体を計算してくれますが、折りたたんでの自動集計まではしてくれません。
色々な集計を行い、多角的にデータを分析したい方にとって、メジャーはとても強い味方になります。
メジャーで設定できる関数は、DAX(Data Analysis Expressions)と呼ばれています。
DAXを駆使すると様々な計算が出来るのですが、足し算と割り算のみでもメジャーの便利さを十分に体感できます。
スライサーで操作性向上
PowerPivotを使った表やグラフは、スライサーとの相性がとても良いです。
スライサーのボタンを選択することで、素早くデータの絞り込むことが出来ます。
フィルターの場合は、「▼」を選んで項目を選択し直さないといけませんが、スライサーはボタンのクリックのみなので、格段に効率が向上します。
スライサーについてはこちらで解説しています。
複数の表とグラフを連動
パワーピボットを使う人が知っておきたいスライサーの設定は、複数の表やグラフと連動できることです。
さっそく他のピボットグラフと連動してみましょう。
スライサーを右クリックして、レポートの接続を選択します。
連動させたいものにチェックを入れます。
これで複数の表やグラフを１つのスライサーで連動させることが出来ます。
PowerPivotを使う時の3つの注意点
- Excelのバージョン
- 更新作業が必要
- パソコンのスペック
Excelのバージョン
PowerPivotはExcel2016から標準装備されています。
Excel2010と2013はアドインを別に入れることである程度使えるようになりますが、完全ではないので複数のバージョンを併用している職場では事前に確認しておきましょう。
Excel2013でも「Excel単体」か「Office Professional Plus版」を使っている場合は、PowerPivotアドインをインストールできます。
一方、他のOfficeバージョン(Personal・Home & Business・Professional)はPower Pivotを使えませんので、注意が必要です。
更新作業が必要
入力データを追加した場合は、更新メニューをクリックする必要があります。
複数の表やグラフがあれば、別々に更新しないといけない場合もあります。
マクロで記録したVBAコードを使ってボタンで更新する方法も可能なのですが、メモリなどのリソース消費によってはエラーが発生します。
VBAを使っての更新については、何かいい方法があれば後日追記致します。
※後日、マクロのエラーが発生した時にデバッグモードで再実行したところ、正しく処理されました。更新毎にSLEEP関数などで遅延させるとエラーを回避できるかもしれません。
パソコンのスペック
PowerPivotは高性能な機能ですので、処理のためにはそれなりのリソースを必要とします。
パソコンのスペックが低いと、更新に時間がかかったり、フリーズやエラーによってExcelが閉じたりします。
PowerPivotを導入する前にCPUやメモリの設定を確認しておきましょう。
肌感覚ですが、Core-i3のメモリ8GB以上が目安になるかと思います。
メモリが不足しているなら、増設することで動作不良が解消されますのでいちどお試しください。
PowerPivotの学習方法について
- 日本語の解説本
- 海外のYouTube動画
- マイクロソフトの公式リファレンス
日本語の解説本
最近Power QueryやPowerPivotを解説しているサイトを見かけるようになりましたが、まだまだ普及しておらす、これから少しずつ認知される機能だと言えます。
解説のサイトもある程度操作出来る人向けに書かれていますので、やはり書籍を手元に置いておくのがマスターするための一番の近道でしょう。
下の記事ではデータ分析関連の良書を紹介していますので、参考にご覧ください。
Power QueryやPower Pivotを詳しく解説しています。
海外のYouTube動画
動画はやはりわかりやすい学習教材だと言えます。
書籍からある程度マスターできたら、海外のYouTube動画を参考にしてみるのがおすすめです。
特に英語圏の解説動画は、日本より進歩しているので得られることが大きいです。
聞き取れなくても、操作手順を動画で確認することができます。
とくにこちらの2チャンネルがおすすめですので、興味のある方は是非ごらんください。
マイクロソフトの公式リファレンス
DAXには、いろいろな関数があります。
マイクロソフトの公式リファレンスでは、解説書籍に無い使われ方が掲載されています。
DAXに興味のある方は是非こちらをどうぞ。
まとめ
PowerPivotはExcelのなかでも新しい機能になりますので、これからますます普及が進むことでしょう。
従来の機能に比べて便利なところはたくさんありますので、マスターしておくと日常のデータ分析で大きく役立ちます。
これからは、業種や職種を問わず「データを様々な角度から加工して価値を見出せる」能力が特に求められます。
また、最近ではプログラミング不要でアプリを開発出来る「ノーコードツール」が進歩しています。
特に、Bubbleというプラットフォームが2020年現在では話題になっています。
PowerQeryやPowerPivotなどのExcelで使える分析ツールは、正しくノーコードツールであると言えます。
Excelのノーコード機能を十分に使えれば、他のノーコードツールが必要になった場合でも、スムーズに習得できるでしょう。
PowerPivotをマスターするためには、書籍代や学習するための時間が必要になりますが、投下した時間や費用以上のリターンを得られます。
実際に私も書籍を購入して学習してきましたが、新しく作るデータ分析ツールは全てPowerPivotです。
社内でPowerPivotを使用してくれている同僚からも評価は上々です。
最後に、ブログでは他のExcelの機能についても解説しています。
よかったらトップページから希望の機能を探してみてください。
また、Excelでデータ加工や分析するためのまとめ記事もありますので、そちらもごらんください。
≫ 正確に手早く集計！Excelのデータ加工・分析テクををまとめました