如何在 Excel 中为任意数据插入中间值

Question 1

简短答案

插值基于与 X 和 Y 值相关的方程。如果您知道实际方程，则可以直接计算所需的任何中间值。如果不知道，则使用近似值进行插值。近似值的质量决定了中间值的准确性。如果您使用有限数量的点来近似曲线，则线性插值会很粗糙。还有其他几种方法可以为您提供更好的结果，并且内置的分析工具可以完成大部分工作。

长答案

您正在寻找一个“通用公式”或解决方案，可以自动插入中间值。您可以对几乎任何数据使用线性插值，但如果数据点数量有限且数据形状曲率较大，结果将很粗糙。如果您想要准确度，就没有“一刀切”的解决方案。给定数据集的最佳解决方案将取决于数据的特征。

等式

不管你如何做，插值都是使用定义 X 和 Y 之间关系的方程式来完成的。该方程式可以是实际方程式，也可以是估算方程式。如果是估算方程式，则有许多不同的方法，具体取决于数据的性质以及你需要完成的任务。

在您的另一个问题中，您使用了基于方程的数据Y=2^X。当您有实际方程时，您可以精确地进行插值。为X或选择一个新值Y，方程会给出另一个值。如果您不知道实际方程，则需要找到一个近似它的方程。我将使用这个答案来关注插值方法。这些通常使用内置的分析工具来完成大部分工作。如果您需要有关使用特定工具或更自动化方法的机制的更多详细信息，我们可以在另一个答案中对其进行扩展。

尝试找到实际方程

最好的解决方案是看看你是否能确定实际方程是什么。如果你知道生成数据的过程，这可能会告诉你方程的性质。许多过程在受控条件下，即你处理的是单个驱动变量且没有随机噪声时，遵循一条简单的曲线，该曲线的方程类型是已知的。因此，第一步是查看数据的形状，看看它是否与其中一种相似。

一个简单的方法是绘制数据图表并添加趋势线。Excel 有许多常用曲线可供尝试拟合。

让我们用2^N您另一个问题中的数据尝试一下。如果您没有识别出数字模式并尝试使用趋势线方法，您会看到不同形状曲线的图标。指数曲线具有相同的一般形状，这将为您提供以下结果：

Excel 使用e而不是2作为基数，这只是一个平移（e ^0.693是2）。从视觉上看，您可以看到趋势线完全跟随数据。R ²也告诉了您这一点。R ²是您用方程式解释的数据变异量的统计度量。该值1表示方程式解释了 100% 的变异，或完美拟合。

本题中的例子也具有指数形状。如果你尝试同样的方法，你会得到以下结果：

所以这个数据不是指数型的。我们可以试试多项式，它描述了一些自然过程，能够模拟各种曲线（我稍后会详细讨论）：

作为数据背后过程的近似值，它并不是很合适。在三阶（包含 X 幂至 X^3 的方程）中，它的主要拐点比数据多，但仍然不匹配。因此，底层方程看起来不像一条简单、常见的曲线，这意味着方程需要近似。

线性插值

这是您在评论中描述的方法。它很简单，使用简单的公式，并且相当容易实现自动化。如果您有很多点，并且它们之间的直线足够接近，那么这种方法就足够了。在许多曲线上，某些区域的短段将接近直线。但是，对于曲线来说，这是一个很差的近似值，并且在任何曲率较大的区域，您的结果都会不准确。在您的示例中，X 值 7 和 8 之间的区域将具有很大的曲率。在这个区域中，与实际曲线相比，直线将如下所示：

您正在寻找一种适用于任何数据的通用解决方案。您可能会发现线性插值对于某些数据来说太粗糙了。

回归

人们建议使用回归作为一种方法，在这里和其他帖子中。可以使用趋势线或其底层工作表函数或分析工具来完成（我认为这可能在分析工具包中，可能需要将该选项加载到 Excel 中，默认情况下可能不会加载）。

回归试图将曲线拟合到您的数据中，目的是最小化数据和曲线之间的总误差。在正常使用中，它不是完成此任务的合适工具（它是用于拟合趋势线的方法，您看到了它与您的需要相比如何）。

它适用于以下情况：您的目标是对数据背后的过程进行建模。假设数据不准确，而回归表明数据实际上应该是什么样的。通过回归找到的曲线可能不会经过任何实际数据点。在您的例子中，数据是给定的，并且假设是准确的。曲线必须经过每个点。
回归试图用一个方程来拟合所有数据。如果创建数据的过程不能用可用的方程类型来描述，那么它将不会有效。对于大量的数据点，每个段的线性插值可能比所有数据的回归曲线更能近似。

