Excelピボットテーブルを使ったベーシックな集計について記事投稿します。
フィールドリストをメインに使ったGUI操作に慣れれば、高度な分析を簡単に手早く行うことができます。これまでエクセル表を切り貼り、関数を駆使して集計していた人にとっては目から鱗になること間違いなし！？
Excelピボットテーブルを使った集計の基本
- まずはデータを俯瞰する
- データから何を知りたいのかを押さえておく
- フィールドを組み合わせて分析する
まずはデータを俯瞰する
いきなりピボットテーブルで分析する前に、そもそもデータソースにどんなフィールド（列）が存在し、どんなデータが入っているのか俯瞰します。
- 日付タイプのデータがあるか
- 代表的な切り口になる列はどれか
- 切り口になる列で親子関係はあるか
- 数値タイプのデータがあるか
データから何を知りたいのかを押さえておく
ピボットテーブルは、データから知りたいことに対する答えを導くツールです。
何のためにピボットテーブルを使って分析するか、データ上の疑問は何か、あらかじめイメージしておきます。
とはいえ、これは建前的でもあり、データの中にどんな答え・ストーリーが導けそうか、あえて知りたいことを整理せずにアプローチすることもありえます。
フィールドを組み合わせて分析する
数々のフィールドを組み合わせて、重要なメジャー（数値フィールド）を多角的にとらえます。行と列のフィールド組み合わせによる二次元に、フィルターを組み合わせた多次元にデータを分析していきます。
表のタイプ（一次元と二次元の表）
ピボットテーブルで作れる表のタイプは、一次元と二次元です。
一次元の表は、物事をわかりやすくするために事柄を分類・整理したもので一般的な表となります。
次の例は、一次元の表として地域別の売上を示しています。
一方で、二次元の表は、二つの観点から事柄を分類・整理した表となります。
次の例は、二次元表として、地域別と製品カテゴリを掛け合わせた売上を示しています。
表の体裁としては、一次元と二次元が存在します。
ピボットテーブルはこれに加えて、フィルター（またはスライサー）という機能で表の体裁の外で、別の観点を加えることを加えることができます。
単純集計とクロス集計
ピボットテーブルでは、「単純集計」「クロス集計」という言葉をよく耳にします。
これはアンケート集計でよく使われる言葉です。単純に二次元の表をクロス集計だと捉える方もいるようです。
単純集計は、アンケートの設問ごとに集計を行うやり方でGT(Grand Total）とも呼ばれます。クロス集計は、アンケートの設問と設問を組み合わせて、回答結果をより細分化していく集計方法です。
最近、よくMicrosoft Formsでアンケートを取って、パワークエリ挟みーの、ピボットテーブルでクロス集計しいます
集計表は一つである必要はない
ピボットテーブルを使われる人で、縦にも横にも異様に長い集計表を見かけることがあります。こういう場合、大抵、集計表を見ても要点がつかみづらいです。
ピボットテーブルは、一つのデータソースからいくらでもピボットテーブルを作ることが出来ます。フィルターや計算処理を加えることで見た目はシンプルなほうがいいでしょう。
値の集計方法
値の集計は、複数行にまたがっている数値フィールドをどうやってまとめあげる（集計）するか、という概念です。以下の集計方法があります。
- 合計
- 個数
- 平均
- 最大
- 最小
- 積
- 数値の個数
- 標本標準偏差
- 標準偏差
- 標本分散
- 分散
ピボットテーブルの既定では、数値フィールドは「合計」、非数値フィールドは「個数」として計算されます。これらは後から変更することが出来ます。
値の計算方法
値の計算は、「値の集計」よりももう一歩複雑な計算をしてくれます。以下の計算方法があり、既定では「計算なし」となります。
- 計算なし（デフォルト）
- 総計に対する比率
- 列集計に対する比率
- 行集計に対する比率
- 基準値に対する比率
- 親行集計に対する比率
- 親列集計に対する比率
- 親集計に対する比率
- 基準値との差分
- 基準値との差分の比率
- 累計
- 比率の累計
- 昇順での順位
- 降順での順位
- 指数（インデックス）
ベーシックな集計の実例
前振りはこのくらいにして、ピボットテーブルを操作します。
ピボットテーブルの挿入については、前回記事にしているので今回は挿入後のデータ操作となります。
操作方法については、次の動画にもまとめています。
やっておきたいオプション設定
ピボットテーブルを挿入したら、ひとまずやっておきたいオプション設定があります。
エクセルのイマイチな標準設定なので、ピボットテーブルを挿入するたびにこの操作をするという…
おまじない・おまじない
既定では、ピボットテーブルのフィールドを入れ替えするたびに、シートの列幅が変動します。
これは「更新時に列幅を自動更新する」というオプション設定が有効になっているためです。大抵の場合、無効にしておいた方が作業しやすいです。
▲ 「更新時に列幅を自動更新する」を無効にするには、ピボットテーブル上で右クリックからオプション設定を呼び出すか、リボンメニュー「ピボットテーブル分析」の「オプション」から設定をおこないます。
行見出し1つのシンプルな集計
まずはシンプルに、行見出し一つのシンプルな集計です。
▲ 行見出しにしたいフィールドを「行ボックス」へドラッグします。ここでは、「地域」というフィールドを行見出しにしました。
次に、分析したい数値フィールドを「値ボックス」へドラッグします。ここでは、売上を集計対象としました。
行見出しを2つ以上で階層構造を持たせた集計
次に、行見出しを2つ以上の階層構造を持たせた集計です。親子関係を持つフィールドで内訳を示したい場合に使います。
▲ 行見出し1つで作った表に、もう一つフィールドを入れます。「地域」というフィールドを入れていたので、親子関係を持つ「都道府県」というフィールドを入れます。
行ボックス内では、順序が意味を持ちます。上にあるフィールドが親、下にあるフィールドが子供という関係になります。親子関係を入れ替えてしまうと見づらくなるので注意が必要です。
これで、地域から都道府県の売上構成を把握することができました。
行見出しと列見出しを組み合わせた集計
今度は、行見出しに加えて列見出しも加えます。
▲ 行見出し1つで作った表に、「カテゴリ」というフィールドを列見出しに入れます。これで「地域」と「カテゴリ」を掛け合わせた売上集計ができました。
ピボットテーブルを操作する
フィルターの追加
ピボットテーブルにフィルターを加える方法は数通りあります。
▲ 一番手っ取り早いのは、フィルターにしたい列をフィールドリストの「フィルターボックス」へ入れる方法です。
▲ また、スライサーもよく使います。フィールドリストでフィルターにしたい列を右クリックして「スライサーを追加」をクリックします。これで視認性の高いボタンフィルターが追加できます。
古いエクセルでは、スライサーは使えないわ。ちゃんとライセンスを更新しなさい。
表示形式の変更
ピボットテーブル上の「売上」フィールドは、桁数が多いので 桁区切り を入れたいと思います。
▲ 右クリック、またはリボンメニューから表示形式を変更できます。
右クリックの場合は、「セルの書式設定」ではなく「表示形式」から設定します。分類を「数値」にして「小数点以下の桁数」は「ゼロ」、「桁区切りを使用する」にチェックを入れます。
表示形式の設定は、列単位で反映されます。すべてのデータを選択する必要はありません。
リボンメニューの場合は、「ピボットテーブル分析」タブの「フィールドの設定」から同様の設定を行えます。
これで桁区切りデータの表示になりました。
値に応じた並べ替え
「売上」数値フィールドの降順で並べ替えてみます。
▲ 行ラベル右にある「▼」ボタンを押し下げて、「そのほかの並べ替えオプション」から「降順」の基準となる「売上」を選択すればOKです。
簡単な装飾
ピボットテーブルには簡単な装飾を行えます。
▲ 装飾したいデータセルを選択後、「ホーム」タブの「条件付き書式」で「データバー」を選ぶと、データ上に棒グラフが描画されます。
▲ 装飾したいデータセルを選択後、「ホーム」タブの「条件付き書式」で「カラースケール」を選ぶと、データ上にヒートマップが描画されます。
値の集計方法を変更する
「売上」フィールドは、数値フィールドなので既定では「合計」で集計されます。ここでは、平均に変更してみます。
▲ 数値データのあるセル上で右クリックを押して、「値の集計方法」から「平均」を選択します。
▲ 売上の表示が合計から平均へと切り替わりました。
簡単な計算
ピボットテーブルに簡単な計算を加えることができます。
既定では「計算なし」になっています。ここでは、売上フィールドをもう一つ加えて「総計に対する比率」にしてみます。
▲ 数値データのあるセル上で右クリックを押して、「計算の種類」から「総計に対する比率」を選択します。
▲ 売上の表示が合計から総計に対する比率に切り替わりました。列ヘッダー名称とデータバーはおまけで設定しました。
まとめ
Excelピボットテーブルを使ったベーシックな集計について記事にしました。
代表的な操作を一通り触れましたので、この記事だけでもある程度は使えるようになるのではないかと思います。またの機会に応用的な記事を投稿したいと思います。
※記事内動画のナレーションは、「CoeFont (https://coefont.cloud/)」を使っています。
紹介する書籍の中で翔泳社出版のものは、紙媒体やKindleのほかに公式サイト「SEshop」でPDF書籍が購入できます。オフィスで画面に移しながら手を動かせるので使い勝手抜群！！
全国送料無料！IT書、ビジネス書、資格書が豊富なSEshop
「SEShop」だとクーポン利用可、PDF版での入手となり扱いやすいです。現在、10%オフとなるクーポンが配布されていました。アマゾンとかで買うよりもお得です。
