从index small if row组合，谈如何避免复杂的Excel公式

最近在一个Excel交流群中，碰这么一幕：

一位函数高手对没有理解Vlookup函数用法的提问者，很自然地流露出不解，这么简单的函数。随后抛出了index + small + if +row的数组万能匹配公式，表示自从学会这个组合以后，看到Vlookup就想吐。还为他写的公式连他的财务总监都看不懂而感到骄傲。

其实，更多时候公式写着写着就复杂了，因为精炼。不过一直以来，我都在尽量克制和避免，也建议团队成员尽量避免。

因为，当Excel公式被越用越复杂以后，对新团队成员的技能要求也越来越高，招聘和新人的培养变得越来越难。

复杂的公式对个人能力而言是有好处的，但对于团队的稳定性和替代性，是个不小的挑战。你能想象到当一个Excel不那么好的新人，被交接一份如同天书的报表模板时的无助。

---

当然为了避免复杂公式也要付出一些代价，例如更多的辅助计算过程，更多的辅助计算表等。那么该如何平衡？

我的标准很简单，只有一条，那就是周围的同事能轻松读懂。

在普通的工作中，被理解是非常重要的事情。可以想象，如果做了一个巨复杂的分析模型，结果大家都不理解，或者要费很大的力气理解。虽然很有可能是对的，但结果往往不是很好。

---

那么从流程和技术上如何避免复杂公式的出现呢？

列出几条原则:

一、把脏数据消灭在源头

这有两层意思：

第一层，不让脏数据产生，可以参考这篇《不做规范！收数就是个灾难！Excel收集数据套路了解一下》；
第二层，如果原始数据不规范不可避免，则必须先做清洗。坚决不用要数据进行下一步的数据处理和分析。自从Excel 2016引入Power Query 后，这件事情变得更加简单。

二、构造合适的表结构

这一步是借鉴数据库的理念，有数据库基础的Excel使用者，这个很好理解。如果没有就记得这几点：

一、不要有合并单元格
二、一列为一个字段，表示同一含义的内容，且有明确的数据类型
三、一行为一条记录，表示描述一条完整的信息，例如什么时间在哪里发生了什么事情

字段、记录、关键字段、数据类型等这些非常重要。如果想要进阶BI，不管是微软的Power BI，还是其他BI。没有这个基础，很难用好。

三、不是非常必要，不用一步到位

拆解计算过程，适当使用辅助表和辅助列，来简化计算过程。直接从一楼跳到二楼基本不可能，变成一级一级的台阶大家都能做到。

---

既然提到了index + small + if +row这个组合，我们就来讲讲，毕竟是个一对多匹配的函数经典组合。

这个组合可以先拆分为两部分，

第一部分是index，任务是取指定序列的第几个；
第二部分是small+if+row，任务是先记录上面序列的行号，然后把不符合条件的行号变成一个非常大的数，最后用small函数按要求去取最小的，第二小的，第三小的以此类推。

这是一个数组公式，在公式输入结束后，必须同时按下Ctrl+Shift+Enter三键才能有效。文字难懂，看看这段操作吧。