CONTACT

FILE No.0284 // NOTICE

複数のExcelファイルを自動集計するには?毎月のコピペを減らす方法と注意点

複数のExcelファイルを自動集計するには?毎月のコピペを減らす方法と注意点

NOTICE

営業担当者から届いた売上表を一つずつ開き、必要な行をコピーして、月次の集計表へ貼り付ける。拠点別の実績や経費の一覧でも、同じ作業を繰り返していないでしょうか。

複数のExcelファイルの集計は、取り込むファイルと、加工・集計のルールが決まっていれば、自動化を検討できます。Excelの機能で対応できる場合もあれば、マクロや専用ツールを組み合わせる方法が合う場合もあります。

ただ、ファイルを一つにまとめるだけでは、毎月の手間が十分に減らないこともあります。列名の違いを直す、修正版を選び直す、集計漏れがないか確かめる。こうした作業まで含めて整理することが大切です。

この記事では、複数のExcelファイルを使う業務を前提に、自動集計の方法と、導入前に確認しておきたい点を説明します。

「ファイルをまとめる」と「数字を集計する」を分けて考える

最初に、今の作業を「明細を一つにまとめること」と「合計や件数を計算すること」に分けてみましょう。

たとえば、担当者ごとに分かれた売上明細を一つの一覧に並べるのは、データを結合する作業です。その一覧から、担当者別・商品別・月別の売上を計算するのが、集計する作業です。

明細を残したいのか、合計だけを知りたいのかで、必要な仕組みは変わります。あとから「この金額はどの取引の合計か」を調べるなら、集計結果だけでなく、元のファイル名や取引番号も確認できる形にすると使いやすくなります。

基本の流れを整理すると、次のようになります。

複数のExcelファイルを集計する流れ
  1. 対象を選ぶ

    対象月・担当者・ファイルを決めます。

  2. 形式をそろえる

    列名や日付、金額の扱いをそろえます。

  3. 明細をまとめる

    必要な行を一つの一覧に結合します。

  4. 数字を集計する

    担当者別・月別などの単位で計算します。

  5. 確認して出力する

    件数や合計を確かめ、集計表を作ります。

今の業務で時間がかかっているのは、どの工程でしょうか。すでに集計用の数式やピボットテーブルがあるなら、その前の「明細を集めて整える作業」を自動化するだけでも、コピペを減らす方法になります。

Excelの機能・Power Query・マクロはどう選ぶ?

方法を選ぶときは、使い慣れたExcelを活かせる範囲と、Excelの外にある作業をつなぐ必要があるかを確認します。

表は左右にスクロールできます。

Excelの集計を自動化する方法の選び方
方法向いている作業確認しておきたいこと
Excelの「統合」機能同じ位置や見出しを持つ複数の表から、合計・平均などを計算する明細の一覧も必要か。対象範囲や見出しがそろっているか
Power Query複数ファイルの明細を取り込み、整形・結合を繰り返すファイルの形式・列名、保存場所、更新の手順
VBAマクロ集計に加え、帳票への配置や保存など、決まったExcel操作をまとめるマクロを利用できる環境か。変更時に誰が修正するか
専用ツール・システム連携複数の形式やサービスを扱い、確認・出力・登録までつなぐ対応する範囲、導入・運用費用、エラー時の対応

Excelの「統合」は、複数の表から合計などを求めるための機能です。一方、担当者ごとの明細を一行ずつ残してまとめたい場合には、Power Queryなど、データを結合する方法を検討します。Microsoftも、複数シートのデータを統合する方法の中で、この違いを説明しています。

毎月届くファイルをまとめるなら、Power Queryが候補になる

Power Queryは、データを取り込み、不要な行や列を除いたり、形式をそろえたりする処理を設定できる機能です。同じ構造のファイルをフォルダから読み込み、まとめる方法もあります。

一度決めた処理を更新時にも使えるため、毎月同じようなファイルを集める業務では、手作業を置き換える候補になります。Microsoftのファイル結合の説明でも、同じ形式・構造の複数ファイルに、共通の処理を適用する方法が紹介されています。

ただし、「更新を押すと集計し直せること」と「決まった時間に無人で実行できること」は、分けて考えます。利用できる機能はExcelのバージョンや利用環境によって異なります。まずは、使っている環境と実際のファイルで、どこまで対応できるかを確認しましょう。

自動集計の前に決めたい、4つのルール

毎回、人がファイルを見て判断している部分は、その判断をどのように扱うか決める必要があります。特に、次の4点を整理しておくと、必要な処理が具体的になります。

