エクセルで二次の最小二乗法をやるやり方を教えてください。

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

A 回答 (1件)

多項式近似で各係数を出したいならLINEST関数を使います。



以下のページの作業列を使用しないで2次式の係数a,b,cを求める方法が最も簡単な操作になります。

http://atiboh.sub.jp/t09takoushiki2.html
    • good
    • 0

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

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

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

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

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

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エクセル 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近似曲線の数式を手計算で出したい。

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

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

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

Aベストアンサー

線形近似においては、最小二乗法による推定が最も優れている(不偏かつ分散が小さい)ことがガウス=マルコフ定理によって証明されています。
最小二乗法とはデータが(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となります。

線形近似においては、最小二乗法による推定が最も優れている(不偏かつ分散が小さい)ことがガウス=マルコフ定理によって証明されています。
最小二乗法とはデータが(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...続きを読む

Qエクセル 0や空白のセルをグラフに反映させない方法

以下の点でどなたかお教えください。

H18.1~H20.12までの毎月の売上高を表に記載し、その表を元にグラフを作成しています。グラフに反映させる表の範囲はH18.1~H20.12の全てです。
そのためまだ経過していない期間のセルが空白になり、そこがグラフに反映され見づらくなります。
データを入力する都度グラフの範囲を変更すればいいのですが、うまく算式や設定等で空白や0円となっているセルをグラフに反映させない方法はありますか?

お手数ですが、よろしくお願いいたします。

Aベストアンサー

売上高のセルは数式で求められているのですよね?
それなら
=IF(現在の数式=0,NA(),現在の数式)
としてみてください。
つまり、0の場合はN/Aエラーにしてしまうんです。N/Aエラーはグラフに反映されません。

Q3次元の近似直線

こんにちは。2次元で実験データなどの点列から近似直線を求めるのは、最小二乗法の基本問題ですが、3次元の点群から直線の方程式(x-x0)/a=(y-y0)/b=(z-z0)/cを求めるにはどんなアルゴリズムを使いますか?スマートな方法があれば教えていただけたら幸いです。よろしくお願いします。

Aベストアンサー

3次元空間の曲面ではなく、直線に乗ると仰るのだから、
(1) x, y, zのどれかを与えて、残りの2つを推定する問題。
(2) <x[i],y[i],z[i]>と直線との距離d[i]の二乗和が最小になる直線を求める問題。
と分類すべきでしょう。

(1)の場合は、たとえばzを与えてx,yを求めたいのであれば、
・zからxを求める問題。
・zからyを求める問題。
の二つを別々に解けばおしまいです。
それぞれの解は(x=Az+B, yは任意)という平面と、(y=Cz+D, xは任意)という平面を定めますから、この二つの平面の交線が、求める直線ということですね。

(2)の場合はやっかいです。
[1]ちょっと手抜きしながらも、まともにやってみましょう。
(i) 直線をどう表すか。
ご質問の式を見ると、この直線はx軸、y軸、z軸のどれとも平行でも垂直でもないことが仮定されています。
ですから、zをパラメータとして
x=az+c
y=bz+d
と書いても良いでしょう。a,b,c,dが決められれば良い訳です。
(ii) 点<p,q,r>と直線との最短距離を求める。
直線上の任意の点<az+c,bz+d, z>と点<p,q,r>の距離をdとすると
d^2 = (az+c-p)^2+(bz+d-q)^2+(z-r)^2
= (az)^2+2az(c-p)+(c-p)^2+(bz)^2+2bz(d-q)+(d-q)^2+z^2-2rz+r^2
です。これが最小になるzを求めると、
0=∂(d^2)/∂z = 2(az+c-p)a+2(bz+d-q)b+2(z-r)
ゆえに
z=(ap+bq+r-ac-bd)/(a^2+b^2+1)
であって、このときの最短距離h(p,q,r)は
h(p,q,r)^2 = (ap+bq+r-ac-bd)^2/(a^2+b^2+1)+2(ap+bq+r-ac-bd)(ac-ap+bd-bq-r)/(a^2+b^2+1)+(c-p)^2+(d-q)^2+r^2
わあ、とんでもないですね。
(iii) じゃあ、直線を求めるには?
S=Σ(h(x[i],y[i],z[i]))^2  (i=1,2,...,N)
を最小化するには
∂S/∂a = 0
∂S/∂b = 0
∂S/∂c = 0
∂S/∂d = 0
を解く必要があります。言い換えれば
∂(h(x[i],y[i],z[i]))/∂a
∂(h(x[i],y[i],z[i]))/∂b
∂(h(x[i],y[i],z[i]))/∂c
∂(h(x[i],y[i],z[i]))/∂d
を求めておいて
Σh(x[i],y[i],z[i]) (∂(h(x[i],y[i],z[i]))/∂a)=0
Σh(x[i],y[i],z[i]) (∂(h(x[i],y[i],z[i]))/∂b)=0
Σh(x[i],y[i],z[i]) (∂(h(x[i],y[i],z[i]))/∂c)=0
Σh(x[i],y[i],z[i]) (∂(h(x[i],y[i],z[i]))/∂d)=0
という連立方程式を解くことになります。
これがa,b,c,dについて非線形である(一次式でない)ことは言うまでもありません。一筋縄では行かず、反復計算で徐々に収束させていくしかありません。

[2]手抜き
もうすこし手抜きの方法を考えてみましょう。
この座標系を回転・平行移動した座標系をX-Y-Zとします。そして、求めたい直線がZ軸と一致するようにしたとします。回転と平行移動は行列を使って
X = R x + p
Y     y   q
Z     z   r
と表せます。Rは3×3の行列で Rの転置をR'とすると RR' = R' R = 単位行列
となる行列です。各点<x[i],y[i],z[i]>をこの変換で<X[i],Y[i],Z[i]>に写したとすると、
直線、すなわちZ軸との最短距離はX[i]^2 + Y[i]^2ですから、他のどんな回転・平行移動の仕方に比べても
U=Σ(X[i]^2 + Y[i]^2)  (i=1,2,....,N)
が最小になっている筈で、しかも
S=U
です。
 さて、UはZ[i]の値とは無関係ですからZ[i]を求める必要はない。さらに座標系をZ軸の周りで回転してもUは変化しません。従って、
X = R x + p
Y     y   q
      z
R =P(α)Q(β)
P(α)=cosα  0  -sinα
       0   1    0
Q(β)= 1  0     0
      0 cosβ -sinβ
      0 sinβ  cosβ
とすれば良いのです。展開すれば
X[i] = x[i]cosα-y[i]sinαsinβ-z[i]sinαcosβ+p
Y[i] = y[i]cosβ-z[i]sinβ+q
ですね。
ここでα、β、p、qを決めたい訳です。

 始めに(1)の問題を解けば、α、β、p、qの大体の値を求めることができます。これを使ってU(α,β,p,q)を計算します。
 それから、U(α,β,p,q)が小さくなるようにα、β、p、qをちょっとずつ改良して行けば良いでしょう。これには微小な角度Δα、Δβを使って、
P(Δα)=cosΔα  0  -sinΔα
        0    1    0
      sinΔα  0   cosΔα
Q(Δβ)= 1  0      0
       0 cosΔβ -sinΔβ
       0 sinΔβ  cosΔβ
を作り、P(α)、Q(α)にそれぞれ掛け算すれば良い。
P(α+Δα)=P(Δα)P(α)
Q(β+Δβ)=Q(Δβ)Q(β)
だからです。さらにここで、Δα、Δβは微小だから、
cosΔα≒1、cosΔβ≒1、sinΔα≒Δα、sinΔβ≒Δβ
(Δα)^2≒0、(Δβ)^2≒0、ΔαΔβ≒0
という近似をしても構わないでしょう。
この近似を利用すると計算は一層簡単になり、Uを最小にするようにΔα、Δβ、p、qを求める問題は線形最小二乗法(一次式の最小二乗法)になってしまい、簡単に解けます。
それを解いてから、真面目にP(α)、Q(α)を計算しなおし、また線形最小二乗法を解く。これを収束するまで繰り返せば良いのです。

なお、stomachmanは計算間違いの常習犯ですから、チェックは慎重に。

3次元空間の曲面ではなく、直線に乗ると仰るのだから、
(1) x, y, zのどれかを与えて、残りの2つを推定する問題。
(2) <x[i],y[i],z[i]>と直線との距離d[i]の二乗和が最小になる直線を求める問題。
と分類すべきでしょう。

(1)の場合は、たとえばzを与えてx,yを求めたいのであれば、
・zからxを求める問題。
・zからyを求める問題。
の二つを別々に解けばおしまいです。
それぞれの解は(x=Az+B, yは任意)という平面と、(y=Cz+D, xは任意)という平面を定めますから、この二つの平面の交線が、求め...続きを読む

Q波長(nm)をエネルギー(ev)に変換する式は?

波長(nm)をエネルギー(ev)に変換する式を知っていたら是非とも教えて欲しいのですが。
どうぞよろしくお願いいたします。

Aベストアンサー

No1 の回答の式より
 E = hc/λ[J]
   = hc/eλ[eV]
となります。
波長が nm 単位なら E = hc×10^9/eλ です。
あとは、
 h = 6.626*10^-34[J・s]
 e = 1.602*10^-19[C]
 c = 2.998*10^8[m/s]
などの値より、
 E≒1240/λ[eV]
となります。

>例えば540nmでは2.33eVになると論文には書いてあるのですが
>合っているのでしょうか?
λに 540[nm] を代入すると
 E = 1240/540 = 2.30[eV]
でちょっとずれてます。
式はあっているはずです。

Qエクセルで2次関数の計算

y=aX^2+bX+cという2次関数でYの値を入れてXを出す計算は可能なのでしょうか?
また、1次関数y=aX+bでaを求める関数がslope(範囲A,範囲B)のように、2次関数のa,bを求める関数があるのでしょうか?大変困っているので宜しくお願いします。

Aベストアンサー

=(-C9-SQRT(C9*C9-4*C8*(C10-C11)))/2/C8
=(-C9+SQRT(C9*C9-4*C8*(C10-C11)))/2/C8

C8にa
C9にb
C10にc
C11にY
の値を入れる

Qエクセルの多項式近似について

エクセルのグラフの機能で、多項式近似というものがあるんですが、この多項式近似に使用されている、数値解析の考え方はなんでしょうか?
最小二乗法が使われているのでしょうか?
ご存知の方お教え下さい。

Aベストアンサー

ヘルプで「近似曲線」などを調べてみると、

| この分析方法は、回帰分析とも呼ばれます。

との事で、一般的に回帰分析と呼ばれる方法が利用されます。

--
Excelでの回帰分析に関して、以下のページが参考になります。1次式でしか使った事無かったですが、なるほど。

マック、まんが、アニメのページ? - Excelでn次多項式の係数を求めたい【Mac/Windows】
http://www.amy.hi-ho.ne.jp/fujisawa/mac/tips/excel_lesq.html

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&Aを見た人がよく見るQ&A

人気Q&Aランキング

おすすめ情報