| |
エラー値がある場合にもグラフの「近似曲線」と同様の動作をする関数、
または関数でなくても係数を得る方法はありませんでしょうか。
グラフの「近似曲線」機能を使用して表示される近似式の係数を、セルで扱いたいと思っています。
色々な方法を試しましたが、エラー値がある場合にも「近似曲線」機能と同じ動作をしてもらえる
方法を見付けることが出来ませんでした。
近似式の関数としては、y=ax+b をはじめ多項式、y=ax^b、y=ae^x 等を想定しています。
ジャンルは統計というよりは、科学技術実験系です。
ここに、調べた方法とエラー値がある場合の結果をまとめました。
なんとか最強の方法に辿り着きたいと思いますのでお助け下さい。
方法 | 空欄or文字列 | #N/A | #NUM! | #DIV/0! | #VALUE! | 備考
--------------------------------------------------------------------------------
「近似曲線」 | ok | ok | 0扱い | 0扱い | 0扱い | 最強
slope() | ok | #N/A | #NUM! | #DIV/0! | #VALUE! | 多項式非対応
intercept() | ok | #N/A | #NUM! | #DIV/0! | #VALUE! | 同上
rsq() | ok | #N/A | #NUM! | #DIV/0! | #VALUE! | 同上
forecast() | ok | #N/A | #NUM! | #DIV/0! | #VALUE! | 同上
linest() | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | 最弱
trend() | #VALUE! | #VALUE! | #VALUE! | #VALUE! | #VALUE! | 同上
現在のところ、妥協を重ねて以下のようなことをしています。
【y=ax+b の場合】
a = slope( y , x ) b = intercept( y , x )
【y=ax^b の場合】
a = exp(intercept( ln(y)を計算したセル , ln(x)を計算したセル )
b = slope( ln(y)を計算したセル , ln(x)を計算したセル )
※ ln(y) を計算したセルとは、 =if(isna(y), "", ln(y)) を計算したセル。
※ 一気に a = exp(intercept( ln(y) , ln(x) ) と書くと、
ln(空欄)がエラーになるため intercept() もエラーになってしまう。
【多項式の場合】
諦めて linest を使い、空欄がある場合は時間をかけて手作業で対応する。
|
|
![[未読]](./data/excel/image/read_icon.gif){kind=icon}
