ラベル データマイニング の投稿を表示しています。 すべての投稿を表示
ラベル データマイニング の投稿を表示しています。 すべての投稿を表示

2012年12月16日日曜日

CSポートフォリオの改善度をExcelで計算する

このエントリーをはてなブックマークに追加
CSポートフォリオをExcelで描く」で満足度と重要度の二軸でCSポートフォリオを作成して、重点改善項目、改善項目、重点維持項目、維持項目という4象限に分割し、重要度が高く満足度が低い重点改善項目に位置する項目を改善すれば全体満足度が向上するという視覚的表現で捉えた説明をしました。今回は、改善順位を数値で客観的に判断するために改善度をという指標を用いて、Excelで計算する方法について説明します。

Excelで学ぶ多変量解析入門から

CSポートフォリオにおける改善度とは

下図(図2)は原点を(50,50)としたCSポートフォリオです。なぜ原点を(50,50)にしているかについては、「偏差値でポートフォリオを描く」を参照してください。

重要度が高く、満足度が低い項目の中で、原点である平均値座標(50,50)から各項目の座標までの距離が遠く離れていれば改善する必要があると判断できます。また原点(50,50)と座標(80,20)を結ぶ改善度基本軸に近い項目は改善の必要があり、逆に改善度基本軸から離れた項目であれば改善の必要は少ないと考えます。この改善度基本軸から離れ具合を表すために角度を用います。

図2

そして、図2のECで結ばれた線が重要度と満足度が均衡しているラインとなりますので、改善しなければならない項目は、ECBの三角形の範囲内にある項目です。また改善度基本軸で線対称となるABCで結ばれる三角形とABEで結ばれる三角形の範囲では、原点からの距離と改善度基本軸から角度が同じであれば、改善度も同じ値となります。つまり、座標(80,50)と座標(50,20)の原点からの距離は30、改善度基本軸からの角度は45°なので改善度も同じになるということです。

また改善度は改善の必要がある項目を正の値、改善の必要がない項目を負の値で表しますので、改善を必要とするECBで結ばれる三角形の範囲にある項目の改善度は正の値となり、EDCで結ばれる三角形の範囲にある改善度は負の値となります。

改善度とは、原点からの距離と改善度基本軸からの角度から求めるという説明をしましたが、前述した満足度と重要度が均衡するECで結ばれる線上にある項目の改善度は0となります。原点からの距離が異なる、座標(20,20)、座標(50,50)、座標(80,80)に布置される項目の改善度は何れも0と考えます。そのため改善度の計算式ではこれを補正する角度の修正指数を用います。

改善度の計算式

CSポートフォリオにおける改善度は、下記の式で求めます。

距離×角度の修正指数 (90-角度)/90

角度の修正指数について補足します。前述した満足と重要度が均衡するEC上に位置する項目の改善度基本軸からの角度は原点である座標(50,50)以外はすべて90°です。つまり角度が90°の場合に解が0となれば、距離が異なっても距離×0となり、改善度はすべて0となるようにしているということです。ちなみに原点の角度は0なので、こちらも改善度は0となります。

距離については、下記の式で求めることができます。




Excelで計算する場合は、下記の式となります。

=SQRT((x-50)^2+(y-50)^2)

CSポートフォリオの改善度に必要な改善基本軸からの角度を求めるにはどうしたらよいのでしょうか。Excelで計算する場合には、少しだけ工夫が必要となります。

実際に下図(図3)、座標F(70,60)の改善度をExcelで計算してみましょう。

図3
距離については、

=SQRT((70-50)^2+(60-50)^2)

で計算することができます。計算結果は、≒26.57 です。

角度を求める(tanθ)

次に改善度基本軸からの角度ですが、まずAFGで結ばれる三角形の角Aの角度を計算して、次に三角形AHBの角Aの角度を加算すれば求めることができます。

三角形AFGの角Aは三角関数を用いて計算します。AGとFGの距離は計算で求めることができるのでtanΘ=(FGの距離)/(AGの距離)で角Aの角度が求まります。AGの距離は70-50=20、FGの距離は60-50=10なので、tanΘ=10/20です。Excelでは下記の計算式となります。

