Excel求解器:c#中的非线性最小二乘等价

I am working on a forms application that reports based on observed data. Prior to development of the application, Excel spreadsheets were used along side excel solver to do non-linear least squares regression. Ive been out of University for a while, and it is possible for me to write one eventually, I doubt the client wants to wait for several months while I come up with a suitable replacement for excel's sovler.

我正在开发一个基于观察数据的表单应用程序。在开发应用程序之前，使用Excel电子表格进行非线性最小二乘回归。我大学毕业已经有一段时间了，我最终有可能写一篇，我怀疑客户是否愿意等上几个月，我才能找到一个合适的替代品来替代excel的sovler。

So my question is this; Is there a C# equivalent of the Excel solver's non-linear least squares regression function? I have observed data and some initial guesses based on the observed data, so input isnt an issue. Hell, even the name of the equation used would be a good starting point.

我的问题是;c#是否等价于Excel求解器的非线性最小二乘回归函数?我根据观察到的数据观察数据和一些初步猜测，所以输入不是问题。见鬼，即使是方程的名字也是个不错的起点。

Ive taken a look at alglib but im not sure which one would be appropriate.

我看了一下止痛剂，但我不确定哪种合适。

Thanks in advance.

提前谢谢。

3 个解决方案

#1

You can do this using the MathDotNet Iridium library.

您可以使用MathDotNet Iridium库进行此操作。

Here is a c# class example:

下面是一个c#类示例:

using MathNet.Numerics.LinearAlgebra;

namespace *.Examples
{
public class PolynomialRegression
{
    readonly Vector _xData;
    readonly Vector _yData;
    readonly Vector _coef;
    readonly int _order;

    public PolynomialRegression(Vector xData, Vector yData, int order)
    {
        if (xData.Length != yData.Length)
        {
            throw new IndexOutOfRangeException();
        }
        _xData = xData;
        _yData = yData;
        _order = order;
        var n = xData.Length;
        var a = new Matrix(n, order + 1);
        for (var i = 0; i < n; i++)
            a.SetRowVector(VandermondeRow(xData[i]), i);

        // Least Squares |y=A(x)*c| ...  tr(A)*y = tr(A)*A*c  ...  inv(tr(A)*A)*tr(A)*y = c
        // http://en.wikipedia.org/wiki/Total_least_squares
        var at = Matrix.Transpose(a);
        var y2 = new Matrix(yData, n);
        _coef = (at * a).Solve(at * y2).GetColumnVector(0);
    }

    Vector VandermondeRow(double x)
    {
        var row = new double[_order + 1];
        for (var i = 0; i <= _order; i++)
            row[i] = Math.Pow(x, i);
        return new Vector(row);
    }

    public double Fit(double x)
    {
        return Vector.ScalarProduct(VandermondeRow(x), _coef);
    }

    public int Order { get { return _order; } }
    public Vector Coefficients { get { return _coef; } }
    public Vector XData { get { return _xData; } }
    public Vector YData { get { return _yData; } }
}
}

And here is an example usage:

这里有一个例子:

 var xVector = new Vector(new double[] { 1, 2, 3, 4, 5 });
 var yVector = new Vector(new double[] { 10, 20, 30, 40, 50 });
 var order = 2;
 _poly = new PolynomialRegression(xVector, yVector, order);

#2

One option you have is to simply use Excel from your C# program. This way the computation is done by Excel and your program can do anything that Excel can do. See How to automate Microsoft Excel from Microsoft Visual C#.NET

你有一个选择，就是使用c#程序中的Excel。这样计算就由Excel完成，你的程序可以做任何Excel能做的事情。请参见如何从Microsoft Visual c# .NET中自动化Microsoft Excel

Another option is to use one of the many available mathematics libraries, as pointed out by Michael. There are many libraries available. Of course you will want to verify that the answers that you get match Excel ;-)

另一个选择是使用众多可用的数学库中的一个，正如Michael所指出的。有许多可用的图书馆。当然，您需要验证您得到的答案是否与Excel匹配;

#3

So I ended up biting the bullet and going with alglib. The lsfit package did eventually do what I wanted, though it took me pretty darn long since its not really readable by a non mathematician.

所以我最终咬住子弹，去了阿尔戈里布。lsfit程序包最终实现了我想要的结果，尽管我花了很长时间才发现它并不是一个真正的数学家所能读懂的。

Ill keep this here so that anyone that has the same problem can find this.

我把这个放在这儿，这样任何有同样问题的人都能找到。

#1