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

質問

お世話になります。

グラフに直線近似式を出させています。
その中の傾きと切片を取り出す為SLOPE関数とINTERCEPT関数を用いていますが
うまくできません。

A　B
1　1
2　2
3　#N/A
4　4
5　#N/A
6　6

というデータについて傾きと切片を抜き出したいのですが#N/Aがある為うまくできません

なんとかうまくできないでしょうか？

お教え下さい。

回答No.2

keithin · ベストアンサー

=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が入る場合でも無事対応できました。

回答No.4

MarcoRossiItaly

No.1です。補足に回答いたします。

私がNo.1で書いたのは、エラーの出ているセルを空白にするだけでなく、そのレコード全体を削除するという案ですので、よくお読みください。したがって、No.2さんが書かれている式と同じ結果が出るかと思います。

ただ、No.1でも言ったとおり、エラーが出ているデータを除くのがいいのか、それとも他の対応方法がいいのか、回答者には何とも分からないので、先にそこを検討されたほうがよいと思いました。

回答No.3

hallo-2007

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

補足

すみません。
もともと#N/Aにしているのは関数のもとの参照範囲に数値が入っていないとき
0や""とするとグラフがおかしくなるからです。

""にするようにすると肝心のグラフのほうがおかしくなります。
・""=0と認識することで散布図の線グラフがおかしくなる
・同様に近似曲線もおかしくなる。

おそらく間違ってないと思いますが確認してみます。
グラフ上もワークシート上も同様に表示させたいと思っています。

回答No.1

MarcoRossiItaly

具体的な加工の方法の前に、エラーの出ているデータの扱いをどうすべきかが問題だと思います。エラーのレコードは除いたほうがいいのでしょうか？

そういうことならば、次の手順で、件数が多くても、エラーのレコードのみ削除できます。計算結果を値のみ別の場所にコピペし、置換で「#N/A」を空白とし、「ホーム＞検索と選択＞ジャンプ（Ctrl+G）＞セル選択＞空白セル」で空白セルのみ選択し、「削除＞行全体」でそのレコードのみ消します。

あと、もしも図に直線が引けさえすればよいということなら、散布図でプロットを右クリックし、「近似曲線の追加」により直線を表示させ、「近似曲線の書式設定」の中の「グラフに数式を表示する」をチェックしておけば直線の式が図に載るので（最小2乗法）、傾き・切片も分かります。線を引くだけが目的なら関数を用いるまでもないことになりますね。

補足

言葉足らずですみません。

グラフ上に直線近似式を出すのはわかるのですがそれを利用したいときに
いちいち直線近似式を読み取って入力しなければならなかったのでそれを解消したかったのです。

ちなみに#N/Aにしているのは""にするとグラフでは0と認識されて
グラフ上の直線近似もおかしくなるからです。

グラフ上にも表示でき、実際その近似式をワークシート上でも算出できる方法だったのは
No2の方でした。ただ早い回答ありがとうございました。