このチュートリアルでは、最もLinest関數(shù)の構(gòu)文を説明し、Excelで線(xiàn)形回帰分析を行うためにそれを使用する方法を示しています。

Microsoft Excelは統(tǒng)計(jì)プログラムではありませんが、多くの統(tǒng)計(jì)機(jī)能があります。そのような機(jī)能の1つは最もラインであり、線(xiàn)形回帰分析を?qū)g行し、関連する統(tǒng)計(jì)を返すように設(shè)計(jì)されています。初心者向けのこのチュートリアルでは、理論と根本的な計(jì)算について軽視するだけです。私たちの主な焦點(diǎn)は、単に機(jī)能し、データ用に簡(jiǎn)単にカスタマイズできる式を提供することです。

Excel Linest関數(shù) - 構(gòu)文と基本的な用途

最もLinest関數(shù)は、獨(dú)立変數(shù)と1つ以上の従屬変數(shù)との関係を説明する直線(xiàn)の統(tǒng)計(jì)を計(jì)算し、線(xiàn)を説明する配列を返します。この関數(shù)は、最小二乗法を使用して、データに最適なものを見(jiàn)つけます。行の方程式は次のとおりです。

単純な線(xiàn)形回帰方程式：

重回帰方程式：

どこ：

• Y-予測(cè)しようとしている従屬変數(shù)。
• X- Yを予測(cè)するために使用している獨(dú)立変數(shù)。
• A-インターセプト（線(xiàn)がY軸と交差する場(chǎng)所を示します）。
• b-勾配（回帰線(xiàn)の急勾配、つまりxが変化するときのyの変化率を示します）。

基本的な形式では、最もライン関數(shù)は、回帰方程式のインターセプト（a）と勾配（b）を返します。オプションでは、この例に示すように、回帰分析のために追加の統(tǒng)計(jì)を返すこともできます。

Linest Function構(gòu)文

Excel Linest関數(shù)の構(gòu)文は次のとおりです。

どこ：

• 既知の_y （必須）は、回帰方程式の依存y値の範(fàn)囲です。通常、それは単一の列または単一の行です。
• 既知の_x （オプション）は、獨(dú)立したx値の範(fàn)囲です。省略した場(chǎng)合、それは既知の_yと同じサイズの配列{1,2,3、...}であると想定されます。
• const （optional） - インターセプト（定數(shù)a ）をどのように扱うべきかを決定する論理値：
  • trueまたは省略した場(chǎng)合、定數(shù)Aは正常に計(jì)算されます。
  • falseの場(chǎng)合、定數(shù)Aが0に強(qiáng)制され、勾配（ B係數(shù)）が計(jì)算され、y = bxに適合します。
• 統(tǒng)計(jì)（オプション）は、追加の統(tǒng)計(jì)を出力するかどうかを決定する論理値です。
  • trueの場(chǎng)合、Linest関數(shù)は追加の回帰統(tǒng)計(jì)を持つ配列を返します。
  • falseまたは省略の場(chǎng)合、Linestはインターセプト定數(shù)と勾配係數(shù)のみを返します。

注記。 Linestは値の配列を返すため、 Ctrlシフトを押すことにより、ショートカットを入力することにより、配列式として入力する必要があります。通常の式として入力された場(chǎng)合、最初の勾配係數(shù)のみが返されます。

Linestによって返される追加の統(tǒng)計(jì)

Trueに設(shè)定されたStats引數(shù)は、リネスト関數(shù)に、回帰分析のために次の統(tǒng)計(jì)を返すように指示します。

以下のマップは、Linestが統(tǒng)計(jì)の配列を返す順序を示しています。

最後の3行では、＃n/aエラーがデータで満たされていない3番目と後続の列に表示されます。これは、最もLinest関數(shù)のデフォルトの動(dòng)作ですが、エラー表記を非表示にする場(chǎng)合は、この例に示すように、最もラインな式をiferrorに包みます。

Excel-フォーミュラの例でLinestを使用する方法

特に初心者では、最も適切な機(jī)能を使用するのが難しい場(chǎng)合があります。これは、式を正しく構(gòu)築するだけでなく、その出力を適切に解釈する必要があるためです。以下に、ExcelでLinestフォーミュラを使用しているいくつかの例があります。

単純な線(xiàn)形回帰：勾配とインターセプトを計(jì)算します

回帰ラインのインターセプトと勾配を取得するには、最も単純な形式で最もライン機(jī)能を使用します。既知の_yの引數(shù)の依存?zhèn)帳喂?fàn)囲と、既知の_xの引數(shù)に獨(dú)立した値の範(fàn)囲を提供します。最後の2つの引數(shù)は、Trueに設(shè)定するか、省略できます。

