今回は、ExcelのPower Queryでデータ取り込みを整え、あとから扱いやすい表にする方法を紹介します。
Power Queryは取り込み作業を記録して再利用できる
Excelで毎月の売上表、在庫一覧、申請データなどを扱うとき、同じような整形作業を繰り返すことがあります。不要な列を消す、見出しを直す、日付の形式をそろえる、空白行を削除する、といった作業です。
Power Queryを使うと、こうした手順を「クエリ」として残せます。次回以降は元データを差し替えて更新するだけで、同じ整形を反映できます。手作業で毎回直すよりも、取り込みから整形までの流れを一定にしやすいのが利点です。
Power Queryは、Excelの「データ」タブから使えます。CSV、Excelブック、フォルダー内のファイル、テーブルなど、さまざまな形式を取り込めます。最初は難しく感じるかもしれませんが、基本は「読み込む」「不要なものを削る」「型をそろえる」「読み込む場所を決める」という流れです。
取り込み前に元データの特徴を確認する
Power Queryを使う前に、元データの形を確認しておくと作業が進めやすくなります。特に、毎回同じ列名で出力されるデータか、不要な説明行が入るか、日付や金額の形式が安定しているかを見ておきます。
確認したいポイントは次のとおりです。
- 見出し行がどこにあるか
- 空白行や合計行が混ざっていないか
- 日付、数値、文字列の形式がそろっているか
- 毎回列名が変わらないか
- 同じフォルダーに不要なファイルが混ざらないか
この確認をしておくと、Power
Query側でどの手順を入れるべきか判断しやすくなります。元データが毎回変わる部分と、変わらない部分を分けて考えることが大切です。
不要な列と行は早い段階で削除する
取り込んだデータに使わない列が多い場合は、早めに削除します。不要な列を残したまま作業すると、後続の手順が見づらくなり、列名変更や型変換の対象も増えます。
たとえば、分析に使う列が「日付」「商品名」「担当者」「金額」だけなら、それ以外の列はクエリエディター上で削除します。列を削除する方法には、選んだ列を消す方法と、必要な列だけを残す方法があります。運用では、必要な列だけを残す方法が扱いやすい場面があります。元データに新しい列が追加されても、クエリの出力に不要な列が混ざりにくいためです。
行についても同じです。先頭に説明文が入っているCSVや、末尾に合計行がある表は、必要なデータ行だけが残るように整えます。集計用の合計行が混ざったまま読み込むと、ピボットテーブルや関数で二重計上の原因になることがあります。
列名は後工程で分かる名前にする
Power Queryでは列名を変更できます。元データの列名が「列1」「項目A」「金額税込」など分かりにくい場合は、後で使いやすい名前に直しておきます。
列名を付けるときは、短く、意味が分かり、同じ表の中で表記がそろう名前にします。「売上金額」「売上額」「金額」が混在すると、後から数式やピボットテーブルを作るときに迷います。列名をそろえるだけでも、集計作業の見通しが良くなります。
また、列名に余分なスペースが入っていると、参照時に気づきにくいミスになります。Power
Query上で列名を整えておくと、Excelシート側の表も扱いやすくなります。
データ型をそろえる
Power Queryでは、列ごとにデータ型を設定できます。日付は日付、金額や数量は数値、コードや電話番号は文字列として扱います。
ここで注意したいのは、数字に見えるものをすべて数値にしないことです。社員番号、商品コード、郵便番号のように、先頭のゼロが意味を持つデータは文字列にします。数値に変換すると先頭のゼロが消え、元に戻すのが面倒になります。
日付も確認が必要です。元データの形式によっては、日付として認識されないことがあります。Power
Queryで日付型に変換できるか確認し、エラーが出る場合は元データの表記を見直します。
エラー行は原因を見てから処理する
型変換をしたときにエラーが出ることがあります。空欄、記号、文字入りの金額などが原因です。エラー行をすぐ削除するのではなく、まず原因を確認します。
入力ミスが原因なら元データ側を直すほうがよい場合があります。不要なメモ行が混ざっているだけなら、Power
Query側で除外する手順を入れます。エラーの扱いを決めておくと、更新時に同じ問題が出ても対応しやすくなります。
フォルダー取り込みはファイル名のルールが大切
Power Queryでは、フォルダー内の複数ファイルをまとめて取り込めます。毎月同じ形式のCSVを追加していく場合に便利です。
ただし、フォルダー取り込みでは、対象外のファイルが混ざると処理が乱れます。作業用のコピー、古い形式のファイル、説明資料などは同じフォルダーに入れないようにします。ファイル名に年月や区分を入れておくと、後から確認しやすくなります。
フォルダー取り込みを使うときは、次のようなルールを決めておくと安定します。
- 取り込み専用フォルダーを用意する
- 対象ファイルだけを入れる
- ファイル名の形式をそろえる
- 列構成が違うファイルを混ぜない
- 処理済みファイルを移動する場合は移動先も決める
このような整理をしておくと、更新作業で余計な確認が減ります。
読み込み先は目的に合わせて選ぶ
Power Queryで整えたデータは、Excelシートにテーブルとして読み込むことも、接続だけ作成することもできます。
シート上で一覧を確認したい場合は、テーブルとして読み込みます。ピボットテーブルやグラフの元データにしたい場合も、テーブル読み込みが分かりやすいです。一方、複数のクエリを組み合わせる途中段階のデータは、接続のみで残すとシートが増えすぎません。
クエリが増えてきたら、名前も整えます。「クエリ1」「テーブル2」のままだと、どれが元データで、どれが集計用なのか分かりにくくなります。「売上CSV_取込」「商品マスタ_整形」「月次集計」のように役割が分かる名前にすると管理しやすくなります。
更新前提で作る
Power Queryは、1回だけ整形するための機能ではありません。更新して使う前提で作ると便利です。そのため、今だけ合えばよい手順ではなく、次回のデータでも同じように動く手順を考えます。
たとえば、特定の行番号を削除するより、「空白行を削除する」「列名が空の行を除外する」といった条件で処理したほうが安定する場合があります。列の順番が変わる可能性があるなら、列名を基準にした操作を意識します。
また、元データの保存場所を変えると更新できなくなることがあります。チームで使うファイルなら、共有フォルダー上の決まった場所に元データを置き、保存場所のルールも合わせて決めます。
処理手順は後から見直せる
Power Queryの画面右側には、適用したステップが並びます。ここを見ると、どの順番で処理したか確認できます。不要なステップを削除したり、途中のステップを選んで状態を確認したりできます。
ステップ名は自動で付きますが、重要な処理は名前を変えておくと後から分かりやすくなります。たとえば「変更された型」だけが並んでいると、何を変えたのか読み取りにくくなります。「日付と金額の型を設定」のようにしておくと、引き継ぎ時にも役立ちます。
複雑な処理を作る場合でも、最初から多くの手順を詰め込む必要はありません。取り込み、列整理、型変換、不要行削除、読み込みの順に確認しながら進めると、問題が出た場所を見つけやすくなります。
まとめ
ExcelのPower Queryは、データ取り込みと整形を安定させるために使える機能です。不要な列や行を削除し、列名とデータ型をそろえ、更新しやすい形で読み込むことで、日々の集計や確認作業が進めやすくなります。
特に、同じ形式のファイルを繰り返し扱う業務では、手作業をクエリに置き換える効果があります。元データの特徴を確認し、フォルダーやファイル名のルールを整え、ステップ名も見直しておくと、後から修正しやすい仕組みになります。
Power Queryでは、取り込み後の表をどう使うかを考えて整えることが大切です。最終的な集計表や確認表を意識して、必要なデータだけが残る形にしていきましょう。