式の例：Excelで回帰。 線形回帰

回帰分析 - 一つ以上の独立変数のパラメータの依存性を示すために、統計的学習方法。 前のコンピュータの時代では、その使用は、それは、大量のデータに来た場合は特に、かなり困難でした。 今日は、Excelで回帰を構築する方法を学んで、あなたはわずか数分で複雑な統計の問題を解決することができます。 以下は、経済学の具体的な例があります。

回帰タイプ

この概念は数学するために導入された フランシス・ゴルトンによって 1886年に。 回帰は次のとおりです。

• リニア;
• 放物線。
• 電源;
• 指数;
• 双曲線;
• 指数;
• 対数。

（実施例1）

6つの工業企業の平均賃金のスタッフのメンバーの辞任の数の依存性を決定する問題を考えます。

タスク。 6社は、平均給与月額と自主的に辞めた従業員の数を分析しました。 表形式では、我々は持っています：

	A	B	C
1	X	辞任の数	給料
2		Y	30000ルーブル
3	1	60	35000ルーブル
4	2	35	40000ルーブル
5	3	20	45000ルーブル
6	4	20	50,000ルーブル
7	5	15	55000ルーブル
8	6	15	60000ルーブル

6つの企業回帰モデルの平均給与の量分離労働者の依存性を決定する問題のためにYが=方程式の形態を有する₀ + _1×1 + ... + _K X _K、ここで、x _I -影響を及ぼす変数_、I -回帰係数、AK -多くの要因。

従業員、要因を発射するための指標である - - Xで示される給与、与えられたタスクのY

「エクセル」のスプレッドシートのパワーを活用

Excelで回帰分析では、組み込み関数既存の表データへの応用が先行されなければなりません。 しかし、これらの目的のために、非常に便利なアドイン「パケット解析」を使用することをお勧めします。 それを有効にするには、以下を行う必要があります。

• タブ「ファイル」「設定」に移動して、
• 開いたウィンドウで、「アドオン」を選択します。
• ライン「経営」の右下にある、「GO」ボタンをクリックしてください。
• 「分析ツール」の横のチェックマークを入れて「OK」を押して操作を確認。

正しく行わ場合は、作業シート「エクセル」の上に位置する「データ」タブの右側には、目的のボタンを示しています。

Excelで線形回帰

今、あなたが手に計量経済計算のために必要なすべての仮想ツールを持っていることを、私たちは私たちの問題に対処するために始めることができます。 これを行うには：

• ボタンは「データ分析」をクリックされます。
• 開いているウィンドウ内のボタン「回帰」をクリックしてください。
• 値の範囲を紹介して表示されるタブY（分離労働者の数）とX（給与）。
• «[OK]を»ボタンを押して、彼らの行動を再確認します。

その結果、プログラムが自動的に新しいシートスプレッドシートのデータの回帰分析を記入します。 注意を払ってください！ Excelでは、あなたは、この目的のために好む場所を設定する機会があります。 例えば、値YとX、またはさらに新しい本を、具体的には、データの記憶のために設計された同じシートであってもよいです。

R-正方形のための回帰分析結果

考慮される例のデータで得られたExcelデータの形式は次のとおりです。

まず第一に、我々はR二乗の値に注意を払う必要があります。 これは、決意の係数を表します。 この例では、R二乗= 0.755（75.5パーセント）、M。E.、モデルの計算されたパラメータは、75.5パーセントによって考慮されるパラメータとの関係を説明します。 決意の係数の値が高いほど、選択されたモデルは、特定のタスクのために、より有用であると考えられます。 正しく0.8以上R-二乗値で、実際の状況を説明すると考えられています。 R二乗<0.5の場合は、Excelでの回帰分析は妥当と考えることができません。

比率分析

数64.1428は、我々のモデルのすべての変数のxiがリセットされます場合は、Yの値がどうなるかを示しています。 換言すれば、分析パラメータの値は、特定のモデルに記載されたもの以外の要因によって影響されると主張することができます。

セルB18に位置する次の要因-0.16285は、これは、モデル内の従業員の平均給与は、-0.16285の重量、Tから退職の数に影響を与えることを意味するYに変数Xの重要な影響を示す。E.全くその影響の程度小さな。 記号は「 - 」の係数が負であることを示しています。 我々はすべての企業ではより多くの給料、少ない人は、雇用契約を終了させる意欲を表明したり却下していることを知っているので、それは、明らかです。

重回帰

この用語のもとでのフォームのいくつかの独立変数との通信方程式を参照します。

Y = F（X ₁ + X ₂ +···のx _M）+ε、Yは-機能スコア（従属変数）であり、そしてX _{1、X 2、...、X mは、} -標識因子（独立変数）です。

パラメータ推定

重回帰（MR）のためには、最小二乗法（LSM）を用いて行われます。 フォームY = A + B ₁ X _{1 + ... + B m個のX M +}の線形方程式のε正規方程式のシステムを構築（CM。下）

この方法の原理を理解するために、我々は、二要素の場合を考えます。 その後、我々は式で表されるような状況を持っています

したがって、我々は得ます：

σはここ - インデックスに反映、各特徴の分散です。

MNCはstandartiziruemom規模の方程式MRに適用されます。 この場合、我々は、式を得ます：

ここで、T _{Y、T、X 1、...、T XM} - 平均値が0であるため、変数をstandartiziruemye。 標準化された回帰係数と標準偏差- - _Iβ1。

_私はすべてがβますのでご注意ください この場合には、従って、有効とみなされ、許容されるとの比較、正規化tsentraliziruemyeとして定義。 加えて、βIの最小値を有するものを廃棄、因子のスクリーニングを行うことが認められています。

線形回帰式を使用しての問題点

