家水供給 MS Excel での重相関係数の決定。 Lab_7 相関分析

水供給

MS Excel での重相関係数の決定。 Lab_7 相関分析

1.Excelを開く

2.データ列を作成します。この例では、1 年生の攻撃性と自己不信の関係、または相関関係を検討します。 30 人の子供が実験に参加しました。データは Excel の表に表示されます。

1列 - 件名番号

2列 - 攻撃性ポイント単位で

3列 - 自信がないポイント単位で

3.次に、表の隣の空のセルを選択し、アイコンをクリックする必要があります。 f(x) Excelパネルで

4.機能メニューが開きます。カテゴリの中から選択する必要があります。 統計的 、そして関数のリストの中からアルファベット順に見つけます。 コレルそして「OK」をクリックします

5.関数の引数のメニューが開き、必要なデータ列を選択できます。最初の列を選択するには 攻撃性行の隣にある青いボタンをクリックする必要があります 配列1

6.データの選択 配列1コラムから 攻撃性ダイアログボックスの青いボタンをクリックします。

7. 次に、配列 1 と同様に、行の横にある青いボタンをクリックします。 配列2

8.データの選択 配列2- カラム 自信のなさもう一度青いボタンを押し、「OK」を押します。

9. ここで、r-ピアソン相関係数が計算され、選択したセルに書き込まれています。この場合、それは正であり、ほぼ等しくなります。 0,225 。これはについて話します 中等度のポジティブ 1年生の攻撃性と自信喪失との関係

したがって、 統計的推論実験は次のようになります: r = 0.225、変数間の適度な正の関係が明らかになりました。 攻撃性そして 自信。

一部の研究では、相関係数の p レベルの有意性を指定する必要がありますが、エクセルプログラムは、SPSS とは異なり、そのようなオプションを提供しません。大丈夫、（A.D.ナスレドフ）はいるよ。

研究結果に添付することもできます。

複数の指標間の依存度を判断するには、複数の相関係数が使用されます。これらはその後、相関行列と呼ばれる別の表にまとめられます。このような行列の行と列の名前は、相互に依存関係が確立されているパラメータの名前です。行と列の交差点に、対応する相関係数が配置されます。 Excel ツールを使用して同様の計算を行う方法を見てみましょう。

相関係数に応じて、さまざまな指標間の関係のレベルを次のように決定するのが通例です。

0 – 0.3 – 接続なし。
0.3 – 0.5 – 接続が弱い。
0.5 – 0.7 – 平均的な接続。
0.7 – 0.9 – 高;
0.9 – 1 – 非常に強い。

相関係数が負の場合、パラメータ間の関係が逆であることを意味します。

Excel で相関行列を作成するには、パッケージに含まれる 1 つのツールを使用します。 "データ分析"。それがそう呼ばれています - "相関"。これを使用して複数の相関メトリクスを計算する方法を学びましょう。

ステップ 1: 分析パッケージをアクティブ化する

すぐに言っておきたいのは、デフォルトのパッケージ "データ分析"無効。したがって、相関係数を直接計算する手順に進む前に、相関係数を有効にする必要があります。残念ながら、すべてのユーザーがこれを行う方法を知っているわけではありません。したがって、この問題については詳しく説明します。

指定されたアクションの後、ツールパッケージ "データ分析"が活性化されます。

ステージ 2: 係数の計算

これで、直接計算に進むことができます多重係数相関関係。以下の表を労働生産性、資本労働比率、エネルギー労働比率の指標の例として使用してみましょう。さまざまな企業これらの要素の重相関係数を計算してみましょう。

ステージ 3: 得られた結果の分析

次に、ツールを使用したデータ処理の過程で受け取った結果を理解する方法を考えてみましょう。 "相関"エクセルで。

表からわかるように、資本労働比率の相関係数は (列2) とエネルギー利用可能性 ( 列 1) は 0.92 で、これは非常に強い関係に相当します。労働生産性（ 列 3) とエネルギー利用可能性 ( 列 1) この指標は 0.72 であり、依存性の度合いが高くなります。労働生産性との相関係数（ 列 3) および資本労働比率 ( 列 2) は 0.88 に等しく、これも高い依存度に相当します。したがって、研究されたすべての要因間の関係は非常に強いと言えます。

