主页 / computer / 问题 / 1697101
Accepted
User1974
Asked: 2022-01-03 15:55:28 +0800 CST

Excel中的正规方程（统计）

  • 772

我正在尝试了解基础设施资产管理行业的统计数据。

我有一个现有的指数回归方程，用于查找给定资产的状况：

Y = B - e ax

在一个相关问题中，有人建议我“可以a直接从正规方程中获取变量”：

基本指数回归

在此处输入图像描述

这听起来很有希望。但作为一个没有数学背景的人，我无法将该等式中的数学符号转换为 Excel 语法。

问题：

如何使用 Excel 2016 语法/公式计算正规方程？

microsoft-excel microsoft-excel-2016

2 个回答

  1. 这是它在 Excel 中的样子：

    在此处输入图像描述

    我试图用伪代码来描述它：

    1. 对于集合中的每条记录，计算 x*LN(21-y)。计算这些值的总和（我们将其称为“sum 1”）。
    2. 对于集合中的每条记录，计算 x^2。计算这些值的总和（我们称之为“sum 2”）。
    3. 将总和 1 除以总和 2。

    来自一位同事：

    i代表特定的观察。所有这些计算都假设有一个固定数量的观测值对，通常称为n 。例如，您的数据中有 20 对观察值。这里，pairs 表示x和y值一起，通常表示为(x, y), (0, 20), (1, 20)....(20, 2)。i代表所有n对中的第i个观察对。

    因此，如果i = 1，这意味着我们指的是第一对，(0, 20)。如果i = 14，我们取第 14 对，(14, 12). 一般来说，在数学上，第i个观察对是(xi, yi)，i在下标中。

    表示i = 1 到n的 sigma 符号本质上意味着我们从第一次观察开始，直到最后一次观察到所有的观察对。

    • 1
  2. Best Answer

    这是解决您在对math.stackexhange上发布的答案的评论中提出的问题的替代方法

    你的模型是：

    y = B - exp(a*x)

    你已经先验地确定了 B = 21。

    该模型不是 y(i) 和 x(i) 值之间的精确关系，因此通常添加一个误差项 e(i) 并将模型表示为

    y(i) = B - exp(a*x(i)) + e(i)

    模型为每个 x 值估计的 y 值用 y'(i) 表示，其中

    y'(i) = B - exp(a*x(i))

    最小二乘法旨在选择使实际 y(i) 值与相应估计值或 y'(i) 值之间的差的平方和最小化的 a 值。

    y(i) - y'(i) = [B - exp(a* x(i)) + e(i)] - [B - exp(a*x(i))] = e(i)

    所以 y(i) 和 y'(i) 值之间的差的平方和是

    Sum[(y(i) - y'(i))^2] = Sum[e(i)^2]

    右侧是平方误差项的总和，因此称为误差平方和或ESS。

    将 y(i) 值转换为 z(i) 值的过程

    z(i) = LN(21 - y(i))

    创建线性模型

    z = 轴

    这允许使用该函数来估计aLINEST的“最佳拟合”值。根据您的答案（或您在 math.stackexchange 上的问题）中提供的数据，a的最佳拟合值为0.147233 - 您在实现正态方程的答案中得出的答案相同。

    与此a值关联的 ESS为 8.27991。但是，该值并不是 ESS 可实现的最低值。当a取值为 0.149140 且对应的 ESS 为 6.66073 时，就会发生这种情况。

    下面的屏幕截图显示了计算。

    在此处输入图像描述

    显示了模型 y = 21 - exp(a*x) 的两个版本的估计 y'(i) 和相关的误差平方和 ESS 值。

    在版本 1 中，使用基于变换模型 z = ax的方法导出a 。LINEST在版本 2 中，a是最小化（未转换）模型的 ESS 的值。下面提供了有关如何获得此值的更多信息。

    对于线性模型，例如 y = mx + c，正态方程提供了一种方便的方法来估计最小化 ESS 的 m 和 c 的值。函数LINEST实现（除其他外）正规方程。

    对于非线性模型（例如 y = 21 - exp(a*x)），通常不存在这样方便的方程，因此需要使用其他方法来找到使 ESS 最小化的a值。

    一种方法是使用搜索方法：本质上为a尝试一系列不同的可能性，然后选择导致最小 ESS 的那个。

    这实际上是下一个屏幕截图显示的内容。它使用微软所谓的数据表。这是一个糟糕的名称选择，因为数据表不是数据表。相反，它是一种用于确定计算值如何随着计算中的一个或两个元素的变化而变化的工具。通过从“假设分析”图标中选择“数据表...”，可以在“数据”菜单的“预测”组的功能区上找到它。

    在此处输入图像描述

    Microsft 创建和使用数据表的文档非常糟糕，所以我将提供一些食谱方法。

    1. 数据表本身在 N2:O23 范围内提供。
    2. 单元格O2包含要探索的计算值。此单元格包含公式，它是与单元格中=J4的值关联的ESSJ3。
    3. a的不同值的可能性被放置在范围内N3:N23，并且 ESS 的结果将出现在单元格中O3:O23。这提供了 21 个可能的a值。这是一个任意选择，数据表可以包含更多或更少的可能值。
    4. 一旦a值到位，选择范围N2:O23并通过从“数据”菜单的“预测”组中的“假设分析”图标中选择“数据表...”来启动“数据表”对话框。
    5. 在对话框中，放入$J$3标有“列输入单元格：”的字段，然后按“确定”按钮。
    6. 现在将使用与a inO3:O23的值对应的 ESS 值填充该范围。更改任何值将更新 ESS 中的值。N3:N23N3:N23O3:O23

    中的a值N3:N23是通过公式设置的，而不是输入的。这些值是使用搜索策略设置的，该策略查看越来越精细的a值集。

    中的 21 a值N3:N23基于位置 11（单元格）中的中心N13值，其上方和下方的单元格依次相差一个增量量，因此 21 个值的整个范围按升序排列。

    搜索策略经过多个步骤，步骤编号由 cell 中的值控制O1。

    在步骤 1 中，中心值设置为 0.15（在单元格中R3），增量设置为 0.001（在单元格中S3），值N3:N23范围为 0.14 到 0.16。此范围是根据版本 1 的 a 值选择的，预计最小 ESS 值将落在此范围内。

    事实证明是这样的。对于从 0.14 开始并增加 0.001 到 0.16 的 21 个a值，相应的 ESS 值从 39 以上开始（当a为 0.14 时），随着a 的增加而减小，直到a的值为 0.149（当 ESS 为 6.66972 时）和然后当a为 0.16时增加达到 70 以上的 ESS 值。这表明使 ESS 最小化的a值在 0.149 附近。

    （如果没有证明在 a 值的范围内找到最小值的情况，则 ESS 值将全部增加或减少，将最小值置于范围的一端。在这种情况下，中心值（在单元格中R3) 将需要调整增量值（在单元格中S3），直到找到中间范围的最小值。）

    对于N3:N23、 单元格中的任何值范围O27，N27分别标识最小 ESS 值和产生最小值的a值。

    产生最小值的值为下一步搜索提供了新的中心值。新的增量是先前的值减少了 10 倍。这些新的中心值和增量值手动输入到列中的“控制表”中R，S并且步数在单元格中手动增加 1 O1。

    搜索通过连续的步骤进行，当无法获得 ESS 值的实际减少时终止。

    屏幕截图显示了搜索步骤 2 的结果。

    • 1

相关问题