「ピボットテーブルの設定がうまくできなくて、思ったような集計結果が出ない…」
「もっと効率的にデータを分析したいけど、ピボットテーブルの機能を使いこなせていない気がする…」
エクセルのピボットテーブルをうまく操るための鍵は、集計機能を思い通りに使いこなせるかにかかっています。
よく使われる合計・個数・平均など集計は、設定メニューの中でも見つけやすい場所に配置されています。
しかし、ちょっと凝った集計方法の場合は、希望の操作方法を探し出すのが大変です。
本記事では、ピボットテーブルの集計機能をできるだけ詳しく紹介します。
「今までピボットテーブルを使いこなせている」と思っている人も、この記事を通して知らなかった機能に気付く機会になるでしょう。
ピボットテーブルで可能な３つの集計方法
ピボットテーブルで可能な集計方法は以下の3つです。
- 基本的な集計機能を使う
- 比率などを使ったやや複雑な集計
- 複数のフィールドを組み合わせた高度な集計
合計や個数などの基本的な集計は、ピボットテーブルを使いこなせるExcelユーザーがよく使う機能です。
本記事では、さらに２つの高度な集計方法も解説します。
これらの技術を習得することで、以下のメリットが得られます。
- ピボットテーブルの活用範囲が広がる
- データ分析スキルのアップ
パワーピボットのメジャーを使えば相当高度な集計も可能ですが、敷居が高くなかなか使いこなせる人がいないのが現状です。
ピボットテーブルの高度な集計方法は、パワーピボットほど難易度は高くないので、本記事の最後まで目を通すだけで理解が深まり、データ分析の幅が広がるでしょう。
その1.基本的な集計機能を使う
基本的な集計とは、主に合計や個数など、初歩的な統計で使われる計算のことです。
これらの集計方法は非常に汎用性が高く、様々な場面で活用できます。
そのため、このあたりの知識は、データ分析担当者だけでなく、Excelを使用する全ての人が知っておきたい基本的なテクニックといえるでしょう。
では、さっそくピボットテーブルの基本的な集計機能を使ってみます。
前準備として、ピボットテーブルの行見出しには品名を設定します。
値ボックスに金額を設定すると、金額の合計値が表示されます。
集計されたデータが入力されたセルをそのまま右クリックし、メニューから他の集計方法を確認してみましょう。
「値の集計方法」にマウスのカーソルを当てます。
データの個数や平均など、別の集計結果を選べます。
ここで「データの個数」に設定したところ、「合計」とは違う計算値が表示されました。
さらに、その他のオプションをクリックしてみましょう。
「値フィールドの設定」メニューが開き、標準偏差や分散などの専門的な集計方法も選択できるようです。
その2.比率などを使ったやや複雑な集計
データの比率を計算するには、まず「値フィールドの設定」メニューから「計算の種類」タブを選びます。
デフォルト設定は「計算なし」ですが、総計に対する比率や行集計に対する比率など、いろいろなパターンの比率を計算することができます。
メニューをさらに下にスクロールすると、順位の計算もできるようです。
ここで「総計に対する比率」に設定したところ、パーセント表記されました。
比率計算を行うときに注意しておきたいことは、選択した集計方法によって計算結果が変わることです。
例えば、個数が基準の場合と合計が基準の場合では、下の図のように計算結果が異なります。
思わぬ集計ミスを引き起こさないよう、比率計算の仕組みをしっかり理解しておきましょう。
ピボットテーブルだけでなく、パワーピボットも習得したい人は、積極的に比率計算を試してみることをおすすめします。
パワーピボットの最大の挫折ポイントはDAX式の習得ですが、比率計算を思い通りに使いこなせれば、DAX式習得のハードルも大きく下がるでしょう。
その3.複数のフィールドを組み合わせた高度な集計
ピボットテーブルは、２つのフィールドを組み合わせて計算することも可能です。
この方法はとても簡単なので、計算手順をしっかりと覚えておくことをおすすめします。
まずは、「ピボットテーブル分析」タブから「フィールド/アイテム/セット」を選び、続いて「集計フィールド」をクリックします。
例として、フィールドの名前を「平均単価」に設定し、金額を個数で割ってみました。
「平均単価」を値ボックスにセットすると、平均単価が計算され、ピボットテーブルに表示されます。
このように、複数のフィールドを組み合わせて集計することで、データ分析の幅が広がるので、積極的に使っていきたいところです。
注意点：集計ミスを防ぐためにも検算は必要
ピボットテーブルを使いこなせれば、高度な集計も可能です。
しかし、複雑な集計式を入れたピボットテーブルをノーチェックで運用するのは、後で大きなトラブルを起こしかねません。
特に、比率計算したり、複数のフィールドを組み合わせた時は、想定した順番に計算が行われない可能性もあります。
思わぬトラブルを防ぐためにも検算を行い、想定通りの答えになっているか確認することが重要です。
いったん問題ないことを確認できたら、安心してピボットテーブルを運用できます。
後で大きなやり直しや集計ミスの報告をしないためにも、ひと手間をかけた検算を習慣化し、集計データの精度を担保していきましょう。
まとめ
今回は、ピボットテーブルで集計する方法を紹介しました。
普段使いでは基本的な集計方法しか使われませんが、高度な集計機能を知っておくと、基本機能しか使わない人と比べると、さらに大きな差をつけることができます。
ピボットテーブルのスキルアップは、データ分析の質を劇的に向上させます。
そして、職場の同僚からのあなたを見る目も変わるはずです。
基本から応用まで、学ぶことは多いですが、少しずつ使いこなしていけば、確実にスキルを伸ばすことができるでしょう。
早速試してくださいね！