主题：[求助]能不能直接调用EXCEL函数？

我在项目中要用到标准曲线，狐表能不能直接调用EXCEL中的函数，如SLOPE（）、INTERCEPT（）、CORREL（）函数？不能的话怎么自定义这三个函数呢？

不能用。

 

你必须去了解各个函数的计算方法，比如SLOPE

 

slope = Sum((X – X mean) * (Y – Y mean))  /  Sum((X – X mean) * (X – X mean))

 

比如有三组数 x=1,y=2; x=3,y=4; x=5,y=6

 

slope = ((1-3)*(2-4)+(3-3)*(4-4)+(5-3)*(6-4)) / ((1-3)*(1-3) + (3-3)*(3-3) + (5-3)*(5-3))

 

其余的函数，算法自己百度一下，然后转成对应的代码即可。

 如果代码不会写，就上传具体例子，说明要计算什么

 比如，根据2楼的逻辑，计算如下，其余什么函数，都可以根据逻辑编写代码。

 

Dim x() As Integer = {1,3,5}
Dim y() As Integer = {2,4,6}

Dim avg1 As Double
Dim avg2 As Double
For i As Integer = 0 To x.length - 1
    avg1 += x(i) / x.length
    avg2 += y(i) / y.length
Next
Dim sum1 As Double
Dim sum2 As Double
For i As Integer = 0 To x.length - 1
    sum1 += (x(i)-avg1)*(y(i)-avg2)
    sum2 += (x(i)-avg1)*(x(i)-avg1)
Next
msgbox(sum1 / sum2)

 进一步的，INTERCEPT 函数

 

https://support.office.com/zh-cn/article/INTERCEPT-%E5%87%BD%E6%95%B0-2a9b74e2-9d47-4772-b663-3bca70bf63ef?CorrelationId=3e76d59e-83f9-4c81-af22-89bf97f5df1d&ui=zh-CN&rs=zh-CN&ad=CN

 

 

Dim x() As Integer = {6,5,11,7,5}
Dim y() As Integer = {2,3,9,1,8}

Dim avg1 As Double
Dim avg2 As Double
For i As Integer = 0 To x.length - 1
    avg1 += x(i) / x.length
    avg2 += y(i) / y.length
Next
Dim sum1 As Double
Dim sum2 As Double
For i As Integer = 0 To x.length - 1
    sum1 += (x(i)-avg1)*(y(i)-avg2)
    sum2 += (x(i)-avg1)*(x(i)-avg1)
Next
Dim b As Double = sum1 / sum2
Dim a As Double = avg2 - b*avg1
msgbox(a)

CORREL根据公式计算

 

https://support.office.com/zh-cn/article/CORREL-%E5%87%BD%E6%95%B0-995DCEF7-0C0A-4BED-A3FB-239D7B68CA92?ui=zh-CN&rs=zh-CN&ad=CN

 

Dim x() As Integer = {3,2,4,5,6}
Dim y() As Integer = {9,7,12,15,17}

Dim avg1 As Double
Dim avg2 As Double
For i As Integer = 0 To x.length - 1
    avg1 += x(i) / x.length
    avg2 += y(i) / y.length
Next
Dim sum1 As Double
Dim sum2 As Double
Dim sum3 As Double
For i As Integer = 0 To x.length - 1
    sum1 += (x(i)-avg1)*(y(i)-avg2)
    sum2 += (x(i)-avg1)^2
    sum3 += (y(i)-avg2)^2
Next
msgbox(sum1 / math.Sqrt(sum2*sum3))

 

收藏

谢谢袍哥