机器算法验证 - 在 Excel 中执行具有共同 x 截距的多重线性回归？ - 吾爱随笔录

在 Excel 中执行具有共同 x 截距的多重线性回归？

机器算法验证回归多重回归擅长

2022-04-20 12:33:31

我在 Excel 中绘制了一些线性数据集，包括线性趋势线：

在此处输入图像描述

我正要执行 5 个单独的线性回归，所以我可以得到每个“独立”数据集的slope和。但转眼间，我意识到数据可能有一个共同点：y-intercept

在此处输入图像描述

事实上，它甚至可能是：x 截距本身。

所以我现在需要的是运行多个同时线性回归的方法，假设所有线都在一个公共点相交。

这样的线性回归分析方法是否存在？它有名字吗？它存在于Excel中吗？

到目前为止，我能做到的最好的方法是运行五个独立的线性回归，得到每个数据集的斜率和截距：

Slope (m)  Intercept (b)
=========  =============
 1.15287     11484.8  
 0.86301      7173.5
 0.43212      4306.4
 0.25894      2853.6

与您作图slope时y-intercept会看到某种相关性：

在此处输入图像描述

如果我假设我的数据集共享一个x-intercept，那么我可以通过以下方式找到该 x 值：

 y = mx + b
 0 = mx + b
-b = mx
 x = m / -b

这使：

Slope (m)  Intercept (b)  Common x-intercept (assuming their is one)
=========  =============  ============================
 1.15287     11484.8        -9961.9
 0.86301      7173.5        -8312.2
 0.43212      4306.4        -9965.6
 0.25894      2853.6       -11020.2

除了一个非常奇怪的点之外，它收敛得很好。

3个回答

在 Excel 中有几种直接的方法可以做到这一点。

也许最简单的用途LINEST是根据 x 截距的试验值拟合线条。该函数的输出之一是均方残差。用于Solver找到最小化均方残差的 x 截距。如果您在控制Solver时小心翼翼——尤其是通过将 x 截距限制在合理的范围内并给它一个良好的起始值——您应该得到很好的估计。

繁琐的部分涉及以正确的方式设置数据。 我们可以通过隐式模型的数学表达式来解决这个问题。有五组数据：让我们按 $k$ 范围从 $1$ 至 $5$ （图中从下到上）。然后可以通过第二个索引识别每个数据点 $j$ 作为有序对 $x_{kj}, y_{kj}$ . （看起来 $x_{kj} = x_{k'j}$ 对于任何两个索引 $k$ 和 $k'$ ，但这不是必需的。）在这些术语中，模型假设有五个斜率 $\beta_k$ 和一个 x 截距 $\alpha$ ; 那是， $y_{kj}$ 应近似为 $\beta_k (x_{kj}-\alpha)$ . 组合LINEST/Solver解决方案使差异的平方和最小化。或者——这将在评估置信区间时派上用场——我们可以查看 $y_{kj}$ 从具有共同未知方差的正态分布中独立得出 $\sigma^2$ 和手段 $\beta_k(x_{kj}-\alpha)$ .

这个公式有五个不同的系数，建议LINEST使用 $k$ 紧随其后的是一列 $y_{kj}$ .

我使用类似于问题中显示的模拟数据制作了一个示例。下面是数据数组的样子：

[B] [C] [D] [E] [F] [G] [H] [I]
k   x   1   2   3   4   5   y
-----------------------------------------------
355 7355    0   0   0   0   636
355 0   7355    0   0   0   3705
355 0   0   7355    0   0   6757
355 0   0   0   7355    0   9993
355 0   0   0   0   7355    13092
429 7429    0   0   0   0   539
...

奇怪的值7355,7429等，以及所有的零，都是由公式产生的。例如，单元格D3中的那个是

=IF($B2=D$1, $C2-Alpha, 0)

这里，Alpha是一个包含截距的命名单元（当前设置为 -7000）。此公式在粘贴标题为“1”到“5”的列的全部范围时，会在每个单元格中放置一个零，除非 $k$ （显示在最左边的列中）对应于列标题，它在其中放置差异 $x_{kj}-\alpha$ . 这是使用执行多元线性回归所需要的LINEST。表情看起来像

LINEST(I2:I126,D2:H126,FALSE,TRUE)

RangeI2:I126是 y 值的列；范围D2:H126包括五个计算列；FALSE规定 y 截距是强制的 $0$ ; 并TRUE要求提供扩展统计信息。公式的输出占据 6 行 x 5 列的范围，其中前三行可能看起来像

1.296   0.986   0.678   0.371   0.062
0.001   0.001   0.001   0.001   0.001
1.000   51.199
...

奇怪的是（在 Excel 中进行统计时，您必须忍受这种奇怪的现象 :-)，输出列以相反的顺序对应于输入列：因此，1.296是列的估计系数H（对应于 $k=5$ , 我们命名为 $\beta_5$ )0.062而是列的估计系数D（对应于 $k=1$ , 我们命名为 $\beta_1$ ）。