たとえば、C2：C13のY値（販売數(shù)）とB2：B13のX値（広告コスト）がある場(chǎng)合、線(xiàn)形回帰式は次のように簡(jiǎn)単です。

=LINEST(C2:C13,B2:B13)

ワークシートに正しく入力するには、同じ行で2つの隣接するセル、E2：F2を選択し、式を入力し、 Ctrlシフトを押して入力して完了します。

式は、最初のセル（E2）の勾配係數(shù)と、2番目のセル（F2）の切片定數(shù)を返します。

勾配は約0.52です（小數(shù)點(diǎn)以下の2つの場(chǎng)所に丸められています）。つまり、 xが1増加すると、 yが0.52増加することを意味します。

y intecteは負(fù)-4.99です。 x = 0の場(chǎng)合、 yの期待値です。グラフにプロットされた場(chǎng)合、それは回帰線(xiàn)がy軸を通過(guò)する値です。

上記の値を単純な線(xiàn)形回帰方程式に供給すると、広告コストに基づいて販売番號(hào)を予測(cè)するために次の式を取得します。

y = 0.52*x - 4.99

たとえば、広告に50ドルを費(fèi)やす場(chǎng)合、21の傘を販売することが期待されています。

0.52*50 - 4.99 = 21.01

スロープとインターセプトの値は、対応する関數(shù)を使用するか、最もラインフォーミュラをインデックスにネストすることによって個(gè)別に取得することもできます。

スロープ

=SLOPE(C2:C13,B2:B13)

=INDEX(LINEST(C2:C13,B2:B13),1)

インターセプト

=INTERCEPT(C2:C13,B2:B13)

=INDEX(LINEST(C2:C13,B2:B13),2)

以下のスクリーンショットに示すように、3つの式すべてが同じ結(jié)果をもたらします。

複數(shù)の線(xiàn)形回帰：勾配とインターセプト

2つ以上の獨(dú)立した変數(shù)がある場(chǎng)合は、隣接する列にそれらを入力し、その範(fàn)囲を既知の_xの引數(shù)に提供してください。

たとえば、D2：D13の販売數(shù)（ Y値）、B2：B13の広告コスト（X値の1つ）、C2：C13の平均月間降雨（ X値の別のセット）で、この式を使用します。

=LINEST(D2:D13,B2:C13)

式は3つの値（2勾配係數(shù)とインターセプト定數(shù)）の配列を返すため、同じ行で3つの隣接セルを選択し、式を入力し、 Ctrlシフトを押してショートカットを入力します。

重回帰式は、獨(dú)立変數(shù)（右から左へ）の逆の順序で勾配係數(shù)を返すことに注意してください。つまり、b _n 、b _n-1 、…、b ₂ 、b ₁ ：

販売番號(hào)を予測(cè)するために、最新の式によって返される値を重回帰方程式に提供します。

y = 0.3*x ₂ 0.19* _x 1-10.74

たとえば、広告に50ドルが費(fèi)やされ、平均月額降雨量が100 mmであるため、約23の傘を販売することが期待されています。

0.3*50 0.19*100 - 10.74 = 23.26

単純な線(xiàn)形回帰：従屬変數(shù)を予測(cè)します

回帰方程式のAおよびB値を計(jì)算することとは別に、Excel Linest関數(shù)は、既知の獨(dú)立変數(shù)（x）に基づいて従屬変數(shù)（y）を推定することもできます。このためには、sumまたはsproduct関數(shù)と組み合わせて最もLinestを使用します。

たとえば、前月の売上と10月の広告予算50ドルに基づいて、來(lái)月の傘の売上の數(shù)を計(jì)算する方法、たとえば10月などです。

=SUM(LINEST(C2:C10, B2:B10)*{50,1})

式のX値をハードコードする代わりに、セル?yún)⒄栅趣筏铺峁─扦蓼?。この?chǎng)合、配列定數(shù)で參照と値を混合できないため、一部のセルに1定數(shù)を入力する必要があります。

E2のx値とF2の定數(shù)1で、以下の式のいずれかのいずれかが処理に機(jī)能します。

通常の式（ Enterを押すことで入力）：

=SUMPRODUCT(LINEST(C2:C10, B2:B10)*(E2:F2))

アレイ式（ ctrlシフトを押すことで入力）：入力）：

=SUM(LINEST(C2:C10, B2:B10)*(E2:F2))

結(jié)果を確認(rèn)するには、同じデータのインターセプトと傾斜を取得し、線(xiàn)形回帰式を使用してyを計(jì)算できます。

=E2*G2 F2

ここで、E2は勾配、G2はX値、F2はインターセプトです。

