Excelをノーコードで自動化しよう! パワークエリの教科書 第1回 Power Query(パワークエリ)の概要と具体的な活用例
2024年5月13日（月）11時0分 マイナビニュース
近年、手間のかかる作業をPCに処理させる「自動化」が注目を集めている。Excelを自動化するために、VBAに挑戦してみた方もいるだろう。しかし、VBAはプログラミング言語の一種になるため、得手／不得手があるのも事実。そこで、この連載では「ノーコード」で自動化を実現できる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日)が保存されている。
各ファイルの内容を紹介していこう。各日の売上データは、提供方法(店内飲食／テイクアウト)や分類(バーガー類／サイドメニュー／ソフトドリンク／アルコール)に分けて記録されている。たとえば、「4月1日の売上」のExcelファイルには、以下の図のような形式で売上データが記録されている。
他の日付もデータ表の形式は同じで、それぞれの項目について「数量」と「売上」を記録していく仕組みになっている。念のため、「4月2日の売上」と「4月3日の売上」についてもファイルの中身を紹介しておこう。
さて、ここからが本題だ。これらのデータをもとに、3日分の「売上の合計」や「提供方法別の合計」など、さまざまな分析を行いたい場合はどうすればよいだろうか？ 通常のExcel操作で作業を進めていくのであれば、たいていの場合、以下のような作業手順になると思われる。
白紙のExcel(ワークシート)を作成し、そこに「4月1日の売上」のデータをコピー&ペーストする。
このままでは「日付」の情報が抜け落ちてしまうので、この表に「4月1日」のデータを手入力で追加する。
続いて、「4月2日の売上」のExcelファイルを開き、データの部分だけをコピー&ペーストする。さらに「4月2日」の日付を手入力で追加する。
以降も同様に、「4月3日の売上」のデータをコピー&ペーストし、日付を手入力してあげる。すると、以下の図に示したようなデータ表を作成できる。
これで準備は完了。あとは、各自の好きなようにデータを分析していけばよい。3日間の「売上の合計」を求めたい場合は、関数SUMを使って「F3:F23」のセル範囲を合計する。
「提供方法別の合計」は関数SUMIFで求められる。たとえば、「店内飲食」についてのみ合計を求めたい場合は、以下の図のように関数SUMIFを記述すればよい。
同様の手順で「テイクアウト」についても「売上」の合計を求めると、以下の図のような結果を得ることができる。
このように、複数のファイルに分割して記録されているデータを分析するときは、コピー&ペーストなどによりデータ表を結合する作業が必要となる。今回の例のように、Excelファイルの数が3つ程度であれば、上記に示した方法で作業を進めても特に問題は生じないだろう。
ただし、「3月の売上データも同じ形式で記録されていた……」となると、少し話が変わってくる。3月は全部で31日あるので、
(1)各日付のExcelファイルを開いてデータをコピーする
(2)集計用のExcelファイルに、先ほどコピーしたデータを貼り付ける
(3)コピー元のExcelファイルを閉じる
(4)日付データを手作業で追加する
といった作業を31回も繰り返さなければならない。無理な話ではないが、少し面倒な作業になると予想される。
さらに、「実は昨年1年分のデータも同じ形式で……」となると、同様の作業を365回も繰り返す羽目になってしまう。これは相当に面倒な作業になるはずだ。また、ミスなく365回もコピペを繰り返す、というのも不安要素になる。もしかしたら途中でコピーミスを犯してしまい、それに気づかずに作業を進めてしまう可能性も十分に考えられる。
パワークエリを使用した場合
このような場合にぜひ活用したいのが、本連載で紹介するパワークエリだ。パワークエリを使えば、クリックひとつで複数のExcelファイルを「1つのデータ表」に結合することが可能となる。具体的には、「データ」タブにある「すべて更新」をクリックするだけでデータ分析の前準備を完了できることになる。
あとは、目的に応じでデータを分析していくだけ。3日分の「売上の合計」は関数SUMで算出できる。
同様に関数SUMIFで「提供方法別の合計」を求めてもよい。以降の分析作業を「どのように進めていくか？」は各自の自由だ。
このように、面倒な繰り返し処理を「自動化」できるツールがパワークエリとなる。ただし、パワークエリが勝手に状況を判断して、自動的に処理を行ってくれる訳ではない。自動化を実現するには、「どのような手順で処理を進めていくか？」を事前にクエリとして指示しておく必要がある。これを指示するための操作画面が「Power Query エディター」となる。
今回の例の場合、
(1)「4月の売上」フォルダー内にあるExcelファイルを結合する
(2)結合したデータ表から「合計」の行を削除する
(3)「日付」のデータを追加する
といった処理をPower Query エディターで指示している。つまり、パワークエリを活用するにはPower Query エディターの使い方を習得しておく必要がある訳だ。この操作方法について詳しく紹介していくのが本連載の主旨となる。
クエリの更新によるデータ処理の自動化
先ほど示したPower Query エディターの処理手順をよく見ると、「3つのExcelファイルを結合する」ではなく、「フォルダー内にあるExcelファイルを結合する」という指示になっていることに気付くと思う。
このため、Excelファイルの数が増えたときも、そのままパワークエリで処理することが可能である。続いては、「4月4日」と「4月5日」の売上データをフォルダーに追加した例を見ていこう。
パワークエリで結合したデータ表を開き、「データ」タブにある「すべて更新」をクリックすると……、
「4月4日」と「4月5日」の売上データが自動的に追加されるのを確認できる。それにあわせて「合計」や「店内飲食」、「テイクアウト」の数値も再計算される。
もちろん、4月6日以降についても同様だ。各日の売上データを記録したExcelファイルをフォルダーに追加するだけで、最新の分析結果を得ることが可能となる。
このように、データの追加・修正に柔軟に対応できることもパワークエリの大きな魅力となる。そのつど新しいデータをコピー&ペーストしてあげる必要はない。クリックひとつで“最新の状況”に即した結果を得られるようになる。
そのほか、毎日のように繰り返す作業ではないが、「通常のExcelでは処理するのが難しい……」といった作業にパワークエリを活用できるケースもある。
Excelで複雑な処理を行うには“関数”の利用が必須になる。状況によっては「関数をいくつも組み合わせて処理しなければならない」というケースもあるだろう。関数に不慣れな方にとって、これは大きな障壁となるかもしれない。
このような場合にパワークエリを活用すると、もっと理解しやすい形で処理を進めていくことが可能となる。関数の知識がなくても大丈夫。パワークエリは、それぞれの処理をステップ形式で指示していく仕組みになっているため、各ステップの結果を見ながら処理を進めていくことが可能となる。
そういった使い方も含めて、次回の連載から「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日)が保存されている。
各ファイルの内容を紹介していこう。各日の売上データは、提供方法(店内飲食／テイクアウト)や分類(バーガー類／サイドメニュー／ソフトドリンク／アルコール)に分けて記録されている。たとえば、「4月1日の売上」のExcelファイルには、以下の図のような形式で売上データが記録されている。
他の日付もデータ表の形式は同じで、それぞれの項目について「数量」と「売上」を記録していく仕組みになっている。念のため、「4月2日の売上」と「4月3日の売上」についてもファイルの中身を紹介しておこう。
さて、ここからが本題だ。これらのデータをもとに、3日分の「売上の合計」や「提供方法別の合計」など、さまざまな分析を行いたい場合はどうすればよいだろうか？ 通常のExcel操作で作業を進めていくのであれば、たいていの場合、以下のような作業手順になると思われる。
白紙のExcel(ワークシート)を作成し、そこに「4月1日の売上」のデータをコピー&ペーストする。
このままでは「日付」の情報が抜け落ちてしまうので、この表に「4月1日」のデータを手入力で追加する。
続いて、「4月2日の売上」のExcelファイルを開き、データの部分だけをコピー&ペーストする。さらに「4月2日」の日付を手入力で追加する。
以降も同様に、「4月3日の売上」のデータをコピー&ペーストし、日付を手入力してあげる。すると、以下の図に示したようなデータ表を作成できる。
これで準備は完了。あとは、各自の好きなようにデータを分析していけばよい。3日間の「売上の合計」を求めたい場合は、関数SUMを使って「F3:F23」のセル範囲を合計する。
「提供方法別の合計」は関数SUMIFで求められる。たとえば、「店内飲食」についてのみ合計を求めたい場合は、以下の図のように関数SUMIFを記述すればよい。
同様の手順で「テイクアウト」についても「売上」の合計を求めると、以下の図のような結果を得ることができる。
このように、複数のファイルに分割して記録されているデータを分析するときは、コピー&ペーストなどによりデータ表を結合する作業が必要となる。今回の例のように、Excelファイルの数が3つ程度であれば、上記に示した方法で作業を進めても特に問題は生じないだろう。
ただし、「3月の売上データも同じ形式で記録されていた……」となると、少し話が変わってくる。3月は全部で31日あるので、
(1)各日付のExcelファイルを開いてデータをコピーする
(2)集計用のExcelファイルに、先ほどコピーしたデータを貼り付ける
(3)コピー元のExcelファイルを閉じる
(4)日付データを手作業で追加する
といった作業を31回も繰り返さなければならない。無理な話ではないが、少し面倒な作業になると予想される。
さらに、「実は昨年1年分のデータも同じ形式で……」となると、同様の作業を365回も繰り返す羽目になってしまう。これは相当に面倒な作業になるはずだ。また、ミスなく365回もコピペを繰り返す、というのも不安要素になる。もしかしたら途中でコピーミスを犯してしまい、それに気づかずに作業を進めてしまう可能性も十分に考えられる。
パワークエリを使用した場合
このような場合にぜひ活用したいのが、本連載で紹介するパワークエリだ。パワークエリを使えば、クリックひとつで複数のExcelファイルを「1つのデータ表」に結合することが可能となる。具体的には、「データ」タブにある「すべて更新」をクリックするだけでデータ分析の前準備を完了できることになる。
あとは、目的に応じでデータを分析していくだけ。3日分の「売上の合計」は関数SUMで算出できる。
同様に関数SUMIFで「提供方法別の合計」を求めてもよい。以降の分析作業を「どのように進めていくか？」は各自の自由だ。
このように、面倒な繰り返し処理を「自動化」できるツールがパワークエリとなる。ただし、パワークエリが勝手に状況を判断して、自動的に処理を行ってくれる訳ではない。自動化を実現するには、「どのような手順で処理を進めていくか？」を事前にクエリとして指示しておく必要がある。これを指示するための操作画面が「Power Query エディター」となる。
今回の例の場合、
(1)「4月の売上」フォルダー内にあるExcelファイルを結合する
(2)結合したデータ表から「合計」の行を削除する
(3)「日付」のデータを追加する
といった処理をPower Query エディターで指示している。つまり、パワークエリを活用するにはPower Query エディターの使い方を習得しておく必要がある訳だ。この操作方法について詳しく紹介していくのが本連載の主旨となる。
クエリの更新によるデータ処理の自動化
先ほど示したPower Query エディターの処理手順をよく見ると、「3つのExcelファイルを結合する」ではなく、「フォルダー内にあるExcelファイルを結合する」という指示になっていることに気付くと思う。
このため、Excelファイルの数が増えたときも、そのままパワークエリで処理することが可能である。続いては、「4月4日」と「4月5日」の売上データをフォルダーに追加した例を見ていこう。
パワークエリで結合したデータ表を開き、「データ」タブにある「すべて更新」をクリックすると……、
「4月4日」と「4月5日」の売上データが自動的に追加されるのを確認できる。それにあわせて「合計」や「店内飲食」、「テイクアウト」の数値も再計算される。
もちろん、4月6日以降についても同様だ。各日の売上データを記録したExcelファイルをフォルダーに追加するだけで、最新の分析結果を得ることが可能となる。
このように、データの追加・修正に柔軟に対応できることもパワークエリの大きな魅力となる。そのつど新しいデータをコピー&ペーストしてあげる必要はない。クリックひとつで“最新の状況”に即した結果を得られるようになる。
そのほか、毎日のように繰り返す作業ではないが、「通常のExcelでは処理するのが難しい……」といった作業にパワークエリを活用できるケースもある。
Excelで複雑な処理を行うには“関数”の利用が必須になる。状況によっては「関数をいくつも組み合わせて処理しなければならない」というケースもあるだろう。関数に不慣れな方にとって、これは大きな障壁となるかもしれない。
このような場合にパワークエリを活用すると、もっと理解しやすい形で処理を進めていくことが可能となる。関数の知識がなくても大丈夫。パワークエリは、それぞれの処理をステップ形式で指示していく仕組みになっているため、各ステップの結果を見ながら処理を進めていくことが可能となる。
そういった使い方も含めて、次回の連載から「Power Query エディター」の操作手順について詳しく解説していこう。パワークエリを使いこなすには、多少の“知識”と“慣れ”が求められる。本連載を読み進めることで、少しでも多くのスキルを習得して頂ければ幸いだ。
