在.net中使用LinEst()
本文关键字:LinEst net | 更新日期: 2023-09-27 18:15:27
我想使用Excel的内置函数LINEST()在。net中进行回归分析。我能够使用函数与方阵数组,但当它不是方阵说的顺序[12,3],那么它给出的错误为:
WorksheetFunction类的LinEst方法失败
请帮我解决这个问题,因为这对我完成这个代码非常重要。这是我的完整代码:
System.Data.DataTable dt = new System.Data.DataTable();
SqlCommand cmd =new SqlCommand("Select QtytoTransfer from DEmo ",con);
SqlDataAdapter adp = new SqlDataAdapter(cmd);
adp.Fill(dt);
List<double> yDatapoints =new List<double>();
foreach (DataRow dr in dt.Rows)
{
yDatapoints.Add(Convert.ToDouble( dr["QtytoTransfer"].ToString()));
}
System.Data.DataTable dt1 = new System.Data.DataTable();
SqlCommand sqlcmd = new SqlCommand("Select CurrentQoh,QtySold,GameTime from DEmo ", con);
SqlDataAdapter adp1 = new SqlDataAdapter(sqlcmd);
adp1.Fill(dt1);
double[,] xAll = new double[dt1.Rows.Count, dt1.Columns.Count];
for (int i = 0; i < dt1.Rows.Count; ++i)
{
for (int j = 0; j < dt1.Columns.Count; ++j)
{
xAll[i, j] = Convert.ToDouble(dt1.Rows[i][j].ToString());
}
}
Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.WorksheetFunction wsf = xl.WorksheetFunction;
object[,] reslut = (object[,])wsf.LinEst(yDatapoints.ToArray(), xAll, missing, true);
如果你的xAll有一个维度[12,3],你的ydatapots长度应该是3为正常运行的LinEst()。
using System;
namespace InteropExcel {
class Program {
static void Main(string[] args) {
Random rand = new Random();
double[] yDatapoints = new double[3];
for (int i = 0; i < 3; i++) {
yDatapoints[i]=rand.Next(20, 60);
}
double[,] xAll = new double[12, 3];
for (int i = 0; i < 12; i++) {
for (int j = 0; j < 3; j++) {
xAll[i, j] = rand.Next(2, 100);
}
}
Microsoft.Office.Interop.Excel.Application xl = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.WorksheetFunction wsf = xl.WorksheetFunction;
object[,] result = (object[,])wsf.LinEst(yDatapoints, xAll, Type.Missing, true);
}
}
}
xAll的列大小应该等于ydatapots数组的长度。请尽量让我知道。
下面是Excel的LINEST()函数在c#中的实现。这可能比在Microsoft.Office.Interop.Excel DLL文件上创建一个依赖项更容易。
返回给定数据集的斜率,使用与LINEST()相同的"最小二乘"方法进行归一化:
public static double CalculateLinest(double[] y, double[] x)
{
double linest = 0;
if (y.Length == x.Length)
{
double avgY = y.Average();
double avgX = x.Average();
double[] dividend = new double[y.Length];
double[] divisor = new double[y.Length];
for (int i = 0; i < y.Length; i++)
{
dividend[i] = (x[i] - avgX) * (y[i] - avgY);
divisor[i] = Math.Pow((x[i] - avgX), 2);
}
linest = dividend.Sum() / divisor.Sum();
}
return linest;
}
另外,这里是我写的一个方法来获取Excel的LINEST函数生成的"b"(y截距)值。
private double CalculateYIntercept(double[] x, double[] y, double linest)
{
return (y.Average() - linest * x.Average());
}
由于这些方法只适用于一组数据,如果您希望生成多组线性回归数据,我建议在循环中调用它们。
这个链接帮我找到了答案:https://agrawalreetesh.blogspot.com/2011/11/how-to-calculate-linest-of-given.html