注文あたりの平均購買額と一人当たりの平均購買額をピボットテーブルの中でいっぺんに計算したい

今回は、ピポットテーブルで注文あたりの平均購買額と一人当たりの平均購買額をいっぺんに計算していきます。

今回使用するデータは売上データです。

このデータは一行が一注文となっていて、列には注文日や顧客ID、マーケットや売上といった列があります。

まずは売上平均を求めていきます。

チャートからタイプにピボットテーブルを選択します。

行にはMarketを選択し、値にSalesを選び、集計関数に平均値(mean) を選択する。

マーケットごとに売上平均を求めることができました。

果たしてこの売上平均は何に対しての売上平均なのでしょうか？

答えは注文あたりの売上平均となります。

売上平均は下記の式で求められます。

売上平均 = 売上合計 / 行の数

しかし、今回は一行が一注文のデータなので、行の数は注文数となります。

注文あたりの売上平均 = 売上合計 / 行の数 (注文数)

つまり、一注文あたりの売上平均となります。

次に顧客一人当たりの売上平均を求めたいとします。

先程の注文あたりの売上平均を求める際、裏では下記の式が使われていました。

注文あたりの売上平均 = sum(sales) / n()

このn()というのは、行の数を求める関数です。

一人当たりの売上平均を求めるには、下記のように売上合計から顧客数を割ることで求められます。

一人当たりの売上平均 = 売上合計 / 顧客数

一人当たりの売上平均を求める式は下記になります。

一人当たりの売上平均 = sum(sales) / n_distinct(CustomerID)

n_distinct(<列名>)を使うことで、一意な値の数、つまり顧客数を求めることができます。

ピボットテーブルの2つ目の値から (カスタム) を選択します。

カスタムの計算を作成のダイアログが表示されます。

計算式に下記の式を入力します。

sum(Sales) / n_distinct(`Customer ID`)

これにより、顧客一人当たりの売上平均も求めることができました。

今回紹介した使い方以外にも、カスタム関数は様々な使い方ができます。

過去にいくつかの使い方の例を紹介しているので、興味がある方は下記のリンクから見てみて下さい。

• カスタム関数の使い方