Excelの回帰モデリング回帰方程式Excelでの道

回帰分析は、最も求められた統計的研究方法の1つです。これにより、従属変数に対する独立した値の影響度を確立することが可能です。機能的にマイクロソフトエクセル。そのような種類の分析を実行するためのツールがあります。彼らが自分自身を表すこと、そしてそれらの使い方を分析しましょう。

ただし、回帰分析を実行できる機能を使用するには、まず、分析パッケージをアクティブにする必要があります。その場合のみ、この手順に必要なツールがExileテープに表示されます。

今、私たちがタブに進むとき "データ"ツールブロックのリボンに "分析" 新しいボタンが表示されます - "データ解析".

回帰分析の種類

回帰にはいくつかの種類があります。

パラボリック
パワー;
対数
指数関数
気持ちいい。
双曲線
線形回帰

最後の種の履行について回帰分析 Excelでは、もっと詳しく説明します。

Excelプログラムにおける線形回帰

以下に、一例として、路上の平均1日の空気温度が平均的なテーブルを提示し、適切な労働日の店舗バイヤーの数が示されている。回帰分析の助けを借りて、気温の形の気象条件が商業機関の出席にどのように影響するかどうかを調べましょう。

線形種の退行方程式は以下の通りである.Y \u003d A0 + A1X1 + ... + AKK。この式で y。変数を意味し、私たちが探検しようとしている要因の影響を意味します。私たちの場合、これは買い手の数です。値 バツ。 - これらは変数に影響を与えるさまざまな要因です。パラメーター a. 回帰係数です。つまり、特定の要因の重要性を判断するのです。索引 k これらの要因の総数を示します。

分析結果の分析

回帰分析の結果は、設定に示されている場所の表の形で表示されます。

主な指標の1つはです R字形。モデルの品質を示します。私たちの場合にはこの係数 0.705または約70.5％に等しい。これは許容可能なレベルの品質です。依存依存は0.5未満が悪い。

別の重要な指標は交差点のセルにあります 「y交差点」 そして列 「要因」。それはどのような値がyになるでしょう、そして私たちの場合では、これはバイヤーの数です。他のすべての要因はゼロに等しいです。このテーブルはこの表の58.04です。

グラフの交差点の値 "変数x1" そして 「要因」 Xに応じてYのレベルを表示します。私たちの場合、それは保管顧客の数の温度の依存性のレベルです。 1.31の係数は影響力のかなり高い指標と見なされます。

あなたが見ることができるように、助けてマイクロソフトプログラム 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。（私たちの指定で）そして残骸。

トピック：相関と回帰分析エクセル

実験室作業番号1

プログラムにおけるペア相関係数の定義エクセル

相関 - 観察の質量でのみ現れているインジケーター間の不完全で確率的な依存性です。

ペア相関- これは2つのインジケータ間の接続であり、そのうちの1つは要因であり、もう1つは効果的です。

多重相関いくつかの要因と効果的な指標との相互作用が発生します。

相関分析を適用するための必須条件：

研究中の要因の大きさおよび効率的な指標に対する十分に多数の観察の存在。

2.研究されている要因は、特定の情報源で定量的な測定と反射を持たなければなりません。

相関分析の使用を使用すると、以下のタスクを解決できます。

1.有効な指標の変更を1つ以上の要因の影響を受けます。

2.各要因からの性能の相対依存度を確立する。

運動1。

20農業農場にはデータがあります。見つけるには 相関係数 穀物作物の収量の値と地球の品質の間にその重要性を評価します。データを表に示します。

テーブル。土地の品質からの穀物作物の収率の依存

経済数	土地の品質、スコアX	収量、C / HA

関数を使用するための相関係数を見つけるためにコケ.

相関係数の重要性は基準によってチェックされる学生.

考慮される例では、r \u003d 0.999、n \u003d 18である。

Studentの分布を見つけるには、straudspob関数を次の引数で使用します。確率 –0,05, 程度自由 –18.

T統計の値と生徒の分布の量とを比較して、ペア相関係数の重要性についての結論を描画します。 T統計の計算値が生徒の分位分布よりも大きい場合、相関係数は重要です。

2つの値の間の通信の回帰モデルを構築する

タスク2.

タスク1によると：

1）地球の品質と歩留まりの間の直線依存性を特徴付ける回帰式（線形モデル）を構築します。

2）。得られたモデルの妥当性を実行してください。

1 - 方法。

