应用Excel进行定量实验解决方案.ppt

应用Excel进行定量分析实验 主要内容 1、用Excel进行回归分析 一元线性回归：工具实现和函数实现 非线性回归：对数函数和指数函数 2、用Excel进行时间序列分析 移动平均及线性趋势预测 指数平滑及线性趋势预测 时间序列综合分解 3、用Excel进行层次分析 1、用Excel进行回归分析 一元线性回归：工具实现 1.打开EXCEL，在工具一栏里找到“加载宏”，选中“分析工具库”，点击“确定”； 1、用Excel进行回归分析 2.在EXCEL里建立工作表； 3.选定数据区域，在“工具”栏里点击“插入图表”，选择“XY散点图”，点击“完成”，出现如下图所示情况； 1、用Excel进行回归分析 4.鼠标放到散点图的点上，点变成黄色或绿色，点击鼠标右键，选择“添加趋势线”; 1、用Excel进行回归分析 5.在“类型”里根据散点图的形状，选择一种类型，这里我们选择“线性”； 6.在“选项”里选择“显示公式”和“显示R2值”，点“确定” 即可； 可以看到回归方程及相关系数，把R2 =0.98开方得r≈0.9899。 1、用Excel进行回归分析 一元线性回归：函数实现 1.打开EXCEL，在工具一栏里找到“加载宏”，选中“分析工具库-VBA函数”，点击“确定”； 1、用Excel进行回归分析 EXCEL回归分析实现的主要函数： （1）截距函数：INTERCEPT(y，x)。 （2）斜率函数：SLOPE (y，x)。 （3）测定系数函数(R平方值)：RSQ(y，x)。 （4）估计标准误差函数：STEYX(y，x)。 1、用Excel进行回归分析 2.在EXCEL里建立工作表； 1、用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。计算结果如图所示。 1、用Excel进行回归分析 非线性回归分析：对数函数 y=a+b·㏒ x 1.打开EXCEL，建立工作表； 2.插入图表“散点图”； 3.在添加趋势线中选择对数并在选项中选择显示公式和R值； 1、用Excel进行回归分析 4.进行回归分析：工具-数据分析-回归 Y与Ln（X）呈线性相关 1、用Excel进行回归分析 非线性回归分析：指数函数 lny = lna + b x 1.打开EXCEL，建立工作表； 2.插入图表“散点图”； 3.在添加趋势线中选择指数并在选项中选择显示公式和R值； 1、用Excel进行回归分析 4.进行回归分析：工具-数据分析-回归 Ln（Y）与X呈线性相关 有问题请举手！ 移动平均及线性趋势预测  2. 用EXCEL进行时间序列分析 移动平均及线性趋势预测 1.打开EXCEL建立工作表； 2.生成散点，分析和判断时间序列有无趋势T； 3.由图可知：商场的年销售额有明显的线性增长趋势T。 2. 用EXCEL进行时间序列分析 4.利用Excel 进行一次和二次移动平均：工具-数据分析-移动平均。 5.建立移动平均线性预测模型。 利用公式求at和bt的值，得出线性模型。 2. 用EXCEL进行时间序列分析 指数平滑及线性趋势预测：α=0.3；α=0.5；α=0.7 1.打开EXCEL建立工作表； 2.生成散点，分析和判断时间序列有无趋势T； 3.由图可知：商场的年销售额有明显的线性增长趋势T。 2. 用EXCEL进行时间序列分析 4.利用Excel 进行一次和二次指数平滑：工具-数据分析-移指数平滑（阻尼系数）。 5.建立指数平滑线性预测模型。 利用公式求at和bt的值，得出线性模型。 时间序列综合分解及预测 时间序列综合分解及预测 假设时间序列存在季节、趋势和不规则因素 yt = Tt ? St ? It 计算季节指数St 进行移动平均或指数平滑It 进行线性趋势预测Tt 进行综合预测 原始时间序列 散点图：时间序列因素判断（S、T、I） 求季节指数 消除季节影响后的数据 季节指数调整后得到综合预测结果 季节指数调整后得到综合预测结果 综合预测效果图 有问题请举手！ 3. 用EXCEL进行层次分析 判断矩阵计算 —求特征向量 —求最大特征根 —一致性检验 判断矩阵