利用Excel对相关系数的计算

利用Excel 对相关系数的计算

计量检定要求客观、公正、科学、准确、可靠。当计量检定过程都按要求完成后，还得严格的按照规程给定的公式进行计算。一些比较繁琐的计算对于计量工作者来说既耗时也容易出错，常常需要反复计算来验证数据的准确性，这在数据后期处理过程中占用了大量的时间和人力。

以相关系数的计算为例，测量质量浓度为2.0、4.0、6.0、8.0、10.0g/L 的标准溶液的吸光度值分别为0.168、0.335、0.507、0.675、0.837Abs ，假如直接用给定的公式进行计算显得相当复杂，本文在此不再加以赘述。

⎥⎥⎦

⎤

⎢⎢⎣

⎡-

⎥⎥⎦⎤⎢⎢⎣

⎡-

-

=

∑∑∑∑∑

∑∑N y y N

x x N

y x xy r 2

22

2

)()( （1）

r ——相关系数，x ——浓度，y ——吸光度值， N ——各浓度点数

为了更好地解决计算中遇到的实际问题，利用Excel 自带的函数公式、图表向导等功能或者自己编辑计算公式进行计算，使得一些比较繁琐的计算变得更简单、快捷、准确。

方法1：图表向导法

第一步：打开Microsoft Excel 新建工作簿，在A1：A5，B1：B5分别输入浓度值和吸光度值。 图1所示

图1

第二步：选中A1：B5，选择“插入→图表”，在图表类型(c):选择“XY散点图”，子图表类型（T）：选择“平滑线散点图”。图2所示

图2

第三步：单击下一步，下一步，得到图表选项图。图3所示

图3

第四步：单击图三所示的“完成”，得到散点系列。图4所示

图4

第五步：选中散点系列，右键→添加趋势线，类型：选择“线性（L）”，选项：选择“显示公式（E）”、“显示R平方值（R）”。图5、图6所示

图5

图6

第六步：点击图6的“确定”，得到直线方程y = 0.0839x+0.001和R2=0.9999。图7所示

图7

由于实际需要的是r值，可以利用公式进行计算开方得到相关系数

r=sqrt(0.9999)=0.999950

方法2：函数公式法

在方法1第一步的基础上直接用Excel函数计算，在C1输入“=CORREL(A1:A5,B1:B5)”。图8所示

按Enter键，在C1得到结果0.999952，即为相关系数r。