【先着1,000名様!】1,000円分をプレゼント!

エクセルを用いずに、近似曲線の数式をエクセルを用いずに算出したいです。

資料となるデータはあるし、計算機もあるのですが、エクセルは手元にないという状態です。

手計算で近似曲線のy=ax+bという数式を出すには、どのような計算をしたら算出できるのか、ご存じの方いらっしゃったら、どうか詳しく計算過程を教えて下さい。お願いします。

このQ&Aに関連する最新のQ&A

A 回答 (3件)

線形近似においては、最小二乗法による推定が最も優れている(不偏かつ分散が小さい)ことがガウス=マルコフ定理によって証明されています。


最小二乗法とはデータが(X1,Y1),(X2,Y2),・・・(Xi,Yi)のように示されていると思うので、そのもとで
Σ(Yi-aXi-b)^2   (i=1,2,3・・・)
を最小化するようなa,bの組を見つけるという方法です。a,bの偏微分によって求めます。
具体的な式としては、Xの平均をX'、Yの平均をY'とし、データ数(標本数)をnとすると、
a=(ΣXiYi-nX'Y')/(ΣXi^2-nX'^2), b=Y'-aX'   (i=1,2,3・・・)
を得ます。
なお、ΣXiYiというのはX1・Y1+X2・Y2+・・・を意味し、ΣXi^2とはX1^2+X2^2+・・・を意味します。

例えば(X,Y)=(10,6),(12,9),(14,10),(16,10)というデータを考えると、標本数は4、Xの平均は13、Yの平均は8.75、ΣXi^2=696、ΣXiYi=468なので、これを代入して
a=(468-4×13×8.75)/(696-4×13^2)=0.65, b=8.75-0.65×13=0.3となり、
求める式はy=0.65x+0.3となります。

この回答への補足

わかりやすいご回答ありがとうございあます。

少しだけわからなかったところがあったので、恐縮ながら質問させてください。

ΣXi^2=696、ΣXiYi=468とはどのような計算式なのでしょうか?。

補足日時:2011/08/01 12:55
    • good
    • 30

y=ax+bなら近似直線ですよね。


グラフにデータをプロットして、近似直線と思われる直線を描いて、その式を求めるのが簡単だと思いますが。
    • good
    • 6

二点の(x、y)の値を与えられると、式に代入して2元1次方程式を解くことで計算できます。

エクセルには関係ありません。
尚、このy=ax+bは直線の式です。
    • good
    • 2

このQ&Aに関連する人気のQ&A

お探しのQ&Aが見つからない時は、教えて!gooで質問しましょう!

このQ&Aを見た人はこんなQ&Aも見ています

このQ&Aを見た人が検索しているワード

このQ&Aと関連する良く見られている質問

Qエクセルの指数近似曲線の式から値を計算する

有るデータを元に指数近似曲線を描かせると「y = 2E+08e-0.098x R² = 0.9847」と出ました。
インターネット等を参考にしてエクセルで値を計算させようとして以下の式を作りましたが計算結果が全く元データと合いません、何が間違っているのでしょうか?教えて下さい。
y = 2E+08e-0.098x⇒y=2*POWER(10,8)*EXP((-0.098*x))

Aベストアンサー

質問者様はグラフに表示された数式と同じワークシートの数式を正しく作成されています。
(実際の数式ではyはなくxのところにセル参照が入っているのですよね)
元データと合わない原因ですが、グラフに表示された数式の係数の精度不足ではないでしょうか。
それが原因であれば、グラフに表示された数式の表示形式を変更して係数の精度を上げ、その数値でワークシートの数式を作り直せば多くの場合解決します。
どのような数値でも精度良く表示するためには、表示形式を指数にして小数点以下14桁にするとよいと思います。

ご存知かもしれませんが、以下念のためグラフに表示された数式の表示の変更方法を説明します。
Excel2003とExcel2007についての解説となりますが他のバージョンも大きくは違わないと思いますのでそこのところの対応はよろしくお願いいたします。
1.グラフに表示された数式を右クリック
2.2003なら「データラベルの書式設定」をクリック、2007なら「近似曲線ラベルの書式設定」を左クリックする。
3.書式設定のダイアログが出るので、「表示形式」を左クリック(2003は上、2007は左上。最初からクリックされた状態になっている可能性あり。)
4.「分類」(表示形式)の「指数」を左クリック。(「数値」はNG)
5.「小数点以下の桁数」を14にする。(最大30だが「分類」(表示形式)が「指数」ならば15以上は無意味。Excelの精度が15桁であるため。)
6.「OK」を左クリック

なお、質問者様の作成された式の「2*POWER(10,8)」の部分ですが、「2*10^8」「2E+08」「2e8」などとしても数式の値としては同じになります。

質問者様はグラフに表示された数式と同じワークシートの数式を正しく作成されています。
(実際の数式ではyはなくxのところにセル参照が入っているのですよね)
元データと合わない原因ですが、グラフに表示された数式の係数の精度不足ではないでしょうか。
それが原因であれば、グラフに表示された数式の表示形式を変更して係数の精度を上げ、その数値でワークシートの数式を作り直せば多くの場合解決します。
どのような数値でも精度良く表示するためには、表示形式を指数にして小数点以下14桁にするとよいと思...続きを読む

Qエクセルの関数による近似式の求め方

(1)1,2,3,4 (2)5,6,7,8
(1)と(2)の二つの数字があります。
(1)をx軸、(2)をy軸としてエクセルの関数の式のみでy=a*x^2+b*x+cを求める事は可能ですか?
現状は(1)(x軸)と(2)(y軸)からグラフを書き近似曲線を描かせ、2次の多項式を表示させているんですが、大量にデータがあり、この作業に大変時間を要しています。調べに調べた結果、y=a*x+bの形は関数で表示可能なことは確認取れているんですが、2次の式は未だ発見できません。
非常に困っています。回答の方よろしくお願いします。また何か不明な点があったら何でも言って下さい。

Aベストアンサー

y の範囲 A2:A10
x の範囲 B2:B10
として、
C2=B2^2
C10までコピー。

と、しておけば
係数 a =INDEX(LINEST(A2:A10,B2:C10),1)
係数 b =INDEX(LINEST(A2:A10,B2:C10),2)
定数 c =INDEX(LINEST(A2:A10,B2:C10),3)

QExcel近似曲線の仕組みについて

Excelグラフの近似曲線についての質問です。

業務でグラフを作成し、近似曲線を出す予定ですが、
Excelの近似曲線はどういった仕組みを使って書き込む方法がありますか?
当方は最小二乗法で書き込む方法しか存じ上げておりませんが、
それ以外にExcelグラフで近似曲線を求める方法はありますか?

業務上精度が悪いと使用できないため、もし他に近似曲線を出せる方法があれば
それも教えて頂きたいです。


よろしくお願いいたします。

Aベストアンサー

どういった近似をしたいのか不明なので,近似全般についてお話しします。

エクセルでは「最小二乗法」を使った近似と,他に「移動平均」を利用することも出来ます。
一般的な意味での「近似曲線」としては,定式化に応じて
・線形近似(直線近似)
・多項式の近似(二次以上の近似)
・指数近似
・対数近似
・累乗近似
などが出来ますが,いずれも最小二乗法を使っているので,基本的な計算方法や精度は同じです。
「グラフに近似曲線を追加する」だけなら,グラフを描いてプロットを右クリック,近似曲線の追加によって希望の近似曲線を追加します。
近似曲線のオプションで「R2」と「近似式」を表示させ,原則としてはR2の値が0.8以上であれば概ね信頼できる近似だと判定します。



>業務上精度が悪いと使用できない

しばしば寄せられるご相談ですが,グラフに表示させた近似曲線の式を「近似値の計算」に使ってはいけません。

「近似値を計算したい」が目的であれば,
・簡単な直線近似なら,FORECAST関数
・一次を含む多項式近似ならTREND関数
・指数近似ならGROWTH関数
を使います。

「近似式のパラメータ(近似係数)を得たい」のが目的であれば
・直線近似ならSLOPE関数とINTERCEPT関数
・多項式近似ならLINEST関数
・指数近似ならLOGEST関数
を使います。具体的な計算方法は,各関数のヘルプをよく読んで計算してください。



参考:
たとえば
A2:A11にXの数値
B2:B11にYの数値
があるとすると
C2に
=LN(A2)
D2に
=LN(B2)
を置いて11行までコピー

=SLOPE(B2:B11,A2:A11)と=INTERCEPT(B2:B11,A2:A11)で線形近似のパラメータ
=SLOPE(D2:D11,A2:A11)と=EXP(INTERCEPT(D2:D11,A2:A11))で指数近似のパラメータ
=SLOPE(B2:B11,C2:C11)と=INTERCEPT(B2:B11,C2:C11)で対数近似のパラメータ
=SLOPE(D2:D11,C2:C11)と=EXP(INTERCEPT(D2:D11,C2:C11))で累乗近似のパラメータ
をそれぞれ得ることも出来ます。


#補足
Excel2002までの近似曲線の計算式にはバグがあるので,近似の計算をしたいときにはExcel2003以降を利用する必要があります。
またExcel2003以降でも,グラフに表示させた近似式には計算間違いしたパラメータが現れる場合があります。


#既にオナカイッパイと思いますので,多項式近似の計算の仕方は下記などを参考に。
http://atiboh.sub.jp/t09takoushiki2.html

どういった近似をしたいのか不明なので,近似全般についてお話しします。

エクセルでは「最小二乗法」を使った近似と,他に「移動平均」を利用することも出来ます。
一般的な意味での「近似曲線」としては,定式化に応じて
・線形近似(直線近似)
・多項式の近似(二次以上の近似)
・指数近似
・対数近似
・累乗近似
などが出来ますが,いずれも最小二乗法を使っているので,基本的な計算方法や精度は同じです。
「グラフに近似曲線を追加する」だけなら,グラフを描いてプロットを右クリック,近似曲線の追加...続きを読む

Q線形近似の数学的求め方について

お世話になります。

各月の出荷実績から次月を予測することを実施しようと考えています。
EXCELの近似曲線の機能でやろうと思っていたのですが、
ひとつひとつを手作業で行わなければならないことが引っかかっています。

複数のサンプリング数値から
線形近似の
Y = aX + b
R^2
a
b
R
をそれぞれ数学的に求めることができないかと情報を探しております。

Aベストアンサー

数学的には以下のように求められるようです。
求める回帰直線を
y=ax+b
とおくと
a=[Σxy-nXY]/[Σx^2-nX^2]
b=Y-aX
ここで、x,yは添え字iを省略。
X,Yはx,yの平均値
nはデータ数

R=[Σ(x-X)(y-Y)]/[√Σ(x-X)^2√Σ(y-Y)^2]
R^2=R*R

Excelのヘルプにも載っていました。
近似曲線、LINESTをキーワードに探してみてください。

Qエクセルで計算すると2.43E-19などと表示される。Eとは何ですか?

よろしくお願いします。
エクセルの回帰分析をすると有意水準で2.43E-19などと表示されますが
Eとは何でしょうか?

また、回帰分析の数字の意味が良く分からないのですが、
皆さんは独学されましたか?それとも講座などをうけたのでしょうか?

回帰分析でR2(決定係数)しかみていないのですが
どうすれば回帰分析が分かるようになるのでしょうか?
本を読んだのですがいまいち難しくて分かりません。
教えてください。
よろしくお願いします。

Aベストアンサー

★回答
・最初に『回帰分析』をここで説明するのは少し大変なので『E』のみ説明します。
・回答者 No.1 ~ No.3 さんと同じく『指数表記』の『Exponent』ですよ。
・『指数』って分かりますか?
・10→1.0E+1(1.0×10の1乗)→×10倍
・100→1.0E+2(1.0×10の2乗)→×100倍
・1000→1.0E+3(1.0×10の3乗)→×1000倍
・0.1→1.0E-1(1.0×1/10の1乗)→×1/10倍→÷10
・0.01→1.0E-2(1.0×1/10の2乗)→×1/100倍→÷100
・0.001→1.0E-3(1.0×1/10の3乗)→×1/1000倍→÷1000
・になります。ようするに 10 を n 乗すると元の数字になるための指数表記のことですよ。
・よって、『2.43E-19』とは?
 2.43×1/(10の19乗)で、
 2.43×1/10000000000000000000となり、
 2.43×0.0000000000000000001だから、
 0.000000000000000000243という数値を意味します。

補足:
・E+数値は 10、100、1000 という大きい数を表します。
・E-数値は 0.1、0.01、0.001 という小さい数を表します。
・数学では『2.43×10』の次に、小さい数字で上に『19』と表示します。→http://ja.wikipedia.org/wiki/%E6%8C%87%E6%95%B0%E8%A1%A8%E8%A8%98
・最後に『回帰分析』とは何?下の『参考URL』をどうぞ。→『数学』カテゴリで質問してみては?

参考URL:http://ja.wikipedia.org/wiki/%E5%9B%9E%E5%B8%B0%E5%88%86%E6%9E%90

★回答
・最初に『回帰分析』をここで説明するのは少し大変なので『E』のみ説明します。
・回答者 No.1 ~ No.3 さんと同じく『指数表記』の『Exponent』ですよ。
・『指数』って分かりますか?
・10→1.0E+1(1.0×10の1乗)→×10倍
・100→1.0E+2(1.0×10の2乗)→×100倍
・1000→1.0E+3(1.0×10の3乗)→×1000倍
・0.1→1.0E-1(1.0×1/10の1乗)→×1/10倍→÷10
・0.01→1.0E-2(1.0×1/10の2乗)→×1/100倍→÷100
・0.001→1.0E-3(1.0×1/10の3乗)→×1/1000倍→÷1000
・になります。ようするに 10 を n 乗すると元の数字になるた...続きを読む

Q多項式近似

エクセルであるデータの散布図を作りました。
ばらつきがみられたので、近似曲線をどうしようかと思ったのですが、6次の多項式近似を行ったところ、プロットした点をほぼすべて正確に通る近似曲線がひけました。
その近似式の式は
y==3E-10*x^6 - 8E-08*x^5 + 8E-06*x^4 - 0.0004*x^3 + 0.0091*x^2 - 0.0695x + 0.3314
でした。
R^2も0.999でほとんど1だったので、このデータ(曲線)の式=近似曲線の式とみなしてもいいと思ったのですが、ダメなのでしょうか?

実際のデータではx=97.12561のときy=6.37なのですが、この式に代入するとy=-14になってしまいます。
なぜでしょうか?
E-10というのは10^(-10)ということで正しいですよね?

Aベストアンサー

#1さんも言われているように単なる各項の有効桁数の不足による桁落ち誤差ですね。
多項式の各項の係数が1桁では、計算精度も高々1桁ですね。
x=97.12 ... ≒100=10^2ですから
y≒3*10^2-8*10^2+8*10^2-4*10^2+0.91*10^2-0.0695*10^2+0.003314*10^2
≒(3-8+8-4+0.9-0.1+0.0)*10^2
≒(-1.1+0.9)*10^2=-0.2*10^2
この計算は100の位だけが有効桁で10の桁は誤差の範囲に含まれ、有効桁数0桁に桁落ちしているということです。

なので100の桁が有効な桁なので、y=6.37もy=-14も見かけだけの数値で、その値が100より1桁以上、下の桁なので数値的意味はありません。

正確にy=6.37まで計算するには係数の有効桁数を6桁与えて計算し最後の桁を四捨五入しないといけないですね。
従って、エクセルで与える有効桁数を6桁以上にして計算しないといけないですね。エクセルの中では有効桁数は15桁(表示指定しなければ表示されないだけ)なので計算はその精度でやっています。
その係数を、人が介在して、表示されている係数データ(有効桁数を表示していない)を使って、近似多項式を書き写して入力しなおす作業で、有効桁数1桁の近似式になってしまいます。

>R^2も0.999でほとんど1だった
これは、エクセル中の表示されていない正確なデータを使って計算した数値だからです。
エクセルの中で保持しているデータの数値と、デホルト設定(あるいはエクセル使用者が設定した桁数設定)の表示データとは異なりますので、表示データをそのまま他で使わないように注意しないといけないですね。

#1さんも言われているように単なる各項の有効桁数の不足による桁落ち誤差ですね。
多項式の各項の係数が1桁では、計算精度も高々1桁ですね。
x=97.12 ... ≒100=10^2ですから
y≒3*10^2-8*10^2+8*10^2-4*10^2+0.91*10^2-0.0695*10^2+0.003314*10^2
≒(3-8+8-4+0.9-0.1+0.0)*10^2
≒(-1.1+0.9)*10^2=-0.2*10^2
この計算は100の位だけが有効桁で10の桁は誤差の範囲に含まれ、有効桁数0桁に桁落ちしているということです。

なので100の桁が有効な桁なので、y=6.37もy=-14も見かけだけの数値で、その値が100よ...続きを読む

QExcelで近似曲線の数値をセルに反映させたい

エクセルでグラフを作って近似曲線を引いたときの間の数値をセル上に反映させたいのですが、方法はないのでしょうか。
例えば、
A B
10 0
11
12 5
13
14 
15
16 15
17
18

といったような表をAをX軸、BをY軸でグラフにすると、3点のプロットのみが表示されて、それに近似曲線を入れることができると思います。
この近似曲線のデータをセルの空白の部分にプロットしていきたいのですが、方法がわかりません。
よろしくお願いします。

Aベストアンサー

こんにちは
近似線はあくまで近似なので値が違うケースもあると
思うのですけど...
近似曲線の書式設定で「数式を表示する」で数式を
利用して埋めるしかないような気もします。
数式はy = 2.5x - 25
なので例えばC2に「=2.5*A2-25」として以下コピーする
と埋めるべき値がわかると思います。
外してるとは思いますが、参考まで。
では。

Qエクセル STDEVとSTDEVPの違い

エクセルの統計関数で標準偏差を求める時、STDEVとSTDEVPがあります。両者の違いが良くわかりません。
宜しかったら、恐縮ですが、以下の具体例で、『噛み砕いて』教えて下さい。
(例)
セルA1~A13に1~13の数字を入力、平均値=7、STDEVでは3.89444、STDEVPでは3.741657となります。
また、平均値7と各数字の差を取り、それを2乗し、総和を取る(182)、これをデータの個数13で割る(14)、この平方根を取ると3.741657となります。
では、STDEVとSTDEVPの違いは何なのでしょうか?統計のことは疎く、お手数ですが、サルにもわかるようご教授頂きたく、お願い致します。

Aベストアンサー

データが母集団そのものからとったか、標本データかで違います。また母集団そのものだったとしても(例えばクラス全員というような)、その背景にさらならる母集団(例えば学年全体)を想定して比較するような時もありますので、その場合は標本となります。
で標本データの時はSTDEVを使って、母集団の時はSTDEVPをつかうことになります。
公式の違いは分母がn-1(STDEV)かn(STDEVP)かの違いしかありません。まぁ感覚的に理解するなら、分母がn-1になるということはそれだけ結果が大きくなるわけで、つまりそれだけのりしろを多くもって推測に当たるというようなことになります。
AとBの違いがあるかないかという推測をする時、通常は標本同士の検証になるわけですので、偏差を余裕をもってわざとちょっと大きめに見るということで、それだけ確証の度合いを上げるというわけです。

Qエクセルで近似曲線の数式の読み方

エクセルでグラフの近似曲線を使用し、
その近似曲線の数式をつかいたいんですが、
数式の意味がわかりません。
EX)y =2E+06x4-1E+07x3+2E+07x2-2E+07x+5E+06
   y =241467x4-1E+06x3+2E+06x2-2E+06x+714567

「x4」とは、xの4乗ということはわかっているのですが、
「E」やピリオドのない「06」とはどういう意味でしょうか?

よろしかったらご教授ください。

Aベストアンサー

「E+06」とは、10の6乗という意味です。

「2E+06」は、2×10の6乗という意味で、200万ということになりますね。

Q指数近似曲線の計算方法について

x:10,20,30,40,50,60
y:10,30,70,170,490,1700
のように変化する場合、y=200のときのxの値が求めたいのですが、うまく求めることができません。

Excelでの解法もあるかと思うのですが、手計算での方法が知りたいです。

わかる方いらっしゃいましたら教えて下さい。

Aベストアンサー

手計算はちょっときついですが、xに対してyが指数的に増えるという
関係が認められるときは、前の方のように、xとlogyが直線的な関係
にあるとみて、まず通常の最小二乗法により、logy=ax+bという直線
の式を求めて、これを逆に指数で戻してやって、y=e^(ax+b)とするのが
良いと思います。実務でもたまにやります。


このQ&Aを見た人がよく見るQ&A