回帰分析と相関分析は統計調査手法です。 これらは、1 つ以上の独立変数に対するパラメーターの依存性を示す最も一般的な方法です。
以下、具体的に 実践例経済学者の間で非常に人気のあるこれら 2 つの分析を見てみましょう。 組み合わせた場合の結果の例も示します。
Excel での回帰分析
従属変数に対するいくつかの値（独立、独立）の影響を示します。 たとえば、経済活動人口の数は企業の数、賃金、その他のパラメーターにどのように依存しますか。 あるいは、海外投資やエネルギー価格などがGDPの水準にどのような影響を与えるのか。
分析の結果により、優先順位を強調することができます。 そして主な要因に基づいて開発を予測し計画します 重点分野、経営上の意思決定を行います。
回帰が起こる:
- 線形 (y = a + bx);
- 放物線 (y = a + bx + cx 2);
- 指数関数 (y = a * exp(bx));
- べき乗 (y = a*x^b);
- 双曲線 (y = b/x + a);
- 対数 (y = b * 1n(x) + a);
- 指数関数 (y = a * b^x)。
Excel で回帰モデルを構築し、結果を解釈する例を見てみましょう。 線形タイプの回帰を考えてみましょう。
タスク。 6社の平均月収と離職者数を分析した。 離職者数が平均給与にどの程度依存しているかを判断する必要がある。
モデル 線形回帰は次の形式になります。
Y = a 0 + a 1 x 1 +…+a k x k。
ここで、a は回帰係数、x は影響を与える変数、k は因子の数です。
この例では、Y は従業員の退職の指標です。 影響を与える要因は賃金 (x) です。
Excel には、線形回帰モデルのパラメーターの計算に役立つ組み込み関数があります。 ただし、「分析パッケージ」アドオンを使用すると、これがより速く実行されます。
強力な分析ツールを有効にします。
アクティブ化すると、アドオンは [データ] タブで使用できるようになります。
それでは、回帰分析自体を実行してみましょう。
まず第一に、R 二乗と係数に注目します。
R 二乗は決定係数です。 この例では、0.755、つまり 75.5% です。 これは、モデルの計算されたパラメーターが、調査されたパラメーター間の関係の 75.5% を説明していることを意味します。 決定係数が高いほど、 より高品質なモデル。 良好 - 0.8 以上。 悪い – 0.5 未満 (このような分析は合理的であるとはほとんど考えられません)。 私たちの例では、「悪くない」です。
係数 64.1428 は、検討中のモデル内のすべての変数が 0 に等しい場合に Y が何になるかを示します。つまり、分析されたパラメーターの値は、モデルに記述されていない他の要因にも影響されます。
係数 -0.16285 は、変数 X の Y に対する重みを示します。つまり、このモデル内の平均月給は、重み -0.16285 で退職者数に影響します (これは影響の程度は小さいです)。 「-」記号は次のことを示します 悪影響: 給料が高くなればなるほど、辞める人は減ります。 それは公平なことだ。
Excelでの相関分析
相関分析は、1 つまたは 2 つのサンプル内の指標間に関係があるかどうかを判断するのに役立ちます。 たとえば、機械の稼働時間と修理費、機器の価格と稼働期間、子供の身長と体重などです。
関係がある場合、一方のパラメータの増加は他方のパラメータの増加 (正の相関) または減少 (負の相関) につながりますか。 相関分析は、アナリストが、ある指標の値を使用して別の指標の可能な値を予測できるかどうかを判断するのに役立ちます。
相関係数はrで表されます。 +1 から -1 まで変化します。 相関関係の分類 さまざまな地域違うでしょう。 係数が 0 の場合、サンプル間に線形関係はありません。
Excelを使って相関係数を求める方法を見てみましょう。
ペアの係数を見つけるには、CORREL 関数が使用されます。
目的: 稼働時間の間に関係があるかどうかを判断する 旋盤そしてその維持費。
任意のセルにカーソルを置き、fx ボタンを押します。
- 「統計」カテゴリで CORREL 関数を選択します。
- 引数「配列 1」 - 最初の値の範囲 - マシンの動作時間: A2:A14。
- 引数「配列 2」 - 2 番目の値の範囲 - 修理コスト: B2:B14。 「OK」をクリックします。
接続のタイプを判断するには、係数の絶対数を調べる必要があります (活動の各分野には独自のスケールがあります)。
のために 相関分析パラメータが複数ある場合（2 つ以上）、「データ分析」（「分析パッケージ」アドオン）を使用すると便利です。 リストから相関を選択し、配列を指定する必要があります。 全て。
結果の係数は相関行列に表示されます。 このような：
相関分析と回帰分析
実際には、これら 2 つの手法が一緒に使用されることがよくあります。
例：
これでデータが表示されるようになりました 回帰分析.
で エクセルさらに速いものもあり、 便利な方法線形回帰グラフを作成します (主な種類の非線形回帰も同様に、以下を参照してください)。 これは次のようにして実行できます。
1) データのある列を選択します バツそして Y(この順序でなければなりません!);
2) 電話する チャートウィザードグループ内で選択します タイプ – スポットそしてすぐに押してください 準備ができて;
3) 図の選択を解除せずに、表示されるメイン メニュー項目を選択します。 ダイアグラム、項目を選択する必要があります トレンドラインを追加する;
4) 表示されるダイアログボックスで トレンドラインタブ内 タイプ選ぶ 線形;
5) タブ内 オプションスイッチをアクティブにすることができます 方程式を図に表示これにより、係数 (4.5) が計算される線形回帰方程式 (4.4) が表示されます。
6) 同じタブでスイッチをアクティブ化できます。 近似信頼度値 (R^2) を図上に配置します。。 この値は相関係数 (4.3) の 2 乗であり、計算された方程式が実験依存性をどの程度よく説明しているかを示します。 もし R 2 は 1 に近いので、 理論方程式回帰は実験の依存性をよく説明します (理論は実験とよく一致します)。 R 2 がゼロに近い場合、この式は実験の依存性を説明するのには適していません (理論は実験と一致しません)。
説明したアクションを実行した結果、回帰グラフとその方程式を含む図が得られます。
§4.3。 非線形回帰の主な種類
放物線回帰および多項式回帰。
放物線状値の依存性 Yサイズから バツ表現された依存性と呼ばれます 二次関数(2次放物線):
この方程式は次のように呼ばれます 放物線回帰式 Yの上 バツ。 オプション あ, b, と呼ばれます 放物線回帰係数。 放物線回帰係数の計算は常に面倒なので、コンピューターを使用して計算することをお勧めします。
放物線回帰の式 (4.8) は、多項式と呼ばれるより一般的な回帰の特殊なケースです。 多項式値の依存性 Yサイズから バツ多項式で表される依存関係と呼ばれます n- 番目の注文:
数字はどこにありますか そして私 (私=0,1,…, n) と呼ばれます 多項式回帰係数.
電力回帰。
力値の依存性 Yサイズから バツは次の形式の依存関係と呼ばれます。
この方程式は次のように呼ばれます べき乗回帰式 Yの上 バツ。 オプション あそして b呼ばれます べき乗回帰係数.
ln =ln ある+b・ ln バツ. (4.11)
この方程式は、対数座標軸 ln を持つ平面上の直線を表します。 バツそしてイン。 したがって、べき乗回帰の適用性の基準は、経験データの対数点 ln が次の条件を満たすという要件になります。 x iそしてln はい、私直線に最も近かった（4.11）。
指数回帰。
示唆的な（または 指数関数的) 値の依存性 Yサイズから バツは次の形式の依存関係と呼ばれます。
（または ）。 (4.12)
この方程式は次のように呼ばれます 指数方程式（または 指数関数的) 回帰Yの上 バツ。 オプション あ（または k） そして b呼ばれます 指数係数（または 指数関数的) 回帰.
べき乗回帰方程式の両辺の対数を取ると、次の方程式が得られます。
ln = バツ ln ある+ln b(または ln = kx+ln b). (4.13)
この方程式は、ある量 ln の対数の別の量に対する線形依存性を表します。 バツ。 したがって、べき乗回帰の適用性の基準は、同じ値の経験的データ ポイントが存在するという要件です。 x iおよび別の量 ln の対数 はい、私直線に最も近かった（4.13）。
対数回帰。
対数値の依存性 Yサイズから バツは次の形式の依存関係と呼ばれます。
=ある+b・ ln バツ. (4.14)
この方程式は次のように呼ばれます 対数回帰式 Yの上 バツ。 オプション あそして b呼ばれます 対数回帰係数.
双曲線回帰。
双曲線値の依存性 Yサイズから バツは次の形式の依存関係と呼ばれます。
この方程式は次のように呼ばれます 双曲線回帰式 Yの上 バツ。 オプション あそして b呼ばれます 双曲線回帰係数そして方法によって決定されます 最小二乗。 この方法を適用すると、次の式が得られます。
式 (4.16-4.17) では、インデックスに対して合計が実行されます。 私 1 から観測値の数まで n.
残念ながら、 エクセル双曲線回帰係数を計算する関数はありません。 測定された量が反比例によって関係していることが不明な場合は、双曲線回帰式の代わりにべき乗回帰式を探すことをお勧めします。 エクセルそれを見つけるには手順があります。 測定された量の間に双曲線依存性があると仮定される場合、その回帰係数は、補助計算テーブルと式 (4.16-4.17) を使用した合計演算を使用して計算する必要があります。
このソフトウェア ユーティリティが仕事で使用される計量経済学などの分野を含む、さまざまな活動分野で役立つことで知られています。 基本的に実習や実験の授業はすべて Excel で行われますが、特定の動作については詳細な説明があるため、作業が大幅に容易になります。 したがって、分析ツールの 1 つである「回帰」を使用して、最小二乗法を使用して一連の観測値のグラフを選択します。 それが何なのか見てみましょう このツールプログラムとそのユーザーにとってのメリットについて説明します。 以下も短いですが、 明確な指示回帰モデルの構築。
主なタスクと回帰の種類
回帰は、指定された変数間の関係を表すため、これらの変数の将来の動作を予測することができます。 変数とは、人間の行動を含むさまざまな周期的現象です。 この分析 Excelプログラム 1 つまたは複数の変数の値が特定の従属変数に与える影響を分析するために使用されます。 たとえば、店舗の売上は、品揃え、価格、店舗の場所などのいくつかの要因によって影響されます。 Excel の回帰機能により、既存の売上実績に基づいて各要因の影響度を判断し、得られたデータを次の月や近隣の別の店舗の売上予測に適用できます。
通常、回帰は次のように表されます。 単純な方程式これにより、2 つの変数グループ間の依存関係とつながりの強さが明らかになります。一方のグループは依存性または内生的であり、もう一方のグループは独立または外生的です。 相互に関連する指標のグループがある場合、従属変数 Y は推論のロジックに基づいて決定され、残りは独立した X 変数として機能します。
回帰モデルを構築する主なタスクは次のとおりです。
- 重要な独立変数 (X1、X2、...、Xk) の選択。
- 機能の種類を選択します。
- 係数の推定値を構築します。
- 工事 信頼区間と回帰関数。
- 計算された推定値と構築された回帰式の有意性を確認します。
回帰分析にはいくつかの種類があります。
- ペア (1 つの従属変数と 1 つの独立変数)。
- 複数 (複数の独立変数)。
回帰式には次の 2 種類があります。
- 線形: 変数間の厳密な線形関係を示します。
- 非線形 - べき乗、分数、三角関数を含む方程式。
モデルの構築手順
Excel で特定の構築を実行するには、次の手順に従う必要があります。
さらに計算するには、「Linear()」関数を使用して、Y 値、X 値、定数、および統計を指定します。 この後、「トレンド」関数を使用して、回帰直線上の点のセット (Y 値、X 値、新しい値、定数) を決定します。 指定されたパラメーターを使用して、問題の指定された条件に基づいて係数の未知の値を計算します。
回帰分析は、統計調査の最も一般的な方法の 1 つです。 これは、従属変数に対する独立変数の影響の程度を確立するために使用できます。 機能的には マイクロソフトエクセルこの種の分析を実行するために設計されたツールがあります。 それらが何であるか、そしてそれらをどのように使用するかを見てみましょう。
分析パッケージの接続
ただし、回帰分析を行う機能を使用するには、まず分析パッケージを有効にする必要があります。 そうすることで初めて、この手順に必要なツールが Excel リボンに表示されます。
- 「ファイル」タブに移動します。
- 「設定」セクションに移動します。
- ウィンドウが開きます Excelの設定。 「アドオン」サブセクションに移動します。
- 開いたウィンドウの一番下にある「管理」ブロックのスイッチが「Excel アドイン」の位置にある場合は、それを移動します。 「実行」ボタンをクリックします。
- 利用可能な Excel アドインのウィンドウが開きます。 「分析パッケージ」の横にあるチェックボックスをオンにします。 「OK」ボタンをクリックします。
ここで、「データ」タブに移動すると、「分析」ツール ブロックのリボンに新しいボタン「データ分析」が表示されます。
回帰分析の種類
回帰にはいくつかの種類があります。
- 放物線状。
- 鎮静する。
- 対数;
- 指数関数的;
- 実証的な;
- 双曲線。
- 線形回帰。
Excel で最後のタイプの回帰分析を実行する方法については、後ほど詳しく説明します。
Excel での線形回帰
以下は、例として、対応する営業日の毎日の外気の平均気温と店舗の顧客数を示す表です。 回帰分析を使用して正確にどのように行われるかを調べてみましょう 天気気温という形で、小売店の来店客数に影響を与える可能性があります。
一般的な線形回帰式は次のとおりです: Y = a0 + a1x1 +…+ akhk。 この式では、Y は要因の影響を調査しようとしている変数を意味します。 私たちの場合、これは購入者の数です。 x の値は、変数に影響を与えるさまざまな要因です。 パラメータ a は回帰係数です。 つまり、特定の要素の重要性を決定するのは彼らです。 インデックス k は、これらの同じ要素の合計数を示します。
分析結果分析
回帰分析の結果は、設定で指定した場所に表形式で表示されます。
主な指標の 1 つは R 二乗です。 モデルの品質を示します。 私たちの場合、この係数は 0.705、つまり約 70.5% です。 これは許容できるレベルの品質です。 0.5 未満の依存関係は不良です。
もう 1 つの重要な指標は、「Y 交差点」行と「係数」列の交差点のセルにあります。 これは、Y がどのような価値を持つかを示します。私たちの場合、これは他のすべての要素を加えた購入者の数です。 ゼロに等しい。 この表では 与えられた値 58.04に相当します。
「変数 X1」列と「係数」列の交点の値は、X に対する Y の依存度を示します。この場合、これは店舗の顧客数の温度への依存度です。 係数 1.31 は、かなり高い影響力指標とみなされます。
ご覧のとおり、使用すると マイクロソフトのプログラム Excelを使えば非常に簡単に回帰分析表を作成できます。 しかし、訓練を受けた人だけが出力データを操作し、その本質を理解できるのです。
問題解決のお手伝いができたことを嬉しく思います。
問題の本質を詳細に説明して、コメントで質問してください。 当社のスペシャリストができるだけ早く回答するよう努めます。
この記事は役に立ちましたか?
線形回帰法を使用すると、一連の順序付きペア (x, y) に最もよく適合する直線を記述することができます。 線形方程式として知られる直線の方程式は次のとおりです。
ŷ - 指定された x の値に対する y の期待値、
x - 独立変数、
a - 直線の y 軸上のセグメント、
b は直線の傾きです。
この概念は、次の図に図示されています。
上の図は、方程式 ŷ =2+0.5x で記述される直線を示しています。 y 切片は、線が y 軸と交差する点です。 この場合、a = 2 です。線の傾き b (線の長さに対する線の上昇の比率) の値は 0.5 です。 正の傾きは、線が左から右に上昇することを意味します。 b = 0 の場合、線は水平です。これは、従属変数と独立変数の間に関係がないことを意味します。 つまり、x の値を変更しても y の値には影響しません。
ŷ と y はよく混同されます。 グラフには、指定された方程式に従って、6 つの順序付けされた点のペアと 1 本の線が表示されます。
この図は、順序ペア x = 2 および y = 4 に対応する点を示しています。 バツ= 2 は ŷ です。 これは次の方程式で確認できます。
ŷ = 2 + 0.5х =2 +0.5(2) =3。
y 値は実際の点を表し、ŷ 値は次を使用して予想される y 値です。 一次方程式与えられた x の値に対して。
次のステップは、順序ペアのセットに最もよく一致する線形方程式を決定することです。これについては、最小二乗法を使用して方程式の形式を決定した前の記事で説明しました。
Excel を使用した線形回帰の定義
Excel に組み込まれている回帰分析ツールを使用するには、アドインを有効にする必要があります。 分析パッケージ。 タブをクリックすると見つかります ファイル -> オプション(2007 以降)、表示されるダイアログ ボックスで オプションエクセルタブに移動します アドオン。フィールド内 コントロール選ぶ アドオンエクセルそしてクリックしてください 行く。表示されるウィンドウで、次のチェックボックスをオンにします。 分析パッケージ、クリック わかりました。
タブ内 データグループで 分析現れる 新しいボタン データ分析。
アドインがどのように機能するかを示すために、男性と女性がバスルームでテーブルを共有する前の記事のデータを使用してみましょう。 空白のシートの列 A と列 B に、バスルームの例のデータを入力します。
タブに移動 データ、グループで 分析クリック データ分析。表示されるウィンドウで データ分析選択する 回帰図に示すように、「OK」をクリックします。
ウィンドウで必要な回帰パラメータを設定します。 回帰、画像に示すように:
クリック わかりました。以下の図は、得られた結果を示しています。
これらの結果は、前の記事で独自の計算を行って得られた結果と一致しています。
回帰分析は、1 つ以上の独立変数に対する特定のパラメーターの依存性を示すことができる統計調査手法です。 コンピューター以前の時代では、特に大量のデータの場合、その使用は非常に困難でした。 今日、Excel で回帰を構築する方法を学んだので、複雑な問題を解決できるようになります。 統計的問題文字通り数分で。 以下は 具体例経済学の分野から。
回帰の種類
この概念自体は、1886 年にフランシス ゴルトンによって数学に導入されました。 回帰が起こる:
- 線形;
- 放物線状。
- 鎮静する。
- 指数関数的;
- 双曲線。
- 実証的な;
- 対数。
例1
6 つの産業企業の平均給与に対する退職チームメンバーの依存性を決定する問題を考えてみましょう。
タスク。 6 社の月次平均を分析しました。 賃金および理由により退職した従業員の数 意のままに。 表形式では次のようになります。
6 つの企業における離職者数の平均給与への依存性を判断するタスクでは、回帰モデルは方程式 Y = a0 + a1×1 +…+аkxk の形式になります。ここで、хi は影響変数、ai です。は回帰係数、k は因子の数です。
このタスクの場合、Y は退職した従業員の指標であり、影響要因は給与であり、これを X で示します。
Excel スプレッドシート プロセッサの機能の使用
Excel で回帰分析を行う前に、既存の表形式データに組み込み関数を適用する必要があります。 ただし、これらの目的には、非常に便利な「Analysis Pack」アドオンを使用することをお勧めします。 これを有効にするには、次のものが必要です。
- 「ファイル」タブから「オプション」セクションに移動します。
- 開いたウィンドウで「アドオン」行を選択します。
- 下の「管理」行の右側にある「実行」ボタンをクリックします。
- 「分析パッケージ」という名前の横にあるボックスにチェックを入れ、「OK」をクリックして操作を確定します。
すべてが正しく行われると、Excel ワークシートの上にある「データ」タブの右側に必要なボタンが表示されます。
Excel での線形回帰
計量経済計算を実行するために必要な仮想ツールがすべて揃ったので、問題の解決を開始できます。 このために：
- 「データ分析」ボタンをクリックします。
- 開いたウィンドウで「回帰」ボタンをクリックします。
- 表示されるタブで、Y (退職従業員の数) と X (従業員の給与) の値の範囲を入力します。
- 「OK」ボタンを押してアクションを確定します。
その結果、プログラムは新しいスプレッドシートに回帰分析データを自動的に入力します。 注記！ Excel では、この目的のために希望の場所を手動で設定できます。 たとえば、Y 値と X 値が配置されているのと同じシートであってもよいし、 新しい本、そのようなデータを保存するために特別に設計されています。
R二乗の回帰結果の分析
Excel では、検討中の例のデータの処理中に取得されるデータは次の形式になります。
まず第一に、R 二乗値に注目する必要があります。 決定係数を表します。 で この例では R-square = 0.755 (75.5%)、つまり、モデルの計算されたパラメーターは、考慮されたパラメーター間の依存性を 75.5% 説明します。 決定係数の値が高いほど、選択したモデルが特定のタスクに適していることになります。 R 二乗値が 0.8 を超える場合、実際の状況を正確に記述していると考えられます。 R 二乗が tcr の場合、線形方程式の自由項の有意性に関する仮説は棄却されます。
自由期間について検討している問題では、Excel ツールを使用して、t = 169.20903、p = 2.89E-12 であることがわかりました。つまり、自由期間の重要性に関する正しい仮説が棄却される確率はゼロです。 。 未知の係数の場合、t=5.79405、p=0.001158。 言い換えれば、未知数の係数の重要性に関する正しい仮説が棄却される確率は 0.12% です。
したがって、結果として得られる線形回帰式は適切であると主張できます。
株式のブロック購入の実現可能性の問題
Excel での重回帰は、同じデータ分析ツールを使用して実行されます。 特定のアプリケーションの問題を考えてみましょう。
NNN 会社の経営陣は、MMM JSC の株式 20% を購入することが適切かどうかを決定する必要があります。 パッケージ（SP）の価格は7000万ドル。 NNNの専門家は同様の取引に関するデータを収集した。 株式ブロックの価値は、数百万米ドルで表される次のようなパラメータに従って評価することが決定されました。
- 買掛金 (VK);
- 年間売上高 (VO)。
- 売掛金（VD）;
- 固定資産コスト (COF)。
さらに、企業の賃金未払い (V3 P) のパラメータ (数千米ドル) が使用されます。
Excel スプレッドシート プロセッサを使用したソリューション
まず、ソース データのテーブルを作成する必要があります。 次のようになります。
- 「データ分析」ウィンドウを呼び出します。
- 「回帰」セクションを選択します。
- 「入力間隔 Y」ボックスに、G 列の従属変数の値の範囲を入力します。
- 「入力間隔 X」ウィンドウの右側にある赤い矢印のアイコンをクリックし、シート上の列 B、C、D、F のすべての値の範囲を強調表示します。
「新しいワークシート」項目にチェックを入れ、「OK」をクリックします。
特定の問題について回帰分析を取得します。
結果と結論の検討
Excel スプレッドシート上に表示された四捨五入されたデータから回帰式を「収集」します。
SP = 0.103*SOF + 0.541*VO – 0.031*VK +0.405*VD +0.691*VZP – 265.844。
より馴染みのある数学的形式では、次のように書くことができます。
y = 0.103*x1 + 0.541*x2 – 0.031*x3 +0.405*x4 +0.691*x5 – 265.844
MMM JSC のデータを表に示します。
これらを回帰式に代入すると、6,472 万米ドルという数字が得られます。 これは、MMM JSC の株式の価値が 7,000 万米ドルにかなり膨らんでいるため、購入する価値がないことを意味します。
ご覧のとおり、Excel スプレッドシートと回帰式を使用することで、非常に具体的なトランザクションの実現可能性に関して情報に基づいた意思決定を行うことが可能になりました。
これで、回帰とは何かがわかりました。 上で説明した Excel の例は、計量経済学の分野における実際的な問題を解決するのに役立ちます。
