在日常的工作中，我们对于公司产品的销售额、竞争对手以及市场推广都会用到同比、环比以及同比\环比增长率进行分析，通过剖析这些数据变化的内在原因，来帮助企业制定更好的营销策略。而在通常用的excel表格里，我们是无法直接能查看到同比、环比、同比增长率、环比增长率的，需要花费很长时间去找这些数据，然后再进行统计，并运用函数，才能计算得出，十分麻烦。

　　 今天，就教大家一个新技能，一键自动生成环比、同比、以及环比/同比增长率分析。坐好板凳，听课时间开始啦！

　　第一招：用快速功能直接计算 同比值

　　第二招：用系统自带的函数公式进行计算 同比值

　　同理：第一招：用快速功能进行计算：同比增长率

　　第二招：用系统自带函数公式进行计算 ：同比增长率

　　然后，就可以直接在一张分析报表上得到本期，同期，同比增长率分析

　　是不是so快？　　相对应的环比以及环比增长率，也可以通这这两种方法进行操作，是不是节省了很多时间~~

在 Excel 中，计算房贷中的等额本息可以用PMT函数，不需用等额本息计算公式，这样既方便又快速；但计算等额本金没有直接的函数，需要用等额本金计算公式一项项计算；下就是它们用 Excel 计算的具体操作方法，操作中所用版本均为 Excel 2016。

一、用Excel计算房贷中的等额本息

假如购房贷款 820000 元，商业贷款年利率为 4.9%，贷款时间 25 年，还款方式为等额本息；现在要求用 Excel 计算每月应该还款数额和需还总贷款数额。

（一）用PMT函数计算

1、计算每月应该还款数额。采用等额本息还款每月还款数额一样，所以只需计算一次；双击 D3 单元格，把公式 =PMT(B3/12,C3*12,A3) 复制到 D3，按回车，返回 -4,745.98，负号表示需还款。

2、 计算需还总贷款数额。双击 E3，输入公式 =D3*12*C3，按回车，返回 -1,423,795.38；操作过程步骤，如图1所示：

3、公式 =PMT(B3/12,C3*12,A3) 说明：

A、Pmt函数的表达式为 PMT(Rate, Nper, Pv, [Fv], [Type])；Rate 表示贷款月利率，Nper 表示贷款总期数（月数），Pv 表示本金；Fv 为可选项，表示未来值，若省略，默认取 0；Type 也为可选项，表示支付时间，0 或省略表示期末，1 表示期初。

B、公式中的 B3/12 用于计算月利率，12 表示一年 12 个月；C3*12 计算总期数，A3 是本金，公式省略了后两个参数。

（二）用等额本息公式计算

1、等额本息计算月还款公式为：（贷款本金×月利率×（1＋月利率）^还款月数）/（（1＋月利率）^还款月数－1），把数值代入此公式变 =(A3*B3/12*(1+B3/12)^(C3*12))/((1+B3/12)^(C3*12)-1)，双击 D5 单元格，把公式复制到 D5，按回车，返回 4,745.98，跟用PMT函数计算结果一致；确保当前选项卡为“开始”，单击“常规”下拉列表框，在弹出的选项中选择“货币”，则数字前自动加上元符号；再单击“字体颜色”图示，选择“浅蓝色”，则文字变为所选颜色；操作过程步骤，如图2所示：

2、计算需还总贷款方法跟上面一样，用已计算出的每月还款数额乘上贷款年数再乘每年12个月即可。

提示：如果贷款利率变了，例如加息或减息了，只需修改 B3 中数值，“每月还款和需还总贷款”会自动重新计算返回新值，演示如图3所示：

二、用Excel计算房贷中的等额本金

1、计算每月需还本金。采用等额本金还款每月需还本金不变，它的计算公式为：每月需还本金 = 本金/还款月数，把数值代入公式变为 =A3/(C3*12)，双击 D3 单元格，把公式复制到 D3，按回车，返回 2,733.33。

2、生成还款期数（即月数）。由于贷款 25 年，每年 12 个月，因此还款期数为 300；选中 B6，输入 1，单击一下 A7 退出输入状态，再次选中 A6，按住 Alt 键，依次按一次 H、F、I、S 键，打开“序列”窗口，选择左上角的“列”，“终止值”输入 300，按回车，则生成 1 到 300 的序列；按快捷键“Ctrl + 向下方向键”定位到表格最后一行，305 行的第 1 列中正是 300。再按快捷键“Ctrl + 向上方向键”定位到表格第 5 行。

3、计算每月需还利息。由于采用等额本金还款每月需还利息都不一样，因此每月都需要计算，计算公式为：每月需还利息 =（本金 - 已归还本金累计额）×月利率，代入数值变为 =(A$3-(A6-1)*D$3)*B$3/12，双击 B6，把公式复制到 B6，按回车，接着把单元格格式设置为“货币”，则数值变为 3,348.33；把鼠标移到 B6 右下角的单元格填充柄上，鼠标变为加号后，双击左键，则 1 到 300 期都计算出所需还利息。

