Excelによる、デシル分析表のつくり方。

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

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

76件のレコードですから

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

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

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

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

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

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

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

大まかに言えば，1～10のセグメントの集計範囲をセル番地で指示する作業です。

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

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

集計を効率的におこなう上で，この作業がかなめになります。

同じようにして，2番目のセグメントのセル範囲を入力します。

このとき，「開始セル」および「終了セル」の“行”をあらわす数字は，それぞれ一定のスパン(ここでは8ずつ)で増えていくことが分かっていますので，このような式で対応できます。

• CELL F4=F3+8
• CELL I4=I3+8

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

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

表「セル範囲からセグメントを定義する」の列見出し「終了セル」の右隣に，セル範囲という名のあたらしい見出しを用意します。

見出し「セル範囲」の直下のセルに次の式を入力します。これによりE3からI3までのセルの内容をアンパサンドで文字列として連結します。

• CELL J3=E3&F3&G3&H3&I3

なお比較的あたらしめのバージョンのExcelであれば，同じことをするにも引数に範囲指定の利くConcat関数で繋げるほうがより簡単です。

• CELL J3=CONCAT(E3:I3)

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

ここからは目的のデシル分析表そのものに係る工程です。

表「セル範囲からセグメントを定義する」の右方が空いていますので，1列ほど余裕を取ってから，下のようにセグメント顧客の数使用枚数1顧客あたりの使用枚数構成比累積構成比の各見出しをあらたに配置しておきます。

シート上ではスペースの都合便宜的に 使用枚数(u)/顧客の数(n) を意味する u/n と表記しています。

「セグメント」列に「デシル1」「デシル10」と入力します。

「デシル1」を作成 コピー で対応できます。

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

データを“均等割り”するデシル分析の趣旨に鑑みれば，計算式によらずとも値をそのまま入力orコピーしていくのが明快でしょう。

とはいえ，最初に決めたようにこの例では「Decile 10」の顧客の数をイレギュラーとしています。その点だけは注意が必要です。

「使用枚数」列に，チケットの消化状況をセグメントごとにひっくるめつつ集計していきます。

この列の「Decile 1」の行に，下の図のように

デシル1の「セル範囲」に含まれる「使用枚数」の合計

となる式を入力します。ここで言う 『デシル1の「セル範囲」』 は 9，10 であらかじめ用意した連結済みの文字列を指しています。

• CELL N3=SUM(INDIRECT(J3))

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

「1顧客あたりの使用枚数」列を計算します。

各セグメントとも，式は

使用枚数÷顧客の数

です。

• CELL O3=N3/M3

入力できたら，これもデシル10までコピーします。

「セグメント」列・最下行の直下のセルに，集計のための見出し計を用意します。

「顧客の数」列と「使用枚数」列それぞれの計をとります。

• CELL M13=SUM(M3:M12)

• CELL N13=SUM(N3:N12)

使用枚数についての「構成比」列を計算します。式は，

使用枚数÷総使用枚数

です。このとき 「総使用枚数」 のセルは，あとからコピーすることを念頭に絶対参照としておきます。

式の中で「使用枚数」の「計」のセルを指定してからF4キーを1回押します。

• CELL P3=N3/$N$13

入力後，デシル10の行までこの式をコピーします。

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

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

• CELL Q3=P3

Excelについてある程度の練度にあれば，CELL Q3=SUM($P$3:P3) を最下行までコピーした方がスマートです。この場合，20 の工程を無視できます。

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

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

です。

• CELL Q4=Q3+P4

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

値にパーセントスタイルを適用します。下のように「構成比」「累積構成比」両列の見出しを除く領域を選択してから

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

もっともこの手法を採用する趣旨からすれば，あえて小数点以下桁数を慮らねばならない理由もないでしょう。ですので小数点以下1桁程度か，ぶっちゃけ0でも憚りもないところかとは思いますが，いずれにしろユーザーが任意の判断で設定すればOKです。

その他，任意の書式設定を経てデシル分析表の完成です。