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

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

How-to

デシル分析表

ここでの作業の主旨は,ある変数(金額や数量・回数など)の中身を降順に並べ替えて,ほぼ均等な10個のセグメントに分割することとなります。デシル分析は,それらセグメント間に見られる値の差からグループ別の管理の効率を推量していく手法です。

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

以下,エクセルによるデシル分析表の作り方です。ここでは一連の手続きをExcel 2016で追っています。Excel 2013,Excel 2010およびExcel 2007についても基本的には同じ流れとなりますが,一部ボタンの配置や名称などが異なる箇所があります。

元データ

綾子

1

元のデータ(一部)です。

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


デシル分析・元データ

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

綾子

2

76件のレコードですから

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

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

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

綾子

3

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

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


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

説明のみ

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

綾子

4

では,はじめに後の工程でセグメントごとの集計を簡単にすすめるための準備をしておきます。

具体的には,シートの空いているところに110のセグメントの集計範囲をセル番地で入力するといった作業です。

綾子

5

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

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

綾子

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ボタンをクリックします。

scrollable

「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")。

scrollable

[セル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 の行までこの式をコピーします。

scrollable

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

綾子

23

累積構成比」列を計算します。ここでは2ステップに分けて計算することにします。

まず,デシル1の累積構成比を計算します。こちらは構成比のセル(左の隣接セル)をそのまま参照(= デシル 1 の構成比)していきます。

  • =P3

scrollable

[セルQ3]=P3

綾子

24

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

= 直前の累積構成比の値 + デシル2の構成比の値

です。

  • =Q3+P4

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

scrollable

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

綾子

25

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

綾子

26

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

とはいえこの分析手法はあくまでグループ分けの目安を提供するものなので,小数点以下桁数を精度の観点から慮る必要性もあまりなさそうに推察します。ということで,この場所では小数点以下1桁のみ有効としておきたいと思います。

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

綾子

27

ちょっと時間かかっちゃいましたけど,デシル分析表の完成です。

FINISHED

デシル分析表の完成

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

LastUpdate

2017.3.27

.
このページの先頭へ