返回目录:excel表格制作
1、打开Excel 2016,将数据填入表格中
2、按住鼠标左键,选择要分析拟合的数zd据
3、点击上方菜单栏中插入按钮,然后点击推荐的图表
4、在弹出的窗口中选择合适的数据表现形式,点击确定
5、将鼠标光标移动到生成的图线上,然后点击鼠标右键,在下拉菜单中点专击“添加趋势线”
6、右侧会出现属“设置趋势线格式”栏,将滚动条拉到最底部,勾选“显示公式”和“显示R平方值”,拟合的函数方程和R平方值就会显示在图中了
利用Linest函数的第二种用法,多元线性回归,就可以了
把X值增广为知{x,x^2},当作两个变量{x1,x2}去拟合
=INDEX(LINEST(Y值的范围,X值的范围^{1,2}),1,1)
这样就可以取得二次回归方程的第一个系数
同理
=INDEX(LINEST(Y值的范围,X值的范围^{1,2}),1,2) 就是第二个系数
Linest
使用最小二乘法对已知数据进行最佳直线拟合,并返回描述此直线的数道组。因为此函数版返回数值数组,所以必须以数组公式的形式输入。
直线的公式为:
y = mx + b or
y = m1x1 + m2x2 + ... + b(如果有多个区域的 x 值)
式中,因变量权 y 是自变量 x 的函数值。M 值是与每个 x 值相对应的系数,b 为常量。注意 y、x 和 m 可以是向量。LINEST 函数返回的数组为 {mn,mn-1,...,m1,b}。LINEST 函数还可返回附加回归统计值。
语法
LINEST(known_y's,known_x's,const,stats)
在日常数据分析工作当中,回归分析是应用十分广泛的一种数据分析方法,按照涉及自变量的多少,可分为一元回归分析和多元回归分析;按照自变量和因变量之间的关系类型,可分为线性回归分析和非线性回归分析。
回归分析的实施步骤:
1)根据预测目标,确定自变量和因变量
2)建立回归预测模型
3)进行相关分析
4)检验回归预测模型,计算预测误差
5)计算并确定预测值
我们接下来讲解在Excel2007中如何进行回归分析?
一、案例场景
为了研究某产品中两种成分A与B之间的关系,现在想建立不同成分A情况下对应成分B的拟合曲线以供后期进行预测分析。测定了下列一组数据:
二、操作步骤
1、先绘制散点图:具体步骤是选中数据,插入—>图表—>散点图
2、在散点图的数据点上右键—>添加趋势线
3、在弹出的选项框的选项中选择公式和相关系数等,这样就以得到拟合的直线
在图中我们可以看到,拟合的回归方程是 y = 0.223x + 9.121 ,R² = 0.982
附:R2相关系数取值及其意义
我们进一步使用Excel中数据分析的回归分析提供e69da5e6ba907a64334更多的分析变量来描述这一个线性模型
4、选中数据—>数据—>数据分析—>回归
注:本操作需要使用Excel扩展功能,如果您的Excel尚未安装数据分析,可以参考该专题文章的第一篇《用Excel进行数据分析:数据分析工具在哪里?》。
从图形上看,拟合得不错啊,拟合方程的相关系数R平方值0.9224也算不错了。
至于残插图,可以根据回归方程,将散点图原来的X值代入,计算出来的结果减去原来的Y值,得到一组数据,然后就可绘图了。