ひとりマーケティングのためのデータ分析

手順解説 | Excel(エクセル)でおこなうビジネスデータの分析

How-to

デシル分析表

デシル分析は、特定の変数(金額や数量・回数など)の降順に処理したデータをもとに、分析の対象をほぼ均等な 10 個のセグメントに分割し、それらセグメント間に見られる値の差からグループ別管理の効率を推量していく手法です。

80 : 20 の法則 を下敷きとした重点管理に利用できることから、パレート分析(ABC分析)と重なる部分の多い手法とも言えます。

以下、エクセルによるデシル分析表の作り方です。ここでは一連の手続きを Excel 2010 で追っています。これは Excel 2007, Excel 2013 および Excel 2016 でも変わりません。

元データ

綾子

1

元のデータ(一部)です。ある販売担当部員の管理する (チケット制の)サービス利用会員 76 社に関して、ここ 2 年間にチケットを消化した枚数を顧客別に集計し降順に並べ替えたものです。

※ すべてのデータはサイドバーのボタンからご利用いただけます。

デシル分析・元データ

10のセグメントに区分けする

綾子

2

76 件のレコードですから…

レコードを 76 ÷ 10 で 8 件ずつ区切り、ほぼ均等に 10 等分していくものとします。

ちょうど次の図のようなイメージです。

ただし最後(10番)のセグメントだけは、除算の余りが生じる関係でレコード数が他と異なってしまいます。

綾子

3

本来、各セグメントに含まれる要素の数が均等なことにこしたことはないのでしょうが、今回のように 10 で割って余りが生じる場合は仕方ありません。

その場合、下の図のように最後のセグメントで調整するか、下位の数個のセグメントに含まれる要素の数を上位のセグメントのそれとは若干減らして調整します。


このケースでは前者の方法をとりますが、後者の考え方を例えるなら…要素の数が 8 個のセグメントを 6 個と、要素の数が 7 個のセグメントを 4 個作成するといった調整の具合です(8×6 + 7×4 = 76)。

説明のみ

デシル分析のセグメント分けのイメージ

綾子

4

では、はじめに… 後の工程でセグメントごとの集計を簡単にすすめるための準備をしておきます。具体的には、シートの空いているところに 1 ~ 10 のセグメントの集計範囲をセル番地で入力する…といった作業です。

綾子

5

1 番目のセグメントに該当するデータは、すぐ上の図を参照するとB2~B9のセル範囲にあることがわかります。したがって、下のように「B2:B9」と入力します。

ただし、「B」「2」「:」「B」「9」といったように、1 つ 1 つのセルに列と行を分解して、半角文字で入力していきます。この点がポイントです

綾子

6

同じようにして、2 番目のセグメントのセル範囲を入力します。ただ、「開始セル」および「終了セル」の “行” をあらわす数字は、それぞれ一定のスパン(ここでは 8 ずつ)で増えていくことが分かっていますので、このような式で対応できます。

[セルF4]=F3+8, [セルI4]=I3+8

綾子

7

ハイライトの部分( 6 つのセル)を選択し、8 行分コピーします。

綾子

8

今回のケースは…最後のセグメントの要素の数だけ 4 個となるように決めましたので、終了セルの行番号(ハイライトの部分)だけは直接修正が必要となります(このケースでは「77」と入力します)。

綾子

9

表「セグメントとセル範囲との対応」の列見出し「終了セル」の右隣に、「セル範囲」という名のあたらしい見出しを作ります。

[セルJ2]セル範囲

綾子

10

見出し「セル範囲」の直下のセルをアクティブにして、関数の挿入ボタンをクリックします。

綾子

11

関数の挿入ダイアログが表示されます。

関数の検索に "concatenate" と入力し検索開始ボタンをクリックします。なお、Concatenate関数は文字列を結合するための関数です(Concatenate関数 ―"Office")。

concatenate関数の検索

綾子

12

関数名からCONCATENATEを選択しOKボタンをクリックします。

関数名からCONCATENATEを選択

綾子

13

文字列 1にシート上の対応する色(緑)のセル番地を指定します。

同じようにして文字列 2文字列 5も指定します。すべて指定できたら、OKボタンをクリックします。

「concatenate関数」ダイアログ…[文字列1]E3, [文字列2]F3, [文字列3]G3, [文字列4]H3, [文字列5]I3

綾子

14

上で入力した式を、残りのセグメントの数…つまり、9 個ですね…コピーします。

表の体裁をととのえる

綾子

