【今だけ!】使ったことあるIT製品レビューを投稿して報酬最大2万円ゲットできる

Excelピボットテーブルを使ったベーシックな集計やり方

Excelピボットテーブルを使ったベーシックな集計やり方モダンExcel

Excelピボットテーブルを使ったベーシックな集計について記事投稿します。

フィールドリストをメインに使ったGUI操作に慣れれば、高度な分析を簡単に手早く行うことができます。これまでエクセル表を切り貼り、関数を駆使して集計していた人にとっては目から鱗になること間違いなし!?

スポンサーリンク

Excelピボットテーブルを使った集計の基本

集計のアプローチ
  1. まずはデータを俯瞰する
  2. データから何を知りたいのかを押さえておく
  3. フィールドを組み合わせて分析する

まずはデータを俯瞰する

いきなりピボットテーブルで分析する前に、そもそもデータソースにどんなフィールド(列)が存在し、どんなデータが入っているのか俯瞰します。

Excelピボットテーブルを使ったベーシックな集計やり方(データを俯瞰する)
  • 日付タイプのデータがあるか
  • 代表的な切り口になる列はどれか
  • 切り口になる列で親子関係はあるか
  • 数値タイプのデータがあるか

データから何を知りたいのかを押さえておく

ピボットテーブルは、データから知りたいことに対する答えを導くツールです。

何のためにピボットテーブルを使って分析するか、データ上の疑問は何か、あらかじめイメージしておきます。

とはいえ、これは建前的でもあり、データの中にどんな答え・ストーリーが導けそうか、あえて知りたいことを整理せずにアプローチすることもありえます。

Excelピボットテーブルを使ったベーシックな集計やり方(データへの疑問を整理する)

フィールドを組み合わせて分析する

Excelピボットテーブルを使ったベーシックな集計やり方(フィールドを組み合わせて分析する)

数々のフィールドを組み合わせて、重要なメジャー(数値フィールド)を多角的にとらえます。行と列のフィールド組み合わせによる二次元に、フィルターを組み合わせた多次元にデータを分析していきます。

表のタイプ(一次元と二次元の表)

ピボットテーブルで作れる表のタイプは、一次元と二次元です。

一次元の表は、物事をわかりやすくするために事柄を分類・整理したもので一般的な表となります。

次の例は、一次元の表として地域別の売上を示しています。

Excelピボットテーブルを使ったベーシックな集計やり方(一次元の表)

一方で、二次元の表は、二つの観点から事柄を分類・整理した表となります。

次の例は、二次元表として、地域別と製品カテゴリを掛け合わせた売上を示しています。

Excelピボットテーブルを使ったベーシックな集計やり方(二次元の表)

表の体裁としては、一次元と二次元が存在します。

ピボットテーブルはこれに加えて、フィルター(またはスライサー)という機能で表の体裁の外で、別の観点を加えることを加えることができます。

単純集計とクロス集計

ピボットテーブルでは、「単純集計」「クロス集計」という言葉をよく耳にします。

これはアンケート集計でよく使われる言葉です。単純に二次元の表をクロス集計だと捉える方もいるようです。

単純集計は、アンケートの設問ごとに集計を行うやり方でGT(Grand Total)とも呼ばれます。クロス集計は、アンケートの設問と設問を組み合わせて、回答結果をより細分化していく集計方法です。

アレコレメモ
アレコレメモ

最近、よくMicrosoft Formsでアンケートを取って、パワークエリ挟みーの、ピボットテーブルでクロス集計しいます

集計表は一つである必要はない

ピボットテーブルを使われる人で、縦にも横にも異様に長い集計表を見かけることがあります。こういう場合、大抵、集計表を見ても要点がつかみづらいです。

Excelピボットテーブルを使ったベーシックな集計やり方(集計表は一つである必要はない)
あまりよくなピボットテーブルの例

ピボットテーブルは、一つのデータソースからいくらでもピボットテーブルを作ることが出来ます。フィルターや計算処理を加えることで見た目はシンプルなほうがいいでしょう。

値の集計方法

値の集計は、複数行にまたがっている数値フィールドをどうやってまとめあげる(集計)するか、という概念です。以下の集計方法があります。

ピボットテーブルで使える値の集計方法
  • 合計
  • 個数
  • 平均
  • 最大
  • 最小
  • 数値の個数
  • 標本標準偏差
  • 標準偏差
  • 標本分散
  • 分散
Excelピボットテーブルを使ったベーシックな集計やり方(値の集計方法)

ピボットテーブルの既定では、数値フィールドは「合計」、非数値フィールドは「個数」として計算されます。これらは後から変更することが出来ます。

値の計算方法

値の計算は、「値の集計」よりももう一歩複雑な計算をしてくれます。以下の計算方法があり、既定では「計算なし」となります。

