如何从给定的数对中找到指数公式的系数 [英] How to find coefficients of exponential formula from given pairs of numbers

问题描述

我有44对值，如下:

X   Y       X   Y
1   1303    23  1471979
2   2689    24  1855942
3   4373    25  2339735
4   7421    26  3096779
5   10037   27  3903252
6   13333   28  5153666
7   20665   29  6199765
8   26849   30  8185063
9   37305   31  10314552
10  47879   32  13588513
11  65572   33  17122961
12  89127   34  21576366
13  106217  35  27187657
14  152379  36  35747356
15  193512  37  45043166
16  244886  38  56755887
17  309618  39  71513915
18  414190  40  93863574
19  552058  41  118269663
20  660106  42  149021335
21  925396  43  187768443
22  1108885 44  246053390

我将它们放在Excel中，然后生成的图形看起来像是给出了指数公式

I put them in Excel and the graph that is produced, looks like it gives an exponential formula

当我尝试使用产生的公式计算Y值时，结果与原始结果有很大的差异.我假设指数公式必须具有一个额外的系数，并且其完整格式必须为:

When I tried to calculate the Y values with the produced formula, the results had big differencies from the original ones. I assume that the exponential formula must have an extra coefficient and it's full format must be the following:

有没有一种方法可以在Excel中计算c系数?

Is there a way to calculate the c coefficient in Excel?

推荐答案

我想您可以使用指数趋势公式进行近似.这将为您提供顶部曲线.您可以对曲线求差以得出近似的c值.

I am guessing you could approximate using the exponential trend formula. This will give you the top curve. You can difference the curves to work out an approximate c value.

指数趋势方程

等式:y = c * e ^(b * x)

Equation: y = c *e ^(b * x)

c:= EXP(INDEX(LINEST(LN(y)，x)，1,2))

c: =EXP(INDEX(LINEST(LN(y),x),1,2))

b:= INDEX(LINEST(LN(y)，x)，1)

b: =INDEX(LINEST(LN(y),x),1)

有关数据的示例，请参见此处:

See here for an example with your data:

然后绘图

更多信息在这里:

http://spreadsheetpage.com/index.php/tip/chart_trendline_formulas/

并查看有关预测和趋势的ExcelIsFun Youtube系列.

And see the ExcelIsFun Youtube series on Forecasting and Trends.

这篇关于如何从给定的数对中找到指数公式的系数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！