導入
売上管理や月次報告、在庫管理のExcel管理表で、SUM や ピボットで全体合計を出したら、なぜか手計算の倍近い数値が出てしまうことはありませんか。原因を辿ると、表の中に「営業1課小計」「営業2課小計」「総合計」のような小計行が紛れていて、それらもデータ行と一緒に SUM されて二重計上になっているケースもよくあります。
こうした「小計の二重計上」は、集計式の使い方が悪いのではなく、データ行と小計行を同じ表に同居させていることが原因です。1つの表に「明細」と「集計結果」が混在していると、機械的な集計のたびに小計行を除外する手間が発生します。
この記事では、データ行と小計を分離し、明細表は明細だけ・集計は別シート（または別ビュー）で行う設計に切り替える方法を20分で完了させる手順として紹介します。終わったときに、SUMやピボットを使っても二重計上が起きない状態になります。
この記事で解決すること
| 項目 | 内容 |
|---|---|
| 解決する課題 | 集計時に小計を二重計上してしまう |
| 主な原因 | データ行と小計行が同じ表に混ざっている |
| 解決方法 | 小計は別シートや集計ビューで表示する |
| 対象業務 | 売上管理・月次報告・在庫管理 |
| 対象人数 | 2〜30人 |
| 難易度 | ★☆☆☆☆ |
| 作業時間 | 20分 |
| 用意するもの | 対象のExcelファイル／編集権限 |
| 効果 | 二重集計を防げる |
| 向かないケース | 印刷用の集計表 |
なぜその管理表はうまくいかないのか
データと小計が混在する管理表には、共通する状況があります。
- 1つの表に明細行と小計行が混ざっている
- 「営業1課小計」「営業2課小計」「総合計」が表の途中・末尾に紛れている
- SUM の範囲指定が雑だと、小計行も加算されて二重計上になる
- ピボットテーブルが小計行を「項目」として認識し、二重に集計される
- フィルタで明細だけ抽出したいのに、小計行も拾われる
- 月次で件数が変わると、小計行の位置がずれて再調整が必要
これは集計式の問題ではなく、表の役割が「明細記録」と「集計結果」の2つに分かれていないことが原因です。見直しは、データ行と小計行を物理的に別シートに分離するところから始めます。
完成イメージ
20分後、データシートには明細だけが並び、集計結果は別シート「集計ビュー」で表示される状態になります。SUMやピボットを使っても二重計上が起きません。
改善前 — 1つの表にデータと小計が混在:
| 部署 | 案件 | 金額 |
|---|---|---|
| 営業1課 | A社見積 | 50,000 |
| 営業1課 | B社契約 | 80,000 |
| 営業1課 | C社対応 | 70,000 |
| 営業1課小計 | 200,000 | |
| 営業2課 | D社見積 | 60,000 |
| 営業2課 | E社契約 | 90,000 |
| 営業2課小計 | 150,000 | |
| 総合計 | 350,000 |
=SUM(金額列) を実行すると、明細5件（350,000）に小計3つ（200,000+150,000+350,000）が加算されて 1,050,000 になります。本来の3倍の数字です。
改善後 — 明細と集計を分離:
「データ」シート（明細のみ）:
| 部署 | 案件 | 金額 |
|---|---|---|
| 営業1課 | A社見積 | 50,000 |
| 営業1課 | B社契約 | 80,000 |
| 営業1課 | C社対応 | 70,000 |
| 営業2課 | D社見積 | 60,000 |
| 営業2課 | E社契約 | 90,000 |
「集計ビュー」シート（別シート、自動計算）:
| 部署 | 件数 | 金額合計 |
|---|---|---|
| 営業1課 | =COUNTIF(データ!A:A,”営業1課”) = 3 | =SUMIF(データ!A:A,”営業1課”,データ!C:C) = 200,000 |
| 営業2課 | 2 | 150,000 |
| 総合計 | =COUNTA(データ!A:A)-1 = 5 | =SUM(データ!C:C) = 350,000 |
明細表の SUM は 350,000 で正しく、集計ビューでは部署別の小計と総合計が自動算出されます。
改善手順
20分ほどで4ステップを進めます。
ステップ1. 既存表の小計行を洗い出す
データ表のどこに小計行があるかを把握します。
操作: データ表を上から見て、「小計」「合計」「総合計」「集計」などの語を含む行を洗い出す。Ctrl+F で「小計」を検索すると一括で見つかる。
記入例:
| 行番号 | 内容 |
|---|---|
| 5 | 営業1課小計 |
| 9 | 営業2課小計 |
| 11 | 総合計 |
ステップ2. データシートから小計行を削除する
小計行を別シートに移すため、まず明細だけの状態を作ります。
操作: 洗い出した小計行を1行ずつ削除する。または、フィルタで「小計」「合計」を含まない行だけを抽出してコピーし、新しいシート「データ」を作って貼り付ける（元の表は念のためバックアップ）。
明細行だけになったら、テーブル機能（Ctrl+T）でテーブル化しておくと、後で行が増えても集計式の範囲が自動拡張される。
記入例: 削除後のデータシート
| 部署 | 案件 | 金額 |
|---|---|---|
| 営業1課 | A社見積 | 50,000 |
| 営業1課 | B社契約 | 80,000 |
| 営業1課 | C社対応 | 70,000 |
| 営業2課 | D社見積 | 60,000 |
| 営業2課 | E社契約 | 90,000 |
ステップ3. 集計ビューシートを新設する
別シートに集計用のレイアウトを作り、SUMIF・COUNTIF で部署別の小計を出します。
操作: 新しいシート「集計ビュー」を追加。A1〜C1 に「部署」「件数」「金額合計」と入力。A2 以降に部署名を並べる。B列とC列に集計式を入れる:
| セル | 式 |
|---|---|
| B2 | =COUNTIF(データ!A:A,A2) |
| C2 | =SUMIF(データ!A:A,A2,データ!C:C) |
最終行に「総合計」を追加:
| セル | 式 |
|---|---|
| 総合計の件数 | =SUM(B2:B最終行) または =COUNTA(データ!A:A)-1 |
| 総合計の金額 | =SUM(C2:C最終行) または =SUM(データ!C:C) |
記入例: 集計ビューシート
| 部署 | 件数 | 金額合計 |
|---|---|---|
| 営業1課 | =COUNTIF(データ!A:A,”営業1課”) | =SUMIF(データ!A:A,”営業1課”,データ!C:C) |
| 営業2課 | =COUNTIF(データ!A:A,”営業2課”) | =SUMIF(データ!A:A,”営業2課”,データ!C:C) |
| 総合計 | =SUM(B2:B3) | =SUM(C2:C3) |
✗悪い例: 集計ビューで部署名をハードコードする（新しい部署が追加されたら手動で項目を追加する必要） ／ ◎良い例: ピボットテーブルで自動的に部署別集計を生成する形にする（手動が嫌な場合）
ステップ4. ピボットテーブルで自動集計化する（推奨）
手動で集計式を書く代わりに、ピボットテーブルを使うと部署や項目が増えても自動的に集計されます。
操作: データシートを範囲選択 → 挿入 → ピボットテーブル → 新規ワークシート → OK。ピボットフィールドリストで「部署」を「行」エリアに、「金額」を「値」エリアに、「案件」（または任意の列）を「値」エリアに「カウント」としてドラッグ。
ピボットテーブルが部署別の件数と金額合計を自動表示。データが追加されたら、ピボットを右クリック → 更新で最新化される。
記入例: ピボット出力
| 部署 | 件数 | 金額合計 |
|---|---|---|
| 営業1課 | 3 | 200,000 |
| 営業2課 | 2 | 150,000 |
| 総計 | 5 | 350,000 |
ピボットテーブルなら、新しい部署や案件が追加されても自動で行が増えます。
実務での注意点
- 印刷用の集計表（紙で配布する月次レポートなど）には向きません。印刷物には小計が必要なので、印刷用シートを別途作って小計を表現してください
- データシートを Ctrl+T でテーブル化しておくと、行が追加されても SUMIF・ピボットの範囲が自動拡張されます
- 集計ビューシートで使う SUMIF・COUNTIF は、データシートの列追加・並び替えに強い構造です。列名で参照する形（テーブル機能）を使うとさらに安定します
- ピボットテーブルは更新を忘れがちです。「更新を自動で行う」設定（ピボット → 分析 → オプション → 開くときに更新）にしておくと安心です
- 既存の小計行を削除する前に、必ずバックアップ（別シートまたは別ファイル）を取ってください。誤って明細行も消すと復元が大変です
まとめ
小計が二重計上される管理表の多くは、データ行と小計行が同じ表に同居していることが原因です。20分で明細だけのデータシートに整理し、集計ビューやピボットで小計を別途表示する設計に切り替えるだけで、SUMやピボットでの二重計上が起きない状態になります。
データと集計の分離とあわせて、セル結合や空白行の整理も進めると、データ構造の正規化が完成します。あわせて以下を参照してください。
