図1の散布図のように１つの列データから２つの直線が出てきた場合に、図3の散布図のように近似曲線を求めたい。
しかしそのまま近似曲線を出すと図2のように採りたい近似曲線とは違う曲線になってしまう。
そのためには図1のデータを上の曲線と下の曲線に分けなければならない。
しかし、データ数が大量の場合、手動で仕分けをするのは不可能に近い。
今回は簡単に自動的に仕分けをする方法を分かりやすく解説していきます。
※図3を作れるようになります
２つの関数だけでデータを分ける
SLOPE関数
SLOPE関数は、グラフの傾き（かたむき）を求める関数です。
つまり、xが増えたときに、yがどれくらい増えるかを表します。
| 行 列 | A | B |
| 1 | X | Y |
| 2 | 1 | 3 |
| 3 | 2 | 5 |
| 4 | 3 | 7 |
ExcelでA2:B4セルにX、Yの値を上記のように入力します。
X＝１のとき、Y＝３
X＝２のとき、Y＝５ ・・・の場合は傾きは２であると分かりますね！
これをSLOPE関数を使用して傾きを求める場合は
=SLOPE(B2:B4, A2:A4)と入力することで結果が2と表示されます
INTERCEPT関数
INTERCEPT関数は、y切片（グラフがy軸と交わる点のyの値）を求める関数です。
つまり、xが0のときのyの値を出します。
| 行 列 | A | B |
| 1 | X | Y |
| 2 | 1 | 3 |
| 3 | 2 | 5 |
| 4 | 3 | 7 |
先ほどと同じデータで切片を求める。この関係を式にすると
y = 2x + 1
この「+1」の部分が 切片 です。つまり、x = 0 のとき y = 1。
これをINTERCEPT関数を使用して切片を求める場合は
=INTERCEPT(B2:B4, A2:A4)
グラフの式
y = ax + b
「a」が SLOPE関数で出す部分、
「b」が INTERCEPT関数で出す部分です！
関数を使ってデータを仕分ける方法について
①
図1のようにきれいに分かれている場合は、２つのデータ分布の間を通る2適当な点を決める。
今回の場合は(50,15.9)、(90,14.6)の2点とします。
この2点を通る直線(赤線)を引き、この直線の傾きと切片を求める。
表1のとおり、傾きと切片は
傾き＝-0.0325
切片＝17.525
②
図1の元データ(流量,圧力)と先ほど求めた赤線の傾き-0.0325と切片17.525から
赤線より上にあるデータか下にあるかをＩＦ文を使って判定します。
表2は図1の元データの一部です。X軸は流量、Y軸は圧力となっています。
C２セルに以下のIF文を入力します
=IF(-0.0325*A2+17.525>B2,"上","下") 'ax+b>yならば"上",違う場合は"下"
こうすることで、簡単に赤線を境にデータを仕分けることが出来ます
③
フィルタ機能を使用してC列に”上”と入力されているデータだけを表示させることが出来ます。
このデータを他のシートにコピペします。
④
同じように”下”のデータを表示させ、ほかのシートにコピペします。これでデータの仕分けが終了します。あとは散布図で２つの曲線を表示させれば図3を作ることが出来ます。
それぞれの曲線に近似曲線＆近似式を求めることが出来るようになりました
コメント