如何动态地对缺失值的数据进行线性插值? [英] How to dynamically do a linear interpolation of data in a row with missing values?

问题描述

我有这个数据

Date    Data
8/25/2017   980
8/24/2017   64
8/23/2017   593   
8/22/2017   595
8/21/2017   
8/20/2017   
8/19/2017   794
8/18/2017   437
8/17/2017   
8/16/2017   
8/15/2017   
8/14/2017   629

如果我想要在8月21日的单元格中输入(794-595)/3，在8月22日的单元格中输入2 *(794-595)/3，同样地在8月17日中想要(629-437)/4，则2 *(629- 437)/4 8月16日，等等...

What if i wanted (794-595)/3 in cell 21st August and 2*(794-595)/3 in cell 22nd August and similarly (629-437)/4 in 17th August, 2*(629-437)/4 in 16th August etc...

如何动态执行此操作，而不必考虑之间的缺失值数量

How to do this dynamically without regard to the number of missing values in between

推荐答案

以下代码提供了一种解决方案，动画gif显示了解决问题的代码.当然，有很多方法可以解决此问题，但这很简单.至少这向您显示了需要什么输入.它假定x值增加1(就像您在数据中一样).否则，您需要使用诸如坡度&拦截功能.

The code below provides one solution, and the animated gif shows the code solving your problem. Of course, there are many ways of solving this problem, but this is simple. At least this shows you what inputs will be needed. It assumes the x-values increment by 1 (as you have in your data). Otherwise you'd need to use something like the slope & intercept functions.

Option Explicit
Sub interpolate()
Dim r As Range, cell As Range
Set r = Application.InputBox("select interpolation range", , , Type:=8)
For Each cell In r
  If cell = "" Then
    cell = r(1) + (r(r.Rows.Count) - r(1)) * (cell.row - r.row) / (r(r.Rows.Count).row - r(1).row)
  End If
Next
End Sub

这篇关于如何动态地对缺失值的数据进行线性插值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！