=ATAN2(20,10)*180/PI()

計算結果は、≒26.57 です。

Excelで三角関数関数を学ぶのであれば、「Excelで学ぶやさしい数学―三角関数から微積分まで」を参考にしてみてください。

Excelで学ぶやさしい数学―三角関数から微積分まで
高橋 幸久 渡辺 八一
オーム社
売り上げランキング: 16444

改善度基本軸からの角度を求める

これで三角形AFGの角Aの角度は求まりましたので、AHBを結ぶ三角形の角Aの角度を足せば、改善度基本軸からの角度となります。

三角形AHBは正三角なので角Aは45°です。上で計算した三角形AFGの角Aの角度は26.57°ですので、改善度基本軸からの角度は 26.57+45=71.57°です。

例として計算した座標(70,60)の場合は、三角形AFGの角A+45°の計算結果は正の値となりますが、改善度基本軸ABより左側に付置される項目では結果が負の値となりますので(2)、絶対値として角度を計算する必要があります。下記がExcelでの計算式となります。

=ABS(ATAN2(20,10)*180/PI()+45)

修正指数の計算

角度の修正指数の計算式は、(90-角度)/90ですから上記で求めたExcelでは下記のように求めます。

=(90-ABS(ATAN2(20,10)*180/PI()+45))/90

改善度の計算


最後に改善度の計算を行います。改善度は【距離×角度の修正指数】なので最終的にExcelでの計算式は下記となります。

=SQRT((70-50)^2+(60-50)^2)*((90-ABS(ATAN2(20,10)*180/PI()+45))/90)

少し長い式となりますが、分解して考えれば難しくはないと思います。座標(70,60)の改善度は上記から≒4.58と求まります。

2 角度の計算結果がマイナスになる場合

下図の場合の改善度基本軸からの角度を計算します。AGの距離は60-50=10、FGの距離は30-50=-20ですので三角形AGFの角Aは、

=ATAN2(10,-20)*180/PI()

で、-63.43°となります。改善度基本軸からの角度は-63.43+45=-18.43となりますので、上で説明したように絶対値をとって18.43°とする必要があります。

検算をしてみよう

計算式があっているか「Excelで学ぶ多変量解析入門」のサンプルを用いて検算してみます。この書籍では第五章でCS分析について記載されています。またCS分析ができるExcelのプログラムが添付されています。

Excelで学ぶ多変量解析入門
菅 民郎
オーム社
売り上げランキング: 120281

書籍上では、重要度偏差値と満足度偏差値が小数点以下1桁で計算されていますが、計算結果が異なってしまうので実際の計算値から小数点以下13桁を記載しています。


改善度は、「Excelで学ぶ多変量解析入門」と同値になります。
計算式については、「品切れがない」についてを例に下記に記載します。計算途中がわかるように分割して計算していますが、

=SQRT((B2-50)^2+(C2-50)^2)*((90-ABS(ATAN2(B2-50,C2-50)*180/PI()+45))/90)

と、まとめて計算しても改善度を求めることができます。

A B C D E F
1 評価項目 重要度 満足度 距離
2 品切れがない 70.31 43.43 =SQRT((B2-50)^2+(C2-50)^2) =B2-50 =C2-50

G H I J
1 tanθ 角度 修正指数 改善度
2 =ATAN2(E2,F2)*180/PI() =ABS(G2+45) =(90-H2)/90 =D2*I2

これで、改善度の計算は終了です。

KINGSOFT Office

Kingsoft Spreadsheets 2012

表題は「CSポートフォリオの改善度をExcelで計算する」ですが、実際はKINGSOFT Office2012 Personalを使っています。キングソフト オフィスの表計算ソフトKINGSOFT Office2012 Spreadsheetsは、Excelとの互換性が非常に高く、操作性はもとより、Excel関数がそのまま利用できます。表計算ソフト単体で¥1,886、プレゼンテーション、ワープロ、表計算がセットになったオフィスで¥3,314と非常にリーズナブルな価格でOffice互換ソフトを利用することができます。またWindows8でも問題なく動作します。

