利用Excel对相关系数的计算

利用Excel对相关系数的计算计量检定要求客观、公正、科学、准确、可靠。当计量检定过程都按要求完成后，还得严格的按照规程给定的公式进行计算。一些比较繁琐的计算对于计量工作者来说既耗时也容易出错，常常需要反复计算来验证数据的准确性，这在数据后期处理过程中占用了大量的时间和人力。以相关系数的计算为例，测量质量浓度为2.0、4.0、6.0、8.0、10.0g/L的标准溶液的吸光度值分别为0.168、0.335、0.507、0.675、0.837Abs，假如直接用给定的公式进行计算显得相当复杂，本文在此不再加以赘述。NyyNxxNyxxyr2222)()(（1）r——相关系数，x——浓度，y——吸光度值，N——各浓度点数为了更好地解决计算中遇到的实际问题，利用Excel自带的函数公式、图表向导等功能或者自己编辑计算公式进行计算，使得一些比较繁琐的计算变得更简单、快捷、准确。方法1：图表向导法第一步：打开MicrosoftExcel新建工作簿，在A1：A5，B1：B5分别输入浓度值和吸光度值。图1所示图1第二步：选中A1：B5，选择“插入→图表”，在图表类型(c):选择“XY散点图”，子图表类型（T）：选择“平滑线散点图”。图2所示图2第三步：单击下一步，下一步，得到图表选项图。图3所示图3第四步：单击图三所示的“完成”，得到散点系列。图4所示00.10.20.30.40.50.60.70.80.9024681012系列1图4第五步：选中散点系列，右键→添加趋势线，类型：选择“线性（L）”，选项：选择“显示公式（E）”、“显示R平方值（R）”。图5、图6所示图5图6第六步：点击图6的“确定”，得到直线方程y=0.0839x+0.001和R2=0.9999。图7所示y=0.0839x+0.001R2=0.999900.10.20.30.40.50.60.70.80.9024681012系列1线性(系列1)图7由于实际需要的是r值，可以利用公式进行计算开方得到相关系数r=sqrt(0.9999)=0.999950方法2：函数公式法在方法1第一步的基础上直接用Excel函数计算，在C1输入“=CORREL(A1:A5,B1:B5)”。图8所示ABCDE120.168=CORREL(A1:A5,B1:B5)240.335360.507480.6755100.8376789图8按Enter键，在C1得到结果0.999952，即为相关系数r。