华人论坛 - Powered by Discuz! Board

标题: [讨论] 急求助 Excel函数相关小问题（更新） [打印本页]

作者: puccafly 时间: 2014-10-5 14:14 标题: 急求助 Excel函数相关小问题（更新）

本帖最后由 puccafly 于 2014-10-6 10:42 编辑

发到这版可能不太合适，但实在没办法，该版是我觉得坛子里最高大上最高技术含量版了。所以问题被解决前希望斑竹先别删帖

是很认真地需要解惑，请不要嫌弃我不懂Excel，因为以前没有用上。帮助者会送花花
=========================待解问题的分割线==========================================================

情况就是Excel 菜鸟画了个趋势发展图，图是分别输入七组原始数据然后它自动出现生成的，并且还白送了个公式，结果现在关键就是只有黑线的直线公式，搞不定橙色曲线公式

最新小结与待解问题如下，希望继续有人能够解答：
1] R方的专业术语是？R呢？如果可以的话，求英文版术语名称
2] R的范围及代表意义（0<R方<1 ？楼下说1就重合，那是代表越接近1月精准么？）
3] 为嘛我在Excel 里输入了7个年份收入与住房数值，结果它画出的是有R方=0,9767的偏差度的一个曲线图呢？我的最终目的只是要通过这7个年份收入与住房的已知数值，画出个相应曲线，再列出该曲线方程，用以解析从09到30年根据收入不同而产生的住房面积变化啊，怎么就辣么难。。

度娘说：拟合公差是描述样条曲线与控制点之间平均差别的数值。差值越大，曲线越流畅，但精确度越低；反之，差值太小，曲线的平滑度越差，复杂性越大。
拟合公差用于设置输入的数据点和拟和生成的样条曲线之间的逼近程度。一般为正值，其值越小，曲线越接近于数据点，如果为0，则样条曲线穿过每一数据点。
她怎么说0 重合？！

图片附件: 图一.PNG (2014-10-6 10:29, 45.76 KB) / 下载次数 78
http://yayabay.com/forum/attachment.php?aid=765562&k=7d2868bdd4020913952c8d2ed2ea7b42&t=1736329788&sid=SsRrIw

作者: puccafly 时间: 2014-10-5 14:31

怎么也没人理我？感觉看股票的人看个Excel分分钟的小事，难道是个美丽的误会？

作者: not4weak 时间: 2014-10-5 14:51

本帖最后由 not4weak 于 2014-10-5 15:00 编辑

回复 2# puccafly

啊，这个是简单的线性回归。
图一P点应该是数据点，然后那个公式给出了一条线性拟合 - 你把X的值带入，当然得不出原来的数据，因为是拟合公式得出的。
R平方是拟合的好坏指标。

作者: not4weak 时间: 2014-10-5 15:02

图二问题不清楚，你的图7 在哪里？

作者: puccafly 时间: 2014-10-5 15:10

回复 puccafly

啊，这个是简单的线性回归。P是信度检验值，R平方是拟合的好坏指标。
not4weak 发表于 2014-10-5 14:51

1] P是点的简写，是我自己命名的
2] 拟合的指标好坏是相对于点09到点30的连接直线而言的吧，怪不得图二R=1呢，能不直么？看来图二的原始数据没意义，根本就是直线公式算出来的吧
3] 图一中比如09年的数值x代入y却不等于原数据，是因为R不是1？那这二元一次方程就不是针对这条橙色曲线的了难道？

感谢回答，送花花

作者: puccafly 时间: 2014-10-5 15:12

图二问题不清楚，你的图7 在哪里？
not4weak 发表于 2014-10-5 15:02

没有图七，七组原始数据指的是每个图中点09到点30的那7个点

作者: not4weak 时间: 2014-10-5 15:20

本帖最后由 not4weak 于 2014-10-5 15:26 编辑

回复 5# puccafly

3）不会等于原始数据的，除非是一条直线，R方是一个衡量指标。完全弥合的话是1.0 。。。基本含义是能解释的方差占总的方差的比例。

你的方程都是线性的，没有非线性的回归。那条曲线就是一个简单的连线，当然你可以做非线性回归，不过都是可以取对数LOG之后变为线性的。（比如 Y = 3.4X^2+4.5X+ 89 ，你可以取LOG变为线性）。

不客气，以后有啥基本的物理统计问题，欢迎周末来问。不过要准备糖果，爱因斯坦经常给小学生解答问题。

作者: fibo5 时间: 2014-10-5 15:58

完全弥合是1.0 .
both charts look almost perfect goodness of FIT

作者: puccafly 时间: 2014-10-5 16:07

你要把小学生改称文科生，我就会考虑准备糖果，不然就是毒糖果

皮埃斯：我对爱因斯坦的印象只有这个，你确定很想要变这样？真的好么，会不会过于飘逸了点儿。。

图片附件: ayst.png (2014-10-5 16:05, 331.63 KB) / 下载次数 61
http://yayabay.com/forum/attachment.php?aid=765535&k=2dd3027f17e8cfbb49d4c5f70ee73657&t=1736329788&sid=SsRrIw

作者: puccafly 时间: 2014-10-5 16:10

图二中 R方=1 ，1不是1,0么难道？

作者: 雅筠儿 时间: 2014-10-6 05:17

没有办法找到最正确的公式你只能通过不同的方法找到best fit line. R^2越接近1表示你原来的点和所画出的现在的函数线的距离越短。大概是这个意思吧。其实我倒是不明白图一，明明是一次函数为什么不是直线

