战胜Excel面试题靠的是——思路！

原创：打不死的面试小强 MarTechCareer

上周，我接到了一家电商公司的面试邀请，面试第一个环节就是做一个Excel Challenge，我信心满满觉得肯定没有问题，但谁知打开题目就傻眼了，思路全无。这个Challenge的题目很短，就只给了一个表，在表中给出了上个月的几个重要指标：包括订单数、新增用户数、以及老用户的复购率，题目就是让我用Excel来预测一下下一个月这些指标的表现。天啊！我本以为熟练掌握Excel里的Sumif，pivot和vlookup，就可以顺利通过测试，没想到竟被这道题难住。

在商业分析中，用Excel来分析数据是一名分析师必备的基本技能。因此，大多公司在面试分析师岗位的求职者时，都会给出Excel案例分析题：给你几个Data Table，让你通过分析解决一个商业问题。面试官所考察的不仅是你对函数操作的了解，更重要的是整个分析的思路是否清晰明确。

今天，想主要通过一个比较简单但很实际的例子，来让同学们了解Excel面试题的攻克思路。不管你是将要去念商业分析或营销分析专业的学生、或是正在积极准备各种求职面试的毕业生、抑或是正在积极跳槽的工作党，希望这篇文章都能为你“如何应对Excel Case面试题”提供一个有用的思路。

//////////

在商业分析中，我们经常要对各种关键绩效指标（KPI）进行预测，比如说对销量进行预测就非常有用，因为这对之后的库存管理、预算计划、投资关系等等重要商业决策都会有直接的影响。

但是如何生成准确的预测往往令人感到困惑。下面这个例子就解释了如何利用Microsoft Excel来一步步生成预测结果。很多求职者反应，他们在做excel测试的时候，往往最煎熬的不是忘记或不会用某个公式，而是缺乏有效的分析思路，结果就是事倍功半。因此，大家在阅读本文的时候，请减少对“函数或方法”的关注，增加对“思路”的关注。

一、收集数据

首先，我们需要明确目标，也就是分析的目的是什么？然后，我们才可以去收集相关的数据并跑出一个预测模型。

假如要预测来年的销量，一开始，我们需要建立一个时间线。你是打算预测接下来12个月？5年？还是未来30天的销量呢？对于一个12个月的预测分析，最好是有至少过去三年的历史数据来确定出季节性趋势。如果你只有几个月的数据，可以使用它们来估计接下来30天左右的情况。

接下来，就是根据预测指标的类型来收集数据。

通常可以有下面几种分类：

整体销售

可以是按年、月或日统计的总销售量或金额

每种产品的销售

可以是按产品名称或类型统计

地区销售

按所需地区（例如国家、州）划分统计

营销渠道销售

按不同的营销渠道，比如Facebook，自然搜索，Google广告等划分统计

只要你拥有足够的历史数据，就可以预测有关销量或是其他业绩的各种关键指标。

二、方法论

一旦你以表格形式获得数据后，就可以使用Excel中的各种函数（或统计模型）来进行分析预测，达到令你满意的最终结果。我们以TREND函数为例，利用Excel一步一步从简到繁的具体分析过程：

TREND函数的公式是

= TREND(Historical Sales, Historical Timeline, Forecast Timeline)

在下图给出的一个简化的Excel截图中，具体的公式就是：

=TREND(B3:B9, A3:A9, A10:A14)

请注意，Excel中的TREND公式是线性的，但对于大多数电子商务、快消或其他类型的公司而言，在一整年中的销售额并不完全呈线性一致，有些月份会因为季节性或是促销等原因而有销量的明显高低。比如说在下面的例子中，如果发现10月，11月和12月的销售占年度总销售的大头，那么接下来就要再把季节性的因素考虑进去。将季节性效应建立进预测模型中就要比刚刚再复杂一些。

第一步：计算出每个月的平均历史销售

第二步：算出每个月的季节性调整

第三步：将季节性调整乘以每月平均销售以获得预测

例如，假设我们有过去三年的年度销售数据。我们先计算每年的每月平均销售（下图以2018年为例，该年的平均每月销售是$6638），然后为每个月分配季节性调整比率（也就是每个月的实际销售除以月平均销售）。以下是2018年的截图：

（2018年平均每月销售是$6638，10月、11月和12月的销售分别超过了平均值的98%，105%和301%，注意：表格中的百分比是只显示了前两位的估计值，比如1月的季节性调整实际为75.3239%）

接下来，我们用TREND函数来预测2019年平均每月销售，在这个例子中是$6924。

然后我们再将每个月的季节性调整比率应用到平均值中来得出2019年每月的预测值（比如下图中，2019年1月的销售额预测等于2019年平均每月预测量$6924乘以1月的季节性调整比率75%）

类似的，你还可以将TREND函数和季节性调整技巧应用到其他针对不同产品、营销渠道、地区地域等等其他情况中。

三、加入其他假设

有时，对预测添加一些假设对提高结果的预测是有帮助也很有必要的，主要是针对一些相比前面没有太多规律性的情况，比如新产品的发布或是促销也会让销售增加。

下面是一个例子，比如某新产品将在4月分推出。你预计这将会对当月销售贡献$50，并且将保持每月5%的增幅持续增加。另外，因为某些历史促销活动曾使当月销售增加了20%，因此计划在今年的2月和8月对新产品也使用这些促销活动。

现在在我们的Excel表格中就包含了新的几行，是关于对刚才所说的各种假设的罗列，来为2019年的销售做出新的预测。

（添加假设能够使预测更加精准。在这个例子中，预测包含了新产品推出以及2月和8月的市场促销期望带来的销售影响）

四、从简单到复杂

上面的例子只是一些基本预测的简化方法。更加复杂的模型可能包括其他各种额外的假设，季节性以外的变量以及多种产品类型。比如，一些高级模型还可能考虑到库存水平以及竞争对手的举措。也有面试题会要求你不能使用Trend函数来进行预测，那样的话，你就需要调用Excel里的回归模型来建模完成预测。

这些相同的预测方法同样可以用来预测其他指标，比如估计每次广告点击费用、库存水平、订单数量、广告开支、新用户人数等等等等。

在Excel面试中，数据基本都是已经放好在表格里了。那么你需要做的就是根据题目要求快速理出你最需要的几行数据。有些善良的面试题会在一开始先让你算一些基本的数据，一步一步引导你形成一个完整的分析，你需要思考的是每一步操作和计算对于得出最终结论的意义；但更多时候的题目是很直接粗暴的，在没有任何提示的情况下让你通过分析一份数据给出结论和建议，前文也说过，面试官考察的就是你对Excel的熟悉度和分析思路，在分析的过程中不断得出新的洞察，为制定未来的策略提供支持，因此就需要你对营销分析中Excel的各种常规操作和通常分析的问题很了解才行。而当你越来越熟悉Excel以后，就能更加感受到Microsoft这项表格类工具的无穷魅力。

现下，MarTechApe又一次推出《数据分析工具箱——Excel特训课》，教给你工作中最实用的Excel功能与商业、营销分析Case Interview题型实战。在这门拥有五个模块的课程中，前四个模块介绍了Excel在工作中几乎所有最实用的、面试最常考的功能、formula。第五个模块则会从头到尾详细解答一个真实的Excel case interview题目，并教会你Excel case interview的套路，让你把学到的能力迁移到其他的面试上。

Excel特训课