コラム

モダンExcelの主要機能であるPower PivotとPower Queryの使い方は?

  • このエントリーをはてなブックマークに追加
  • Pocket
  • LINEで送る
モダンExcelの主要機能

以前、モダンExcelとはどのような機能なのかについて紹介しました。非常に便利な機能であり、スキルを身につけると、企業が保有しているデータの整理・分析やグラフによる可視化などさまざまな用途に役立てることができます。

モダンExcelの柱となる2つの機能が「Power Query(パワークエリ)」と「Power Pivot(パワーピボット)」です。そこで、Power QueryとPower Pivotの概要や使い方について紹介したいと思います。

▼ 目次
1.モダンExcelについて
 ・モダンExcelの主要な機能について
 Power PivotやPower Queryが使えるExcelのバージョンは?
 ・VBAとの違い
 ・Power BIとの違い
2.Power Queryとは
 ・Power Queryの使い方
3.Power Pivotとは
 ・Power Pivotの使い方
 ・ピボットグラフの作成
 ・リレーションシップの活用
4.ダッシュボードの活用
5.まとめ

モダンExcelについて

モダンExcelを一言で述べると、高度なデータ処理機能を搭載したMicrosoft Excelの新機能のことです。

Microsoft Excelはもともと表計算アプリで、スケジュール表や家計簿などを作成することができます。従来のExcelでも、Excel関数でデータ処理を行ったり、VBA(Visual Basic Applications)for Excelで自動化したりすることが可能でした。ただし、Excel関数やプログラミング言語を覚えないと高度な処理を行えない点がボトルネックでした。

こうした従来のExcel(レガシーExcel)に対し、より直感的な操作でデータ処理や可視化を行えるようになったのがモダンExcelのメリットであり、それを実現するのがPower QueryやPower Pivotという機能です。

モダンExcelの主要な機能について

Microsoft Office 2013以降のExcelでは、外部からデータを取り込み、簡単なデータベースとして使用したり、データを適切に分析したり、グラフによってデータを可視化したりすることが可能です。

Microsoft Office 2013以降のExcelで登場した新機能は以下の3つです。

  • Power Pivot…Excelにデータを取り込む
  • Power Query…取り込んだデータを分析
  • Power View…データ分析にもとづきグラフで見える化

このうち、データを可視化したグラフを閲覧する「Power View」は2021年10月12日にサポート終了し、Power BI Desktopに移行しました。後述するようにPower BIはデータを可視化したりダッシュボードを作成したりするアプリケーションであり、Power BI Desktopは無料配布されているバージョンです。ただし、Power BIがなくてもモダンExcelだけでデータの可視化やダッシュボードの作成は可能です。

本記事では、Power QueryとPower Pivot、可視化したデータを表示するダッシュボードの特徴について解説していきます。

Power PivotやPower Queryが使えるExcelのバージョンは?

すべてのバージョンのExcelがモダンExcelの機能を使えるのではありません。Power PivotやPower Queryが使えるのは、次で示すバージョンのExcelです。

  • Microsoft 365
  • Office Professional 2021
  • Office Home & Business 2021
  • Office Home & Student 2021
  • Office Home & Business 2019
  • Office Home & Student 2019
  • Office 2016 Professional Plus
  • Office 2013 Professional Plus
  • Excel 2013
  • Excel 2016

Office for MacやOffice for AndroidではPower QueryやPower Pivotが使えませんので、注意が必要です。

VBAとの違い

これまで業務用にExcelを使用してきた方は、「VBAだけでモダンExcelと同等のことが行えるのではないか?」とお思いかもしれません。

VBA(Visual Basic for Applications)はMS Officeの拡張機能として提供しているプログラミング言語であり、Visual Basicと同等の機能を持っている本格的な言語です。たとえば、繰り返し使用する処理やよく使用する作業を「マクロ」化し、いつでも呼び出して使えるようになります。これにより、ドキュメント作成やデータ整理を効率化することが可能です。

もちろんVBAを使って、データからグラフを作成することも可能です。しかしVBAだけでデータからグラフを作成するのはかなり大変です。Visual Basicと同等の機能を持つプログラミング言語であるため、学習コストも高くついてしまいます。

これに対して、Power PivotやPower Queryはデータ処理に特化した機能です。Excelからメニューを操作するだけでも簡単なデータ処理やグラフ化ができるという意味では、学習コストは低いと言えるでしょう。

