具有自由选择功能的VBA EXCEL拟合曲线 [英] VBA EXCEL Fitting Curve with freely chosen function

问题描述

作为开始的情况，我有一个xy图，上面带有一些值，其进度类似于指数函数。我需要编写在图表上绘制拟合曲线的代码，但是我必须使用非指数的特定函数（因为我需要从中获取一些系数）。

As start situation, I have an xy-chart with some values on it whose progression resemble an exponential function. I need to write a code that draws a fitting curve on the chart, but I have to use a particular function which is not exponential (because I need to get some coefficients from it).

我需要使用的功能之一是K（Cx）²/（1 + x），其中k和C是我需要的参数。（它们分别是2和这使它变得更加复杂）显然，您无法在Excel的拟合曲线工具中找到这种结构。是否有可能在图表上拟合一条曲线，以便您可以自己编写函数的结构？

One of the functions i need to use is K(C-x)²/(1+x) whereby k and C are the parameters I need.(They are two and it makes it a lot more complicated) Obviously you can't find this kind of structure on the fitting curve tool in Excel. Is there any possibility to have a fitting curve to a chart where you can write yourself the structure of the function?

很抱歉，如果我没有添加任何书面代码，但我只需要提示即可开始编写。
谢谢

Sorry if I don't add any written code, but i just need a hint to start writing. Thank you

< img src = https://i.stack.imgur.com/8lQtz.png alt =在此处输入图片说明>

推荐答案

前段时间我做了类似的事情。我采用的方法是使用求解器（如gary的学生所建议）。我认为它是从VBA解雇的，但这并不重要。

I did something to similar to this a while ago. The approach I took was to use the solver (as gary's student suggests). I think it was fired from VBA but that's unimportant.

基本上，每行数据有两个输入单元格，变量K和C。然后，您需要找到函数产生的值之间的差（错误）将输入单元格中的值与实际值进行比较（我认为使用errors ^ 2可以更快地进行转换）。然后，您将另一个单元格中的差异求和。运行求解器时，您要求它通过更改K和C来使差异之和最小。

Basically you'd have two input cells per row of data with your variables K and C. Then you need to find the difference (errors) between the values the function produces with the values in the input cells compared to the actual values (I think using errors^2 gives quicker conversion). You then sum the differences in another cell. When running the solver, you ask it to minimise the sum of differences by changing K and C.

这有意义吗？？

这篇关于具有自由选择功能的VBA EXCEL拟合曲线的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！