但是，除了通常使用回归之外，还可以将其“滥用”为您想要的解决方案，而且通常可以奏效。当您尝试对过程进行建模时，最简单的公式通常更有价值（奥卡姆剃刀）。另一方面，使用足够复杂的方程，您可以拟合任何内容。您总是可以画出一条通过每个点的涂鸦。有了N点，您可以找到一个N-1通过所有点的阶多项式方程（最坏情况）。

我说“通常”是因为在某些情况下，这是一条相当扭曲的线，对你的目的毫无用处。请注意，这种方法实际上并没有“建模”任何东西，因为结果方程会预测数据范围之外的行为。

以下是使用具有逐级高阶方程的多项式回归对您的数据进行的分析（第一个屏幕截图包括 3 - 5 阶）：

（单击图像可查看大小。）请注意，分析工具包括您想要执行的插值类型；它生成中间值。对于每个分析，这些a(n)值是它找到的方程的系数。 a(0)是常数，a(1)是 X^1 项的系数，等等。它显示拟合的 R ²值。它需要几乎 1足够接近您的目的。

我已将差异最大的原始数据值标出。在这个顺序范围内，拟合度会随着每个连续顺序而变得更好，但哪些特定点的描述更准确可能会发生变化。以下是这三个点的图表：

当我们得到 6 阶和 7 阶多项式时，它看起来像这样：

如果我们用 8 阶多项式来表示这 9 个值，那么它就是完美的，但 7 阶可能已经足够接近了。从这个角度来看，请注意 7 阶方程的 R ²为 .99999，但仍然不是完美的。

使用回归分析工具找到合适的拟合值（在本例中为 7 阶或 8 阶方程），将产生您想要的中间值。但最好将结果绘制成图表并目测曲线，以确保它不是乱画。

样条线

如果您绘制数据图表并选择平滑线选项，Excel 会使用样条线来生成该图表。事实上，几乎所有计算机图形应用（包括字体定义）都是基于样条线来生成平滑曲线和曲线过渡。它以绘图员曾经用来将任意点与曲线连接起来的灵活规则命名。

样条线会考虑相邻点，为每个部分创建曲线，每次创建一个部分。曲线会经过每个点，并且点的两侧不会出现突然变化，就像用直线连接点时那样。

样条函数使用的方程式并不试图模拟产生数据的过程；它只是为了看起来漂亮。但是，大多数过程都遵循某种连续、平滑的曲线。当您处理单个曲线段时，许多产生形状大致相似的曲线的不同方程将在该段内产生非常相似的值。因此，在大多数情况下，样条函数将产生您想要的良好近似值（并且它自然地通过每个点，而不像回归那样必须强制通过每个点）。

再次强调，我说的是“大多数情况”。样条线非常适合那些非常均匀和规则的数据，并且遵循曲线的“规则”。它可以对不寻常的数据做出一些意想不到的事情。例如，上一个 SU 问题是关于 Excel 生成的数据图表中这个奇怪的负“下降”：

样条线有点像果冻。想象一下一大块果冻，然后你把特定的点约束在你想要的地方。果冻的其余部分会在需要的地方凸起。方程可以定义某些类型的曲线。如果你强迫曲线通过特定的点，也会发生同样的事情。使用样条线，效果仅限于奇怪的凸起或看起来不自然的曲线段；高阶回归方程可以遵循一条狂野的路径。

样条线是这样表示数据曲线的：

如果将其与高阶回归曲线进行比较，样条曲线对局部变化的“响应”更灵敏。

我使用 LibreOffice Calc 进行了此分析，它有一个包含样条函数的分析插件。如您所见，它还为样条函数生成了您想要的插值结果。我无法随时访问 Excel 的分析工具包，因此我不知道 Excel 是否包含样条函数。如果没有，LO Calc 将在 Windows 中运行，而且它是免费的。

结论

这涵盖了您可以用来插入中间值的方法。不同的方法可能对不同的数据效果更好。或者，您的要求可能是任何近似、快速和简单的要求。决定您需要哪种插值。如果您需要有关如何实现它的更多详细信息，我们可以在另一个答案中讨论机制。

Answer

简短答案

插值基于与 X 和 Y 值相关的方程。如果您知道实际方程，则可以直接计算所需的任何中间值。如果不知道，则使用近似值进行插值。近似值的质量决定了中间值的准确性。如果您使用有限数量的点来近似曲线，则线性插值会很粗糙。还有其他几种方法可以为您提供更好的结果，并且内置的分析工具可以完成大部分工作。

