☰

非线性曲线公式

tag-icon • tag-icon microsoft-excel regression
非线性曲线公式

我有如下数据：

在此处输入图片描述

我需要一个 Excel 公式，根据左边的美元数计算出右边的数量。

这不是线性回归。其轻微的曲线很重要。

我只需要沿着这样的曲线生成虚拟数据。没什么特别的，也不必精确。理想情况下，我会将某种对数、指数或倒数放入其他单元格中，以根据需要更改曲线——但曲线很重要。

Excel 中有哪些公式能够使给定 A 列的值生成接近 B 列的值？

答案1

Tl;DR - 公式是B = A * (21000000 - A) / 20000000000

我是怎么会这样呢？

好的，第一步，很明显 B 是 A/1000 减去某个值

B = A / 1,000 - fn(A)

fn(A) 的值是

A           fn(A)
1000000     0
2000000     100
3000000     300
4000000     600
5000000     1000

看起来像三角形数字 1、3、6、10、15 等（当然乘以 100）

公式是n(n+1) / 2- 虽然我们希望第一项为 0，n(n-1)/2

为了我们的目的n = A / 1000000，想将结果乘以 100

所以

So fn(A) = 100 * ((A / 1000000 * (A - 1000000) / 1000000) / 2 )

或者

fn(A) = 100 * A * (A - 1000000) / 2000000000000

然后变成

fn(A) = A * (A - 1000000) / 20000000000

所以，现在我们有

B = A / 1000 - A * (A - 1000000) / 20000000000

使用基本方法进行简化

B = A * (21000000 - A) / 20000000000

答案2

你需要拉格朗日插值

我在 sagemath 中执行此操作，但您也可以手动计算。

我首先创建一个需要插入的点列表

sage: pts
[(1000000, 1000),
 (2000000, 1900),
 (3000000, 2700),
 (4000000, 3400),
 (5000000, 4000),
 (6000000, 4500),
 (7000000, 4900),
 (8000000, 5200),
 (9000000, 5400),
 (10000000, 5500)]

然后我对有理数中的这些点进行拉格朗日插值

sage: Q.<x> = PolynomialRing(QQ)
sage:
sage: Q.lagrange_polynomial(pts)
-1/20000000000*x^2 + 21/20000*x

B1我将上述多项式写为单元格中的 excel 公式=(-1/20000000000)*POWER(A1,2)+(21/20000)*A1

然后我将单元格复制到剩余的 9 个单元格（单元格 ID 将进行调整）

在此处输入图片描述

由于得到的多项式是二次的，因此 3 个点就足够了，但我们无法通过查看这些点来知道这一点。

只需 3 个点就足以进行插值。

例如

pts = [(1000000, 1000), (2000000, 1900), (3000000, 2700)]

如果您想手动尝试，只需尝试上述 3 个点或拉格朗日插值公式列表中的任意 3 个点。

相关内容