在Excel中插值数据点

Question

我相信这是其他人已经解决过许多次的问题。

一组人将进行测量（准确来说是家庭能源使用情况）。 他们每个人都会在不同的时间和不同的间隔内进行测量。

所以，我从每个人那里得到的是一组{日期，数值}对，其中集合中缺少日期。

我需要的是一组完整的{日期，数值}对，其中对于范围内的每个日期，都有一个已知的值（已测量或计算）。 我认为这个项目只需要简单的线性插值。

如果我假设它必须在Excel中完成。 在这样的数据集中进行插值的最佳方法是什么（以便我每天都有一个值）？

谢谢。

注意：当这些数据集完整时，我将确定斜率（即每天的用电量），从而可以开始进行家庭之间的比较。

附加信息：在得到前几个建议后： 我不想手动找出测量集中的空洞（不完整的测量集太多了！）。 我正在寻找某种（现有的）自动方式来帮助我完成这项工作。 因此，如果我的输入为

{2009-06-01,  10}
{2009-06-03,  20}
{2009-06-06, 110}

我期望能够自动获取

{2009-06-01,  10}
{2009-06-02,  15}
{2009-06-03,  20}
{2009-06-04,  50}
{2009-06-05,  80}
{2009-06-06, 110}

是的，我可以编写解决这个问题的软件。我只是希望有人已经为这个（相当通用的）问题准备好了“即插即用”的软件（Excel）功能。

- Niels Basjes

---

我今天刚刚偶然发现了这个。这里有一个非常棒的工作示例，类似于下面的一个答案，但是有一个可下载的示例，不需要Add-ins（http://www.blueleafsoftware.com/Products/Dagra/LinearInterpolationExcel.php）。 - EvilDr

Answer 1

我发现了这个内容，但不想使用插件，因为这样很难与没有安装该插件的人分享工作表。

我的同事设计了一个干净的公式，它相对紧凑（的代价是使用了一些魔法）。

需要注意的事项：

• 该公式的工作原理是：

  • 使用MATCH函数查找在搜索值之前的inputs范围内的行（例如，3是搜索值3.5之前的值）
  • 使用OFFSET选择该行和下一行的正方形（浅紫色）
  • 使用FORECAST仅使用这两个点建立线性插值，并获取结果

• 此公式不能进行外推；确保您的搜索值介于端点之间（在下面的示例中通过具有极端值来实现此操作）。

不确定是否对大家来说过于复杂，但它有非常便携的优点（比许多其他解决方案更简单）。

如果要复制粘贴公式，则为：

