插值丢失的数据 [英] Interpolate missing Data

问题描述

我在excel中有数据，我需要填写缺失(空白)数据，输入数据如下:

I have data in excel , I need fill missing (blank) data, the input data is like:

row1 --> 1   2      3   blank   5   6   blank  blank   9   10

row2 --> 2   4   blank  blank   10  12    14   blank   18  blank

VBA代码必须读取每一行并像这样填充它们:

the VBA code must read each rows and fill them like :

row1 --> 1   2  3  4   5    6    7    8    9    10

row2 --> 2   4  6  8   10   12   14   16   18   20

在VBA(excel)中是否有明确的解决方案?

is there clear solution to do this in VBA(excel)?

推荐答案

以下是数学解决方案的示例:

Here is an Example for a mathematical solution:

1. 生成 x值 x(在接下来的2个步骤中，我们需要将它们作为数组使用)
2. 为给定的行值计算斜率 m
3. 为给定的行值计算拦截 c
4. 用y = m * x + c
插入缺失值y

1. Generate x-values x (we need them as array for the next 2 steps)
2. Calculate the slope m for the given row values
3. Calculate the intercept c for the given row values
4. Interpolate the missing values y with y = m * x + c

示例:

Option Explicit

Public Sub LinearInterpolateRowWise()
    Dim DataRange As Range
    Set DataRange = Worksheets("Sheet1").Range("A1:J3")

    Dim ArrX As Variant 'create an array of x-values
    ReDim ArrX(1 To 1, 1 To DataRange.Columns.Count)
    Dim c As Long
    For c = 1 To DataRange.Columns.Count
        ArrX(1, c) = c
    Next c

    Dim iRow As Long, iCol As Long
    For iRow = 1 To DataRange.Rows.Count 'loop row wise
        Dim Slope As Double 
        Slope = Application.WorksheetFunction.Slope(DataRange.Rows(iRow), ArrX)

        Dim Intercept As Double
        Intercept = Application.WorksheetFunction.Intercept(DataRange.Rows(iRow), ArrX)

        For iCol = 1 To DataRange.Columns.Count 'interpolate missing values
            If DataRange.Cells(iRow, iCol) = vbNullString Then
                DataRange.Cells(iRow, iCol) = Slope * iCol + Intercept 'y = m * x + c
            End If
        Next iCol
    Next iRow
End Sub

因此，假设此源数据

So assuming this source data

像这样插值

以下是第3行的插值的可视化显示:

The following is a visualization of the interpolation of row 3:

那么，发生的事情是我们计算给定点(蓝色)的线性方程，并用它来计算缺失点(橙色).

So what happens is we calculate the linear equation through the given points (blue) and use it to calculate the missing point (orange).

这甚至适用于非线性原始点(蓝色)，如以下示例所示.

This will even work for non linear original points (blue) like in the following Example.

这篇关于插值丢失的数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！