表計算ソフトによる曲線あてはめ作業

公開

 Excelなどの表計算ソフトを使用した曲線あてはめによるノッチ曲線のデータ補間方法について紹介します。グラフを作成してから近似曲線とその式を表示させて近似曲線の式の係数を得る手法と、表計算ソフトの関数から直接係数もしくは近似曲線のy座標を得る二種類の手法があります。

 当記事の解説の実例を示したサンプルシートを以下にて公開します。解説とあわせてご覧ください。

>>Download xlsx file (curve-fitting-sample.xlsx)

>>Download xls file (curve-fitting-sample.xls) - Excel2003以前向け

近似曲線の式の利用

 散布図グラフを作成し、近似曲線をそこに追加することにより近似曲線の各係数を求めます。ExcelやGoogle スプレッドシートで使用できます。直感的な作業であり取っつきやすいのがメリットですが、地味な手作業が多く、作業する曲線の数が多いと手間がかかるというデメリットがあります。

散布図グラフの作成

 近似曲線をあてはめたいデータを選択しグラフを作成します。グラフの種類は「散布図」を選択します。

散布図グラフの作成(Excel)

近似曲線の追加と係数取得

Excelの場合

 プロットを右クリックして「近似曲線の追加(R)」を選択します。

近似曲線の追加(Excel)

 次に近似曲線の書式設定を行います。二次関数もしくは二次分数関数の曲線をあてはめる場合、「多項式近似(P)」を選択し、「次数(D)」は「2」とします。一次関数(直線)の場合はそのままにします。「グラフに数式を表示する(E)」にチェックを入れることで、グラフ上に近似曲線の式が表示されます。

近似曲線の書式設定(Excel)

 表示された式から係数をコピーします。

近似曲線の係数のコピー(Excel)

 なお、近似曲線の式ラベルの表示形式を「指数」に設定し、さらに「小数点以下の桁数(D)」を2~3程度にすれば、係数が極端に小さい場合においてより正確な係数が得られます。指数表示は有理数rと整数nにより「rEn」の形で表記され、実際の数値は「r * 10n」となります。例えば、1.23E+01は12.3、1.23E-01は0.123といった値を表しています。

近似曲線の式ラベルの表示形式変更(Excel)

Google スプレッドシートの場合

 グラフの右上の編集メニューから「高度な編集...」を選択します。

Googleスプレッドシートのグラフ編集

 「カスタマイズ」タブの一番下にある、「トレンドライン」の選択リストから「線形」(直線の場合)もしくは「多項式」(二次関数もしくは二次分数関数の曲線の場合)を選択します。後者の場合は、次数を2に設定します。また、「ラベル」は「方程式を使用」を選択します。

近似曲線の追加(Googleスプレッドシート)

 右のプレビュー表示上に現れた式から係数をコピーします。凡例の文字列は通常はコピーすることができませんが、編集画面のプレビュー表示上では文字列を選択して「Ctrl + Vキー」を押すことでコピーすることができます。

近似曲線の係数のコピー(Googleスプレッドシート)

曲線のy座標を求める式の作成

 上記の関数にて求めた係数を用いて以下の式を作成し、曲線のy座標値を求めます。

関数
一次 =係数a*新規x+係数b
二次 =係数a*新規x^2+係数b*xの値+係数c
二次分数 =1/(係数a*新規x^2+係数b*新規x+係数c)

近似曲線のy座標値の計算

LINEST関数の利用

 表計算ソフトに実装されているLINEST関数などを利用することにより、データから直接近似曲線の式の係数を求め、得られた係数から曲線の式を作成し、最終的に規則的なx座標値を代入して曲線上のy座標値を求めます。少々取っつきにくいですが、表計算ソフトの取り扱いに習熟していれば手早く補間を行うことができます。Excel、Google スプレッドシート、LibreOffice Calcで動作を確認しています。

 LINEST関数の仕様についてはここでは詳しくは説明せず、実際の利用方法を紹介するに留めます。詳細を知りたい方は、以下のページを参考にしてみてください。

 なお、以下に掲載する式は値の選択範囲に空白セルが含まれるとエラーが発生するため、そのままでは使い勝手が良くありません。実用性を持たせるためにはCOUNT関数とOFFSET関数を組み合わせ、既知xと既知yの選択範囲をデータ個数に合わせて可変にするなどの工夫を行う必要があります。

係数を求める式の追加

 2つ以上の既知のxと既知のyの組にあてはめられる一次関数(直線)\[y = ax + b\]の各係数は、以下の式を使用することで得られます。SLOPE関数は直線の傾きを、INTERCEPTは切片を求める関数です。

係数
\(a\) =SLOPE(既知y,既知x)
\(b\) =INTERCEPT(既知y,既知x)

 3つ以上の既知のxと既知のyの組にあてはめられる二次関数\[y = ax^2 + bx + c\]の各係数は、以下の式を使用することで得られます。LINEST関数の出力は配列形式のため、INDEX関数で配列の個々の要素を取り出しています。

 以下の式のうち、中かっこ『{}』が含まれる式はExcelやGoogleスプレッドシートではそのまま入力してもエラーにはなりませんが、LibreOffice Calcでは入力確定時にCtrl + Shift + Enterキーの同時押しを行い配列数式として入力しないとエラーになりますのでご注意ください。LibreOffice Calcでは配列数式のオートフィルが行えず、少々使い勝手が悪いです。

係数
\(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)

 二次分数関数\[y = \frac{1}{ax^2 + bx + c}\]の各係数は、以下の式を使用することで得られます。

係数
\(a\) =INDEX(LINEST({1}/既知y,既知x^{1,2}),1,1)
\(b\) =INDEX(LINEST({1}/既知y,既知x^{1,2}),1,2)
\(c\) =INDEX(LINEST({1}/既知y,既知x^{1,2}),1,3)

曲線のy座標を求める式の作成

 上記の関数にて求めた係数を用いて以下の式を作成し、曲線のy座標値を求めます。

関数
一次 =係数a*新規x+係数b
二次 =係数a*新規x^2+係数b*新規x+係数c
二次分数 =1/(係数a*新規x^2+係数b*新規x+係数c)

TREND関数の利用

 TREND関数を利用すれば、いったん係数を求める必要がなく単一の関数で近似曲線のy座標値を求めることができます。以下の式のうち、中カッコ『{}』の付いた式はExcelはそのまま入力可能ですが、LibreOffice CalcとGoogleスプレッドシートでは入力確定時にCtrl + Shift + Enterキーの同時押しを行い配列数式として入力しないとエラーになります。既知の値の選択範囲に空白セルが含まれるとエラーを起こすのもLINEST関数と同様であり、使い勝手を良くするためには一工夫する必要があります。

関数
一次 =TREND(既知y,既知x,新規x)
二次 =TREND(既知y,既知x^{1,2},新規x^{1,2})
二次分数 =1/TREND({1}/既知y,既知x^{1,2},新規x^{1,2})