Excel中的二次和三次回归 [英] Quadratic and cubic regression in Excel

问题描述

我有以下信息：

 身高体重
 
 170 65 
 167 55 
 189 85 
 175 70 
 166 55 
 174 55 
 169 69 
 170 58 
 184 84 
 161 56 
 170 75 
 182 68 
 167 51 
 187 85 
 178 62 
 173 60 
 172 68 
 178 55 
 175 65 
 176 70 
  

我想在Excel中构造二次和三次回归分析。我知道如何通过Excel中的线性回归来做，但是二次方和立方的呢？我已经搜索了很多资源，但找不到任何有用的东西。

解决方案

您需要使用Excel的 LINEST function：

  = LINEST（known_y's，[known_x's]，[const ]，[stats]）
  

背景

  = LINEST（B2： B21，A2：A21）
  

其返回单个值，线性斜率（ m ）根据公式：

您的数据：

是：

< h2>未记录的三角形k Number 1

您还可以使用Excel来计算使用指数为 x 不同的公式的回归从 1 ，例如x ^1.2

：

使用公式：

  = LINEST （B2：B21，A2：A21 ^ 1.2）
  

您的数据：

是：

您不限于一个指数

Excel的 LINEST 函数也可以计算多个回归，同时在 x 上具有不同的指数，例如：

  = LINEST（B2：B21，A2：A21 ^ {1,2}）
  

注意：如果语言环境设置为欧洲（十进制符号，），则应以分号和反斜杠替换逗号，即 = LINEST（B2：B21; A2：A21 ^ {1\2}）

现在Excel将同时使用x ¹ 和x ² 计算回归：

如何实际执行

这个不太可能的部分没有明显的方法来查看其他回归值。为了做到这一点，您需要：

• 选择包含您的公式的单元格：

  
  
  




• 扩展选择左边2个空格（你需要选择至少3个单元格宽）：

  
  
  




• 按 F2




• 按 Ctrl + Shift + 输入

  
  
  

您现在将看到3个回归常数：

  y = -0.01777539x ^ 2 + 6.864151123x + -591.3531443 
  

Bonus Chatter

我有一个函数，我想使用一些指数执行回归：

y = m×x k + b

但是我没有知道指数。所以我更改了 LINEST 函数来使用单元格引用：

  = LINEST（B2：B21，A2：A21 ^ F3，true，true）
  

使用Excel然后输出完整统计（第4个参数 LINEST ）：

我告诉求解器以最大化R ² ：

它可以找出最好的指数。哪个数据：

是：

I have the following information:

  Height    Weight

    170     65
    167     55
    189     85
    175     70
    166     55
    174     55
    169     69
    170     58
    184     84
    161     56
    170     75
    182     68
    167     51
    187     85
    178     62
    173     60
    172     68
    178     55
    175     65
    176     70

I want to construct quadratic and cubic regression analysis in Excel. I know how to do it by linear regression in Excel, but what about quadratic and cubic? I have searched a lot of resources, but could not find anything helpful.

解决方案

You need to use an undocumented trick with Excel's LINEST function:

=LINEST(known_y's, [known_x's], [const], [stats])

Background

A regular linear regression is calculated (with your data) as:

=LINEST(B2:B21,A2:A21)

which returns a single value, the linear slope (m) according to the formula:

which for your data:

is:

Undocumented trick Number 1

You can also use Excel to calculate a regression with a formula that uses an exponent for x different from 1, e.g. x^1.2:

using the formula:

=LINEST(B2:B21, A2:A21^1.2)

which for you data:

is:

You're not limited to one exponent

Excel's LINEST function can also calculate multiple regressions, with different exponents on x at the same time, e.g.:

=LINEST(B2:B21,A2:A21^{1,2})

Note: if locale is set to European (decimal symbol ","), then comma should be replaced by semicolon and backslash, i.e. =LINEST(B2:B21;A2:A21^{1\2})

Now Excel will calculate regressions using both x¹ and x² at the same time:

How to actually do it

The impossibly tricky part there's no obvious way to see the other regression values. In order to do that you need to:

• select the cell that contains your formula:

• extend the selection the left 2 spaces (you need the select to be at least 3 cells wide):

• press F2

• press Ctrl+Shift+Enter

You will now see your 3 regression constants:

  y = -0.01777539x^2 + 6.864151123x + -591.3531443

Bonus Chatter

i had a function that i wanted to perform a regression using some exponent:

y = m×x^k + b

But i didn't know the exponent. So i changed the LINEST function to use a cell reference instead:

=LINEST(B2:B21,A2:A21^F3, true, true)

With Excel then outputting full stats (the 4th paramter to LINEST):

i tell the Solver to maximize R²:

And it can figure out the best exponent. Which for you data:

is:

这篇关于Excel中的二次和三次回归的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！