4、计算每月应还款。只需把每月应还本金和利息加起来，双击 C6，把公式 =D$3+B6 复制到 C6，按回车，返回第一个月（期）应还款 6,081.67；同样用双击单元格填充柄的方法返回剩余月数的应还款，按“Ctrl + 向下方向键”定位到表格最后一行，已经有了数值。

5、计算需还总贷款。计算方法有两种，一种为把所有“月还款”相加，可用公式 =SUM(C6:C305)；另一种为把每月应还本金乘总月数再加所有月应还利息，可用公式 =D3*12*C3+SUM(B3:B305)，这种方法更精确一点；假如用后一种方法，把后一个公式复制到 E3，按回车，返回需还总贷款 1,323,924.22；操作过程步骤，如图4所示：

6、公式说明：

（1）计算月利息公式。

A、在 =(A$3-(A6-1)*D$3)*B$3/12 中，A$3 表示对行的绝对引用，在往下拖时，A3 不会变为 A4、A5 等，D$3 和 B$3 也是一个意思。

B、A6-1 用于计算已还款期数，(A6-1)*D$3 用于计算已归还本金累计额，当公式在 B6 时，A6 为 1，(1-1)*D$3 结果为 0；当公式在 B7 时，A7 为 2，(2-1)*D$3 恰好是第 1 期还款本金；其它的以此类推。B$3/12 用于求月利率。

（2）计算需还总贷款公式。在 =D3*12*C3+SUM(B3:B305) 中，Sum 用于求 B3 到 B305 的和，即计算所有月的利息总和；公式的意思是把所有月的本金和利息加起来。

三、比较等额本息和等额本金哪个还款少

以上面的对等额本息和等额本金的计算结果为例进行比较，它们的结果如图5所示：

1、等额本息和等额本金需还总贷款分别为 1,607,614.01 和 1,323,924.22，在同等条件下，前者比后者需多还 283689.79 元；这主要是由于后者需要还更多的利息，两者需还总利息分别为 787,614.01 和 503,924.17。

2、尽管等额本息需要还款比等额本金多，但它每月还款固定，且前期还款比等额本金少；而等额本金每月还款不固定（主要是每月还款利息不固定），前期需还款多然后逐渐减少，从图5可以看出，第 2 个月所需还款比第 1 个月减少 11 元左右，第 3 个月比第 2 个月又减少 11 元。

上次给大家分享了《2017年最全的excel函数大全14—统计函数（3）》，这次分享给大家统计函数（4）。

FORECAST.ETS.CONFINT 函数

说明

返回指定目标日期预测值的置信区间。 95% 的置信区间意味着 95% 的未来点预计将处于 FORECAST.ETS 预期结果中的此范围内（使用正态分布）。 使用置信区间可以帮助掌握预测模型的准确度。较小的区间意味着在针对此特定点的预测中有更多置信。

用法

预测. ets . confint ( target_date 、值、时间线,[ confidence_level ]、[ seasonality ],[ data_completion ],[汇总])

FORECAST.ETS.CONFINT 函数用法具有以下参数：

target_date 必需。要为其预测值的数据点。目标日期可以是日期/时间或数字。 如果目标日期在历史时间线结束前按时间顺序排序，则 FORECAST.ETS.CONFINT 将返回 #NUM! 错误。

值必需。 值是历史值，您要为其预测下一点。

时间线必需。独立数组或数值数据区域。时间线中的日期之间必须有一致步长且不能为零。 无需对时间线进行排序，因为 FORECAST.ETS.CONFINT 会对其进行隐式排序，以进行计算。 如果无法在提供的时间线中识别一致步长，则 FORECAST.ETS.CONFINT 将返回 #NUM! 错误。 如果时间线包含重复值，则 FORECAST.ETS.CONFINT 将返回 #VALUE! 错误。 如果时间线和值的范围大小不同，则 FORECAST.ETS.CONFINT 将返回 #N/A 错误。

confidence_level 可选。0 和 1 之间的一个数值（独占），指示计算置信区间的置信度。 例如，对于 90% 的置信区间，将计算 90% 置信度（90% 的未来点将处于此预测范围内）。 默认值为 95%。 对于 (0,1) 范围外的数值，FORECAST.ETS.CONFINT 将返回 #NUM! 错误。

季节性可选。一个数值。 默认值为 1，意味着 Excel 自动检测季节性进行预测，并使用正整数作为季节性模式的长度。 0 表示无季节性，意味着预测为线性预测。 正整数指示算法使用此长度模式作为季节性。 对于其他任何值，FORECAST.ETS.CONFINT 将返回 #NUM! 错误。

最大支持 seasonality 是8,760（一年中的小时数）。 该数字上方的任何 seasonality 将导致# NUM ! 错误。