作者: puccafly 时间: 2014-10-6 10:35

没有办法找到最正确的公式你只能通过不同的方法找到best fit line. R^2越接近1表示你原来的点和所画出的现在 ...
雅筠儿发表于 2014-10-6 05:17

感谢回答。因为之前的图里直线被我隐形了，已修改

而且我一直以为它附带的是橙色曲线的公式，怪不得点代入数值不对，因为点根本就不在直线上阿

橙色区线的函数无法求出么？那我怎么解释它的生成存在，难道跟人说那就是7个点连巴连巴弄出这么一个？！

作者: 雅筠儿 时间: 2014-10-6 19:14

回复 12# puccafly
我觉得是的，但是也有可能找best fit line的时候他代入的公式不是一次方程而是多次方程或者是含有e的那种方程。

作者: 雅筠儿 时间: 2014-10-6 19:18

回复 13# 雅筠儿

还有R2我记得好像是linear regression

作者: not4weak 时间: 2014-10-6 20:58

感谢回答。因为之前的图里直线被我隐形了，已修改

而且我一直以为它附带的是橙色曲线的公式，怪不 ...
puccafly 发表于 2014-10-6 10:35

啊？还没明白？
橙色曲线的函数是样条差值啊 - Spline interpolation - 只要在连接处的7 个点保持一阶导数和二阶导数连续就可以了。

作者: vegetable8 时间: 2014-10-6 21:03

回复 15# not4weak

数学家

作者: 大胖熊 时间: 2014-10-6 22:03

not4weak 都是教授级别的人啦，线性回归，曲线拟合，样条插值，这些都是大三
就学的。以后大家有问题，尽管问他。

。

作者: qzhou3 时间: 2014-10-7 00:45

本帖最后由 qzhou3 于 2014-10-7 00:55 编辑

回复 1# puccafly

我用英文解释一遍
What you have is a linear regression. For linear regression, the R2 ranges from 0 to 1. If it is 0, the two variables, X and Y, are not associated. If R2 is 1, the two variables are perfectly associated. In real life, variables are rarely perfectly associated. Thus R2 is rarely 1.
The lower case r, called correlation coefficient, is the correlation between two variables. It ranges from -1 to +1, where 1 is total positive correlation, 0 is no correlation, and −1 is total negative correlation. The upper case R is the same as r if there is only one independent variable (predictor).
R2 is called coefficient of determination. It tells how much variance in Y can be explained by X. In your case, how much variance in living space is explained by income.
In your example, you use X, the income, to predict Y, the living space in square meters. Your model can predict 97.67% of the variance in living space. This is a super strong association, almost too good to be true...
When you use excel or statistical software to perform regression analysis, the calculation is based on least squares method. That is, the sum of squares of the distance from your data points to the regression line is the smallest. Hence your points are rarely sitting on the line; when a point is on the line, the error for that point is 0.
Your orange line is not a formula. It is just a connection of the points.

作者: vegetable8 时间: 2014-10-7 01:06

又是一个数学家或统计学家

作者: vegetable8 时间: 2014-10-7 01:07

回复 19# vegetable8

我是指qzhou3

作者: not4weak 时间: 2014-10-7 10:27

回复 puccafly

我用英文解释一遍
What you have is a linear regression. For linear regression, th ...
qzhou3 发表于 2014-10-7 00:45

不对，那条orange line 是一个函数，不是连线

作者: qzhou3 时间: 2014-10-7 10:38

回复 21# not4weak

quadratic function?

作者: not4weak 时间: 2014-10-7 10:43

本帖最后由 not4weak 于 2014-10-7 10:47 编辑

回复 22# qzhou3

你也是对的，她这数据比较规则，在eXcel 下可以加trendline 公式就出来了。 polynomial 也可我试一下

作者: not4weak 时间: 2014-10-7 10:45

本帖最后由 not4weak 于 2014-10-7 10:48 编辑

11.059ln(x) -78.068

Wow! 这么规则 r^2 =1.0

估计是伪造的吧？

作者: qzhou3 时间: 2014-10-7 10:47

回复 24# not4weak

Too good to be true :) Look like fake data

作者: puccafly 时间: 2014-10-7 11:14

"只要在连接处的7 个点保持一阶导数和二阶导数连续就可以了"

所以最终可求得一个那个橙色曲线的函数方程？不是6个。。
艾玛，这种时候就无比遗憾没有工科南喷有，一个会算样条插值的

作者: puccafly 时间: 2014-10-7 11:20

11.059ln(x) -78.068

Wow! 这么规则 r^2 =1.0

估计是伪造的吧？
not4weak 发表于 2014-10-7 10:45

中国指数研究院的网上文件中当下的数据，图二的那组画出来完全就是个直线，图一的也没好多少阿这样看来

中指院阿，名字看起来很正式，怎么实力跟文献这么坑爹？！

作者: not4weak 时间: 2014-10-7 12:08

本帖最后由 not4weak 于 2014-10-7 12:09 编辑

"只要在连接处的7 个点保持一阶导数和二阶导数连续就可以了"

所以最终可求得一个那个橙色曲线的函数方程 ...
puccafly 发表于 2014-10-7 11:14

理科南，哈哈，彻底明白了吧

作者: puccafly 时间: 2014-10-9 10:13

理科南，哈哈，彻底明白了吧
not4weak 发表于 2014-10-7 12:08

我一直以为你的头像是条飘逸的红色纱裙。。。

欢迎光临华人论坛 (http://yayabay.com/forum/)