长答案

您正在寻找一个“通用公式”或解决方案，可以自动插入中间值。您可以对几乎任何数据使用线性插值，但如果数据点数量有限且数据形状曲率较大，结果将很粗糙。如果您想要准确度，就没有“一刀切”的解决方案。给定数据集的最佳解决方案将取决于数据的特征。

等式

不管你如何做，插值都是使用定义 X 和 Y 之间关系的方程式来完成的。该方程式可以是实际方程式，也可以是估算方程式。如果是估算方程式，则有许多不同的方法，具体取决于数据的性质以及你需要完成的任务。

在您的另一个问题中，您使用了基于方程的数据Y=2^X。当您有实际方程时，您可以精确地进行插值。为X或选择一个新值Y，方程会给出另一个值。如果您不知道实际方程，则需要找到一个近似它的方程。我将使用这个答案来关注插值方法。这些通常使用内置的分析工具来完成大部分工作。如果您需要有关使用特定工具或更自动化方法的机制的更多详细信息，我们可以在另一个答案中对其进行扩展。

尝试找到实际方程

最好的解决方案是看看你是否能确定实际方程是什么。如果你知道生成数据的过程，这可能会告诉你方程的性质。许多过程在受控条件下，即你处理的是单个驱动变量且没有随机噪声时，遵循一条简单的曲线，该曲线的方程类型是已知的。因此，第一步是查看数据的形状，看看它是否与其中一种相似。

一个简单的方法是绘制数据图表并添加趋势线。Excel 有许多常用曲线可供尝试拟合。

让我们用2^N您另一个问题中的数据尝试一下。如果您没有识别出数字模式并尝试使用趋势线方法，您会看到不同形状曲线的图标。指数曲线具有相同的一般形状，这将为您提供以下结果：

Excel 使用e而不是2作为基数，这只是一个平移（e ^0.693是2）。从视觉上看，您可以看到趋势线完全跟随数据。R ²也告诉了您这一点。R ²是您用方程式解释的数据变异量的统计度量。该值1表示方程式解释了 100% 的变异，或完美拟合。

本题中的例子也具有指数形状。如果你尝试同样的方法，你会得到以下结果：

所以这个数据不是指数型的。我们可以试试多项式，它描述了一些自然过程，能够模拟各种曲线（我稍后会详细讨论）：

作为数据背后过程的近似值，它并不是很合适。在三阶（包含 X 幂至 X^3 的方程）中，它的主要拐点比数据多，但仍然不匹配。因此，底层方程看起来不像一条简单、常见的曲线，这意味着方程需要近似。

线性插值

这是您在评论中描述的方法。它很简单，使用简单的公式，并且相当容易实现自动化。如果您有很多点，并且它们之间的直线足够接近，那么这种方法就足够了。在许多曲线上，某些区域的短段将接近直线。但是，对于曲线来说，这是一个很差的近似值，并且在任何曲率较大的区域，您的结果都会不准确。在您的示例中，X 值 7 和 8 之间的区域将具有很大的曲率。在这个区域中，与实际曲线相比，直线将如下所示：

您正在寻找一种适用于任何数据的通用解决方案。您可能会发现线性插值对于某些数据来说太粗糙了。

回归

人们建议使用回归作为一种方法，在这里和其他帖子中。可以使用趋势线或其底层工作表函数或分析工具来完成（我认为这可能在分析工具包中，可能需要将该选项加载到 Excel 中，默认情况下可能不会加载）。

回归试图将曲线拟合到您的数据中，目的是最小化数据和曲线之间的总误差。在正常使用中，它不是完成此任务的合适工具（它是用于拟合趋势线的方法，您看到了它与您的需要相比如何）。

它适用于以下情况：您的目标是对数据背后的过程进行建模。假设数据不准确，而回归表明数据实际上应该是什么样的。通过回归找到的曲线可能不会经过任何实际数据点。在您的例子中，数据是给定的，并且假设是准确的。曲线必须经过每个点。
回归试图用一个方程来拟合所有数据。如果创建数据的过程不能用可用的方程类型来描述，那么它将不会有效。对于大量的数据点，每个段的线性插值可能比所有数据的回归曲线更能近似。

但是，除了通常使用回归之外，还可以将其“滥用”为您想要的解决方案，而且通常可以奏效。当您尝试对过程进行建模时，最简单的公式通常更有价值（奥卡姆剃刀）。另一方面，使用足够复杂的方程，您可以拟合任何内容。您总是可以画出一条通过每个点的涂鸦。有了N点，您可以找到一个N-1通过所有点的阶多项式方程（最坏情况）。