数据完成可选。虽然时间线需要数据点之间的一致步长，但 FORECAST.ETS.CONFINT 支持最多 30% 的丢失数据，并会自动对其进行调整。 0 表示算法将缺少的点视为零。 通过将缺少的点算为邻接点的平均值，默认值 1 将计算缺少的点。

聚合可选。虽然时间线需要数据点之间的一致步长，但 FORECAST.ETS.CONFINT 会聚合具有相同时间戳的多个点。聚合参数是一个数值，指明要用于聚合具有相同时间戳的多个值的方法。默认值 0 将使用 AVERAGE，而其他选项为 SUM、COUNT、COUNTA、MIN、MAX、MEDIAN。

FORECAST.ETS.SEASONALITY 函数

说明

返回 Excel 针对指定时间系列检测到的重复模式的长度。 FORECAST.ETS.Seasonality 可用于FORECAST.ETS之后，确定已检测到的自动季节性和 FORECAST.ETS 使用的季节性。 虽然它可以独立于 FORECAST.ETS 使用，但鉴于相同的输入参数会影响数据完整性，函数会受到限制，因为在该函数中检测到的季节性与 FORECAST.ETS 使用的季节性相同。

用法

FORECAST.ETS.SEASONALITY(值, 时间线,[data_completion], [聚合])

FORECAST.ETS.SEASONALITY 函数用法具有下列参数：

值 必需。 值是历史值，您要为其预测下一点。时间线 必需。独立数组或数值数据区域。时间线中的日期之间必须有一致步长且不能为零。 无需对时间线进行排序，因为 FORECAST.ETS.SEASONALITY 会对其进行隐式排序，以进行计算。 如果无法在提供的时间线中识别一致步长，则 FORECAST.ETS.SEASONALITY 将返回 #NUM! 错误。 如果时间线包含重复值，则 FORECAST.ETS.SEASONALITY 将返回 #VALUE! 错误。 如果时间线和值的范围大小不同，则 FORECAST.ETS.SEASONALITY 将返回 #N/A 错误。数据完成 可选。虽然时间线需要数据点之间的一致步长，但 FORECAST.ETS.SEASONALITY 支持最多 30% 的丢失数据，并会自动对其进行调整。 0 表示算法将缺少的点视为零。 通过将缺少的点算为邻接点的平均值，默认值 1 将计算缺少的点。聚合 可选。虽然时间线需要数据点之间的一致步长，但 FORECAST.ETS.SEASONALITY 会聚合具有相同时间戳的多个点。聚合参数是一个数值，指明要用于聚合具有相同时间戳的多个值的方法。默认值 0 将使用 AVERAGE，而其他选项为 SUM、COUNT、COUNTA、MIN、MAX、MEDIAN。

FORECAST.ETS.STAT 函数

说明

返回作为时间序列预测的结果的统计值。

统计值类型表明此函数请求的统计信息。

用法

FORECAST.ETS.STAT(值, 时间线, statistic_type, [季节性], [data_completion], [聚合])

FORECAST.ETS.STAT 函数用法具有以下参数：

值 必需。 值是历史值，您要为其预测下一点。时间线 必需。独立数组或数值数据区域。时间线中的日期之间必须有一致步长且不能为零。 无需对时间线进行排序，因为 FORECAST.ETS.STAT 会对其进行隐式排序，以进行计算。 如果无法在提供的时间线中识别一致步长，则 FORECAST.ETS.STAT 将返回 #NUM! 错误。 如果时间线包含重复值，则 FORECAST.ETS.STAT 将返回 #VALUE! 错误。 如果时间线和值的范围大小不同，则 FORECAST.ETS.STAT 将返回 #N/A 错误。statistic_type 必需。 数字值介于1和8之间，指示哪些统计值将不会为计算预测返回。季节性 可选。一个数值。 默认值为 1，意味着 Excel 自动检测季节性进行预测，并使用正整数作为季节性模式的长度。 0 表示无季节性，意味着预测为线性预测。 正整数指示算法使用此长度模式作为季节性。 对于其他任何值，FORECAST.ETS.STAT 将返回 #NUM! 错误。

最大支持 seasonality 是8,760（一年中的小时数）。 该数字上方的任何 seasonality 将导致# NUM ! 错误。

数据完成 可选。虽然时间线需要数据点之间的一致步长，但 FORECAST.ETS.STAT 支持最多 30% 的丢失数据，并会自动对其进行调整。 0 表示算法将缺少的点视为零。 通过将缺少的点算为邻接点的平均值，默认值 1 将计算缺少的点。聚合 可选。虽然时间线需要数据点之间的一致步长，但 FORECAST.ETS.STAT 会聚合具有相同时间戳的多个点。聚合参数是一个数值，指明要用于聚合具有相同时间戳的多个值的方法。默认值 0 将使用 AVERAGE，而其他选项为 SUM、COUNT、COUNTA、MIN、MAX、MEDIAN。

