今回は、Excelのピボットテーブルの値フィールド設定を使いこなす方法を紹介します。
値フィールド設定とは
Excelのピボットテーブルでは、行や列に分類項目を置き、値エリアに集計したい数値を置きます。この値エリアで、合計、個数、平均、最大、最小などの集計方法を変えるための機能が値フィールド設定です。
Excel ピボットテーブル
値フィールド設定を理解しておくと、同じ元データから複数の見方を作れます。売上金額の合計、案件数の個数、平均単価、構成比、前月との差などを、目的に合わせて表示できます。
ピボットテーブルを作った直後は、自動で合計や個数が選ばれます。数値列なら合計、文字列や空欄が混ざる列なら個数になることがあります。意図した集計になっているか、最初に値フィールド設定を確認することが大切です。
集計方法を変更する
値フィールド設定では、集計方法を選べます。売上や数量なら合計がよく使われますが、件数を数えたい場合は個数、平均値を見たい場合は平均を使います。
- 合計
- 個数
- 平均
- 最大
- 最小
- 標準偏差
たとえば、売上金額を値エリアに入れたとき、合計で表示すれば部門別の売上を見られます。同じ売上金額をもう一度値エリアに追加し、平均に変更すれば、部門別の平均売上も並べて確認できます。
数値なのに個数になる場合
本来は合計したい列なのに、ピボットテーブルで個数になってしまうことがあります。原因として、元データの列に文字列、空白、エラー値が混ざっている場合があります。
まず元データを確認し、数値列として扱える状態に整えます。金額列に「未定」や「-」のような文字が入っていると、数値として集計しにくくなります。必要に応じて空欄にする、別列で数値に変換するなどの対応をします。
表示形式を整える
値フィールド設定では、表示形式も設定できます。金額なら桁区切り、割合ならパーセント、日数なら小数なしなど、読みやすい形に整えます。
- 金額に桁区切りを付ける
- 割合をパーセントで表示する
- 小数点以下の桁数をそろえる
- 負の値の表示を見やすくする
通常のセルの表示形式を変えるだけでも見た目は変わりますが、ピボットテーブルでは値フィールド設定から表示形式を指定しておくと、更新後も保たれやすくなります。集計表を定期的に使う場合は、この設定をしておくと手直しが減ります。
フィールド名も分かりやすくする
値エリアに同じ項目を複数入れると、「合計 / 売上金額」「平均 /
売上金額」のように表示されます。必要に応じて「売上合計」「平均売上」など、見出しを分かりやすく変更します。
ただし、元データの列名とまったく同じ名前にはできないことがあります。少し表現を変え、集計内容が分かる名前にします。
計算の種類を使う
値フィールド設定には、単に合計するだけでなく、表示方法を変える機能があります。「計算の種類」を使うと、構成比や差分、累計などを表示できます。
- 総計に対する比率
- 列集計に対する比率
- 行集計に対する比率
- 前の項目との差
- 累計
- 順位
売上金額をそのまま表示するだけでは、全体の中でどの部門がどれくらいの割合を占めるのか分かりにくい場合があります。総計に対する比率を使えば、金額と割合を並べて見られます。
同じ値を複数回使う
ピボットテーブルでは、同じ数値項目を値エリアに複数回入れられます。1つ目は合計、2つ目は構成比、3つ目は前月差のように設定すれば、1つの表で複数の視点を確認できます。
このとき、列見出しが長くなりすぎると表が読みにくくなります。表示名を短く整え、必要な指標だけを残すと見やすくなります。
元データの整え方
値フィールド設定を活用するには、元データの形式が整っていることが前提です。集計したい数値は1列にまとめ、見出し行を1行にし、空白列や結合セルを避けます。
- 見出し行を1行にする
- 同じ種類のデータを同じ列に入れる
- 金額や数量は数値で入力する
- 空白行を表の途中に入れない
- セル結合を使わない
見た目を整えた表と、集計しやすい表は違います。ピボットテーブル用の元データは、装飾よりも列の意味がそろっていることを優先します。
値フィールド設定で意図した結果を出すには、元データの列が正しく扱える状態であることが重要です。
更新後の確認ポイント
ピボットテーブルは、元データを更新した後に表を更新する必要があります。更新後は、値フィールド設定が意図したままか確認します。新しいデータに文字列が混ざったことで、集計が変わる場合があります。
- 集計方法が合計になっているか
- 表示形式が保たれているか
- 構成比や差分の基準が合っているか
- 空白やエラーが増えていないか
- フィールド名が分かりやすいか
月別比較では、項目の並びが変わると差分の見え方に影響することがあります。日付や月の並び順も合わせて確認します。
複数の値を並べるときの見せ方
値フィールドを複数並べる場合は、表の幅が広がりすぎないようにします。合計、構成比、前月差、件数をすべて入れると便利に見えますが、読み手がどの数字を見るべきか迷うことがあります。目的に合わせて、必要な指標を選びます。
報告用の表では、まず合計や件数などの基本指標を置き、補足として構成比や差分を追加すると読みやすくなります。確認用の表では指標を多めにしてもよいですが、共有用では列名を短くし、表示形式をそろえることが大切です。
確認用と提出用を分ける
ピボットテーブルは、作業中の確認用と提出用で見せ方を分けると扱いやすくなります。確認用では複数の値フィールドを並べ、原因を探しやすくします。提出用では必要な指標に絞り、列名と表示形式を整えます。
まとめ
Excelのピボットテーブルの値フィールド設定を使うと、同じ元データから合計、個数、平均、構成比、差分、累計などを表示できます。集計方法と表示形式を整えるだけで、表の読みやすさが変わります。
数値列なのに個数になる場合は、元データに文字列や空白が混ざっていないか確認します。表示形式は値フィールド設定から指定しておくと、更新後も扱いやすくなります。
Excel ピボットテーブル
値フィールド設定は、集計表を目的に合わせて見せるための基本機能です。元データを整え、必要な指標だけを選んで設定すれば、確認しやすいピボットテーブルを作れます。