• ベストアンサー
  • 困ってます

グラフの直線近似式の傾きと切片をとりだす

お世話になります。 グラフに直線近似式を出させています。 その中の傾きと切片を取り出す為SLOPE関数とINTERCEPT関数を用いていますが うまくできません。 A B 1 1 2 2 3 #N/A 4 4 5 #N/A 6 6 というデータについて傾きと切片を抜き出したいのですが#N/Aがある為うまくできません なんとかうまくできないでしょうか? お教え下さい。

共感・応援の気持ちを伝えよう!

  • 回答数4
  • 閲覧数2323
  • ありがとう数1

質問者が選んだベストアンサー

  • ベストアンサー
  • 回答No.2
  • keithin
  • ベストアンサー率66% (5278/7939)

=SLOPE(IF(ISERROR(B1:B6),"",B1:B6),A1:A6) をコントロールキーとシフトキーを押しながらEnter =INTERCEPT(IF(ISERROR(B1:B6),"",B1:B6),A1:A6) をコントロールキーとシフトキーを押しながらEnter

共感・感謝の気持ちを伝えよう!

質問者からのお礼

ありがとうございます。 言われていた方法で完璧でした。 ISERROR関数で配列数式も対応できるとは知りませんでした。 x、y両方に#N/Aが入る場合でも無事対応できました。

関連するQ&A

  • エクセルで1次近似式の傾きについて

    1次近似で、y=ax+bの近似式を求める場合、 傾きaは、SLOPE(yのデータ,xのデータ) y切片bは、INTERCEPT(yのデータ,xのデータ) で求めています。 そこでグラフを描いて近似式を表示する際に、グラフオプションでy切片=0にするとSLOPEで求めた傾きと違う値になってしまい悩んでいます。 y切片=0にした場合のy=aXのaを出す関数があるのでしょうか?どなたか教えて下さい。宜しくお願い致します。

  • 近似直線の傾きとSlope関数で求めた傾きについて

    近似直線の傾きとSlope関数で求めた傾きについて Excelでxとyについてのプロットデータに近似直線を追加しグラフにしました。そのときグラフ上にy=aX+bのような近似式を表示することができます。 ことのきの近似式の傾きaと、同じプロット点を使いSlope関数で求めたときの傾きとは別物なのでしょうか。同じになると思っていたのですが、同じ値にならないもので・・・。

  • 両対数グラフの傾きと切片から近似式を求めたい

    今晩は。よろしくお願いします。 カテゴリー違いでしたらごめんなさい。 あるサイトにあった以下の数値の近似式と対数グラフを エクセルを用いて自分で求めてみました。 X      Y 1.30E+15    3.5 4.8E+15    1 1E+16    0.5 まず、この値を用いてエクセルで両対数グラフを描きました。 次に、累乗近似の近似曲線を描き、その数式を表示させました。 y = 9E+14x-0.95 となりました。 これは y=9・10^(14・x^(-0.95)) ということでいいのでしょうか? また上式は上記の3点を通る近似式としてよいのでしょうか? でも何度検算しても   x=1.30E+15 y=3.5 が求まりません。 3.5=9・10^(14・1.30E+15^(-0.95)) という式がそもそも間違っているのでしょうか? 別の方法で、この類似曲線をほぼ直線として  この直線の傾きと切片(Y=AX+B)を用いれば、       y=ax^n(n=A  B=loga)  を近似式として求めてもいいんですよね?  (x、y)=(1.30E+15 、 3.5) (1E+16 、 0.5)の2点から  傾きをA=-3/(8.7E+15)=-0.34482E-15  切片をB=3.5  としました。  このとき  y=(10^3.5)・x^-334E-18    としてよいのでしょうか。  また、検算をする際、上式に(1.30E+15、3.5)を代入する際は (X、Y)になるのでしょうか?それとも(x、y)ですか?   X=logx Y=logy を考えないといけないのでしょうか 文章が下手ですみません。 どうぞアドバイスをください。

その他の回答 (3)

  • 回答No.4

No.1です。補足に回答いたします。 私がNo.1で書いたのは、エラーの出ているセルを空白にするだけでなく、そのレコード全体を削除するという案ですので、よくお読みください。したがって、No.2さんが書かれている式と同じ結果が出るかと思います。 ただ、No.1でも言ったとおり、エラーが出ているデータを除くのがいいのか、それとも他の対応方法がいいのか、回答者には何とも分からないので、先にそこを検討されたほうがよいと思いました。

共感・感謝の気持ちを伝えよう!

  • 回答No.3

No1の方の意見に賛成です。 エラーのある行を無視した関数を考えるより B列の関数でエラーが出ないように完了すべきですね。 B列の関数を =IF(ISERROR(今の関数),"",今の関数) と変更してみてください。

共感・感謝の気持ちを伝えよう!

質問者からの補足

すみません。 もともと#N/Aにしているのは関数のもとの参照範囲に数値が入っていないとき 0や""とするとグラフがおかしくなるからです。 ""にするようにすると肝心のグラフのほうがおかしくなります。 ・""=0と認識することで散布図の線グラフがおかしくなる ・同様に近似曲線もおかしくなる。 おそらく間違ってないと思いますが確認してみます。 グラフ上もワークシート上も同様に表示させたいと思っています。

  • 回答No.1