我说“通常”是因为在某些情况下，这是一条相当扭曲的线，对你的目的毫无用处。请注意，这种方法实际上并没有“建模”任何东西，因为结果方程会预测数据范围之外的行为。

以下是使用具有逐级高阶方程的多项式回归对您的数据进行的分析（第一个屏幕截图包括 3 - 5 阶）：

（单击图像可查看大小。）请注意，分析工具包括您想要执行的插值类型；它生成中间值。对于每个分析，这些a(n)值是它找到的方程的系数。 a(0)是常数，a(1)是 X^1 项的系数，等等。它显示拟合的 R ²值。它需要几乎 1足够接近您的目的。

我已将差异最大的原始数据值标出。在这个顺序范围内，拟合度会随着每个连续顺序而变得更好，但哪些特定点的描述更准确可能会发生变化。以下是这三个点的图表：

当我们得到 6 阶和 7 阶多项式时，它看起来像这样：

如果我们用 8 阶多项式来表示这 9 个值，那么它就是完美的，但 7 阶可能已经足够接近了。从这个角度来看，请注意 7 阶方程的 R ²为 .99999，但仍然不是完美的。

使用回归分析工具找到合适的拟合值（在本例中为 7 阶或 8 阶方程），将产生您想要的中间值。但最好将结果绘制成图表并目测曲线，以确保它不是乱画。

样条线

如果您绘制数据图表并选择平滑线选项，Excel 会使用样条线来生成该图表。事实上，几乎所有计算机图形应用（包括字体定义）都是基于样条线来生成平滑曲线和曲线过渡。它以绘图员曾经用来将任意点与曲线连接起来的灵活规则命名。

样条线会考虑相邻点，为每个部分创建曲线，每次创建一个部分。曲线会经过每个点，并且点的两侧不会出现突然变化，就像用直线连接点时那样。

样条函数使用的方程式并不试图模拟产生数据的过程；它只是为了看起来漂亮。但是，大多数过程都遵循某种连续、平滑的曲线。当您处理单个曲线段时，许多产生形状大致相似的曲线的不同方程将在该段内产生非常相似的值。因此，在大多数情况下，样条函数将产生您想要的良好近似值（并且它自然地通过每个点，而不像回归那样必须强制通过每个点）。

再次强调，我说的是“大多数情况”。样条线非常适合那些非常均匀和规则的数据，并且遵循曲线的“规则”。它可以对不寻常的数据做出一些意想不到的事情。例如，上一个 SU 问题是关于 Excel 生成的数据图表中这个奇怪的负“下降”：

样条线有点像果冻。想象一下一大块果冻，然后你把特定的点约束在你想要的地方。果冻的其余部分会在需要的地方凸起。方程可以定义某些类型的曲线。如果你强迫曲线通过特定的点，也会发生同样的事情。使用样条线，效果仅限于奇怪的凸起或看起来不自然的曲线段；高阶回归方程可以遵循一条狂野的路径。

样条线是这样表示数据曲线的：

如果将其与高阶回归曲线进行比较，样条曲线对局部变化的“响应”更灵敏。

我使用 LibreOffice Calc 进行了此分析，它有一个包含样条函数的分析插件。如您所见，它还为样条函数生成了您想要的插值结果。我无法随时访问 Excel 的分析工具包，因此我不知道 Excel 是否包含样条函数。如果没有，LO Calc 将在 Windows 中运行，而且它是免费的。

结论

这涵盖了您可以用来插入中间值的方法。不同的方法可能对不同的数据效果更好。或者，您的要求可能是任何近似、快速和简单的要求。决定您需要哪种插值。如果您需要有关如何实现它的更多详细信息，我们可以在另一个答案中讨论机制。

Question 2

通读您对这个问题的评论和修改，我发现您想做的几件事并没有真正涵盖在我之前的回答中。这个答案将处理这些项目，并且我提供了逐步指导您如何完成整个插值过程。

数据不准确

您将生成数据的过程描述为在某个时间间隔内读取数据，并且数字是四舍五入的时间。方程的好坏取决于数据。在实际分析中，您应该使用最精确的数字（也许您只是通过显示四舍五入的时间来保持示例的简单性）。

