Aさん Excelでデータの集計をする前に、元データが散らばっていて、かつ不備も多いので、データの収集や整形にかなり時間がとられてしまっています・・・。
できればマクロで自動化したいのですが、VBAを覚えるのは大変そうですし、何か良い方法はありませんかね？
森田 それなら、「パワークエリ」という機能がおすすめですよ！
マウス操作中心で一連のデータ収集/整形の手順を記録させ、以降は同じ手順の作業を自動化することが可能です。
では、詳細を解説していきますね。
解説動画：【パワークエリ#1】「パワークエリ（Power Query）」とはどんな機能か？大枠の流れや手順、使い方まとめ
この記事の内容は下記の動画でも解説しています。
コメント欄の各プロセスの時間部分をクリックすると該当の解説へジャンプできますよ！
Excelの「パワークエリ」とは
パワークエリとは、データソースに指定したデータの収集/整形の一連の定型作業を自動化できる機能です。
データソースとは、使用するデータの提供元のこと（元データ）。
実務では、データが複数ファイルに散らばっている、あるいは各データに不備や使いにくいデータ構成になっている等が影響し、集計/分析の準備（前処理）に労力がかかることが多いもの。
こんな場合に、パワークエリを活用することで、不備がなく使いやすい集計用の元データを自動作成でき、不毛な前処理を時短でき、データ集計/分析作業全体の生産性アップが期待できます。
一例として、一連の複数手順をパワークエリで自動化したものが以下です。
- 同じブック内の「売上明細」テーブルのデータを取得
- 「商品コード」列を大文字へ変換
- 「原価」列を削除
- 「売上金額」列を追加
※「販売単価」列×「数量」列 - ②~④の結果をテーブルで新規ワークシートへ表示
従来のExcelでは、こうした一連の作業の自動化は、マクロ（VBA）を使うしかありませんでした。
しかし、マクロを作成するには、VBAというOffice専用のプログラミング言語を習得する必要があるため、習得のハードルは高めです。
一方、このパワークエリは、マウス操作中心のローコードで自動化する仕組みを構築できます。
よって、マクロ（VBA）と比べると習得のハードルは低め（ピボットテーブルよりやや難しいレベル）のため、前処理に時間がかかっている方こそ、ぜひ習得することがおすすめです。
なお、パワークエリで自動化する一連の作業は「クエリ」としてExcelブック内に記録されます。
データソースに指定したデータ側に追加や修正があった際は、このクエリを更新（ピボットテーブルと同じイメージ）することで、データソース側の更新情報がパワークエリの処理結果に反映される仕組みです。
テーブルの概要については、以下の記事をご参照ください。
はじめに 本題に入る前に、この記事がおすすめな方を挙げてみます。 Excelでデータの集計/分析作業を行うこと …
パワークエリを利用できるExcelのバージョン
パワークエリはExcel2016以降から標準機能となったため、Excel2016以降あるいはMicrosoft365ユーザーは、Excel上で追加の設定をすることなく利用可能です。
バージョンがExcel2010（Professional Plus）とExcel2013の場合、Microsoft社の公式アドインを事前にインストールすることでパワークエリを利用できるようになります。
Excel2010のProfessional Plus以外のバージョン、またはExcel2007以前のバージョンは利用不可。
該当の場合は、以下URLからダウンロードおよびインストールをしておきましょう。
バージョンがExcel2010（Professional Plus）とExcel2013の方は、以下のダウンロードセンター（Microsoft社）からアドインをインストールしてください。
Microsoft Power Query for Excel は、データの検出、アクセスおよびコラボレーションを簡略化することで、Excel のセルフサービス ビジネス インテリジェンス環境を拡張する Excel アドインです。
パワークエリでできること
パワークエリでできることは多岐に渡りますが、大枠で次のような作業を記録できます。
- データの収集（テーブル/セル範囲、CSVファイル、Excelブック、フォルダー等）
- データの削除（不要な行列、重複行、空白行等）
- 表記ゆれの修正（英数字の大文字⇔小文字、置換、トリミング等）
- データ型の変換（数値、文字列、日付等）
- データの抽出/分割/連結
- 計算列の追加（四則演算、日付計算、条件分岐等）
- データの転記/追加
- データのレイアウト変更（行列の入れ替え、ピボットの設定/解除等）
実務で頻出のケースの大部分には対応できると思います。
なお、パワークエリの標準機能では一部対応していない処理（英数カナの全角⇔半角の変換ができない等）もあります。
こうした場合、データソース側のデータを処理するか（Excelの場合）、パワークエリ内の別機能で補うといった工夫が必要ですので、ご留意ください。
クエリの新規作成ステップ
パワークエリは、ETLツールの一種だと言えます。
「ETL」とは、以下の3ステップの頭文字を略したものです。
この3ステップを行うことで、クエリの新規作成が可能です。
では、各ステップの詳細を見ていきましょう。
【STEP1】データソースを取得/収集（Extract）
STEP1は、パワークエリのデータソースにするデータを取得/収集します。
パワークエリのデータソースに指定できるデータの種類は非常に多く、2024年11月現在では以下の通りです。
- テーブル/セル範囲 ※クエリを記録したブックの中
- Excelブック ※クエリを記録したブックとは別ファイル
- テキスト/CSVファイル
- XMLファイル
- JSONファイル
- PDFファイル
- フォルダー
- SQL Serverデータベース
- Microsoft Accessデータベース
- Analysis Service
- SQL Server Analysis Serviceデータベース（インポート）
- Azure Data Lake Storage Gen2
- Azure Data Explorer
- レイクハウス
- ウェアハウス
- データフロー
- Dataverse
- Web
- ODataフィールド
- ODBC
- OLE DB
- 画像
- 空のクエリ
しかし、これらすべてを覚える必要はありません。
どこで蓄積/更新されるデータを取得/収集することが頻出なのかを整理すると、多くのビジネスパーソンにとっては以下4種類のコマンドを押さえれば十分だと思います。
- テーブルまたは範囲から
※クエリを記録したブック内のテーブル/セル範囲 - テキストまたはCSVから
※別ファイルのテキスト/CSVファイル - Excelブックから
※別ファイルのExcelブックのシート/テーブル - フォルダーから
※ローカルフォルダー内の別ファイル
該当の表データの蓄積/更新される場所に応じて、上記コマンドを使い分けましょう。
「テーブルまたは範囲から」・「テキストまたはCSVから」・「Excelブックから」・「フォルダーから」の詳細は、以下の記事をご参照ください。
はじめに 本題に入る前に、この記事がおすすめな方を挙げてみます。 同じブック内の表データ（テーブルまたはセル範 …
はじめに 本題に入る前に、この記事がおすすめな方を挙げてみます。 テキストファイルまたはCSVファイルのデータ …
はじめに 本題に入る前に、この記事がおすすめな方を挙げてみます。 別ブックのデータを整形・加工したい方 データ …
はじめに 本題に入る前に、この記事がおすすめな方を挙げてみます。 別ブックの複数のテーブル/シートをまとめて整 …
はじめに 本題に入る前に、この記事がおすすめな方を挙げてみます。 フォルダー内の複数ファイルのテーブル/シート …
なお、Excelのバージョンによって、データソースに指定できるデータの種類が変わりますので、ご注意ください。
Excelのバージョン別のパワークエリのデータデータソースの種類は、Microsoft公式サポートの記事をご参照ください。
Microsoft Office SKU に含まれる Get & Transform (Power Query) 機能について詳しくは、こちらをご覧ください。
【STEP2】取得データを整形/加工
STEP2は、STEP1で取得したデータに対し、整形/加工を行います。
STEP2の処理結果は、データソースのオリジナルのデータへ影響しない（ピボットテーブルと同様）。
このステップでは、「Power Queryエディター」という専用エディター上で作業手順（ステップ）を記録していきます。
基本操作はワークシートに似ていますが、操作対象を選択し、リボン上のコマンドを実行することで、作業手順が記録される仕様です。
ワークシートと異なり、エディター上の操作対象がセルではなく「列」単位が基本となる。
なお、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の行数を超えたデータ、または別作業用にクエリを仮作成する場合等で便利です。
ちなみに、いずれもクエリ作成後はエディターが閉じてワークシートに戻りますが、ワークシート右側に「クエリと接続」ウィンドウが表示されます。
「クエリと接続」ウィンドウ上のクエリの基本操作の詳細は、以下の記事をご参照ください。
解説動画：【パワークエリ#9】クエリの基本操作5選 – 作成したクエリの各種編集や削除、コピペの方法をマスターする（編集、削除、名前の変更、読み込み先、コピー＆貼り付け） この記事の内容は下記の動画でも解説し …
「クエリと接続」ウィンドウの画面構成
「クエリと接続」ウィンドウの画面構成は以下の通りです。
「クエリと接続」ウィンドウの「接続」タブは、パワーピボットを使用する際に使うことが多い。
パワーピボットの詳細は、以下の記事をご参照ください。
はじめに 本題に入る前に、この記事がおすすめな方を挙げてみます。 Excelで複数種類のテーブルでデータ集計/ …
クエリの更新手順
クエリ作成後、データソース側で追加/更新クエリを更新したい場合、対象のクエリが複数あるか個別なのかで手順が異なります。
すべてのクエリを更新する場合
すべてのクエリを更新する場合は、以下の手順となります。
- リボン「データ」タブをクリック
- 「すべて更新」の上側をクリック
手順①②は「Ctrl」+「Alt」+「F5」のショートカットキーで行うことも可能。
個別のクエリを更新する場合
個別のクエリを更新する場合は、「クエリと接続」ウィンドウ上の該当クエリ名の右横にある「最新の情報に更新」をクリックします。
クエリの読み込み先がテーブルやピボットテーブル/グラフの場合、該当のテーブルやピボットテーブル/グラフのセル範囲を選択中に「Alt」+「F5」のショートカットキーで更新することも可能。
クエリを記録したExcelブックを再度開いた場合の操作
クエリ作成後、改めてクエリを記録したExcelブックを開いた場合、以下のような「セキュリティの警告」メッセージが表示される場合があります。
セキュリティの警告
外部データ接続が無効になっています
クエリの更新や編集等を行いたい場合は、以下の手順で信頼済みドキュメントにしましょう。
- 「コンテンツの有効か」をクリック
- 「はい」をクリック
なお、再度開いたExcelブックの場合、デフォルトでは「クエリと接続」ウィンドウが非表示状態となります。
この場合、以下の手順でウィンドウの再表示が可能です。
- リボン「データ」タブをクリック
- 「クエリと接続」をクリック
さいごに
いかがでしたでしょうか？
パワークエリは、マウス操作中心のローコードで、既存データの収集や整形に関する一連の作業手順を記録でき、自動化できて大変便利な機能です。
ピボットテーブルや関数等での集計/分析の前処理に苦労している方は、ぜひ覚えると生産性アップや時短に役立ちますので、ぜひ覚えて使ってみてください！
なお、パワークエリの各種テクニックは、私の拙著でも体系的に解説していますので、こちらも参考にしてみてください。
また、よりコアな情報を発信していくために「公式LINE」を開始しましたので、ご興味があれば以下バナーから友だち追加をお願いします！
特典として、Excelワークシート関数一覧（計22スライド）やExcelショートカットキー一覧（計25スライド）等をプレゼント！
ご参考になれば幸いですm(_ _)m
森田 パワークエリ単独では、不備がなく使いやすい集計用の元データを作成することまでが対応範囲です。
この元データを用いて集計/分析するのは関数でも良いのですが、私がおすすめするのは、同じマウス操作中心で集計/分析が可能な「ピボットテーブル」や「パワーピボット」との組み合わせです。
データの収集~分析までの一連のプロセスの自動化を、パワークエリ+ピボットテーブル（パワーピボット）で行えると、より楽に時短できるので、ぜひともセットで習得してみてください！
