Aさん Excelでデータの集計をする前に、元データが散らばっていて、かつ不備も多いので、データの収集や整形にかなり時間がとられてしまっています・・・。
できればマクロで自動化したいのですが、VBAを覚えるのは大変そうですし、何か良い方法はありませんかね？
森田 それなら、「パワークエリ」という機能がおすすめですよ！
マウス操作中心で一連のデータ収集/整形の手順を記録させ、以降は同じ手順の作業を自動化することが可能です。
では、詳細を解説していきますね。
解説動画
この記事の内容は下記の動画「【パワークエリ#1】「パワークエリ（Power Query）」とはどんな機能か？大枠の流れや手順、使い方まとめ」でも解説しています。
コメント欄の各プロセスの時間部分をクリックすると該当の解説へジャンプできますよ！
パワークエリ（Power Query）とは、一連のデータ収集/整形作業を自動化し、集計表の元データを作成する機能
パワークエリ（Power Query）とは、データソースを対象に一連の収集/整形作業を自動化し、集計表の元データとなるテーブルを作成する機能です。
データソースとは、収集/整形の対象となる各種表データのこと。
実務では、データが複数ファイルに散らばっている、あるいは各データに不備や使いにくいデータ構成になっている等が影響し、集計/分析の準備（前処理）に労力がかかることが多いもの。
こんな場合に、パワークエリを活用することで、不備がなく使いやすい集計用の元データを自動作成でき、不毛な前処理を時短でき、データ集計/分析作業全体の生産性アップが期待できます。
一例として、一連の複数手順をパワークエリで自動化したものが以下です。
- 同じブック内の「売上明細」テーブルのデータを取得
- 「商品コード」列を大文字へ変換
- 「原価」列を削除
- 「売上金額」列を追加
※「販売単価」列×「数量」列 - ②～④の結果をテーブルで新規ワークシートへ表示
従来のExcelでは、こうした一連の作業の自動化は、マクロ（VBA）を使うしかありませんでした。
しかし、マクロを作成するには、VBAというOffice専用のプログラミング言語を習得する必要があるため、習得のハードルは高めです。
一方、このパワークエリは、マウス操作中心のローコードで自動化する仕組みを構築できます。
よって、マクロ（VBA）と比べると習得のハードルは低く、ピボットテーブルよりやや難しい程度のため、前処理に時間がかかっている方こそ、ぜひ習得することがおすすめです。
なお、パワークエリで自動化する一連の作業は「クエリ」としてExcelブック内に記録されます。
データソースに指定したデータ側に追加や修正があった際は、このクエリを更新（ピボットテーブルと同じイメージ）することで、データソース側の更新情報がパワークエリの処理結果に反映される仕組みです。
パワークエリ（Power Query）を利用できるExcelのバージョン
パワークエリはExcel2016以降またはMicrosoft365ユーザーが利用可能です。
以前はバージョンがExcel2010（Professional Plus）とExcel2013の場合でもMicrosoft社の公式アドインを事前にインストールすることでパワークエリを利用できましたが、現在はダウンロードセンターの記事が非公開になり、公式アドインのダウンロードが不可になりました。
パワークエリ（Power Query）でできること
パワークエリでできることは多岐に渡りますが、大枠で次のような作業を記録できます。
- データの収集（テーブル/セル範囲、CSVファイル、Excelブック、フォルダー等）
- データの削除（不要な行列、重複行、空白行等）
- 表記ゆれの修正（英数字の大文字⇔小文字、置換、トリミング等）
- データ型の変換（数値、文字列、日付等）
- 計算列の追加（抽出/分割/連結、数値・日付計算、条件分岐等）
- データの転記/追加
- データのレイアウト変更（行列の入れ替え、ピボットの設定/解除等）
実務で頻出のケースの大部分には対応できると思います。
なお、パワークエリの標準機能では一部対応していない処理（英数カナの全角⇔半角の変換ができない等）もあります。
こうした場合、データソース側のデータを処理するか（Excelの場合）、パワークエリ内の別機能で補うといった工夫が必要ですので、ご留意ください。
クエリの新規作成ステップ
パワークエリは、ETLツールの一種だと言えます。
「ETL」とは、以下の3ステップの頭文字を略したものです。
この3ステップを行うことで、クエリの新規作成が可能です。
では、各ステップの詳細を見ていきましょう。
【Step1】データソースを取得/収集（Extract）
Step1は、パワークエリのデータソースにするデータを取得/収集します。
パワークエリのデータソース指定できるデータは、以下の通り種類が非常に多いです。
- テーブル/セル範囲 ※クエリを記録したブックの中
- Excelブック ※クエリを記録したブックとは別ファイル
- テキスト/CSVファイル
- XMLファイル
- JSONファイル
- PDFファイル
- フォルダー
- SharePointフォルダー
- 各種データベース（SQL ServerやAccess等）
- Azure
- Fabric・Power Platform
- オンラインサービス（SharePoint・Salesforce等）
- その他データソース（Web・画像・空のクエリ等）
Excelのバージョンによって、データソースに指定できるデータの種類が変わる（詳細はMicrosoft公式サポートの記事を参照）。
ExcelバージョンのPower Queryデータソース
しかし、これらすべてを覚える必要はありません。
どこで蓄積/更新されるデータを取得/収集することが頻出なのかを整理すると、多くのビジネスパーソンにとっては以下4種類のコマンドを押さえれば十分だと思います。
- テーブルまたは範囲から
- テキストまたはCSVから
- Excelブックから
- フォルダーから
該当の表データが蓄積/更新される場所に応じて、上記コマンドを使い分けましょう。
①「テーブルまたは範囲から」コマンド
「テーブルまたは範囲から」コマンドは、クエリを記録したブック内のテーブル/セル範囲をデータソースにする場合に活用します。
「テーブルまたは範囲から」コマンドの詳細は、以下の記事をご参照ください。
【パワークエリ】「テーブルまたは範囲から」の使い方｜同じブック内のデータを取得する方法【動画あり】
②「テキストまたはCSVから」コマンド
「テキストまたはCSVから」コマンドは、別ファイルのテキスト/CSVファイルをデータソースにする場合に活用します。
「テキストまたはCSVから」コマンドの詳細は、以下の記事をご参照ください。
【パワークエリ】「テキストまたはCSVから」の使い方｜CSV・テキストファイルのデータを取得する方法【動画あり】
③「Excelブックから」コマンド
「Excelブックから」コマンドは、別ブック（別ファイルのExcelブック）のシート/テーブルをデータソースにする場合に活用します。
「Excelブックから」コマンドの詳細は、以下の記事をご参照ください。
【パワークエリ】「Excelブックから」の使い方｜別ブックのテーブル・シート（単一・複数）を取得する方法【動画あり】
④「フォルダーから」コマンド
「フォルダーから」コマンドは、ローカルフォルダー内の別ファイルをデータソースにする場合に活用します。
「フォルダーから」コマンドの詳細は、以下の記事をご参照ください。
【パワークエリ】フォルダー内の複数ファイルの表データを一括で取得できる「フォルダーから」の使い方
【Step2】取得データを整形/加工
Step2は、Step1で取得したデータに対し、整形/加工を行います。
Step2の処理結果は、データソースのオリジナルのデータへ影響しない（ピボットテーブルと同様）。
このステップでは、「Power Queryエディター」という専用エディター上で作業手順（ステップ）を順番に記録していきます。
基本操作はワークシートに似ていますが、操作対象を選択し、リボン上のコマンドを実行することで、作業手順が記録される仕様です。
ワークシートと異なり、エディター上の操作対象がセルではなく「列」単位が基本となる。
なお、Power Queryエディターを開いている間は、該当ブックでのワークシート上の作業はできませんので、ご注意ください。
Step2で使用するコマンドが多岐に渡るため、クエリで自動化できる作業別に代表的なコマンドを例示します。
各コマンドの詳細は、リンク先の個別記事を参照のこと。
データの削除
Power Queryエディター上で取得データの不要な行列データの削除を自動化できます。
データの削除に役立つ代表的なコマンドは以下の通りです。
表記ゆれの修正
Power Queryエディター上で取得データの表記ゆれの修正を自動化できます。
表記ゆれの修正に役立つ代表的なコマンドは以下の通りです。
データ型の変換
Power Queryエディター上で取得データのデータ型の変換を自動化できます。
データ型の変換に役立つコマンドは「データ型の変更」です。
「データ型の変更」コマンドの詳細は、以下の記事をご参照ください。
【パワークエリ】対象の列のデータ型を一括で変換できる「データ型の変更」の使い方
計算列の追加
Power Queryエディター上で取得データへ、計算列の追加を自動化できます。
計算列の追加に役立つ代表的なコマンドは以下の通りです。
- 最初の文字：選択した列の各セルの先頭から指定文字数を抽出する
- 最後の文字：選択した列の各セルの末尾から指定文字数を抽出する
- 範囲：選択した列の各セルの指定位置から指定文字数を抽出する
- 区切り記号の前のテキスト：選択した列の各セルの区切り記号から前方の文字を抽出する
- 区切り記号の後のテキスト：選択した列の各セルの区切り記号から後方の文字を抽出する
- 区切り記号の間のテキスト：選択した列の各セルの開始区切り記号と終了区切り記号の間の文字を抽出する
- 文字数による分割：選択した列を指定文字数で複数列に分割する
- 区切り記号による分割：選択した列を区切り記号の位置で複数列に分割する
- 数字から数字以外による分割：選択した列を数字と数字以外の2列に分割する
- 数字以外から数字による分割：選択した列を数字以外と数字の2列に分割する
- 列のマージ：選択した複数列を1列に分割する
- 加算：選択した数値列の値に加算する
- （標準）減算：選択した数値列の値に減算する
- 乗算：選択した数値列の値に乗算する
- 除算：選択した数値列の値に除算する
- 剰余：選択した数値列の値を除算した剰余（余りの数）を計算する
- 四捨五入：選択した数値列の値を四捨五入する
- 切り上げ：選択した数値列の値を切り上げる
- 切り捨て：選択した数値列の値を切り捨てる
- 年：選択した日付列の値から年（西暦）を整数で取得する
- 月：選択した日付列の値から月を整数で取得する
- 日：選択した日付列の値から日を整数で取得する
- 曜日：選択した日付列の値から曜日名を取得する
- 時：選択した時刻列の値から時を整数で取得する
- 分：選択した時刻列の値から分を整数で取得する
- 秒：選択した時刻列の値から秒を整数で取得する
- 日数の減算：選択した2列の日付列の値から日数を計算する
- （時刻）減算：選択した2列の時刻列の値から経過時間を計算する
- インデックス列：テーブルに通し番号（0か1始まり）の新しい列を追加する
- 条件列：テーブルに条件分岐する新しい列を追加する
- カスタム列：テーブルに任意の数式をセットした新しい列を追加する
データの転記/追加
Power Queryエディター上で取得データへ、別表の転記や追加を自動化できます。
データの転記や追加に役立つ代表的なコマンドは以下の通りです。
データのレイアウト変更
Power Queryエディター上で取得データのレイアウトの変更を自動化できます。
レイアウトの変更に役立つ代表的なコマンドは以下の通りです。
【参考】Power Queryエディターの画面構成
Power Queryエディターの画面構成は以下の通りです。
- リボン
- タブ
- コマンド
- 数式バー
- ナビゲーションウィンドウ
- プレビューウィンドウ
- ヘッダー
- 列
- 行
- データ型
- フィルターボタン
- 「クエリの設定」ウィンドウ
- クエリ名
- 適用したステップ
- ステップ
補足が必要な構成要素について、順番に解説していきます。
数式バー
ワークシートと同じく、数式バーがあります。
各ステップがどんな処理がされているか、「M」という言語で記録されています。
任意のコマンドを押下する等の操作をすれば、自動的に基本的に記録されているものなので、最初はM言語を触らなくて問題ありません。
パワークエリに慣れてきたら、数式バー上でM言語を直接編集するケースも出てきます。
この数式バーの表示/非表示を変更したい場合は、以下の手順となります。
- リボン「表示」タブをクリック
- 「数式バー」のチェックをON/OFF
プレビューウィンドウ
プレビューウィンドウは、各ステップの実行結果を確認する領域です。
また、コマンドを実行する対象の列の選択や、このウィンドウ上の右クリックメニュー等からコマンドを実行するケースもあります。
「クエリの設定」ウィンドウ
「クエリの設定」ウィンドウは、クエリ名やクエリ内の作業手順（ステップ）を確認/編集する領域です。
クエリ名は、文字通り「クエリの名前」です。
なお、「クエリ」はパワークエリに限らず、データベースを扱うシステム/ツールに共通する用語です。
ざっくり説明すると、クエリとは「データベースに対しての命令文（検索、更新、削除、抽出など）」を指します。
ここでは、クエリのことを「一連の作業手順の総称」くらいに理解しておけば十分です。
そして、クエリに記録された各作業手順（ステップ）が、「適用したステップ」へ記録されていきます。
この各ステップは、上から下へ1つずつ順番に実行される仕様です。
なお、クエリ名も各ステップも自由にリネームできます。
ステップ名は実行したコマンドに応じて自動で設定される。
ステップについては、該当ステップを選択して「F2」キーを押すか、右クリック→「名前の変更」でリネームが可能です。
後から各ステップの内容の編集や削除することも可能。
標準のステップ名が分かりにくい場合等、必要に応じて変更してください。
【Step3】整形/加工結果を読み込み（Load）
Step3は、Step2の整形/加工結果の読み込み先を設定します。
Step3まで完了することで、ようやくエディター上の処理結果をワークシート上で表示する、あるいはピボットテーブル等で集計/分析が可能となります。
データの読み込み先の設定手順は、以下の通りです。
- リボン「ホーム」タブをクリック
- 「閉じて読み込む」の下側をクリック
- 「閉じて次に読み込む」をクリック
- 任意の読み込み先を選択
- 「OK」をクリック
手順②で「閉じて読み込む」の上側をクリックした場合、読み込み先がテーブル（新規ワークシート）となる。
手順④で「テーブル」を選択した際、手順⑤の前にテーブルの表示先を選択すること（新規ワークシートか既存ワークシートか）。
※既存ワークシートの場合、表示先のセル番地も指定（テーブルの左上隅のセル）。
なお、手順④の「データのインポート」ダイアログ上で選択できるデータの読み込み先は4種類あります。
- テーブル
- ピボットテーブルレポート
- ピボットグラフ
- 接続の作成のみ
このうち、実務で良く使うのは「テーブル」・「接続の作成のみ」の2種類です。
「テーブル」を選択した場合、パワークエリを実行したExcelブック内のワークシート上（新規or既存）にStep2の処理結果をテーブルとして出力します。
「接続の作成のみ」を選択した場合、Step2の処理結果をExcelブック内のワークシート上に表示せず、内部に保持します。
これは、Excelの行数を超えたデータを扱う場合や、別作業用にクエリを仮作成する場合等に便利です。
いずれも、クエリ作成後はエディターが閉じてワークシートに戻りますが、ワークシート右側に「クエリと接続」ウィンドウが表示されます。
「クエリと接続」ウィンドウ上でのクエリの操作方法は、以下の記事をご参照ください。
【パワークエリ】作成したクエリの編集・削除・各種設定変更ができる「クエリと接続」ウィンドウの使い方
【参考】「クエリと接続」ウィンドウの画面構成
「クエリと接続」ウィンドウの画面構成は以下の通りです。
「クエリと接続」ウィンドウの「接続」タブは、データモデル（パワーピボット）で使用。
クエリの更新手順
データソース側のデータを更新した後、クエリを更新したい場合、対象のクエリが複数あるか個別なのかで手順が異なります。
すべてのクエリを更新する場合
すべてのクエリを更新する場合は、以下の手順となります。
- リボン「データ」タブをクリック
- 「すべて更新」の上側をクリック
手順①②は「Ctrl + Alt + F5」のショートカットキーで行うことも可能。
単一のクエリを更新する場合
単一のクエリを更新する場合は、「クエリと接続」ウィンドウ上の該当クエリ名の右横にある「最新の情報に更新」をクリックします。
クエリの読み込み先がテーブルやピボットテーブル/グラフの場合、該当のテーブルやピボットテーブル/グラフのセル範囲を選択中に「Alt + F5」のショートカットキーで更新することも可能。
クエリを記録したExcelブックを再度開いた場合の操作
クエリ作成後、改めてクエリを記録したExcelブックを開いた場合、以下のような「セキュリティの警告」メッセージが表示される場合があります。
セキュリティの警告
外部データ接続が無効になっています
クエリの更新や編集等を行いたい場合は、以下の手順で信頼済みドキュメントにしましょう。
- 「コンテンツの有効化」をクリック
- 「はい」をクリック
なお、再度開いたExcelブックの場合、デフォルトでは「クエリと接続」ウィンドウが非表示状態となります。
この場合、以下の手順でウィンドウの再表示が可能です。
- リボン「データ」タブをクリック
- 「クエリと接続」をクリック
さいごに
いかがでしたでしょうか？
パワークエリ（Power Query）は、マウス操作中心のローコードで、既存データの収集や整形に関する一連の作業手順を記録でき、集計表の元データ作成を自動化できて大変便利な機能です。
ピボットテーブルや関数等での集計/分析の前処理に苦労している方は、ぜひ覚えると生産性アップや時短に役立ちますので、ぜひ覚えて使ってみてください！
なお、パワークエリの各種テクニックは、私の拙著でも体系的に解説していますので、こちらも参考にしてみてください。
ご参考になれば幸いですm(_ _)m
森田 パワークエリ単独では、不備がなく使いやすい集計用の元データを作成することまでが対応範囲です。
この元データを用いて集計/分析するのは関数でも良いのですが、私がおすすめするのは、同じマウス操作中心で集計/分析が可能な「ピボットテーブル」や「パワーピボット」との組み合わせです。
データの収集～分析までの一連のプロセスの自動化を、パワークエリ+ピボットテーブル（パワーピボット）で行えると、より楽に時短できるので、ぜひともセットで習得してみてください！
