導入
案件管理表に新しい案件を入力したいのに、上司がピボットで集計を見ている最中で「動かさないで」と言われる。集計のために行を並び替えたら、別の人がやりかけの入力行が変な位置に飛んでしまった、ということもある。1枚のシートで入力と集計を同時にやろうとして、両方が崩れる場面はありませんか。
これは作業者の連携不足ではなく、入力と集計を1枚のシートで兼ねていることが原因です。本記事では、入力用シートを正本とし、集計用シートを別に作って参照式で読む形に変える手順をまとめます。
この記事で解決すること
| 項目 | 内容 |
|---|---|
| 解決する課題 | 入力しやすさと集計しやすさが衝突する |
| 主な原因 | 1つのシートで入力と集計を兼ねている |
| 解決方法 | 入力用シートを正とし、集計用シートで見せ方を分ける |
| 対象業務 | 売上管理・進捗管理・月次報告 |
| 対象人数 | 3〜30人 |
| 難易度 | ★☆☆☆☆ |
| 作業時間 | 30分 |
| 用意するもの | 対象のExcelファイル／編集権限 |
| 効果 | 入力表を壊さず集計できる |
| 向かないケース | 単純な一覧確認だけの表 |
入力と集計は要求が逆向きです（入力者は縦に伸びる素の表が欲しい、集計者は横断的なビューが欲しい）。1枚で両立させようとせず、シートを分けるのが基本です。
なぜその管理表はうまくいかないのか
- 並び替えで集計のピボット範囲が変わる
- 集計のためにフィルタをかけると入力者が他の行を見つけられない
- 集計セルが入力範囲のすぐ下にあり、新規行を入れると壊れる
- 集計用の列（小計・前月比など）が入力者の作業の邪魔になる
- 同時編集時にロックが取り合いになる
担当者の連携不足ではなく、入力と集計が同じシートで衝突せざるを得ない構造そのものが原因です。見直しは、入力シートと集計シートを2枚に分けて、集計側を参照だけにするところから始めます。
完成イメージ
直す前 — 入力と集計が同じシート:
| 案件ID | 顧客名 | 金額 | 月区分 | （空行） | （小計行） |
|---|---|---|---|---|---|
| 240401 | 山田商事 | 120,000 | 2024-04 | ||
| 240402 | 鈴木物産 | 80,000 | 2024-04 | ||
| 4月小計：200,000 | |||||
| 240501 | 山田商事 | 150,000 | 2024-05 | ||
| 5月小計：150,000 |
並び替えで小計の位置が崩れる、新規行を月小計の下に入れると見落とされる、といった問題が出る。
直した後 — 入力シートと集計シートに分離:
シート「案件_入力」（追加・編集はここだけ）
| 案件ID | 顧客名 | 金額 | 月区分 |
|---|---|---|---|
| 240401 | 山田商事 | 120,000 | 2024-04 |
| 240402 | 鈴木物産 | 80,000 | 2024-04 |
| 240501 | 山田商事 | 150,000 | 2024-05 |
シート「案件_集計」（参照式と集計のみ）
| 月区分 | 件数 | 合計金額 |
|---|---|---|
| 2024-04 | 2 | 200,000 |
| 2024-05 | 1 | 150,000 |
入力者は素の表だけ見て作業し、集計者は集計シートだけ見ます。互いに干渉しません。
改善手順
ステップ1. 入力用シートを「正本」と決める
データの正がどこにあるかを明示します。
操作: 既存シートを「案件_入力」にリネームし、タブ色を青に変える。シートA1上の見出し行に「このシートはデータの正本。集計は別シートで行う」と記載する。
記入例:
| シート名 | 役割 | 編集ルール |
|---|---|---|
| 案件_入力 | データの正 | 追加・修正はここだけ |
| 案件_集計 | 集計結果 | 参照式のみ。値の上書き禁止 |
ステップ2. 入力用シートから集計列・小計行をすべて削除する
入力用シートに紛れている集計要素を退避します。
操作: 入力用シート内の小計行・合計列・前月比列などを順に削除する。削除前にコピーを取り、集計シート作成時の元ネタにする。
記入例:
| 削除対象 | 退避先 |
|---|---|
| 各月の小計行 | 案件_集計シートでSUMIFで再計算 |
| 前月比列 | 案件_集計シートで計算 |
| 累計列 | 案件_集計シートで計算 |
✗悪い例: 入力シートに小計行を残したまま集計シートも作る → 二重管理 ◎良い例: 入力シートは素の表、集計は集計シート、と完全に分ける
ステップ3. 集計用シートを新規作成し参照範囲を準備する
集計シートを別に作り、入力シートの全データ範囲を参照対象にします。
操作: シート「案件_集計」を新規作成。タブ色を緑にする。集計範囲は固定の 案件_入力!A:F のように列全体を指定することで、入力行が増えても自動で取り込まれる。
記入例:
| 設定項目 | 値 |
|---|---|
| 集計シート名 | 案件_集計 |
| 参照範囲 | 案件_入力!A:F（列全体） |
| ピボット範囲 | 案件_入力テーブル化 → テーブル名で参照 |
ステップ4. SUMIF / COUNTIF / ピボットで集計式を入れる
集計シートに月別・担当別の集計を書きます。
操作: 集計シートA2から下に「2024-04」「2024-05」のような月区分を入れる。B列に =COUNTIF(案件_入力!D:D,A2)、C列に =SUMIF(案件_入力!D:D,A2,案件_入力!C:C) を入れる。担当別集計も同様に列を追加する。
記入例:
| 月区分 | 件数（数式） | 合計金額（数式） |
|---|---|---|
| 2024-04 | =COUNTIF(案件_入力!D:D,A2) | =SUMIF(案件_入力!D:D,A2,案件_入力!C:C) |
| 2024-05 | =COUNTIF(案件_入力!D:D,A3) | =SUMIF(案件_入力!D:D,A3,案件_入力!C:C) |
ステップ5. 入力シートと集計シートの編集ルールを明文化する
シート役割を運用ルールとして残します。
操作: 別シート「シート役割」を作り、A列にシート名、B列に編集可能/不可、C列に編集できる人、D列に注意を書く。ファイルを開いた人が最初に見るように、シートの並びを先頭にする。
記入例:
| シート名 | 編集可否 | 編集できる人 | 注意 |
|---|---|---|---|
| 案件_入力 | 編集可 | 営業全員 | 並び替えしてもOK（集計は数式参照） |
| 案件_集計 | 参照のみ | 数式管理者のみ | 値直接入力禁止 |
実務での注意点
- 単純な一覧確認だけの表（集計が要らない管理表）にはシート分離の手間が見合いません。
- 集計シートで使う関数は SUMIF/COUNTIF/ピボットを基本にします。INDEX-MATCH の複雑な数式に頼ると、入力シートの列追加で式が崩れます。
- 入力シートをテーブル化（Ctrl+T）しておくと、参照範囲が自動で広がります。手動で範囲を広げる手間が減ります。
- 集計シートにフィルタや並び替えを多用すると、参照式の表示順がずれて見えます。固定の月リストを左列に置く方が確実です。
- 同時編集が多いファイルは、入力者と集計者で参照シートを分けるだけでもロック競合が大幅に減ります。
まとめ
入力と集計が衝突する原因は、1枚のシートで2つの役割を兼ねていることです。入力用シートを正本とし、集計用シートを参照式と集計関数で別に作れば、入力作業を妨げずに集計結果が常に最新になります。
次にやることは、対象ファイルの「集計列・小計行」を一覧化し、入力シートから集計シートに退避する計画を立てることです。一度に全部やらず、まず月小計から移すだけでも効きます。あわせて、入力と確認を分けたい場合は入力用と確認用のシートを分ける手順、raw データと加工結果を分けたい場合はrawシートと加工シートを分ける手順も参考になります。
