Excelでカテゴリ別・担当者別・月別に合計や件数を集計するなら、複雑なSUMIFSを増やす前にピボットテーブルを検討します。元データを「1行1明細、1列1項目」の表へ整え、Excelテーブルにしてから挿入すると、行・列・値・フィルターをドラッグするだけで集計できます。ただしピボットは元データの写しであるキャッシュを使うため、追加データを反映するには更新と検算が必要です。
速さは利点ですが、「秒でできた」ことと「正しい」ことは別です。空白、重複、文字列の数値、更新漏れ、集計方法の誤りを確認してから結果を共有します。
ピボットに向く集計と関数に向く処理
ピボットは、売上を商品・支店・月で切り替える、回答件数を属性別に数える、平均や最大値を比較するなど、切り口を探索する集計に向きます。結果表の形が頻繁に変わる作業でも、数式を書き直さず配置を変えられます。一方、明細行ごとの判定、固定帳票のセルへ値を返す、別処理が数式結果を参照する場合は関数が適することがあります。目的に応じて併用します。
元データを整える
- 先頭行に重複しない見出しを置き、途中に見出し行や空白行を挟まない
- 一つの列には日付、数値、文字列など同じデータ型を入れる
- セル結合、小計行、手入力の色分けを集計条件にしない
- 1セルへ「商品A／商品B」のように複数値を詰め込まない
- 数値に単位文字を付けず、表示形式で円・個・%を表す
- 個人情報を含む場合は共有範囲と出力粒度を先に決める
Microsoft公式は、表形式で空白行・空白列を避け、同じ列のデータ型を揃え、Excelテーブルをデータソースにすることを推奨しています。テーブルなら末尾に行を追加したとき、更新後のピボットへ含めやすく、新しい列もフィールド一覧へ現れます。
基本手順
- 元データを別名保存し、件数・合計・期間を控えます。
- データ内の一セルを選び、挿入からテーブルを作成します。「先頭行を見出しとして使用」を確認します。
- テーブル内を選び、挿入からピボットテーブルまたはおすすめピボットテーブルを選びます。
- 原則として新しいワークシートへ配置し、元データを上書きしないようにします。
- フィールドを行、列、値、フィルターへ配置し、値の集計方法を確認します。
- 元データを追加したらピボットを更新し、控えた件数・合計と照合します。
「個数」になってしまう理由
数値列を値エリアへ入れたのに合計ではなく個数になる場合、列に空白、エラー、数値に見える文字列が混在している可能性があります。値フィールドの設定で合計へ変えるだけでなく、元データの型を直します。文字列の数字を無理に合計した結果は欠落を隠すため、入力規則やPower Queryで整形します。
日付を月別・年別にまとめる
日付列が正しいExcel日付なら、行へ置いて年・四半期・月でグループ化できます。文字列の日付や空白があるとグループ化に失敗します。会計年度が暦年と違う場合、元データへ会計年度・会計月の列を追加すると定義が明確です。タイムゾーンをまたぐデータは、どの基準日で集計するかを決めます。
更新の考え方
ピボットは元データを直接ライブ表示するとは限りません。Microsoft公式の更新手順に従い、右クリックの更新やRefresh Allを使います。2026年のMicrosoft 365ではローカルデータ由来の新規ピボットに自動更新機能が提供される環境もありますが、共有ブック・既存ブック・版で設定が異なるため、更新日時を結果表へ明記します。
外部接続を含むRefresh Allはデータベースや認証へアクセスします。更新前に接続先、資格情報、クエリ、行数を確認し、機密データが新たに取り込まれないかを確認します。マクロや外部接続の警告を回避して有効化しません。
正しさを検算する
- 元データの明細件数とピボットの総個数が一致する
- 元データの合計とピボットの総計が一致する
- 空白・エラー・除外フィルターの件数を別に確認する
- 日付の最小・最大が想定期間内である
- 同名に見える前後スペースや全角半角が別項目になっていない
- 平均は単純平均か加重平均か、業務定義と一致する
フィルターと共有時の注意
ピボットのフィルターが残ったまま結果をコピーすると、全体値と誤認されます。共有前にフィルター、スライサー、展開状態、非表示項目を確認し、タイトルに対象期間と条件を書きます。明細のドリルダウンで新シートが作成されると個人情報が複製されるため、共有版から不要な明細シートを除きます。
戻し方と保守
ピボットは元データを変更しないため、結果が誤っていても元データを残していれば作り直せます。変更前ブックを別名保存し、フィールド配置、集計方法、更新日時を記録します。テーブル範囲や接続先を変えた場合は旧設定を控え、問題が出たら旧ブックへ戻します。月次定型なら検算セルと更新チェックを手順書へ組み込みます。
集計表を業務で再利用する設計
一度だけの分析なら手動配置でも十分ですが、毎月更新するなら元データ、変換、ピボット、配布用表を別シートに分けます。元データシートへ小計や説明文を置かず、入力規則とテーブル名を固定します。ピボットシートには作成者、データ基準日、最終更新、フィルター条件を表示し、閲覧者が古い結果を見分けられるようにします。
前月ブックをコピーして明細だけ差し替える運用は、データソースが旧範囲や旧ファイルを参照し続ける事故が起きます。テンプレートを一つ管理し、当月データを同じテーブルへ読み込むか、Power Queryで承認済みフォルダーから取り込みます。外部接続のプライバシーレベルと資格情報を確認します。
集計方法を選び直す
値フィールドは合計、個数、平均、最大、最小などへ変更できます。単価の平均と売上単価、割合の平均と全体割合は同じではありません。業務定義書で分母・分子を確認し、必要なら元データへ数量と金額を持たせ、加重平均を計算します。計算フィールドやメジャーを使う場合は式とゼロ除算をレビューします。
Distinct Countが必要な場合、データモデルへ追加する選択で利用できる環境がありますが、通常の個数と意味が違います。顧客IDの空白や表記揺れを整え、サンプルで手数えと一致するか確認します。ブックを別版Excelで開く利用者がいる場合は互換性も試します。
数値が合わないときの分岐
総計が元データより小さいなら、フィルター、空白、文字列数値、データソース範囲、更新を確認します。大きいなら重複行、結合時の多対多、同じ明細の二重取込を疑います。カテゴリだけ違うなら前後空白、改行、全角半角、別コードを確認します。いきなり結果を手入力で修正せず、元データまたは変換ルールを直します。
ドリルダウンで総計をダブルクリックすると明細シートを生成できる環境があります。差分調査には便利ですが、個人情報を複製します。調査後は承認された保存先へ移すか削除し、配布版へ残さないでください。
結果を固定値で渡す場合
受信者がピボットを更新できない、元データを共有できない場合は、配布用ブックへ値として貼り付けます。その際も元ブック、更新日時、検算記録を保管します。行列の非表示やフィルター状態を解除し、印刷・PDFで総計が切れないか確認します。
数字を意思決定へ使う前に、データ所有者と集計定義の承認を得ます。「関数よりピボットが常に正確」なのではなく、入力データと設定が正しいときに効率的という位置付けです。
数式へ変換する前に
ピボット結果をGETPIVOTDATAや通常数式から参照すると、フィールド名や配置変更で参照が変わります。固定帳票へ連携するなら、参照式、フィールド名、更新順をテストし、エラー時に前月値を残して誤配布しないようにします。ピボットを削除する前に依存数式を検索し、別シートや名前定義も確認します。
コメント