Excelで回帰モデルを構築する。 Konrad Carlberg。 Microsoft Excelの回帰分析

MS Excelパッケージは、式を構築するときの方程式を可能にします線形回帰ほとんどの仕事は非常に早く行われています。得られた結果をどのように解釈するかを理解することが重要です。

働ければならない 分析パッケージメニュー項目で有効にしたいです サービス\\ add-in.

分析パッケージを有効にするには、Excel 2007では、ブロックをクリックする必要があります。 Excelの設定左上隅のボタンを押してからボタンを押すことによって Excelの設定»ウィンドウ下部にある：

回帰モデルを構築するには、項目を選択する必要があります サービス\\ Data Analysis \\ Regression。（Excel 2007では、このモードはブロック内にあります データ/データ解析/回帰）。ダイアログボックスが入力されているように見えます。

1) 入力間隔Y ➤パフォーマンスの値を含むセルへのリンクが含まれています y。。値は列に配置する必要があります。

2) 入力間隔X ④係数の値を含むセルへのリンクを含みます。値は列に配置する必要があります。

3）符号タグ最初のセルに説明テキスト（データシグネチャ）が含まれている場合は発生します。

4) 信頼性レベル ❖これはデフォルトで95％と見なされる信頼確率です。この値が適していない場合は、この機能を含めて希望の値を入力する必要があります。

5）符号 コンスタンツーゼロ 空き変数が必要な方程式を構成する必要がある場合はオンになります。

6) 出力パラメータ 結果を配置する場所を決定します。デフォルトのモードはです 新しい作業シート;

7）ブロック 残留物 残余の出力とそれらのグラフの構築を有効にすることができます。

その結果、必要な情報をすべて含み、3つのブロックにグループ化されている情報が表示されます。 回帰統計, 分散解析, 結論残差。それらをより詳細に考えます。

1. 回帰統計:

複数 r 式によって決定される（ ピアソン相関係数);

r (決定係数);

norm r-kvadratは式によって計算されます（に使用されます複数回帰);

標準誤差 s 式によって計算された ;

観測×これはデータ量です n.

2. 分散解析、ライン回帰:

パラメータ dF。 カラス m （要因のセット数 バツ。);

パラメータ ss。 式によって決定される。

パラメータ MS。 式によって決定される。

統計 f 式によって決定される。

意義 f。得られた数が超えると仮説が取られ（線形的な関係はない）、そうでなければ仮説が取られる（線形関係がある）。

3. 分散解析、ライン残基:

パラメータ dF。 等しい;

パラメータ ss。 式が決定されます ;

パラメータ MS。 式によって決定されます。

4. 分散解析、ライン合計最初の2列の合計を含みます。

5. 分散解析、ライン y断面 係数、標準エラーの値が含まれています t-統計。

p-notion†は計算に対応する有意性のレベルの値です。 t-統計。 Stouturaspの機能によって決定されます（ t-統計; ）。もし p-notionは、対応する変数が統計的に重要ではなく、モデルから除外することができます。

95％低い そして トップ95％ ◦これは、線形回帰理論式の係数に対する95パーセント信頼区間の下限および上限です。データ入力ブロックでは、信頼確率値がデフォルトで残された場合、最後の2列は前のものを複製します。ユーザーが信頼確率を入力した場合、最後の2列には、指定された信頼確率の場合は下限と上限の値が含まれています。

6. 分散解析、文字列には係数の値、標準エラーの値が含まれています。 t- スタティシスト p- 関連する近似と信頼区間。

7.ブロック 結論残差 予測値が含まれています y。（私たちの指定で）そして残骸。

に エクセル 線形回帰スケジュール（そしてCMとしての非線形回帰の主要な種類でさえも）を構築するためのさらに高速でより便利な方法があります。これは次のようにすることができます。

1）データで列を割り当てます バツ。 そして y。（彼らはこの順序で見つけるべきです！）;

2）呼び出します マスターチャート そしてグループで選ぶ タイプ – p そしてすぐにプレスします 準備ができて;

3）ダイアグラムから選択をドロップすることなく、表示される最初の項目項目を選択します ダイアグラムあなたはアイテムを選ぶべきです トレンドラインを追加してください;

4）ダイアログの表示ダイアログボックスで トレンドライン タブに タイプ選ぶ線形;

5）タブに パラメータースイッチを有効にすることができます チャートの方程式を示すこれにより、係数（4.5）が計算される線形回帰式（4.4）を見ることができます。

6）同じタブで、スイッチを有効にすることができます ダイアグラム上の近似精度の値（R ^ 2）を配置する（R ^ 2）。この大きさは相関係数（4.3）の二乗であり、計算式に実験的依存性をどの程度うまく表すかを示しています。もし r 2は単位に近い、理論回帰方程式は実験的な依存を表しています（理論は実験とよく一致しています）、そして r 2ゼロに近い、その後この方程式実験的依存性を説明するのには適していない（理論は実験と一致していません）。

説明された動作の実行の結果として、回帰スケジュールおよびその方程式を有する図が得られるであろう。

