「Excel グラフウィザードの近似曲線(累乗近似)」は、Excelが提供する便利な機能の一つです。
累乗近似の式「 y = ax^b 」(Excelではy=cx^b)の式です。
累乗近似とは、データが曲線的に増加するとき(加速度的に増加するデータの分析に適しています。) Excelグラフウィザード「近似曲線の書式設定」→「種類」→「近似または回帰の種類」で、データに「0」又はマイナスの値が含まれている場合は、累乗近似を作成できません。
Excel関数で自然対数を返すLN関数を使用して、変数変換を行うことにより形状を直線に近づける手法ををとりますと、線形近似(直線)で使用するExcel関数が使用できます。
(1)データは、A1セルからB8セルに入力されています。(下記の図)
(2)累乗近似の式 y = ax^b (Excelではy=cx^b) から定数「a」、「b」の値を求めます。
「b」を求める関数としてSLOPE関数、LN関数を使用します。
「b」の値は1.0942です。
「a」を求める関数としてEXP関数、INTERCEPT関数及びLN関数を使用します。
「a」の値は11.849です。
(3)決定係数R2乗を求めます。
決定係数R2乗を求める関数としてRSQ関数とLN関数を使用します。
また、CORREL関数を使用してCORREL関数の2乗でも求められます。
決定係数R2乗の値はどちらも0.9744です。
(4)グラフで確認
グラフウィザードを起動し、散布図を作成します。(作成方法は省略します。)
プロットを右クリックして「近似曲線を追加」をクリック
「近似曲線を追加」ダイアログボックスから「累乗近似」を選択します。
「オプション」をクリック「グラフに数式を表示する」及び「グラフにR-2乗値を表示する」にﾚ点を入れます。
式及び決定係数が表示されます。
(5)LINEST関数とLN関数を使用して求める方法があります。
「b」を求める関数としてINDEX関数、LINEST関数、LN関数を使用します。
「b」の値は1.0942です。
「a」を求める関数として、EXP関数、INDEX関数、LINEST関数、LN関数を使用します。
「a」の値は11.849です。
(6)決定係数R2乗を求めます。
決定係数R2乗を求める関数としてINDEX関数、LINEST関数、LN関数を使用します。
決定係数R2乗の値は0.9744です。
(7)LN関数代わりに、LOG関数を使用して求めます。
「b」を求める関数としてSLOPE関数、LOG関数を使用します。
「b」の値は1.0942です。
「a」を求める関数としてPOWER関数、INTERCEPT関数及びLOG関数を使用します。
POWER関数を使用しない場合は「=10^INTERCEPT(LOG(B2:B8),LOG(A2:A8))」です。
「a」の値は11.849です。
(8)決定係数R2乗を求めます。
決定係数R2乗を求める関数としてRSQ関数、LOG関数を使用します。
決定係数R2乗の値は0.9744です。
(9)LINEST関数とLOG関数を使用して求める方法があります。
「b」を求める関数としてINDEX関数、LINEST関数、LOG関数を使用します。
「b」の値は1.0942です。
「a」を求める関数として、POWER関数、INDEX関数、LINEST関数、LOG関数を使用します。
POWER関数を使用しない場合は「=10,INDEX(LINEST(LOG(B2:B8),LOG(A2:A8)),2)」です。
「a」の値は11.849です。
(10)決定係数R2乗を求めます。
決定係数R2乗を求める関数としてINDEX関数、LINEST関数、LOG関数を使用します。
決定係数R2乗の値は0.9744です。
(11)LINEST関数が返す配列でも求められます
LINEST関数の式{=LINEST(LN(B2:B8),LN(A2:A8),,1)}
B11セル〜C15セルを選択し、「=LINEST(LN(B2:B8),LN(A2:A8),,1」と入力
配列数式ですので、式を入力後「Shift」キー+「Ctrl」キーを押しながら「OK」ボタンを押すか、「Enter」キーを押してください。
定数「a」を求めるために、D11セルに=EXP(C11)と入力します。
連立方程式で累乗近似の定数(a、b)を求めます。
(12).連立方程式で累乗近似の定数(a、b)を求めるには、作業列が必要です。
C2セルに「=LN(A2)」と入力。C3セル〜C8セルにオートフィルでコピー
D2セルに「=C2^2」と入力。D3セル〜D8セルにオートフィルでコピー
E2セルに「=LN(B2)」と入力。E3セル〜E8セルにオートフィルでコピー
F2セルに「=C2*E2」と入力。F3セル〜F8セルにオートフィルでコピー
C9セルに「=SUM(C2:C8)」と入力。D9セル〜F9セルにオートフィルでコピー
(13).MINVERSE関数を使用しますので、配列には行数と列数が等しい数値配列(正方行列)します。
A11セルには、データ数を入力します。「=COUNT(A2:A8)」と入力
B11セルに「=C9」と入力
A12セルに「=C9」と入力
B12セルに「=D9」と入力
D11セルに「a」と入力
D12セルに「b」と入力
E11セルに「=E9」と入力
E12セルに「=F9」と入力
(14).MINVERSE関数で計算します。
A14セル〜B15セルを選択し、A14セルに「=MINVERSE(A11:B12)」と入力し、
配列数式ですので、式を入力後「Shift」キー+「Ctrl」キーを押しながら「OK」ボタンを押すか、「Enter」キーを押してください。
MINVERSE関数で求めた値が表示されます。
(15).定数(a、b)を求めるためにMMULT関数を使用します。
B17セル〜B18セルを選択し、B17セルに「=MMULT(A14:B15,E11:E12)」と入力し、
配列数式ですので、式を入力後「Shift」キー+「Ctrl」キーを押しながら「OK」ボタンを押すか、「Enter」キーを押してください。
下記の図のように表示され、定数a、及びbの値が求められますが、「※ グリコのおまけ4」でLINEST関数が返す配列で求めた値と同じ値です。
そのため、定数「a」を求めるために、C17セルに「=EXP(B17)」と入力します。
(16).予測値y '
累乗近似の予測値y ' を、SERIESSUM関数を使用しても求められます。<統計編>→最適な近似曲線を選ぶ
(part2) にありますので参考に！
<使用した関数>
・SLOPE(即知のy,即知のx)[スロープ] ： 回帰直線の傾きを返します。
・LN(数値)[ログ･ナチュラル]数値の自然対数を返します。数値には自然対数を求めたい、正の実数値を指定します。（EXPは、LNの逆関数になる）
・EXP(数値)[エクスポネンシャル]eを指定された数乗した数値を返します。数値には底eに対する指数を指定します。（指数関数。LNは、EXPの逆関数になる）
・INTERCEPT(即知のy,即知のx)[インターセフト] ： 回帰直線の切片の値を返します。
・RSQ(既知のy,既知のx)[スクエア･オブ･コリレーション] ： ピアソンの積率相関係数を2
乗した値を返します。
・CORREL(配列1,配列2)[コーレル] ： 2 つのデータ間の相関係数を返します。
・INDEX(配列,行番号,列番号[領域番号])[インデックス] ： セル範囲の中で、指定した行番号、列番号にあるセル内容を返します。
・LINEST(既知のy,既知のx,定数,補正)[ラインエスト]線形トレンドのパラメータを返します。
・POWER(数値,指数)[パワー]数値を累乗した値を返します。
・LOG(数値,[底])[ログ]指定した底に基づく引数の対数値を返します。数値には対数を求めたい。正の実数を指定>底を省略すると、10
を指定したと見なされます。
・MINVERSE(配列)[マトリックス･インバース]配列の逆行列を返します。配列には行数と列数が等しい数値配列(正方行列)を指定
・MMULT(配列1,配列2)[マトリックス･マルチプリケーション]2つの配列の積を求めます。