vba 寻找拐点
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17324033/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Finding the Inflection Point
提问by AM847
I currently have two sets of data, a x and y axis, and I need to find the point where it changes from a positive slope to negative slope. Is there anyway of finding that data in VBA or a function within excel?
我目前有两组数据，ax 和 y 轴，我需要找到它从正斜率变为负斜率的点。无论如何可以在VBA或excel中的函数中找到该数据？
回答by MikeD
You can - as an approximation - calculate DeltaY / DeltaX for each subsequent pair of lines and check for change of the sign of this.
您可以 - 作为近似值 - 计算每对后续线的 DeltaY / DeltaX 并检查其符号的变化。
Example(starting in [A1] - copy all formulas down from their starting cell)
示例（从 [A1] 开始 - 从起始单元格向下复制所有公式）
[B2] =A2^3-A2
[C3] =(B3-B2)/(A3-A2)
[D3] =SIGN(C3)
[E4] =IF(D4<>D3;"beep";"")
X X^3-x DY/DX SIGN(F'(x)) change
-1 0
-0,9 0,171 1,71 1
-0,8 0,288 1,17 1
-0,7 0,357 0,69 1
-0,6 0,384 0,27 1
-0,5 0,375 -0,09 -1 beep
-0,4 0,336 -0,39 -1
-0,3 0,273 -0,63 -1
-0,2 0,192 -0,81 -1
-0,1 0,099 -0,93 -1
0 0 -0,99 -1
0,1 -0,099 -0,99 -1
0,2 -0,192 -0,93 -1
0,3 -0,273 -0,81 -1
0,4 -0,336 -0,63 -1
0,5 -0,375 -0,39 -1
0,6 -0,384 -0,09 -1
0,7 -0,357 0,27 1 beep
0,8 -0,288 0,69 1
0,9 -0,171 1,17 1
1 0 1,71 1
1,1 0,231 2,31 1
change of slope occurs at relative maxima or minima (1st differential quotient equal 0)
斜率的变化发生在相对最大值或最小值（第一微分商等于 0）
回答by Robert Dodier
If there is any noise in the data, computing differences will amplify that noise, so there is a greater chance of finding spurious inflection points. A way to reduce the noise is to fit a curve to the data, and then compute the inflection points for that curve. E.g. fit a cubic polynomial to the data, and find the inflection point of that.
如果数据中存在任何噪声，计算差异会放大该噪声，因此更有可能找到虚假拐点。降低噪声的一种方法是对数据拟合一条曲线，然后计算该曲线的拐点。例如，将三次多项式拟合到数据，并找到它的拐点。
回答by CharlesPL
May I suggest doing this would by using regression. Not a linear, but a typical multiple order regression, AKA polynomial regression (y = a_0 + a_1*x + a_2*x^2 + ... + a_n*x^n). See this thread for more details on how to do it.This can be done directly in Excel, no need to code anything in VBA. However, you'll probably need to deal with array formulas (AKA CTRL+ Enter formulas).
我可以建议通过使用回归来做到这一点。不是线性的，而是典型的多阶回归，AKA 多项式回归 ( y = a_0 + a_1*x + a_2*x^2 + ... + a_n*x^n)。有关如何执行此操作的更多详细信息，请参阅此线程。这可以直接在 Excel 中完成，无需在 VBA 中编写任何代码。但是，您可能需要处理数组公式（AKA CTRL+ Enter 公式）。
Then, once, you've find a regression that fits your distribution (r2 > 0.9 or what suits you), you could simply do a derivative of this equation. Since this is a polynomial equation, the equation is quite easy : y' = a_1 + 2*a_2*x + ... + n*a_n+1.
然后，一旦找到适合您的分布（r2 > 0.9 或适合您的分布）的回归，您就可以简单地对这个方程进行导数。由于这是一个多项式方程，方程是很容易的：y' = a_1 + 2*a_2*x + ... + n*a_n+1。
The fun part now starts! We need to find what values of xmakes y = 0. If your regression is below the 4th order, there is an analytical solution possible (i.e. there's an equation that can gives you the xvalue, because your derivative will be of order 3). If you are over the 4th order, then, you need to use a numerical method. Yes, you can use VBA to get a bisection algorithm going, but do you know that Excel has a numerical solver integrated? Use it to get the values you are looking for (assuming at least one value is real).
有趣的部分现在开始！我们需要找到什么值x品牌y = 0。如果您的回归低于 4 阶，则可能有一个解析解（即，有一个方程可以为您提供x值，因为您的导数将是 3 阶的）。如果你超过4阶，那么，你需要使用数值方法。是的，您可以使用 VBA 来实现二分算法，但是您知道Excel 集成了数值求解器吗？使用它来获取您正在寻找的值（假设至少有一个值是真实的）。
As you didn't supplied exemple of dataset, this is though to figure, but if we use MikeD exemple, we would get this !
由于您没有提供数据集示例，这虽然是数字，但如果我们使用 MikeD 示例，我们会得到这个！
With the values, instead of the formulas:
使用值而不是公式：