KINGSOFT Office2012 Personal [ダウンロード]
キングソフト (2011-10-31)
売り上げランキング: 98

KINGSOFT Office 2012 Standard パッケージCD-ROM版
キングソフト (2011-10-07)
売り上げランキング: 79

今日の一曲

Corinne Bailey Rae(コリーヌ・ベイリー・レイ)を初めて聴いたのはMarcus Miller のFreeでした。そのときは、アルバムタイトル曲が女性ボーカルなんだという程度の印象で、後にラジオから流れてきた「Like a Star」が、同一人物の歌声だとは気が付きませんでした。
♪Just Like a ... と、車中に静かに流れ始めたこの曲は、とても甘く切ない歌声で、高速道路を走行する車の流れと共鳴し、恋愛に伴う不可解な態度や行動、そして感情を懐かしく想い出させてくれました。




キングソフト オフィスソフト office

2012年9月3日月曜日

CSポートフォリオをExcelで描く

このエントリーをはてなブックマークに追加
顧客満足度調査(CS調査)を行う目的は、お客様が自社の店舗、商品、サービスについて、どの程度満足しているかを調べるためです。そしてCS調査の結果から改善すべき項目をみつけることが重要です。では、どうやって改善すべき項目を見つければよいのでしょうか。CSポートフォリオでは、重要度と満足度でポートフォリオを描くことにより、この改善点を可視化します。

満足度:

各評価項目の満足度です。下記の値が用いられます。
・平均点
・満足率(良いの割合)
・良い(%)-悪い(%)

Q.商品の価格に満足されていますか?
回答項目 回答数 構成比
5.非常に満足している 20 20%
4.満足している 35 35%
3.どちらともいえない30 30%
2.不満がある10 10%
1.非常に不満がある 5 5%

上記の場合の平均点は、
((5点×20)+(4点×35)+(3点×30)+(2点×10)+(1点×5))/100=3.55

満足率は、単純にどれくらいの人が満足かしているかの率です。例えば下記の設問に対しては、非常に満足していると、満足しているしているとした回答が何%あるかが満足率になります。つまり、「非常に満足している」と「満足している」の構成比を合算(20%+35%)した55%が満足率となります。

良い(%)-悪い(%)については、過去のブログで「良い(%)-悪い(%)」に記載していますが、満足している(20%+35%)から不満がある(10%+5%)を減算した、40%となります。この考え方は、ネットプロモータスコアも同じなので「NPS: Net Promoter Score」も併せて参考にしてください。

重要度:

各項目が総合満足度にどれくらい寄与しているか(相関があるか)が重要度となります。下記の手法を用いて重要度を計算します。
・単相関
・回帰分析
・数量化Ⅰ類
・因子分析

重要度を表す指標に関しては、「総合満足度」「次回利用意思」「推奨」などを用いればよいのではないでしょうか。

例えば、ある商品に関しての満足度調査を行った場合に下記のような設問になります。

あなたは、○○○に満足していますか?【総合満足度】
あなたは、○○○を次回も購入したいと思いますか?【次回利用意思】
あなたは、○○○を友人や知人にも勧めたいと思いますか?【推奨】

そして各項目の満足度がどれだけ総合評価に寄与しているかを調べます。単相関、回帰分析であればExcelのデータ分析ツールで簡単に求めることができます。回帰分析の場合には係数を利用します。

CSポートフォリオの4象限

CSポートフォリオの4象限

①重点改善項目

ここに位置する項目は、重要度が高いのに満足度が低い項目となります。つまり総合満足度を上げるために最優先で改善しなければならい項目となります。

②重点維持項目

現状の満足度調査で、重要度も満足度も高い項目になりますので、引き続き項目の満足度を下がらないようにする必要があります。

③維持項目

満足度は高いけど、あまり総合評価に起因しない項目となります。折角、満足度は高いので維持する必要はあります。

④改善項目

総合評価への影響は少ないけれども、満足度が低い項目となります。重点改善項目の次に改善を必要とする項目となります。

CSポートフォリオを描こう

