内容
相关性分析是一种常用的研究方法，用于确定第一个值对第二个值的依赖程度。 电子表格有一个特殊工具，可让您实施此类研究。
相关分析的本质
有必要确定两个不同数量之间的关系。 换句话说，它揭示了值在哪个方向（更小/更大）根据秒的变化而变化。
相关分析的目的
当相关系数的识别开始时，依赖性就建立起来了。 该方法与回归分析不同，因为只有一个指标使用相关性计算。 间隔从 +1 变为 -1。 如果它是正的，那么第一个值的增加有助于第二个值的增加。 如果为负，则第一个值的增加会导致第二个值的减少。 系数越高，一个值对第二个值的影响越强。
重要！ 在第 0 个系数处，数量之间没有关系。
相关系数的计算
让我们分析几个样本的计算。 例如，有表格数据，其中广告促销和销售量的支出按月份在单独的列中描述。 根据表格，我们将找出销售量对广告促销费用的依赖程度。
方法 1：通过函数向导确定相关性
CORREL – 一个允许您执行相关分析的函数。 一般形式—— CORREL(massiv1;massiv2)。 详细说明：
- 有必要选择计划显示计算结果的单元格。 单击位于文本字段左侧的“插入函数”以输入公式。
- 函数向导打开。 在这里你需要找到 CORREL，单击它，然后单击“确定”。
- 参数窗口打开。 在“Array1”行中，您必须输入第一个值的间隔坐标。 在此示例中，这是“销售价值”列。 您只需要选择此列中的所有单元格。 同样，您需要将第二列的坐标添加到“Array1”行。 在我们的示例中，这是广告费用列。
- 输入所有范围后，单击“确定”按钮。
系数显示在我们行动开始时指示的单元格中。 得到的结果是 0,97。 该指标反映了第一个值对第二个值的高度依赖。
方法 2：使用分析工具库计算相关性
还有另一种确定相关性的方法。 这里使用了分析包中的功能之一。 在使用它之前，您需要激活该工具。 详细说明：
- 转到“文件”部分。
- 将打开一个新窗口，您需要在其中单击“设置”部分。
- 点击“附加组件”。
- 我们在底部找到元素“管理”。 在这里，您需要从上下文菜单中选择“Excel加载项”，然后单击“确定”。
- 一个特殊的附加组件窗口已打开。 在“分析包”元素旁边放置一个复选标记。 我们点击“确定”。
- 激活成功。 现在让我们转到数据。 出现“分析”块，您需要在其中单击“数据分析”。
- 在出现的新窗口中，选择“相关”元素并单击“确定”。
- 分析设置窗口出现在屏幕上。 在“输入区间”行中，必须输入参与分析的所有列的范围。 在此示例中，这些是“销售价值”和“广告成本”列。 输出显示设置最初设置为新工作表，这意味着结果将显示在不同的工作表上。 或者，您可以更改结果的输出位置。 完成所有设置后，单击“确定”。
最终成绩出来了。 结果与第一种方法相同 - 0,97。
MS Excel中多重相关系数的定义与计算
为了确定几个量的相关程度，使用了多个系数。 将来，结果将汇总在一个单独的表中，称为相关矩阵。
详细指南：
- 在“数据”部分，我们找到已知的“分析”块，然后单击“数据分析”。
- 在出现的窗口中，单击“相关”元素，然后单击“确定”。
- 在“输入间隔”行中，我们驱动源表的三个或更多列的间隔。 范围可以手动输入或简单地用 LMB 选择它，它会自动出现在所需的行中。 在“分组”中选择适当的分组方法。 在“输出参数”中指定显示关联结果的位置。 我们点击“确定”。
- 准备好！ 建立了相关矩阵。
Excel中的对相关系数
让我们弄清楚如何在 Excel 电子表格中正确绘制对相关系数。
Excel中对相关系数的计算
例如，您有 x 和 y 值。
X 是因变量，y 是自变量。 需要找到这些指标之间关系的方向和强度。 分步说明：
- 让我们使用函数找到平均值 心。
- 让我们计算每个 х и 平均, у и 平均 使用«-»运算符。
- 我们将计算出的差异相乘。
- 我们计算该列中指标的总和。 分子是找到的结果。
- 计算差异的分母 х и x 平均值，y и y-中. 为此，我们将执行平方。
- 使用功能 自动总结，在结果列中找到指标。 我们做乘法。 使用功能 根 平方结果。
- 我们使用分母和分子的值来计算商。
- CORREL 是一个集成功能，可让您防止复杂的计算。 我们进入“函数向导”，选择 CORREL 并指定指标数组 х и у. 我们构建了一个图表来显示获得的值。
Excel中的成对相关系数矩阵
下面我们来分析如何计算成对矩阵的系数。 例如，有一个由四个变量组成的矩阵。
分步说明：
- 我们转到位于“数据”选项卡的“分析”块中的“数据分析”。 从出现的列表中选择相关性。
- 我们设置了所有必要的设置。 “输入间隔”——所有四列的间隔。 “输出间隔”——我们要显示总数的地方。 我们点击“确定”按钮。
- 在选定的地方建立了一个相关矩阵。 行和列的每个交集是一个相关系数。 坐标匹配时显示数字 1。
在 Excel 中确定关系和相关性的 CORREL 函数
CORREL – 用于计算 2 个数组之间的相关系数的函数。 让我们看一下这个函数所有能力的四个例子。
在 Excel 中使用 CORREL 函数的示例
第一个例子。 有一个盘子，上面有关于公司员工在 XNUMX 年内的平均工资和美元汇率的信息。 有必要确定这两个量之间的关系。 该表如下所示：
计算算法如下所示：
显示的分数接近 1。 结果：
动作对结果影响的相关系数的确定
第二个例子。 两名投标人向两个不同的机构寻求帮助，进行为期 XNUMX 天的促销活动。 每天都会进行一次社会民意调查，以确定每个申请人的支持程度。 任何被采访者都可以选择两个申请人之一或全部反对。 有必要确定每个广告促销对申请人的支持程度有多大影响，哪家公司更有效率。
使用下面的公式，我们计算相关系数：
- =相关（A3：A17；B3：B17）。
- =相关（A3：A17；C3：C17）。
结果：
从得到的结果可以看出，随着广告宣传的每一天，对第一个申请人的支持程度都在增加，因此，相关系数接近于1。当广告投放时，另一个申请人有大量的信任，并且对于1天有一个积极的趋势。 然后信任度下降，到了第十五天，它跌破了最初的指标。 低分表明晋升对支持产生了负面影响。 不要忘记，未以表格形式考虑的其他伴随因素也可能影响指标。
通过视频观看和转发的相关性分析内容流行度
第三个例子。 在 YouTube 视频托管上宣传自己的视频的人使用社交网络来宣传该频道。 他注意到社交网络中的转发数量与频道上的观看次数之间存在某种关系。 是否可以使用电子表格工具预测未来的表现？ 有必要确定应用线性回归方程根据转发次数预测视频观看次数的合理性。 带值的表：
现在有必要根据以下公式确定2个指标之间是否存在关系：
0,7;IF(CORREL(A3:A8;B3:B8)>0,7;"强正相关";"强逆关系");"弱关系或无关系")' class='公式'>
如果得到的系数高于 0,7，则更适合使用线性回归函数。 在这个例子中，我们这样做：
现在我们正在构建一个图表：
我们应用这个等式来确定 200、500 和 1000 股的观看次数： =9,2937*D4-206,12。 我们得到以下结果：
功能 预测 允许您确定当前的观看次数，例如，如果有 XNUMX 次转发。 我们申请： 0,7;PREDICTION(D7;B3:B8;A3:A8);"数值不相关")' class='公式'>. 我们得到以下结果：
Excel中使用CORREL函数的特点
该功能具有以下特点：
- 不考虑空单元格。
- 不考虑包含布尔和文本类型信息的单元格。
- 双重否定“-”用于以数字的形式说明逻辑值。
- 研究数组中的单元格数量必须匹配，否则将显示 #N/A 消息。
评估相关系数的统计显着性
在检验相关系数的显着性时，原假设是该指标的值为 0，而备择假设不是。 以下公式用于验证：
结论
电子表格中的相关性分析是一个简单且自动化的过程。 要执行它，您只需要知道必要工具的位置以及如何通过程序设置激活它们。