どのファイルを対象にするか
対象月・拠点・担当者を決め、下書きや過去のファイルを除く条件を整理します。「修正版」が届いたときに、どちらを使うかも決めます。
どの列が、同じ情報なのか
「担当者」と「担当者名」など、名前が違う列の対応を確認します。同じ「金額」という列でも、税込・税抜など意味が異なる場合は、別に扱います。
空欄や表記の違いをどう扱うか
日付や担当者名の表記、数値として扱う項目をそろえます。未入力の金額を確認待ちにするかなど、判断できないデータの扱いも決めます。
何を、重複や集計漏れと判断するか
取引番号などを使い、同じ取引を見分ける条件を決めます。日付と金額が同じだけで、別の取引を消してしまわないようにします。

たとえば、同じ取引を修正前・修正後の両方のファイルから取り込むと、二重に集計するおそれがあります。「同じ取引番号なら修正版に置き換える」「承認されたファイルだけを取り込む」といった、業務に合うルールが必要です。

また、入力用の表に小計・合計の行が含まれている場合は、そのまま明細と一緒に足し合わせないようにします。人が目で見て除いていた行や、選び直していたファイルこそ、自動化の前に共有したい情報です。

集計後に確認できる形にしておく

自動化後も、集計結果を確かめる手がかりは必要です。合計額だけを見るより、処理したファイルや件数も分かるほうが、取り込み漏れや意図しない対象に気づきやすくなります。

確認する情報としては、次のようなものが考えられます。

  • 集計対象にしたファイルの一覧
  • ファイルごとの取り込み件数と、対象期間
  • 取り込めなかった行や、確認が必要な項目
  • 拠点別・担当者別の件数や合計

たとえば、「全拠点分を集計したつもりが、未提出の拠点があった」という場合は、数字の計算が正しくても、業務としては集計が完了していません。提出予定の一覧と照らし合わせるなど、確認したい内容に合わせて仕組みを考えます。

ファイルの置き場所も整理しておきたい点です。Power Queryのフォルダーコネクタは、サブフォルダ内のファイルも対象にします。集計結果を同じ場所に保存するときは、次回の取り込みから除外する条件が必要です。入力用と出力用のフォルダを分けておく方法もあります。

さらに、修正後にもう一度実行したとき、結果を作り直すのか、既存の台帳へ追記するのかも決めます。何度実行しても、意図した結果に戻せることまで考えると、月次業務で使い続けやすくなります。

自社で対応するか、開発を依頼するかの判断点

入力形式がそろっていて、処理のルールも単純なら、Excelの既存機能で対応できる可能性があります。社内で設定を変更できる人がいるなら、まずは一部のファイルで試す方法もあります。

一方、ファイルの形式が複数ある、確認画面を用意したい、別のシステムへの登録までつなげたい場合は、設定や開発を依頼する選択肢があります。担当者の異動後も使えるように、操作手順や変更時の対応を整えたい場合も同様です。

相談の際は、入力に使う実際のファイルと、完成させたい集計表の両方があると、話が具体的になります。あわせて、次の情報を共有すると整理しやすくなります。

  • 通常のファイルと、修正版・例外的な形式のサンプル
  • 毎月のファイル数・行数と、現在かかっている時間
  • 対象を選ぶところから、集計結果を渡すまでの作業手順
  • Excelのバージョン、Windows・Macなどの利用環境、保存先
  • 実行する担当者と、変更・エラーがあった場合の相談先

費用を比べるときは、初期の設定・開発費だけでなく、毎月残る確認作業や、入力形式が変わった際の修正も含めて考えます。「コピペが何回減るか」に加えて、担当者が無理なく運用できるかを確かめたいところです。

毎月繰り返す集計を、使い続けられる仕組みにする

複数のExcelファイルの自動集計では、取り込む対象を決め、形式をそろえ、明細をまとめ、必要な単位で計算します。そこに、重複・集計漏れの確認や、修正後にやり直すためのルールを組み合わせます。

いま使っている集計表を出発点にして、毎月どの作業を繰り返しているかを整理することが、方法を選ぶ第一歩です。

集計の前に、PDFや紙の書類からExcelへ入力する作業がある場合は、PDFからExcelへの転記を自動化する方法もあわせてご覧ください。

Excelの集計・転記作業を相談したい方へ

GANCHAN 11号では、中小企業の業務改善を支援する「仕組み実装室」を運営しています。既存サービスの活用から、業務に合わせたツールの開発まで対応しています。

「毎月、このフォルダのファイルを開いて、この集計表を作っている」という説明からで大丈夫です。実際のファイルと作業の流れをもとに、いまのExcelを活かせる範囲や、整形・確認までまとめて仕組みにする範囲を一緒に整理します。

仕組み実装室で業務改善・自動化の対応内容を見る

この記事を書いた人
HIROFUMI IWATANI

企業課題をデジタル技術で解決する設計・開発者。設計から実装、AI活用までを単独で遂行する。最前線で得た技術ログやWeb戦術を記録・発信中。
>> STATUS: ALL GREEN.

HIROFUMI IWATANIをフォローする
NOTICE