インハムシステムの自動化ノート

現役エンジニアが、Excel VBA・Google Apps Scriptによる業務効率化のヒントをお届けします。

【Excel】Power Queryの基本操作|VBAを使わずにデータを整形する

毎月届くCSVを開いて、不要な列を消して、日付の形式を直して、複数ファイルを1枚に貼り付ける。この一連の作業を毎月繰り返しているなら、Power Queryを試す価値があります。

Power QueryはExcelに標準で入っているデータ整形機能です。一度手順を記録すれば、翌月からは「更新」ボタン1つで同じ整形が再現されます。 マクロと違ってコードを書く必要がなく、ファイルも通常の.xlsxのまま扱えます。

この記事では、Power Queryの基本操作と、VBAとの使い分けについて解説します。


Power Queryとは何か

ひとことで言えば「データを取り込んで整形する手順を記録しておく仕組み」です。

  • 取り込み元: CSV、テキスト、Excelファイル、フォルダ、Webページ、データベースなど
  • 整形の内容: 列の削除・分割・結合、行のフィルター、型変換、集計、テーブル同士の結合
  • 出力先: シート上のテーブル、またはピボットテーブル

重要なのは、元データを一切書き換えないことです。整形は取り込んだ後の複製に対して行われるため、元のCSVやファイルはそのまま残ります。

Excel 2016以降なら「データ」タブに標準で入っています。Excel 2010・2013では、Microsoftが配布していたアドインの導入が必要です。


基本操作1:CSVを取り込んで整形する

「データ」タブ →「データの取得」→「ファイルから」→「テキストまたはCSVから」でファイルを選びます。

プレビュー画面が出たら、右下の「読み込み」ではなく 「データの変換」 をクリックします。ここがPower Queryエディターの入口です。

エディターが開いたら、次のような操作を行います。

やりたいこと 操作
不要な列を消す 列を右クリック →「削除」
必要な列だけ残す 列を選択 →「他の列の削除」
1行目を見出しにする 「1行目をヘッダーとして使用」
日付の形式を直す 列見出しの型アイコン →「日付」
空白行を消す 「行の削除」→「空白行の削除」
特定の条件で絞る 列見出しの▼から条件を指定
列を分ける 「列の分割」→「区切り記号による分割」

右側の「適用したステップ」に、行った操作が順番に記録されていきます。このステップの並びこそがPower Queryの本体です。 間違えたステップは×印で削除でき、後から順序を入れ替えることもできます。

整形が終わったら「閉じて読み込む」を押すと、シートにテーブルとして出力されます。

翌月、同じ形式の新しいCSVに差し替えたいときは、「データ」タブの「すべて更新」を押すだけです。記録したステップが同じ順序で再実行されます。


基本操作2:フォルダ内の全ファイルを1つに結合する

Power Queryが最も威力を発揮する場面です。「支店ごとに分かれた12個のCSVを1枚にまとめる」といった作業が、数クリックで終わります。

  1. 「データの取得」→「ファイルから」→「フォルダーから」
  2. 対象フォルダを選ぶと、中のファイル一覧が表示される
  3. 「結合」→「データの結合と変換」をクリック
  4. 見本にするファイルを選んで「OK」

これだけで、フォルダ内の全ファイルが縦に連結された1つの表になります。

この方法の利点は、フォルダにファイルを追加するだけで結果に反映されることです。13個目のCSVを置いて「すべて更新」を押せば、自動的に取り込まれます。マクロのように「ファイル数が変わったら書き換える」必要がありません。

なお結合時に「Source.Name」という列が追加され、どのファイル由来の行かが分かるようになっています。不要なら削除できますが、集計の切り口として使えることが多いため残しておくと便利です。


基本操作3:クエリのマージ(VLOOKUPの代替)

2つの表を突き合わせる処理も、Power Queryで完結できます。

  1. 両方の表をそれぞれクエリとして取り込む
  2. 片方のクエリを選び、「ホーム」タブ →「クエリのマージ」
  3. 結合キーになる列を、両方の表で選択する
  4. 結合の種類を選んで「OK」
  5. 追加された列の展開ボタン(⇔アイコン)から、必要な列だけを選ぶ

