以前にこのブログで線形回帰について説明しましたが、データを表現するための最良の方法は直線ではありません。 これらの特定の状況では、Excelで非線形回帰または曲線近似を実行するために使用できるいくつかのツールを利用できます。
私たちの古い友人LINESTを覚えていますか？
LINESTは”線形推定”の略ですが、いくつかの簡単な調整で非線形データに使用することもできます。圧力降下対圧力降下のデータがあるとしましょう。
水弁を通る流量、およびチャート上のデータをプロットした後、我々はデータが二次であることがわかります。
このデータは非線形ですが、LINEST関数を使用して、このデータに最適な近似曲線を見つけることもできます。 多項式の場合、配列定数を使用してこれを行います。LINESTを使用して多項式を定義する係数を取得することの利点は、係数をセルに直接返すことができることです。
LINESTを使用して多項式を定義する係数 そうすれば、手動でチャートからそれらを転送する必要はありません。方程式は二次、または二次多項式であるため、3つの係数、xの2乗、xの1、および定数があります。
式は2次、または2次多項式であるため、xの2乗、xの1、および定数が存在します。
したがって、方程式の3つの係数を格納するスペースを作成することから始める必要があります。
Excelでの非線形回帰にLINESTを使用する
LINEST関数は、係数の配列とオプションの回帰統計を返します。 したがって、数式を入力する前に、係数の3つのセルすべてを選択して、配列数式として入力する必要があります。
流れと圧力データを含むセルの名前が”flow”と”pressure”の場合、式は次のようになります。
=LINEST(pressure,flow^{1,2},TRUE,FALSE)
この場合の既知のyは圧力測定値であり、既知のxは圧力測定値である。第一および第二のパワー。 中括弧”{“と”}”は、Excelの配列定数を示します。 基本的には、Excelに2つの配列を作成するように指示しています。flowの1つとflow-squaredのもう1つ、およびこれらの配列の両方に圧力を合わせるためです。最後に、TRUE引数とFALSE引数は、LINEST関数にy切片を（強制的にゼロにするのではなく）正常に計算し、それぞれ追加の回帰統計を返さないように指示します。これは配列数式なので、Ctrl+Shift+Enterと入力して入力する必要があります。次に、関数はx2とxの係数と定数を返します（LINESTにy切片の計算を許可することを選択したため）。
係数はチャートトレンドラインツールで生成されたものと同じですが、セル内にあるため、後続の計算で使用しやすくなりま
任意の多項式の場合、LINESTは左端の独立変数の最上位の係数を返し、次に最上位の係数を返し、最後に定数を返します。
任意の多項式の場合、LINESTは最同様の手法は、Excelの指数関数、対数関数、およびべき乗関数の曲線近似にも使用できます。
データに対数曲線をフィッティング
対数関数は、次の形式を持ちます。
=LINEST(y_values,ln(x_values),TRUE,FALSE)
もちろん、このメソッドは、基数に関係なく、任意の対数方程式に適用されます。 したがって、log10またはlog2を含む方程式にも簡単に適用できます。
ベストフィット指数曲線の係数を見つける
指数関数は、次の形式を持ちます:最初に方程式を「線形」形式にするために少し代数を行う必要があるため、この方程式の係数aとbを取得するのは少しトリッキーです。 まず、式の両側の自然対数を取って次のようになります。
linestを使用して、ln(y)をy_valuesの引数として入力してln(a)とbを取得:
=LINEST(ln(y_values),x_values,TRUE,FALSE)
この配列数式によって返される2番目の値はln(a)なので、”a”だけを取得するには、単に指数関数を使用します。
Excelでは、次のように変換されます:
=EXP(number)
データへのべき乗関数の近似
べき乗関数曲線は、指数関数に対して行うのとほぼ同じ方法でlinestを使用してデータに適合させることができます。 べき乗関数は次の形式を持ちます:ここでも、式の両側の基数10のログを取得して次のように「線形化」できます。
この形式の式では、bとlog10(a)を返すlinest関数は次のように設定できます。
=linest(log10(yvalues),log10(xvalues),true,false)
linest関数はbとlog10(a)を返すので、次の式でaを見つける必要があります。
excelでは、その式は次のとおりです:
=10^（数字）
それは今のところそれです。 ご覧のとおり、Excelで非線形曲線近似にLINEST関数を使用する方法はいくつかあります。p>