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