如何在Excel中执行线性校准曲线

excel徽标

Excel具有内置功能,可用于显示校准数据并计算最佳拟合线。当您编写化学实验室报告或将校正因子编程到一台设备中时,这可能会有所帮助。

在本文中,我们将介绍如何使用Excel创建图表,绘制线性校准曲线,显示校准曲线的公式,然后使用SLOPE和INTERCEPT函数设置简单公式以在Excel中使用校准公式。

什么是校准曲线以及Excel在创建时如何有用?

要执行校准,您需要将设备的读数(如温度计显示的温度)与称为标准的已知值(如水的冷冻和沸点)进行比较。这使您可以创建一系列数据对,然后使用这些数据对来开发校准曲线。

使用水的冷冻和沸点对温度计进行两点校准将有两个数据对:一个从温度计放入冰水中时(32个)°F或0°C)和一个在沸水中(212°F或100°C)。当您将这两个数据对绘制为点并在它们之间绘制一条线(校准曲线)时,假设温度计的响应是线性的,您可以选择与温度计显示的值对应的线上的任何点,并且可以找到相应的“真实”温度。

因此,该线基本上是为您填写两个已知点之间的信息,以便您在估算温度计读数为57.2度时的实际温度时可以合理地确定,但是当您从未测量过相应的“标准”时那读书。

Excel具有以下功能:您可以在图表中以图形方式绘制数据对,添加趋势线(校准曲线),并在图表上显示校准曲线的公式。这对于可视显示很有用,但您也可以使用Excel的SLOPE和INTERCEPT函数计算线的公式。将这些值输入简单公式后,您将能够根据任何测量值自动计算“真实”值。

我们来看一个例子

对于此示例,我们将从一系列十个数据对开发校准曲线,每个数据对由X值和Y值组成。 X值将是我们的“标准”,它们可以代表我们使用科学仪器测量的化学溶液浓度到控制大理石发射机的程序的输入变量。

Y值将是“响应”,它们将代表仪器在测量每种化学溶液时所提供的读数,或者表示使用每个输入值离开大理石着陆的发射器的距离。

在我们以图形方式描绘校准曲线之后,我们将使用SLOPE和INTERCEPT函数来计算校准线的公式,并根据仪器的读数确定“未知”化学溶液的浓度,或者决定我们应该为程序提供哪些输入,以便大理石与发射器相距一定距离。

第一步:创建图表

我们的简单示例电子表格由两列组成:X值和Y值。

创建x值和y值列

让我们从选择要在图表中绘制的数据开始。

首先,选择“X值”列单元格。

选择x值列

现在按Ctrl键,然后单击Y值列单元格。

单击Y值列时按住Ctrl键

转到“插入”选项卡。

插入标签

导航到“图表”菜单,然后选择“分散”下拉列表中的第一个选项。

如何在Excel中执行线性校准曲线 1

将出现一个图表,其中包含两列中的数据点。

图表出现了

通过单击其中一个蓝点来选择系列。选择后,Excel将概述要点的概述。

选择数据点

右键单击其中一个点,然后选择“添加趋势线”选项。

选择添加趋势线选项

图表上会出现一条直线。

在屏幕的右侧,将出现“格式趋势线”菜单。选中“在图表上显示公式”和“在图表上显示R平方值”旁边的框.R平方值是一个统计数据,可以告诉您线条与数据的拟合程度。最佳R平方值为1.000,这意味着每个数据点都接触该线。随着数据点和线之间的差异增大,r平方值下降,其中0.000是最低可能值。

格式趋势线窗格

趋势线的等式和R平方统计量将显示在图表上。请注意,在我们的示例中,数据的相关性非常好,R平方值为0.988。

该等式的形式为“Y = Mx + B”,其中M是斜率,B是直线的y轴截距。

方程现在显示在图表上

现在校准已完成,让我们通过编辑标题和添加轴标题来自定义图表。

要更改图表标题,请单击它以选择文本。

现在输入描述图表的新标题。

新标题出现在图表上

要将标题添加到x轴和y轴,首先,导航到“图表工具”>“设计”。

如何在Excel中执行线性校准曲线 2

单击“添加图表元素”下拉列表。

单击“添加图表元素”按钮

现在,导航到Axis标题> Primary Horizo​​ntal。

头到轴工具>主要水平

将出现轴标题。

轴标题出现

要重命名轴标题,首先,选择文本,然后键入新标题。

更改轴标题

现在,前往Axis Titles> Primary Vertical。

添加主垂直轴标题

将出现轴标题。

显示新轴标题

通过选择文本并键入新标题来重命名此标题。

重命名轴标题

