1. 趋势预测的定义
趋势预测法又称趋势分析法。是指自变量为时间，因变量为时间的函数的模式。
趋势预测法的主要优点是考虑时间序列发展趋势，使预测结果能更好地符合实际。
2. 如何对给定序列计算趋势
|
序号 |
值 |
|
1 |
562 |
|
2 |
345 |
|
3 |
567 |
|
4 |
652 |
|
5 |
249 |
已知以上序列，分别计算趋势线性趋势，指数趋势，对数趋势以及乘幂趋势。
2.1线性趋势
a) 趋势图
b) 计算方法：
/// <summary>
/// 计算序列的线性趋势相关系数
/// </summary>
/// <param name="serials"></param>
public void CalculateLinEstTrendline(Dictionary<double, double> serials)
{
double[] xArray = new double[serials.Count];
double[] yArray = new double[serials.Count];
xArray = serials.Keys.ToArray<double>();
yArray = serials.Values.ToArray<double>();
//计算线性回归线的斜率
object m = worksheetFuncion.Slope(yArray, xArray);
//计算线性回归线的截距
object b = worksheetFuncion.Intercept(yArray, xArray);
//计算 Pearson 乘积矩相关系数的平方
object rsquare = worksheetFuncion.RSq(yArray, xArray);
//趋势参数赋值
linEstParams[0] = (double)rsquare;
linEstParams[1] = (double)m;
linEstParams[2] = (double)b;
}
2.2指数趋势
a) 趋势图
b) 计算方法
/// <summary>
/// 计算序列的指数趋势相关系数
/// </summary>
/// <param name="serials"></param>
public void CalculateExponentialTrendline(Dictionary<double, double> serials)
{
double[] xArray = new double[serials.Count];
double[] yArray = new double[serials.Count];
xArray = serials.Keys.ToArray<double>();
yArray = serials.Values.ToArray<double>();
double[] lnyArray = new double[serials.Count];
for (int i = 0; i < yArray.Length; i++)
{
lnyArray[i] = worksheetFuncion.Ln(yArray[i]);
}
object tmp = worksheetFuncion.LinEst(lnyArray, xArray, oMissing, oMissing);
object b = worksheetFuncion.Index(tmp, 1, oMissing, oMissing);
object c = Math.Exp((double)worksheetFuncion.Index(tmp, 1, 2, oMissing));
//计算 Pearson 乘积矩相关系数的平方
object rsquare = worksheetFuncion.RSq(lnyArray, xArray);
//趋势参数赋值
exponentialParams[0] = (double)rsquare;
exponentialParams[1] = (double)c;
exponentialParams[2] = (double)b;
}
2.3对数趋势
a) 趋势图
b) 计算方法
/// <summary>
/// 计算序列的对数趋势相关系数
/// </summary>
/// <param name="serials"></param>
public void CalculateLogarithmicTrendline(Dictionary<double, double> serials)
{
double[] xArray = new double[serials.Count];
double[] yArray = new double[serials.Count];
xArray = serials.Keys.ToArray<double>();
yArray = serials.Values.ToArray<double>();
double[] lnxArray = new double[serials.Count];
for (int i = 0; i < xArray.Length; i++)
{
lnxArray[i] = worksheetFuncion.Ln(xArray[i]);
}
object tmp = worksheetFuncion.LinEst(yArray, lnxArray, oMissing, oMissing);
object b = worksheetFuncion.Index(tmp, 1, oMissing, oMissing);
object c = worksheetFuncion.Index(tmp, 1, 2, oMissing);
//计算 Pearson 乘积矩相关系数的平方
object rsquare = worksheetFuncion.RSq(yArray, lnxArray);
//趋势参数赋值
logarithmicParams [0] = (double)rsquare;
logarithmicParams[1] = (double)b;
logarithmicParams[2] = (double)c;
}
2.4乘幂趋势
a) 趋势图
b) 计算方法：
/// <summary>
/// 计算序列的乘幂趋势相关系数
/// </summary>
/// <param name="serials"></param>
public void CalculatePowerTrendline(Dictionary<double, double> serials)
{
double[] lnxArray=new double[serials.Count];
double[] lnyArray = new double[serials.Count];
for (int i = 0; i < serials.Count; i++)
{
lnxArray[i] = worksheetFuncion.Ln(serials.Keys.ToArray()[i]);
lnyArray[i] = worksheetFuncion.Ln(serials.Values.ToArray()[i]);
}
object tmp=worksheetFuncion.LinEst(lnyArray ,lnxArray ,oMissing ,oMissing );
object b = worksheetFuncion.Index(tmp, 1, oMissing, oMissing);
object c = Math.Exp((double)worksheetFuncion.Index(tmp, 1, 2, oMissing));
//计算 Pearson 乘积矩相关系数的平方
object rsquare = worksheetFuncion.RSq(lnyArray, lnxArray);
//趋势参数赋值
powerParams[0] = (double)rsquare;
powerParams[1] = (double)c;
powerParams[2] = (double)b;
}
3给定序列，分别计算线性趋势，对数趋势，指数趋势，乘幂趋势，并根据R2值得到最合理的趋势方程。
完整代码
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop.Excel;
/***********************************************************************
* Module:
* Author: hbb0b0@163.com
* Create Date: 2010/11/28
* Summary: Excel内置函数辅助类
***********************************************************************/
namespace TrendlinesLib
{
/// <summary>
/// 趋势计算委托
/// </summary>
/// <param name="x">输入值</param>
/// <returns>返回趋势值</returns>
public delegate double TrendlineExpression(double x);
/// <summary>
/// Excel趋势辅助工具
/// </summary>
public class ExcelTrendLineHelper : IDisposable
{
#region Filed
/// <summary>
/// 用于存放 Application的实例
/// </summary>
private _Application xlApp = null;
/// <summary>
/// Excel 函数集对象
/// </summary>
private WorksheetFunction worksheetFuncion = null;
public WorksheetFunction WorksheetFuncion
{
get { return worksheetFuncion; }
set { worksheetFuncion = value; }
}
/// <summary>
/// 缺省参数的默认值
/// </summary>
private System.Reflection.Missing oMissing = System.Reflection.Missing.Value;
/// <summary>
/// 是否被处置标志
/// </summary>
private bool Disposed = false;
/// <summary>
/// 趋势表达式
/// </summary>
private TrendlineExpression trendLineExpression = null;
/// <summary>
/// 线性趋势相关参数
/// </summary>
private double[] linEstParams = new double[3];
public double[] LinEstParams
{
get { return linEstParams; }
}
/// <summary>
/// 乘幂趋势相关参数
/// </summary>
private double[] exponentialParams = new double[3];
public double[] ExponentialParams
{
get { return exponentialParams; }
}
/// <summary>
/// 对数趋势相关参数
/// </summary>
private double[] logarithmicParams = new double[3];
public double[] LogarithmicParams
{
get { return logarithmicParams; }
}
/// <summary>
/// 指数趋势相关参数
/// </summary>
private double[] powerParams = new double[3];
public double[] PowerParams
{
get { return powerParams; }
}
/// <summary>
/// 趋势表达式
/// </summary>
public TrendlineExpression TrendLineExpression
{
get { return trendLineExpression; }
}
#endregion
#region Property
#endregion
#region Method
#region IDisposable 成员
public void Dispose()
{
Dispose(true);
GC.SuppressFinalize(this);
}
protected virtual void Dispose(Boolean disposing)
{
if (!this.Disposed)
{
if (disposing)
{
xlApp.Application.Quit();
xlApp.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheetFuncion);
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
worksheetFuncion = null;
GC.Collect();
}
this.Disposed = true;
}
}
#endregion
~ExcelTrendLineHelper()
{
Dispose(false);
}
/// <summary>
/// 构造函数
/// </summary>
public ExcelTrendLineHelper()
{
xlApp = new ApplicationClass();
worksheetFuncion = xlApp.WorksheetFunction;
}
/// <summary>
/// 计算序列的线性趋势相关系数
/// </summary>
/// <param name="serials"></param>
public void CalculateLinEstTrendline(Dictionary<double, double> serials)
{
double[] xArray = new double[serials.Count];
double[] yArray = new double[serials.Count];
xArray = serials.Keys.ToArray<double>();
yArray = serials.Values.ToArray<double>();
//计算线性回归线的斜率
object m = worksheetFuncion.Slope(yArray, xArray);
//计算线性回归线的截距
object b = worksheetFuncion.Intercept(yArray, xArray);
//计算 Pearson 乘积矩相关系数的平方
object rsquare = worksheetFuncion.RSq(yArray, xArray);
//趋势参数赋值
linEstParams[0] = (double)rsquare;
linEstParams[1] = (double)m;
linEstParams[2] = (double)b;
}
/// <summary>
/// 计算序列的对数趋势相关系数
/// </summary>
/// <param name="serials"></param>
public void CalculateLogarithmicTrendline(Dictionary<double, double> serials)
{
double[] xArray = new double[serials.Count];
double[] yArray = new double[serials.Count];
xArray = serials.Keys.ToArray<double>();
yArray = serials.Values.ToArray<double>();
double[] lnxArray = new double[serials.Count];
for (int i = 0; i < xArray.Length; i++)
{
lnxArray[i] = worksheetFuncion.Ln(xArray[i]);
}
object tmp = worksheetFuncion.LinEst(yArray, lnxArray, oMissing, oMissing);
object b = worksheetFuncion.Index(tmp, 1, oMissing, oMissing);
object c = worksheetFuncion.Index(tmp, 1, 2, oMissing);
//计算 Pearson 乘积矩相关系数的平方
object rsquare = worksheetFuncion.RSq(yArray, lnxArray);
//趋势参数赋值
logarithmicParams [0] = (double)rsquare;
logarithmicParams[1] = (double)b;
logarithmicParams[2] = (double)c;
}
/// <summary>
/// 计算序列的指数趋势相关系数
/// </summary>
/// <param name="serials"></param>
public void CalculateExponentialTrendline(Dictionary<double, double> serials)
{
double[] xArray = new double[serials.Count];
double[] yArray = new double[serials.Count];
xArray = serials.Keys.ToArray<double>();
yArray = serials.Values.ToArray<double>();
double[] lnyArray = new double[serials.Count];
for (int i = 0; i < yArray.Length; i++)
{
lnyArray[i] = worksheetFuncion.Ln(yArray[i]);
}
object tmp = worksheetFuncion.LinEst(lnyArray, xArray, oMissing, oMissing);
object b = worksheetFuncion.Index(tmp, 1, oMissing, oMissing);
object c = Math.Exp((double)worksheetFuncion.Index(tmp, 1, 2, oMissing));
//计算 Pearson 乘积矩相关系数的平方
object rsquare = worksheetFuncion.RSq(lnyArray, xArray);
//趋势参数赋值
exponentialParams[0] = (double)rsquare;
exponentialParams[1] = (double)c;
exponentialParams[2] = (double)b;
}
/// <summary>
/// 计算序列的乘幂趋势相关系数
/// </summary>
/// <param name="serials"></param>
public void CalculatePowerTrendline(Dictionary<double, double> serials)
{
double[] lnxArray=new double[serials.Count];
double[] lnyArray = new double[serials.Count];
for (int i = 0; i < serials.Count; i++)
{
lnxArray[i] = worksheetFuncion.Ln(serials.Keys.ToArray()[i]);
lnyArray[i] = worksheetFuncion.Ln(serials.Values.ToArray()[i]);
}
object tmp=worksheetFuncion.LinEst(lnyArray ,lnxArray ,oMissing ,oMissing );
object b = worksheetFuncion.Index(tmp, 1, oMissing, oMissing);
object c = Math.Exp((double)worksheetFuncion.Index(tmp, 1, 2, oMissing));
//计算 Pearson 乘积矩相关系数的平方
object rsquare = worksheetFuncion.RSq(lnyArray, lnxArray);
//趋势参数赋值
powerParams[0] = (double)rsquare;
powerParams[1] = (double)c;
powerParams[2] = (double)b;
}
/// <summary>
/// 线性趋势表达式
/// </summary>
/// <param name="x">输入值</param>
/// <returns>预测值</returns>
private double LinEstExpressionTrendline(double x)
{
double y = linEstParams[1] * x + linEstParams[2];
return y;
}
/// <summary>
/// 对数趋势表达式
/// </summary>
/// <param name="x">输入值</param>
/// <returns></returns>
private double LogarithmicExpressionTrendline(double x)
{
double y = logarithmicParams[1] *Math.Log(x) - logarithmicParams[2];
return y;
}
/// <summary>
/// 指数趋势表达式
/// </summary>
/// <param name="x">输入值</param>
/// <returns></returns>
private double ExponentialExpressionTrendline(double x)
{
double y = exponentialParams[1] * Math.Pow(Math.E, x * exponentialParams[2]);
return y;
}
/// <summary>
/// 乘幂趋势表达式
/// </summary>
/// <param name="x">输入值</param>
/// <returns></returns>
private double PowerExpressionTrendline(double x)
{
double y = powerParams[1] * Math.Pow(x,powerParams[2]);
return y;
}
/// <summary>
/// 生成给定序列的趋势表达式
/// </summary>
/// <returns></returns>
public void CreateTrendLineExpression(Dictionary<double, double> serials)
{
//计算
CalculateLinEstTrendline(serials);
CalculateExponentialTrendline(serials);
CalculateLogarithmicTrendline(serials);
CalculatePowerTrendline(serials);
//rsqare与方程关联
Dictionary<double,TrendlineExpression> dic = new Dictionary<double,TrendlineExpression>();
dic.Add(linEstParams[0], LinEstExpressionTrendline );
dic.Add(exponentialParams[0],ExponentialExpressionTrendline);
dic.Add(logarithmicParams[0],LogarithmicExpressionTrendline );
dic.Add(powerParams[0], PowerExpressionTrendline);
//查找rsquare最大值对应的方程
KeyValuePair<double,TrendlineExpression> result= dic.Where(p => p.Key == dic.Keys.Max()).SingleOrDefault();
//委托赋值
trendLineExpression=result.Value ;
}
#endregion
}
}