奮闘記(2)加重平均

集計フィールドと加重平均について

今回は、ピボットテーブルで業務用としてお薦めする機能を紹介します。
ピボットテーブル機能で、行・列・値の各フィールドの配置をおこなえば、 簡単に集計表が完成します。売上や数量の小計、行列の合計も計算できます。 もちろん、売価単価の合計も計算可能ですが、検討外れの数値になります。 合計/平均=合計:売価単価÷個数で、単純平均になります。

ピボットテーブルの平均は、単純平均です ・・値フィールド

一般的な加重平均と、ピボットテーブルの単純平均とは違います。
加重平均と単純平均
ピボットテーブルの値フィールドでは、合計、平均(単純平均)です。
値フィールド

加重平均は、「集計フィールド」を使います


業務用には、売上単価=売上金額÷数量という「加重平均」が必要です。
このような計算を行う便利な機能がピボットテーブルの「集計フィールド」です。
私は、25年前にこの機能の存在を知り、本当に驚愕しました。

集計フィールドは、ピボットテーブルの「フィールド」タブを使います
集計フィールド


当時は、ホストコンピューターで様々な帳表をプログラミングで作成していました。それが、Excelにデータをダウンロードすれば、ユーザーが自由に帳表を作ることが可能になるからです。どこまで使えるのか?追究してみました。何と複雑な製紙業の管理データの計算でも、関数を使って正しく計算できる、素晴らしい能力を持っています。

Excelのヘルプで、「集計フィールド」の使い方が検索できます。

ピボットテーブルの集計フィールドのヘルプ説明です。
集計フィールド(ヘルプ)

こうした素晴らしい機能でも、多くの解説書では、「消費税額の計算」、ヘルプでは「手数料の計算」を例として用いています。

集計フィールドの設定は、数式を入力します。
集計フィールドの設定

ヘルプの説明では、実務に使えません! 


集計フィールドの 数式は、消費税額=round(売上金額*0.08,0)の入力です。 ピボットテーブル機能で集計した数値を使って、新たに計算をします。個々のセルで計算する場合も同様の数式を入力しますが、明細行で個々に積み上げた消費税額の合計数値と、売上金額の合計値で計算する集計フィールドの計算値とは、端数の不一致が生じます。当然手数料も同じです。

集計フィールドで、消費税や手数料を計算はできますが、実務では端数調整で差異が生じるため、使えません。
集計フィールドの計算は誤り

「集計フィールド」は「加重平均」が使える機能と説明して欲しい!

実務に長けた方なら、個々の積み上げが正しくこのような使い方は不適切だということになります。
しかし、このような不適切な説明が、ピボットテーブルの能力が過小評価される原因と思われてなりません。
説明を正確にするなら「加重平均」を採用すべきです。それなら、皆さん積極的に使ってくれるでしょう。
そもそも、名前が「集計フィールド」なので、誤解されやすいのです。
「集計フィールド」は、集計値を使って新たな計算を行うものであり、
「値フィールド」の合計値は、計算された個々の数値を集計するもので、全く異なるものです。
誰にも分かりやすくピボットテーブルのメリットを説明すべきです。

リレーションシップと集計フィールドは、同時に使えません!

Excel2013では、集計フィールドが使えない、という現象が生じることをご存知ですか。
Excel2013では、「リレーションシップ」という便利な機能が追加されました。しかし、この機能を使うと、集計フィールドが使えないのです。
解説書では、2つの機能が説明されています。しかし「同時には使えない。」という大事な情報が、説明されていません。

集計フィールドは、「リレーションシップ」との同時はできません。ヘルプ・・OLAP接続では作成できない ・・理解しにくい!
ヘルプ・・OLAP接続では作成できない ・・理解しにくい


ほとんどの解説書は、マイクロソフトの技術情報から作成されています。実務の事例紹介が少なく、こうした問題点がフィードバックされていないために起こっていると思います。
私は、表計算アドバイザーとして、ユーザーと実務の視点から、皆さんのお役に立てる情報をお伝えしたいと考えています。

Excel2016以降では、「PowerPivot」、「PowerQuery」という
新機能が使えるようになりました。先ほど紹介した「集計フィールドが使えない」という 不便さは解消されているように見えますが、使用法などの確認もまだまだ十分ではなく、この機能を使うユーザーの負担も大きくなると思っています。詳細については、改めてご紹介します。