应用Excel进行定量分析实验

1、1应用应用Excel进行定量分析实验进行定量分析实验主要内容主要内容v1 1、用、用Excel进行回归分析进行回归分析一元线性回归：工具实现和函数实现一元线性回归：工具实现和函数实现非线性回归：对数函数和指数函数非线性回归：对数函数和指数函数v2、用、用Excel进行时间序列分析进行时间序列分析移动平均及线性趋势预测移动平均及线性趋势预测指数平滑及指数平滑及线性趋势预测线性趋势预测时间序列综合分解时间序列综合分解v3、用、用Excel进行层次分析进行层次分析231、用、用Excel进行回归分析进行回归分析n一元线性回归：工具实现一元线性回归：工具实现1.1.打开打开EXCELEXCEL，在工具

2、一栏里找到，在工具一栏里找到“加载宏加载宏”，选，选中中“分析工具库分析工具库”，点击，点击“确定确定”；41、用、用Excel进行回归分析进行回归分析2.2.在在EXCELEXCEL里建立工作表；里建立工作表；3.3.选定数据区域，在选定数据区域，在“工具工具”栏里点击栏里点击“插入图表插入图表”，选择选择“XYXY散点图散点图”，点击，点击“完成完成”，出现如下图所，出现如下图所示情况；示情况；5674.4.鼠标放到散点图的点上，点变成黄色或绿色，鼠标放到散点图的点上，点变成黄色或绿色，点击鼠标右键，选择点击鼠标右键，选择“添加趋势线添加趋势线”; ;1、用、用Excel进行回归分析进行回

3、归分析81、用、用Excel进行回归分析进行回归分析5.5.在在“类型类型”里根据散点图的形状，选择里根据散点图的形状，选择一种类型，这里我们选择一种类型，这里我们选择“线性线性”；6.6.在在“选项选项”里选择里选择“显示公式显示公式”和和“显显示示R R2 2值值”，点，点“确定确定” ” 即可；即可；可以看到回归方程及相关系数，把可以看到回归方程及相关系数，把R R2 2 =0.98=0.98开方得开方得r0.9899r0.9899。9101、用、用Excel进行回归分析进行回归分析n一元线性回归：函数实现一元线性回归：函数实现1.1.打开打开EXCELEXCEL，在工具一栏里找到，在工

4、具一栏里找到“加载宏加载宏”，选中，选中“分析工具库分析工具库-VBA-VBA函数函数”，点击，点击“确定确定”；111、用、用Excel进行回归分析进行回归分析EXCELEXCEL回归分析实现的主要函数：回归分析实现的主要函数：（1 1）截距函数：）截距函数：INTERCEPT(yINTERCEPT(y，x)x)。（2 2）斜率函数：）斜率函数：SLOPE (ySLOPE (y，x)x)。 （3 3）测定系数函数）测定系数函数(R(R平方值平方值) )：RSQ(yRSQ(y，x)x)。（4 4）估计标准误差函数：）估计标准误差函数：STEYX(ySTEYX(y，x)x)。 121、用、用Ex

5、cel进行回归分析进行回归分析2.2.在在EXCELEXCEL里建立工作表；里建立工作表；131、用、用Excel进行回归分析进行回归分析3.3.用用ExcelExcel提供的函数进行计算。提供的函数进行计算。（1 1）在单元格）在单元格A12A12A15A15中分别输入中分别输入“截距截距”、“斜率斜率”、“测定系数测定系数”、“估计标准误差估计标准误差”。（2 2）在单元格）在单元格B12B12中输入公式中输入公式“=INTERCEPT(C2:C11,B2:B11)”“=INTERCEPT(C2:C11,B2:B11)”，回车后显示，回车后显示-79.42015-79.42015。（3 3

6、）在单元格）在单元格B13B13中输入公式中输入公式“=SLOPE(C2:C11,B2:B11)”“=SLOPE(C2:C11,B2:B11)”，回车后显示回车后显示0.80418250.8041825。（4 4）在单元格）在单元格B14B14中输入公式中输入公式“=RSQ(C2:C11,B2:B11)”“=RSQ(C2:C11,B2:B11)”，回，回车后显示车后显示0.68170180.6817018。（5 5）在单元格）在单元格B15B15中输入公式中输入公式“=STEYX(C2:C11,B2:B11)”“=STEYX(C2:C11,B2:B11)”，回车后显示回车后显示2.818073

7、82.8180738。计算结果如图所示。计算结果如图所示。141516171、用、用Excel进行回归分析进行回归分析非线性回归分析：对数函数非线性回归分析：对数函数 y=a+b x1.1.打开打开EXCELEXCEL，建立工作表；，建立工作表；2.2.插入图表插入图表“散点图散点图”；3.3.在添加趋势线中选择对数并在选项中选择显示公式和在添加趋势线中选择对数并在选项中选择显示公式和R R值；值；181、用、用Excel进行回归分析进行回归分析4.4.进行回归分析：工具进行回归分析：工具- -数据分析数据分析- -回归回归 Y Y与与LnLn（X X）呈线性相关）呈线性相关191、用、用Ex

8、cel进行回归分析进行回归分析非线性回归分析：指数函数非线性回归分析：指数函数 lny = lna + b x1.1.打开打开EXCELEXCEL，建立工作表；，建立工作表；2.2.插入图表插入图表“散点图散点图”；3.3.在添加趋势线中选择指数并在选项中选择显示公式和在添加趋势线中选择指数并在选项中选择显示公式和R R值；值；xey201、用、用Excel进行回归分析进行回归分析4.4.进行回归分析：工具进行回归分析：工具- -数据分析数据分析- -回归回归 LnLn（Y Y）与）与X X呈线性相关呈线性相关21有问题请举手！有问题请举手！22年份年份 销售额销售额 年份年份 销售额销售额

