日々の業務で扱うExcelファイルが多くなると、データの集約や管理に膨大な時間と手間がかかります。そんな課題を解決するのが「Power Query」です。
Power Queryは、複数のExcelファイルを自動で集約・整形できるノーコードツールで、プログラミング不要で直感的に操作できます。
本コラムでは、Power Queryの基本機能から、実践的なデータ統合手順、注意点までを詳しく解説しています。
なお、ペパコミ株式会社では、脱Excelとして「kintone」を活用した業務支援を行っています。業務効率化にお悩みの方は、ぜひ以下からお気軽にご相談ください。
Excel Power Queryで複数ファイルを自動集約する方法
Excelには、日々の業務を自動化し、データ処理を効率的にする強力な機能が備わっています。その一つが「Power Query」です。Power Queryは、複雑なデータ統合や変換作業を簡素化し、手作業でのミスを減らし、時間を節約する手助けをします。
Excel業務の自動化を可能にするPower Query
Power Queryは、Excelの作業を自動化できるツールです。プログラミングコードを書く必要がなく、画面上のボタンを操作するだけでExcelが自動でコードを生成するため、「ノーコードツール」に分類されます。
Power Queryは本来、データベースを作成するためのツールとして開発されました。しかし、業務でデータ作成だけで完結することは少ないため、関数やピボットテーブルなどのExcel機能を組み合わせることで、業務自動化ツールとして活用できます。
Power Queryとマクロ（VBA）の違い
Power QueryとExcelマクロ（VBA）は、どちらもExcelの自動化に寄与する機能ですが、両者は以下のように異なります。
| 項目 | Power Query | マクロ（VBA） |
| プログラミング | 不要（画面上のボタン操作でExcelが自動コード作成） | 必要（VBAコードの記述） |
| 難易度 | 比較的低い（VBAのレベル感を10とすると、Power Queryは3〜4程度） | 比較的高い（プログラミング知識が必要） |
| メンテナンス性 | 高い（ノーコードのため、担当者以外も修正しやすい） | 属人化しがち（コードを理解できる人が限られる） |
| 主な用途 | データの取得、変換、統合、整形 | あらゆるExcel操作の自動化、システム連携など |
| 特徴 | データ処理に特化、使いやすさを重視 | 汎用性が高い、より複雑な処理に対応 |
Power QueryはVBAほど何でもできるわけではありませんが、その分、使いやすさに優れます。VBAのようなプログラミング知識が不要なため、VBAの経験がない方でも仕組みを構築でき、負担を少なくして理解が進みます。
これにより、担当者の退職などでシステムが使えなくなる「属人化」のリスクを低減できます。
従来の方法で複数のExcelファイルを集約する際の課題
大企業の子会社約50社から、従業員の氏名とメールアドレスが記載されたExcelファイルが個別に送られてくるケースを想定します。
それぞれのファイルを収集し、全従業員分のデータを一つのリストにまとめる必要があります。
担当者が上記の作業を手作業で行おうとすると、約50個のExcelファイルを一つずつ開き、各ファイルからデータをコピー＆ペーストする作業が伴うため、非常に手間がかかり、時間消費の大きい作業です。
さらに、手作業によるコピー＆ペーストは、データの取りこぼしや誤入力といったヒューマンエラーが発生する可能性もありました。
Power Queryによる課題解決事例
Power Queryを活用すれば、複数Excelファイルからのデータ集約作業を自動化できます。
手作業で数日を要していた作業が、Power Queryの設定を行うことでわずか10分程度で完了し、慣れていれば1分程度で処理を終えることも可能です。これにより、業務時間の削減とデータ整合性の向上に大きく貢献します。
Power Queryを活用した複数Excelファイル集約のステップ
ここでは、Power Queryを使って複数Excelファイルに分散したデータを一つのシートに集約する具体的な手順を解説します。
データの取得方法
Power Queryでデータを取得する最初のステップは、対象となるExcelファイルが格納されているフォルダを選択することです。手順は以下のとおりです。
- Excelを開く
- 「データ」タブをクリックする
- 「データの取得」から「ファイルから」を選び、「フォルダから」を選択する
- 次に、集約したいExcelファイルが保存されているフォルダを選択する
- 「データの変換」ボタンをクリックしてPower Queryエディターを起動させる
なお、「4」の手順でフォルダを選択すると、ファイルの一覧が表示されますが、ここでは何も操作する必要はありません。
データの変換操作
Power Queryエディターに画面が切り替わると、普段のExcelとは異なる画面が表示されます。ここでは以下の手順でデータの結合と整形を行います。
- Power Queryエディターの画面で、「Content」列の右側にある下矢印をクリックする
- 各Excelファイルに保存されているシートを選択し、「OK」をクリックする
- Excelファイルに格納されていたデータが一つのテーブルとして結合される
なお、元データのExcelファイル名を示す「Source.Name」列が表示される場合がありますが、必要に応じて削除できます。
シート名の統一とヘッダーの設定
Power Queryで複数のシートを結合する際には、主に以下2つの注意点があります。
- シート名の統一
- ヘッダーの設定
Power Queryで複数のシートを結合するためには、シート名が統一されている必要があります。例えば、「Sheet1」と「シート1」のように表記が異なる場合や、大文字と小文字、全角と半角の違いがある場合もエラーの原因となります。
エラーを避けるためには、データを取り込む前に、対象となるExcelファイルのシート名をすべて同じに統一することが重要です。
また、Power Queryでデータを取り込んだ際、最初の行が列名（ヘッダー）として認識されていない場合があります。その場合、データは「Column1」「Column2」といった仮の列名で表示されます。
正しいヘッダーを設定するためには、Power Queryエディター内の「1行目をヘッダーとして使用」ボタンをクリックします。これにより、データの1行目が正式な列名として設定され、データがより分かりやすく整理されます。
データの読み込みと更新
データの取得と変換が完了したら、以下の方法でPower Queryで処理したデータをExcelシートに読み込みます。
- Power Queryエディター上部にある「閉じて読み込む」ボタンをクリックする
- Power Queryで結合・整形されたデータが新しいExcelシートに表示される
Excelデータを読み込むと、手作業でコピー＆ペーストして作成した最終的なデータと同じ形式になります。Power Queryで設定した処理は、Excelファイル内に埋め込まれるため、一度設定すれば繰り返し利用できます。
また、元のフォルダ内のExcelファイルに変更があった場合、Excel上の「すべて更新」ボタンをクリックするだけで、Power Queryの設定が実行され、データが自動的に最新の状態に更新可能です。
そのため、ピボットテーブルの更新と似た感覚で利用できます。
Power Queryの便利な機能
Power Queryは、単一のデータ集約だけでなく、以下のようにデータの管理や抽出にも活用できます。
- 複数クエリの管理
- 特定のデータの抽出とソート
それぞれ解説します。
複数クエリの管理
一つのExcelファイル内には、以下の手順で複数のPower Query設定の埋め込みが可能です。これにより、異なる目的のデータ処理を同じファイル内で管理可能です。
Excelの画面右側にある「クエリと接続」をクリックすると、保存されているPower Queryの設定（クエリ）が一覧表示されます。
例えば、「元データ」クエリと、別の処理を行うクエリが複数ある場合、それぞれが独立したコードとして認識されます。
なお、「すべて更新」ボタンをクリックすると、「クエリと接続」に保存されている全てのPower Queryが実行されます。
もし特定のクエリだけを更新したい場合は、目的のクエリを選択した状態でPower Queryエディターに入り、「プレビューの更新」ボタンをクリックすると実行されます。
特定のデータの抽出とソート
Power Queryでは、以下の手順で既存のクエリを参照して新しいクエリを作成し、特定の条件でデータを抽出したり、並べ替えたりできます。
- データを取り込むクエリを選択し、「参照」として複製する
- 複製したクエリをPower Queryエディターで開き、特定の条件（例：特定の人物の名前）でデータを抽出したり、並べ替える
- 新しいクエリを「閉じて読み込む」と、元のデータとは別に、フィルターされた特定のデータだけがExcelシートに表示される
なお、元のデータを変更しなくてもデータを加工して利用できますが、クエリ名も分かりやすい名前に変更すると管理が容易になります。
Power Query利用における注意点
Power Queryは非常に便利なツールですが、その機能を最大限に活かすためには、以下に注意する必要があります。
- シート名の変更
- データ保存場所の変更
- Excelブック名の変更
Power Queryは、処理対象のExcelファイル内のシート名が設定時と同じであることを前提とします。シート名が異なる場合、データが正しく取り込めないエラーが発生します。大文字・小文字、全角・半角の違いも、Power Queryにとっては異なるシート名と認識されるため、特に注意が必要です。
Power Queryで指定した元のデータの保存場所（フォルダのパス）が変更されると、Power Queryはデータを見つけられなくなり、エラーが発生します。データの更新を自動で行うためには、元のExcelファイルが常に同じフォルダに保存されている必要があります。
同様に、個々のExcelファイルのブック名が変更された場合も、Power Queryの設定をやり直す必要が生じることがあります。
そのため、Power Queryを設定した後は、できるだけ同じ場所で作業を行い、元のExcelファイルも同じフォルダ内、できれば同じブック名で保存しましょう。
Excel Power Queryを活用して業務の自動化を図ろう
Power Queryは、日々のExcel業務におけるデータ集約や加工の負担を軽減してくれるツールです。
プログラミングの知識がなくても活用できるため、多くのExcelユーザーにとって業務改善の選択肢となります。
ただし、Power Queryを活用するためには、シート名の統一が必須です。
なお、ペパコミ株式会社では、脱Excelとして「kintone」を活用した業務支援を行っています。業務効率化にお悩みの方は、ぜひ以下からお気軽にご相談ください。
コメント