对于很多Excel新手和入门级用户来说，公式函数仿佛就是Excel学习晋级的一只拦路虎。很多人会用死记硬背的方式把常用IF、COUNTIFS、SUMIFS、VLOOKUP函数的语法背了下来，可在实际应用的时候，却还是不知道在什么场景下用什么函数。
说到死记硬背，我想起来在李笑来老师的《把时间当作朋友》一书中看到的一则故事：
那天下课之后，一个男孩捧着我写的那本非常畅销的《TOEFL iBT高分作文》让我给他前面。我签了。然后他说“老师，我可不可以问你一个问题？”我笑着说“你现在可以直接问第二个了。”他说：“老师，你说，如果我把你这本书里的作文全都背下来，在考场上默写一篇，会不会被判雷同呢？”我当时一下子失去了耐心，尽管没有发火，但是语气里肯定有一些东西：“那你说呢？！”那个男孩脸红了一下，迅速走了。
显然，用死记硬背的方法是学不好写作的，李笑来老师在书中说到：“写作是只有想清楚了，才有可能写清楚。想不清楚，连写出来的必要都没有。”
这句话同样适用于Excel函数的学习。在Excel线下培训讲解到函数这个模块的时候，给学员讲解函数的语法和各个参数的概念之前，我都会告诉学员：“函数没有你想象中的那么复杂，只要想明白的就能写出来。”
编写函数之前，一定要梳理出数据背后的规律，我来举例说明一下。
前段时间给某银行企业培训完Excel课程之后，学员M拿着他工作中的一个问题向我咨询。这是M的业务数据（数据为模拟）：
这是M需要计算的收益数据的计算规则，如果资产分类是“非标准化资产”，那么收益的计算公式是=B2*E2/F2，以此类推的按照底部的条件来计算收益。
Excel常用的5大函数及其语法分别是：
求和：SUM（区域）
逻辑判断：IF（逻辑判断式，真值，假值）
多条件计数：COUNTIFS(条件范围1，条件1，条件范围2，条件2，……）
多条件求和：SUMIFS(求和范围，条件范围1，条件1，条件范围2，条件2，……）
条件查询：VLOOKUP（查询值，数据源，返回第几列，0/1）
通常我们在遇到计算条件是如果……那么……，这类的规则的时候，一定会想到对应的函数是逻辑判断IF函数，现在判断条件有6个，也就意味着要嵌套5层IF函数才能完成这个条件判断。而实际上M的需求比现在我们看到的规则还更复杂，这就意味着将嵌套更多层的IF函数才能实现他的需求。
如果仔细观察这个运算规则，你会发现第一个B2都是固定的，最后一个F2也是固定的。唯有中间乘的数据在发生变化。
那我们是不是可以将中间乘的E2、D2或0.2拆分到两列中，看到上面的拆分结果，现在你可能有会1个问题：为什么E后面不写行号2？
因为行号2不是固定的，第一个地址如果我们直接引用B2，当公式往下复制的时候，B2会自动变成B3、B4、B5……。而后面的E2、D2我们需要根据左边的资产分类为条件，查询出这一列，再用间接引用函数INDIRECT引用出这个数据。
有关INDIRECT函数的用法，请参考此文：Excel中神秘的间接引用函数Indirect。此时如果把2写到E或D的后面，2就变成固定的。
现在的规则就变成了根据A列查询返回K列的数据了，想清楚了我们就知道接下来的公式应该怎样写了。
首先，用VLOOKUP函数用A2作为查询条件，返回K列的数据，需要用到查询函数VLOOKUP。
=VLOOKUP(A2,I:K,3,0)
接着让后面的2变成动态可变的，就需要用到ROW返回行号的函数。
=VLOOKUP(A2,I:K,3,0)&ROW(A2)
现在要把D2、E2变成对应的数据，就要用到间接引用函数INDIRECT。
=INDIRECT(VLOOKUP(A2,I:K,3,0)&ROW(A2))
由于有部分是没有引用结果的，所以需要用IFERROR函数来指定错误值的显示，如果出错了我们就返回0.2。
=IFERROR(INDIRECT(VLOOKUP(A2,I:K,3,0)&ROW(A2)),0.2)
最后前面添加B2，后面除以F2就得到了我们想要的结果了。
=B2*IFERROR(INDIRECT(VLOOKUP(A2,I:K,3,0)&ROW(A2)),0.2)/F2
涉及到复杂公式计算的时候，我们是很难一步到位的写出这个公式的，这样一步步把公式嵌套进去，能让公式化繁为简。
公式其实就把你思考的过程转换成Excel能识别的语言，只要想明白了你一定能写出来。
网友评论