パッケージはご覧のとおり、 "データ分析" Excel のは、重相関係数を求めるための非常に便利でかなり使いやすいツールです。これを利用すると、2 つの要素間の通常の相関関係を計算することもできます。

ラテン語から翻訳された「相関」は「相関」、「相互接続」を意味します。相関係数を計算することで、関係の定量的な特性を得ることができます。この係数は統計分析でよく使われ、特定のパラメーター (身長と体重、知能レベルと学力、怪我の数と労働時間など) が相互に関連しているかどうかを示します。

相関関係の使用

相関計算は、経済学、社会学研究、医学、生体認証など、関係性が見出せる 2 セットのデータを取得できるあらゆる分野で特に広く使用されています。

簡単な算術演算を実行することで相関関係を手動で計算できます。ただし、データセットが大きい場合、計算プロセスには非常に時間がかかることがわかります。この方法の特徴は、収集が必要なことです。大量特性間に関係があるかどうかを最も正確に反映するためにソースデータを作成します。したがって、相関分析を本格的に使用するには、コンピューター技術を使用する必要があります。この問題を解決するための最も人気があり、アクセスしやすいプログラムの 1 つは、次のとおりです。

Excelで相関関係を実行するにはどうすればよいですか?

相関関係を判断する際に最も時間のかかるステップは、データ配列の収集です。比較されるデータは通常、2 つの列または行に配置されます。表はセルに隙間がないように作成する必要があります。最新バージョン Excel（2007以前）はインストール不要追加の設定統計計算用。必要な操作を行うことができます。

計算結果を表示する空のセルを選択します。
Excelのメインメニューで「数式」をクリックします。
「機能ライブラリ」にまとめられたボタンの中から「その他の機能」を選択します。
ドロップダウンリストで、相関計算関数 (統計 - CORREL) を選択します。
Excel で「関数の引数」パネルが開きます。「配列 1」と「配列 2」は比較されるデータの範囲です。これらのフィールドに自動的に入力するには、必要な表のセルを選択するだけです。
「OK」をクリックして関数の引数ウィンドウを閉じます。計算された相関係数がセルに表示されます。

相関関係は、直接的 (係数がゼロより大きい場合) と逆相関 (-1 から 0 まで) の場合があります。

1 つ目は、一方のパラメータが増加すると、他方のパラメータも増加することを意味します。逆（負の）相関は、一方の変数が増加すると、もう一方の変数が減少するという事実を反映しています。

相関関係はゼロに近い可能性があります。これは通常、調査対象のパラメータが互いに関連していないことを示します。ただし、関係を反映していないサンプルが作成された場合、または関係が複雑で非線形である場合、相関関係がゼロになることがあります。

係数が中程度または強い関係 (±0.5 ～ ±0.99) を示している場合、これは単なる統計的な関係であり、あるパラメーターが別のパラメーターに与える影響をまったく保証するものではないことに注意してください。両方のパラメータが互いに独立しているが、何らかの第 3 の説明されていない要因の影響を受けるという状況を排除することもできません。 Excel を使用すると相関係数を即座に計算できますが、通常、定量的手法だけでは相関サンプルの因果関係を確立するのに十分ではありません。

多くの企業や企業で広く使用されているユーティリティです。 Excel プログラムは非常に幅広い問題の解決に使用されるため、実際には、ほとんどすべての従業員がある程度 Excel に習熟している必要があります。テーブルを操作するとき、特定の変数が相互に関連しているかどうかを判断する必要があることがよくあります。この目的のために、いわゆる相関が使用されます。この記事では、Excelで相関係数を計算する方法を詳しく説明します。それを理解しましょう。行く！

相関係数とは一般的に何なのかから始めましょう。これは 2 つの要素間の関係の度合いを示し、常に -1 (強い逆関係) から 1 (強い順関係) の範囲になります。係数が 0 の場合、値の間に関係がないことを示します。

