楽天リーベイツで楽天ポイント還元最大20%!今なら登録して初回購入で500ポイントもGET!

エクセル【ピボットテーブル】集計値同士を使った高度な計算をしよう!!

4 min 65,202 views
エクセル初心者

エクセル初心者

ピボットテーブルの集計値同士の計算は、どうやって設定するのかな?

ピボットテーブルの集計値同士の計算は、ピボットテーブルの『計算の種類』を選択すると簡単にできるよ。とても便利だから、これから解説するね。

Dr.オフィス

Dr.オフィス

今回は、ピボットテーブルの集計値同士の計算について解説します。

ピボットテーブルは、ピボットテーブルの値フィールドの集計方法を変更で解説した集計方法以外にも、『計算の種類』を指定し、集計値同士の計算をすることができます。

例えば、商品分類の売上全体に占める割合を計算したり、前月比や前年比を簡単に計算することができますよ。

ピボットテーブルの集計値同士の計算でできること
  • 計算式を入力することなく、比率、累計、差分などの高度な計算が一瞬でできる

ピボットテーブルの『計算の種類』を指定すると、比率、累計、差分などの計算が簡単にできます。

ピボットテーブルの集計については、こちら≫【エクセル】ピボットテーブルの集計方法を解説!今すぐ使える便利機能を紹介で詳しく解説しています。

次項より、ピボットテーブルの集計値同士の計算について詳しく解説します。

※本記事は『OS:Windows10』画像は『Excelのバージョン:2016』を使用しています。

ピボットテーブルの集計値同士の計算

ピボットテーブルの集計値同士の計算は、目的に合った計算方法を『計算の種類』から指定できます。

ここでは、下記の4種類の計算方法について解説します。

  • 売上構成比を求める計算
  • 売上金額の前月比を求める計算
  • 集計値の累計を求める計算
  • 金額の降順で順位を求める

集計値同士を使った売上構成比

一般的に商品の売上構成比を求めるには、各商品の売上を全商品の売上で割って、パーセント表示にする必要がありますが、ピボットテーブルでは、『計算の種類』を指定するだけで、簡単に計算ができます。

右クリック-[値フィールドの設定]を選択
右クリック-[値フィールドの設定]を選択

↑それでは、集計値同士を使って月別の売上の割合を計算してみましょう。

  1. ピボットテーブルの『値』エリアのいずれかの集計値の上で右クリックを選択
  2. [値フィールドの設定]を選択
[値フィールドの設定]ダイアログボックスより設定
[値フィールドの設定]ダイアログボックスより設定

↑[値フィールドの設定]ダイアログボックスが表示されたら、下記の通りに設定しましょう。

  1. [計算の種類]を選択
  2. [列集計に対する比率]を選択
  3. [OK]を選択
[列集計に対する比率]を選択後のピボットテーブル
[列集計に対する比率]を選択後のピボットテーブル

↑月別における、商品分類別の売上構成比が計算されました。

[計算の種類]を指定するだけで、一気にパーセント表示にしてくれるのは、とても便利だね。

Dr.オフィス

Dr.オフィス

集計値同士を使った売上金額の前月比

ピボットテーブルで売上金額が前の月に比べて何%増減したかを求める場合にも、計算式を入力する必要はありません。

右クリック-[値フィールドの設定]を選択
右クリック-[値フィールドの設定]を選択

↑それでは、集計値同士を使って売上金額の前月比を計算してみましょう。

  1. ピボットテーブルの『値』エリアのいずれかの集計値の上で右クリック
  2. [値フィールドの設定]を選択
[値フィールドの設定]ダイアログボックスより設定
[値フィールドの設定]ダイアログボックスより設定

↑[値フィールドの設定]ダイアログボックスが表示されたら、下記の通りに設定しましょう。

  1. [計算の種類]を選択
  2. [基準値との差分の比率]を選択
  3. [月]を選択
  4. [(前の値)]を選択
  5. [OK]を選択
[基準値との差分の比率]を選択後のピボットテーブル
[基準値との差分の比率]を選択後のピボットテーブル

↑売上金額の前月比が計算されました。

また、1月にはデータが表示されていませんが、これは比べる値(前月である12月のデータ)が存在しないからです。

集計値同士を使った累計

ピボットテーブルで、『○○日時点の売上』や『売上高上位○位までの商品の売上合計』などがすぐ分かるようにするには、集計値を上から順に加算する累計を使います。

それでは、集計値同士を使って累計を計算してみましょう。

『金額』の『合計』フィールドを『値』エリアに追加
『金額』の『合計』フィールドを『値』エリアに追加

あらかじめ、『金額』の『合計』フィールドをもう1つ『値』エリアに追加しておきます。

右クリック-[値フィールドの設定]を選択
右クリック-[値フィールドの設定]を選択