もちろんモダンExcelでも、テーブルやセル内の値を操作する「DAX関数」やPower Query専用のプログラミング言語「M言語」など覚えておいたほうが便利なツールはあります。しかし、ある程度は直感的な操作だけでデータ処理やグラフ化が行えます。そのため、初心者の方はPower PivotやPower Queryの直感的な操作を覚えることから始め、VBAなど業務である程度Excelの操作を慣れている方は、DAX関数やM言語などをマスターし、モダンExcelの高度な機能を使えるようにステップアップすると良いでしょう。

Power BIとの違い

上で述べたように、Power BIもまたインポートしたデータを可視化したりダッシュボードを作成したりするためのツールです。Power BI DesktopはPower BIのラインナップの1つであり、無償で提供されています。

Power BIで作成したレポートのサンプル
図1:Power BIで作成したレポートのサンプル

ただしモダンExcelでもPower QueryやPower Pivotを使って、データを可視化することができます。またダッシュボードをExcelで作成でき、共有することも可能です。

Power BIとモダンExcelの違いとして、ダッシュボードを共有できるかどうかが挙げられます。データを共有するためには、Power BI Proなど有償版を手に入れる必要があります。一方モダンExcelの場合には、通常のOfficeのファイル同様、アクセス許可した方とダッシュボードを共有することが可能です。

そのため、企業や学校で手の込んだダッシュボードを共有したいという方はPower BIをマスターし、ちょっとしたグラフやダッシュボードを作ってみたいという方はモダンExcelを利用すると良いのではないでしょうか。なおPower BIでもDAX関数など高度な処理を行うことが可能ですので、モダンExcelの機能を学ぶことはけっして無駄ではありません。

参考:Microsoftが提供するBIツール「Power BI」とは

Power Queryとは

以前にも解説したように、Power Queryとはさまざまなデータ構造を内包するテキストファイルをインポートし、簡単なデータベースを作成できる機能です。Power Queryは一般的に使用されるデータ形式に対応しています。

  • CSVファイル
  • XMLファイル
  • JSON形式のファイル
  • Excelのテーブル
  • Webページの表

Power Queryでこうした構造化されたデータをインポートし、操作しやすい表形式にしてくれる「テーブル」の作成や編集を行うことが可能です。

Power Queryの使い方

Power Queryは、Excel2013以降のExcelに標準搭載されています。今回、Office Home & Business 2019に付属するExcel を使って解説します。バージョンによって操作方法に多少違いがありますが、本質的にほぼ変わりません。

実際、外部からデータをインポートしてみましょう。まずExcelを立ち上げ、「データ」メニューを選択すると、外部からデータをインポートできるメニューが表示されます。

「データ」メニューから外部データをインポートできる
図2 :「データ」メニューから外部データをインポートできる

例として、気象庁のホームページから、2021年の東京の平均気温のデータをインポートします。CSVファイルをダウンロードしExcelに取り込むと、自動的にテーブルが作成されます。

データをインポートしたあとに作成されたテーブル
図3:データをインポートしたあとに作成されたテーブル

ただし、このままでは空白部分や不要なデータが含まれているので、Power Pivotでデータを分析できません。そのため、テーブルを整形する必要があります。

不要なデータを取り除くため、Power Queryエディタから「クエリ」を編集します。

Power Queryエディタでテーブルを編集
図4:Power Queryエディタでテーブルを編集

不要な行と列を削除し、編集完了すると、整形されたテーブルが表示されます。なお「適用したステップ」で操作を確認できます。間違った操作をしても、クリックするだけでクエリの状態を操作前に戻すことが可能です。

Power Queryエディタで編集後のテーブル
図5:Power Queryエディタで編集後のテーブル

このようにPower Queryはデータを取り込み、テーブルを整形するなど、データ分析の前処理に使用する機能だと位置づけられます。

Power Pivotとは

Power Pivotとは、Excelにインポートしたデータを分析する機能です。テーブルから、行と列をラベルとするクロス集計表などを作成することが可能です。また、テーブル同士を結合することでデータモデルを拡張したり、データ分析をもとにグラフを作成し直感的に理解しやすい形にしたりすることも可能です。

Power Pivotの使い方

Power Pivotを使用するためには、ExcelからPower Pivot for Excelというアドインを追加する必要があります。