ピボットテーブルで使える値の集計方法
  • 計算なし(デフォルト)
  • 総計に対する比率
  • 列集計に対する比率
  • 行集計に対する比率
  • 基準値に対する比率
  • 親行集計に対する比率
  • 親列集計に対する比率
  • 親集計に対する比率
  • 基準値との差分
  • 基準値との差分の比率
  • 累計
  • 比率の累計
  • 昇順での順位
  • 降順での順位
  • 指数(インデックス)
Excelピボットテーブルを使ったベーシックな集計やり方(値の計算方法)
スポンサーリンク

ベーシックな集計の実例

前振りはこのくらいにして、ピボットテーブルを操作します。

ピボットテーブルの挿入については、前回記事にしているので今回は挿入後のデータ操作となります。

操作方法については、次の動画にもまとめています。

やっておきたいオプション設定

ピボットテーブルを挿入したら、ひとまずやっておきたいオプション設定があります。

アレコレメモ
アレコレメモ

エクセルのイマイチな標準設定なので、ピボットテーブルを挿入するたびにこの操作をするという…

アンナ
アンナ

おまじない・おまじない

既定では、ピボットテーブルのフィールドを入れ替えするたびに、シートの列幅が変動します。

Excelピボットテーブルを使ったベーシックな集計やり方(初期設定ではピボットテーブルの列幅が自動で変動する)

これは「更新時に列幅を自動更新する」というオプション設定が有効になっているためです。大抵の場合、無効にしておいた方が作業しやすいです。

Excelピボットテーブルを使ったベーシックな集計やり方(初期設定ではピボットテーブルの列幅が自動で変動する)

「更新時に列幅を自動更新する」を無効にするには、ピボットテーブル上で右クリックからオプション設定を呼び出すか、リボンメニュー「ピボットテーブル分析」の「オプション」から設定をおこないます。

行見出し1つのシンプルな集計

まずはシンプルに、行見出し一つのシンプルな集計です。

Excelピボットテーブルを使ったベーシックな集計やり方(行見出し一つの集計)

▲ 行見出しにしたいフィールドを「行ボックス」へドラッグします。ここでは、「地域」というフィールドを行見出しにしました。

次に、分析したい数値フィールドを「値ボックス」へドラッグします。ここでは、売上を集計対象としました。

行見出しを2つ以上で階層構造を持たせた集計

次に、行見出しを2つ以上の階層構造を持たせた集計です。親子関係を持つフィールドで内訳を示したい場合に使います。

Excelピボットテーブルを使ったベーシックな集計やり方(行見出し二つの集計)

▲ 行見出し1つで作った表に、もう一つフィールドを入れます。「地域」というフィールドを入れていたので、親子関係を持つ「都道府県」というフィールドを入れます。

Excelピボットテーブルを使ったベーシックな集計やり方(行見出しの親子関係を入れ替えると見え方が変わってしまう)

行ボックス内では、順序が意味を持ちます。上にあるフィールドが親、下にあるフィールドが子供という関係になります。親子関係を入れ替えてしまうと見づらくなるので注意が必要です。

これで、地域から都道府県の売上構成を把握することができました。

行見出しと列見出しを組み合わせた集計

今度は、行見出しに加えて列見出しも加えます。

Excelピボットテーブルを使ったベーシックな集計やり方(行見出しと列見出しの集計)

▲ 行見出し1つで作った表に、「カテゴリ」というフィールドを列見出しに入れます。これで「地域」と「カテゴリ」を掛け合わせた売上集計ができました。

ピボットテーブルを操作する

フィルターの追加

ピボットテーブルにフィルターを加える方法は数通りあります。

Excelピボットテーブルを使ったベーシックな集計やり方(フィルターの追加)

▲ 一番手っ取り早いのは、フィルターにしたい列をフィールドリストの「フィルターボックス」へ入れる方法です。

Excelピボットテーブルを使ったベーシックな集計やり方(スライサーの追加)

▲ また、スライサーもよく使います。フィールドリストでフィルターにしたい列を右クリックして「スライサーを追加」をクリックします。これで視認性の高いボタンフィルターが追加できます。

アンナ
アンナ

古いエクセルでは、スライサーは使えないわ。ちゃんとライセンスを更新しなさい。

表示形式の変更

ピボットテーブル上の「売上」フィールドは、桁数が多いので 桁区切り を入れたいと思います。

Excelピボットテーブルを使ったベーシックな集計やり方(値の表示形式の変更)

▲ 右クリック、またはリボンメニューから表示形式を変更できます。

右クリックの場合は、「セルの書式設定」ではなく「表示形式」から設定します。分類を「数値」にして「小数点以下の桁数」は「ゼロ」、「桁区切りを使用する」にチェックを入れます。

アレコレメモ
アレコレメモ