下列可选的统计信息可以返回：

Alpha ets 算法的参数 返回参数较高值基值为最近的数据点的详细粗细。Beta ets 算法的参数 返回参数的趋势值较高值为最近的趋势的详细粗细。ets 算法的伽玛参数 返回参数 seasonality 值较高值为最近使用的季节性期间内的详细粗细。mase 跃点 返回绝对按比例缩放的错误平均值跃点数度量值预测的准确性。smape 跃点 返回绝对跃点数基于百分比错误的准确性度量值的百分比错误的对称平均值。mae 跃点 返回绝对跃点数基于百分比错误的准确性度量值的百分比错误的对称平均值。rmse 跃点 返回 根 平均值平方值错误跃点数预测和观察值之间的差异的度量。检测到步骤大小 返回历史时间线中检测到的步骤大小。

FORECAST.LINEAR 函数

说明

根据现有值计算或预测未来值。 预测值为给定 x 值后求得的 y 值。 已知值为现有的 x 值和 y 值，并通过线性回归来预测新值。 可以使用该函数来预测未来销售、库存需求或消费趋势等。

用法

预测.线性( x , known _ y ' s , known _ x ' s )

FORECAST.LINEAR 函数用法具有以下参数：

X 必需。 需要进行值预测的数据点。

Known_y's 必需。 相关数组或数据区域。

Known_x's 必需。 独立数组或数据区域。

FREQUENCY 函数

说明

计算数值在某个区域内的出现频率，然后返回一个垂直数组。 例如，使用函数 FREQUENCY 可以在分数区域内计算测验分数的个数。 由于 FREQUENCY 返回一个数组，所以它必须以数组公式的形式输入。

用法

FREQUENCY(data_array, bins_array)

FREQUENCY 函数用法具有下列参数：

Data_array必需。 要对其频率进行计数的一组数值或对这组数值的引用。 如果 data_array 中不包含任何数值，则 FREQUENCY 返回一个零数组。Bins_array必需。 要将 data_array 中的值插入到的间隔数组或对间隔的引用。 如果 bins_array 中不包含任何数值，则 FREQUENCY 返回 data_array 中的元素个数。

备注

在选择了用于显示返回的分布结果的相邻单元格区域后，函数 FREQUENCY 应以数组公式的形式输入。返回的数组中的元素比 bins_array 中的元素多一个。 返回的数组中的额外元素返回最高的间隔以上的任何值的计数。 例如，在对输入到三个单元格中的三个值范围（间隔）进行计数时，确保将 FREQUENCY 输入到结果的四个单元格。 额外的单元格将返回 data_array 中大于第三个间隔值的值的数量。函数 FREQUENCY 将忽略空白单元格和文本。对于返回结果为数组的公式，必须以数组公式的形式输入。

案例

GAMMA 函数

说明

返回 gamma 函数值。

用法

GAMMA(number)

GAMMA 函数用法具有下列参数：

Number 必需。 返回一个数字。

备注

GAMMA 使用以下公式：

Г(N+1) = N * Г(N)如果 Number 为负整数或 0，则 GAMMA 返回 错误值 #NUM!。如果 Number 包含无效的字符，则 GAMMA 返回 错误值 #VALUE!。

案例

GAMMA.DIST 函数

说明

返回伽玛分布函数的函数值。 可以使用此函数来研究呈斜分布的变量。 伽玛分布通常用于排队分析。

用法

GAMMA.DIST(x,alpha,beta,cumulative)

GAMMA.DIST 函数用法具有下列参数：

X必需。 用来计算分布的数值。Alpha必需。 分布参数。Beta必需。 分布参数。 如果 beta = 1，则 GAMMA.DIST 返回标准伽玛分布。Cumulative必需。 决定函数形式的逻辑值。 如果 cumulative 为 TRUE，则 GAMMA.DIST 返回累积分布函数；如果为 FALSE，则返回概率密度函数。

备注

如果 x、alpha 或 beta 为非数值型，则 GAMMA.DIST 返回 错误值 #VALUE!。如果 x 0，则 GAMMA.DIST 返回 错误值 #NUM!。如果 alpha ≤ 0 或 beta ≤ 0，则 GAMMA.DIST 返回 错误值 #NUM!。伽玛概率密度函数的计算公式如下：

标准伽玛概率密度函数为：

当 alpha = 1 时，GAMMA.DIST 返回如下的指数分布：

对于正整数 n，当 alpha = n/2，beta = 2 且 cumulative = TRUE 时，GAMMA.DIST 以自由度 n 返回 (1 - CHISQ.DIST.RT(x))。当 alpha 为正整数时，GAMMA.DIST 也称为爱尔朗 (Erlang) 分布。

案例

GAMMA.INV 函数

说明