但是，您显示的数据并不完全符合您通常看到的物理过程曲线。当只有一个驱动变量并且没有噪音时，理论曲线通常是平滑的。如果您使用非常精确的设备来以预设的间隔触发读数并提供准确的测量结果，那么您可以将结果视为精确的。但是，如果您手动计时并手动读取读数，X即使读数本身是准确的，值也可能是不精确的时间。将单个X值稍微向一个方向移动将引入您在数据曲线中看到的各种小的不规则性（除非示例只是您为了举例而编造的数字）。

如果是这种情况，您可能会受益于使用回归来估计最佳拟合。

使用 Y 作为 X

在您的问题中，您需要定义的值Y（本例中为 1 到 37 之间的整数值），并找到相关的 X 值。这在您的Y=2^X问题中很容易做到，因为这个简单的方程式可以很容易地反转为X=log(Y)/log(2)，并且您可以直接计算您想要的任何值。如果方程式不简单，通常没有实用的方法来反转它。我之前回答中的“滥用”回归方法为您提供了一个高阶方程，但它是“单向的”，通常不适用于求解逆方程。

最简单的方法是从头开始反转X。Y这样你就可以用你引入的整数值来得到一个方程（分析会给出方程的系数，如上一个答案所述）。

看看一条简单的曲线是否有效永远不会有什么坏处。这是反转的数据，你可以看到没有有用的拟合：

因此，尝试多项式拟合。但是，这种情况就像我在上一个答案中描述的那样。从 1 到 8 的值拟合得很好，但 9 会让它消化不良。三阶多项式会给你带来一个颠簸：

随着方程阶数的增加，它变得越来越“有趣”。到第 7 阶时，你会得到这个：

它几乎精确地穿过每个点，但 8 和 9 之间的曲线没有用。一种解决方案是在 8 和 9 之间进行线性插值。不过，在这种情况下，您可以通过在上端合并样条线来获得更好的值。样条线选项提供了良好的拟合效果，以及 8 和 9 之间更有意义的曲线：

不幸的是，样条方程有点复杂，而且没有提供方程。但是，您可以对分析提供的中间值进行线性插值，这应该会让您得到非常接近合理曲线的数字。

外推与内推

在此示例中，您的第一个Y值是 2.9。您想要生成1和的值2，这些值超出了数据的范围。这需要外推而不是内插，这是一个非常不同的要求。

如果已知方程，就像你的Y=2^X例子一样，你可以计算出你想要的任何值。
如果已知生成数据的过程遵循一条简单曲线，并且您对拟合度有信心，那么您可以投射数据范围之外的值，甚至可以获得该值实际范围的有意义的置信区间（基于数据与数据范围内的曲线之间的变化量）。
如果强制将高阶方程拟合到数据中，数据范围之外的预测通常是没有意义的。
如果您使用样条曲线，则没有在数据范围外进行投影的基础。

无论您在数据范围之外做出什么预测，其好坏都取决于您使用的方程式，如果您没有使用精确的方程式，那么您得到的数据离您越远，它就会越不准确。

查看第一张图中的对数曲线，您会发现它投射出的值与您的预期非常不同。

对于多项式方程，零幂系数是一个常数，它是X的值所产生的值0。所以这是一种简单的方法来观察曲线在这个方向上的走向。

请注意，在第 4 阶或第 5 阶时，点 1 到 8 非常准确。但是一旦超出范围，方程的表现就会大不相同。

使用有限数据进行推断

改进方法之一是仅拟合该末端的点，并包含尽可能多的连续点，使之符合该末端的曲线形状。点 9 显然不合适。在此之前，曲线中有几个拐点，一个拐点在点 5 或 6 附近，因此高于该拐点的点遵循不同的曲线。仅使用点 1 到 5，您就可以接近完美拟合 3 阶多项式。该方程将投射出零点 0.12095（与上表比较），对于X的值1，0.3493。

如果您仅将前五个点拟合成一条直线，会发生什么情况：

这投射出 -0.5138 的零点，X并且1。-0.0071

可能结果的范围表明了超出数据范围的不确定性水平。没有正确答案。而这是曲线的“良好”端。的值为。Y您想要达到 37。样条曲线表明曲线在处渐近。在原始数据中投影直线将产生略大于的值（与 4 阶多项式相同）。3 阶多项式表示小于的值（5 阶和 6 阶也是如此）。7 阶多项式表示远高于的值。因此，超出数据范围的任何东西都是猜测，或者是您想要的任何值。X936.79999

综合起来

那么让我们逐步了解实际解决方案是什么样子。我们假设您已经尝试找到一个精确的方程式并使用趋势线测试了常见曲线。下一步是尝试回归，因为它为您提供了曲线的公式，您可以插入整数值。