ポートフォリオについては「偏差値でポートフォリオを描く」でExcelでのポートフォリオの描き方を説明していますのでこちらを参考にしてください。コツは4象限に分けるので、データを標準化して平均値を使うことです。CSポートフォリオでもY軸とX軸の単位の意味はあまりないので、偏差値にして偏差値50で交点させるときれいに描写できます。

また散布図にラベルをふる方法については、「Excelの散布図にラベルをつける」を参考にしてください。

下図が作成したCSポートフォリオになります。
CSポートフォリオの例
上記のCSポートフォリオから重要度が高く、満足度が低い「商品が品切れしていない」ことがお客様にとって大切なことであり、全体の満足度低下にもつながっていることが読み取れます。つまり、最優先で改善すべきことが「在庫の担保」であることがわかります。

最後に...

CS分析やCSポートフォリオについては、すべてわかるアンケートデータの分析Excelで学ぶ多変量解析入門に詳細の説明がありますので、参考にしてください。上記のポートフォリオについても2冊に掲載されているサンプルをもとに作成しています。またExcelで学ぶ多変量解析入門には、付属でCD-ROMがあり、CSポートフォリオが簡単に描写できます。

次回は、CS分析で改善項目の優先度の指標となる改善度について説明したいと思います。
(「CSポートフォリオの改善度をExcelで計算する」を更新しました[2012.12.16])


すべてわかるアンケートデータの分析
菅 民郎
現代数学社
売り上げランキング: 354116
Excelで学ぶ多変量解析入門
菅 民郎
オーム社
売り上げランキング: 242115

今日の一曲

ひと月に2回満月になることがあります。この2回目の満月のことをブルームーンと呼びます。先日の2012年8月31日がちょうどブルームーンでした。英語では「once in a blue moon」というと、めったに起きないということの慣用句となっています。本来は、大気中の塵などが反射して月が青くなることをさしていたようですが、青い月を想像するとなんだか妖艶な感じがします。そして、今日の一曲のタイトルは、Mabel Mercerが歌う「Once In A Blue Moonです。古いJazz歌手ですが、とても切なくこの歌を歌いあげます。ブルームーンでなくても月を見上げてこの曲を聴くと切なくなります。

once in a lifetime when moon is blue...


2012年3月25日日曜日

ExcelでBox Plot(箱ひげ図)を描く

このエントリーをはてなブックマークに追加
Excel 2007での箱ひげ図(はこひげず、箱髭図、ボックスプロット、box plot)の作成方法を紹介します。元ネタは、Box Plot for Excel 2007からとなります。

下記が完成した箱ひげ図(box plot)です。株価チャートを使って似たようなグラフを作成できますが、100万倍こちらの方がステキです。
箱ひげ図
Box Plot(箱ひげ図)

箱ひげ図(はこひげず、箱髭図、ボックスプロット、box plot)とは

箱ひげ図(box plot)は、データの分布を可視化するのに優れたグラフです。特に幾つかのデータの分布を比較するのに長けており、分布がどのように異なっているかを視覚的に捉えるのに便利です。Wikipediaで下記のように説明されています。
箱ひげ図(はこひげず、箱髭図、box plot)とは、ばらつきのあるデータをわかりやすく表現するための統計学的グラフである。細長い箱と、その両側に出たひげで表現されることからこの名がある。一般的には(ジョン・テューキーの方式)、重要な5種の要約統計量である、最小値、第1四分位点、中央値、第3四分位点と最大値を表現する。母集団は実際には様々なタイプの確率分布に従うわけだが、箱ひげ図はそのような仮定に関係なく、データの分布を表現することができる。箱の各部分の間隔から分散や歪度の程度、また外れ値(これは後述のように箱ひげ図の方式により異なる)を知ることもできる。
箱ひげ図(box plot)は、データを1/4(25%)ずつに分割して、最小値、第一四分位(25%点)、中央値(50%点)、第三四分位(75%)、最大値の5つの要素でデータの分布を可視化します。
データの分布を5つの要素で可視化

Excelで四分位を求める

Excelで四分位を求めるには、=percentile()、若しくは=quartile()で求めることができます。最大値、中央値、最小値は、それぞれ=max、=median()、=min()で求めることもできます。