返回伽玛累积分布函数的反函数值。 如果 p = GAMMA.DIST(x,...)，则 GAMMA.INV(p,...) = x。 使用此函数可以研究有可能呈斜分布的变量。

用法

GAMMA.INV(probability,alpha,beta)

GAMMA.INV 函数用法具有下列参数：

Probability必需。 伽玛分布相关的概率。Alpha必需。 分布参数。Beta必需。分布参数。如果 beta = 1，则 GAMMA.INV 返回标准伽玛分布。

备注

如果任一参数为文本型，则 GAMMA.INV 返回 错误值 #VALUE!。如果 probability 0 或 probability 1，则 GAMMA.INV 返回 错误值 #NUM!。如果 alpha ≤ 0 或 beta ≤ 0，则 GAMMA.INV 返回 错误值 #NUM!。

如果已给定概率值，则 GAMMA.INV 使用 GAMMA.DIST(x, alpha, beta, TRUE) = probability 求解数值 x。 因此，GAMMA.INV 的精度取决于 GAMMA.DIST 的精度 GAMMA.INV 使用迭代搜索技术。 如果搜索在 64 次迭代之后没有收敛，则函数返回错误值 #N/A。

案例

GAMMALN 函数

说明

返回伽玛函数的自然对数，Γ(x)。

用法

GAMMALN(x)

GAMMALN 函数用法具有下列参数：

X必需。 要计算其 GAMMALN 的数值。

备注

如果 x 为非数值型，则 GAMMALN 返回 错误值 #VALUE!。如果 x ≤ 0，则 GAMMALN 返回 错误值 #NUM!。数字 e 的 GAMMALN(i) 次幂的返回值与 (i - 1)! 的结果相同，其中 i 为整数。GAMMALN 的公式为：

其中：

案例

GAMMALN.PRECISE 函数

说明

返回伽玛函数的自然对数，Γ(x)。

用法

GAMMALN.PRECISE(x)

GAMMALN.PRECISE 函数用法具有下列参数：

X必需。 要计算其 GAMMALN.PRECISE 的数值。

备注

如果 x 为非数值型，则 GAMMALN.PRECISE 返回 错误值 #VALUE!。如果 x ≤ 0，则 GAMMALN.PRECISE 返回 错误值 #NUM!。数字 e 的 GAMMALN.PRECISE(i) 次幂返回与 (i-1)! 相同的结果，其中 i 为整数。GAMMALN.PRECISE 计算公式如下：

GAMMALN.PRECISE=LN(Γ(x))

其中：

案例

GAUSS 函数

说明

计算标准正态总体的成员处于平均值与平均值的 z 倍标准偏差之间的概率。

用法

GAUSS(z)

GAUSS 函数用法具有下列参数：

z 必需。返回一个数字。

备注

如果 z 不是有效数字，GAUSS 返回 错误值 #NUM!。如果 z 不是有效数据类型，GAUSS 返回 错误值 #VALUE!。因为 NORM.S.DIST(0,True) 总是返回 0.5，所以 GAUSS (z) 将总是等于 NORM.S.DIST(z,True) - 0.5。

案例

GEOMEAN 函数

说明

返回一组正数数据或正数数据区域的几何平均值。 例如，可以使用 GEOMEAN 计算可变复利的平均增长率。

用法

GEOMEAN(number1, [number2], ...)

GEOMEAN 函数用法具有下列参数：

number1, number2, ...Number1 是必需的，后续数字是可选的。 用于计算平均值的 1 到 255 个参数。 也可以用单一数组或对某个数组的引用来代替用逗号分隔的参数。

备注

参数可以是数字或者是包�...

“POWER(b/a，1/n)-1，还记得这是计算什么吗”——ACCA

helloaccaer上午好啊

每天一见的我又来了

有没有很想我

嗯~最近呢

不少学ACCA的伙伴们说道

F5-9阶段将全部采用机考形式

全面机考生效时间：2018年3月

考试形式：仅机考，F5-9将不再提供笔试，机考跟现在的笔考一样，只在3月/6月/9月/12月有考试，跟现在的F1-F4可以随时机考不同哦；考试时间：从2017年开始机考变为：3小时20分钟；每个学生碰到的题可能都不一样，因为机考是从题库中抽题。

所以对于ACCA学员来说更好的掌握Excel的技巧

学会利用Word,Excel等办公软件

来完成一些写作、案例分析、计算、沟通等题目的要求显得十分关键

嗯嗯~

对于Excel的使用

一直以来都是

部分ACCA伙伴们的心头大恼

毕竟手机用得多了

公式也忘得差不多了

（实践性很关键）

往下看，往下看

简单测试一下咯

POWER(b/a，1/n)-1

Number*（1+r%）^n

Number/（1+r%）^n

PV（rate,nper,pmt,fv,type）

NPV(rate,number1,number2…)

AVERAGE(number1,number2,…)

