Power Query(パワークエリ)の概要と具体的な活用例

近年、手間のかかる作業をPCに処理させる「自動化」が注目を集めている。Excelを自動化するために、VBAに挑戦してみた方もいるだろう。しかし、VBAはプログラミング言語の一種になるため、得手／不得手があるのも事実。そこで、この連載では「ノーコード」で自動化を実現できるPower Query(パワークエリ)の使い方を詳しく紹介していこう。

Power Query(パワークエリ)の概要

ノーコードで自動化を実現するパワークエリ

Excelはビジネスに必須のアプリであり、詳しくなればなるほど便利で快適なツールとして活用できるようになる。しかし、どんなにExcelスキルを磨いても、状況によっては「面倒で単調な作業」を強いられてしまうケースがある。

このような場合にぜひ覚えておきたいのが「パワークエリ」と呼ばれるツールだ。パワークエリはExcelに標準装備されているツールで、サブスク版のMicrosoft 365はもちろん、Excel 2021/2019/2016で使用することが可能となっている。

このツールを上手に使うと、日々のExcel作業を自動化できるようになる。パワークエリは「ノーコード」でも使えるように設計されているため、プログラミングが苦手でVBAに挫折した経験がある方でも十分に対応できるツールとなる。

ということで、「パワークエリ」の詳しい使い方を本連載で紹介していこう。この出会いをきっかけに「Excelの新しい活用方法」を発見して頂ければ幸いだ。

なお、本連載では、Excel 2021をベースにパワークエリの使い方を解説していく。他のバージョンのExcelでは画面表示などが若干異なる部分もあるが、基本的な操作手順は同じと考えてよい。よって、Excel 2021以外を使用している場合でも十分に参考になるはずだ。

簡単だけど少し面倒なExcel作業の例

まずは、パワークエリの概要を知るために、具体的な例を紹介していこう。以下の図は、あるハンバーガー店が日々の売上データをExcelで記録したものだ。売上データは1日ずつ別のExcelファイルに記録されており、現時点では3日分のデータ(4月1日～4月3日)が保存されている。

日々の売上データを記録したExcelファイル

各ファイルの内容を紹介していこう。各日の売上データは、提供方法(店内飲食／テイクアウト)や分類(バーガー類／サイドメニュー／ソフトドリンク／アルコール)に分けて記録されている。たとえば、「4月1日の売上」のExcelファイルには、以下の図のような形式で売上データが記録されている。

4月1日の売上データ

他の日付もデータ表の形式は同じで、それぞれの項目について「数量」と「売上」を記録していく仕組みになっている。念のため、「4月2日の売上」と「4月3日の売上」についてもファイルの中身を紹介しておこう。

4月2日の売上データ

4月3日の売上データ

さて、ここからが本題だ。これらのデータをもとに、3日分の「売上の合計」や「提供方法別の合計」など、さまざまな分析を行いたい場合はどうすればよいだろうか？　通常のExcel操作で作業を進めていくのであれば、たいていの場合、以下のような作業手順になると思われる。

白紙のExcel(ワークシート)を作成し、そこに「4月1日の売上」のデータをコピー&ペーストする。

「4月1日の売上データ」をコピー&ペースト

このままでは「日付」の情報が抜け落ちてしまうので、この表に「4月1日」のデータを手入力で追加する。

日付データの追加

続いて、「4月2日の売上」のExcelファイルを開き、データの部分だけをコピー&ペーストする。さらに「4月2日」の日付を手入力で追加する。

「4月2日の売上データ」を追加

以降も同様に、「4月3日の売上」のデータをコピー&ペーストし、日付を手入力してあげる。すると、以下の図に示したようなデータ表を作成できる。

「4月3日の売上データ」を追加

これで準備は完了。あとは、各自の好きなようにデータを分析していけばよい。3日間の「売上の合計」を求めたい場合は、関数SUMを使って「F3:F23」のセル範囲を合計する。

関数SUMで「売上」の合計を算出

「提供方法別の合計」は関数SUMIFで求められる。たとえば、「店内飲食」についてのみ合計を求めたい場合は、以下の図のように関数SUMIFを記述すればよい。

関数SUMIFで「店内飲食」の合計を算出

同様の手順で「テイクアウト」についても「売上」の合計を求めると、以下の図のような結果を得ることができる。

「提供方法」で分類した合計

このように、複数のファイルに分割して記録されているデータを分析するときは、コピー&ペーストなどによりデータ表を結合する作業が必要となる。今回の例のように、Excelファイルの数が3つ程度であれば、上記に示した方法で作業を進めても特に問題は生じないだろう。

ただし、「3月の売上データも同じ形式で記録されていた……」となると、少し話が変わってくる。3月は全部で31日あるので、

(1)各日付のExcelファイルを開いてデータをコピーする
(2)集計用のExcelファイルに、先ほどコピーしたデータを貼り付ける
(3)コピー元のExcelファイルを閉じる
(4)日付データを手作業で追加する

といった作業を31回も繰り返さなければならない。無理な話ではないが、少し面倒な作業になると予想される。

さらに、「実は昨年1年分のデータも同じ形式で……」となると、同様の作業を365回も繰り返す羽目になってしまう。これは相当に面倒な作業になるはずだ。また、ミスなく365回もコピペを繰り返す、というのも不安要素になる。もしかしたら途中でコピーミスを犯してしまい、それに気づかずに作業を進めてしまう可能性も十分に考えられる。

パワークエリを使用した場合

このような場合にぜひ活用したいのが、本連載で紹介するパワークエリだ。パワークエリを使えば、クリックひとつで複数のExcelファイルを「1つのデータ表」に結合することが可能となる。具体的には、「データ」タブにある「すべて更新」をクリックするだけでデータ分析の前準備を完了できることになる。

パワークエリで結合したデータ表

あとは、目的に応じでデータを分析していくだけ。3日分の「売上の合計」は関数SUMで算出できる。

関数SUMで「売上」の合計を算出

同様に関数SUMIFで「提供方法別の合計」を求めてもよい。以降の分析作業を「どのように進めていくか？」は各自の自由だ。

「提供方法」で分類した合計

このように、面倒な繰り返し処理を「自動化」できるツールがパワークエリとなる。ただし、パワークエリが勝手に状況を判断して、自動的に処理を行ってくれる訳ではない。自動化を実現するには、「どのような手順で処理を進めていくか？」を事前にクエリとして指示しておく必要がある。これを指示するための操作画面が「Power Query エディター」となる。