エクセルのパワークエリ(PowerQuery)は、データ処理を自動化するのに非常に役立つ機能です。
Excel2016のバージョンから標準搭載された機能ですが、まだ認知度は低いままです。
しかし、この機能の基本的な使い方を理解しておくだけで、自動化できるExcel処理が数多くあります。
この記事では、エクセルのパワークエリの入門編として、以下の内容をお届けします。
この記事で学習できること
１．パワークエリ(PowerQuery)とは？
２．パワークエリで出来ること
３．パワークエリのデータ処理のイメージ
４．パワークエリを使った事例を４つ紹介
パワークエリの入門用の事例を紹介しています。誰でも作成できるように紹介しているので、ぜひ試してみて便利さを実感してみてください。
エクセルのパワークエリ(PowerQuery)とは？
パワークエリ(PowerQuery)と聞いても、いったいどんな機能なのかイメージできない人がほとんどではないでしょうか。
まずは、パワークエリとは何なのかを紹介します。
パワークエリとは、『Microsoft Excelに備わっているデータ処理ツールの1つ』です。
この機能を使うと、複数のデータを取り込んで自動で結合したり、変形させたり、数式を挿入することができるので、大幅な時短につながります。
パワークエリは、Excel バージョン2016以降から標準機能として搭載され、「データの取得と変換」から使用することができます。
CSVファイルを取り込む際に、知らず知らずのうちに使ったことがある人もいるかもね
パワークエリで出来ること
パワークエリは、VBA(マクロ)とは異なり、ローコードで使用できるのがメリットです。
つまり、プログラミングの知識がなくても、データ処理の自動化を行える便利なツールです。
厳密には、M言語というプログラミング言語を使用しているので、高度なデータ処理の自動化を行う場合は、M言語を取得する必要があります。
しかし、簡単なデータ処理であれば、だれでも簡単にパワークエリを使ってデータ処理することができます！
パワークエリを活用すれば、以下の作業を行えます。
- ExcelやCSV、PDFなどからデータを取得できる
- データベース(アクセス、Oracleなど)からデータを取得できる
- クラウドサービス(SharePointなど)のデータを取得できる
- 取り込んだデータの整形や加工ができる
- 取り込んだデータの表示形式を日付や文字列などに変換できる
- 不要な列を削除したり、並び替えを行える
- 列を追加して合計などの数式を挿入する
- 複数のデータを結合できる
- パワーピボットと組み合わせると、高度な集計や分析が行える
- 「更新」ボタンを押すだけで、データの取得、加工、出力を自動で行える
パワークエリの一番のメリットは、10項の「更新」ボタンを押すだけで、自動でデータを加工できる点にあります。
パワークエリを使って、任意のフォルダに保管されているデータを取得し、加工が終わったらExcelファイルに出力できます。
仮に、取得した元データの値を変更しても、「更新」ボタンを押すだけで、パワークエリで出力したデータを自動で更新することができます。
パワークエリを使ってなかったら、毎回、元データや数式を貼りつけ直す必要があるから大変だけど、その必要が無くなるよ。
パワークエリのデータ処理のイメージ
パワークエリをデータ処理のイメージは、以下のようになります。
- ステップ１．データを取得する
ExcelファイルやCSVファイルなどを読み込んで、PowerQueryの処理を開始する。複数のデータを読み込むことが可能。 - ステップ２．データを加工する
複数のデータの結合や並べ替え、列の追加/削除、数式の挿入などを行う - ステップ３．データを出力する
Excelのワークシートにデータを出力する。「更新」ボタンを押すことで、元データの変更を反映できる
【入門編】パワークエリを使った事例その１
それでは実際にパワークエリ(PowerQuery)を使ってみましょう。
今回は説明用に、複数のExcelファイルを使って、「データを自動で結合して加工」する方法を解説します。
難しい操作はありません。ステップ０からステップ４までの手順に沿って落ち着いて作業すれば、１５分程度で完了します。
パワークエリの便利さを感じること間違いないので、ぜひチャレンジしてください。
簡単な作業だから、だまされたと思って1度でいいからやってみて！
PowerQuery/ステップ０．元データの準備
ステップ0では、パワークエリを使うための元データを２つ作成します。
手順１．パワークエリに取り込む元データ「仕入先」を準備
まずは、下のスクリーンショットのような「仕入先」マスタを作成してください。「商品コード」ごとの「商品名」「生産地」「カテゴリ」を表にまとめます。
手順２．パワークエリに取り込む元データ「販売明細」を準備
新規にExcelファイルを開いて、下のスクリーンショットのような「販売明細」データを作成してください。「販売明細」データには、「仕入先」マスタの商品コードを使用してください。
手順３．任意のフォルダに保存
手順１，２で作成したデータを任意のフォルダに保存してください。
パワークエリを実行するための準備は以上で完了です。
PowerQuery/ステップ１．データの読み込み
STEP1ではパワークエリ機能を使って、STEP0で作成した２つの元データをExcelに読み込みします。
パワークエリに「読み込む」とは、「取り込む」「取得する」と同義だと思ってください。
手順１．「データの取得」を選択
新規にExcelファイルを開いて、「データ」タブの「データの取得」コマンドを選択し、「ファイルから」の「Excelブックから」を選択します。
手順２．元データをインポートする
「データの取り込み」画面が表示されます。ステップ0で作成した「仕入先」ファイルを選択して(図2)、「インポート」を押してください(図3)。
手順３．元データをインポートする
「ナビゲーター」画面が開きます。画面の左側に読み込んだ「仕入先」ファイルのワークシートが表示されるので、対象のシートを選択し(図4)、「読み込み」を押します(図5)。
手順４．元データをインポートする
「仕入先」のデータがExcelに取り込まれます。画面右側に「クエリと接続」画面に取り込んだクエリ(※)が表示されます。
※「クエリ」とは・・取り込んだExcelファイルに要求を送信する命令文のこと
右クリックして(図6)、右クリックメニューの「名前と変更」から名前を「仕入先」に変更します(図7、8)。
画面右側に「クエリと接続」画面が表示されていない場合は、「データ」タブの「クエリと接続」をクリックしてください。
手順５．「見出し」を確認
読み込んだ仕入先データを確認すると、見出し行が「column1」「column2」「column3」「column4」となっています。これは、ファイルを取り込んだ際、見出し行を正しく判断できなかったために発生した現象です。
見出し行を編集するために、対象のクエリをダブルクリックします(図9)。※修正する必要が無い場合は、手順９から作業を開始してください。
手順６．「ヘッダー」を変換
「Power Query エディター」画面が表示されます。「変換」タブの「１行目をヘッダーとして使用」ボタンを押します(図10)。
手順７．「閉じて読み込む」を押す
１行目がヘッダーに変わったことを確認します。確認できたら、「ホーム」タブの「閉じて読み込む」ボタンを押して、エディター画面を閉じます(図11)。
手順８．「ヘッダー」を確認
元のエクセル画面に戻ります。下のように、ヘッダー(見出し)が変更されたことを確認してください。
手順９．「ヘッダー」を確認
以上で「仕入先」ファイルの読み込みは完了です。続いて、同じExcelファイル上で、ステップ0で作成した「販売明細」エクセルも読み込みます。
手順１～手順8を繰り返して、「販売明細」データも読み込んでください(図12)。２つのデータを読み込んだら、ワークシートの名前も分かりやすい名称に変更しておきましょう(図13)。
手順１０．名前を付けて保存して閉じる
以上でステップ１の「パワークエリの読み込み」に関する作業は完了です。
読み込んだExcelブックは名前を付けて保存して閉じてください。
PowerQuery/ステップ２．２つのExcelブックを結合
ステップ2では、ステップ1で読み込んだ「仕入先」と「販売明細」の２つのExcelブックを結合していきます。
手順１．Excelファイルを開く
ふたたび、Excelブックを開きます。リボンの下に「セキュリティの警告」のメッセージが表示される場合は、「コンテンツの有効化」をクリックしてください(図1)。この操作を行わないと、元データとの外部連携が行われません。
手順２．セキュリティ警告のメッセージ
以下のスクリーンショットのような「セキュリティの警告」画面が表示される場合は、「はい」を選択します。
手順３．「クエリと接続」をクリック
画面右側に「クエリと接続」画面が表示されていない場合は、「データ」タブの「クエリと接続」をクリックします。
手順４．「結合」をクリック
「仕入先」データの任意のセルをクリックして(図4)、「クエリ」タブの「結合」を選択します(図5)。
手順５．「結合の種類」を指定
「マージ」画面で、上側のデータを「仕入先」を選択します(図6)。下側のデータは「販売明細」を選択します(図7)。
最下部の結合の種類は、「左外部(最初の行すべて、および２番目の行のうち一致するもの」を選択します(図8)。
手順６．「キー列」を指定
結合するキーとなるキー列をします。「仕入先」の「商品コード」列をクリックします(図9)。「販売明細」の「商品コード」列もクリックします(図10)。選択したら、右下の「OK」を押してください(図11)。
手順７．「販売明細」列を開く
「PowerQueryエディター」が開きます。「仕入先」に結合された「販売明細」列を確認し、ヘッダーの右側のボタンを押してください(図12)。
手順８．展開する列を選択する
「商品コード」列は「仕入先」データから取り込まれるので、「商品コード」のチェックを外します(図13)。「もの列名をプレフィックスとして使用します」のチェックも外してください(図14)。
設定したら「OK」を押してください(図15)。
手順９．PowerQueryエディターを閉じる
「販売明細」データが展開されます。「閉じて読み込む」をクリックして、PowerQueryエディターを閉じてください(図16)。
手順１０．クエリの名称を変更する
「仕入先」と「販売明細」が結合されたデータが新しいシートに展開されます。「クエリと接続」画面に結合したクエリが表示されるので、右クリックメニューから名前を「結合データ」に変更します(図17)。
以上でステップ２の作業は完了です。
PowerQuery/ステップ３．加工(列の挿入、並べ替え、フィルター)
ステップ３では、結合したデータを加工します。
今回は、以下の３つの加工が自動で行われるように、PowerQueryエディターで編集します。
パワークエリで結合データを加工
１．列を追加して、「売価」×「販売数量」の合計列を挿入
２．列の並べ替え
３．フィルターで特定の値のみ表示
手順１．「結合」クエリをダブルクリック
ステップ２で作成した「結合」クエリを編集するため、ダブルクリックします。
手順２．「カスタム列」をクリック
PowerQueryエディターが開きます。「売価」×「販売数量」を計算するための列を追加するため、「列の追加」タブの「カスタム列」をクリックします。
手順３．「カスタム列」の名前を設定
「カスタム列」画面が表示されるので、新しい列名を入力します(図3)。次にカスタム列の数式を入力するため、右側のリストから「売価」をダブルクリックします(図4)。
手順４．「カスタム列」の数式を設定
「カスタム列」の式を選択し、掛算のための「*」を入力します(図5)。右側のリストから「販売数量」をダブルクリックします(図6)。
=[売価]*[販売数量]
カスタム式の作成が終わったら「OK」を押してください(図7)。
手順５．「合計列」を並べ替え
PowerQueryエディターに戻ると、設定した合計列が一番右端に追加されます。見出しをドラッグすると列の並べ替えができるので、「販売高」列を「販売数量」列の右側に移動します(図8)。
合計列に限らず、別の列も並べ替えしたい場合は、設定しておきましょう。
手順６．フィルターで商品コードを絞る
「商品コード」の見出しボタンを押して(図9)、「A001」だけにチェックを入れた状態にしてから(図10)、「OK」を押します(図11)。
手順７．PowerQueryエディターを終了する
「商品コード」がA001のみ抽出されたことを確認します。「閉じて読み込む」をクリックして、PowerQueryエディターを閉じてください(図12)。
手順８．編集後の結合データを確認
以上の作業により、下のスクリーンショットのように、「合計列の追加」「並べ替え」「フィルター」の３つが行われたことを確認できれば完了です。
以上でステップ３の作業は完了です。
PowerQuery/ステップ４．更新ボタンで自動化を確認
ステップ４では、パワークエリによって「複数のデータが自動で結合されて加工」されるかどうかを確認します。
手順１．「販売明細」データを編集
パワークエリで結合した元データ「販売明細」ファイルを開いて、データを追加します(図1)。データを追加したら上書き保存して、ファイルは閉じてください。
手順２．「結合」データを更新
パワークエリを使用したExcelブックを開きます。「データ」タブの「すべて更新」ボタンを押すと、クエリのすべてが最新の状態に更新され、「販売明細」に追加した１行も加工されて反映されます。
データのアップデートは、「クエリ」タブの「更新」ボタンからでも、元データの情報を再取得することができます。
ただし、複数のクエリを作成している場合は、一部のデータを更新し忘れる可能性があるので、「すべて更新」ボタンを使うことをお勧めします。
パワークエリを使った事例その２(複数データの一括取り込み)
パワークエリを使えば、下のスクリーンショットのように、特定のフォルダに保管されている複数ファイルを一括で取り込むことができます。
パワークエリを使うと、下のように、１つのワークシートに12個の複数ファイルをまとめて一括でデータ出力することができます。
このパワークエリの使い方は、以下の記事で詳しく紹介しているので、興味のある方は参考にしてください。
パワークエリを使った事例その３(CSVファイルの取り込み)
パワークエリを使えば、CSVファイルやテキストもExcelブックに取り込むことができます。
数値の先頭「0」が消えないように表示させるなど、表示形式を変更するのにもパワークエリは役立ちます。
「データ」タブの「テキストまたはCSVから」ボタンからデータの取得を開始します。
このパワークエリの使い方は、以下の記事で詳しく紹介しているので、興味のある方は参考にしてください。
パワークエリを使った事例その４(PDFファイルの取り込み)
パワークエリを使えば、PDFファイルもExcelブックに取り込むことができます。
このパワークエリの使い方は、以下の記事で詳しく紹介しているので、興味のある方は参考にしてください。
パワークエリは初心者にも使える「自動化」ツール！
今回はパワークエリの仕組みと、パワークエリを使った事例を紹介しました。
パワークエリは、エクセルの「自動化」に最適なツールです。
毎月、外部システムから同じ形のデータを出力し加工しているような作業であれば、特にエクセルのパワークエリと相性がいいでしょう。
パワークエリでIF関数(IF文)を利用する場合など、覚えるテクニックは数多くありますが、まずは思いつく簡単な作業を自動化して時短につなげていってください。
パワークエリに使い慣れてきたら、以下の記事からマクロ(VBA)の利用にもチャレンジしてください。
