今回は、ExcelのPower Queryを使って、データ取り込みを整える方法を紹介します。
Power Queryを使う場面
Excelで毎月の売上表、出勤一覧、問い合わせ記録などを扱うとき、同じ形のファイルを繰り返し取り込むことがあります。毎回コピーして貼り付け、不要な列を消し、日付や列名を直す作業をしているなら、Power Queryを使う価値があります。
Power Queryは、外部データを取り込み、必要な形に整えてExcelへ読み込む機能です。元データに手を加えず、取り込み時の手順を記録して再利用できます。次回以降は更新操作で同じ加工を反映できるため、定型作業に向いています。
CSV、Excelブック、フォルダー内の複数ファイル、テキスト、データベースなど、さまざまな取り込み元に対応しています。まずは、手作業で毎回同じ整形をしている表から試すと使いどころが分かりやすくなります。
取り込み前に確認すること
Power Queryを使う前に、元データの状態を確認します。元データの列名、日付形式、空白行、不要な説明行、合計行の有無を見ておくと、後の設定がしやすくなります。
特に重要なのは、元データの形が今後も同じかどうかです。列名が毎回変わる、列の順番が変わる、ファイル名の付け方がばらばら、といった状態では、更新時にエラーが出ることがあります。
取り込みを安定させるには、次の点をそろえておきます。
- 見出し行の位置を固定する
- 列名を毎回同じにする
- 不要なメモ行を表の外に出す
- 日付やコードの形式をそろえる
- 合計行を元データに含めるか決める
元データを作る人と取り込む人が違う場合は、取り込みやすい形を共有しておくと、後の修正が少なくなります。
基本の取り込み手順
Power Queryは、Excelの「データ」タブから使います。「データの取得」を選び、取り込み元を指定します。CSVやExcelファイルを選ぶと、プレビュー画面が表示されます。
プレビューで内容を確認し、「データの変換」を選ぶとPower Queryエディターが開きます。ここで不要な列を削除したり、列名を変えたり、データ型を指定したりします。最後に「閉じて読み込む」を選ぶと、Excelシートへ結果が読み込まれます。
このとき、元データを直接編集しているわけではありません。Power Queryエディターでは、取り込み時に行う加工手順を作っています。右側の「適用したステップ」に作業履歴が残るため、後から手順を見直せます。
不要な行と列を整理する
取り込みデータには、集計に使わない列や、説明用の行が含まれていることがあります。Power Queryでは、不要な列を削除し、上部の不要な行をスキップできます。
列を削除するときは、今後も不要な列かどうかを確認します。今は使わない列でも、後から確認に必要になる場合があります。迷う列は残しておき、Excel側で非表示にする方法もあります。
不要な行を削除するときは、見出し行の位置に注意します。先頭に説明文があるCSVでは、上の行を削除してから、最初の行を見出しとして使う設定にします。見出しが正しく設定されると、後の加工が分かりやすくなります。
データ型を整える
Power Queryでは、列ごとにデータ型を指定できます。日付、数値、テキストなど、用途に合わせて型を設定します。データ型が合っていないと、Excelに読み込んだ後で並べ替えや計算が思った通りにならないことがあります。
コードや社員番号のように、先頭にゼロが付く値はテキストとして扱うのが基本です。数値にすると、先頭のゼロが消えることがあります。金額や数量は数値、受付日や締切日は日付として指定します。
データ型の変更でエラーが出る場合は、その列に想定外の文字が混ざっている可能性があります。エラー行を確認し、元データを直すか、Power Query側で不要な文字を取り除きます。
列名を分かりやすくする
外部データでは、列名が短すぎたり、英字の略称だけになっていたりすることがあります。Power Queryで列名を変更しておくと、Excelに読み込んだ後の確認がしやすくなります。
列名を変えるときは、後続の手順との関係に注意します。列名を変えた後で、その列を参照するステップがある場合、名前の変更が影響します。Power Queryでは作業順が大切です。
おすすめは、不要な列を整理し、見出しを確定した後で、列名を整える流れです。列名は、読み手が用途を判断できる形にします。「日付」だけでは複数の意味がある場合、「受付日」「処理日」「支払日」のように分けると扱いやすくなります。
フォルダーから複数ファイルを取り込む
Power Queryの便利な使い方に、フォルダー内の複数ファイルをまとめて取り込む方法があります。同じ形式のCSVやExcelファイルを毎月保存している場合、フォルダーを指定して結合できます。
この方法では、同じ列構成のファイルを同じフォルダーに入れておくことが重要です。余計なファイルや形式の違うファイルが混ざると、取り込みがうまくいかないことがあります。
運用しやすくするには、取り込み用フォルダーを分けます。作業中のファイル、確認済みのファイル、取り込み対象のファイルを同じ場所に置かないようにすると、誤って読み込むリスクを減らせます。
更新操作で再利用する
Power Queryで一度取り込み手順を作ると、元データが更新されたときに「更新」で再読み込みできます。毎回同じ加工を手作業で行う必要がなくなります。
更新前には、元ファイルの保存場所とファイル名を確認します。参照先が変わると、Power Queryは元データを見つけられません。共有フォルダーで運用する場合は、移動や名前変更のルールを決めておくと安定します。
更新後は、行数や主要な列の値を確認します。統計的な検証ではなく、空白が増えていないか、日付列にエラーが出ていないか、列名が崩れていないかを見るだけでも、取り込みミスに気づきやすくなります。
Excel表として読み込むと扱いやすい
Power Queryの結果は、Excelの表として読み込むと扱いやすくなります。表として読み込むことで、フィルター、並べ替え、数式参照が使いやすくなります。
ただし、Power Queryの出力範囲に直接手入力を加える運用は避けたほうが無難です。更新時に上書きされる可能性があります。メモや確認列が必要な場合は、別の表に分けるか、キーとなる列で参照する形を考えます。
読み込み先のシート名や表名も分かりやすくしておくと、後からブックを開いた人が確認しやすくなります。「Query1」のままにせず、「取込_問い合わせ」「取込_売上一覧」のように用途が分かる名前にします。
まとめ
ExcelのPower Queryは、繰り返し行うデータ取り込みと整形を扱いやすくする機能です。元データを残したまま、不要な行や列の削除、データ型の指定、列名の変更などを手順として保存できます。
使うときは、元データの形をそろえ、取り込み手順を小さく確認しながら作ります。更新後の確認も作業に含めることで、毎回のコピー貼り付けに頼らず、安定した取り込みを進めやすくなります。定期的に同じ形式のファイルを扱うなら、Power Queryで取り込み手順を作っておくと、次回以降の作業が見通しやすくなります。