1. Excelシートで、5行のフリーセルと2列のアレイを選択します。

2.関数を呼び出します lin lin.

3.次の関数の引数： izv_val.y。 収量、C / HA。izv_val.バツ。- インジケータの列値 地球の品質、スコア; 定数-1、stat-1 （モデルの妥当性を確認するために使用される指標を計算することができます。場合 STAT - 0、 そのような指標は計算されない。

4.キーの組み合わせを押します Ctrl.- シフト。- 入る.

専用セルはモデル係数、ならびに妥当性のためにモデルをチェックすることを可能にするインジケータによって表示されます（表2）。

表2.
a. 1	a. 0
s e1	s e0
r 2	s e.

Q. r	Q. e.

a. 1 , a. 0 - モデル係数

s e. 1 s e. 0 - 係数の標準誤差。モデルを正確にするほど、これらの値が少なくなります。

r 2 - 決定係数彼よりも正確なモデルはより正確なモデルです。

f - モデルの重要性を確認するための統計。

n- k-1 - 自由度（サンプルのnサイズ、k-入力変数の数。この例ではn \u003d 20、k \u003d 1）

Q. r - 回帰による正方形の合計。

Q. e. - 誤差の二乗の合計。

5.機種の妥当性を検証するためのフィッシャー分布分位数 f f . 機能を使う fボード。これを行うには、フリーセル内の関数を入力してください。 fボード次の引数を持つ確率 – 0,05, 学位_svobody._1–1, 学位_svobody._2-18。 f\u003e f fの場合、モデルはソースデータに適しています

6.構築したモデルの妥当性を確認してください決済レベル有意性（P）。関数を入力してください fデザイズ次の引数を持つ バツ。- 統計値 f, 学位_svobody_1–1, tegre_svobody_2。- 18.算出された有意水準Pの場合<α =0,05, то модель адекватна исходным данным.

第2の方法。

係数の妥当性と意義を検証するためのインジケータを取得するためのモデル係数を決定する。

コマンドを選択してください サービス/データ分析/回帰。 [インストール]ダイアログボックスで： 入力間隔y。 - インジケータの値 収量、C / HA、入力間隔バツ。 - インジケータの値 地球の品質、スコア.

チェックボックスをインストールしますタグ。エリアで 出力パラメータ スイッチを選択 出力間隔 出力が始まるセルを指定します。結果を入手するには、[OK]をクリックします。

結果の解釈

モデルの所望の係数は列内にあります要因:

この例では、モデルの式は次の形式です。

y \u003d 2.53 + 0.5倍

にこの例スコア当たりの土壌の品質が増加すると、粒子作物の収率は平均0.5℃/ haで増加する。

妥当性モデルを確認してください 列に指定されているPの重要度の計算レベルに従って実行されます。意義f. 推定値のレベルが指定された有意レベルα\u003d 0.05よりも小さい場合、モデルは適切である。

統計的意義を確認してくださいモデル係数は、列に指定されたPの重要度の計算レベルで実行されます。 p-値。推定された有意水準が指定された有意水準α\u003d 0.05未満である場合、対応するモデル係数は統計的に有意である。

複数r – 相関係数。その値が1に近いほど、検討された指標の間には密接な関係が狭くなります。この例では、r \u003d 0.99です。これにより、地球の品質が穀物作物の歩留まりが依存する主な要因の1つであると結論付けることができます。

r-平方 – 決定係数。相関係数 - R 2 \u003d 0.98の構成により得られる。それは、穀物作物の収率が98％の土壌の品質に依存し、他の要因のシェアは0.02％を占めることを示しています。

第3回。モデルを構築するためのグラフィック方法

収量と地球の品質の間の接続を反映したポイント図を構築します。

地球の品質からの穀物作物の歩留まりの依存性の線形モデルを得る。

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

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

相関分野の構築

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

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

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

2.コンテキストメニューで、コマンドを選択します トレンドラインを追加します。

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

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

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

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

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

機器の交換係数から

相関行列の構築。

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

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

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

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

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

計算結果を表4に示す。

表4 - 相関行列

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

単一因子回帰分析

回帰ツールを使う

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

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

回帰分析は、最も求められた統計的研究方法の1つです。これにより、従属変数に対する独立した値の影響度を確立することが可能です。 Microsoft Excelの機能には、同様の種類の分析を目的としたツールがあります。彼らが自分自身を表すこと、そしてそれらの使い方を分析しましょう。

分析パッケージを接続する