您的图表现已完成。

查看完整的图表

第二步:计算线方程和R平方统计量

现在让我们使用Excel的内置SLOPE,INTERCEPT和CORREL函数计算线方程和R平方统计量。

在我们的表格(第14行)中,我们为这三个功能添加了标题。我们将在这些标题下面的单元格中执行实际计算。

首先,我们将计算SLOPE。选择单元格A15。

选择坡度数据的单元格

导航到公式>更多函数>统计> SLOPE。

如何在Excel中执行线性校准曲线 3

弹出Function Arguments窗口。在“Known_ys”字段中,选择或键入Y值列单元格。

选择或输入Y值列单元格

在“Known_xs”字段中,选择或键入X值列单元格。 “Known_ys”和“Known_xs”字段的顺序在SLOPE函数中很重要。

选择或输入X值列单元格

单击“确定”。公式栏中的最终公式应如下所示:

= SLOPE(C3:C12,B3:B12)

请注意,单元格A15中SLOPE函数返回的值与图表上显示的值匹配。

显示斜率值

接下来,选择单元格B15,然后导航到公式>更多函数>统计> INTERCEPT。

如何在Excel中执行线性校准曲线 4

弹出Function Arguments窗口。选择或键入“Known_ys”字段的Y值列单元格。

选择或输入Y值列单元格

选择或键入“Known_xs”字段的X值列单元格。 “Known_ys”和“Known_xs”字段的顺序在INTERCEPT函数中也很重要。

选择或输入X值列单元格

单击“确定”。公式栏中的最终公式应如下所示:

= INTERCEPT(C3:C12,B3:B12)

请注意,INTERCEPT函数返回的值与图表中显示的y轴截距相匹配。

显示拦截功能

接下来,选择单元格C15并导航到公式>更多函数>统计> CORREL。

如何在Excel中执行线性校准曲线 5

弹出Function Arguments窗口。选择或键入“Array1”字段的两个单元格范围中的任何一个。与SLOPE和INTERCEPT不同,顺序不会影响CORREL函数的结果。

进入第一个细胞范围

选择或键入“Array2”字段的两个单元格范围中的另一个。

进入第二个细胞范围

单击“确定”。公式栏中的公式应如下所示:

= CORREL(B3:B12,C3:C12)

请注意,CORREL函数返回的值与图表上的“r-squared”值不匹配。 CORREL函数返回“R”,因此我们必须将其平方以计算“R平方”。

显示相关功能

在功能栏内单击并在公式的末尾添加“^ 2”以平方CORREL函数返回的值。完成的公式现在应该如下所示:

= CORREL(B3:B12,C3:C12)^ 2

按Enter键。

查看完成的公式

更改公式后,“R平方”值现在与图表中显示的值匹配。

r平方值现在匹配

第三步:设置公式以便快速计算值

现在我们可以在简单的公式中使用这些值来确定“未知”解决方案的浓度,或者我们应该在代码中输入什么输入,以便大理石飞行一定距离。

这些步骤将设置您能够输入X值或Y值所需的公式,并根据校准曲线获得相应的值。

输入X值或Y值并获取相应的值

最佳拟合线的方程式为“Y-value = SLOPE * X-value + INTERCEPT”,因此通过将X值和SLOPE相乘得到“Y值”的求解,然后添加INTERCEPT。

例如,我们将零值作为X值。返回的Y值应等于最佳拟合线的INTERCEPT。它匹配,所以我们知道公式正常工作。

显示零,因为X值等于INTERCEPT

通过从Y值中减去INTERCEPT并将结果除以SLOPE来求解基于Y值的X值:

X值=(Y-值截距)/ SLOPE

根据y值求解x值

例如,我们使用INTERCEPT作为Y值。返回的X值应该等于零,但返回的值是3.14934E-06。返回的值不为零,因为我们在键入值时无意中截断了INTERCEPT结果。但是,该公式正常工作,因为公式的结果为0.00000314934,基本上为零。

您可以在第一个厚边框单元格中输入您想要的任何X值,Excel将自动计算相应的Y值。

用Y求解x值

在第二个粗边界单元格中输入任何Y值将得到相应的X值。您可以使用此公式计算该溶液的浓度或将大理石发射一定距离所需的输入。

求x代表y值

在这种情况下,仪器显示“5”,因此校准将建议浓度为4.94或我们希望大理石行进五个单位的距离,因此校准建议我们输入4.94作为控制大理石发射器的程序的输入变量。由于此示例中的高R平方值,我们可以对这些结果有合理的信心。

本文机器自动生成,不保证语句完整通顺和表达准确,亦不对内容真实有效性负责,请读者知悉。