Excel具有内置功能,您可以使用这些功能来显示校准数据并计算最佳拟合直线。当您正在编写化学实验报告或将校正因子编程到一台设备中时,这会很有帮助。
在本文中,我们将介绍如何使用Excel创建图表,绘制线性校准曲线,显示校准曲线的公式,然后使用斜率和截距函数设置简单的公式,以便在Excel中使用校准方程。
什么是校准曲线?创建校准曲线时Excel有何用处?
要执行校准,您可以将设备的读数(如温度计显示的温度)与称为标准的已知数值(如水的冰点和沸点)进行比较。这使您可以创建一系列数据对,然后使用这些数据对来开发校准曲线。
使用水的冰点和沸点对温度计进行两点校准将有两个数据对:一个是温度计放在冰水(32°F或0°C)中时的数据对,另一个是放在沸水(212°F或100°C)中时的数据对。当您将这两个数据对绘制为点并在它们之间画一条线(校准曲线)时,然后假设温度计的响应是线性的,您可以选择线上与温度计显示的值相对应的任何点,您可以找到相应的“真”温度。
因此,这条线基本上是在为您填充两个已知点之间的信息,这样当温度计读数为57.2度,但您从未测量过与该读数对应的“标准”时,您可以合理地确定实际温度。
Excel的功能允许您在图表中以图形方式绘制数据对,添加趋势线(校准曲线),并在图表上显示校准曲线的公式。这对于直观显示很有用,但您也可以使用Excel的斜率和截取函数计算直线的公式。当您将这些值输入到简单公式中时,您将能够根据任何测量值自动计算“真”值。
让我们看一个例子
对于本例,我们将从一系列10个数据对中开发校准曲线,每个数据对由一个X值和一个Y值组成。X值将是我们的“标准”,它们可以代表任何东西,从我们用科学仪器测量的化学溶液的浓度,到控制大理石发射机的程序的输入变量。
Y值将是“响应”,它们将代表仪器在测量每个化学溶液时提供的读数,或者使用每个输入值测量的弹珠与发射器之间的距离。
在我们以图形方式描绘校准曲线之后,我们将使用斜率和截距函数来计算校准线的公式,并根据仪器的读数确定“未知”化学溶液的浓度,或者决定我们应该为程序提供什么输入,以便大理石落在离发射器一定距离的地方。
第一步:创建图表
我们的简单示例电子表格由两列组成:X值和Y值。
让我们从选择要在图表中绘制的数据开始。
首先,选择“X值”列单元格。
现在按Ctrl键,然后单击Y值列单元格。
转到“插入”选项卡。
导航到“图表”菜单并选择“散点图”下拉列表中的第一个选项。
将出现一个图表,其中包含两列中的数据点。
通过单击其中一个蓝点选择该系列。选择后,Excel会勾勒出点的轮廓。
右键单击其中一个点,然后选择“添加趋势线”选项。
图表上将出现一条直线。
屏幕右侧将出现“格式化趋势线”菜单。选中“在图表上显示方程式”和“在图表上显示R平方值”旁边的复选框。R平方值是一个统计数据,它告诉您直线与数据的拟合程度。最佳R平方值为1.000,这意味着每个数据点都会接触到这条线。随着数据点和直线之间的差异增大,r平方值会下降,0.000是可能的最低值。
趋势线的方程和R平方统计量将出现在图表上。请注意,在我们的示例中,数据的相关性非常好,R平方值为0.988。
方程的形式为“Y=Mx+B”,其中M是斜率,B是直线的y轴截距。
现在校准已经完成,让我们通过编辑标题和添加轴标题来定制图表。
要更改图表标题,请单击它以选择文本。
现在键入描述该图表的新标题。
要将标题添加到x轴和y轴,请首先导航到图表工具>设计。
单击“添加图表元素”下拉列表。
现在,导航到轴标题>主要水平。
将显示一个轴标题。
若要重命名轴标题,请首先选择文本,然后键入新标题。
现在,转到轴标题>主要垂直方向。
将显示一个轴标题。
通过选择文本并键入新标题来重命名此标题。
您的图表现在已完成。
第二步:计算直线方程和R平方统计量
现在,让我们使用Excel的内置斜率、截断和相关函数来计算直线方程和R平方统计量。
在我们的工作表(在第14行)中,我们已经为这三个函数添加了标题。我们将在这些标题下面的单元格中执行实际计算。
首先,我们将计算坡度。选择单元格A15。
定位至公式>更多函数>统计>斜率。
将弹出函数参数窗口。在“KNOWN_YS”字段中,选择或键入Y值列单元格。
在“KNOWN_XS”字段中,选择或键入X值列单元格。在斜率函数中,‘KNOWN_YS’和‘KNOWN_XS’字段的顺序很重要。
单击“确定”。公式栏中的最终公式应如下所示:
=坡度(C3:C12,B3:B12)
请注意,单元格A15中的斜率函数返回的值与图表上显示的值匹配。
接下来,选择单元格B15,然后导航到Forms>More Functions>Statistics>Intercept。
将弹出函数参数窗口。选择或键入“KNOWN_YS”字段的Y值列单元格。
选择或键入“KNOWN_XS”字段的X值列单元格。在INTERCEPT函数中,‘KNOWN_YS’和‘KNOWN_XS’字段的顺序也很重要。
单击“确定”。公式栏中的最终公式应如下所示:
=截取(C3:C12,B3:B12)
请注意,Intercept函数返回的值与图表中显示的y-intercept匹配。
接下来,选择单元格C15并导航到Forms>More Functions>Statistics>Correl。
将弹出函数参数窗口。选择或键入“Array1”字段的两个单元格范围中的任何一个。“与斜率和截距不同,顺序不会影响Correl函数的结果。
为“Array2”字段选择或键入两个单元格范围中的另一个。
单击“确定”。公式栏中的公式应如下所示:
=相关(B3:B12,C3:C12)
请注意,Correl函数返回的值与图表上的“r平方”值不匹配。correl函数返回“R”,因此我们必须将其平方才能计算“R平方”。
在函数栏内单击,并在公式末尾添加“^2”,以使correl函数返回的值成为方形。现在,完成的公式应该如下所示:
=CORREL(B3:B12,C3:C12)^2
按Enter键。
更改公式后,“R平方”值现在与图表中显示的值匹配。
第三步:设置快速计算值的公式
现在,我们可以在简单的公式中使用这些值来确定那个“未知”解决方案的浓度,或者我们应该在代码中输入什么来使大理石飞行一定的距离。
这些步骤将设置所需的公式,以便您能够输入X值或Y值,并根据校准曲线获得相应的值。
最佳拟合线的公式为“Y值=斜率*X值+截距”,因此求解“Y值”的方法是将X值和斜率相乘,然后加上截距。
例如,我们将零作为X值。返回的Y值应等于最佳拟合直线的截距。它是匹配的,所以我们知道公式工作正常。
基于Y值的X值求解是通过从Y值中减去截距并将结果除以斜率来完成的:
X-value=(Y-value-INTERCEPT)/SLOPE
作为示例,我们使用截距作为Y值。返回的X值应该等于零,但是返回的值是3.14934E-06。返回的值不是零,因为我们在键入值时无意中截断了截取结果。不过,公式工作正常,因为公式的结果是0.00000314934,基本上是零。
您可以在第一个粗边框单元格中输入您想要的任何X值,Excel将自动计算相应的Y值。
在第二个粗边单元格中输入任何Y值都会得到相应的X值。这个公式就是用来计算溶液浓度的公式,或者用来计算弹珠发射到一定距离所需的输入。
在这种情况下,仪器读数为“5”,因此校准建议浓度为4.94,或者我们希望大理石移动5个单位的距离,因此校准建议我们输入4.94作为控制弹珠发射器的程序的输入变量。我们可以对这些结果相当有信心,因为本例中的R平方值很高。