今回は、Excelのピボットテーブルで集計表を作りやすくする方法を紹介します。
ピボットテーブルを使う目的
Excelで売上、在庫、問い合わせ、勤怠、アンケートなどの一覧データを扱うとき、手作業で集計表を作ると修正に時間がかかります。行や列を追加するたびに数式を直したり、コピー範囲を確認したりする必要があるためです。
ピボットテーブルを使うと、元データをもとに分類別の集計表を作れます。商品別、担当者別、月別、拠点別など、見たい切り口を後から変更できるのが特徴です。
特に、同じデータをいくつかの角度から確認したいときは、集計の形を作り直しやすいことが大きな利点です。数式を多く並べる前に、ピボットテーブルで整理できないか考えると、作業の見通しが良くなります。
元データを整える
ピボットテーブルは、元データの状態に影響を受けます。集計機能そのものよりも、最初の表づくりが大切です。元データが乱れていると、集計結果も見づらくなります。
1行目に見出しを入れる
ピボットテーブルでは、列見出しがフィールド名として使われます。見出しが空欄だったり、同じ名前が複数あったりすると、集計時に扱いにくくなります。
見出しは短く、内容が分かる名前にします。
- 日付
- 商品名
- 担当者
- 部門
- 数量
- 金額
「備考1」「項目A」のような名前は、後から見たときに意味が分かりにくくなります。自分以外が使う可能性がある表では、列名だけで内容を判断できるようにしておくと便利です。
空白行や結合セルを避ける
一覧データの途中に空白行があると、Excelが表の範囲を正しく認識しにくくなります。また、結合セルは見た目を整えるには便利ですが、集計データとしては扱いにくい形式です。
ピボットテーブルに使う元データは、1行に1件の情報を入れる形にします。たとえば1件の注文なら、日付、商品、担当者、金額などを同じ行に並べます。見た目よりも、データとしての扱いやすさを優先するのがポイントです。
表形式に変換しておく
元データをExcelのテーブル形式にしておくと、後から行を追加したときにピボットテーブルの参照範囲を管理しやすくなります。テーブル名を付けておけば、どのデータを集計しているのかも分かりやすくなります。
テーブル名は「売上データ」「問い合わせ一覧」のように、内容を表す名前にします。複数のピボットテーブルを作る場合でも、元データの場所を確認しやすくなります。
基本の集計表を作る
ピボットテーブルを作るときは、最初から完成形を狙いすぎず、基本の集計表から始めると作りやすくなります。まずは何を行に置き、何を値として集計するかを決めます。
行に分類、値に数値を置く
商品別の売上を見たい場合は、行に商品名、値に金額を置きます。担当者別に確認したい場合は、行に担当者、値に金額を置きます。
このとき、値エリアが「合計」ではなく「データの個数」になることがあります。これは、金額列に文字列や空白が混じっている場合に起こりやすいです。集計結果が想定と違うときは、元データの列が数値として入力されているか確認します。
列を使って比較しやすくする
月別や部門別に横方向で比較したいときは、列エリアを使います。たとえば行に商品名、列に月、値に金額を置くと、商品ごとの月別集計表になります。
列が増えすぎると見づらくなるため、比較したい項目だけを列に置くのが扱いやすい方法です。細かい分類はフィルターに回すと、表全体が広がりすぎません。
見やすくするための設定
ピボットテーブルは作っただけでは、読みやすい表にならないことがあります。表示形式や並び順を整えることで、集計結果を確認しやすくなります。
数値の表示形式を整える
金額や数量を扱う場合は、桁区切りや小数点の表示を整えます。セルに直接書式を設定するより、値フィールドの設定から表示形式を指定すると、更新後も設定が保たれやすくなります。
金額なら桁区切り、割合ならパーセント、件数なら整数表示にするなど、項目の意味に合わせて設定します。集計表は数字を出すだけでなく、読み間違いを防ぐ表示にすることが大切です。
不要な小計を消す
分類が多いピボットテーブルでは、小計が多く表示されることがあります。小計が必要ない場合は非表示にすると、表がすっきりします。
逆に、部門ごとの合計を確認したい場合は小計を残すと便利です。小計はすべて消すものではなく、読み手が必要とする単位に合わせて使います。
並び順を調整する
金額の大きい順、件数の多い順、日付順など、目的に合わせて並び替えます。初期状態の並び順のままだと、確認したい項目が見つけにくいことがあります。
月別の集計では、月名が文字列として扱われていると、意図しない順番になることがあります。その場合は、元データの日付を日付形式にそろえ、ピボットテーブル側でグループ化すると扱いやすくなります。
フィルターとスライサーを使う
ピボットテーブルでは、条件を絞って集計結果を確認できます。定期的に見る集計表では、フィルターやスライサーを使うと操作しやすくなります。
フィルターで対象を絞る
部門、担当者、地域、商品区分などをフィルターに入れると、必要な条件だけを表示できます。資料作成時に「特定部門だけの集計を見たい」という場面で便利です。
フィルターを使う場合は、現在どの条件で表示しているかを確認する習慣を持ちます。条件が残ったまま集計結果を見ると、全体の数字と勘違いすることがあります。
スライサーで操作しやすくする
スライサーは、ボタン形式で条件を切り替えられる機能です。自分以外の人に集計表を渡すときや、会議中に条件を変えながら確認するときに使いやすいです。
スライサーを使う項目は、選択肢が多すぎないものにします。担当者が多い表で全員分のスライサーを出すと画面を圧迫するため、部門や期間など、切り替え頻度が高い項目を選ぶと扱いやすくなります。
更新時に確認すること
ピボットテーブルは、元データを更新しただけでは結果が変わらない場合があります。新しいデータを追加したら、ピボットテーブルを更新して反映します。
- 元データに新しい行が含まれているか確認する
- ピボットテーブルを更新する
- フィルター条件が残っていないか確認する
- 空白や表記ゆれが増えていないか確認する
- 集計結果の単位や表示形式が崩れていないか確認する
特に表記ゆれには注意が必要です。「東京支店」と「東京
支店」のように文字が少し違うだけで、別項目として集計されます。元データの入力ルールを決めておくと、集計後の修正を減らせます。
まとめ
Excelのピボットテーブルは、一覧データをさまざまな切り口で集計できる便利な機能です。使いやすい集計表を作るには、ピボットテーブルの操作だけでなく、元データを整えることが欠かせません。
見出しをそろえ、空白行や結合セルを避け、テーブル形式にしておくと、後からデータを追加したときも管理しやすくなります。集計後は、表示形式、並び順、小計、フィルターを整えることで、読み手が確認しやすい表になります。
更新時には、元データの範囲、フィルター条件、表記ゆれを確認しましょう。ピボットテーブルを活用すれば、集計表の作成と見直しを進めやすくなります。