§4.3。非線形回帰の主な種類

パラボラおよび多項式の回帰

パラボリック 大きさの依存 y。マグニチュードから h 依存関係は二次関数（2次パラボラ）と呼ばれます。

この式は求められます 放物線回帰の方程式 上に h。パラメーターだが, b, から呼び出す 放物線退行係数の係数。放物線回帰係数の計算は常に面倒なので、計算にコンピュータを使用することをお勧めします。

パラボラ回帰の式（4.8）は、多項式と呼ばれるより一般的な回帰の特別なケースです。 多項式 大きさの依存 y。マグニチュードから h Polynomialによって表現された依存関係と呼ばれます n注文：

数字の数 a i. (私。=0,1,…, n） 多項式回帰係数の係数.

消費電流

力大きさの依存 y。マグニチュードから h フォームの依存関係が呼び出されます。

この式は求められます 消費電流の方程式 上に h。パラメーターだがそして b 呼び出す 消費電流の係数.

ln \u003d ln。 a.+bln。 バツ。. (4.11)

この式は、LNの対数座標軸を持つ平面上の直接を表します。 バツ。 そしてln。したがって、電力回帰の適用性の基準は、経験データLNの対数のポイントが要求されていることです。 x i. そしてln。私。それらは線に最も近い（4.11）。

指標回帰

indic indic（または又は 指数関数）大きさの依存 y。マグニチュードから h フォームの依存関係が呼び出されます。

（または）。（4.12）

この式は求められます 式は示します （または又は 指数関数) 退職上に h。パラメーターだが（または又は k）私。 b 呼び出す 係数を示す （または又は 指数関数) 回帰.

電力回帰式の両方の部分が事例である場合、式は次に

ln \u003d。 バツ・ln。 a.+ ln。 b （またはln \u003d k・X.+ ln。 b). (4.13)

この式は、別の値からの単一のLN値の対数の線形依存性を表しています。 バツ。。したがって、消費電流の適用性の基準は、同じ大きさの経験的データの点が要求されていることです。 x i. そして他のLNの大きさの対数私。彼らは直接（4.13）に最も近いです。

対数回帰

対数大きさの依存 y。マグニチュードから h フォームの依存関係が呼び出されます。

=a.+bln。 バツ。. (4.14)

この式は求められます 対数回帰の方程式 上に h。パラメーターだがそして b 呼び出す 対数回帰係数.

双曲線回帰

双曲線 大きさの依存 y。マグニチュードから h フォームの依存関係が呼び出されます。

この式は求められます 双曲線回帰の方程式 上に h。パラメーターだがそして b 呼び出す 双曲線回帰の係数 そして最小の正方形の方法によって決定されます。この方法を使用すると、式があります。

式（4.16-4.17）の合計はインデックスによって行われます私。観察数から観察数まで n.

残念ながら、 エクセル 双曲線回帰の係数を計算する機能はありません。測定値が逆比例に関連していることが知られていない場合、電力回帰式を求めるための双曲線回帰式の代わりに推奨される。 エクセル その場所の手順があります。測定値の間に双曲線依存性が想定されている場合、その回帰係数は式（4.16-4.17）に従って補助計算テーブルと合計操作を使用して計算する必要があります。

回帰および相関分析 - 統計的研究方法これらは、1つ以上の独立した変数からのパラメータの依存関係を示すための最も一般的な方法です。

具体的な実例では、環境環境におけるこれら2つの非常に一般的な分析を検討してください。また、それらを組み合わせるときに結果を取得する例も与えます。

Excelの回帰分析

従属変数にある、ある値（独立した、独立）の効果を示します。たとえば、企業数の経済的に積極的な人口の数として、賃金などの値は異なります。または：外国投資、エネルギー価格などに影響を与える方法GDPのレベルで。

分析の結果、優先順位を強調することができます。そして主な要因に基づいて、管理の決定を下すために、優先順位の開発を予測し、計画します。

回帰が起こります：

線形（Y \u003d A + BX）。
パラボラ（y \u003d A + BX + Cx 2）。
指数関数（y \u003d a * exp（bx））。
電力（y \u003d a * x ^ b）。
双曲線（y \u003d b / x + a）。
対数（y \u003d b * 1n（x）+ a）。
表示（y \u003d a * b ^ x）。

例では、Excelの回帰モデルの構築と結果の解釈を考慮してください。取るリニアタイプ回帰

仕事。 6つの企業では、月平均給与の平均給与と廃止された従業員数が分析されました。廃止された従業員数の平均給与からの依存性を判断する必要があります。

線形回帰モデルは次の形式です。

y \u003d A 0 + A 1 x 1 + ... + Aからxへ。

ここで、Aは回帰係数、X - 変数に影響を与えることです。

この例では、労働者が蒸留した人の指標があるかのように。影響率 - 賃金（X）。

Excelには、リニア回帰モデルのパラメータを計算できる関数が組み込まれています。しかし、それはより速くそれがアドインの「分析パッケージ」を作ります。

強力な分析ツールを起動します。

起動後、アドインは[データ]タブで使用可能になります。

