ID非公開
ID非公開さん
2022/6/21 12:21
2回答
Excelの散布図で2系列の分割ラインを作成しようとしています。 具体的には、 回帰直線を平行移動させ、回帰式の上にある点の数をカウントし、2系列の数が逆転するところまで平行移動させる。
Excelの散布図で2系列の分割ラインを作成しようとしています。 具体的には、 回帰直線を平行移動させ、回帰式の上にある点の数をカウントし、2系列の数が逆転するところまで平行移動させる。 といった内容を考えています。 そこで、Excelにおいて分割直線の上にある点の数をカウントするような方法はありますでしょうか。教えていただけると幸いです ※Excel2016を使用しております。
補足
説明が不足していました。 回帰式の上というのは、 回帰直線上ではなく、その線より上側の領域という意味です。
ベストアンサー
元データが不明のため、適当に値を決めています。 添付の表のA列B列を元データとします。 まず、回帰直線を求めます。 H2=SLOPE(B2:B22,A2:A22) H3=INTERCEPT(B2:B22,A2:A22) 回帰直線を移動させる際の値（x軸方向）をH5に入力します。 （最初は0と入力しておきます） 次に回帰直線を移動させた際の切片を求めます。 H4=H3-H5*H2 この直線（回帰直線を移動させているので、回帰直線ではなく、分割直線と呼ぶことにします）をグラフに追加するために D2=0 D3=100 とし、（元データに合わせて決めてください） E2=$H$2*(D2-$H$5)+$H$3 E2をE3にコピー （E2=$H$2*D2+$H$4 でもOK） A2:B22を元に散布図を作成し、それにD2:E3を追加します。 次に、元データのそれぞれが、この分割直線の上側か下側かを判定します。 C2=B2-$H$2*A2-$H$4 これをC22までコピー 正になれば分割直線の上側、負になれば下側、にあるということです。 H7=COUNTIF(C2:C22,">=0") H8=COUNTIF(C2:C22,"<0") とすることで、上側、下側それぞれの個数をカウントします。 （直線そのものの上にある場合は「上」としています） H5を変化させれば、どこで上側の個数と下側の個数が入れ替わるかがわかります。（手入力としますが、計算で求めることもできそうですね） 添付の表では、フォームコントロールのスピンボタンで連続的に変化させるようにしていますが、必須ではないので、説明は省略します。
なお、この例の場合、個数の変化は添付のグラフのようになります。 >Excelにおいて分割直線の上にある点の数をカウントするような方法はありますでしょうか。 というご質問なので、こういう方法でどうでしょう？ という回答です。 >調べている中で私のやりたい事が判別分析に近いような気がしました。このような場合は判別分析を行うほうがよろしいのでしょうか。 については、わかりかねます。
質問者からのお礼コメント
お二人ともご丁寧にありがとうございます。 どちらもとても参考になりお二人ともベストアンサーにしたいところですが、先に回答いただいたyha…さんをベストアンサーにさせていただきたいと思います。 ありがとうございました。
お礼日時：7/1 22:49