我无法随时访问 Excel 2013 或分析工具包。我将使用 LibreOffice Calc 来说明这一点。它并不完全相同，但足够接近，您应该能够在 Excel 中遵循它。在 LO Calc 中，这实际上是一个需要加载的免费扩展。我正在使用CorelPolyGUI，可下载这里我记得分析工具包中没有样条函数。如果情况仍然如此，并且您想在 Excel 中执行此操作，我遇到了这个免费插件（我还没有测试过）。另一种方法是使用 LO Calc，它可以在 Windows 中运行，而且是免费的。

在这里，我在 A 列和 B 列中输入了 X 和 Y 值（反转），然后打开分析对话框。突出显示 X 值并单击 X 按钮加载数据范围，我选择了多项式。

在下一个选项卡上，我指定要使用0的7度（具有所有阶数的 7 阶多项式）。

要指定输出，我选择 C1 并单击“列”，它会记录输出所需的列。我选择让它输出原始数据，即计算结果，并选择让它在每个原始数据点之间添加三个中间点。我告诉它我想要一张新图表上的结果图。然后转到计算菜单并单击计算。

就是这样。如果你看一下计算值，你可能会注意到一个问题。它将在下一步中变得明显。

在这里，我添加了1通过的37值。此时，我们只想处理插值，因此我添加了一个公式来仅计算3通过的值36。该公式仅扩展结果中列出的系数（a(n) 值）。I2 中的公式为：

=D$4+D$5*H3+D$6*H3^2+D$7*H3^3+D$8*H3^4+D$9*H3^5+D$10*H3^6+D$11*H3^7

这只是每个系数乘以 X 值的相关幂。将其向下拖动即可得到结果。不完全是；您必须查看它才能确定它是否通过了健全性测试。我们知道和之间存在问题8，但事实证明这只是您想要的值的一半。我们可以使用从到的9值，但将来自其他方法的那么多值组合在一起是没有意义的。所以让我们对整个事情使用样条线。320

再次打开分析对话框，在输入选项卡上将方法更改为“样条线”（此处未显示）。为其指定一个新的输出范围并告诉它进行计算。这就是全部操作。

我们有新的结果可以使用。将数据范围划分为这么多段可以使每个段保持较短，因此线性插值应该相当不错（比在原始数据上使用它要好得多）。

曲线拟合或插值的过程涉及创建数据点；使用您自己的判断来判断曲线“应该”（或不应该）是什么样子（回归假设即使原始数据也不精确）。

对该数据进行完整性检查表明，偶数样条曲线会产生凸起的连接曲线；一个值略微超过9，这可能是人为的，而不是您正在测量的过程的反映。在这种情况下，曲线在处渐近的9可能性更大，因此我通过目测任意地为高点分配了一个比稍小的值9。假设我的值不是精确的，只是它是一种改进。为了说明这一点，我创建了一个新列，其中包含将要使用的值。

1我添加了一列，其中包含从到的数字37。根据前面的讨论，我们没有可靠的基础来预测1和的值2，所以我将它们留空。对于37，我采用了渐近假设，并将其设为。从到的9值是通过线性插值找到的（这是一个您可以适应其他数据的公式）。Q3 中的公式是：336

=TREND(OFFSET($M$1,MATCH(P3,M$1:M$33)-1,2,2),OFFSET($M$1,MATCH(P3,M$1:M$33)-1,0,2),P3)

当范围是两个点时，TREND 函数只会进行插值。语法为：

TREND(Y_range, X_range, X_value)

OFFSET 函数用于每个范围。在每种情况下，它都使用 MATCH 函数来查找包含目标值的范围的第一行。这些值为，-1因为这些是偏移量而不是位置；第一行的匹配是0与参考行的偏移量。请注意，在本例中，Y列的偏移量为2，因为我添加了一个额外的列来手动调整值。OFFSET 参数选择包含 Y 或 X 值的列，并选择范围高度为 2，这将为您提供低于和高于目标的值。

结果：

分析向导完成了繁重的工作，无论您使用多项式回归还是样条曲线，它只需要一个公式即可生成结果。

Answer

通读您对这个问题的评论和修改，我发现您想做的几件事并没有真正涵盖在我之前的回答中。这个答案将处理这些项目，并且我提供了逐步指导您如何完成整个插值过程。

数据不准确

您将生成数据的过程描述为在某个时间间隔内读取数据，并且数字是四舍五入的时间。方程的好坏取决于数据。在实际分析中，您应该使用最精确的数字（也许您只是通过显示四舍五入的时间来保持示例的简单性）。