DON'TWOEER

~因为~

今天想要为大家分享的

就是ACCA机考中

一些常用的公式

虽简单但很实用

求和

=SUM（number1,number2,…）

减项

=number1-number2

乘法

=number1*number2

除法

=number1/number2

分为两类

①直接乘除

②带入数据乘除

平均数

=AVERAGE(number1,number2,…)

平均增长率

有两种方法

方法1

=POWER(b/a，1/n)-1

方法2

=(b/a）^（1/n)-1

复利求终值(通胀，增长等）

=Number*（1+r%）^n

折现

=Number/（1+r%）^n

NPV

=NPV(rate,number1,number2…)

年金折现

=PV（rate,nper,pmt,fv,type）

其中：

参数rate为各期利率；

参数nper为投资期（或付款期）数；

参数pmt为各期支付的金额。省略pmt参数就不能省略fv参数；

fv参数为未来值，省略fv参数即假设其值为0，也就是一笔贷款的未来值为零，此时不能省略pmt参数；

type参数值为1或0，用以指定付款时间是在期初还是在期末，如果省略type则假设值为0，即默认付款时间在期末。

有以下分类

普通复利年金

先付年金

上面的你记得多少呢

另外还有几个常用操作

想为大家分享一下

1设置单元格格式

2设置数字格式

3从working中选数值

~其实~

这些公式都简单

关键在于

熟能生巧

所以练习练习练习才是关键的

祝愿大家逢考必过

专心准备6月考试

刚接触Excel的时候，我对函数公式存在疑惑，要不要先学习英文、数学等知识，结果有学霸告诉我不用，函数可以记下来，学会了函数的用法就可以编辑公式了，其它方面的知识不需要。当时我信以为真了，但是现在我发现我错了，学Excel不需要学数学真的真的是一个伪命题。

某天

需要有公式对A列数据统一除以100，学霸在B2单元格编辑的公式是=A2%。

我：公式应该是=A2/100吧。

学霸：对一个数加一个百分比(%)是不是就是除以100了？

我：哦，明白了。

双某天

需要用公式对A列数据统一除以10000，学霸在B2单元格编辑的公式是=A2%%。

我：你这个公式是错误的，公式应该是=A2/10000。

学霸：对一个数加一个百分比(%)是除以100，再加一个又除以100，那不就是除以10000了？

我：……

叒某天

需要用公式对A列数据统一乘以10000，学霸在B2单元格编辑的公式是=A2/1%%。

我：你这个公式是错误的，公式应该是=A2*10000。

学霸：对一个数除以万分之一，那不就是乘以10000了？

我掩面而泣，不带这么玩的。

需要用公式在A列生成1，2，3，1，2，3……重复序列

我：这个好难。

学霸：这个好像用小学的知识结合函数就能解决。

我：你不要搞笑。

学霸：从第1行开始，返回A列每个单元格的行号，是不是一个从1开始序列？我们反复返回1，2，3，是不是对行号用3求余，返回的结果为1，2，0，1，2，0……。这样的话公式你会不会编辑了？

我：恩，差不多，行号用ROW来返回，求余用MOD，当MOD公式的值为0时，我们让它返回3，用IF来判断，公式为=IF(MOD(ROW,3)=0,3, MOD(ROW,3))

学霸：这是不是用的小学数学知识和函数来解决的？其实你还可以编辑公式=MOD(ROW-1,3)+1，这也是小学数学知识哦。

我感觉我的数学可能没学好。

我：刚看到一个生成工资条的公式，每个函数都懂，但是组合到一起就不知道为啥这么编辑了。

学霸：这不和上次教你生成序列的方法类似吗？

我：这怎么能类似？完全是不一样的。

学霸：行号为1、4、7……时，返回第一行的标题；行号为3、6、9……3的倍数时，返回空值；行号为2、5、8……时，返回2、3、4……的序列，它们的规律是不是行号加4再除以3？很简单的数学题呀，只是找出它们的对应规律，你不会这都不会吧？

我感觉我的数学肯定没学好，要不然为什么我想不到？

我：知道等级1和等级10，要求等级1到等级10等比增长，这个公式怎么编辑？

学霸：等级1是2500，假定增长率为X，等级2是不是等于2500*(1+X)，等级3是不是等于2500*(1+X)* (1+X)，等级4……

我：停，我不是来听数学的，你告诉你公式怎么编辑？

学霸：B2=($B$10/$B$1)^(1/9)*B1，双击向下填充。

我：为什么这么编辑？

学霸：咱们继续刚才的讲解，等级4等于……

我：难道不能讲数学么？

学霸：不讲数学你不知道公式为什么这么编辑呀。

我终于知道了，我Excel学不好，居然还有数学没学好的原因，所以我决定先去学数学了。你们不要找我了，我先去学数学去了，真的！

往期文章

Hello，伙伴们

导读

excel数据透视表被称为拖拖拉拉司机

以为只要拖动一下，拉一下，就将报表呈现了出来

但是刚接触的亲们

很多都觉得局限性太差

没有函数公式那么简单

今天介绍一篇如何让EXCEL中的透视表，想函数一样，计算增长率

案例：首先我们有一份数据，要求统计每个部门每月的增长率

透视表：大众方法

这个结果，就是透视的结果，只要鼠标一拖一拉，就完成了， 不做解释问题是，如何在透视表上，显示2月到3月的增长率呢

效果：

制作方法：第一步

首先我们用鼠标点击透视表的列字段区域因为我们要增加的，增长率是显示在列字段那里的，不能点击数据区域，否则无法实现而后在上方功能区，点击分析，点击字段、项目和集，而后在弹出的选项中，点击计算项点击计算项后，在弹出的对话框，名称输入增长率，这个就是显示在3月份后面的类字段在公式栏，输入=（3月-2月）/2月 这个就是自己算增长率的公式，注意是双击列标即可，不是手工输入而后点击添加，确定，关闭对话框，我们就可以看到，透视表上，就新增了一列，出现了增长率动画操作如下：

操作步骤2，设置格式

直接添加出现的，不是百分比格式，我们只需要选中，而后设置单元格格式为百分比即可操作步骤：

喜欢的亲们，欢迎关注我们，一起学习更多教程

1、文本与百分比连接公式

2、账龄分析公式

3、屏蔽错误值公式

4、完成率公式

5、同比增长率公式

6、金额大小写公式

7、多条件判断公式

8、单条件查找公式

9、双向查找公式

10、多条件查找公式

11、单条件求和公式

12、多条件求和公式

13、隔列求和公式

14、多表相同位置求和

如果你是会计并掌握了上面的函数公式，在数据处理中常见的excel问题基本上都可以解决了，当然要解决复杂的问题，还是需要掌握另外20%的高难度公式。

把会计常用的Excel公式进行一次大整理，大约15个，希望对做会计工作的同学们有用。

1、文本与百分比连接公式

如果直接连接，百分比会以数字显示，需要用Text函数格式化后再连接

="本月利润完成率为"&TEXT(C2/B2,"0%")

2、账龄分析公式

用lookup函数可以划分账龄区间

=LOOKUP(D2,G$2:H$6)

如果不用辅助区域，可以用常量数组

=LOOKUP(D2,{0,"小于30天";31,"1~3个月";91,"3~6个月";181,"6-1年";361,"大于1年"})

3、屏蔽错误值公式

把公式产生的错误值显示为空

公式：C2

=IFERROR(A2/B2,"")

说明：如果是错误值则显示为空，否则正常显示。

4、完成率公式

如下图所示，要求根据B的实际和C列的预算数，计算完成率。

公示：E2

=IF(C3<0,2-B3/C3,B3/C3)

5、同比增长率公式

如下图所示，B列是本年累计，C列是去年同期累计，要求计算同比增长率。

公示：E2

=(B2-C2)/IF(C2>0,C2,-C2)

6、金额大小写公式

=TEXT(LEFT(RMB(A2),LEN(RMB(A2))-3),"[>0][dbnum2]G/通用格式元;[<0]负[dbnum2]G/通用格式元;;")&TEXT(RIGHT(RMB(A2),2),"[dbnum2]0角0分;;整")

7、多条件判断公式

公式：C2

=IF(AND(A2<500,B2="未到期"),"补款","")

说明：两个条件同时成立用AND,任一个成立用OR函数。

8、单条件查找公式

公式1：C11

=VLOOKUP(B11,B3:F7,4,FALSE)

9、双向查找公式

公式：

=INDEX(C3:H7,MATCH(B10,B3:B7,0),MATCH(C10,C2:H2,0))

说明：利用MATCH函数查找位置，用INDEX函数取值

10、多条件查找公式

公式：C35

=Lookup(1,0/((B25:B30=C33)*(C25:C30=C34)),D25:D30)

11、单条件求和公式

公式：F2

=SUMIF(A:A,E2,C:C)

12、多条件求和公式

=Sumifs(c2:c7,a2:a7,a11,b2:b7,b11)

13、隔列求和公式

公式H3:

=SUMIF($A$2:$G$2,H$2,A3:G3)

如果没有标题，那只能用稍复杂的公式了。

=SUMPRODUCT((MOD(COLUMN(B3:G3),2)=0)*B3:G3)

14、两条查找相同公式

公式:B2

=COUNTIF(Sheet15!A:A,A2)

说明：如果返回值大于0说明在另一个表中存在，0则不存在。

15、两表数据多条件核对

如下图所示，要求核对两表中同一产品同一型号的数量差异，显示在D列。

公式：D10

=SUMPRODUCT(($A$2:$A$6=A10)*($B$2:$B$6=B10)*$C$2:$C$6)-C10

复合增长率又称为年均复合增长率，英文为 Compound Annual Growth Rate，通常只用简写 CAGR 代替；它是用来描述一个投资在一定时期内的年度增长率。与算术平均增长率相比，复合增长率是几何平均增长率，也就是平常所说的利滚利，即本年的利息也计入下一年的本金获利。在 Excel 中，计算复合增长率分为百分比和数字两种形式，它们都可用同一个公式计算，下就是它们用 Excel 计算的具体操作方法，操作中所用版本均为 Excel 2016。

一、Excel计算百分比形式的年均复合增长率

1、添加辅助列，并在该列给每个百分比增长率加 1。双击 C2 单元格，输入公式 =b2+1，按回车，返回 113%；选中 C2，把鼠标移到 C2 右下角的单元格填充柄上，鼠标变为加号后双击左键，则 B 列剩余的增长率全部都加 1。

2、计算年均复合增长率。双击 C8 单元格，把公式 =(PRODUCT(C2:C7))^(1/(YEAR(A7)-YEAR(A2)))-1 复制到 C8，按回车，返回 0.056567391；确保当前选项卡为“开始”，单击“百分号（%）”（或按快捷键 Ctrl + Shift + %），则小数转为百分数且不保留小数和自动四舍五入；操作过程步骤，如图1所示：

提示：如果要保留一位小数，选中 C8 后，按 Ctrl + 1（需关闭汉语输入法），打开“设置单元格格式”窗口， 选择“数字”选项卡，再选择左边的“百分比”，在右边“小数位数”后输入 1，单击“确定”，则 6% 变为 5.7%，如图2所示：

3、增长率为什么要加 1？因为本年价值 = 上年价值 × （1 + 增长率），而复合增长率 =（现有价值/基础价值)^(1/年数) - 1，即求复合增长率需要把增长率转为“现有价值/基础价值”形式，则（本年价值/上年价值）= 1 + 增长率。