特别注意输出51.199的第 3 行第 2 列LINEST：这是残差平方和的均值。这就是我们想要最小化的。在我的电子表格中，这个值位于 cell U9。在观察情节时，我认为 x 截距肯定介于 $-20000$ 和 $0$ . 这是通过改变来最小化的相应Solver对话框U9 $\alpha$ XIntercept，在此表中命名：

求解器对话框

它几乎立即返回了一个合理的结果。要了解它的性能，请将模拟中设置的参数与以这种方式获得的估计值进行比较：

Parameter Value   Estimate
Alpha     -10000  -9696.2
Beta1        .05    .0619
Beta2        .35    .3710
Beta3        .65    .6772
Beta4        .95    .9853
Beta5       1.25   1.2957
Sigma      50     51.199

使用这些参数，拟合非常好：

具有线性拟合的散点图

通过计算拟合并使用它来计算对数似然度，可以走得更远。 Solver可以一次修改一组参数（初始化为LINEST估计值），以获得小于最大值的对数似然的任何期望值。以通常的方式 - 通过将对数似然度降低一个分位数 $\chi^2$ 分布——您可以获得每个参数的置信区间。事实上，如果你愿意——这是学习最大似然机制如何工作的好方法——你可以LINEST完全跳过该方法并使用Solver最大化对数似然。然而，Solver以这种“赤裸裸”的方式使用——事先不知道参数估计应该是什么——是有风险的。 Solver将很容易停在（较差的）局部最大值。初始估计的组合，例如通过猜测 $\alpha$ 和应用LINEST，以及快速应用Solver来完善这些结果，更加可靠并且往往效果很好。

Excel 不太可能轻松或可靠地做到这一点（除了最简单的统计数据，您真的不应该使用 Excel，有时甚至不使用）。

如果您知道（或认为您知道）常见的 x 截距是什么（不仅仅是从数据中估计它），那么您可以从所有 x 变量中减去该值并进行没有截距的回归（因为该线应该通过 0， 0 现在）。您可以将该模型与每条线都有自己的截距的模型进行比较（如果它们都经过相同的固定截距，那么所有拟合的截距应该同时与 0 没有显着差异。

快速了解您的 x 截距是否可能相同的方法是反转 x 和 y 变量并拟合线，这意味着现在 y 截距将相同，这更容易测试。然而，这也改变了错误的方向，因此回答了一些不同的问题，可能应该跟进一些更正式的问题。

您可以创建 x 截距的引导估计（计算为 -b/m）并使用它来估计截距是否不同。

您可以拟合非线性最小二乘模型来估计具有公共 x 截距的模型，并将其与每个都有自己的截距的模型进行比较，以查看它们是否显着不同（模型的形式为斜率 *（x- x0) 以斜率和 x0 作为参数（x0 是 x 截距）。

您也可以使用贝叶斯技术拟合类似的模型进行比较。

这些中的任何一个都可以在 R 或其他统计包中实现。

我只是一名化学家而不是统计学家，但我知道的最简单的方法是使用虚拟变量。即，对于每个 (n-1) 斜率，在 z 列中分配一个 1 并乘以 x*z。这意味着一个斜率在所有 z 中都为 0。对于具有 3 个斜率的常见截距，它看起来像这样。

batch   y   x   xz 48   xz 58
48          0.9 0   0   0
48          0.7 12  12  0
48          0.6 24  24  0
48          0.6 36  36  0
48          0.66    48  48  0
48          0.59    60  60  0
58          1   0   0   0
58          0.9 12  0   12
58          0.8 24  0   24
58          0.75    36  0   36
58          0.82    48  0   48
69          1   0   0   0
69          0.9 12  0   0
69          0.84    24  0   0
69          0.83    36  0   0

然后使用 Excel 回归添加。摘要输出

Regression Statistics                       
Multiple R  0.838                   
R Square    0.703                   
Adjusted R Square   0.622                   
Standard Error  0.0851                  
Observations    15                  

ANOVA                       
    df  SS  MS  F   Significance F  
Regression  3   0.1886  0.0629  8.68    0.003   
Residual    11  0.0797  0.00725         
Total   14  0.2683              

    Coefficients    Standard Error  t Stat  P-value Lower 95%   Upper 95%
Intercept    0.901          0.0381   23.7   9.E-11   0.817          0.984
months  -0.00199    0.00233 -0.85   0.41    -0.00712    0.00315
xz 48   -0.00441    0.00218 -2.02   0.068   -0.00921    0.00039
xz 58   -0.000725   0.00232 -0.31   0.76    -0.00582    0.00437

方差分析是垃圾，但输出将为您提供每条线的所有方程。例如

Y=0.901-0.00199x for b69
Y=0.901-0.00199x-0.00218zx for b48
Y=0.901-0.00199x-0.000725zx for b58

其它你可能感兴趣的问题

上一篇多元回归中显着影响之间的矛盾，但本身不显着的 t 检验下一篇对应于 R 中这个 gam 模型拟合的数学模型公式是什么？