我正在尝试学习统计学基础设施资产管理行业。
我有一个现有的指数回归方程，用于查找给定资产的状况：
Y = B - e ax
在一个相关问题中，有人建议我“可以a直接从正规方程“：
这听起来很有希望。但作为一个没有数学背景的人，我很难将该等式中的数学符号转换为 Excel 语法。
问题：
我如何计算正规方程使用 Excel 2016 语法/公式？
答案1
这是一个替代方案，可以解决您在评论中提出的问题的答案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) 值之间的差的平方和最小化。
y(i) - y'(i) = [B - exp(a* x(i)) + e(i)] - [B - exp(a*x(i))] = e(i)
因此 y(i) 和 y'(i) 值之差的平方和为
总和[(y(i) - y'(i))^2] = 总和[e(i)^2]
右边是误差平方和，因此称为误差平方和或储能系统。
将 y(i) 值转换为 z(i) 值的过程通过
z(i) = LN(21 - y(i))
创建线性模型
z = ax
允许使用LINEST函数来估计“最佳拟合”值A。根据您答案中提供的数据（或您在 math.stackexchange 上的问题），最佳拟合值为A是 0.147233——与您在实施标准方程时得出的答案相同。
与此值相关的 ESSA为 8.27991。然而，这个值并不是 ESS 的最小可实现值。当A取值为0.149140，对应的ESS为6.66073。
下面的截图显示了计算结果。
显示了模型 y = 21 - exp(a*x) 的两个版本的估计 y'(i) 和相关的误差平方和 ESS 值。
在版本 1 中，A是使用LINEST基于变换模型 z = ax 的方法得出的。在版本 2 中，A是最小化（未转换）模型的 ESS 的值。有关此值的更多信息，请参见A得到的结果如下。
对于线性模型（例如 y = mx + c），正态方程提供了一种方便的方法来估计最小化 ESS 的 m 和 c 的值。函数LINEST实现（除其他外）正态方程。
对于非线性模型（例如 y = 21 - exp(a*x)），这种方便的方程通常不存在，因此需要使用其他方法来找到A这使得 ESS 最小化。
一种方法是使用搜索方法：本质上尝试一系列不同的可能性来A并选择导致最小 ESS 的那个。
这实际上是下一个屏幕截图显示的内容。它使用微软所谓的数据表。这个名字不太好，因为数据表不是数据表。相反，它是一种工具，用于确定计算值如何随着计算中的一个或两个元素的变化而变化。它位于数据菜单的预测组中的功能区中，通过从“假设分析”图标中选择“数据表...”可以找到它。
Microsft 有关创建和使用数据表的文档非常糟糕，因此我将提供一些指南方法。
- 数据表本身在范围 N2:O23 中提供。
- 单元格包含要探索的计算值。此单元格包含与以下值相关的 ESS 的
O2公式=J4A在 牢房 里J3。 - 不同值的可能性A被放置在范围内
N3:N23，ESS 的结果将显示在单元格中O3:O23。这提供了 21 个可能的值A。这是一个任意选择，数据表可以涉及更多或更少的可能值。 - 一旦A值到位后，选择范围
N2:O23并通过从数据菜单的预测组中的“假设分析”图标中选择“数据表......”来启动数据表对话框。 - 在对话框中，输入
$J$3标有“列输入单元格：”的字段，然后按“确定”按钮。 - 该范围
O3:O23现在将填充与以下值相对应的 ESS 值A中的N3:N23。更改任何值都N3:N23将更新 中的 ESS 值O3:O23。
这A中的值N3:N23是通过公式而不是输入来设置的。这些值是使用搜索策略设置的，该策略会查找越来越精细的值集，以A。
21A中的值N3:N23基于中央位置 11 的单元格中的值，N13其上方和下方的单元格依次相差增量数量，使得整个 21 个值的范围按升序排列。
搜索策略经过多个步骤，步骤数由单元格中的值控制O1。
在步骤 1 中，中央值设置为 0.15（在单元格中R3），并且增量设置为 0.001（在单元格中S3），给出的值范围N3:N23从 0.14 到 0.16。此范围是根据版本 1 的值选择的A，预计最低 ESS 值将落在此范围内。
事实证明确实如此。对于A从 0.14 开始，以 0.001 为增量增加到 0.16，对应的 ESS 值从 39 以上开始（当A为 0.14)，减少为A增加直到A值为 0.149（当 ESS 为 6.66972 时），然后增加，达到 ESS 值超过 70 时A为 0.16。这表明A这使得 ESS 最小化，其值在 0.149 附近。
（如果没有证明在值范围内存在最小值，则 ESS 值要么全部增加，要么全部减少，将最小值置于范围的一端。在这种情况下中央值（在单元格中R3）将需要调整，可能会增加增量值（在单元格中S3），直到找到中间范围的最小值。）
对于 中的任何值范围N3:N23，单元格O27和N27分别确定最小 ESS 值和 的值A产生最小值。
的价值A生产最低限度，提供新的中央下一步搜索的值。新的增量是之前的值减少了 10 倍。这些新的中央和增量R在“控制表”的列和中手动输入值，S并在单元格中手动将步骤号增加 1 O1。
搜索通过连续的步骤进行，当 ESS 值无法实现实际降低时终止。
屏幕截图显示了搜索第 2 步的结果。
答案2
在 Excel 中其显示内容如下：
我尝试用伪代码来描述它：
- 对于集合中的每条记录，计算 x*LN(21-y)。计算这些值的总和（我们称之为“总和 1”）。
- 对于集合中的每条记录，计算 x^2。计算这些值的总和（我们称之为“总和 2”）。
- 将和 1 除以和 2。
来自一位同事：
我表示特定的观察结果。所有这些计算都假设有一个固定的数字，通常称为n，观测值对。例如，您的数据中有 20 对观测值。此处，对表示X和是值一起，通常表示为
(x, y), (0, 20), (1, 20)....(20, 2)。我代表我'在所有观察中n对。因此，如果我= 1，这意味着我们指的是第一对，
(0, 20)。如果我= 14，我们取第 14 对，(14, 12)。一般来说，从数学上讲，我第对观测值(xi, yi)是我为下标。西格玛符号表示我= 1 至n，本质上意味着我们从第一个观察开始，直到最后一个观察，获取所有的观察对。