Excelでのデータ管理は、日常業務において不可欠な作業です。特に複数のExcelファイルにまたがるデータの参照や集計は、多くの時間と手間を要する場合がありました。
本記事では、Power Queryを活用して、このような課題を解決し、データ処理を自動化する具体的な方法を解説します。
なお、ペパコミ株式会社では、脱Excelとして「kintone」を活用した業務支援を行っています。業務効率化にお悩みの方は、ぜひ以下からお気軽にご相談ください。
Excel Power Query（パワークエリ）とは
Power Queryは、Microsoft Excelに搭載されている強力なデータ収集・変換ツールです。
異なるデータソースからデータを抽出し、整形し、結合する作業を自動化する機能を有します。Power Queryを活用すると、手作業によるミスを減らし、作業時間を削減できます。
従来のデータ参照と集計が抱える課題
従来のExcelにおけるデータ参照や集計には、VLOOKUP関数を用いる方法は広く知られていますが、以下のような難点がありました。
- 手作業の繰り返し
- データ整合性の問題
- ファイル間の連携の複雑さ
VLOOKUP関数では、都度データを入力したり、コピペで関数を適用範囲に広げたりする手間が発生します。データの増減に応じて、関数の範囲を調整する手作業も必要です。
マスタデータを別シートで管理している場合でも、他の部署から送られてくるデータにはマスタが添付されていないことがあり、その都度マスタシートを作成しVLOOKUPを設定する手間が発生します。
また、複数のExcelファイルに分かれたデータを参照する場合、それぞれのファイルを開いて手動で連携させる必要があり、煩雑さが増します。
上記は、特に総務部門などで携帯電話の通信費を管理する際に顕著に表れる課題です。
電話番号ごとの基本料金、通話料、オプション料、そして使用者名が記録されたデータにおいて、部署コードがコード番号でしか表示されていないため、別途部署名のマスタデータと結合し、通信費の合計を算出する必要が生じます。
Power Queryによるルックアップと集計の自動化
Power Queryは、以下の課題を解決し、ファイルを跨いだルックアップやデータ集計を自動化します。
- データ取得と整形
- クエリのマージ（VLOOKUP相当の機能）
- 通信費の合計集計
- データのエクスポートと活用
ここでは、Power Queryで部署名表示の集計、解説します。
データ取得と整形
まず、Power Queryを使ってデータ取得や整形する方法は以下のとおりです。
- 新しい空白のExcelファイルを開く
- Excelの「データ」タブを選択し、「データの取得」から「ファイルから」を選び、「Excelブックから」を選択する
- 部署コードと部署名が記載された「部署マスタ」ファイルを選択し、読み込む
- Power Queryエディターで整形する
読み込んだ部署マスタはPower Queryエディターに表示されます。シート名が分かりにくい場合は「部署マスタ」のように名前を変更しましょう。また、1行目が正しい列名になっていない場合は、「1行目をヘッダーとして使用」機能を使って列名を正しく設定します。
なお、同様の手順で、電話番号、部署コード、基本料、通話料、パケット通信料、オプション料、使用者氏名が含まれる「通信費明細」ファイルを読み込みます。こちらもシート名を「通信費明細」など分かりやすい名前に変更しましょう。
クエリのマージ（VLOOKUP相当の機能）
Power Queryの「クエリのマージ」機能は、ExcelのVLOOKUP関数やXLOOKUP関数と同様の働きをします。
- クエリのマージを選択：Power Queryエディターで「通信費明細」クエリが選択されている状態で、「クエリのマージ」機能を選ぶ
- マスタクエリの選択： 上段に「通信費明細」クエリ、下段には結合したい「部署マスタ」クエリを選択する
- 共通キーの指定： 「通信費明細」の「請求部署コード」列と、「部署マスタ」の「部署コード」列をそれぞれクリックして、共通のキーとして指定する
- 結合結果の展開： マージを実行すると、右端に部署マスタのクエリがテーブルとして結合されます。このままでは「Table」と表示されるため、結合された列の右にある展開ボタン（左右の矢印）をクリックする
- 必要な列の選択とプレフィックス解除： 展開メニューで、取得したい「部署名」にチェックを入れる
- 列の並べ替え： 追加された「部署名」列は、ドラッグアンドドロップで任意の場所に移動できる
なお、「元の列名をプレフィックスとして使用します」のチェックを外すと、列名が「部署マスタ.部署名」ではなく「部署名」として追加されます。
また、部署コードの隣に配置することで、見やすいデータに整形可能です。
上記の操作により、手作業で関数を入力したり、ドラッグしてコピーしたりする手間なく、瞬時にデータを結合できます。
通信費の合計集計
次に、各料金項目を合計して通信費を算出する方法を解説します。
- 複数列の選択： 基本料、通話料、パケット通信料、オプション料の各列をShiftキーを押しながらクリックし、複数選択する
- 列の追加と加算： 「列の追加」タブから「標準」を選択し、「加算」をクリックする
- 合計列の生成： 「2」の操作により、選択した列の合計値が新しい列として瞬時に生成される
- 列名の変更と並べ替え： 生成された列名は「合計」などとなっているため、「通信費合計」に設定する
なお、生成された列もドラッグアンドドロップで任意の場所に移動させることができ、見やすい位置に配置します。
データのエクスポートと活用
整形・集計が完了したデータは、以下の方法でExcelシートに読み込むことができます。
- 閉じて読み込む： Power Queryエディターの「ホーム」タブにある「閉じて読み込む」をクリックする
- Excelシートへの出力： 処理されたデータが新しいExcelシートにデータベースとして表示される
上記のデータベースが完成すると、PivotTable（ピボットテーブル）を使って、部署ごとの通信費合計や電話番号ごとの通信費合計など、必要な項目を自由に選択し、多角的なデータ分析を行えます。
Power Query利用のメリット
Power Queryを利用してデータを処理する主なメリットは以下に示すとおりです。
| Power Queryの特徴 | VLOOKUP関数などの手動処理の特徴 | |
| 自動化 | データ更新時に「すべて更新」で自動反映 | データ量に応じて手動で関数範囲調整が必要 |
| ファイル連携 | 複数ファイルからの参照が容易 | ファイルごとに手動での参照設定が必要 |
| データ整合性 | 元データが変更されても自動で更新される | 関数が壊れたり、コピーミスが発生する可能性 |
| 作業時間削減 | 設定一度で済むため、大幅な時間削減 | 毎回の手作業が発生し、時間がかかる |
| マスタデータ管理 | 独立したマスタファイルを安全に参照 | マスタの共有や管理が煩雑になる場合がある |
なお、携帯電話の明細以外にも、電話番号を共通のキーとして他の重要マスタデータと紐づけるなど、同様の仕組みを応用できます。
Excel Power Queryを活用してルックアップや集計作業を自動化しよう
Excel Power Queryは、複数ファイルにまたがるデータのルックアップと集計作業を自動化するツールです。一度設定を完了すれば、データ更新のたびに自動で最新の結果を反映できるため、大幅な業務効率化と正確なデータ分析が可能です。
従来のVLOOKUP関数など手動でのデータ処理が抱えていた、繰り返し発生する手作業やデータ整合性の課題を解決します。
そのため、Power Queryの活用は、Excelでのデータ管理を次のレベルへと引き上げるために重要です。
なお、ペパコミ株式会社では、脱Excelとして「kintone」を活用した業務支援を行っています。業務効率化にお悩みの方は、ぜひ以下からお気軽にご相談ください。
コメント