あれっ…このピボットテーブルの総計、なんか計算おかしくないですか？
あー、それね。集計フィールドあるあるだよ。実は、計算の順番の違いが影響してるんだよね。これ、気づかずに使ってたらハマるから気を付けてね。
Excelのピボットテーブルで「集計フィールド」を使ったら、総計の値が「あれ？なんか変…」と思ったこと、ありませんか？
実はこれ、計算の順番が原因なんです。
気づかずに使っていたら、結果が期待と違っていた！…なんてことも。
この記事では、そんなピボットテーブルのちょっとした落とし穴をわかりやすく解説します！
正しい集計をするためのコツも紹介するので、同じようにモヤモヤしたことがある人はぜひチェックしてみてくださいね。
Excelに計算させた結果が変！？
月ごとに「取引回数」と「売上計」があるデータを使って、取引先ごとの平均取引額（売上計 ÷ 取引回数）を計算します。
まず、ピボットテーブルの集計フィールドで「売上計 ÷ 取引回数」を作成して、月ごとの平均取引額の項目を作成。
列名は集計フィールドで作ったので「集計平均取引額」とします。
ピボットテーブルで取引先ごとに合計します。
１社あたり１３００円台です。
すでにちょっと怪しいですが、今度は元のテーブルに「売上計 ÷ 取引回数」の列をあらかじめ作ります。
列の名前は、テーブルに作った列ということで「テーブル平均取引額」にしてあります。
それをピボットテーブルで集計して、合計を出してみます。
えっ、全然ちがう！
今度は、平均の場合もチェックしてみましょう！
結果がこちら。
なんとなく近いけど、やっぱり違う！
原因はコレ！ピボットテーブルの計算は“順番”がカギ
実は、集計フィールドって先に各列の合計を出してから、その値を使って計算してるんです。
たとえば「売上計 ÷ 取引回数」なら、「売上計の合計 ÷ 取引回数の合計」って感じになります。
なので、テーブルで計算してから集計したように１行ずつ「売上計 ÷ 取引回数」を計算してから合計するって方法とは、ちょっと仕組みが違うんです。
ちょっと手計算してみましょう。
ピボットテーブルの集計結果は次のとおりでしたので、手計算の結果とピッタリ合います！
- 集計フィールドの平均取引額の合計
1325.210084 - テーブルで計算した値の合計
15886.38574
この計算の仕組みは、実はMicrosoftの公式サポートサイトでもちゃんと紹介されています。
どうすればいい？ズレを防ぐ回避策
ここまで読んで、「じゃあ結局どうしたら正しい値が出るの？」と思った方も多いはず。
まず大事なのは、自分が出したい答えが「合計から計算した値」なのか、それとも「1行ずつ計算してから集計した値」なのかをハッキリさせることです。
列の合計から計算した値が欲しい
もし「列の合計から計算した値（たとえば売上の合計 ÷ 取引回数の合計）」を出したい場合は、集計フィールドを使うのがピッタリです。
ピボットテーブルの集計フィールドは、まさにその「列ごとに合計してから計算する」という仕組みなので、合計から計算したいなら、むしろ集計フィールドは正しい選択です！
１行ずつ計算してから集計したい
１行ずつ計算した結果を集計したいときは、次の方法でやるのがオススメです！
１．元データに計算列を追加する
今回の例でもやりましたが、最もシンプルで確実な方法がこれ。
元データのテーブルに計算列を作っておくんです。
あとはその列をピボットテーブルに入れて、普通に集計を出すだけでOK。
２．Power Queryで前処理する
「元データを直接いじれない…」「定期的にデータを更新したい」といった場合は、Power Queryを使って事前に計算列を作るのがおすすめです。
Power Query内で「売上計 ÷ 取引回数」のように計算しておけば、それをそのままピボットテーブルで使えます。
一度作っておけば、データ更新もワンクリックなのもうれしいところ！
まとめ
ピボットテーブルの計算フィールドはとても便利ですが、計算の順番がちょっとクセモノ。
「１行ずつ計算してから集計したい」と思っていても、実は列を合計してから計算する仕組みになっているので、期待とズレた結果になることもあります。
そんなズレを防ぐためには、
- 元データに計算列を作る
- Power Queryであらかじめ計算しておく
- 計算フィールドの特性を理解して使い分ける
この３つがポイントとなります！
意識して使えば集計ミスはしっかり防げます。
「なんかおかしいな？」と思ったら、この記事の内容を思い出してみてくださいね！
なるほど～！集計フィールドって便利だけど、仕組みをちゃんと知ってないとズレちゃうんですね…。これからは「どこで計算するか」も気にしながら、使っていきますっ！