「ファイル」タブに移動します。
「パラメータ」セクションに進みます。
Excel Parametersウィンドウが開きます。サブセクション「付加構造」に進みます。
開閉ウィンドウの下部には、別の位置にある場合は、「コントロール」ブロックのスイッチを「Excelアドイン」位置に並べ替えます。「GOボタン」をクリックしてください。
Excelの上部構造にアクセス可能なウィンドウを開きました。「分析パッケージ」項目についてチェックを立てる。「OK」ボタンをクリックしてください。

さて、「データ」タブに移動すると、「分析」ツールバー「データ解析」ボタンの新規ボタンが表示されます。

回帰分析の種類

回帰にはいくつかの種類があります。

パラボリック
パワー;
対数
指数関数
気持ちいい。
双曲線
線形回帰

Exceleの最後のタイプの回帰分析の実装についてもっと話します。

Excelプログラムにおける線形回帰

線形種の退行方程式は以下の通りである.Y \u003d A0 + A1X1 + ... + AKK。この式において、yは変数を意味し、私たちが探検しようとしている要因の影響を意味します。私たちの場合、これは買い手の数です。 xの値は、変数に影響を与える様々な要因です。パラメータAは係数回帰である。つまり、特定の要因の重要性を判断するのです。インデックスkは、これらの要因の総数を表す。

分析結果の分析

回帰分析の結果は、設定に示されている場所の表の形で表示されます。

主な指標の1つはR字形です。モデルの品質を示します。私たちの場合、この係数は0.705または約70.5％です。これは許容可能なレベルの品質です。依存依存は0.5未満が悪い。

別の重要な指標は、「Y交差点」ラインと「係数」列との交差点のセル内に配置されている。それはどのような値がyになるでしょう、そして私たちの場合では、これはバイヤーの数です。他のすべての要因はゼロに等しいです。このテーブルはこの表の58.04です。

カウント「変数x1」および「係数」の交点における値は、XからのYの依存性レベルを示しています。私たちの場合、それは温度上のストアのクライアント数の依存度です。 1.31の係数は影響力のかなり高い指標と見なされます。

ご覧のとおり、Microsoft Excelプログラムを使用すると、回帰分析のテーブルを作成することは非常に簡単です。しかし、出口で得られたデータを処理し、その本質を理解するために、準備人だけが可能になります。

私たちはあなたが問題を解決するのを助けることができることをうれしく思います。

問題の本質を詳細に演奏しながら、コメントに質問してください。私たちの専門家はできるだけ早く答えようとします。

この記事はあなたを助けますか？

線形回帰法により、直接回線、最も適切な順序蒸気（x、y）を記述することができます。線形方程式として知られている直線の方程式を以下に示します。

④ - 与えられた値xの期待値

xは独立変数です。

a - 直線のY軸を切り取る

b - 直線を傾ける。

次の図では、この概念はグラフィカルに表されます。

上の図は、式ν\u003d 2 + 0.5xで説明されている行を示しています。軸の軸上のセグメントは軸の軸の交点です。私たちの場合、a \u003d 2の勾配、b、l線の長さへの持ち上がり線の比は0.5です。正の斜面は、線が左から右に上昇することを意味します。 B \u003d 0の場合、水平線は、従属変数と独立変数の間に接続がないことを意味します。つまり、値xの変化はyの値には影響しません。

頻繁に混乱してŷとy。この式に従って、グラフは6つの順序の点と線の対を示しま\u200b\u200bす。

この図では、順序付けされたペアX \u003d 2、Y \u003d 4に対応する点を示している。 h \u003d 2は√です。次の式を使用してこれを確認できます。

○\u003d 2 + 0.5x \u003d 2 + 0.5（2）\u003d 3。

値uは実際の点で、値はyを使ってyの期待値です。線形方程式与えられた値xで。

次のステップは、順序付けられた蒸気のセットに対応する最大方程式を決定することであり、最小二乗法による式の形式が決定された前の記事でこれについて説明した。

Excelを使用して線形回帰を決定します

Excelに埋め込まれた回帰分析ツールを使用するには、アドインを有効にする必要があります。 分析パッケージ。タブをクリックして見つけることができます ファイル - \u003eパラメータ（2007以降）、ダイアログボックスに表示されるダイアログボックスに パラメーターエクセルタブに行きます 上部構造フィールドで コントロール選ぶ 上部構造エクセルそしてクリックします go表示されるウィンドウで、私たちは反対のティックを置きます 分析パッケージzhmem。 OK。

