应用Excel进行定量分析实验

应用Excel进行定量分析实验1主要内容1、用Excel进行回归分析一元线性回归：工具实现和函数实现非线性回归：对数函数和指数函数2、用Excel进行时间序列分析移动平均及线性趋势预测指数平滑及线性趋势预测时间序列综合分解3、用Excel进行层次分析21、用Excel进行回归分析一元线性回归：工具实现1.打开EXCEL，在工具一栏里找到“加载宏”，选中“分析工具库”，点击“确定”；31、用Excel进行回归分析2.在EXCEL里建立工作表；3.选定数据区域，在“工具”栏里点击“插入图表”，选择“XY散点图”，点击“完成”，出现如下图所示情况；4564.鼠标放到散点图的点上，点变成黄色或绿色，点击鼠标右键，选择“添加趋势线”;1、用Excel进行回归分析71、用Excel进行回归分析5.在“类型”里根据散点图的形状，选择一种类型，这里我们选择“线性”；6.在“选项”里选择“显示公式”和“显示R2值”，点“确定”即可；可以看到回归方程及相关系数，把R2=0.98开方得r≈0.9899。891、用Excel进行回归分析一元线性回归：函数实现1.打开EXCEL，在工具一栏里找到“加载宏”，选中“分析工具库-VBA函数”，点击“确定”；101、用Excel进行回归分析EXCEL回归分析实现的主要函数：（1）截距函数：INTERCEPT(y，x)。（2）斜率函数：SLOPE(y，x)。（3）测定系数函数(R平方值)：RSQ(y，x)。（4）估计标准误差函数：STEYX(y，x)。111、用Excel进行回归分析2.在EXCEL里建立工作表；121、用Excel进行回归分析3.用Excel提供的函数进行计算。（1）在单元格A12～A15中分别输入“截距”、“斜率”、“测定系数”、“估计标准误差”。（2）在单元格B12中输入公式“=INTERCEPT(C2:C11,B2:B11)”，回车后显示-79.42015。（3）在单元格B13中输入公式“=SLOPE(C2:C11,B2:B11)”，回车后显示0.8041825。（4）在单元格B14中输入公式“=RSQ(C2:C11,B2:B11)”，回车后显示0.6817018。（5）在单元格B15中输入公式“=STEYX(C2:C11,B2:B11)”，回车后显示2.8180738。计算结果如图所示。131415161、用Excel进行回归分析非线性回归分析：对数函数y=a+b·㏒x1.打开EXCEL，建立工作表；2.插入图表“散点图”；3.在添加趋势线中选择对数并在选项中选择显示公式和R值；171、用Excel进行回归分析4.进行回归分析：工具-数据分析-回归

Y与Ln（X）呈线性相关181、用Excel进行回归分析非线性回归分析：指数函数lny=lna+bx1.打开EXCEL，建立工作表；2.插入图表“散点图”；3.在添加趋势线中选择指数并在选项中选择显示公式和R值；191、用Excel进行回归分析4.进行回归分析：工具-数据分析-回归

Ln（Y）与X呈线性相关20有问题请举手！21

年份

销售额

年份

销售额

1998100.002006200.001999120.002007190.002000140.002008210.002001150.002009230.002002170.002010230.002003180.002011240.002004190.002012250.002005210.00

2013

？移动平均及线性趋势预测已知某商场1998～2012年的年销售额如下表所示，试预测2013年该商场的年销售额（n=3；n=4；n=5）。2.用EXCEL进行时间序列分析222.用EXCEL进行时间序列分析移动平均及线性趋势预测1.打开EXCEL建立工作表；2.生成散点，分析和判断时间序列有无趋势T；3.由图可知：商场的年销售额有明显的线性增长趋势T。232.用EXCEL进行时间序列分析4.利用Excel进行一次和二次移动平均：工具-数据分析-移动平均。5.建立移动平均线性预测模型。利用公式求at和bt的值，得出线性模型。242.用EXCEL进行时间序列分析252627282930指数平滑及线性趋势预测：α=0.3；α=0.5；α=0.71.打开EXCEL建立工作表；2.生成散点，分析和判断时间序列有无趋势T；3.由图可知：商场的年销售额有明显的线性增长趋势T。2.用EXCEL进行时间序列分析312.用EXCEL进行时间序列分析4.利用Excel进行一次和二次指数平滑：工具-数据分析-移指数平滑（阻尼系数）。5.建立指数平滑线性预测模型。利用公式求at和bt的值，得出线性模型。32333435363738时间序列综合分解及预测时间序列综合分解及预测假设时间序列存在季节、趋势和不规则因素yt=Tt

St

It计算季节指数St进行移动平均或指数平滑It进行线性趋势预测Tt进行综合预测39原始时间序列40散点图：时间序列因素判断（S、T、I）具有明显的季节因素S；具有较为明显的趋势因素T；具有不太明显的随机因素I。41求季节指数4243消除季节影响后的数据44（1）建立趋势T：用消除季节后的销量进行回归分析用趋势得到1到16期估计值45季节指数调整后得到综合预测结果46季节指数调整后得到综合预测结果47综合预测效果图48（2）建立趋势T：用移动平动建立线性趋势49趋势值T和综合预测值50综合预测效果图51有问题请举手！523.用EXCEL进行层次分析判断矩阵计算—求特征向量—求最大特征根—一致性检验53判断矩阵计算54（1）列归一化（1）列归一化依次用公式：=B2/SUM(B$2:B$6)55（2）行归一化用求和函数：依次用公式=sum(B8:F8)=H8/SUM(H$8:H$12)（3）得到特征向量W＝（0.255，0.460，0.053，