15

ここから目的の…デシル分析表・作成のメイン工程となります。

表「セグメントのセル範囲」の右方が空いていますので、1 列ほど余裕を取ってから、下のように「セグメント」「顧客数」「消化枚数」「顧客あたり平均消化枚数」「構成比」「累積構成比」といったような見出しをあらたに作成しておきます。

scrollable

[セルL2]セグメント, [セルM2]顧客数, [セルN2]消化枚数, [セルO2]顧客平均, [セルP2]構成比, [セルQ2]累積構成比

綾子

16

セグメント」列に「デシル 1」・・・「デシル 10」と入力します(「デシル 1」を作成 コピー で対応できます)。

[セルL3:L12]デシル1~10セグメントの作成

綾子

17

顧客数」列にセグメントごとの顧客数をカウントして入力します。

データを “均等割り” するというデシル分析の趣旨もあり、関数などを使うことなく値をそのまま入力 or コピーしていった方が簡単だと思います。

あ! ただし今回のケースではデシル 10 の要素の数だけは他と異なります。その点だけ注意してください。

綾子

18

消化枚数」列に、各セグメントに属する顧客の チケットを消化した枚数について集計していきます。「消化枚数」列の「デシル 1」の行に、下のように

= デシル 1 の「セル範囲」内での消化枚数の合計となる式を直接入力します。ここで言う 『デシル 1 の「セル範囲」』 とは、下の図のシートの上で…えーと、対応する色のついたセル番地のことです。

  • =SUM(INDIRECT(J3))

入力できたら、デシル 10 の行まで式をコピーします(Indirect関数 ―"Office")。

[セルN3]=SUM(INDIRECT(J3))。この式をデシル10までコピー。

綾子

19

顧客あたり平均消化枚数」列を計算します。各セグメントとも、式は= 消化枚数 ÷ 顧客数です。

  • =N3/M3

入力できたら、デシル 10 の行までコピーします。

[セルO3]=N3/M3。この式をデシル10までコピー。

綾子

20

セグメント」列・最下行の直下のセルに「総計」と入力します。

[セルL13]総計

綾子

21

顧客数」列と「消化枚数」列の総計を計算します。

下のようにシート上の対応する色の範囲をSum関数で合計します。

  • =SUM(M3:M12)
  • =SUM(N3:N12)

[セルM13]=SUM(M3:M12), [セルN13]=SUM(N3:N12)

綾子

22

消化枚数に関する「構成比」列を計算します。

式は= 消化枚数 ÷ 消化枚数の総計です。このとき 「消化枚数の総計」 のセル番地は、コピーすることを考慮して絶対参照としておきます(絶対参照:式中で “消化枚数の総計” のセルを指定してからF4キーを 1 回押します)。

  • =N3/$N$13

入力後、デシル 10 の行まで計算式をコピーします。

[セルP3]=N3/$N$13。この式をデシル10までコピー。

綾子

23

累積構成比」列を計算します。ここでは 2 段階に分けて計算します。

まず、デシル 1 の累積構成比を計算します。こちらは構成比のセル(左の隣接セル)の内容をそのまま参照する式= デシル 1 の構成比とします。

  • =P3

[セルQ3]=P3

綾子

24

次にデシル 2 までの「累積構成比」を計算します。式は= 直前の累積構成比の値 + デシル2の構成比の値です。

  • =Q3+P4

入力できたら、この式をデシル 10 の行までコピーします。

[セルQ4]=Q3+P4。この式をデシル10までコピー。

綾子

25

値にパーセントスタイルを設定します。下のように「構成比」「累積構成比」の両列のデータ範囲を選択してから…

綾子

26

リボンのホームタブ 数値グループにあるパーセントスタイルボタンをクリックします。また、[小数点以下の表示桁数を増やす | 減らす]ボタンで小数点以下桁数を適当なものに調整します。

この分析手法はあくまでグループ分けの目安を提供するものなので、小数点以下桁数を精度の観点から慮る必要性はあまりないと思います。ということで、今回は小数点以下 1 桁としておきます。

ホーム→パーセントスタイル

綾子

27

ちょっと時間かかっちゃいましたけど、デシル分析表の完成です。…はぁふぅ。

FINISHED

デシル分析表の完成

  • 本頁で使用したデータはすべて架空のものです。また特定の会社等に実在する人物名、および同場所で実際に観測されたデータ群などを根拠にしたものでもありません。
.

LastUpdate

2016.7.12

.
このページの先頭へ