|
发表于 2016-6-11 23:19
|
显示全部楼层
本楼为最佳答案
你看这样行了没!- Sub aa()
- arr = [a5:a20]
- brr = [b5:b20]
- For i = 1 To UBound(brr) '寻找B数组的开始行号和结束行号
- If brr(i, 1) = "" Then
- If i1 = 0 And brr(i + 1, 1) <> "" Then
- i1 = i + 1 'B数组的开始行号
- ElseIf i1 > 0 Then
- i2 = i - 1: Exit For 'B数组的结束行号
- End If
- End If
- Next
- ReDim crr(1 To i2 - i1 + 1, 1 To 1) '声明动态数组变量crr 1 to 4
- ReDim drr(1 To i2 - i1 + 1, 1 To 1) '声明动态数组变量drr
- n = 0
- For i = i1 To i2
- n = n + 1
- drr(n, 1) = brr(i, 1) 'B数组写入drr
- Next
- Max = 0
- For i = 1 To UBound(arr) - n + 1 '循环 维度的最大值13-n4的第一行
- For ii = 1 To n '循环 1 to 4
- crr(ii, 1) = arr(i + ii - 1, 1) '将A数组1 to 4写入crr
- Next
- xsdu = Application.Correl(crr, drr) '启用Correl(crr, drr)函数,并将结果写入xsdu(变量)
- If Max < xsdu Then Max = xsdu: i0 = i '自Max=0开始降序排列xsdu变量,最后将最大xsdu写入Max,i0 = i是对应行的意思
- Next
- [b24] = Max '将结果写入[b24]
- [b5:b20] = "" '清空[b5:b20]
- Range("b4").Offset(i0, 0).Resize(n, 1) = drr '将B数组写入Range("b4")开维的比对开始范围(i0, 0)的n + 1
- Call xx
- End Sub
- Sub xx()
- Dim i&, x%, a As Double, b As Double, c As Double, n&
- x = 3
- With Sheet1
- n = .Cells(22, 2).End(xlUp).Row + 1
- For i = n To 20
- a = Application.Evaluate("INDEX(LINEST(B" & i - 4 & ":" & "B" & i - 1 & ",A" & i - 4 & ":A" & i - 1 & "^{1,2},TRUE,TRUE),1,1)")
- b = Application.Evaluate("INDEX(LINEST(B" & i - 4 & ":" & "B" & i - 1 & ",A" & i - 4 & ":A" & i - 1 & "^{1,2},TRUE,TRUE),1,2)")
- c = Application.Evaluate("INDEX(LINEST(B" & i - 4 & ":" & "B" & i - 1 & ",A" & i - 4 & ":A" & i - 1 & "^{1,2},TRUE,TRUE),1,3)")
- .Range("B" & i) = a * .Range("A" & i) ^ 2 + b * .Range("A" & i) + c
- x = x + 1
- Next
- End With
- End Sub
复制代码 |
评分
-
查看全部评分
|