4、公式 =(PRODUCT(C2:C7))^(1/(YEAR(A7)-YEAR(A2)))-1 说明：

A、PRODUCT(C2:C7) 是把 C2 至 C7 的每个数值相乘，结果为 1.316697512。

B、YEAR(A7) 是取 A7 中日期的年份（即返回 2018），因为 A2 到 A7 的单元格格式为日期型；如果单元格格式为数值型或文本型不需用 Year 函数取年份；YEAR(A7)-YEAR(A2) 用于计算求复合增长率的年数，结果为 5。

C、则公式变为 (1.316697512)^(1/5)-1，接着对小数求指数为 1/5 的指数运算，然后再把计算结果减 1，最后返回 0.056567391。

提示：如果“年份”连续的，还可以用 Count(A2:A7) -1 来统计“年数”，则公式变为 =(PRODUCT(C2:C7))^(1/(Count(A2:A7)-1))-1。

二、Excel计算数字形式的年均复合增长率

1、假如有一个 2013 年到 2018 年的投资表，“资产”列出的是每年的总资产，现在要求年均复合增长率。双击 B8 单元格，把公式 =(B7/B2)^(1/(YEAR(A7)-YEAR(A2)))-1 复制到 B8，按回车，返回 0.108318466，按快捷键 Ctrl + Shift + % 把小数转为百分数；操作过程步骤，如图3所示：