=FORECAST(F3,OFFSET(inputs,MATCH(F3,inputs)-1,1,2,1),OFFSET(inputs,MATCH(F3,inputs)-1,0,2,1

（其中inputs是一个命名范围）

- YGA

---

很实用的技巧。尽管你提供的复制粘贴公式与截图中的不匹配，后者才是正确的；前者存在错误。在我的情况下，我使用了以下公式：=FORECAST(W33,OFFSET(T38:T59,MATCH(W33,S38:S59)-1,0,2,1),OFFSET(S38:S59,MATCH(W33,S38:S59)-1,0,2,1))其中S38:S59包含x值，T38:T59包含y值。 - Jean-François Corbett

谢谢！已经更正了公式。 - YGA

使用X和Y进行简单的复制和粘贴替换：=FORECAST(XX1,OFFSET(Y1:Y10,MATCH(XX1,X1:X10)-1,0,2,1),OFFSET(X1:X10,MATCH(XX1,X1:X10)-1,0,2,1)) - Paul Seeb

Answer 2

有两个函数，LINEST和TREND，你可以尝试使用它们来看哪一个能给出更好的结果。它们都需要一组已知的X和Y值以及一个新的X值，并计算出一个新的Y值。区别在于，LINEST进行简单的线性回归，而TREND会首先尝试找到适合您数据的曲线，然后再进行回归。

- Bill the Lizard

---

谢谢你的建议。 我尝试了这两个函数，显然这两个函数都会在所有数据点上绘制一条直线。 这不是我想要的。我的主要要求是测量点保持原样。这些函数“破坏”了这个要求。 - Niels Basjes

使用已知的两个输入点进行线性插值。因此，如果您在8:30、9:00、10:00、10:30等时刻进行了测量，并且您想要估计9:30的测量值，您只需要在LINEST函数中使用9:00和10:00的测量值，而不是整个数据集。对于每个需要估计的数据点，都使用最近的两个边界点来进行插值。 - Bill the Lizard

也许我误解了你的建议，但在我看来，这意味着我需要手动确定“洞在哪里”。作为一名开发人员，我天生懒惰，希望软件能够替我找出这些洞。 - Niels Basjes

Answer 3

最简单的方法可能是如下所示：

1. 在此处下载Excel插件：XlXtrFun™ Extra Functions for Microsoft Excel

2. 使用interpolate()函数。 =Interpolate($A$1:$A$3,$B$1:$B$3,D1,FALSE,FALSE)

列A和B应包含您的输入，列G应包含所有日期值。公式放入列E中。

- Deniss

---

谢谢。我本来以为这是 Excel 的标准功能。这个可以满足我的需求。 - Niels Basjes

Xlxtrfun不再可供下载。请参见网站上的403错误消息。 - davidbourguignon

有人知道在网站不再支持下载的情况下，现在还能在哪里获取XLxtrfun吗？ - thron of three

Answer 4

YGA的回答没有处理当所需X值与参考范围的X值相同时的范围结束情况。使用YGA给出的示例，如果要求在9999处插值，则Excel公式将返回#DIV/0！错误。这显然是YGA将9999和-9999的极端端点添加到输入数据范围中的部分原因，并假定所有预测值都在这两个数字之间。如果不希望或无法进行此类填充，则避免#DIV/0！错误的另一种方法是使用以下公式检查精确输入值匹配：

=IF(ISNA(MATCH(F3,inputs,0)),FORECAST(F3,OFFSET(inputs,MATCH(F3,inputs)-1,1,2,1),OFFSET(inputs,MATCH(F3,inputs)-1,0,2,1)),OFFSET(inputs,MATCH(F3,inputs)-1,1,1,1))

F3是需要进行插值计算的值。

注意：我本来想在原始YGA帖子下添加评论，但我还没有足够的声望点数。

- DakotaD

Answer 5

一个很好的图形化方式来观察你的插值结果的拟合程度：

使用Excel中的XY图表（而不是线形图）绘制你的日期-数值对，并右键单击图表上的生成的曲线，点击“添加趋势线”。有许多不同的选项可选择使用哪种类型的曲线拟合。然后，你可以进入新创建的趋势线的属性并显示方程和R平方值。

请确保在格式化趋势线方程标签时，将数字格式设置为高精度，以便显示方程常数的所有有效数字。

- Stewbob

Answer 6

或者说。

=INDEX(yVals,MATCH(J7,xVals,1))+(J7-MATCH(J7,xVals,1))*(INDEX(yVals,MATCH(J7,xVals,1)+1)-INDEX(yVals,MATCH(J7,xVals,1)))/(INDEX(xVals,MATCH(J7,xVals,1)+1)-MATCH(J7,xVals,1))

j7 是 x 值。

xvals 是 x 值的范围，yvals 是 y 值的范围。

把这个放进代码里会更容易理解。

- darren

Answer 7

您可以使用Excel的“趋势线”功能找出最适合您数据的公式。使用该公式，您可以计算任何x的y值。

1. 为其创建线性散点（XY）图（插入=>散点）；
2. 创建多项式或移动平均趋势线，勾选“在图表上显示方程式”（右键单击系列=>添加趋势线）；
3. 将方程式复制到单元格中，并用所需的x值替换x；

在下面的截图中，A12：A16保存x，B12：B16保存y，C12包含计算任何x的y的公式。

我最初在这里发布了一个答案，但后来发现了这个问题

- alexkovelsky