今度は直接回帰分析に対処します。

まず、R四角形と係数に注意を払ってください。

R平方根 - 決定係数この例では、0.755、または75.5％。つまり、モデルの計算されたモデルのパラメータは75.5％が検討されたパラメータの関係を説明することを意味します。決定係数が高いほど、モデルはより良くなります。良い - 0.8。 BAD - 0.5未満（そのような分析は妥当とはほとんど考慮されません）。この例では、「悪くない」

係数64,1428は、検討中のモデル内の全ての変数が0になるとyがどのようになるかを示している。すなわち、モデルに記載されていない他の要因は分析されているパラメータの値の影響を受ける。

係数-0,16285は、変数Xの重量を示しています。すなわち、このモデル内の平均月間賃金は、重量で引用されている-0,16285の量に影響を与えます（これは小さな影響です）。「 - 」は悪影響を示しています：より多くの給与、廃止されません。正しいこと

Excelの相関分析

相関分析は、1つまたは2つのサンプル内のインジケータ間に接続があるかどうかを確立するのに役立ちます。例えば、機械の運転時と修理費用、機器の価格と運用期間、子供の成長と重量など

接続が利用可能である場合、1つのパラメータの増加が増加する（正の相関）、または別のパラメータの減少（負）。相関分析は、Analyticsが、あるインジケータの大きさで他方の可能な値を予測することが可能かどうかを決定するのに役立ちます。

相関係数はRで表される。 +1から-1まで変化します。異なる球に対する相関関係の分類は異なります。係数0の値で線形依存サンプルはありません。

方法を検討してくださいエクセルファンド相関係数を見つけます。

ペア係数を見つけるために、CORRELA関数が使用されます。

タスク：旋盤の運用時の関係とそのサービスのコストの間に関係があるかどうかを判断します。

カーソルを任意のセルにしてFXボタンを押す。

カテゴリ「統計」で、コケルの機能を選択します。
引数 "配列1" - 最初の値の範囲 - マシンの動作時間：A2：A14。
引数「配列2」 - 2番目の値の範囲 - 修理のコスト：B2：B14。 [OK]をクリックします。

通信の種類を判断するには、（アクティビティの各分野では自身のスケールがあるアクティビティの各領域に対して）、係数の絶対数を見る必要があります。

いくつかのパラメータ（2を超える）の相関分析のためには、「データ解析」を適用する方が便利です（アドオン「分析パッケージ」）。リストは相関関係を選択して配列を指定する必要があります。すべて。

結果として生じる係数は相関行列に表示されます。このような：

相関回帰分析

実際には、これら2つの技術はしばしば一緒に使用されています。

例：

これらの回帰分析も見えるようになります。

の相関と回帰分析MS。 エクセル

1. MS Excelでソースデータファイルを作成します（表2など）。

相関分野の構築

の相関フィールドを構築するコマンドラインメニューを選択してください ボックス/ダイアグラム。表示されるダイアログで、チャートの種類を選択します。 p; 見る： ポイント図これにより、蒸気を比較することができます（図22）。

図22 - ダイアグラムタイプの選択

図23-範囲と行を選択するときのウィンドウの表示
図25 - ウィンドウ表示ステップ4

B. コンテキストメニューチームを選ぶ トレンドラインを追加します。

3.表示されるダイアログボックスで、図26に示すように、グラフの種類（この例では）と式のパラメータを選択します。

[OK]をクリックします。結果を図27に示す。

図27 - 株式修理への労働生産性依存性の相関分野

同様に、機器の変更係数に依存する相関分野を構築します。（図28）。

図28 - 労働生産性依存性の相関分野

機器の交換係数から

相関行列の構築。

メニューに相関行列を構築するには サービス 選ぶ データ解析。

データ分析ツールを使用する回帰回帰統計、分散分析、信頼区間の結果に加えて、回復線、残留物、および通常の確率の残留物とグラフを得ることができます。これを行うには、分析パッケージへのアクセスを確認してください。メインメニューで順番に選択します サービス/アドイン。ボックスをチェックしてください 分析パッケージ （図29）

図30 - ダイアログボックス データ解析

表示されたダイアログボックスで[OK]を押した後は、図31に示すように、入力間隔（この例A2：D26）、グループ化（列を介した）と出力パラメータを指定します。

図31 - ダイアログボックス相関

計算結果を表4に示す。

表4 - 相関行列

	列1。	列2。	列3。
列1。
列2。
列3。

単一因子回帰分析

回帰ツールを使う

メニュー内の在庫プロジェクトからの労働生産性の依存関係の回帰分析を実行する サービス 選ぶ データ解析 分析ツールを指定します回帰（図32）。

図33 - ダイアログボックス回帰

MS Excelパッケージは、線形回帰方程式の構造を非常に迅速に作動させることを可能にします。得られた結果をどのように解釈するかを理解することが重要です。回帰モデルを構築するには、Service \\ Data Analysis \\ Regressionを選択する必要があります（Excel 2007このモードはデータ/データ解析単位/回帰にあります）。その後、結果を分析ユニットにコピーします。