近期工作中出现了一些需要对大量数据进行拟合，从而进行预测或插值等工作的需求，于是整体研究了一波excel的相关方法。虽说如此，也未必研究的很全面，这里仅把过程中涉及的问题和方法分享一下，仅供参考。

为了便于叙述，这里暂且预设一个简单的，不考虑实用性的需求：

有两列数据样本，叫做X和Y。我们仅知X和Y具有相关性，并不能求出二者的函数关系。现在需求得出一个函数表达式，用来大致描述X和Y的关系，且对样本以外的X值预测出一个可靠的Y值。

将XY放在直角坐标系内：

选中两列数据，点选菜单-插入-图标-散点图，excel会自动把第一列视作自变量，第二列视作因变量。或者选择推荐的图表，自行选择需要的图表样式。也可以插入图表以后自行定义，这里不做过多说明。

excel的图表有个很方便的功能，在图表中点击Y系列数据，然后右击：

选择添加趋势线，excel就可以生成一份自动拟合的趋势线

可以看到，图中的趋势线自动生成为直线。点击趋势线，右击选择设置趋势线格式，可以打开一个设置面板：

在这里可以调整趋势线的样式，这里选择对数，勾选显示公式和显示R平方值，得到曲线：

这里的公式便是趋势线的函数表达式。

关于R平方值，引用自官方文档：

在趋势预测中填写前推周期和后推周期，看看曲线对样本的预测情况：

可以看出，对数曲线对于样本的拟合和预测准确度相当有限。于是我们选择分段拟合，前段选择多项式拟合，后段选择对数曲线拟合，分界点选择样本疏密产生变化的点（实际应用中，我使用了其他方法自行判定分段，读者在处理此类问题的时候还需自行设计方法，不同分段使用什么曲线也需要根据样本选择）。

前段曲线由于有起始点和终点，因此不需要对范围外进行预测，多项式拟合是一个十分合适的选择——三次多项式足够满足需求。而后段曲线是后开区间，后推500000个周期查看预测效果：

大致可以判断，趋势比较符合主观预期，R平方值也比之前要高很多，已经很接近于1了。因此这种拟合方法针对这份样本是相对准确的。

那么现在可以直接使用图表中的函数表达式了吗？实践发现是不行的。

手动录入公式中的常数，并各自计算一份关于样本X的函数值，得到：

和样本一起放在一个图表中，根本就看不出来原样本了：

将纵坐标切换成对数显示：

可以看出误差巨大。来看看为什么。

选中公式文本，设置趋势线标签格式，将类别改成数字，把小数位数选为10位，就会发现：

公式的常数实际上有效位数很多。之前进行公式计算的时候使用的常数并不准确，因此造成计算误差巨大。

那么如何获取准确的趋势线公式常数呢，可以使用excel函数Linest来获取计算值。

接下来的内容在官方文档中都有说明，可以对照阅读。但为满足直接阅读需求，这里引用部分文档：

语法Known_y's 必需。 关系表达式 y = mx + b 中已知的 y 值集合。 如果 known_y's对应的单元格区域在单独一列中，则 known_x's 的每一列被视为一个独立的变量。 如果 known_y's对应的单元格区域在单独一行中，则 known_x's 的每一行被视为一个独立的变量。Known_x's 可选。 关系表达式 y = mx + b 中已知的 x 值集合。 known_x's 对应的单元格区域可以包含一组或多组变量。 如果仅使用一个变量，那么只要known_x's 和 known_y's 具有相同的维数，则它们可以是任何形状的区域。 如果用到多个变量，则 known_y's 必须为向量（即必须为一行或一列）。 如果省略 known_x's，则假设该数组为 {1,2,3,...}，其大小与 known_y's 相同。const 可选。 一个逻辑值，用于指定是否将常量 b 强制设为 0。 如果 const 为 TRUE 或省略，b 将按正常计算。 如果 const 为 FALSE，b 将被设为 0，并同时调整 m 值使 y = mx。stats 可选。 一个逻辑值，用于指定是否返回附加回归统计值。 如果 统计信息 为 TRUE， 则 LINEST 返回其他回归统计信息;因此，返回的数组是 {mn，mn-1,...,m1，b;sen，sen-1,...,se1，seb;r2，sey;F，df;ssreg，ssresid}。 如果 stats 为 FALSE 或省略，则函数 LINEST 只返回系数 m 和常量 b。附加回归统计值如下：

简单来看，当仅使用Linest(Known_y's, Known_x's)时，就可以直接执行回归分析，返回对于Known_x's中自变量x的系数m和y轴截距b（当y = mx + b），或返回Known_x's中各项自变量x1、x2……的系数m1、m2……和y轴截距b（当y = m1x1 + m2x2 + ... + b）。如此就可以进行一元或多元的线性回归分析。

当需要做一元多次回归分析时，可以将X的各次幂视作单独的自变量，例如当需求对集合Y作关于集合X的三次回归分析，得到y关于x的三次多项式y=m3X^3+m2x^2+m1x+b，便可以分别对集合X进行三次方、二次方，得出三个集合X^3、X^2、X并带入公式Linest，便可返回一个数组[m3, m2, m1, b]。

使用公式：

其中{1,2,3,……}部分为需要的x的所有次幂的数组组合（不含零次幂）。

当需要做对数回分析时，可以将lnx视作自变量，于是集合Y关于集合LnX的一元线性回归分析返回的值[m, b]便是对数函数y=mln(x)+b的系数和y轴截距。

使用公式：

当需要做指数分析时，可以对公式y=ax^t进行转换： y=ax^t\\ ln y=\ln ax^t\\ =\ln a +\ln x^t\\ =\ln a + t\ln x

即对集合Y做相对于集合X的指数分析等价于对集合LnY做关于集合LnX的一次回归分析，则：

a=e^b\\ t=m

b、m即为返回值[m, b]中对应值。

使用公式：

则函数表达式为：

在excel2019版本以前（大概），数组不能直接在单元格中访问，需要使用数组公式（按CTRL+SHIFT+回车）或者地址访问公式（例如Index）获取值。2019版本可以直接填充单元格。本文使用2019版本，因此不对数组访问作多余处理，统一使用m、b表示，如有相关处理需求，可以查阅官方文档。

于是针对集合Y和集合X，使用公式

得到多项式系数和对数系数

各自计算一份关于样本X的函数值，得到：

放进图表中，纵坐标对数显示：

我们删掉图表读取的，分段外的数值，并取消纵坐标对数显示：

如图，拟合效果合格。

略微扩展自变量域：

其实在使用图表的时候就使用过完全相同的过程进行过拟合和验证了。

于是这里对样本X、Y进行了曲线拟合。

此外，为了方便使用多项式，笔者编写了一个excel函数，放置在【模块】中，便可在表格中调用。

range为使用的多项式系数，直接框选一个范围即可，范围格式参考上文中多项式系数的排布格式；parameter即是输入的自变量。

此函数支持输入任意多个系数，并自动计算对应阶数的多项式。

预测数据趋势的文档

使用分析工具库执行复杂数据分析的文档

LINEST 函数文档