四分位の求め方
項目名備考PERCENTILEQUARTILE
最大値データの最大値PERCENTILE(データ範囲,1) QUARTILE(データ範囲,4)
第3四分位データの下から3/4に位置する値(75%点)PERCENTILE(データ範囲,0.75)QUARTILE(データ範囲,3)
中央値データの中央値(50%点)PERCENTILE(データ範囲,0.5)QUARTILE(データ範囲,2)
第1四分位データの下から4/1に位置する値(25%点)PERCENTILE(データ範囲,0.25)QUARTILE(データ範囲,1)
最小値データの最小値PERCENTILE(データ範囲,0)QUARTILE(データ範囲,0)

Excel 2007で箱ひげ図(はこひげず、箱髭図、ボックスプロット、box plot)を描く

Box Plot for Excel 2007にあるサンプルデータを使用して説明しますが、今回作成したサンプルを下記に置いてありますのでご利用ください。



サンプルデータ
 
ABCDEFG
1 sample1 sample2 sample3 sample4 sample5 sample6
2 50.5 46.7 43.2 62.5 52.0 53.2
3 51.3 45.5 45.3 64.2 52.3 58.6
4 55.3 45.6 43.2 66.1 55.0 55.4
5 50.3 46.3 43.5 66.7 54.3 53.5
6 55.0 49.7 45.6 63.4 52.6 56.0
7 59.6 49.8 43.1 67.7 53.9 57.6
8 51.3 48.5 45.4 62.2 51.2 54.5
9 56.1 48.7 46.0 68.4 52.1 58.7
10 59.7 48.8 44.1 62.7 54.5 55.7

12-16行にそれぞれ最大値、第三四分位、中央値、第一四分位、最小値を求めます。

B12:B16に下の式を代入してC12:C16からG12:G16にコピーします。

=QUARTILE(B2:B10,4)
=QUARTILE(B2:B10,3)
=QUARTILE(B2:B10,2)
=QUARTILE(B2:B10,1)
=QUARTILE(B2:B10,0)

四分位数の計算結果)
ABCDEFG
12最大値59.749.846.068.455.058.7
13第3四分位56.148.845.466.754.357.6
14中央値55.048.544.164.252.655.7
15第1四分位51.346.343.262.752.154.5
16最小値50.345.543.162.251.253.2

次にグラフを描写するために必要な値を計算して18-22行に代入します。

B18:B22に下の式を代入してC12:C16からG12:G16にコピーします。

=B15-B16
=B15
=B14-B15
=B13-B14
=B12-B13

グラフ用の計算値)
ABCDEFG
18第1四分位-最小値10.80.10.50.91.3
19第1四分位51.346.343.262.752.154.5
20中央値-第1四分位3.72.20.91.50.51.2
21第3四分位-中央値1.10.31.32.51.71.9
22最大値-第3四分位3.610.61.70.71.1

積み上げ縦棒グラフで基本グラフの作成

A19:G21を選択して、2-D 縦棒から積み上げ縦棒を選択します(下記のイメージを参考)。
積み上げ縦棒グラフの作成
作成したグラフは下から第1四分位、中央値-第1四分位、第3四分位で構成されています。このグラフをよくよく眺めると赤色と緑色の部分が箱ひげ図(Box Plot)の箱の部分になっていることが分かりますでしょうか。

積み上げ縦棒グラフ

箱ひげ図の箱を作成する

作成した積み上げ縦棒グラフの青色(第1四分位)の部分を「塗りつぶしなし」にして、箱ひげ図の箱の部分だけにします。

1)グラフの青色(第1四分位)部分をマウスで選択して、右クリックで「データ系列の書式設定」を選択

第1四分位を選択
2)「データ系列の書式設定」→「塗りつぶし」で「塗りつぶしなし(N)」を選択

第1四分位を塗りつぶしなしに設定
これで、箱ひげ図の箱の部分が作成できました。

箱ひげ図(box plot)の箱部分

箱ひげ図(box plot)の髭(ひげ)を作成する

箱ひげ図(box plot)の髭(ひげ)の部分は、誤差範囲で作成します。

