2023年6月19日
以前、モダンExcelとはどのような機能なのかについて紹介しました。非常に便利な機能であり、スキルを身につけると、企業が保有しているデータの整理・分析やグラフによる可視化などさまざまな用途に役立てることができます。
モダンExcelの柱となる2つの機能が「Power Query（パワークエリ）」と「Power Pivot（パワーピボット）」です。そこで、Power QueryとPower Pivotの概要や使い方について紹介したいと思います。
目次
モダンExcelを一言で述べると、高度なデータ処理機能を搭載したMicrosoft Excelの新機能のことです。
Microsoft Excelはもともと表計算アプリで、スケジュール表や家計簿などを作成することができます。従来のExcelでも、Excel関数でデータ処理を行ったり、VBA（Visual Basic Applications）for Excelで自動化したりすることが可能でした。ただし、Excel関数やプログラミング言語を覚えないと高度な処理を行えない点がボトルネックでした。
こうした従来のExcel（レガシーExcel）に対し、より直感的な操作でデータ処理や可視化を行えるようになったのがモダンExcelのメリットであり、それを実現するのがPower QueryやPower Pivotという機能です。
Microsoft Office 2013以降のExcelでは、外部からデータを取り込み、簡単なデータベースとして使用したり、データを適切に分析したり、グラフによってデータを可視化したりすることが可能です。
Microsoft Office 2013以降のExcelで登場した新機能は以下の3つです。
このうち、データを可視化したグラフを閲覧する「Power View」は2021年10月12日にサポート終了し、Power BI Desktopに移行しました。後述するようにPower BIはデータを可視化したりダッシュボードを作成したりするアプリケーションであり、Power BI Desktopは無料配布されているバージョンです。ただし、Power BIがなくてもモダンExcelだけでデータの可視化やダッシュボードの作成は可能です。
本記事では、Power QueryとPower Pivot、可視化したデータを表示するダッシュボードの特徴について解説していきます。
すべてのバージョンのExcelがモダンExcelの機能を使えるのではありません。Power PivotやPower Queryが使えるのは、次で示すバージョンのExcelです。
Office for MacやOffice for AndroidではPower QueryやPower Pivotが使えませんので、注意が必要です。
これまで業務用に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 DesktopはPower BIのラインナップの1つであり、無償で提供されています。
ただしモダン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でこうした構造化されたデータをインポートし、操作しやすい表形式にしてくれる「テーブル」の作成や編集を行うことが可能です。
Power Queryは、Excel2013以降のExcelに標準搭載されています。今回、Office Home & Business 2019に付属するExcel を使って解説します。バージョンによって操作方法に多少違いがありますが、本質的にほぼ変わりません。
実際、外部からデータをインポートしてみましょう。まずExcelを立ち上げ、「データ」メニューを選択すると、外部からデータをインポートできるメニューが表示されます。
例として、気象庁のホームページから、2021年の東京の平均気温のデータをインポートします。CSVファイルをダウンロードしExcelに取り込むと、自動的にテーブルが作成されます。
ただし、このままでは空白部分や不要なデータが含まれているので、Power Pivotでデータを分析できません。そのため、テーブルを整形する必要があります。
不要なデータを取り除くため、Power Queryエディタから「クエリ」を編集します。
不要な行と列を削除し、編集完了すると、整形されたテーブルが表示されます。なお「適用したステップ」で操作を確認できます。間違った操作をしても、クリックするだけでクエリの状態を操作前に戻すことが可能です。
このようにPower Queryはデータを取り込み、テーブルを整形するなど、データ分析の前処理に使用する機能だと位置づけられます。
Power Pivotとは、Excelにインポートしたデータを分析する機能です。テーブルから、行と列をラベルとするクロス集計表などを作成することが可能です。また、テーブル同士を結合することでデータモデルを拡張したり、データ分析をもとにグラフを作成し直感的に理解しやすい形にしたりすることも可能です。
Power Pivotを使用するためには、ExcelからPower Pivot for Excelというアドインを追加する必要があります。
「オプション」メニューを開き、「アドイン」を選択。「管理」から「COMアドイン」を選択すると、「Power Pivot for Excel」などCOMアドインが表示されるので、チェックを入れ有効化します。
すると、「Power Pivot」というメニューが登場します。
では実際にデータをインポートし、テーブルを分析してみましょう。サンプルデータとして、次の売上データを考えてみましょう。
| 顧客 | 性別 | 年齢 | 品目 |
| S1 | M | 42 | ラーメン |
| S2 | F | 36 | オムライス |
| S3 | F | 24 | ケーキ |
| S4 | M | 60 | カレー |
| S5 | M | 51 | パスタ |
| S1 | M | 42 | コーヒー |
| S5 | F | 19 | ケーキ |
| 品目 | 価格 |
| ラーメン | 700 |
| オムライス | 800 |
| ケーキ | 400 |
| カレー | 650 |
| コーヒー | 300 |
| パスタ | 700 |
まず、Power Queryで売上データからテーブルを作成しましょう。
売上データを示すテーブルから、クロス集計表を作成してみます。「ピボットテーブルのフィールド」から行と列のラベルを選択すると、顧客と各品目の注文数を表すクロス集計表が作成されます。
なおPower Pivotには、テーブルを自動的に分析して、おすすめのピボットテーブルを提示してくれる機能もあります。
Power Pivotの分析結果をもとに、「ピボットグラフ」を作成することも可能です。たとえば、上で作成したクロス集計表からピボットグラフを作成してみましょう。各顧客が何を注文したかを可視化することができます。
なお、棒グラフだけでなく、折れ線グラフや散布図など、さまざまなグラフを選択することが可能です。
Power Pivotを語るうえで欠かせない機能が「リレーションシップ」です。複数のテーブルを連携してデータモデルを拡張することができます。たとえば、2つのテーブルの項目を結びつけることが可能です。
テーブル同士の結びつきを示すダイヤグラムは、Power Pivotから確認できます。
データモデルを拡張することで別のピボットグラフが作成でき、新たなデータ分析の可能性が拡がるでしょう。
先述したように、Excelの機能だけでもPower BIのようなダッシュボードを作成することができます。Excelのシートにピボットテーブルやピボットグラフを張り付けるだけです。たとえば、Microsoftのサイトには次のようなダッシュボードの見本が載っています。
参照：Excel でダッシュボードを作成し、Microsoft グループで共有する
Excelで作成したファイルを共有できますので、アクセス許可をした方なら誰でも閲覧することが可能です。
今回、モダンExcelの主要な機能であるPower QueryとPower Pivotについて紹介しました。直感的な操作だけでも、インポートしたデータを分析し可視化できることを何となく理解できたのではないでしょうか。
モダンExcelの世界は奥深く、Excelの従来から搭載されているDAX（Data Analysis Expressions）関数やM言語など、データを分析するうえの必要となる指標を覚えることで、より詳細なダッシュボードの作成が可能になります。詳しくは、モダンExcelやPower Query、Power Pivotなどを取り扱った初心者向けの本をご確認ください。
弊社のブログでは、ExcelやPower BIなど、ビジネスを効率化するうえで欠かせないツールの紹介を続けていきたいと思います。
