如何在C＃中重新创建调用TREND（）的Excel公式？ [英] How do I recreate an Excel formula which calls TREND() in C#?

问题描述

我正在构建一个.net页面来模拟电子表格。表格包含此公式

I'm building a .net page to mimic a spreadsheet. The sheet contains this formula

=ROUND(TREND(AA7:AE7,AA$4:AE$4,AF$4),1)

有人可以提供C＃相当于 TREND（）？或者，如果任何人可以提供一个捷径，这也是很好的;我不太熟悉数学知道是否有更简单的方法。

Can someone provide the C# equivalent of TREND() ? Alternatively if anyone can provide a shortcut around it that's fine too; I'm not familiar enough with the math there to know if there's an easier way.

如果有帮助，这里有一些示例号。

Here are some sample numbers if it helps.

AA7：AE7
6 8 10 12 14

或
10.2 13.6 17.5 20.4 23.8

AA $ 4：AE $ 4
600 800 1000 1200 1400

AF $ 4
650

编辑：这里是我想出来的，似乎正在生产相同的数字作为我的电子表格。

edit: here's what I came up with and it seems to be producing the same numbers as my spreadsheet.

public static partial class Math2
{
    public static double[] Trend(double[] known_y, double[] known_x, params double[] new_x)
    {
        // return array of new y values
        double m, b;
        Math2.LeastSquaresFitLinear(known_y, known_x, out m, out b);

        List<double> new_y = new List<double>();
        for (int j = 0; j < new_x.Length; j++)
        {
            double y = (m * new_x[j]) + b;
            new_y.Add(y);
        }

        return new_y.ToArray();
    }

    // found at http://stackoverflow.com/questions/7437660/how-do-i-recreate-an-excel-formula-which-calls-trend-in-c
    // with a few modifications
    public static void LeastSquaresFitLinear(double[] known_y, double[] known_x, out double M, out double B)
    {
        if (known_y.Length != known_x.Length)
        {
            throw new ArgumentException("arrays are unequal lengths");
        }

        int numPoints = known_y.Length;

        //Gives best fit of data to line Y = MC + B
        double x1, y1, xy, x2, J;

        x1 = y1 = xy = x2 = 0.0;
        for (int i = 0; i < numPoints; i++)
        {
            x1 = x1 + known_x[i];
            y1 = y1 + known_y[i];
            xy = xy + known_x[i] * known_y[i];
            x2 = x2 + known_x[i] * known_x[i];
        }

        M = B = 0;
        J = ((double)numPoints * x2) - (x1 * x1);

        if (J != 0.0)
        {
            M = (((double)numPoints * xy) - (x1 * y1)) / J;
            //M = Math.Floor(1.0E3 * M + 0.5) / 1.0E3; // TODO this is disabled as it seems to product results different than excel
            B = ((y1 * x2) - (x1 * xy)) / J;
            // B = Math.Floor(1.0E3 * B + 0.5) / 1.0E3; // TODO assuming this is the same as above
        }
    }

}

推荐答案

考虑TREND是基于Excel函数LINEST。
如果您遵循此链接， https://support.office.com/en-us/article/LINEST-function-84d7d0d9-6e50-4101-977a-fa7abf772b6d ，它将说明LINEST背后的功能。

Consider TREND is based upon the Excel Function, LINEST. If you follow this link, https://support.office.com/en-us/article/LINEST-function-84d7d0d9-6e50-4101-977a-fa7abf772b6d, it will explain the functionality behind LINEST.

此外，您会发现它使用的基本公式。

In addition, you'll find the base formula that it uses.

。

这篇关于如何在C＃中重新创建调用TREND（）的Excel公式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！