1)グラフエリアを選択してツールバーのレイアウトから誤差範囲の「その他の誤差範囲オプション(M)」を選択

誤差範囲の指定
2)誤差範囲の追加から「第1四分位」を選択


3)箱ひげ図の下側の髭(ひげ)を作成するため縦軸誤差範囲の表示は「負の方向」「キャップあり」を選択し、誤差範囲はユーザ設定を選択。値の指定で「負の誤差の値(N)」に第1四分位-最小値(B18:G18)を範囲選択して値を入力


箱ひげ図(box plot)の下髭を作成
これで下側の髭(ひげ)が完成です。

箱ひげ図(box plot)の下髭
続いて上側の髭(ひげ)を作成します。

4)グラフエリアを選択してツールバーのレイアウトから誤差範囲の「その他の誤差範囲オプション(M)」を選択

誤差範囲の指定
5)誤差範囲の追加から「第3四分位-中央値」を選択


6)箱ひげ図の上側の髭(ひげ)を作成するため、縦軸誤差範囲の表示は「正の方向」「キャップあり」を選択し、誤差範囲はユーザ設定を選択。値の指定で「正の誤差の値(N)」に最大値-第3四分位(B22:G22)を範囲選択して値を入力


箱ひげ図(box plot)の上髭を作成 
これで髭(ひげ)の部分も作成完了です。

箱ひげ図(box plot)の髭(ひげ)

箱ひげ図(box plot)の見栄えを調整する

箱ひげ図(box plot)はこれで一応完成となります。後は箱ひげ図(box plot)の見栄え(ボックスの縦幅と横幅、色など)を調整します。

ボックス縦幅の調整

1)グラフの縦軸を選んで右クリックから「軸の書式設定(F)」を選択

軸の書式設定
2)軸のオプションで最小値と最大値を自動から固定に変更して値を入力します。今回のサンプルでは、最小値を40、最大値を70としています

軸のオプション
下記が縦幅を調整した箱ひげ図になります。

縦に広がった箱ひげ図(box plot)

ボックス横幅の調整

次に横幅を調整します。

1)グラフの赤色(中央値-第1四分位)を選択して右クリックで「データ系列の書式設定(F)」を選択

データ系列の書式設定
2)系列のオプションで「要素の間隔(W)」で箱の横幅を調整します。値が小さくなるほど、箱の横幅は広がります。サンプルでは50%としています


横幅を調整したグラフが下記になります。

箱の横幅を調整した箱ひげ図(box plot)

グラフの色の削除

グラフの色を削除する場合に枠線をなしのままにしていると箱が表示されなくなってしまいますので注意してください。

1)グラフの赤色(中央値-第1四分位)部分を選択して右クリックで「データ系列の書式設定(F)」を選択


2)枠線の色で「線(単色)(S)」で枠線の色を指定


3)塗りつぶしで「塗りつぶしなし(N)」を選択


これで箱ひげ図の箱の下側が色がなくなり、枠線だけになりました。


同様にグラフの緑色(第3四分位-中央値)部分についても枠線だけにします。

箱ひげ図の完成

凡例を削除すれば、箱ひげ図の完成です。好みで箱に色を付けたり、目盛り線を調整してください。

完成した箱ひげ図

Excel Hacks 第2版― プロが教える究極のテクニック140選
Excel Hacks 第2版― プロが教える究極のテクニック140選David Hawley Raina Hawley 羽山 博

オライリー・ジャパン 2007-11-22
売り上げランキング : 155743


Amazonで詳しく見る by G-Tools

今日の一曲


Yellow Magic Orchestraのアルバムで唯一保有していなかったのが、『浮気なぼくら(インストゥルメンタル)』でした。iTunesでYMOのアルバムが公開され、実にリリースから28年経って、2枚組の『浮気なぼくら&インストゥルメンタル』を購入しました。アルバムを聴いてみると、後期の『BGM』、『テクノデリック』で完成されたYMOがしっかりと組み込まれていました。そして細野さんのリズムと歌詞は、いつの時代もステキです。今日の一曲は、ユキヒロと細野さんのハーモニーが至極刺激的な『Focus』です。




 
"));