vba使用linest计算多项式系数和索引以输出 [英] vba use linest to calculate polynomial coefficients and index to output

问题描述

我有两行数据，断裂压力和深度.我必须在 vba 中编码以生成多项式(在这种情况下为二次)方程，然后将系数输出到工作表.我正在使用 Linest 和 Index.对于这两行数据，我不知道我有多少数据集，因为我需要先删除一些噪声数据(噪声数据的定义是随机的所以每次数据集的数量都不同)，所以我不能使用类似于 linest 函数中的A17:A80".但是，看起来 vba 中的工作表函数不能用于数组.

I have two rows of data, fracture pressure and depth. I have to code in vba to generate the polynomial (quadratic for this case) equation and then output the coefficients to the worksheet. I am using Linest and Index. For this two rows of data, I don't know how many datasets I have because I need to delete some noisy data first (the definition of noisy data is randomly so the number of datasets vary each time), so I can't use something like "A17:A80" in the linest function. However, it looks like the worksheet function in vba can't work for arrays.

Dim Frac_x, Frac_y As Range
Dim X
        Set Frac_x = Range(Cells(17, 1), Cells(e - 1, 1))
        Set Frac_y = Range(Cells(17, 7), Cells(e - 1, 7))
        X= Application.WorksheetFunction.LinEst(Frac_y,Frac_x,{1,2})
        Cells(3, 8).Value = Application.WorksheetFunction.Index(X, 1, 1)
        Cells(4, 8).Value = Application.WorksheetFunction.Index(X, 1, 2)
        Cells(5, 8).Value = Application.WorksheetFunction.Index(X, 1, 3)

在这段代码中，e在前面的代码中定义，(e-1)代表数据集的总数.但是，我不断收到该行的 { is an invalid character: X= Application.WorksheetFunction.LinEst(Frac_y,Frac_x,{1,2})然后我做了一些研究并将代码修改为:

In this code, e is defined in the previous code, (e-1) represents the total number of datasets. However, I keep getting { is a invalid character for the line: X= Application.WorksheetFunction.LinEst(Frac_y,Frac_x,{1,2}) Then I did some researches and modified the code to:

Dim Frac_x, Frac_y As Range
Dim X
        Set Frac_x = Range(Cells(17, 1), Cells(e - 1, 1))
        Set Frac_y = Range(Cells(17, 7), Cells(e - 1, 7))
        X = Application.Evaluate("=linest(" & Frac_y & "," & Frac_x & "^ {1,2}))")
        Cells(3, 8).Value = Application.WorksheetFunction.Index(X, 1, 1)
        Cells(4, 8).Value = Application.WorksheetFunction.Index(X, 1, 2)
        Cells(5, 8).Value = Application.WorksheetFunction.Index(X, 1, 3)

然后我不断收到该行的 Type Dismatch 错误:X = Application.Evaluate("=linest(" & Frac_y & "," & Frac_x & "^ {1,2}))")我确定这两个范围 frac_y 和 frac_x 的类型匹配.有人可以帮忙吗?

Then I keep getting Type Dismatch error for the line: X = Application.Evaluate("=linest(" & Frac_y & "," & Frac_x & "^ {1,2}))") I am sure the two ranges frac_y and frac_x their type matches. Anyone could help?

推荐答案

你说得对，Excel VBA 不能做像 arrVariable^{1,2} 这样的事情.这必须通过对数组项进行循环来完成.

You are right, that Excel VBA can't do things like arrVariable^{1,2}. That must be done with loops over the array items.

但是 Evaluate 方法应该有效.但是您的公式字符串不正确.为了检测和避免这种错误，我将首先在 String 变量中连接这样的公式字符串.然后我可以简单地检查变量的值.

But the Evaluate approach should work. But your formula string is not correct. To detect and avoid such incorrectness, I will ever concatenate such formula strings within a String variable first. Then I can simply check the variable's value.

例如，值在 A17:A26 和 G17:G26 中:

Example, Values are in A17:A26 and G17:G26:

Sub test()

 Dim Frac_x As Range, Frac_y As Range
 Dim X

 e = 27

 With ActiveSheet

  Set Frac_x = .Range(.Cells(17, 1), .Cells(e - 1, 1))
  Set Frac_y = .Range(.Cells(17, 7), .Cells(e - 1, 7))
  arrX = Frac_x
  ReDim arrX2(1 To UBound(arrX), 1 To 2) As Double
  For i = LBound(arrX) To UBound(arrX)
   arrX2(i, 1) = arrX(i, 1)
   arrX2(i, 2) = arrX(i, 1) * arrX(i, 1)
  Next

  X = Application.LinEst(Frac_y, arrX2)

  'sFormula = "=LINEST(" & Frac_y.Address & "," & Frac_x.Address & "^{1,2})"
  'X = Application.Evaluate(sFormula)

  .Range(.Cells(3, 8), .Cells(5, 8)).Value = Application.Transpose(X)

 End With

End Sub

提示:使用 Application.LinEst 而不是 Application.WorksheetFunction.LinEst.如果函数无法工作，后者将抛出错误，而第一个将返回错误值.所以前者不会像后者那样中断程序.

Hints: Use Application.LinEst instead of Application.WorksheetFunction.LinEst. The latter will throw an error if the function cannot work while the first will return an error value instead. So the first will not interrupt the program as the latter will do.

这篇关于vba使用linest计算多项式系数和索引以输出的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！