表示形式の設定は、列単位で反映されます。すべてのデータを選択する必要はありません。

リボンメニューの場合は、「ピボットテーブル分析」タブの「フィールドの設定」から同様の設定を行えます。

Excelピボットテーブルを使ったベーシックな集計やり方(値に応じた並べ替え)

これで桁区切りデータの表示になりました。

値に応じた並べ替え

「売上」数値フィールドの降順で並べ替えてみます。

Excelピボットテーブルを使ったベーシックな集計やり方(値に応じた並べ替え)

▲ 行ラベル右にある「▼」ボタンを押し下げて、「そのほかの並べ替えオプション」から「降順」の基準となる「売上」を選択すればOKです。

簡単な装飾

ピボットテーブルには簡単な装飾を行えます。

Excelピボットテーブルを使ったベーシックな集計やり方(データバーによるピボットテーブルの装飾)

▲ 装飾したいデータセルを選択後、「ホーム」タブの「条件付き書式」で「データバー」を選ぶと、データ上に棒グラフが描画されます。

Excelピボットテーブルを使ったベーシックな集計やり方(カラースケールによるピボットテーブルの装飾)

▲ 装飾したいデータセルを選択後、「ホーム」タブの「条件付き書式」で「カラースケール」を選ぶと、データ上にヒートマップが描画されます。

値の集計方法を変更する

「売上」フィールドは、数値フィールドなので既定では「合計」で集計されます。ここでは、平均に変更してみます。

Excelピボットテーブルを使ったベーシックな集計やり方(値の集計方法の変更)

▲ 数値データのあるセル上で右クリックを押して、「値の集計方法」から「平均」を選択します。

Excelピボットテーブルを使ったベーシックな集計やり方(値の集計方法の変更)

▲ 売上の表示が合計から平均へと切り替わりました。

簡単な計算

ピボットテーブルに簡単な計算を加えることができます。

既定では「計算なし」になっています。ここでは、売上フィールドをもう一つ加えて「総計に対する比率」にしてみます。

Excelピボットテーブルを使ったベーシックな集計やり方(値の計算方法の変更)

▲ 数値データのあるセル上で右クリックを押して、「計算の種類」から「総計に対する比率」を選択します。

Excelピボットテーブルを使ったベーシックな集計やり方(値の計算方法の変更)

▲ 売上の表示が合計から総計に対する比率に切り替わりました。列ヘッダー名称とデータバーはおまけで設定しました。

まとめ

Excelピボットテーブルを使ったベーシックな集計について記事にしました。

代表的な操作を一通り触れましたので、この記事だけでもある程度は使えるようになるのではないかと思います。またの機会に応用的な記事を投稿したいと思います。

※記事内動画のナレーションは、「CoeFont (https://coefont.cloud/)」を使っています。

Excelパワーピボット 7つのステップでデータ集計・分析を「自動化」する本
翔泳社
パワークエリやパワーピボットといったExcelの新機能を利用して、データ集計・分析を「自動化」する方法を解説が7つのステップで解説してくれますデータとロジックが分離されるので、今までの次元の異なる、圧倒的な業務改善が可能になります。
Excelパワークエリ データ収集・整形を自由自在にする本
翔泳社
日本で初めてとなるMicrosoft Power Query(パワークエリ)専門の書籍です。例をなぞってお終いではなく、パワークエリを使ったデータ変換の原理・原則を理解し、自由自在に応用できる基礎を身に着けることができます。
Excelピボットテーブル データ集計・分析の「引き出し」が増える本
翔泳社
ピボットテーブルの専門書籍ならコレできまり!ピボットテーブルの基本から応用まで網羅、さまざまな集計・分析の方法を知ることができ、引き出しが増える、集計前のデータ整理や、ピボットテーブルの構造も理解できる。
ピボットテーブルも関数もぜんぶ使う!Excelでできるデータの集計・分析を極めるための本
ソシム
Excelで実際に高度なデータ分析を行なっている現役社員が解説するエクセルデータ分析の専門書籍。集計作業に費やす時間を大幅に短縮するためのテクニックを、そして「質」の高い分析結果を導き出すためのノウハウが徹底的に追求されている。
翔泳社ならPDFで書籍が購入できる!

紹介する書籍の中で翔泳社出版のものは、紙媒体やKindleのほかに公式サイト「SEshop」でPDF書籍が購入できます。オフィスで画面に移しながら手を動かせるので使い勝手抜群!!

\ お得なポイントバック! /

全国送料無料!IT書、ビジネス書、資格書が豊富なSEshop

「SEShop」だとクーポン利用可、PDF版での入手となり扱いやすいです。現在、10%オフとなるクーポンが配布されていました。アマゾンとかで買うよりもお得です。

\ 10%OFFクーポン配布中!! /
タイトルとURLをコピーしました