Introduction
ピボットテーブルの使い方③です。
Ver. all 2013 2016 2019 365
基本的な使い方は、下のリンク先で説明しています。
今回はピボットテーブルでの、集計方法と計算の種類の変更方法を説明します。集計の方法は、合計だけではなく、平均など様々な集計方法に簡単に切り替えることができます。
今回使用する表も、前回と同じく下の表です。表の内容をある程度把握しておいてください。
集計方法をいろいろ変更できるにゃ
集計方法を変更する方法
下の画像は「受注営業所」ごとに「金額」を集計したピボットテーブルです。
元の表で確認すると、下の場所を集計したことになります。
ピボットテーブルは、作成した直後の集計方法は合計を表示するようになっています。
これを変更するには、変更するフィールドの好きな場所をクリックして、
［オプション］タブ → ［計算方法］の中の１番左、
［集計方法］をクリックします。
すると、変更できる種類の一覧が表示されます。
最初は［合計］になっていますね。それぞれを関数であらわすと、
| 合計 | SUM |
| データの個数 | COUNTA |
| 平均 | AVERAGE |
| 最大値 | MAX |
| 最小値 | MIN |
| 積 | PRODUCT |
という感じです。［データの個数］は数値以外も数えるので「COUNT」ではなく「COUNTA」ですね。
合計で計算されていた先ほどの集計を「データの個数」に変更すると、
このように集計され、「平均」に変更すると、
このようになります。「平均」にした場合は、数値の場所を選択して、［ホーム］タブから、小数点の桁数表示を調整したいですね。
計算の種類を変更する方法
次は、計算の種類を変更してみます。
こちらを扱う上で大切なことは、先ほど説明した［計算方法］の結果に対して計算が行われるということです。
先ほどと同じ、［オプション］タブ → ［計算方法］の中の［集計方法］の右隣、
［計算の種類］をクリックします。
こちらは種類がたくさんあります。
- 計算なし
- 総計に対する比率
- 列集計に対する比率
- 行集計に対する比率
- 基準値に対する比率
- 親行集計に対する比率
- 親列集計に対する比率
- 親集計に対する比率
- 基準値との差分
- 基準値との差分の比率
- 累計
- 比率の累計
- 昇順での順位
- 降順での順位
- 指数（インデックス）
最初は［計算なし］が選ばれています。
使用する可能性の高そうなものを幾つか紹介します。
元となるピボットテーブルは、「受注営業所」を「合計」で集計したものです。
総計に対する比率
［総計］に対する比率を選ぶと、全体の合計を100％とした場合の各比率が表示されます。円グラフを想像すると理解しやすいと思います。
基準値との差分
［基準値との差分］を選ぶと、下のように［基準フィールド］と［基準アイテム］を選ぶが画面が表示されます。
複数の計算するフィールドを使用しているピボットテーブルの場合は［基準フィールド］を選ぶことができます。今回の場合は、「金額」フィールドしか使用していないため「受注営業所」しか選べません。
［基準アイテム］で、差分の元となる項目を選びます。今回は「新宿営業所」を選んでみました。
［OK］をクリックすると、「新宿営業所」の「235600」の数値を基準とした差分が表示されました。
累計
［累計］を選んだ場合は、［基準フィールド］を選べる場面が表示されます。
そのまま［OK］をクリックすると、累計が表示されます。
累計を表示した場合は、「総計」の数値は意味がないので表示されていませんね。
降順での順位
［降順での順位］を選んだ場合も［基準フィールド］を選べる画面が表示されます。
［OK］をクリックすると結果が表示されます。
集計方法と計算の種類の違い
ここで、［集計方法］と［計算の種類］の違いをまとめておきます。
それぞれ隣同士、名前も似ているので区別が付きにくいかもしれません。
まず、できることが違いますね。
［集計方法］では合計や平均ですし、［計算の種類］では比率を出したり順位を出したり。
この２つを使用する時に１番大切なことは、前にも書きましたが［計算の種類］は、［集計方法］の結果に対して計算が行われるということです。
試してみましょう。
先ほどの表で［計算の種類］の順位を降順で出すと下のようになりました。「東京本店」が１位になっていますね。
この元の表の［集計方法］を［最小値］に変更します。
結果は、下の画像。
この状態で、［計算の種類］で［降順での順位］を選択すると、
下のように「渋谷営業所」が１位になりました。
このように、右側の［計算の種類］で選んだ結果は、左側の［集計方法］の結果に対して計算されることが分かります。
まとめ
ピボットテーブルのメイン機能は集計ですが、その集計結果の計算方法を自由に変更できます。
データ分析にも活用できますし、使い方次第で仕事にうまく活かせるはず。
前 → ピボットテーブルの使い方 ②｜並び替え、フィルター（抽出機能）やスライサーの使い方
次 → ピボットテーブルの使い方 ④｜日付でグループ化する方法とフィールドの使い方
集計方法と計算方法の違いを覚えておくにゃ