Excel 2021で部署別集計を自動化する方法｜個人シートをまとめて集計する関数活用術

Excelで社員ごとの個人シートが多数あり、それぞれのデータを部署別に集計したい場合、手作業で転記すると時間がかかり、入力ミスも発生しやすくなります。特に人数が多い場合は、関数を使って自動集計できる仕組みを作ると効率的です。

この記事では、Excel 2021の標準機能を使って、名前や部署の一覧表をもとに個人データを分類し、部署別集計表へ反映する考え方や具体的な方法について解説します。マクロを使わずに実現できる方法を中心に紹介します。

Excelで個人別シートを部署別に集計する考え方

複数の個人シートがある場合、まず重要なのは「どの人がどの部署に所属しているか」という対応表を作ることです。

例えばlistシートに以下のような情報があるとします。

この一覧を基準にすると、Excelは「山田さんのデータは部署Aへ合計する」という判断ができるようになります。

SUMIF関数で部署ごとの集計を行う方法

個人データが1つの一覧表になっている場合は、SUMIF関数を利用すると簡単に部署別集計ができます。

例えば、社員一覧表の部署列がB列、集計したい数値がC列の場合、部署Aの合計は次のような式になります。

=SUMIF(B:B,"部署A",C:C)

この関数では、B列から「部署A」という条件に一致する行を探し、C列の数値を合計します。

ただし、質問のように個人ごとに別シートが存在する場合は、SUMIFだけでは直接集計できないため、別の工夫が必要になります。

個人シートが複数ある場合の集計方法

個人ごとにシートが分かれている場合、Excel標準関数だけで全シートを自動巡回することは難しい場合があります。

その場合は、まず個人シートのデータを1つの一覧表にまとめる方法がおすすめです。

例えば、Power Queryを利用すると、複数シートや複数ファイルのデータをまとめて1つの表に変換できます。Excel 2021にはPower Queryが標準搭載されています。

Power Queryを使って個人データをまとめる方法

Power Queryでは、複数の個人シートからデータを取り込み、1つのテーブルとして管理できます。

基本的な流れは以下の通りです。

1. 「データ」タブを開く
2. 「データの取得と変換」を選択する
3. ブック内のシートデータを読み込む
4. 必要な列だけを残す
5. 1つの一覧表として読み込む

一覧表にできれば、その後はSUMIF関数やピボットテーブルで部署別集計が可能になります。

例えば、個人シートが50枚あっても、Power Queryで1つにまとめれば、部署A計・部署B計・部署C計のような表を自動更新できます。

ピボットテーブルを使った部署別集計

データを一覧化できた場合、ピボットテーブルは部署別集計に非常に便利です。

作成手順は以下の通りです。

1. 一覧データ内のセルを選択する
2. 「挿入」から「ピボットテーブル」を選択する
3. 部署項目を行へ配置する
4. 集計したい項目を値へ配置する

これだけで部署ごとの合計表を自動作成できます。

例えば、毎月の実績データを個人シートへ入力している場合でも、更新ボタンを押すだけで部署別の集計結果を反映できます。

INDIRECT関数でシート名を参照する方法

どうしても個人シートをそのまま利用したい場合は、INDIRECT関数を使ってシート名を動的に指定する方法もあります。

例えば、A1セルに社員名が入力されている場合、次のような式で指定したシートを参照できます。

=INDIRECT("'"&A1&"'!B2")

この方法では、A1セルの名前と同じシートからデータを取得できます。

ただし、人数が多い場合や毎月更新する仕組みでは管理が複雑になるため、Power Queryや一覧表形式へ変更する方が安定します。

マクロを使うべきケースとは

個人シートが数十枚以上あり、毎月同じ作業を繰り返す場合は、VBAマクロを利用するとさらに自動化できます。

例えば、「全社員シートを順番に確認して部署ごとに集計する」という処理は、VBAなら自動実行できます。

しかし、Excel 2021では関数やPower Queryだけでも多くの集計処理が可能です。まずはデータ構造を整理し、必要に応じてマクロを検討するとよいでしょう。

部署別集計を作りやすいExcelデータ構造

Excelで集計しやすい形は、「1人1シート」よりも「1つの一覧表」です。

理想的な形式は、以下のような表です。

この形式なら、関数・ピボットテーブル・Power Queryなど、Excelの多くの機能を利用できます。

まとめ

Excel 2021では、個人ごとのデータを部署別に集計することは可能です。ただし、個人シートが多数ある場合は、そのまま関数だけで処理するより、一度データを一覧化する方が効率的です。

Power Queryで個人シートをまとめ、SUMIF関数やピボットテーブルで部署別集計を行う方法なら、マクロを使わずに自動化できます。

今後も継続して集計する場合は、Excelのデータ構造を一覧形式へ変更することが、最も管理しやすくミスの少ない方法です。