さて、理論を説明したので、実践に移りましょう。変数と y の関係を調べるには、Microsoft Excel の組み込み関数「CORREL」を使用します。これを行うには、関数ウィザードボタン (数式フィールドの横にあります) をクリックします。開いたウィンドウで、関数のリストから「CORREL」を選択します。その後、「Array1」と「Array2」フィールドに範囲を設定します。たとえば、「Array1」の場合は y 値を選択し、「Array2」の場合は x 値を選択します。その結果、プログラムによって計算された相関係数が得られます。

次の方法は、特定の公式を使用して依存関係を見つける必要がある学生に適しています。まず、変数 x と y の平均値を知る必要があります。これを行うには、変数値を選択し、「AVERAGE」関数を使用します。次に、各 x と x avg および y avg の差を計算する必要があります。選択したセルに次のように書き込みます式 x ～ x、よー。平均値を含むセルを固定することを忘れないでください。次に、式を下に拡張して、残りの数値に適用します。

必要なデータがすべて揃ったので、相関関係を計算できます。結果として生じる差を次のように乗算します: (x-x avg) * (y-y avg)。各変数の結果を取得したら、オート Sum 関数を使用して結果の数値を加算します。これが分子の計算方法です。

さて、分母に移りましょう。計算された差は二乗する必要があります。これを行うには、別の列に式 (x-x avg) 2 および (y-y avg) 2 を入力します。次に、数式を範囲全体に拡張します。次に、「AutoSum」ボタンを使用して、すべての列 (x と y) の合計を求めます。見つかった合計を掛け合わせてそこから抽出することが残っています平方根。最後のステップは、分子を分母で割ることです。得られる結果は、目的の相関係数になります。

ご覧のとおり、Microsoft Excel 関数を正しく操作する方法を知っていれば、難しい計算作業を大幅に簡素化できます。数式。プログラムに実装されたツールのおかげで、Excel で相関分析をわずか数分で簡単に実行でき、時間と労力を節約できます。この記事が問題の理解に役立ったかどうかをコメントに書き、議論されたトピックについて興味を持ったことすべてについて質問してください。

相関接続ありある特性の同じ値は、別の特性の異なる値に対応します。たとえば、身長と体重、悪性新生物の発生率と年齢などの間には相関関係があります。

相関係数を計算するには、二乗法 (Pearson) と順位法 (Spearman) の 2 つの方法があります。

最も正確なのは二乗法 (ピアソン) で、相関係数は次の式で求められます。

r xy は、統計系列 X と Y の間の相関係数です。

d x は、統計系列 X の各数値の算術平均からの偏差です。

d y は、統計系列 Y の各数値の算術平均からの偏差です。

接続の強さと方向に応じて、相関係数は 0 から 1 (-1) の範囲になります。相関係数 0 は、接続が完全に欠如していることを示します。相関係数のレベルが 1 または (-1) に近づくほど、それに応じて測定される直接またはフィードバックがより大きく、より厳密になります。相関係数が 1 または (-1) の場合、接続は完了し、機能しています。

相関係数を用いて相関の強さを評価するスキーム

つながりの力	相関係数の値 (利用可能な場合)
つながりの力	直結（＋）	フィードバック (-)
接続がありません
接続が小さい（弱い）	0から+0.29まで	0から-0.29まで
平均接続 (中程度)	+0.3から+0.69まで	–0.3 ～ –0.69
つながりは大きい（強い）	+0.7から+0.99まで	–0.7 ～ –0.99
完全なコミュニケーション (機能的)

二乗法を使用して相関係数を計算するには、7 列のテーブルが作成されます。例を使用して計算プロセスを見てみましょう。

間の接続の強さと性質を決定する

時間です- らしさ甲状腺腫 (V y )	d x = V バツ –M バツ	d y= V y –M y	d バツ d y	d バツ 2	d y 2







			Σ -1345 ,0	Σ 13996 ,0	Σ 313 , 47

1. 水中の平均ヨウ素含有量 (mg/l) を測定します。

mg/l