タブに データグループで分析新しいボタンが表示されます データ解析。

アドインの作業を実証するために、私たちは前の記事からのデータを使っています、そこでは男と女の子がバスルーム内のテーブルを共有するものとします。列Aとクリーンシートの入ったお風呂で、この例のデータを入力してください。

タブに行きます データ、グループで分析クリック データ解析。表示されるウィンドウで データ解析 選ぶ回帰図に示すように、[OK]をクリックします。

ウィンドウに必要な回帰パラメータをインストールします回帰写真のように：

クリック OK。得られた結果を下図を示します。

これらの結果は、前の記事で独立したコンピューティングによって受け取ったものに対応しています。

回帰分析は、1つまたは複数の独立した変数からのパラメータの依存性を示す統計的研究方法です。特に大量のデータがあった場合、アプリケーションは複雑な時代に使用するのが困難でした。今日、Excelで回帰を築く方法を学ぶ、あなたは文字通り数分で複雑な統計タスクを解決することができます。以下を表しています具体的な例経済学の分野から。

回帰の種類

このコンセプト自体は1886年に数学フランシス・ガルトに導入されました。回帰が起こります：

線形;
パラボリック
パワー;
指数関数
双曲線
気持ちいい。
対数

実施例1。

チームのメンバーの数の数の依存性を6産業企業の平均給与から決定するという課題を考えてみましょう。

仕事。 6つの企業では、月平均賃金と自分の要求を辞めた従業員の数を分析しました。表形式で：

6企業の平均給与から圧倒された労働者の量の依存性を判断するという課題は、回帰モデルは式y \u003d A0 + A1×1 + ... + akxkの形式を有する。ここで、XI - 影響変数AI回帰係数、AKは要因数です。

このタスクでは、yは声をかけた従業員、そして影響因子 - XがXで示される人の指標です。

「Excel」テーブルプロセッサの機能を使用する

Excelの回帰分析は、組み込み関数の既存の表データへのアプリケーションが先行する必要があります。しかし、これらの目的のためには、非常に有用な上部構造「分析パッケージ」を使用することをお勧めします。それを有効にするには、次のようにします。

[ファイル]タブから[パラメータ]セクションに移動します。
開くウィンドウで、「超構造」文字列を選択します。
行「管理」の右側にある「GOボタン」をクリックしてください。
「分析パッケージ」という名前の横にあるチェックを入れ、[OK]をクリックしてアクションを確認します。

すべてが正しく行われたら、ワークステーション「Excel」の上にある「データ」タブの右側に、目的のボタンが表示されます。

Excelの線形回帰

現在、計算計算の実装に必要なすべての仮想ツールがある場合は、私たちのタスクを解決するために進むことができます。このため：

「データ分析」ボタンをクリックしてください。
開くウィンドウで、「回帰」ボタンをクリックしてください。
表示されるタブでは、Y（廃止された従業員数）とXの値の範囲を入力します（廃棄物）。
「OK」ボタンを押して操作を確認してください。

その結果、プログラムは自動的に回帰分析データを持つ新しいテーブルプロセッサのシートを記入します。注意！ Excelは、この目的のためにあなたが好む場所を独立して尋ねる能力を持っています。たとえば、値がYとX、さらには同じシートである場合があります。新しい本そのようなデータを保存するために特別に設計されています。

R字形の回帰結果の解析

に Excelデータ考慮された例の処理中に得られた例は、以下のとおりです。

まず第一に、あなたはR四角形の値に注意を払うべきです。判定係数です。この例では、R四角\u003d 0.755（75.5％）、すなわち算出されたモデルのパラメータは、検討中のパラメータ間の関係を75.5％で説明する。決定係数の値が高いほど、選択されたモデルは特定のタスクに適していると考えられる。 0.8を超えるR四方の値を実際の状況を正しく説明すると考えられています。 R字形TKRの場合、線形方程式の自由メンバーの無意味の仮説は拒絶される。

「Excel」ツールを使用して、自由メンバーの検討中の問題において、T \u003d 169,20903、およびP \u003d 2.89E-12、すなわち、我々は自由のわからないという正しい仮説がゼロの確率を有する。メンバーは拒否されます。未知のT \u003d 5,79405、およびP \u003d 0.001158の係数の場合。言い換えれば、係数の無意味の正しい仮説が未知で拒絶される可能性は0.12％である。

したがって、結果として生じる線形回帰の方程式は十分にあると主張することができる。