Translate

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となります。

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

\[ \sqrt{\left( x - \bar{x} \right) ^2 + \left( y - \bar{y} \right) ^2} \]

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で学ぶ多変量解析入門」のサンプルを用いて検算してみます。この書籍では第五章でCS分析について記載されています。またCS分析ができる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 は、Excelとの互換性が非常に高く、操作性はもとより、Excel関数がそのまま利用できます。KINGSOFT OFFICE は、非常にリーズナブルな価格でOffice互換ソフトを利用することができます。またWindows8でも問題なく動作します。

*2017/07/09 追記

KING SOFT OFFICE は、新たにWPS OFFICEとして生まれ変わりました。


今日の一曲 - Like a Star

コリーヌ・ベイリー・レイを初めて聴いたのはMarcus Miller の『Free』でした。そのときは、アルバムタイトル曲が女性ボーカルなんだという程度の印象で、後にラジオから流れてきた「Like a Star」が、同一人物の歌声だとは気が付きませんでした。

♪Just Like a ... と、車中に静かに流れ始めたこの曲は、とても甘く切ない歌声で、高速道路を走行する車の流れと共鳴し、恋愛に伴う不可解な態度や行動、そして感情を懐かしく想い出させてくれました。

2012年12月2日

これは簡単!PEラインとリーダーの結び方

アオリイカ

エギングは簡単に始められる釣りですが、一番の難関は、何といっても道糸として使う極細のPEラインとリーダーのフロロカーボンラインという二種類の異なったラインを結んで繋げなければならないということだと思います。ラインの結び方が悪いとガイドで引っかかったり、スッポ抜けたり、結びめでラインが切れたりしてしまいます。そこで簡単なPEラインとリーダーの結び方がYoutubeにアップされていましたので紹介します。

エギングは簡単に始められる釣りですが、一番の難関は、何といっても道糸として使う極細のPEラインとリーダーのフロロカーボンラインという二種類の異なったラインを結んで繋げなければならないということだと思います。ラインの結び方が悪いとガイドで引っかかったり、スッポ抜けたり、結びめでラインが切れたりしてしまいます。そこで簡単なPEラインとリーダーの結び方がYoutubeにアップされていましたので紹介します。

先ずは、エギングで使用するラインのおさらいから、


PEラインとは

道糸として使用するPEラインは、ポリエチレン繊維の撚り糸で強度が強く、細くて伸びがない軽いラインです。ポリエチレン(PolyEthylene)のスペルからPEと呼ばれています。遠くに飛ばし、シャクリの動作を餌木に正確に伝えるのには理想的なラインです。

ラインの1号は、5尺(約150cm)の重さが1厘(0.0375g)というのがナイロンラインでは標準直径として決められているようです。つまり号数はもともと5尺の重さを意味しているので、2号のラインは、1号のライン直径が2倍となっているわけではないようです。但し、現在発売されているPEの号数と直径は各メーカによって異なるようです。

エギングなら0.6号のPEラインがお勧めです。


フロロカーボンリーダーとは

エギングでは道糸の先にリーダーとしてポリフッ化ビニリデン樹脂(フロロカーボン)素材のリーダーを直結します。フロロカーボンは、比重が重く沈みやすく、伸度がなく、強度があります。餌木を着底させるエギングではショックリーダーは根擦れに強い必要があります。また浮力のあるPEを使うので、餌木をフリーフォールさせるためにも比重が重いリーダは必要です。自分は0.6号のPEラインに2.0号のリーダーを組み合わせています。


PEラインとリーダーの結び方

エギングを始めたときに最初に覚えた結び方が、電車結びと呼ばれる(ダブルユニノット)です。下記の動画は英語の説明ですが映像を見ているだけでも結び方がわかるかと思います。説明ではユニノット(輪っかにぐるぐると糸を巻きつける)の回数を6回とありますが、エギング使うPEラインは細いので0.6号であれば8回は巻きつけたいところです。また電車結びは強度が低いためPEラインを二重にして結束すると強度は高くなりますが、結びめが大きくなってしまいます。

最近、Youtubeで見つけた「ヒゲなしノット」は電車結びよりもっと簡単で、慣れれば1分もかからずにPEとリーダーを結束できます。電車結びも簡単な部類ではありますが、それでも最初の頃は時間がかかっていました。初めか「ヒゲなしノット」に出会っていればと思います。実際のエギング釣行でも「ヒゲなしノット」でキロオーバークラスでも問題ありませんでしたし、もちろん、すっぽ抜けるようなこともありませんでした。


超簡単「ヒゲなしノット」の結束方法

「ヒゲなしノット」の結束方法についても動画を見れば理解できると思いますが、説明には以下のように記載されています。

  1. リーダーを折り返し2重(輪っか状)にしてから8~10回編み込む。
  2. PEヒゲをリーダーの輪っかに通してハーフヒッチで仮止めする。
  3. 輪っかがなくなるまで編み込み部を端にスライドさせ、PEとリーダーを軽く引っ張って締め込む。
  4. リーダーヒゲを切り落とし、編み目を2つほど切り口に被せる。
  5. PEとリーダーを力いっぱい引っ張って締め込む。
  6. PEヒゲを切り落として完成。

ヒゲなしノットは、オルブライトノットの変形だと思います。PEを編み込むことでオルブライトノットよりも結束強度を高めているのではないかと思います。

参考までにオブラインノットの動画も掲載しておきます。

DO! エギング!!


今日の一曲

エギングの休憩中にアオリイカが海中から眺める空を思い浮かべながら無我の境地で聴くのに最適な一曲です。iTunes Storeでは『/ 05』に再収録された『Aqua』しかありませんが、個人的には『BTTB』に収録されているバージョンの方が演奏も率直で好きです。教授にしては珍しくメジャーコードで始まる曲でとてもシンプルですが、シンプルであるが故にメロディーの美しさが際立っています。当時、購入したBTTBには譜面とFDが附属されていました。FDの中身が何であったかは忘れてしまいました。そして、今となっては、自宅の環境では確認することもできなくなってしまったことに時代の変遷を感じます。

アクセス上位(過去7日間)