重回帰：従屬変數(shù)を予測(cè)します

いくつかの予測(cè)因子、つまりいくつかの異なるx値のセットを扱っている場(chǎng)合、配列定數(shù)にこれらすべての予測(cè)因子を含めます。たとえば、50ドル（x ₂ ）の広告予算と100 mmの平均降雨量（x ₁ ）の場(chǎng)合、式は次のとおりです。

=SUM(LINEST(D2:D10, B2:C10)*{50,100,1})

ここで、D2：D10は既知のY値であり、B2：C10は2セットのX値です。

配列定數(shù)のX値の順序に注意してください。先に指摘したように、Excel Linest関數(shù)を使用して重回帰を?qū)g行すると、勾配係數(shù)が右から左に戻ります。この例では、広告係數(shù)が最初に返され、次に降雨係數(shù)が返されます。予測(cè)された販売番號(hào)を正しく計(jì)算するには、係數(shù)に対応するX値を掛ける必要があるため、この順序で配列定數(shù)を配置する必要があります：{50,100,1}。最後の要素は1です。なぜなら、Linestによって返される最後の値は、変更すべきではないインターセプトであるため、単に1を掛けるだけです。

配列定數(shù)を使用する代わりに、一部のセルのすべてのx変數(shù)を入力し、前の例のように式のセルを參照できます。

通常の式：

=SUMPRODUCT(LINEST(D2:D10, B2:C10)*(F2:H2))

配列式：

=SUM(LINEST(D2:D10, B2:C10)*(F2:H2))

ここで、F2とG2はX値、H2は1です。

Linest Formula：追加の回帰統(tǒng)計(jì)

覚えているように、回帰分析のためのより多くの統(tǒng)計(jì)を取得するために、最もラインな関數(shù)の最後の引數(shù)に真のものを置きます。サンプルデータに適用されると、式は次の形狀を取得します。

=LINEST(D2:D13, B2:C13, TRUE, TRUE)

列bとcに2つの獨(dú)立変數(shù)があるため、3行（2 x値インターセプト）と5列で構(gòu)成される怒りを選択し、上記の式を入力し、 Ctrlシフトを押して、この結(jié)果を取得します。

＃n/aエラーを取り除くには、このようなiferrorにLinestにネストすることができます。

=IFERROR(LINEST(D2:D13, B2:C13, TRUE, TRUE), "")

以下のスクリーンショットは結(jié)果を示し、各番號(hào)の意味を説明します。

勾配係數(shù)とy interceptは前の例で説明されているため、他の統(tǒng)計(jì)を簡(jiǎn)単に見(jiàn)てみましょう。

決定係數(shù)（R ² ）。 R ²の値は、二乗の回帰合計(jì)を総平方合計(jì)で割る結(jié)果です。 x変數(shù)によって説明されるy値の數(shù)を示します。 0?1の任意の數(shù)、つまり0％から100％になる場(chǎng)合があります。この例では、R ²は約0.97です。つまり、従屬変數(shù)の97％（傘の販売）は、獨(dú)立変數(shù)（広告平均月平均降雨量）によって説明されています。

標(biāo)準(zhǔn)エラー。一般に、これらの値は回帰分析の精度を示しています。數(shù)字が小さいほど、回帰モデルについて確実になります。

f統(tǒng)計(jì)。 F統(tǒng)計(jì)を使用して、帰無(wú)仮説をサポートまたは拒否します。全體の結(jié)果が有意であるかどうかを決定する際に、P値と組み合わせてF統(tǒng)計(jì)を使用することをお?jiǎng)幛幛筏蓼埂?/p>

自由度（DF）。 Excelで最もラインな関數(shù)は、自由度の殘留度を返します。これは、総DFから回帰dfを差し引いています。自由度を使用して統(tǒng)計(jì)テーブルでf-critical値を取得し、f-critical値をF統(tǒng)計(jì)と比較して、モデルの信頼レベルを決定できます。

正方形の回帰合計(jì)（別名、説明された正方形の合計(jì)、または正方形のモデル合計(jì)）。これは、この式で計(jì)算された予測(cè)y値とyの平均の間の二乗の違いの合計(jì)^です。これは、回帰モデルが説明する従屬変數(shù)の変動(dòng)の量を示しています。

正方形の殘差。これは、実際のy値と予測(cè)されたy値の間の二乗の違いの合計(jì)です。これは、モデルが説明していない従屬変數(shù)の変動(dòng)の量を示しています。正方形の合計(jì)と比較して、正方形の殘留和が小さいほど、回帰モデルがデータに適合します。

最もラインな関數(shù)について知っておくべき5つのこと

