計算結果にゼロが含まれるデータのゼロを無視して、近似直線の係数算出とグラフ作図をしたい

元データから計算したデータを使って、関数で線形近似の傾きと切片を求め、グラフに近似直線を表示しようとしています。データは添付図参照ください。
（実際にはもっと多いですが構図は同じです）

元データにはランダムにデータの欠けがあって、一部のYの値がゼロになってしまう場合があります。
本来ゼロでないのにゼロのデータがあると計算があわないので、そのデータを無視できるようIF文で空白になるようにしたところ、関数による計算は正しくできるようになりました。しかし、グラフには0のデータとして表示されてしまいます。空白セルを空白にするよう設定済みですが、グラフの場合は計算式で空白にしてもゼロと認識してしまうようです。
多数のシートに同じ配置でデータがあるため、個別に修正せずコピペで対応できるようにしたいのです。

数式とグラフ(近似直線)の両方を正しく表示するにはどうすればよいかご教示ください。

No.1 回答者： seiji91 回答日時： 2021/06/18 02:47

やった事ありますが、記憶が・・・

例えば、y = ax + b の a, b はエクセル関数で算出できるはずで、ISERRORとか何か、ゼロや空白を無視して計算する関数で囲ったような・・・
なので、近似曲線は　データを作る必要があります。
相関係数も関数で計算できるので、グラフ内への式/係数の表示以外は対応可能なはずです。

おぼろげですみませんが、まだ誰も回答書いてなかったので、ご参考まで。

この回答へのお礼

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

やったことがある方ですか。
a,bの演算は、添付図では表の右端部分になります。これはご教示のとおり関数でだせています。
グラフについては、表のデータを使うのではなく、計算したa,bから近似直線を描けばよいということですね。

たしかに、そのやり方ならやりたいことができるかもしれません。Y軸の表示範囲を0.01～にすれば値=0のマーカーも表示されないですし。

お礼日時：2021/06/18 22:08