[已解决]用代码计算黄色数据

759857387

用代码计算黄色数据.rar (9.24 KB, 下载次数: 13)

2016-6-11 20:06 上传

点击文件名下载附件

最佳答案

月排行榜 / 总排行榜

老司机带带我

2016-6-11 22:19

1. Sub xx()
   
2.     Dim i&, x%, a As Double, b As Double, c As Double
   
3.     x = 3
   
4.     With Sheet1
   
5.         For i = 13 To 20
   
6.             a = Application.Evaluate("INDEX(LINEST(B" & i - 4 & ":" & "B" & i - 1 & ",A" & i - 4 & ":A" & i - 1 & "^{1,2},TRUE,TRUE),1,1)")
   
7.             b = Application.Evaluate("INDEX(LINEST(B" & i - 4 & ":" & "B" & i - 1 & ",A" & i - 4 & ":A" & i - 1 & "^{1,2},TRUE,TRUE),1,2)")
   
8.             c = Application.Evaluate("INDEX(LINEST(B" & i - 4 & ":" & "B" & i - 1 & ",A" & i - 4 & ":A" & i - 1 & "^{1,2},TRUE,TRUE),1,3)")
   
9.             .Range("B" & i) = a * .Range("A" & i) ^ 2 + b * .Range("A" & i) + c
   
10.             x = x + 1
    
11.         Next
    
12.     End With
    
13. End Sub

复制代码

跳转到最佳答案楼层

老司机带带我

1. Sub xx()
   
2.     Dim i&, x%
   
3.     x = 3
   
4.     With Sheet1
   
5.         For i = 13 To 20
   
6.             .Range("B" & i) = .Cells(i, x) * .Range("A" & i) ^ 2 + .Cells(i + 1, x) * .Range("A" & i) + .Cells(i + 2, x)
   
7.             x = x + 1
   
8.         Next
   
9.     End With
   
10. End Sub

复制代码

759857387

老司机带带我 发表于 2016-6-11 20:33

C13：J22是为计算而做出的辅助列，最好是空着
希望看到代码中数组公式

759857387

老司机带带我 发表于 2016-6-11 20:33

我只会写单一的一句
a = Application.Evaluate("INDEX(LINEST(B9:B12,A9:A12^{1,2},TRUE,TRUE),1,1)*A13^2+INDEX(LINEST(B9:B12,A9:A12^{1,2},TRUE,TRUE),1,2)*A13+INDEX(LINEST(B9:B12,A9:A12^{1,2},TRUE,TRUE),1,3)")

其他不会写了

老司机带带我

1. Sub xx()
   
2.     Dim i&, x%, a As Double, b As Double, c As Double
   
3.     x = 3
   
4.     With Sheet1
   
5.         For i = 13 To 20
   
6.             a = Application.Evaluate("INDEX(LINEST(B" & i - 4 & ":" & "B" & i - 1 & ",A" & i - 4 & ":A" & i - 1 & "^{1,2},TRUE,TRUE),1,1)")
   
7.             b = Application.Evaluate("INDEX(LINEST(B" & i - 4 & ":" & "B" & i - 1 & ",A" & i - 4 & ":A" & i - 1 & "^{1,2},TRUE,TRUE),1,2)")
   
8.             c = Application.Evaluate("INDEX(LINEST(B" & i - 4 & ":" & "B" & i - 1 & ",A" & i - 4 & ":A" & i - 1 & "^{1,2},TRUE,TRUE),1,3)")
   
9.             .Range("B" & i) = a * .Range("A" & i) ^ 2 + b * .Range("A" & i) + c
   
10.             x = x + 1
    
11.         Next
    
12.     End With
    
13. End Sub

复制代码