結合の種類は用途で選びます。

種類 結果
左外部 左の表を全件残し、一致するものだけ右の値が付く(VLOOKUPに近い)
内部 両方に存在する行だけ残す
左反 右の表に存在しない行だけ残す(照合漏れの抽出に便利)

「左反」は実務で重宝します。 「マスタに登録されていない商品コードを探す」「入金のない請求を洗い出す」といった突合作業が、数式なしで行えます。


更新を自動化する

ファイルを開いたときに自動更新する

クエリを右クリック →「プロパティ」→「ファイルを開くときにデータを更新する」にチェックを入れます。

VBAから更新する

他のマクロ処理と組み合わせる場合は、VBAから更新を呼び出せます。

Sub RefreshQueries()
    Application.ScreenUpdating = False

    ' ブック内の全接続を更新する
    ThisWorkbook.RefreshAll

    ' 更新完了を待ってから次の処理へ進む
    DoEvents

    Application.ScreenUpdating = True
    MsgBox "データを更新しました。"
End Sub

特定のクエリだけを、完了を待って更新したい場合はテーブル経由で指定します。

Sub RefreshOneQuery()
    Dim lo As ListObject
    Set lo = ThisWorkbook.Sheets("集計").ListObjects("売上データ")

    ' BackgroundQuery:=False で更新完了まで待つ
    lo.QueryTable.Refresh BackgroundQuery:=False

    MsgBox "「売上データ」を更新しました。"
End Sub

BackgroundQuery:=False を指定しないと、更新が終わる前に次の処理へ進んでしまい、古いデータで集計してしまうことがあります。


VBAとの使い分け

どちらも自動化の手段ですが、得意分野がはっきり分かれます。

観点 Power Query VBA
データの取り込み・整形 得意 書けるが手間がかかる
複数ファイルの結合 数クリックで完了 ループ処理を自作する必要がある
表同士の突合 マージ機能で完結 Dictionary等で実装
セルの書式設定 不可 得意
他アプリとの連携(メール等) 不可 得意
条件分岐を含む複雑な処理 苦手 得意
ファイル形式 .xlsxのまま .xlsmが必要
引き継ぎやすさ 画面で手順を確認できる コードを読める人が必要

判断の目安としては、「データを整えるだけ」ならPower Query、「整えた後に何かをする」ならVBAです。

現実的には併用が最も効率的です。Power Queryでデータを整形してテーブルに出力し、そのテーブルをVBAから読んで帳票を作る、という分担にすると、どちらも短いコードで済みます。


つまずきやすい点

ファイルのパスが変わると壊れる

取り込み元のパスはクエリに記録されています。フォルダを移動したりファイル名を変えたりすると更新に失敗します。「データ」タブ →「データの取得」→「データソースの設定」から変更できます。

共有すると相手の環境で動かない

C:\Users\自分の名前\...のようなローカルパスを参照していると、他の人が開いても更新できません。共有する場合は、取り込み元をネットワークドライブやSharePointなど、全員が同じパスでアクセスできる場所に置きます。

型の自動変換が裏目に出る

Power Queryは取り込み時に列の型を推測します。「0001」のような先頭ゼロ付きコードが数値の「1」に変換されてしまうことがあります。「適用したステップ」の「変更された型」を削除するか、その列を「テキスト」に指定し直してください。

更新に時間がかかる

数十万行を扱うと更新に数分かかることがあります。不要な列の削除と行の絞り込みをできるだけ最初のステップで行うと、後続の処理が軽くなります。


まとめ

  • Power Queryは「整形手順を記録して再実行する」仕組み
  • フォルダ結合とクエリのマージだけでも、手作業の大部分が置き換わる
  • 元データを書き換えないため、失敗してもやり直せる
  • VBAとは競合せず、整形はPower Query、その先の処理はVBA、と分担できる

「毎月同じ手順でデータを整えている」という作業が1つでもあれば、まずはそれをPower Queryに置き換えてみるのが分かりやすい入口になります。


他の記事でもExcelやスプレッドシートの業務効率化テクニックを紹介していますので、ぜひチェックしてみてください。