4

今回は、かなり便利なのにあまり使われていないエクセルの機能の一つ、PowerQuery（パワークエリ）について解説したいと思います。おそらく少しとっつきにくい印象があるのではないかと思いますが、色んなデータ分析の作業過程で発生するであろうデータの加工作業が自動化できます。

たまにRPAでこれらの作業工程（コピー＆貼り付け）をしている人を見かけますが、そんなことは必要ありません。マクロを使う必要もありません。エクセルのPowerQuery（パワークエリ）だけで完結できます。つまり、マウスの操作ができれば実現できますので、一緒に頑張っていきましょう！

PowerQuery（パワークエリ）で実現できること

では、具体的な作業ステップを見ていく前に、そもそもPowerQueryを使うと何が出来るのか？ということを見ていきます。

例えば、経理部・財務部の担当の方であれば、毎月…

1. 月末に経理システムからデータをダウンロード
2. それに対して、売上予測のファイルを結合して、業績予測シートを修正し、
   
3. Vlookupなどで項目ごとにコストの調整をしたり、
   
4. 計算式を追加したり、
   
5. 別のシートからコピー＆貼り付けをしたり、
6. そして、やっとたどり着いたデータ分析
   

これ、随分と多くの、かつ煩雑な作業が発生しています。この作業が1回だけなら問題ないかもしれませんが、これを繰り返し、同じ作業を必要となる場合は、改善の余地ありです。

PowerQuery（パワークエリ）は、この作業工程を自動化してくれます。

PowerQuery（パワークエリ）とは

PowerQuery（パワークエリ）とは、マイクロソフト社の エクセルの機能の一つで、外部データとの連携や、連携してからのデータの加工（列の追加や抽出等）を自動化する機能です。ここですごいのが、連携するデータとしては、エクセルファイルはもちろんのこと、Web上の表や、JSONデータなどとの連携も簡単に行うことが出来ます。また、マクロとは違ってプログラミングを書く必要はなく、マウスを使った直感的な操作で設計できるため、実は初心者向きと言えます。

PowerQuery（パワークエリ）の使い方

PowerQuery（パワークエリ）は、外部データとの接続（取り込み）、外部データの変換、任意の列の追加、操作の順番変更、取り消しなどができます。

外部データとの接続

データタブの中の一番左リボンの「データの取得」をクリックすると、以下のメニューが表れます。これだけでも、数多くの外部データと接続できることが分かります。では順番に見ていきたいところですが、今回は皆さんが最も使うであろう 2つ（ブックから、オンラインサービスから）を紹介します。

ファイルから

ブックから、テキストまたはCSVから、XMLから、JSONから、フォルダーから

データベースから

Azureから

オンラインサービスから

オンラインサービスは、グレーアウトしてしまっていて選択できなくなってしまっていますが、これはマイクロソフトと契約しているプランが問題です。

利用できるプラン

1. Office / Microsoft 365 E3
2. Office / Microsoft 365 E5

利用できないプラン

1. Microsoft 365 Apps for enterprise (旧称 : Office 365 ProPlus)
2. Microsoft 365 Apps for business (旧称 : Office 365 Business)
   
3. Microsoft 365 Business Standard (旧称 : Office 365 Business Premium)
   

その他のデータソースから

今回の例では、皆さんがよく使うであろう複数のエクセルを取り込んでみます。では、今回の例では、皆さんが最もよく使うであろう、複数のエクセルファイルからデータを取り込み、結合、編集して、エクセルに出力する方法です。

まずは、月ごとにまとまった複数のファイルを一瞬で取り込み、結合し、更にデータを変換することができます。

データを変換する

では、今回の例では、皆さんが最もよく使うであろう、複数のエクセルファイルからデータを取り込み、結合、編集して、エクセルに出力する方法です。

まずは、月ごとにまとまった複数のファイルを一瞬で取り込み、結合し、更にデータを変換することができます。

今回は、事前に月ごとにまとまった営業成績（売上票）ファイルを営業成績フォルダの中に用意しておきます。1月から3月までとします。

データの取得 → ファイルから → フォルダーからを選択します。

目的のファイルが入ったフォルダ「営業成績」をクリックし、「開く」をクリックします。

そうすると、3つのファイルが表示されます。

結合メニューより、データの結合と変換を選択します。

ファイルの結合画面が表示されます。ファイルの中に、テーブル形式になっているデータセットとワークシート全体を取り込むかを選択できるようになっています。ここでは、「テーブル」を選択しますが、どちらでも結構です。

そうすると、エクセルでは普段見ることない画面「Power Query エディター」が表示されます。

この画面は、大きく分けて4つのパーツで構成されていて、上のタブは何かしらの操作を行うためのコマンドメニューの集まり、左の「クエリ」は取り込んだソースファイルを表示、真ん中のデータ表示領域には1月〜3月までのデータが既に結合されていて、右側の「クエリの設定」には、実行した操作の履歴が表示されています。

プロパティの名前は、変更できるため今回は、「営業成績集計」とします。

では、本題のデータの変換をしていきます。

列の削除

表には、そのデータソースとなっているファイル名が一番左の列に自動的に挿入されているので、これを削除します。

列の削除をします。

年月日を整理する

3月分のファイルだけ誤って2021年ではなく、2031年と入力されていたため、月日だけ残るようにデータを整理していきます。

列の分割より、区切り記号による分割を選択してます。

そこから「/（スラッシュ）」を選択して、分割の中から「一番左の区切り記号」を選択してOKをクリックします。これで、年月日の年と月日を分けることができます。

右のクエリの設定から、一番下のステップを削除してあげると、以下の表になります。

値の置換

2031年を2021年に変換することも可能です。その場合は、「変換」タブより、「値の置換」を選択します。

そうすると、値の置換メニューが出てくるので、ここから「検査する値」を2031に、「置換後」を2021に指定してあげたら、OKを押します。

2031年となっていたのが、2021年に変換されていることが分かります。

非常に簡単ですよね。Power Queryの良いところは、このようにデータを変換しても、ソースファイルは変換されていないことです。つまり、他の部署から、もしくは顧客からデータを受け取る場合になかなかファイルフォーマットの変換をお願いできない場合でも、Power Queryを使えば簡単にデータをキレイしてから取り込むことができます。

Power Queryでは変換のルールを決めているためなので、どのファイルでも再現が可能です。

以上！