16.14
Microsoft Excel: Pearsonの相関
Microsoft Excel は、2 つの連続変数間の線形関係の強さと方向を測定するPearsonの相関係数の計算を含む、統計分析のための強力なツールです。Pearsonの相関係数は、通常「r」と表記され、-1 から 1 の範囲です。1 に近い値は強い正の相関を示し、一方の変数が増加すると、もう一…
相関は、2つの変数間の関係です。Microsoft Excel で整理された次の架空のデータについて考えてみます。
単変量相関を実行するには、X変数を列に配置し、Y変数をその右側の列に配置する必要があります。
これら 2 つの変数をプロットするには、まず挿入タブで両方の列を選択し、グラフを見つけて、散布図を選択します。
トレンドラインなどの他の要素もチャートデザインに追加できます。
データが正規分布の母集団からのものであると仮定すると、ピアソンの相関係数 (r) は、データに対して選択された関数 CORREL を使用して計算されます。
別の関数 PEERSON も同じ値を戻します。
これらの関数は、2 つの変数間の線形関係の強度と方向を測定します。この場合、0.985であり、強く正の相関関係があることを示唆しています。
関数 RSQ は、r の 2 乗値を返します。この値は、独立変数から予測可能な従属変数の分散の割合を測定する決定係数です。
View the full transcript and gain access to JoVE Core videos
Q1: What does Pearson's correlation coefficient measure?
Pearson's correlation coefficient, denoted as r, measures the strength and direction of the linear relationship between two continuous variables. The coefficient ranges from -1 to 1, where values close to 1 indicate strong positive correlation, values near -1 indicate strong negative correlation, and values around 0 suggest no linear relationship between the variables.
Q2: How do you calculate Pearson's correlation in Microsoft Excel?
In Microsoft Excel, use the CORREL function to calculate Pearson's correlation coefficient. Enter the formula =CORREL(array1, array2), where array1 and array2 represent your two data columns. For example, =CORREL(A1:A10, B1:B10) calculates correlation between variables in cells A1:A10 and B1:B10. The PEARSON function returns the same result.
Q3: How should data be organized in Excel before calculating correlation?
To perform correlation analysis in Excel, arrange the X variable in one column and the Y variable in an adjacent column to its right. Select both columns together, then use the CORREL function or create a scatter plot through the Insert tab to visualize the relationship between your two variables.
Q4: What does the coefficient of determination (R²) tell you?
The coefficient of determination, calculated using the RSQ function in Excel, represents the squared value of the correlation coefficient r. This value measures the proportion of variance in the dependent variable that is predictable from the independent variable, indicating how well the linear model explains the data variation.
Q5: Can you visualize correlation relationships in Excel?
Yes, you can create a scatter plot in Excel to visualize the correlation between two variables. Select both data columns, go to the Insert tab, choose Charts, and select a scatter plot. You can enhance the visualization by adding trendlines and displaying the equation, which helps you observe how closely data points fit a straight line.
Q6: Why is correlation different from causation?
Correlation measures the strength of a linear relationship between two variables but does not imply that one variable causes changes in the other. Two variables can have high correlation without a causal relationship. Additionally, Pearson's correlation captures only linear relationships and may miss complex, non-linear associations between variables.
Q7: What advanced correlation analysis options does Excel provide?
Excel offers the Data Analysis Toolpak add-on, which provides correlation matrices for analyzing relationships across multiple variables simultaneously. This tool makes it easier to compare correlations across datasets and conduct more robust statistical analysis beyond simple pairwise correlation calculations between two variables.