2、公式说明：

A、公式 =(B7/B2)^(1/(YEAR(A7)-YEAR(A2)))-1 由求复合增长率公式代入数值变化而来，B7 为现在价值，B2 为基础价值，也就是用最后一年的价值比第一年的价值；公式中的其它部分与上例中的一样。

B、另外，公式还可以用指数计算函数 Power 计算，即 =POWER(B7/B2,1/(YEAR(A7)-YEAR(A2)))-1。

提示：一年增长下一年亏损且数值大于 0 也可以用上面的方法计算，例如：第一年资产为 12000，第二年为 16000，第三年为 8000，用这个公式 =(8000/12000)^(1/2)-1 计算能获得正确的结果（返回负增长），演示如图4所示：

另外，如果次产中有负数不能简单的用“现有价值/基础价值)^(1/年数) - 1”公式求复合增长率，这样会获得错误的结果。

打开excel表格，点击菜单栏中的“公式”命令，在左上角会出现“插入函数”按钮并单击此按钮会出现如图所示。

在选择类别中，选择“数学与三角函数”。

然后在下拉列表中找到POWER函数，点击确定。

出现如图所示，那么下一步就可以计算年均增长率了，以某例说明：1996年GDP 5647.5亿元，2009年GDP 50611亿元，求年均增长率。

在数值这一栏中填写的是：09年的gdp量除以96年的gdp量(50611/5647.5);在幂这一栏中填写年份（2009-1996=13)。计算结果（1.183752）再减去1就是年均增长率（18.37%）。

（本文内容由百度知道网友逆长小白菜贡献）