ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 295380|回复: 414

[原创]图表趋势线公式系数的计算公式

  [复制链接]

TA的精华主题

TA的得分主题

发表于 2003-3-4 00:30 | 显示全部楼层 |阅读模式
本帖已被收录到知识树中,索引项:趋势线应用
论坛又受到攻击,我的帖子不见了,重传一个,供参考。 h3035aLw.zip (4.7 KB, 下载次数: 25467)

评分

5

查看全部评分

TA的精华主题

TA的得分主题

发表于 2003-8-26 12:52 | 显示全部楼层
用于制定企业计划销售指标很有用,但我不知道这三个趋线图中哪个更科学一点,大家共同探讨一下。万一日后老板让你根据以往销售记录制定个计划,你也有个制定依据。这么好的贴子为什么没人顶呢?怪了。。。

TA的精华主题

TA的得分主题

发表于 2003-10-20 20:15 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2004-1-30 12:43 | 显示全部楼层
非常好!挺实用的!谢谢chenjun

TA的精华主题

TA的得分主题

发表于 2004-1-31 21:03 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2004-3-11 15:35 | 显示全部楼层

[原创]图表趋势线公式系数的计算公式

我知道好,看不懂,请高人讲解,!!!

TA的精华主题

TA的得分主题

发表于 2004-7-5 14:43 | 显示全部楼层
好是好,就是看不懂啊,chen大侠能不能像解释那些函数一样来个详细说明啊,那一定感激不尽!

TA的精华主题

TA的得分主题

 楼主| 发表于 2004-7-6 21:26 | 显示全部楼层
以下是引用tigerwoods在2004-7-5 14:43:00的发言: 好是好,就是看不懂啊,chen大侠能不能像解释那些函数一样来个详细说明啊,那一定感激不尽!

LINEST

使用最小二乘法计算对已知数据进行最佳直线拟合,并返回描述此直线的数组。因为此函数返回数值数组,故必须以数组公式的形式输入。

直线的方程为:

y = mx + b 或 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)

Known_y's 是关系表达式 y = mx + b 中已知的 y 值集合。

  • 如果数组 known_y's 在一列中,则 known_x's 的每一列都被当作单独的变量。
  • 如果数组 known_y's 在一行中,则 known_x's 的每一行都被当作单独的变量。

Known_x's 是关系表达式 y = mx + b 中已知的可选 x 值集合。

  • 数组 known_x's 中包括一个或多个变量集合。如果只用到一个变量,只要 known-y's 和 known-x's 维数相同,它们可以是任何形状的选定区域。如果用到不只一个变量,known_y's 必须是向量(就是说,必须是一行或一列的区域)。
  • 如果省略 known_x's,则假设该数组是 {1,2,3...},其大小与 known_y's 相同。

Const 为一逻辑值,指明是否强制使常数 b 为 0。

  • 如果 const 为 TRUE 或省略,b 将被正常计算。
  • 如果 const 为 FALSE,b 将被设为 0,并同时调整 m 值使 y = mx。

Stats 为一逻辑值,指明是否返回附加回归统计值。

  • 如果 stats 为 TRUE,函数 LINEST 返回附加回归统计值,这时返回的数组为 {mn,mn-1,...,m1,b;sen,sen-1,...,se1,seb;r2,sey;F,df;ssreg,ssresid}。
  • 如果 stats 为 FALSE 或省略,函数 LINEST 只返回系数 m 和常数项 b。

附加回归统计值如下:

统计值 说明
se1,se2,...,sen 系数 m1,m2,...,mn 的标准误差值。
Seb 常数项 b 的标准误差值(当 const 为 FALSE时,seb = #N/A )
r2 判定系数。Y 的估计值与实际值之比,范围在 0 到 1 之间。如果为 1,则样本有很好的相关性,Y 的估计值与实际值之间没有差别。而在另一方面,如果判定系数为 0,则回归方程不能用来预测 Y 值。关于计算 r2 的方法的详细信息,请参阅本节后面的“说明”。
sey Y 估计值的标准误差。
F F 统计值或 F 观察值。使用 F 统计可以判断因变量和自变量之间是否偶尔发生过观察到的关系。
df 自由度。用于在统计表上查找 F 临界值。所查得的值和函数 LINEST 返回的F统计值的比值可用来判断模型的置信度。
ssreg 回归平方和。
ssresid 残差平方和。

下面的图示显示了附加回归统计值返回的顺序。

说明

  • 可以使用斜率和 y 轴截距描述任何直线:

    斜率 (m): 通常记为 m,如果需要计算斜率,则选取直线上的两点,(x1,y1) 和 (x2,y2);斜率等于 (y2 - y1)/(x2 - x1)。

    Y 轴截距 (b):

    通常记为 b,直线的 y 轴的截距为直线通过 y 轴时与 y 轴交点的数值。

  • 当只有一个自变量 x 时,可直接利用下面公式得到斜率和 y 轴截距值:

    斜率: INDEX(LINEST(known_y's,known_x's),1)

    Y 轴截距: INDEX(LINEST(known_y's,known_x's),2)

  • 数据的离散程度决定了函数 LINEST 计算的精确度。数据越接近直线形,LINEST 模型就越精确。函数 LINEST 使用最小二乘法来判定最适合数据的模型。当只有一个自变量 x 时,m 和 b 是根据下面公式计算出的:

  • 直线和曲线函数 LINEST 和 LOGEST 可用来计算与给定数据拟合程度最高的直线或指数曲线。但需要判断两者中哪一个更适合数据。可以用函数 TREND(known_y's,known_x's) 来计算直线,或用函数 GROWTH(known_y's, known_x's) 来计算指数曲线。如果函数不带参数 new_x's,可在实际数据点上根据直线或曲线来预测 y 的数组值,然后可以将预测值与实际值进行比较。还可以用图表方式来直观地比较二者。
  • 回归分析时,Microsoft Excel 计算每一点的 y 的估计值和实际值的平方差。这些平方差之和称为残差平方和。然后 Microsoft Excel 计算 y 的实际值和平均值的平方差之和。称为总平方和(回归平方和 + 残差平方和)。残差平方和与总平方和的比值越小,判定系数 r2 的值就越大,r2 是表示回归分析方程的结果反映变量间关系的程度的标志。
  • 对于返回结果为数组的公式,必须以数组公式的形式输入。
  • 当在参数中输入 known_x's 这样的数组常数时,可以用逗号分隔同一行中的数值,用分号分隔数值行。根据国别设置,分隔符有可能不同。
  • 注意,如果 y 的回归分析预测值超出了用来计算方程的 y 值的范围,它们可能是无效的。
上面这个解释你要看懂了,LINEST函数实际返回的是一个数组,系数中的一部分是多元线性回归的各个系数,我只不过是用INDEX函数从这个数组中取出相应的系数,对于幂函数、对数函数、指数函数的关系是先转换为线性关系求回归系数再反算回去。

TA的精华主题

TA的得分主题

发表于 2004-7-7 12:39 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2004-7-7 16:09 | 显示全部楼层

我看了贴,能否再加工一下,备注上此图表的具体含义及用途等等。

期待你的完善。

您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-29 17:39 , Processed in 0.052177 second(s), 10 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表