ピボットテーブルの集計方法を任意の種類へ変更する方法 [ピボットテーブル基礎]
AさんAさん

ピボットテーブルで集計すると、自動的に「合計」になってしまいますが、データの数をカウントしたりできないんですか?

森田森田

ピボットテーブルは集計方法を任意のものに変更できますよ!

では、詳細を解説していきますね。

はじめに

本題に入る前に、この記事がおすすめな方を挙げてみます。

  • Excelでデータの集計・分析作業を行うことが多い方
  • ピボットテーブルを実務で使う機会がある方
  • ピボットテーブルで集計方法を変更できず困っている方

前提条件

この記事はピボットテーブルの概要を理解していることが前提です。
→まず、以下の記事で概要を把握することをおすすめします。

ピボットテーブルは「値」ボックスにセットしたデータによってデフォルトの集計方法が決まる

まず、ピボットテーブルの集計方法の仕様ですが、「値」ボックスにドロップしたフィールドのデータによって、デフォルトの集計方法が決まります。

具体的には、以下のように数値データのフィールドなら「合計」、数値以外のデータのフィールドなら「個数」で集計されます。

この集計方法は、任意の種類へ変更することが可能です。

では、集計方法の変更手順を確認していきましょう。

ピボットテーブルの集計方法の変更手順

今回の前提として、すでにピボットテーブルの集計条件がセットされた状態からスタートします。

ピボットテーブルの集計方法の変更は、以下の2ステップとなります。

STEP1】「値フィールドの設定」ダイアログを起動

まず、「値」ボックス内の集計方法を変更したいフィールド名の「▼」をクリック(①)します。

すると、メニューが表示されますので、その中から「値フィールドの設定」をクリック(②)してください。

【参考】手順①②は右クリックメニュー経由でもOK

手順①②は以下のように右クリックメニュー経由(ピボットテーブルレポートの任意のフィールド上で右クリック→「値フィールドの設定」をクリック)でもOKです。

以降の手順や効果は変わりませんので、状況に合わせて使い分けてください。

STEP2】任意の集計方法を選択

「値フィールドの設定」ダイアログが起動します。

あとは、任意の集計方法を選択(③)したら、OK」をクリック(④)して完了です。

今回は「合計」から「個数」へ変更してみました。

【参考】ピボットテーブルで選択可能な集計方法

手順③で選択可能な集計方法は以下の通りです。

  • 合計
  • 個数
  • 平均
  • 最大
  • 最小
  • 数値の個数
  • 標本標準偏差
  • 標準偏差
  • 標本分散
  • 分散

ただ、集計の基本はピボットテーブルの仕様通り、「合計」と「個数」を使うことが圧倒的に多いです。

次点では「平均」「最大」「最小」といったところなので、最初のうちは上から5つを最低限覚えれば十分です。

ワークシート上で集計方法の変更が確認できたらOK

ワークシート上を見ると、B3セルが「合計 / 金額」から「個数 / 金額」へ変更されており、B4~B13セルもデータの個数で集計されていることが分かりますね!

ちなみに、「値」ボックス上の表記もB3セル同様に「個数 / 金額」へ変更されています。

【注意点】元データのレコード数をカウントする場合は「主キー」のフィールドを指定しよう!

主キーとは、そのレコードが一意(重複していない)であることを示すためのコードや番号のことです。(代表例として、社員番号や商品コードなど)

実務で元データのレコード数をカウントする場合、原則元データの主キーのフィールドを指定することをおすすめします。

理由としては、入力必須のフィールドを指定しないと、特定のレコードのカウント漏れがリスクとしてあるからです。(主キーは基本的に入力必須です)

もし、主キーがない元データの場合は、すべてのレコードが埋まっているフィールドは何かを確認したうえで、ピボットテーブルで集計しましょう。

主キーが数値データの場合、本記事のテクニックで「個数」で集計するように変更してください。

サンプルファイルで練習しよう!

可能であれば、以下のサンプルファイルをダウンロードして、実際に操作練習をしてみてください。

サンプルファイル_ピボットテーブル_集計方法.xlsx

※サンプルファイルのダウンロードには無料メルマガに登録いただく必要があります。
(上記リンクから登録フォームへ遷移します)

ファイルを開いたら、次の手順を実施してください。(今までの解説のまとめです)

  1. 「値」ボックス内の集計方法を変更したいフィールド名の「▼」をクリック
  2. 「値フィールドの設定」をクリック
  3. 任意の集計方法を選択
  4. OK」をクリック

本記事の解説と同じ結果になればOKです!

動画の解説もどうぞ!

上記の内容を動画でも解説しています。

文字だけではわかりにくかった方は動画を参考にしてくださいね。

コメント欄にタイムコードを用意しました。
時間の部分をクリックすることで該当の解説へジャンプできますよ!

もし、参考になった方はチャンネル登録ならびに高評価をよろしくお願いいたします!

さいごに

いかがでしたでしょうか?

ピボットテーブルの集計方法の変更は基本中の基本のテクニックです。

データ集計作業でピボットテーブルを活用していく上では必須なので、ぜひ自由自在に集計方法を切り替えできるようになりましょう!

なお、ピボットテーブルの各種テクニックは、私の拙著でも体系的に解説していますので、こちらも参考にしてみてください。

ご参考になれば幸いですm(_ _)m

森田森田

ピボットテーブルはちょっと難しい条件付きの関数(SUMIFCOUNTIFAVERAGEIFなど)と同じ集計がマウス操作だけでできてしまいます。

データ集計を行う機会が多い方は、ピボットテーブルを活用できると時短できるケースが多いので、ぜひ習得してほしい機能のひとつですね。