あなたが最後の8ヶ月間、特定の製品Nの価格のダイナミクスのテーブルがあるとします。 1850ルーブルの価格で彼の党の獲得かどうかを決定する必要がある。/ T.

	A	B	C
1	月	月の名前	価格N
2	1	1月	トン当たり1750ルーブル
3	2	二月	トン当たり1755ルーブル
4	3	三月	トン当たり1767ルーブル
5	4	四月	トン当たり1760ルーブル
6	5	かもしれません	トン当たり1770ルーブル
7	6	六月	トン当たり1790ルーブル
8	7	七月	トン当たり1810ルーブル
9	8	八月	トン当たり1840ルーブル

すでに上記の例のツール「データ解析」のために知られて使用するために必要な表形式のプロセッサ「エクセル」でこの問題を解決するには。 次に、「回帰」のセクションと設定したパラメータを選択します。 （月）の独立のために - 私たちは、に入力間隔Xの»「入力範囲Y»はとで（年間の具体的な数ヶ月で、この場合には、商品の価格を）従属変数の値の範囲に導入すべきである」ことを覚えておく必要があります。 私たちは、«OK»をクリックして操作を確認します。 （そう指示されている場合）新しいワークシートでは、回帰のためのデータを取得します。

我々は、それらにパラメータa、bは係数の月と名前の行数との回帰分析の結果とシートの«Y-交差点」ラインから係数であるように、フォームY = AX + Bの線形方程式を構築しています。 したがって、問題の線形回帰方程式（EQ）3のように書くことができます。

商品の価格N = 11714 * 1727.54月番号+。

あるいは代数表記

Y = 11714 X + 1727,54

結果の分析

複数の相関係数（CMC）と決意、ならびに試験およびフィッシャーのt検定を用いて適切に線形回帰方程式を受信したかどうかを決定します。 彼らはそれぞれ、複数のR、R-広場、F-T-統計や統計、名前の下に行動する結果とテーブル「エクセル」回帰で。

KMC Rは、独立変数と従属変数との近さの確率の関係を推定することができます。 その高い値は、変数「月の数」との間に強い十分な接続を示す「1トンあたりのルーブルでN製品の価格を。」 しかし、この関係の性質は不明です。

決意のR ²の係数の二乗（RI）は、すなわち、総散乱の割合の数値特性であり、実験データ部分の散乱を示します 線形回帰式に対応する従属変数の値。 この問題では、この値が84.8パーセント、融点である。E.統計が得高精度にSDに記載されています。

また、フィッシャー基準として知られるF-統計は、線形依存性またはその存在を確認したという仮説を反証の重要性を評価するために使用しました。

t統計（スチューデントのt検定）の値は、任意のフリーの未知の線形依存性部材に係数の有意性を評価するのに役立ちます。 t検定> T _CRの値ならば_、自由な用語の線形方程式の取るに足りないの仮説は棄却されます。

楽器「エクセル」による無料の用語については、この問題では、それをt = 169,20903、およびp = 2,89E-12、Tことが判明した。E.は忠実に自由用語の取るに足りないの仮説を拒否されることを、ゼロ確率を持っています。 不明トン= 5,79405の係数であり、p = 0,001158ください。 言い換えれば、拒否正しい仮説は、未知の係数を無意味になる確率は、0.12％です。

したがって、十分に線形回帰式を得たと主張することができます。

株式を購入するの可否の問題

重回帰は、同じ「データ分析」ツールを使用してExcelで行いました。 特定のアプリケーションを考えてみましょう。

ガイド会社«NNN»は、JSC«MMM»の株式の20％を購入するかどうかを決定する必要があります。 パッケージ価格（SP）を70億ドルです。 «NNN»の専門家は、類似の取引に関するデータを収集しました。 このようなパラメータに株式の価値を評価することが決定された、百万米ドルで表され、例えば：

• 債務（VK）。
• 年間売上高のボリューム（VO）;
• 債権（VD）。
• 固定資産（SOF）の値。

また、米ドルの何千もの企業の賃金債務（V3 U）を使用します。

デシジョン・テーブル・プロセッサExcelの手段

まず、入力データのテーブルを作成する必要があります。 以下のようになります：

次へ：

• コール・ボックス「データ分析」。
• 選択した「回帰」セクション。
• ウィンドウ列Gから「入力間隔Y»投与範囲従属変数の値。
• ウィンドウ「入力間隔Xの»の右側に赤い矢印のアイコンをクリックし、カラムB、C、D、Fの全ての値の用紙範囲で隔離

ポイントをマークし、「新ワークシート」と「OK」をクリックしてください。

このタスクのための回帰分析を取得します。

研究結果と結論

シートテーブルExcelのプロセッサ回帰式に上記のデータから丸め「収集」。

SD = 0.103 * SOF + 0541 *のVO - 0031 * VK + 0405 * VD + 0691 * VZP - 265844。

より一般的な数学的な形式では、それは次のように記述することができます。

Y = 0.103 * x1 + 0.541 * x2 - 0.031 * x3 + 0.405 * x4 + 0.691 * x5 - 265.844

MMMのデータは次のとおりです。

SOF、USD	VO、USD	VK、USD	VD、USD	VZP、USD	JV、USD
102.5	535.5	45.2	41.5	21.55	64.72

それらを回帰方程式に代入すると、6,472万ドルの数字が得られます。 これは、MMM株式を購入してはならないことを意味します。なぜなら、7,000万米ドルの価値がかなり誇張されているからです。

ご覧のとおり、Excelテーブルプロセッサと回帰式を使用することで、特定のトランザクションの実現可能性について情報に基づいた判断を下すことができました。

今、あなたは回帰が何であるか知っています。 上で説明したExcelの例は、計量経済学の分野の実用的な問題を解決するのに役立ちます。