Excelにおけるデータ集計作業は、特に複数店舗から日々送られてくる売上データなどを取り扱う場合、手作業でのコピー＆ペーストに多くの時間を要してしまう場合があります。
この集計作業には、店舗ごとに作成されたExcelシートを一つにまとめるという手間が伴います。
そのため上記の課題を解決するためには、ExcelのPower Query機能を活用した自動化が有効な手段です。Power Queryを導入することで、手作業で行っていた集計作業を自動で実行できるようになり、業務時間の削減へとつながるでしょう。
なお、ペパコミ株式会社では、脱Excelとして「kintone」を活用した業務支援を行っています。業務効率化にお悩みの方は、ぜひ以下からお気軽にご相談ください。
複数店舗の売上データ集計の現状と課題
各店舗では、日々の売上高、材料費、人件費などのデータをExcelシートに日々入力していると思います。月末には、本社がこれらのExcelデータを回収し、一つのデータとしてまとめる作業が発生します。その上で、店舗ごとの売上高や利益などの計算も必要です。
具体的には、A店舗、B店舗、C店舗、D店舗、E店舗といった複数の店舗から、それぞれのExcelファイルが送られてくる状況が考えられます。
これらのファイルを一つに集約する際には、手作業でのコピー＆ペースト作業が必要となり、多くの時間と労力を要する原因です。
その後、ピボットテーブルを用いて、9月分のA店舗の売上合計やB店舗の売上合計といった形で、店舗ごとの合計データを生成します。
そのため、業務を効率化するためには、上記一連の作業を自動で行う仕組みを構築することが求められます。
Power Queryを用いたデータ集計自動化の具体的な手順
ここでは、Power Queryを活用して複数店舗のExcelデータを自動で集計する具体的な手順を紹介します。
空白のExcelファイルからの開始
まず、新しい空白のExcelファイルを開きます。このファイルが、集約されたデータを受け取る場所となります。
データの取得とフォルダからの選択
複数のExcelファイルを一括で集約するための手順は以下のとおりです。
- Excelのリボンメニューにある「データ」タブを選択する
- 「データの取得」グループの中から「ファイルから」を選択する
- 「フォルダから」を選択する
- 「店舗データ」フォルダを指定し、読み込む
上記の操作により、A店舗、B店舗、C店舗、D店舗、E店舗といった各店舗のExcelファイルがPower Queryによって認識されます。
データの変換とシート名の統一
データの変換とシート名の統一
フォルダを読み込んだ後、「データの変換」をクリックすると、Power Queryエディターが起動し、フォルダに含まれるExcelファイルの情報が表示されます。
表示されたテーブルの「Content」列の右側にある下向き矢印をクリックすると、各Excelファイルに含まれるシートの情報が表示されます。
ただし、Power Queryで複数のシートを効率的に結合するためには、すべてのExcelファイルのシート名が同じでなければいけません。
例えば、各店舗の売上データが「9月」というシート名で保存されている場合、すべての店舗ファイルでこのシート名が「9月」に統一されていなければ、正確なデータ結合ができません。
シート名が異なると、データが適切に読み取られず、エラーが発生する可能性も考えられます。シート名が統一されていることを確認した上で、「OK」をクリックすると、各店舗のデータが一つに統合されます。
データ型の修正と店舗名の抽出
データが結合された後、追加のデータ整形作業が必要となる場合があります。
例えば、日付の列が数字として認識されてしまう場合があります。このような場合は、該当する列のデータ型を「日付」への修正が必要です。
次に、結合された左の列に、元のファイル名と拡張子が含まれている場合は、以下の手順で列の分割機能を使用して店舗名だけを抽出します。
- 列を選択し、「区切り記号による分割」を選択する
- 区切り記号として「.」を指定すると、ファイル名（店舗名）と拡張子を分離できる
なお、拡張子が含まれる列は、不要であるため削除します。その後、店舗名が残った列の名前を「店舗名」へと変更します。
統合されたデータのExcelへの読み込みとピボットテーブルによる集計
Power Queryでのデータ加工が完了した後、そのデータをExcelシートに読み込み、さらにピボットテーブルを用いて集計結果を生成します。
統合データのExcelシートへの出力
Power Queryエディター上で「閉じて読み込む」を選択すると、加工されたデータが新しいExcelシート上に表示されます。このシートには、複数の店舗からのデータが日付順に統合され、店舗名ごとに識別可能な形で一覧表示されます。
ピボットテーブルの活用
Excelシートに表示された統合データに対して、挿入タブからピボットテーブルを挿入します。ピボットテーブルの設定画面で、集計したい項目を適切に配置します。
例えば、売上高を「値」エリアにドラッグし、店舗名を「行」エリアにドラッグすると、9月分の店舗ごとの売上高合計が一覧で表示されます。
また、利益のような他の項目も「値」エリアに追加することで、売上と利益の両方を同時に表示可能です。
そのため、各店舗の9月分の売上高合計と利益合計を一度に把握できるようになります。
複数月のデータ対応
ピボットテーブルを活用すると、単月だけでなく、複数月のデータを集計する場合にも適用できます。
例えば、8月、9月、10月、11月のデータがそれぞれ別のExcelファイルとして存在し、それぞれに同じフォルダに保存されている場合、Power Queryでファイルを読み込むことで、すべての月のデータが結合されます。
その後、ピボットテーブルの日付フィルター機能を使って、特定の月を抽出して表示可能です。
また、月ごとにシートが分かれているケースでも、Power Queryは複数のシートを同時に読み込み、それらを一つに結合する機能も備えています。
ピボットテーブルは複数のシートに分散しているデータを統合し、管理を容易に行うことが可能になるでしょう。
Power Queryによる業務改善の展望
Power Queryの様々な機能を組み合わせることで、これまで手作業で行っていたデータ集計業務の多くを自動化できる可能性を秘めています。
今回の事例は、各店舗からExcel形式で送られてきた売上データをPower Queryで自動的に集計し、ピボットテーブルで集計結果を出力する自動化の一部を紹介しました。
ピボットテーブルに関する知識をさらに深めることで、より複雑な条件でのデータ表示や、多様な視点からの分析が可能となります。Power Queryは、データ集計の自動化を通じて、業務プロセス全体の改善へとつながる機能を備えていると言えるでしょう。
なお、ペパコミ株式会社では、Excelとして「kintone」を有効活用した業務支援を行っています。業務効率化にお悩みの方は、ぜひ以下からお気軽にご相談ください。
コメント