内容
相関分析は、1 番目の値の 2 番目の値への依存度を判断するために使用される一般的な調査方法です。 スプレッドシートには、この種の調査を実施できる特別なツールがあります。
相関分析の本質
XNUMX つの異なる量の間の関係を決定する必要があります。 つまり、秒単位の変化によって値がどちらの方向（小さい/大きい）に変化するかがわかります。
相関分析の目的
相関係数の同定が始まると、依存関係が確立されます。 この方法は、相関を使用して計算される指標が 1 つしかないため、回帰分析とは異なります。 間隔は +1 から -2 に変化します。 正の場合、最初の値の増加は 1 番目の値の増加に寄与します。 負の場合、2 番目の値の増加は XNUMX 番目の値の減少に寄与します。 係数が高いほど、一方の値が他方に影響を与えます。
重要な！ 0 番目の係数では、数量間に関係はありません。
相関係数の計算
いくつかのサンプルで計算を分析してみましょう。 たとえば、広告プロモーションへの支出と販売量が別の列に月ごとに記述されている表形式のデータがあります。 この表に基づいて、販売量の広告宣伝費への依存度を調べます。
方法 1: Function Wizard による相関の決定
CORREL – 相関分析を実装できる機能。 一般形 – CORREL(massiv1;massiv2)。 詳細な手順：
- 計算結果を表示する予定のセルを選択する必要があります。 テキストフィールドの左側にある「関数の挿入」をクリックして、式を入力します。
- 関数ウィザードが開きます。 ここで見つける必要があります CORRELをクリックし、「OK」をクリックします。
- 引数ウィンドウが開きます。 「Array1」行に、最初の値の間隔の座標を入力する必要があります。 この例では、これは Sales Value 列です。 この列にあるすべてのセルを選択するだけです。 同様に、1 番目の列の座標を「Array2」行に追加する必要があります。 この例では、これは Advertising Costs 列です。
- すべての範囲を入力したら、「OK」ボタンをクリックします。
係数は、アクションの最初に示されたセルに表示されました。 得られた結果は0,97です。 この指標は、最初の値が XNUMX 番目の値に大きく依存していることを反映しています。
方法 2: 分析ツールを使用して相関関係を計算する
相関関係を判断する別の方法があります。 ここでは、分析パッケージにある関数の XNUMX つが使用されます。 使用する前に、ツールをアクティブ化する必要があります。 詳細な手順:
- 「ファイル」セクションに移動します。
- 「設定」セクションをクリックする必要がある新しいウィンドウが開きます。
- 「アドオン」をクリックします。
- 一番下に「管理」という要素があります。 ここで、コンテキスト メニューから [Excel アドイン] を選択し、[OK] をクリックする必要があります。
- 特別なアドオン ウィンドウが開きました。 「分析パッケージ」要素の横にチェックマークを付けます。 「OK」をクリックします。
- 有効化に成功しました。 それでは、データに行きましょう。 「分析」ブロックが表示され、「データ分析」をクリックする必要があります。
- 表示される新しいウィンドウで、「相関」要素を選択し、「OK」をクリックします。
- 解析設定ウィンドウが画面に表示されました。 「入力間隔」の行では、分析に参加する絶対にすべての列の範囲を入力する必要があります。 この例では、これらは列「売上高」と「広告費」です。 出力表示設定は、最初は [新しいワークシート] に設定されています。これは、結果が別のシートに表示されることを意味します。 必要に応じて、結果の出力場所を変更できます。 すべての設定が完了したら、「OK」をクリックします。
最終スコアが出ました。 結果は最初の方法と同じで、0,97 です。
MS Excel での重相関係数の定義と計算
複数の量の依存度を識別するために、複数の係数が使用されます。 今後、結果は相関行列と呼ばれる別の表にまとめられます。
詳細ガイド：
- 「データ」セクションで、既知の「分析」ブロックを見つけて、「データ分析」をクリックします。
- 表示されるウィンドウで、「相関」要素をクリックし、「OK」をクリックします。
- 行「入力間隔」では、ソース テーブルの XNUMX つ以上の列の間隔を駆動します。 範囲は手動で入力するか、LMB で選択するだけで、目的の行に自動的に表示されます。 「グループ化」で、適切なグループ化方法を選択します。 「出力パラメータ」では、相関結果が表示される場所を指定します。 「OK」をクリックします。
- 準備！ 相関行列が構築されました。
Excelでのペア相関係数
Excel スプレッドシートでペア相関係数を正しく描画する方法を考えてみましょう。
Excelでのペア相関係数の計算
たとえば、x 値と y 値があります。
X は従属変数で、y は独立変数です。 これらの指標間の関係の方向性と強さを見つける必要があります。 ステップバイステップの説明:
- 関数を使って平均値を求めましょう ハート。
- それぞれ計算してみましょう х и xavg, у и 平均 «-» 演算子を使用します。
- 計算された差を掛けます。
- この列の指標の合計を計算します。 分子は見つかった結果です。
- 差の分母を計算する х и x 平均、y и y-中. これを行うには、二乗を実行します。
- 関数の使用 オートスマ、結果の列でインジケーターを見つけます。 乗算を行います。 機能の使用 ROOT 結果を二乗します。
- 分母と分子の値を使用して商を計算します。
- CORREL は、複雑な計算を回避できる統合関数です。 「関数ウィザード」に移動し、CORREL を選択して、指標の配列を指定します。 х и у. 得られた値を表示するグラフを作成します。
Excel のペアワイズ相関係数の行列
ペア行列の係数を計算する方法を分析しましょう。 たとえば、XNUMX つの変数の行列があります。
ステップバイステップの説明：
- 「データ」タブの「分析」ブロックにある「データ分析」に移動します。 表示されるリストから相関関係を選択します。
- 必要なすべての設定を行います。 「入力間隔」 – XNUMX つの列すべての間隔。 「出力間隔」 – 合計を表示する場所。 「OK」ボタンをクリックします。
- 選択した場所に相関行列が作成されました。 行と列の各交点が相関係数です。 座標が一致すると数字の 1 が表示されます。
Excelで関係と相関関係を判断するCORREL関数
CORREL – 2 つの配列間の相関係数を計算するために使用される関数。 この関数のすべての機能の XNUMX つの例を見てみましょう。
Excel での CORREL 関数の使用例
最初の例。 会社の従業員の XNUMX 年間の平均給与とドルの為替レートに関する情報が記載されたプレートがあります。 この XNUMX つの量の関係を特定する必要があります。 テーブルは次のようになります。
計算アルゴリズムは次のようになります。
表示されるスコアは 1 に近いです。結果:
結果に対するアクションの影響の相関係数の決定
XNUMX番目の例。 XNUMX 人の入札者が、XNUMX 日間のプロモーションの支援を求めて XNUMX つの異なる代理店にアプローチしました。 毎日社会調査が行われ、各応募者の支持度が決定されました。 面接対象者は、XNUMX 人の応募者のうちの XNUMX 人を選択することも、全員に反対することもできます。 それぞれの広告宣伝が応募者の支持率にどの程度影響したか、どちらの会社がより効率的かを判断する必要があります。
以下の式を使用して、相関係数を計算します。
- =CORREL(A3:A17;B3:B17)。
- =CORREL(A3:A17;C3:C17)。
結果について
得られた結果から、第1志願者への支持度は広告宣伝を行う日を追うごとに増加し、相関係数は1に近づくことが明らかになりました。 5日間はプラスの傾向がありました。 その後、信頼度は低下し、XNUMX 日目までには当初の指標を下回りました。 低いスコアは、プロモーションがサポートに悪影響を及ぼしていることを示しています。 表形式では考慮されていない他の付随要因も指標に影響を与える可能性があることを忘れないでください。
動画の再生回数と再投稿の相関関係によるコンテンツの人気度の分析
XNUMX番目の例。 YouTube ビデオ ホスティングで自分のビデオを宣伝する人は、ソーシャル ネットワークを使用してチャンネルを宣伝します。 彼は、ソーシャル ネットワークでの再投稿数とチャンネルでのビュー数の間に何らかの関係があることに気付きました。 スプレッドシート ツールを使用して将来のパフォーマンスを予測することは可能ですか? 再投稿数に応じた動画再生数の予測に線形回帰式を適用することの妥当性を確認する必要があります。 値を持つテーブル:
以下の式に従って、2 つの指標間の関係の存在を判断する必要があります。
0,7;IF(CORREL(A3:A8;B3:B8)>0,7;"強い直接関係";"強い逆関係");"弱いまたは関係なし")' class='式'>
結果の係数が 0,7 より大きい場合は、線形回帰関数を使用する方が適切です。 この例では、次のことを行います。
今、グラフを作成しています:
この式を適用して、200、500、および 1000 シェアでのビュー数を決定します。 =9,2937*D4-206,12、XNUMX。 次の結果が得られます。
演算 予報 たとえば、XNUMX回の再投稿があった場合、現時点でのビュー数を判断できます。 私たちは適用します： 0,7;PREDICTION(D7;B3:B8;A3:A8);「値は関係ありません」)' class='式'>. 次の結果が得られます。
ExcelでCORREL関数を使用する機能
この機能には次の特徴があります。
- 空のセルは考慮されません。
- ブール型およびテキスト型の情報を含むセルは考慮されません。
- 二重否定「-」は、数値の形式で論理値を説明するために使用されます。
- 調査対象のアレイ内のセルの数は一致する必要があります。そうでない場合、#N/A メッセージが表示されます。
相関係数の統計的有意性の評価
相関係数の有意性を検定する場合、帰無仮説は指標の値が 0 であるのに対し、対立仮説はそうではないというものです。 検証には次の式が使用されます。
まとめ
スプレッドシートでの相関分析は、シンプルで自動化されたプロセスです。 それを実行するには、必要なツールがどこにあるか、プログラム設定でそれらをアクティブにする方法を知るだけで済みます。