问题描述
我在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:
- 生成 x值
x
(在接下来的2个步骤中,我们需要将它们作为数组使用) - 为给定的行值计算斜率
m
- 为给定的行值计算拦截
c
- 用
y = m * x + c
插入缺失值
y
- Generate x-values
x
(we need them as array for the next 2 steps) - Calculate the slope
m
for the given row values - Calculate the intercept
c
for the given row values - Interpolate the missing values
y
withy = 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.
这篇关于插值丢失的数据的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!