「オプション」メニューを開き、「アドイン」を選択。「管理」から「COMアドイン」を選択すると、「Power Pivot for Excel」などCOMアドインが表示されるので、チェックを入れ有効化します。

Power Pivot for Excelのインストール①
図6:Power Pivot for Excelのインストール①
Power Pivot for Excelのインストール②
図7:Power Pivot for Excelのインストール②
Power Pivot for Excelのインストール③
図8:Power Pivot for Excelのインストール③

すると、「Power Pivot」というメニューが登場します。

では実際にデータをインポートし、テーブルを分析してみましょう。サンプルデータとして、次の売上データを考えてみましょう。

顧客性別年齢品目
S1M42ラーメン
S2F36オムライス
S3F24ケーキ
S4M60カレー
S5M51パスタ
S1M42コーヒー
S5F19ケーキ
表9:売上データ
品目価格
ラーメン700
オムライス800
ケーキ400
カレー650
コーヒー300
パスタ700
表10:価格表

まず、Power Queryで売上データからテーブルを作成しましょう。

売上データからテーブルを作成
図11:売上データからテーブルを作成

売上データを示すテーブルから、クロス集計表を作成してみます。「ピボットテーブルのフィールド」から行と列のラベルを選択すると、顧客と各品目の注文数を表すクロス集計表が作成されます。

テーブルからピボットテーブルを作成。クロス集計表が出力される
図12:テーブルからピボットテーブルを作成。クロス集計表が出力される

なおPower Pivotには、テーブルを自動的に分析して、おすすめのピボットテーブルを提示してくれる機能もあります。

おすすめのピボットテーブルが自動的に作成される
図13:おすすめのピボットテーブルが自動的に作成される

ピボットグラフの作成

Power Pivotの分析結果をもとに、「ピボットグラフ」を作成することも可能です。たとえば、上で作成したクロス集計表からピボットグラフを作成してみましょう。各顧客が何を注文したかを可視化することができます。

ピボットテーブルからピボットグラフを作成
図14:ピボットテーブルからピボットグラフを作成

なお、棒グラフだけでなく、折れ線グラフや散布図など、さまざまなグラフを選択することが可能です。

リレーションシップの活用

Power Pivotを語るうえで欠かせない機能が「リレーションシップ」です。複数のテーブルを連携してデータモデルを拡張することができます。たとえば、2つのテーブルの項目を結びつけることが可能です。

異なるテーブルを「リレーションシップ」で結びつける
図15:異なるテーブルを「リレーションシップ」で結びつける

テーブル同士の結びつきを示すダイヤグラムは、Power Pivotから確認できます。

テーブル同士を結びつけたダイヤグラムを表示
図16:テーブル同士を結びつけたダイヤグラムを表示

データモデルを拡張することで別のピボットグラフが作成でき、新たなデータ分析の可能性が拡がるでしょう。

ダッシュボードの活用

先述したように、Excelの機能だけでもPower BIのようなダッシュボードを作成することができます。Excelのシートにピボットテーブルやピボットグラフを張り付けるだけです。たとえば、Microsoftのサイトには次のようなダッシュボードの見本が載っています。

Excelで作成したダッシュボードの例
図17:Excelで作成したダッシュボードの例

参照:Excel でダッシュボードを作成し、Microsoft グループで共有する

Excelで作成したファイルを共有できますので、アクセス許可をした方なら誰でも閲覧することが可能です。

まとめ

今回、モダンExcelの主要な機能であるPower QueryとPower Pivotについて紹介しました。直感的な操作だけでも、インポートしたデータを分析し可視化できることを何となく理解できたのではないでしょうか。

モダンExcelの世界は奥深く、Excelの従来から搭載されているDAX(Data Analysis Expressions)関数M言語など、データを分析するうえの必要となる指標を覚えることで、より詳細なダッシュボードの作成が可能になります。詳しくは、モダンExcelやPower Query、Power Pivotなどを取り扱った初心者向けの本をご確認ください。

弊社のブログでは、ExcelやPower BIなど、ビジネスを効率化するうえで欠かせないツールの紹介を続けていきたいと思います。

  • このエントリーをはてなブックマークに追加
  • Pocket
  • LINEで送る

お知らせ

お知らせです
%d人のブロガーが「いいね」をつけました。