我该如何重新创建Excel公式这就要求在C＃TREND（）？ [英] How do I recreate an Excel formula which calls TREND() in C#?
问题描述
我建立一个.NET页面模仿电子表格。该表包含该公式
= ROUND（TREND（AA7：AE7，AA $ 4：AE $ 4 AF $ 4），1）
有人可以提供C＃相当于的TREND（）？另外，如果任何人都可以提供一个快捷键周围也没关系;我还没有与数学有熟悉的知道，如果有一个更简单的方法。
下面是一些样本数是否有帮助。
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'/ STRONG>
650
编辑：这里就是我想出了和它似乎产生相同的号码我的电子表格。
公共静态部分类Math2的
{
公共静态双重[]趋势（双[] known_y，双[]和known_x，则params双[]一个new_x）
{
//返回新的y值
双阵列米，b;
Math2.LeastSquaresFitLinear（known_y的，known_x，出男，OUT B）;
名单，LT;双> new_y =新的List<双>（）;
为（INT J = 0; J< new_x.Length; J ++）
{
双Y =（M *一个new_x [J]）+ B;
new_y.Add（Y）;
}
返回new_y.ToArray（）;
}
//发现在http://stackoverflow.com/questions/7437660/how-do-i-recreate-an-excel-formula-which-calls-trend-in -c
//作一些简单修改
公共静态无效LeastSquaresFitLinear（双[] known_y，双[]和known_x，出双男，出双b）
{
如果（ ！known_y.Length = known_x.Length）
{
抛出新的ArgumentException（数组长度不等）;
}
INT为NumPoints = known_y.Length;
//给出的数据线Y = MC + B
双X1，Y1，XY，X2，J的最佳拟合;
X1 = Y1 = XY = X2 = 0.0;
的for（int i = 0; I<为NumPoints;我++）
{
X1 = X1 +与known_x [I]
Y1 = Y1 + known_y [I]
XY = XY +和known_x [I] * known_y [I]
X2 = X +和known_x [I] *和known_x [I]
}
M = B = 0;
J =（（双）为NumPoints * X2） - （X1 * X1）;
如果
{
M =（（（双）为NumPoints * XY） - （X1 * Y1））/ J（J = 0.0！）;
// M = Math.Floor（1.0E3 * M + 0.5）/ 1.0E3; // TODO因为它似乎产品的结果比不同，这被禁用的Excel
B =（（Y1 * X2） - （X1 * XY））/ J;
// B = Math.Floor（1.0E3 * B + 0.5）/ 1.0E3; // TODO假设这是与上面相同
}
}
}
考虑趋势是基于Excel函数，LINEST。
如果按照这个链接，的 https://support.office.com/en-us/article/LINEST-function-84d7d0d9-6e50-4101-977a-fa7abf772b6d ，这将解释背后LINEST的功能。
此外，你会发现，它使用基本公式
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)
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
or
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
}
}
}
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.
.
这篇关于我该如何重新创建Excel公式这就要求在C＃TREND（）？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
