「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ポートフォリオにおける改善度は、下記の式で求めます。
角度の修正指数について補足します。前述した満足と重要度が均衡する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で学ぶやさしい数学―三角関数から微積分まで」を参考にしてみてください。
改善度基本軸からの角度を求める
これで三角形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と求まります。
角度の計算結果がマイナスになる場合
下図の場合の改善度基本軸からの角度を計算します。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で学ぶ多変量解析入門
書籍上では、重要度偏差値と満足度偏差値が小数点以下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
*2017/07/09 追記
KING SOFT OFFICE は、新たにWPS OFFICEとして生まれ変わりました。
今日の一曲 - Like a Star
コリーヌ・ベイリー・レイを初めて聴いたのはMarcus Miller の『Free』でした。そのときは、アルバムタイトル曲が女性ボーカルなんだという程度の印象で、後にラジオから流れてきた「Like a Star」が、同一人物の歌声だとは気が付きませんでした。
♪Just Like a ... と、車中に静かに流れ始めたこの曲は、とても甘く切ない歌声で、高速道路を走行する車の流れと共鳴し、恋愛に伴う不可解な態度や行動、そして感情を懐かしく想い出させてくれました。
0 件のコメント:
コメントを投稿