但是，您显示的数据并不完全符合您通常看到的物理过程曲线。当只有一个驱动变量并且没有噪音时，理论曲线通常是平滑的。如果您使用非常精确的设备来以预设的间隔触发读数并提供准确的测量结果，那么您可以将结果视为精确的。但是，如果您手动计时并手动读取读数，X即使读数本身是准确的，值也可能是不精确的时间。将单个X值稍微向一个方向移动将引入您在数据曲线中看到的各种小的不规则性（除非示例只是您为了举例而编造的数字）。

如果是这种情况，您可能会受益于使用回归来估计最佳拟合。

使用 Y 作为 X

在您的问题中，您需要定义的值Y（本例中为 1 到 37 之间的整数值），并找到相关的 X 值。这在您的Y=2^X问题中很容易做到，因为这个简单的方程式可以很容易地反转为X=log(Y)/log(2)，并且您可以直接计算您想要的任何值。如果方程式不简单，通常没有实用的方法来反转它。我之前回答中的“滥用”回归方法为您提供了一个高阶方程，但它是“单向的”，通常不适用于求解逆方程。

最简单的方法是从头开始反转X。Y这样你就可以用你引入的整数值来得到一个方程（分析会给出方程的系数，如上一个答案所述）。

看看一条简单的曲线是否有效永远不会有什么坏处。这是反转的数据，你可以看到没有有用的拟合：

因此，尝试多项式拟合。但是，这种情况就像我在上一个答案中描述的那样。从 1 到 8 的值拟合得很好，但 9 会让它消化不良。三阶多项式会给你带来一个颠簸：

随着方程阶数的增加，它变得越来越“有趣”。到第 7 阶时，你会得到这个：

它几乎精确地穿过每个点，但 8 和 9 之间的曲线没有用。一种解决方案是在 8 和 9 之间进行线性插值。不过，在这种情况下，您可以通过在上端合并样条线来获得更好的值。样条线选项提供了良好的拟合效果，以及 8 和 9 之间更有意义的曲线：

不幸的是，样条方程有点复杂，而且没有提供方程。但是，您可以对分析提供的中间值进行线性插值，这应该会让您得到非常接近合理曲线的数字。

外推与内推

在此示例中，您的第一个Y值是 2.9。您想要生成1和的值2，这些值超出了数据的范围。这需要外推而不是内插，这是一个非常不同的要求。

如果已知方程，就像你的Y=2^X例子一样，你可以计算出你想要的任何值。
如果已知生成数据的过程遵循一条简单曲线，并且您对拟合度有信心，那么您可以投射数据范围之外的值，甚至可以获得该值实际范围的有意义的置信区间（基于数据与数据范围内的曲线之间的变化量）。
如果强制将高阶方程拟合到数据中，数据范围之外的预测通常是没有意义的。
如果您使用样条曲线，则没有在数据范围外进行投影的基础。

无论您在数据范围之外做出什么预测，其好坏都取决于您使用的方程式，如果您没有使用精确的方程式，那么您得到的数据离您越远，它就会越不准确。

查看第一张图中的对数曲线，您会发现它投射出的值与您的预期非常不同。

对于多项式方程，零幂系数是一个常数，它是X的值所产生的值0。所以这是一种简单的方法来观察曲线在这个方向上的走向。

请注意，在第 4 阶或第 5 阶时，点 1 到 8 非常准确。但是一旦超出范围，方程的表现就会大不相同。

使用有限数据进行推断

改进方法之一是仅拟合该末端的点，并包含尽可能多的连续点，使之符合该末端的曲线形状。点 9 显然不合适。在此之前，曲线中有几个拐点，一个拐点在点 5 或 6 附近，因此高于该拐点的点遵循不同的曲线。仅使用点 1 到 5，您就可以接近完美拟合 3 阶多项式。该方程将投射出零点 0.12095（与上表比较），对于X的值1，0.3493。

如果您仅将前五个点拟合成一条直线，会发生什么情况：

这投射出 -0.5138 的零点，X并且1。-0.0071

可能结果的范围表明了超出数据范围的不确定性水平。没有正确答案。而这是曲线的“良好”端。的值为。Y您想要达到 37。样条曲线表明曲线在处渐近。在原始数据中投影直线将产生略大于的值（与 4 阶多项式相同）。3 阶多项式表示小于的值（5 阶和 6 阶也是如此）。7 阶多项式表示远高于的值。因此，超出数据范围的任何东西都是猜测，或者是您想要的任何值。X936.79999

综合起来