2. 甲状腺腫の平均発生率を%で求めます。

3. M x からの各 V x の偏差を決定します。つまり、 DX。

201–138=63; 178 – 138 = 40 など

4. 同様に、M y からの各 V y の偏差を決定します。よ。

0.2–3.8=-3.6; 0.6～38=-3.2など

5. 偏差の積を決定します。結果の積を合計して取得します。

6. d x を二乗し、結果を合計すると、次の結果が得られます。

7. 同様に、d y を二乗し、結果を合計すると、次のようになります。

8. 最後に、受け取ったすべての金額を次の式に代入します。

相関係数の信頼性の問題を解決するには、次の式を使用して平均誤差を決定します。

(観測値の数が 30 未満の場合、分母は n–1 になります)。

私たちの例では

相関係数の値が平均誤差の 3 倍以上であれば、その値は信頼できると見なされます。

私たちの例では

したがって、相関係数は信頼できないため、観測値の数を増やす必要があります。

相関係数は、精度は若干劣りますが、より簡単な方法であるランク付け法 (Spearman) で決定できます。

スピアマン法: P=1-(6∑d 2 /n-(n 2 -1))

ペアになった比較可能な特徴の 2 行を作成し、最初と 2 行目をそれぞれ x と y に指定します。この場合、特性の最初の行を降順または昇順で表示し、2 行目の数値を対応する最初の行の値の反対側に配置します。

比較される各シリーズの特性の値をシリアル番号 (ランク) に置き換えます。ランク、または数値は、1 行目と 2 行目の指標 (値) の位置を示します。その中で数値 2 番目の特性の場合、ランクは最初の特性の値に割り当てるときに採用されたのと同じ順序で割り当てる必要があります。一連の特性の同じ値については、これらの値のシリアル番号の合計から平均値としてランクを決定する必要があります。

x と y の間のランクの差を決定します (d): d = x - y

結果の順位差を二乗します (d 2)

差の二乗和 (Σ d 2) を取得し、結果の値を式に代入します。

例：以下のデータが得られた場合、ランク法を使用して、勤務経験年数と負傷の頻度との関係の方向性と強さを確立します。

この方法を選択する理由:問題を解決するには方法しか選べない順位相関、なぜなら属性「勤務経験年数」の最初の行には、オプションを開く(実務経験は 1 年まで、7 年以上)、比較される特性間の関連性を確立するために、より正確な方法 (二乗法) を使用することはできません。

解決。計算の順序は本文に示され、結果は表に示されます。 2.

表2

長年の実務経験	負傷者数	序数(順位)	ランク差	順位の差の二乗
長年の実務経験	負傷者数		d(x-y)	d 2

ペアになった特性の各行は、「x」と「y」で指定されます (列 1 ～ 2)。

各符号の値はランク (順序) 番号に置き換えられます。「x」行のランクの分布順序は次のとおりです。属性の最小値 (経験年数 1 年まで) にはシリアル番号「1」が割り当てられ、同じ行の属性の後続のバリアントにはそれぞれ、昇順、2 番目、3 番目、4 番目、5 番目のシリアル番号 - ランク (列 3 を参照)。 2 番目の属性「y」(列 4) にランクを分配するときも、同様の順序に従います。同じ大きさの選択肢が複数ある場合 (たとえば、標準的な問題では、経験年数 3 ～ 4 年と 5 ～ 6 年の労働者 100 人当たりの負傷数が 12 件と 12 件である場合、シリアル番号は平均数で指定されます)これらのシリアル番号の合計から、ランク付けすると、負傷者数 (12 人) のデータが 2 位と 3 位を占めるはずなので、平均数は (2 + 3)/2 = 2.5 になります。負傷の数は「12」と「12」（属性））、同じランク番号「2.5」（列 4）が配分される必要があります。

ランクの差を決定します d = (x - y) - (列 5)

ランク差 (d 2) を二乗し、ランク差の二乗和 Σ d 2 を取得します (列 6)。

次の式を使用して順位相関係数を計算します。

ここで、n は行「x」と行「y」で比較されるオプションのペアの数です。