在 Excel 中插入数据点

Question

我确信这是其他人之前已经解决过很多次的问题。

一群人将进行测量（确切地说是家庭能源使用情况）。 他们都会在不同的时间和不同的时间间隔这样做。

因此，我将从每个人那里得到一组 {date, value} 对，其中缺少日期。

我需要的是一组完整的 {date, value} 对，其中对于范围内的每个日期，都有一个值已知（测量或计算）。 我希望一个简单的线性插值就足以满足这个项目的需要。

如果我假设它必须在 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）功能来解决这个（相当通用的）问题。

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

（输入是命名范围）

Answer 2

一种很好的图形方式来查看插值结果的拟合程度：

获取日期、值对并使用 Excel 中的 XY 图表（而不是折线图）将它们绘制成图表。 右键单击图表上的结果线，然后单击“添加趋势线”。 有许多不同的选项可以选择使用哪种类型的曲线拟合。 然后您可以转到新创建的趋势线的属性并显示方程和 R 平方值。

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

Answer 3

最简单的方法可能如下：

1. 在此处下载 Excel 插件：XlXtrFun ™ Microsoft Excel 的额外功能

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

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

Answer 4

有两个函数，LINEST 和 TREND，您可以尝试看看哪个可以给您带来更好的结果。 它们都采用一组已知的 X 和 Y 以及新的 X 值，并计算新的 Y 值。 不同之处在于，LINEST 执行简单的线性回归，而 TREND 在执行回归之前会首先尝试找到一条适合您的数据的曲线。

Answer 5

YGA 的上述答案不处理所需 X 值与参考范围的 X 值相同的范围结束情况。 使用 YGA 给出的示例，Excel 公式将返回 #DIV/0！ 如果要求 9999 处的插值，则会出错。 这显然是 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 帖子的评论，但我还没有足够的声誉点。

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 值的范围，

更容易将其放入代码中。

Answer 7

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

1. 为其创建线性散点图 (XY)（Insert => Scatter）；
2. 创建多项式或移动平均趋势线，选中“在
   图表”（右键单击系列 => 添加趋势线）；
3. 将方程复制到单元格中，并将 x 替换为您想要的 x 值

在下面的屏幕截图中，A12:A16 包含 x< /code> 的，B12:B16 保存 y 的，C12 包含计算任何 x 的 y 的公式

。 "https://i.sstatic.net/ZUjPs.jpg" rel="nofollow noreferrer">

我首先在此处发布了答案，但后来发现了这个问题