9、1998100.002006200.001999120.00 2007190.002000140.00 2008210.002001150.00 2009230.002002170.00 2010230.002003180.00 2011240.002004190.00 2012250.00 2005210.00 2013 ？移动平均及线性趋势预测移动平均及线性趋势预测E已知某商场已知某商场19982012年的年销售额如下表所示，年的年销售额如下表所示，试预测试预测2013年该商场的年销售额（年该商场的年销售额（n=3；n=4；n=5）。）。2. 用用EXCEL进行时间序列分析进行时间序列分析

10、232. 用用EXCEL进行时间序列分析进行时间序列分析移动平均及线性趋势预测移动平均及线性趋势预测1.1.打开打开EXCELEXCEL建立工作表；建立工作表；2.2.生成散点，分析和判断时间序列有无趋势生成散点，分析和判断时间序列有无趋势T T；3.3.由图可知：商场的年销售额有明显的线性增长趋势由图可知：商场的年销售额有明显的线性增长趋势T T。242. 用用EXCEL进行时间序列分析进行时间序列分析4.4.利用利用Excel Excel 进行一次和二次移动平均：工具进行一次和二次移动平均：工具- -数据分析数据分析- -移动平均。移动平均。5.5.建立移动平均线性预测模型。建立移动平均线

11、性预测模型。利用公式求利用公式求a at t和和b bt t的值，得出线性模型。的值，得出线性模型。28.24722.232240215a)(12)2()1(tttMMnbTbayttTt)2()1(2tttMMa)(12)2()1(tttMMnb)2()1(2tttMMa78. 7)22.232240(13215b06.255178. 728.24778. 728.247115120122013TyyyTbayttTt)2()1(2tttMMa)(12)2()1(tttMMnbTbayttTt)2()1(2tttMMa252. 用用EXCEL进行时间序列分析进行时间序列分析262728293

12、031 指数平滑及线性趋势预测：指数平滑及线性趋势预测：=0.3；=0.5；=0.71.1.打开打开EXCELEXCEL建立工作表；建立工作表；2.2.生成散点，分析和判断时间序列有无趋势生成散点，分析和判断时间序列有无趋势T T；3.3.由图可知：商场的年销售额有明显的线性增长趋势由图可知：商场的年销售额有明显的线性增长趋势T T。2. 用用EXCEL进行时间序列分析进行时间序列分析322. 用用EXCEL进行时间序列分析进行时间序列分析4.4.利用利用Excel Excel 进行一次和二次指数平滑：工具进行一次和二次指数平滑：工具- -数据分析数据分析- -移指数移指数平滑（阻尼系数）。平

13、滑（阻尼系数）。5.5.建立指数平滑线性预测模型。建立指数平滑线性预测模型。利用公式求利用公式求a at t和和b bt t的值，得出线性模型。的值，得出线性模型。)(1)2()1(tttSSb)2()1(2tttSSaTbayttTt)2()1(2tttSSaTbayttTt)(1)2()1(tttSSb)2()1(2tttSSaTbayttTt14. 5)33.22432.236(3 . 013 . 015b31.24833.22432.236215a45.253114. 531.24814. 531.2481152013Tyy33343536373839时间序列综合分解及预测时间序列综合

14、分解及预测时间序列综合分解及预测时间序列综合分解及预测假设时间序列存在季节、趋势和不规则因素假设时间序列存在季节、趋势和不规则因素yt = Tt St It计算季节指数计算季节指数St进行移动平均或指数平滑进行移动平均或指数平滑It进行线性趋势预测进行线性趋势预测Tt进行综合预测进行综合预测40原始时间序列原始时间序列41散点图：时间序列因素判断（散点图：时间序列因素判断（S、T、I）具有明显的季节因素具有明显的季节因素S；具有较为明显的趋势因素具有较为明显的趋势因素T；具有不太明显的随机因素具有不太明显的随机因素I。42求季节指数求季节指数4314. 13141. 1156. 1133. 1

15、09. 13109. 1075. 1096. 184. 03834. 0839. 0840. 093. 03908. 0918. 0971. 0第四季度季节指数第三季度季节指数第二季度季节指数第一季度季节指数44消除季节影响后的数据消除季节影响后的数据ttSY /45（1）建立趋势）建立趋势T：用消除季节后的销量进行回归分析：用消除季节后的销量进行回归分析趋势线y = 0.1471x + 5.0996R2 = 0.920801234567805101520tTt147. 0100. 5用趋势得到用趋势得到1到到16期估计值期估计值46190.914.1061.8625.809.1913.752

16、3.684.0765.7084.793.0617.7016.8913.7765.7617.717148.0101.5201918181817171720191817FFSTFSTFTTTT季节指数调整后得到综合预测结果季节指数调整后得到综合预测结果47季节指数调整后得到综合预测结果季节指数调整后得到综合预测结果48综合预测效果图综合预测效果图49（2）建立趋势）建立趋势T：用移动平动建立线性趋势：用移动平动建立线性趋势)(12)2()1(tttMMnbTbayttTt)2()1(2tttMMa127. 0)96. 615. 7(142tbTyTt127. 034. 734. 796. 615. 72ta50趋势值趋势值T和综合预测值和综合预测值51综合预测效果图综合预测效果图52有问题请举手！有问题请举手！533. 用用EXCEL进行层次分析进行层次分析判断矩阵计算判断矩阵计算求特征向量求特征向量求最大特征根求最大特征根一致性检验一致性检验54判断矩阵计算判断矩阵计算1135131112513131211714155712334211A55（1）列归一化）列归一化（1）列归一化）列归一化依次用公式：依次用公式：=B2/SUM(B$2:B$6)56（2）行归一化）行归一化用求和函数：依次用公式用求和函数：依次用