通过Google电子表格中的插值填充缺失数据

我的Google电子表格包含以下数据

   A           B       D
1  Date        Weight  Computation
2  2015/12/09          =B2*2
3  2015/12/10  65      =B3*2
4  2015/12/11          =B4*2
5  2015/12/12          =B5*2
6  2015/12/14  62      =B6*2
7  2015/12/15          =B7*2
8  2015/12/16  61      =B8*2
9  2015/12/17          =B9*2

我想绘制权重日期,和/或将其用于计算其他重量的数量的其他列.但是你会注意到有一些缺失的条目.我想要的是另一列,其数据基于权重列,内插和填充缺失值.例如:

   A           B       C       D
1  Date        Weight  WeightI Computation
2  2015/12/09          65      =C2*2          # use first known value
3  2015/12/10  65      65      =C3*2
4  2015/12/11          64      =C4*2          # =(62-65)/3*(1)+65
5  2015/12/12          63      =C5*2          # =(62-65)/3*(2)+65
6  2015/12/14  62      62      =C6*2
7  2015/12/15          61.5    =C7*2          # =(61-62)/2*(1)+62
8  2015/12/16  61      61      =C8*2
9  2015/12/17          61      =C9*2          # use the last known value

在C列中,当我必须在两个已知点之间找到缺失数据时,使用线性插值填充值.

我相信这是一个非常简单和常见的用例,所以我确信这是一件微不足道的事情,但我无法找到使用内置函数的解决方案.我对电子表格也没有多少经验.我花了几个小时试验= INDEX,= MATCH,= VLOOKUP,= LINEST,= TREND等,但我无法从示例中得到一些东西.我可以使用的唯一解决方案是使用Google Apps脚本创建自定义功能.虽然我的解决方案有效但似乎执行得非常慢.我的电子表格也很庞大.

任何指针,解决方案？

1> pnuts..：

---

您可能希望使用forecast哪个更方便,首先将您读取的日期与您没有读取的日期分开(并稍后重新排列).所以只有三个读数说:

       A        B
1 10/12/2015    65
2 14/12/2015    62
3 16/12/2015    61

以及左下方需要值的日期:

 6 09/12/2015   65.6
 7 11/12/2015   64.3
 8 12/12/2015   63.6
 9 15/12/2015   61.5
10 17/12/2015   60.2

65.6在B6中产生的公式(并从那里复制到适合)是:

=forecast(A6,$B$1:$B$3,$A$1:$A$3)  

这不是以您显示的方式计算的,但可以被认为稍微更精确,特别是通过外推丢失的最终值,而不是仅重复它们最接近的可用值.

计算了值后,您可能希望按日期顺序重新组合数据.因此,我建议复制B6:B10和编辑,粘贴特殊,粘贴值仅在顶部,然后排序以适应.

下图将上面的结果(蓝色)与OP中的结果(绿色)进行比较,并标记给定的数据点:

---

我可以看到`forcast()`的值,但是,在我的实验中,我发现它在整个数据范围内插入一条直线.因此,它不会追踪已有数据的点.理想情况下,如果我可以使用样条拟合而不是直线插值,我会喜欢.

2> Shriram V..：

---

使用以下方法找到满足我大部分要求的解决方案:

用于=FILTER()首先删除数据不可用的空白行(感谢"pnuts"提示).

并=MATCH()从过滤表中查找两个连续的行.在我的情况下,我能够使用此功能,因为列A已排序,没有重复.

然后使用线公式来插值.

所以输出变成:

   A            B       C           D       E
 1 Date         Weight  FDdate      FWeight IWeight
 2 2015/05/09           2015/05/10  65.00   #N/A
 3 2015/05/10   65.00   2015/05/13  62.00   65.00
 4 2015/05/11           2015/05/15  61.00   64.00
 5 2015/05/12                               63.00
 6 2015/05/13   62.00                       62.00
 7 2015/05/14                               61.50
 8 2015/05/15   61.00                       61.00
 9 2015/05/16                               61.00
10 2015/05/17                               61.00

其中细胞C2和D2具有下列范围内的式(次要注:下式当然可以如果列被组合A和B是相邻的):

C2 =FILTER($A$2:$A$10, NOT(ISBLANK($B$2:$B$10)))
D2 =FILTER($B$2:$B$10, NOT(ISBLANK($B$2:$B$10)))

单元格E2通过E10包含以下行插值公式:[ y = y1 + (y2 - y1) / (x2 - x1) * (x - x1)]:

E2 =(INDEX($D:$D, MATCH($A2, $C:$C, 1),     1))
   +(INDEX($D:$D, MATCH($A2, $C:$C, 1) + 1, 1)
   - INDEX($D:$D, MATCH($A2, $C:$C, 1),     1))
   /(INDEX($C:$C, MATCH($A2, $C:$C, 1) + 1, 1)
   - INDEX($C:$C, MATCH($A2, $C:$C, 1),     1))
   *(INDEX($C:$C, MATCH($A2, $C:$C, 1),     1) - $A2) * -1

此解决方案不起作用的是当第一个单元格B2没有值时,公式结果为#N/A. 如果我们=INTERPOLATE_LINE( A2, $A$2:$A$10, $B$2:$B$10 )在谷歌电子表格中有类似的东西,所有这些都会更有效率,但不幸的是,这不存在.如果我在阅读谷歌电子表格中支持的功能时错过了它,请纠正我.