今回は、Excelのピボットテーブルを使って、集計表を作り直しやすくする方法を紹介します。
ピボットテーブルが向いている作業
Excelで売上、経費、在庫、問い合わせ、作業時間などの一覧を扱うとき、手作業で小計を作ると修正が増えやすくなります。行を追加したり分類を変えたりするたびに、関数の範囲や並び順を確認する必要があります。
ピボットテーブルは、一覧形式のデータをもとに、項目ごとの合計や件数を切り替えながら確認できる機能です。元データを集計用に崩さず、別の視点で集計表を作れることが利点です。
元データを整える
ピボットテーブルを使う前に、元データの形を整えます。見た目を整えた表よりも、1行に1件の情報が入っている一覧表が向いています。結合セルや空白行が多い表は、集計の対象として扱いにくくなります。
元データでは、列見出しを分かりやすく付けます。たとえば、日付、担当者、商品名、分類、金額、ステータスのように、1列につき1種類の情報にします。同じ列に複数の意味を入れると、後で集計しづらくなります。
整えておきたいポイント
ピボットテーブルに使う元データでは、次の点を確認します。
- 列見出しが空白になっていない
- 同じ意味の項目名が表記ゆれしていない
- 日付や金額が文字列として入力されていない
- 途中に空白行や不要な合計行が入っていない
- 1つのセルに複数の情報を詰め込んでいない
この準備をしておくと、ピボットテーブルを作成した後の修正が少なくなります。集計機能の前に、一覧表として扱いやすい状態にすることが大切です。
ピボットテーブルを作成する
元データの中にカーソルを置き、挿入タブからピボットテーブルを選びます。作成先は新しいワークシートにすると、元データと集計表を分けて管理できます。元データを見ながら確認したい場合は、同じブック内の別シートに置くのが扱いやすい方法です。
作成後は、フィールド一覧から行、列、値、フィルターに項目を配置します。たとえば、担当者ごとの金額を見たいなら、行に担当者、値に金額を入れます。月ごとに見たい場合は、日付を行や列に置き、グループ化して月単位にします。
行と列の配置を試す
ピボットテーブルでは、項目の置き場所を変えるだけで集計の見方が変わります。行に分類、列に月を置くと、分類別の月次集計になります。行に担当者、列にステータスを置くと、担当者ごとの進捗確認に使えます。
最初から正解の形を決めようとせず、いくつか配置を試すと必要な表が見つかりやすくなります。集計表を作り込む前に、何を比較したいのかを決めると、不要な項目を入れずに済みます。
値フィールドの集計方法
値エリアに入れた項目は、合計、件数、平均などに切り替えられます。金額なら合計、案件数なら件数、単価の傾向を見るなら平均が候補になります。
数値のはずなのに件数で集計される場合は、元データで数値が文字列になっている可能性があります。セルの表示だけでなく、実際のデータ型を確認します。
フィルターとスライサーを使う
ピボットテーブルには、条件を絞って見るためのフィルターがあります。特定の期間、担当者、分類だけを見たいときに便利です。さらに、スライサーを使うとボタン形式で絞り込みができます。
スライサーは、他の人に集計表を見せるときにも使いやすい機能です。条件を変えるたびにフィルターの小さなメニューを開かなくても、画面上のボタンで切り替えられます。
更新を忘れない
元データを追加または修正しても、ピボットテーブルは自動で常に最新になるとは限りません。集計結果を確認する前に、ピボットテーブルを右クリックして更新します。ブックを開いたときに更新する設定もありますが、提出前には手動で確認するとよいでしょう。
元データの範囲が固定されている場合、後から追加した行が集計対象に入らないことがあります。元データをテーブルとして設定しておくと、行を追加したときに範囲を扱いやすくなります。
テーブル化しておく利点
元データを選び、挿入タブからテーブルを作成しておくと、列見出しや範囲が管理しやすくなります。ピボットテーブルの元にする範囲としてテーブル名を使えば、行の追加にも対応しやすくなります。
テーブル名は、内容が分かる名前に変えておくと後から管理しやすくなります。たとえば、売上一覧、経費一覧、問い合わせ一覧のようにしておくと、複数の集計を作るときに迷いにくくなります。
見た目を整える
ピボットテーブルは、作成した直後の状態でも集計できますが、見る人に合わせて表示を整えると使いやすくなります。数値には桁区切りや通貨表示を設定し、不要な小計や総計は目的に応じて非表示にします。
列幅を調整し、項目名を短くしておくと、画面上で確認しやすくなります。集計表を印刷する場合は、ページの向きや拡大縮小も確認します。見た目を整える作業は、集計が正しいことを確認した後に行うと戻り作業を減らせます。
元データを消さない運用
ピボットテーブルを作ると、集計表だけを残したくなることがあります。しかし、元データを消すと、後で条件を変えて確認することが難しくなります。元データ、集計表、提出用の表を分けると管理しやすくなります。
提出用の表だけを整える場合は、ピボットテーブルをコピーして値として貼り付ける方法があります。この場合、貼り付けた表は更新されないため、作業用と提出用を取り違えないようにします。
よくあるつまずき
ピボットテーブルでよくあるのは、同じ項目が分かれて表示されることです。担当者名や分類名に余分な空白が入っていたり、全角と半角が混ざっていたりすると、別項目として集計されます。元データ側で表記をそろえることが対策になります。
日付が月ごとにまとまらない場合は、日付として認識されていない可能性があります。見た目が日付でも、文字列として入っているとグループ化できないことがあります。元データの入力形式を確認します。
集計結果を説明しやすくする
ピボットテーブルを資料に使う場合は、何を集計しているのかが分かるように見出しを付けます。単に「集計」と書くより、「担当者別の対応件数」「分類別の金額合計」のように、行と値の意味を含めると読み手が理解しやすくなります。
また、フィルターを使っている場合は、どの条件で絞っているのかを表の近くに残します。条件が分からない集計表は、後から見直したときに判断しづらくなります。集計条件を表の外に少し書き添えるだけでも、確認しやすい資料になります。
まとめ
Excelのピボットテーブルは、一覧データをいろいろな視点で集計するための機能です。元データを1行1件の形に整え、列見出しや表記をそろえておくと、集計表を作り直しやすくなります。
行、列、値、フィルターの配置を試しながら、見たい切り口を探します。元データをテーブル化し、更新操作を確認することも大切です。集計結果だけでなく、元データの形を整えることが、ピボットテーブルを使いこなす近道です。