原创： 小花 小花学Excel 2017-12-27
年终一到，又是结算的高峰期。各种佣金，各种税费，各种结算款，各式各样的累进比例问题，花瓣们的年终头痛症估计又要犯了。今天，小花就给大家安利一波，专治各种累计比例问题的Trend函数献给大家。
核心函数：TREND
Trend函数，返回线性回归拟合线的一组坐标轴值（Y值）。基本函数语法
=TREND(一组已知Y值，一组已知的X值，一组新的X值，截距是否为零）
这个函数需要从数学上去理解。设定一个一元一次函数y=kx b，它的直线经过A（1,2）和B（3,4）两点，已知点C对应的X值为2，求A点对应的Y值。此时，TREND函数的第一个参数为A、B两点的Y值（2,4），第二个参数为A、B两点的X值（1,3），第三个参数为C点的X值2，截距不强制为0则第四个参数为TRUE或1或忽略不填。示例如下：
辅助函数：OFFSET和MATCH
掌握了TREND函数，我们这个解决方案就算打好了基础。接下来介绍两个辅助函数，OFFSET和MATCH。
OFFSET是一个偏移函数，即根据指定偏移的行数和列数，将原区域向指定区域偏移，并取一定的函数和列数。这里我们仅介绍最基本的用法，涉及三个参数：
=OFFSET(原区域，向下偏移的函数，向右偏移的列数）
MATCH是一个匹配函数，当第三个参数为1时，执行数字模糊匹配，返回不大于目标值的最大数字所对应的序数。
=MATCH（目标值，匹配区域，模糊/精确匹配）
解决方案：TREND OFFSET MATCH
情景：某公司为了提升销售业绩，制定了一套根据销售额的累进提成比例促销方案，如下图：
如何根据不同销售人员的不同销售额自动计算出对应的奖金？这样的问题，你还在用IF来反复嵌套吗？新的一年就要来了，是时候学个新招数。
累进比例问题其实是一个分段函数求值问题，我们把临界点的值求出来，把它们绘制成折线图，答案就一目了然了。
假设图中的红点代表销售人员的销售额和提成金额的组合，那么我们可以通过X值定位位置，在图中找出对应的Y值。这就是TREND函数的功能，来看公式：
=TREND(OFFSET($F$1:$F$2,MATCH(B2,$E$2:$E$8,1),0),OFFSET($E$1:$E$2,MATCH(B2,$E$2:$E$8,1),0),B2,TRUE)
解释说明：
TREND函数根据给定的X值返回所在直线的对应的Y值，但是需要事先给定一条直线，而对于分段线性情况，我们需要在一堆数据中找到销售人员销售额所在的区间，并将区间首尾两点的X值和Y值作为TREND函数的第一、第二个参数。
OFFSET函数的首个参数确定为二行一列的数字，这满足TREND的参数要求，即首尾两点的X值组或Y值组，通过给定的偏移量向下来锁定销售额所在的分段直线首尾两点。
MATCH函数通过销售额与给点列表的模糊匹配，确定销售额出现的序数，根据序数让OFFSET向下偏移。从而实现用OFFSET函数锁定分段直线的首尾，进而用TREND函数计算出对应的提成金额。
以小花的销售提成为例， $E$2:$E$8中不大于20的最大数10出现在第2个，即MATCH此时的返回值为2；OFFSET使 $F$1:$F$2和$E$1:$E$2向下偏移两行到$F$3:$F$4和$E$3:$E$4，即（500,2100）和（10,30），这是销售额20所在的分段直线的首尾Y值和X值。最后TREND根据一组已知的Y值$F$3:$F$4，一组已知的X值$E$3:$E$4，和给定的新X值20，计算出对应的Y值1300。
唠嗑花
——————
此篇应小花瓣所问而作
以这种图文结合的方式
细化讲解复杂公式
希望小花瓣么喜欢
学习是一种乐趣，分享是一种品质
,
