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

4 min 18,478 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つ計算式を入力する手間が省けるので、時短にもつながり、効率的に作業を進めることができます。

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

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

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

ExcelドクターがおすすめするExcel本はこちら

カテゴリー:

20万人以上が参考にしたエクセルドクターで最も人気の記事

エクセルで家計簿を作る方法

ワード初心者

ワード初心者

エクセルで本格的な家計簿を作ってみたいんだけど詳しく解説してる本とかがないんだよね・・・
徹底的に解説してほしいな!

エクセルで本格家計簿の作り方なら私に任せて!過去に20万人以上のユーザーが参考にした解説記事を紹介するね♪

Dr.オフィス

Dr.オフィス

エクセルを少し使えるようになってきたら、本格的な家計簿を作ってみるのがとてもおすすめです。

収支シートの出来上がりイメージ
収支シートの出来上がりイメージ

基本機能をしっかりと使って作るから、エクセルをマスターするのにも家計簿づくりはぴったり!

20万人以上が参考にしたエクセルでの本格的な家計簿の作り方はこちら↓↓

【エクセル家計簿の作り方】月ごとに入力して合計を反映させれば見やすくなる!

関連記事