実演！Excelを使った身近なデータ分析～4.実践③顧客構造分析

こんにちは、阿久津恵子です。
「ユーザ視点、データ起点」としてExcelを使った身近なデータ分析を、ここで紹介いたします。

具体的には、企業にある身近なデータの１つ顧客管理データを使って、ＣＲＭ(顧客関係管理)のためのデータ分析をします。

2017年JSDG東京研修会でご紹介した内容を５回にわたって説明いたします。この連載の概要はご紹介ページをご覧くださいませ。

４．実践③顧客構造分析

今回は、顧客構造分析としでデシル分析を行います。
デシル分析では、顧客の購入金額によって全顧客を10等分し、10のグループに分類することにより上位の何％の顧客（件数）で全体の何％の売上（金額）を占めるかを把握する事が可能となります。

一般的には上位20％の顧客で全体の80％の売上を占めていると言われています。

今回のデータを使って分析すると下図のようになります。

このグラフを作成する手順を以下にご説明いたします。
具体的には、以下の順番でピボット集計表を加工してデシルグラフの為の数値リストを作ります。
①ピボットテーブルの作成
②データの並べ替え
③デシルランク付け
④デシルランク毎の購買金額、累積金額割合集計

①ピボットテーブルの作成
まず「２．実践①データ基礎俯瞰１」と同じ様に「Total Price(請求金額)」を作成した後、全データを選択しピボットテーブルを作成します。

①ピボットテーブルの作成　設定画面

新しいシートが作成されますので、以下のようにピボットテーブルの設定を行います。
・フィールド欄から「行」に「CustomerID」をドラッグ＆ドロップ、「値」に「TotalPrice」をドラッグ＆ドロップします。

これで顧客毎の購買金額が集計できます。

②データの並べ替え
ピボットテーブルのままだと扱いづらいので、A3～B4375までを選択しコピーし、ピボットテーブルの横D3セルを選択し貼り付けます。

②データの並べ替え　並べ替え前

分かりやすいようにD3は「CustomerID」E3は「購買合計金額」と項目名を記載します。
続いて貼り付けたデータ部分D3～E4375を選択し、メニューの「データ」から「並べ替え」をクリックし、「並べ替え」画面を出します。

更に下図のように最優先されるキーに「購買合計金額」を設定し、順序は「大きい順」として、張り付けたデータを購買合計金額が大きい順に並べ替えます。

②データの並べ替え　並べ替えの設定画面

並べ替えた結果をみると4330番目以降の顧客の購買合計金額がマイナスであるため、デシル分析の対象顧客を4329番目までとし、１グループ433人として10グループに分ける事にします。

③デシルランク付け
分かりやすいようにF3に「No.」G3に「デシルランク」と項目名を記載します。
続いてF4に「0」を入力しF5は「=F4+1」を入力し、一番下の行までコピーします。
更にG2に「433」を入力し、G4に式「=INT(F4/$G$2)+1」を入力し、一番下の行までコピーします。
これで対象となる全顧客に購買合計金額の順位とデシルランクのラベル付けを行う事ができます。

③デシルランク付け　デシルランクを設定した画面

④デシルランク毎の購買金額、累積金額割合集計
更に、デシルランク毎の購買金額および累積金額割合を集計していきます。

分かりやすいようにI3に「デシルランク」J3に「購買金額」K3に「累積金額割合」と項目名を記載します。
続いてI4～I13に「1」～「10」とデシルランクを記載し、J4に式「=SUMIF(G:G,I4,E:E)」を記載し、J5～J13までコピーします。
これで各デシルランク毎の購買金額を集計する事ができます。

更にJ14に式「=SUM(J4:J13)」を記載し、全体の購買金額を集計し、K4に式「=J4/$J$14」を記載し、K5～K13までコピーします。
これで各デシルランク毎の累積金額割合を集計する事ができます。

※Excel関数の説明
SUMIF：指定した検索条件に一致するセルの値を合計する関数