1次近似で、y=ax+bの近似式を求める場合、
傾きaは、SLOPE(yのデータ,xのデータ)
y切片bは、INTERCEPT(yのデータ,xのデータ)
で求めています。
そこでグラフを描いて近似式を表示する際に、グラフオプションでy切片=0にするとSLOPEで求めた傾きと違う値になってしまい悩んでいます。

y切片=0にした場合のy=aXのaを出す関数があるのでしょうか?どなたか教えて下さい。宜しくお願い致します。

A 回答 (3件)

原点を通る1次近似式y=axの傾きaは定義式から算出します。

原点を通る場合は、

a=Σxy/Σx^2

です。#2さんのデータを使うと、

x  y xy x^2
1  3  3 1
2  2  4 4
3  5  15 9
4  6  24 16
5  7  35 25

となり、xyの列の合計Σxy=81, x^2の列の合計Σx^2=55だから、
傾き a=Σxy/Σx^2=81/55=1.47
1次近似式 y=1.47x
となります。
Excelの「近似曲線の追加」で切片=0にして近似式を求めても、上式と同じ結果が出ます。
    • good
    • 1
この回答へのお礼

ご回答有難う御座いました。大変参考になりました。

お礼日時:2004/01/16 20:23

わたしも危うく引っかかりました。


>SLOPEで求めた傾きと違う値になってしまい悩んでいます
=SLOPE(A1:A5,B1:B5)とついしてしまいましたが、「逆にして」=SLOPE(B1:B5,A1:A5)とすると近似曲線の値と一致しました。
これと同じことをしていませんか。
(データ)A1:B5
13
22
35
46
57
(SLOPE)
=SLOPE(B1:B5,A1:A5)が1.2
=INTERCEPT(B1:B5,A1:A5)が1
グラフ近似直線が
y=1.2x+1
    • good
    • 0
この回答へのお礼

ご回答有難う御座いました。

お礼日時:2004/01/16 20:23

もっと単純に考えてみましょう。


線形にしたいんですよね?
LINEST関数を使います。

LINEST({1,9,5,7},{0,4,2,3}) = {2,1} となり、傾きが 2 で y 切片が 1 の直線となります。

では・・・
=LINEST({1,9,5,7},{0,4,2,3},FALSE)
どうですか?
    • good
    • 2
この回答へのお礼

ご回答有難う御座いました。

お礼日時:2004/01/16 20:24

お探しの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)

Qエクセルの関数を使って、2座標を通る1次関数等を求めたいです

皆様、はじめまして!

オタッキーな質問で恐縮ですが、どうぞよろしくお願いいたします。

では、さっそく参ります。

エクセル2003の関数を使って、2点(2つの座標)を通る1次関数を求め、任意のX軸の値を代入し、Yの値を求めたいと思っております。

株のシステムトレードで、エクセルを使って、投資システムの検証をするために、このような事をしようとしています。

理想を言えば、2点(2つの座標)から直接Yの値まで1発で求める関数があればよいのですが、エクセル関数の事典で調べてもなさそうでした。

皆様、どうぞよろしくお願いいたします。

Aベストアンサー

エクセルであればFORECAST関数で求めることが可能です
A列にxの値、B列にyの値を入力します(これが2点の座標です)
A1 1、B1 5
A2 3、B2 11

任意のセルに
=FORECAST(5,B1:B2,A1:A2)
と入力すればx=5の時のyの値(=17)を得ます。

反対にyの値からxを求めたいなら
=FORECAST(17,A1:A2,B1:B2)
でy=17にたいするx(=5)を求めることができます。

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エクセル 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エクセルで正確な傾き、切片の求め方

現在データ解析を行っています。
あるサンプルの抵抗値を測定しましてそのサンプルの長さを横軸、抵抗値を縦軸にとりその直線から傾きと切片を求めようとしています。
複数のサンプルを測定していまして抵抗値にばらつきがあり、ある長さの抵抗値が複数あるという状況です。
このような状況で正確な傾きと切片を求めるにはどういった処理を行えばいいでしょうか。単純に抵抗値の平均値をとり近似直線を引くというのは性格ではないですよね。

Aベストアンサー

> 値が複数ある場合も可能なのでしょうか。

可能です。
サンプルを測定すればばらつきが出るのは当然です。ですから,同じ長さでも抵抗値にばらつきが出るのは当然です。
今回の場合は,長さをx,抵抗値をyと考えて表を作り,それから計算すれば良いのです。長さがダブっても問題ありません。
#1のリンク先の最後には,Excelで最小二乗法を計算する方法が載っているリンク先がありますから,そのまま数値を変えて計算すれば良いと思います。(式の求め方など細かいところは読み飛ばしてもらって結論だけ使えばいいですが,最小二乗法で何をしたいのか,その原理は理解しておいてくださいね)

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エクセルの指数近似曲線の式から値を計算する