ワークシートで最もラインフォーミュラを効率的に使用するには、関數(shù)の「內(nèi)部力學(xué)」についてもう少し知りたい場(chǎng)合があります。

1. nown_y's and nown_x's 。 x変數(shù)のセットのみを備えた単純な線(xiàn)形回帰モデルでは、 necking_yとnecking_xは、同じ數(shù)の行と列を持っている限り、あらゆる形狀の範(fàn)囲になります。獨(dú)立したX変數(shù)の複數(shù)のセットを使用して重回帰分析を行う場(chǎng)合、既知の_yはベクトルである必要があります。つまり、1行または1列の範(fàn)囲です。
2. 定數(shù)をゼロに強(qiáng)制します。 const引數(shù)が真であるか、省略されている場(chǎng)合、定數(shù)（インターセプト）が計(jì)算され、方程式に含まれます：y = bx a。 constがfalseに設(shè)定されている場(chǎng)合、インターセプトは等しい0と見(jiàn)なされ、回帰方程式から省略されています：y = bx。

   統(tǒng)計(jì)では、インターセプトを0に強(qiáng)制することが理にかなっているかどうかは何十年も議論されてきました。多くの信頼できる回帰分析実務(wù)者は、インターセプトをゼロ（const = false）に設(shè)定すると有用であると思われる場(chǎng)合、線(xiàn)形回帰自體はデータセットの間違ったモデルであると考えています。他の人は、たとえば、回帰の不連続設(shè)計(jì)のコンテキストなど、特定の狀況で定數(shù)を強(qiáng)制することができると仮定します。一般に、ほとんどの場(chǎng)合、デフォルトのconst = trueまたは省略することをお?jiǎng)幛幛筏蓼埂?/p>

3. 正確さ。最もLinest関數(shù)によって計(jì)算された回帰方程式の精度は、データポイントの分散に依存します。データがより直線(xiàn)的になればなるほど、最もラインフォーミュラの結(jié)果がより正確になります。
4. 冗長(zhǎng)X値。狀況によっては、1つ以上の獨(dú)立したX変數(shù)に追加の予測(cè)値がなく、回帰モデルからそのような変數(shù)を削除しても、予測(cè)Y値の精度に影響しません。この現(xiàn)象は「共線(xiàn)性」として知られています。 Excel Linest関數(shù)は共線(xiàn)性をチェックし、モデルから識(shí)別する冗長(zhǎng)X変數(shù)を省略します。省略されたX変數(shù)は、0の係數(shù)と0の標(biāo)準(zhǔn)誤差値によって認(rèn)識(shí)できます。
5. 最もライン対勾配とインターセプト。最もライン関數(shù)の根本的なアルゴリズムは、勾配関數(shù)とインターセプト関數(shù)で使用されるアルゴリズムとは異なります。したがって、ソースデータが未定またはコリネアの場(chǎng)合、これらの関數(shù)は異なる結(jié)果を返す可能性があります。

Excel Linest関數(shù)が機(jī)能していません

あなたの最もラインフォーミュラがエラーを投げたり、間違った出力を生成したりする場(chǎng)合、それは次の理由のいずれかが原因である可能性があります。

1. Linest関數(shù)が1つの數(shù)値（勾配係數(shù)）のみを返す場(chǎng)合、おそらくアレイ式ではなく、通常の式として入力した可能性が高いです。 Ctrl Shift Enterを押して、式を正しく完了してください。これを行うと、フォーミュラバーに表示される{Curly Brackets}に式が囲まれます。
2. #ref！エラー。既知の_xとnowned_yの範(fàn)囲が異なる寸法の場(chǎng)合に発生します。
3. ＃価値！エラー。既知の_xまたはnecking_yが少なくとも1つの空白のセル、テキスト値、またはExcelが數(shù)値として認(rèn)識(shí)していない數(shù)のテキスト表現(xiàn)が含まれている場(chǎng)合に発生します。また、 constまたはstats引數(shù)をtrueまたはfalsに評(píng)価できない場(chǎng)合、#valueエラーが発生します。

これで、ExcelでLinestを使用して、シンプルで複數(shù)の線(xiàn)形回帰分析を行う方法です。このチュートリアルで説明した式を詳しく見(jiàn)るには、以下のサンプルワークブックをダウンロードできます。お読みいただきありがとうございます。來(lái)週のブログでお會(huì)いしましょう！

ダウンロード用のワークブックを練習(xí)します

Excel Linest関數(shù)の例（.xlsxファイル）

以上が式の例を備えたLinest関數(shù)をExcelの詳細(xì)內(nèi)容です。詳細(xì)については、PHP 中國(guó)語(yǔ) Web サイトの他の関連記事を參照してください。