excel - 如何根据空单元格在excel中进行线性插值

Question

第一个宏运行预定义的检查，以查找数据集 y 和 z 中的间隙：我们有数据 x，它是从 1-7 运行的顺序。值 y 和 z 是具有间隙的值，这些值需要相对于 x（订单号）进行插值。

解决方案如下

例如：

x     y    z  
1     2    3    
2     4    6    
3     ?    8  ' interpolation of y  between x=2 and x=4    
4     8    ?  ' interpolation of z between x=3 and x= 5    
5     12   14

编辑版本 2

Public Sub Interpol_Klicken()

Dim i As Long
Dim x1 As Double
Dim x2 As Double
Dim y1 As Double
Dim y2 As Double
Dim x As Double
Dim rngY As Range
Dim iCol  As Long
Dim iRow  As Long

Set rngY = Worksheets("Z").Range("A1:D10")

 iRow = rngY.Rows.Count
 iCol = rngY.Columns.Count


 For iCol = 2 To rngY.Columns.Count
 For iRow = 2 To rngY.Rows.Count

 If rngY.Cells(iRow, iCol).Value = "" Then

 y2 = rngY.Cells(iRow, iCol).Offset(RowOffset:=1).Value
 y1 = rngY.Cells(iRow, iCol).Offset(RowOffset:=-1).Value

 x2 = rngY.Cells(iRow, 1).Offset(RowOffset:=1).Value
 x1 = rngY.Cells(iRow, 1).Offset(RowOffset:=-1).Value
 x = rngY.Cells(iRow, 1).Value

 rngY.Cells(iRow, iCol) = y1 + (y2 - y1) * (x - x1) / (x2 - x1)

 End If
 Next iRow
 Next iCol


 End Sub

标签： excelvbainterpolationlinear-interpolation