有るデータを元に指数近似曲線を描かせると「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エクセルによる近似(回帰)直線の切片0にした場合の計算方法

エクセルでグラフを描いて近似直線を設定して、
オプションで切片を零にした場合に傾きが変化します。
この計算(導出)方法が分かりません。
どなたか教えてください。

Aベストアンサー

最小二乗法は通常、

Y=ax+b+ε
ε=Y-ax-b
Σε^2=Σ(Y-ax-b)^2

を最小にするために用います。計算はこの式をa,bの関数と捉えて

S(a,b)=Σ(Y-ax-b)^2
∂S/∂a=0
∂S/∂b=0

の連立方程式を解くことになります。
この近似式を

Y=aX

と一変数の式にすると

S(a)=Σ(Y-aX)^2
dS/da=-2ΣX(Y-aX)=0
a=ΣXY/ΣX^2

つまり、各データのXとYを掛けた総和をX^2の総和で割ることになります。

それぞれのデータが例えばXがA1からA10,YがB1からB10に入っているなら

=SUMPRODUCT(B1:B10,A1:A10)/SUMSQ(A1:A10)

で計算してください。

Qエクセル、散布図でデータの一部のみの近似直線を書きたい

(1、5)、(2,8)、(3、16)、(4、25)、(5、37)というグラフをかきました。
ここでグラフのプロットは全てのデータについて表示されたままで、(3、16)、(4、25)、(5、37)だけについての近似直線を描き、式やR2値を表す方法は無いものでしょうか。
(1、5)、(2,8)というデータを消せば目的の式は得られるのですが、(1、5)、(2,8)というプロットをグラフに残したままにしたいのです。
どうぞよい知恵をお貸し下さい。

Aベストアンサー

1系列の一部のデータ範囲を対象に近似曲線を引くことは出来ないように思えます。便宜的な方法として以下が考えられます。お試しください。

■グラフの一部に近似曲線を追加する

全てのデータ範囲を選択する
|グラフウィザード 2/4 「グラフの元データ」|系列タブ|
系列1
 すでに全てのデータ範囲が対象となっている
系列2
 |追加|
 「Xの値」のボタンを押して後半のX値のセル範囲を選択する
 「Yの値」のボタンを押して後半のY値のセル範囲を選択する
グラフが作成される
全てのデータ範囲(系列1)と後半のデータ範囲(系列2)は重なっている
系列2へ近似曲線を追加する
 グラフ上、後半のデータ範囲の1要素を右クリック
 |近似曲線の追加|
 パターン・種類・オプションを指定する

■検討事項

・凡例・マーカー
無指定で系列に「系列1」・「系列2」という名前が付きます。同じ名前にすることは出来るようですが、系列2のみを消すことは出来ないようです。系列名の色を白にして見えなくする、プロットエリアのマーカーも二系列を同色とする、など考えられます。

・近似線
私は近似曲線のオプションに詳しくありませんが、全てのデータ範囲に対する近似線を引いたとして、後半のデータ範囲に対する近似線と重ならない(同形ではない)と思います。

1系列の一部のデータ範囲を対象に近似曲線を引くことは出来ないように思えます。便宜的な方法として以下が考えられます。お試しください。

■グラフの一部に近似曲線を追加する

全てのデータ範囲を選択する
|グラフウィザード 2/4 「グラフの元データ」|系列タブ|
系列1
 すでに全てのデータ範囲が対象となっている
系列2
 |追加|
 「Xの値」のボタンを押して後半のX値のセル範囲を選択する
 「Yの値」のボタンを押して後半のY値のセル範囲を選択する
グラフが作成される
全てのデ...続きを読む

Qエクセルで、グラフ上の近似曲線の係数を参照元にすることはできますか。

エクセル2000を使っています。

エクセルのグラフには、近似曲線を表示する機能がありますが、
その近似曲線の係数を参照元にして、セル上の関数計算をしたいと
思っています。

例えば、線形近似ならば、

 y= ○○○x + ●●●

という式をグラフ上に表示できるわけですが、この係数の
○○○や●●●を他のセルから参照したいんです。

グラフの元になる数値を変更した場合、リンクして変わる
ようにしたいので、手入力の転記では困るのです。

もし可能でしたら、ご教授ください。

Aベストアンサー

グラフから参照することは(おそらく)できませんが,○○○はSLOPEというワークシート関数,●●●はINTERCEPTというワークシート関数から求めることができます.
それぞれ
=SLOPE(y軸のデータ範囲,x軸のデータ範囲)
=INTERCEPT(y軸のデータ範囲,x軸のデータ範囲)
で適当なセルに出力して,それを参照すればよろしいかと思います.

どちらの関数も未入力のデータ範囲は無視してくれますので,データ数が変わるのであれば,データの範囲を少し広めに指定しておけば,データの変更にも耐えられます.


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

人気Q&Aランキング

おすすめ情報