【Excel エクセル】 グラフの近似曲線の係数をセルに書き出す方法
エクセルでグラフを作り、近似曲線を追加したとき、近似曲線の書式設定で「数式を追加する」というところにチェックを入れるとグラフ上に数式が出てきます。
データ解析をする際に、この式の係数をセルに書き出したいと思ったことはありませんか。
例えば、近似曲線の式を使ってデータ点間の値を予測したり、複数の近似式の係数を比較したりするなど、セルに近似曲線の式の係数を書き出すとデータ解析に便利な場合があります。
毎回グラフを作成し、グラフに表示された数式の係数をセルに打ち込むという作業は面倒で非効率です。
そこで、今回は、近似曲線の係数をセルに書き出す関数をご紹介したいと思います。
線形近似曲線の式 y=ax+b
一番シンプルな線形近似の場合は、以下の関数で求めることができます。
a=SLOPE(yの範囲, xの範囲)
b=INTERCEPT(yの範囲, xの範囲)
yの範囲の部分にグラフの縦軸とするデータ系列、xの範囲の部分にグラフの横軸とするデータ系列を選択してください。
yの範囲を先に選択する点に注意してください。
SLOPEは傾き、INTERCEPTは切片を意味しています。
多項式（2次式）の近似曲線の式 y=ax^2+bx+c
2次関数の場合は以下のように、LINEST関数を使用します。
a=INDEX(LINEST(yの範囲, xの範囲^{1,2}),1,1)
b=INDEX(LINEST(yの範囲, xの範囲^{1,2}),1,2)
c=INDEX(LINEST(yの範囲, xの範囲^{1,2}),1,3)
多項式（3次式）の近似曲線の式 y=ax^3+bx^2+cx+d
3次式は以下のようになります。
a=INDEX(LINEST(yの範囲, xの範囲^{1,2,3}),1,1)
b=INDEX(LINEST(yの範囲, xの範囲^{1,2,3}),1,2)
c=INDEX(LINEST(yの範囲, xの範囲^{1,2,3}),1,3)
d=INDEX(LINEST(yの範囲, xの範囲^{1,2,3}),1,4)
4次式以上の場合も同様に後ろの部分を増やしていくことで求めることができます。
指数近似曲線の式 y=a×e^bx
指数近似曲線の場合は、以下の式で係数が求められます。
a=EXP(INTERCEPT(LN(既知のy), 既知のx))
b=SLOPE(LN(既知のy), 既知のx)
LNは自然対数を意味しています。
累乗近似曲線の式 y=a×x^b
累乗近似曲線の場合は、以下の式で係数が求められます。
a=EXP(INTERCEPT(LN(既知のy), LN(既知のx)))
b=SLOPE(LN(既知のy), LN(既知のx))
対数近似曲線の式 y=aln(x)+b
対数近似曲線の場合は、以下のようになります。
a=SLOPE(既知のy, LN(既知のx))
b=INTERCEPT(既知のy, LN(既知のx))
まとめ
今回は、エクセルでグラフを書いたときに、近似曲線の式の係数をセルに書き出す方法をご紹介しました。
毎日のルーチンになっている作業が少しでも簡単にできるようになれば、トータルとして大きな効率化につながります。
エクセルには、作業を効率化できる仕組みが数多くありますので、少しずつ学んでいき、快適に仕事ができる環境を作っていきましょう。