[已解决]EXCEL中统计分析——在大数组集中寻找最稳态、斜率最小的小数组

求助，工业上分析使用，求大神帮忙有什么方法寻找符合条件的数组集？
  举个例子，如下图（实际图有点波动）：

大数组作图abcd，如何寻找的是 bc 段 对应的数组集，EXCEL 函数 、VBA都会，就是没有想到用什么方法解决。谢谢了！

最佳答案

月排行榜 / 总排行榜

香川群子

2014-8-30 18:21

线性函数关系的自定义函数：

1. Function f_Line(x, x1, y1, x2, y2, Optional m = 0)
   
2.     k = (y1 - y2) / (x1 - x2): If m = 1 Then f_Line = k: Exit Function
   
3.     b = (x1 * y2 - x2 * y1) / (x1 - x2): If m = 2 Then f_Line = b: Exit Function
   
4.     If m = -1 Then y = x: f_Line = (y - b) / k Else f_Line = k * x + b
   
5. End Function

复制代码

本函数可以用于：
已知两点坐标(x1,y1)、(x2,y2)

1. 计算该两点决定的直线的斜率k
   =f_Line(0,x1,y1,x2,y2,1)

2. 计算该两点决定的直线的原点偏移b
   =f_Line(0,x1,y1,x2,y2,2)

3. 已知直线上任意一点x坐标时，求其y值
   =f_Line(x,x1,y1,x2,y2,0)
或   =f_Line(x,x1,y1,x2,y2) (默认m参数=0)

4. 已知直线上任意一点y坐标时，求其x值
   =f_Line(y,x1,y1,x2,y2,-1)


5. 用于任意两点之间的插值计算。用法同3或4

以上。 呵呵

跳转到最佳答案楼层

能否用数学、统计等方法来寻找中间趋向直线的数据段。该方法应该不止针对于本次数据，需要更有广泛性。

香川群子

没附件。图片也看不到。楼主想自己一个人玩？

EXCEL中统计分析——在大数组集中寻找最稳态、斜率最小的小数组

香川群子 发表于 2014-8-30 13:57
没附件。图片也看不到。楼主想自己一个人玩？

图中第二阶段指的就是 bc 段

香川群子

直线线性分析……很容易的。

但是只看图有什么用？楼主的原始数据在哪里？

香川群子

计算连续两点间的直线斜率k，比较各个k值直到k值稳定(误差在设置精度以内)

然后根据分析比较得到的k确定值，再检查一遍原始数据，根据误差精度，即可确定bc段范围。

香川群子

一、直线计算：

公理：两点决定一条直线

那么，假设已知一条直线上两点的坐标(x1,y1)、(x2,y2)，
就一定能通过简单的计算得到这条直线的解析方程式，可以计算任意其它点的坐标值了。

下面是简单的计算推导过程：

为方便起见，我们就在Sheet1的 x1,y1,x2,y2 这4个单元格内输入已知两点的坐标，
所以计算时的变量，就和公式引用完全一致了。

首先，直线的通式为： y=k*x+b 【k为斜率、b为原点偏移值】

于是代入已知两点坐标有：
① y1 = kx1 + b
② y2 = kx2 + b

计算①-②得到③
③ y1 - y2 = k * (x1 - x2)

于是得到斜率k的表达式：
④ k = (y1 - y2) / (x1 - x2)

把k代入等式①就得到⑤
⑤ b = y1 - kx1
       = y1 - x1 * (y1 - y2) / (x1 - x2)            '代入k
       = [y1*(x1-x2)-x1*(y1-y2)] / (x1 - x2)    '通分
       = (x1 * y2 - x2 * y1) / (x1 - x2)           '合并简化

以上计算完成，代入得到通式：
y = k*x + b
y = x * (y1 - y2) / (x1 - x2) + (x1 * y2 - x2 * y1) / (x1 - x2)

^_^

总结：
斜率 k = (y1 - y2) / (x1 - x2)
偏移 b = (x1 * y2 - x2 * y1) / (x1 - x2)

直线计算表达式为：
  y = k*x + b  (k、b可直接代入上面的计算结果，或引用含计算结果的单元格)
或
  y = x * (y1 - y2) / (x1 - x2) + (x1 * y2 - x2 * y1) / (x1 - x2)

注意在工作表中使用公式时，x1、y1、x2、y2要绝对引用，所以是：
  y = x * ($y$1 -$y$2) / ($x$1 - $x$2) + ($x$1 * $y$2 - $x$2 * $y$1) / ($x$1 - $x$2)

但如果在VBA中使用，则直接设置x1、y1、x2、y2为变量即可。

香川群子

线性函数关系的自定义函数：

1. Function f_Line(x, x1, y1, x2, y2, Optional m = 0)
   
2.     k = (y1 - y2) / (x1 - x2): If m = 1 Then f_Line = k: Exit Function
   
3.     b = (x1 * y2 - x2 * y1) / (x1 - x2): If m = 2 Then f_Line = b: Exit Function
   
4.     If m = -1 Then y = x: f_Line = (y - b) / k Else f_Line = k * x + b
   
5. End Function

复制代码

本函数可以用于：
已知两点坐标(x1,y1)、(x2,y2)

1. 计算该两点决定的直线的斜率k
   =f_Line(0,x1,y1,x2,y2,1)

2. 计算该两点决定的直线的原点偏移b
   =f_Line(0,x1,y1,x2,y2,2)

3. 已知直线上任意一点x坐标时，求其y值
   =f_Line(x,x1,y1,x2,y2,0)
或   =f_Line(x,x1,y1,x2,y2) (默认m参数=0)

4. 已知直线上任意一点y坐标时，求其x值
   =f_Line(y,x1,y1,x2,y2,-1)


5. 用于任意两点之间的插值计算。用法同3或4

以上。 呵呵

开心妙妙

香川群子 发表于 2014-8-30 18:21
线性函数关系的自定义函数：本函数可以用于：
已知两点坐标(x1,y1)、(x2,y2)

膜拜，就这么一个图
弄出这么多的文章

su45

裙子的理论丰富呀！