↑ピボットテーブルに『合計/金額2』の集計値が追加されたら、下記の通りに設定しましょう。

  1. ピボットテーブルの『値』エリアにある『合計/金額2』のいずれかの集計値の上で右クリック
  2. [値フィールドの設定]を選択
[値フィールドの設定]ダイアログボックスより設定
[値フィールドの設定]ダイアログボックスより設定

↑[値フィールドの設定]ダイアログボックスが表示されたら、下記の通りに設定しましょう。

  1. [計算の種類]を選択
  2. [累計]を選択
  3. [月]を選択
  4. [OK]を選択
列見出しを『累計』に変更
列見出しを『累計』に変更

↑最後に『合計/金額2』の列見出しを『累計』に変更します。

[値フィールドの設定]ダイアログボックスを開き、下記の通りに設定しましょう。

  1. [名前の指定]-[累計]と入力
  2. [OK]

または、『合計/金額2』フィールドのいずれかの上でF2を押して、直接入力することもできます。

[累計]を選択後のピボットテーブル
[累計]を選択後のピボットテーブル

↑累計が計算され、列見出しが『合計/金額2』から『累計』に変更されましたね。

集計値同士を使った金額の降順で順位を求める

一般に、順位を求めるには関数を利用しますが、ピボットテーブルでは、関数を設定しなくても簡単に素早く順位を求めることができます。

右クリック-[値フィールドの設定]を選択
右クリック-[値フィールドの設定]を選択

↑それでは、集計値同士を使って累計を計算してみましょう。

  1. ピボットテーブルの『値』エリアのいずれかの集計値の上で右クリック
  2. [値フィールドの設定]を選択
[値フィールドの設定]ダイアログボックスより設定
[値フィールドの設定]ダイアログボックスより設定

↑[値フィールドの設定]ダイアログボックスが表示されたら、下記の通りに設定しましょう。

  1. [計算の種類]を選択
  2. [降順での順位]を選択
  3. [商品名]を選択
  4. [OK]を選択
[降順での順位]を選択前と選択後のピボットテーブル
[降順での順位]を選択前と選択後のピボットテーブル

↑商品の売上金額の高い順に、順位が表示されました。

累計を計算したときのように、『金額』の『合計』フィールドを追加して順位を表示すると、金額と並んで順位を確認できるから見やすくなるね。

Dr.オフィス

Dr.オフィス

次項では、ピボットテーブルの『計算の種類』の一覧を紹介します。

ピボットテーブルの『計算の種類』の一覧

ピボットテーブルの『計算の種類』を下記の一覧にまとめました。

計算の種類内容
計算なし[集計の方法]タブで選択した計算
総計に対する比率総合計に対する各項目の比率を求める
列集計に対する比率列の合計に対する各項目の比率を求める
行集計に対する比率行の合計に対する各項目の比率を求める
基準値に対する比率[基準フィールド]の[基準アイテム]で選択した数値に対する比率を求める
親行集計に対する比率[項目の数値÷行ラベルの親項目の数値]を計算
親列集計に対する比率[項目の数値÷列ラベルの親項目の数値]を計算
親集計に対する比率[項目の数値÷『基準フィールド』で選択したフィールドの親項目]を計算
基準値との差分[基準フィールド]の[基準アイテム]で選択した数値との差を計算
基準値と差分の比率[『基準フィールド』の『基準アイテム』で選択した数値との差÷『基準フィールド』の『基準アイテム』で選択した数値]を計算
累計[基準フィールド]の数値の累計を求める
比率の累計[基準フィールド]の数値の比率の累計を求める
昇順での順位数値を昇順に並べたときの順位を求める
降順での順位数値を降順に並べたときの順位を求める
指数(インデックス)([セルの値] x [総計]) ÷ ([行の総計] x[列の総計])を求める

説明文だけでは、分かりにくい『計算の種類』については、実際に試してみて、結果を確認してみるといいよ。

Dr.オフィス

Dr.オフィス

ピボットテーブルの集計値同士の計算で作業効率を上げよう!

ピボットテーブルの集計値同士で目的に合った『計算の種類』を指定すると、高度な計算が簡単にできましたね。

1つ1つ計算式を入力する手間が省けるので、時短にもつながり、効率的に作業を進めることができます。

ピボットテーブルの集計値同士の計算を活用して、より分析しやすい表を作成しましょう。

ピボットテーブルの集計については、こちら≫【エクセル】ピボットテーブルの集計方法を解説!今すぐ使える便利機能を紹介で詳しく解説しています。

さらに、ピボットテーブルの概要については、こちら≫【エクセル】ピボットテーブルとは?実は難しくない!?初心者でも集計と分析が簡単にできる、で詳しく解説しています。

関連記事