具体的な加工の方法の前に、エラーの出ているデータの扱いをどうすべきかが問題だと思います。エラーのレコードは除いたほうがいいのでしょうか? そういうことならば、次の手順で、件数が多くても、エラーのレコードのみ削除できます。計算結果を値のみ別の場所にコピペし、置換で「#N/A」を空白とし、「ホーム>検索と選択>ジャンプ(Ctrl+G)>セル選択>空白セル」で空白セルのみ選択し、「削除>行全体」でそのレコードのみ消します。 あと、もしも図に直線が引けさえすればよいということなら、散布図でプロットを右クリックし、「近似曲線の追加」により直線を表示させ、「近似曲線の書式設定」の中の「グラフに数式を表示する」をチェックしておけば直線の式が図に載るので(最小2乗法)、傾き・切片も分かります。線を引くだけが目的なら関数を用いるまでもないことになりますね。

共感・感謝の気持ちを伝えよう!

質問者からの補足

言葉足らずですみません。 グラフ上に直線近似式を出すのはわかるのですがそれを利用したいときに いちいち直線近似式を読み取って入力しなければならなかったのでそれを解消したかったのです。 ちなみに#N/Aにしているのは""にするとグラフでは0と認識されて グラフ上の直線近似もおかしくなるからです。 グラフ上にも表示でき、実際その近似式をワークシート上でも算出できる方法だったのは No2の方でした。ただ早い回答ありがとうございました。

関連するQ&A

  • 両対数グラフでの直線の傾きと切片の求め方

    両対数グラフに書かれた直線の傾きと切片を求めたいのですが、どうしたら良いのでしょうか。

  • エクセルの近似曲線(直線)

    いつもお世話になっております。 ある実験での測定結果をグラフにしたいのですが困っています。 測定は二回行ったので、一回目と二回目それぞれの近似曲線が記入可能でした。しかし、どうしても、グラフ上に、二本近似曲線がひかれてしまいます。それを一本にしたいのですが可能でしょうか。 データはおよそこんな感じです。 一回目→(x=1.3のときy=1540)     (x=0.7のときy=840)         (x=0.3のときy=600)     (x=0.2のときy=250) 二回目→(x=1.3のときy=2300)     (x=0.7のときy=680)         (x=0.3のときy=630)     (x=0.2のときy=260) 散布図で、8点がグラフに描かれますが、8つのポイントのぶれ具合を考慮したうえで、一本の近似曲線(直線の一次関数)を引くことが出来ますでしょうか? 例えばxが1.3のときy=1540と2300なので、大きくぶれが生じます。単にxが1.3、0.7、0.3、0.2のときの2つのy測定結果を足して平均するだけでは、グラフの傾きが正常より上がりすぎ困ります。 説明が下手ですいません。 とにかく、複数のxに対するyの値がそれぞれ2つある場合、そのそれぞれの点の真ん中らへんを通る直線が引きたいのです(そのとき直線は大きく外れたデータのポイント付近に存在しないようにする)。 よろしくお願いします! 一回目データと二回目のデーターから得られるそれぞれの近似曲線(一次関数)の傾きと切片の平均からなら一本の直線が求められるなど、どんな些細なことでもいいのでお願いします。

  • Excel内で傾きを変えて直線近似したい

    Excel内で,傾きを変えて直線近似をしたいです. たとえば,セルA1からA5に数字の0から4をX座標として入力します. セルB1からB5にはY座標として0から2,4,6,8と入力します. この直線では,当然傾きが2なのですが, この直線をあえて傾きが1になるように直線近似して,その際の切片を求めるなんて やり方ありませんか. 実験の解析で使うのですが・・・. エクセルでの計算に詳しい方,わかる方がいらっしゃいましたら回答よろしくお願いします.

  • 回帰直線の傾きと切片の誤差

     エクセルで散布図を作成した後、近似直線(y=ax+b)を作りました。R-2値は0.999以上で非常に高い相関がみられたのですが、この直線の傾きと切片はどの程度の誤差を有しているのか知りたいです。傾きaと切片bの誤差の導き方を教えてください (それぞれの誤差をc,dとすると、a±c, b±dのような表現の仕方がしたいと思ってます)。よろしくお願いいたします。

  • 傾きと切片について?

     2点(1,2)(3,8)を通る直線のグラフの傾きとY切片はいくらですか?

  • エクセルによる近似(回帰)直線の切片0にした場合の計算方法

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

  • エクセルで正確な傾き、切片の求め方

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

  • 片対数グラフと傾き・切片の出し方

    片対数グラフと傾き・切片の出し方 大学の物理実験の前に、準備としてグラフの書き方を教わっています。 そこでの練習問題なんですが、V=V0e^-αtという式があり、いくつかの数値と計測回数が書いてあります。 グラフへの記入はできるのですが、傾きと切片(V0)の出し方がいまいちわかりません。 両辺を自然対数に変換後グラフに記入する(使う数値は同じ)問題も、記入はできても傾き・切片の出し方がわかりません。 とくに自然対数グラフからどうやって傾き、切片を導くのかが(lnで出てきた数値をどうやって元に戻すのか)どうなっているのかわかりません・・・・。 どなたか詳しい方教えていただけませんか?

  • 検量線作成時の近似直線に関して

    実験データで検量線を作成しているのですがexcelで x・・・0、0.25、0.5、1.0 y・・・0.0025、0.0605、0.1240、0.2420 という数値を用いて散布図グラフを作り、近似直線の追加で直線の式やR^2を表示させると式がy=0.2431xとなり、切片(y=ax+b)のb部分が出てきません。原点は通っていないのに何度やっても切片が表示されないのでなぜ表示されないのかがわかりません。 ちなみにバージョンは2003です。 よろしくお願いします。

  • 最小二乗法の傾きと切片について

    あるデータの直線関係を最小二乗法を用いて求めたのですが、 傾きaと切片bの値ではどちらが信頼性があるかを一般的に 説明したいのですがどうやって説明すればいいのかわかりません。 感覚的には傾きだと思うんですけど・・・ ご教授何卒よろしくお願いします。