那么让我们逐步了解实际解决方案是什么样子。我们假设您已经尝试找到一个精确的方程式并使用趋势线测试了常见曲线。下一步是尝试回归，因为它为您提供了曲线的公式，您可以插入整数值。

我无法随时访问 Excel 2013 或分析工具包。我将使用 LibreOffice Calc 来说明这一点。它并不完全相同，但足够接近，您应该能够在 Excel 中遵循它。在 LO Calc 中，这实际上是一个需要加载的免费扩展。我正在使用CorelPolyGUI，可下载这里我记得分析工具包中没有样条函数。如果情况仍然如此，并且您想在 Excel 中执行此操作，我遇到了这个免费插件（我还没有测试过）。另一种方法是使用 LO Calc，它可以在 Windows 中运行，而且是免费的。

在这里，我在 A 列和 B 列中输入了 X 和 Y 值（反转），然后打开分析对话框。突出显示 X 值并单击 X 按钮加载数据范围，我选择了多项式。

在下一个选项卡上，我指定要使用0的7度（具有所有阶数的 7 阶多项式）。

要指定输出，我选择 C1 并单击“列”，它会记录输出所需的列。我选择让它输出原始数据，即计算结果，并选择让它在每个原始数据点之间添加三个中间点。我告诉它我想要一张新图表上的结果图。然后转到计算菜单并单击计算。

就是这样。如果你看一下计算值，你可能会注意到一个问题。它将在下一步中变得明显。

在这里，我添加了1通过的37值。此时，我们只想处理插值，因此我添加了一个公式来仅计算3通过的值36。该公式仅扩展结果中列出的系数（a(n) 值）。I2 中的公式为：

=D$4+D$5*H3+D$6*H3^2+D$7*H3^3+D$8*H3^4+D$9*H3^5+D$10*H3^6+D$11*H3^7

这只是每个系数乘以 X 值的相关幂。将其向下拖动即可得到结果。不完全是；您必须查看它才能确定它是否通过了健全性测试。我们知道和之间存在问题8，但事实证明这只是您想要的值的一半。我们可以使用从到的9值，但将来自其他方法的那么多值组合在一起是没有意义的。所以让我们对整个事情使用样条线。320

再次打开分析对话框，在输入选项卡上将方法更改为“样条线”（此处未显示）。为其指定一个新的输出范围并告诉它进行计算。这就是全部操作。

我们有新的结果可以使用。将数据范围划分为这么多段可以使每个段保持较短，因此线性插值应该相当不错（比在原始数据上使用它要好得多）。

曲线拟合或插值的过程涉及创建数据点；使用您自己的判断来判断曲线“应该”（或不应该）是什么样子（回归假设即使原始数据也不精确）。

对该数据进行完整性检查表明，偶数样条曲线会产生凸起的连接曲线；一个值略微超过9，这可能是人为的，而不是您正在测量的过程的反映。在这种情况下，曲线在处渐近的9可能性更大，因此我通过目测任意地为高点分配了一个比稍小的值9。假设我的值不是精确的，只是它是一种改进。为了说明这一点，我创建了一个新列，其中包含将要使用的值。

1我添加了一列，其中包含从到的数字37。根据前面的讨论，我们没有可靠的基础来预测1和的值2，所以我将它们留空。对于37，我采用了渐近假设，并将其设为。从到的9值是通过线性插值找到的（这是一个您可以适应其他数据的公式）。Q3 中的公式是：336

=TREND(OFFSET($M$1,MATCH(P3,M$1:M$33)-1,2,2),OFFSET($M$1,MATCH(P3,M$1:M$33)-1,0,2),P3)

当范围是两个点时，TREND 函数只会进行插值。语法为：

TREND(Y_range, X_range, X_value)

OFFSET 函数用于每个范围。在每种情况下，它都使用 MATCH 函数来查找包含目标值的范围的第一行。这些值为，-1因为这些是偏移量而不是位置；第一行的匹配是0与参考行的偏移量。请注意，在本例中，Y列的偏移量为2，因为我添加了一个额外的列来手动调整值。OFFSET 参数选择包含 Y 或 X 值的列，并选择范围高度为 2，这将为您提供低于和高于目标的值。

结果：

分析向导完成了繁重的工作，无论您使用多项式回归还是样条曲线，它只需要一个公式即可生成结果。

如何在 Excel 中为任意数据插入中间值

我尝试过的方法

答案1

简短答案

长答案

等式

尝试找到实际方程

线性插值

回归

样条线

结论

答案2

数据不准确

使用 Y 作为 X

外推与内推

使用有限数据进行推断

综合起来

相关内容