【学习】excel函数嵌套

1. 前言：

相信很多学习EXCEL的同伴都会时常将一句话挂在嘴边：

“请老师教我下这个公式怎么写？”

要么就是：

“老师太牛了，这么厉害的嵌套您是怎么写出来的，能不能教教我？”

说实话，我也被这样问过几次，虽说自己函数学的也不怎么样，但是对于这样的问题，我实在不知如何回答，更谈不上“教”这么神圣的动作。

……

在我看来（至少我是这么认为的），学习EXCEL不是单纯的了解函数。

了解函数只是工具，更重要的是如何分析问题，写EXCEL公式不是函数记忆大比拼，而是逻辑思维的较量。

……

可能这样说，只会让一些人觉得：“切，富人永远不知道穷人的苦~~”，

但是，请记住，所谓的大神也只是记得那些函数，面对你们形形色色的问题，他们不可能是先知，每一点的模式都学到位。

那为什么他们总能时不时的为你们解惑呢？因为他们知道了学习函数的本质是看问题，而不是看函数。

2. 来，看个问题

好了，话不多说，且看下面一道问题，让我拙略的给你分析

图片分享：

问题是：如何根据“旅游地点”，在“拟处理数据”中查找并提取相应的“名胜古迹”？

相信看到这样的问题，有一大半人会晕吧，因为微软没有为我们提供这样一个便利的函数一次到位。

其实不难，我刚有说过：写EXCEL公式不是函数记忆大比拼，而是逻辑思维的较量。

怎么思维较量的呢？估计一大批人到这里，懒得动脑子去想了~~

解铃还须系铃人，相信古人都这样教导过我们吧！要处理问题，首先得看清问题！

1. 问题分析

1、已知数据：“拟处理数据”、“旅游地点”

2、所求数据：“名胜古迹”

3、已知与所求的关系：根据“旅游地点”，在“拟处理数据”中查找并提取相应的“名胜古迹”？

4、问题中出现的动词：查找、提取

a) 查找：查找符合条件的数据

b) 提取：将原区域符合条件的提取到目标区域

为什么要这一条：因为动词才是需要的函数的关键，根据动词我们可以迅速的将函数范围缩小。

例如：问题中有“求和”两字，我们自然的将范围缩小至SUM类函数。

注：有些问题不是这么系统的表述，需要我们将<1、已知数据>和<2、所求数据>之间的关系，也就是<3、已知与所求的关系>整理出来，再根据<3、已知与所求的关系>，得到第4条动词的提取。

当然只有问题提出者自己更明白自己的问题是什么，因此原则上是自己更能很好的提取<3、已知与所求的关系>

2. 函数选择：

根据分析中提取的动词：查找、提取

我不用多说：

文本查找函数：自己可以随便翻翻函数字典，或是按下F1，或是用我们伟大的度娘来搜索。

文本查找函数 这里首先想到的是FIND、SEARCH，不管这两个函数写出来的精简不精简，起码能达到你要的效果。

提取函数：这个提取，就是将原区域符合条件的提取到目标区域，也就是引用函数。

这个我不重复了，希望学会F1和度娘。引用函数主要3巨头：INDEX、OFFSET、INDIRECT。这3个都可以，你随便挑。

3. 逻辑整合：

所谓逻辑整合，其实就是将已经分析的核心函数，按题意<3、已知与所求的关系>整合起来。

根据题意，理所当然的是先查找，再提取

因此函数可写为：INDEX(“拟处理数据”,FIND(“旅游地点”， “拟处理数据”)）的形式。

这一步就是所谓的编程语言，其实和说话没两样。

_________华丽丽的开始逻辑整合______________

接下来，如果你以前从没有接触过这两个函数，请你边度娘边写公式，不需要提前对这函数了如指掌的。

INDEX函数：

因为其第一参数已经是“拟处理数据”区域了，所以直接忽略第一参数。

再看第二参数：第二参数，相信度娘（百度百科）或是F1说的很明白：是行号。

我们这里，永远不要忘了自己用的函数的初衷，FIND函数的初衷是在“拟处理数据”中查找“旅游地点”有没有！！！

注意到，是有没有！！！它存在的价值只是判断有或者没有；

结果是有的，我们得返回一个行号（因为INDEX第二参数需要）

结果没有的，我们还得返回一个行号（因为INDEX第二参数需要）

这里，这两行就是怎么将八竿子打不到一块的两个函数，如何有效的粘合起来！

……

因为FIND不到，会返回错误值，要想它和INDEX粘合，就必须ISERR容错函数。

这一块就算你想不到，当你写公式，并F9查看结果的时候，你自然就对这些错误值考虑如何容错了~

因此需要套上ISERR(FIND(“旅游地点”， “拟处理数据”))

……

对于有的（FIND不出错），返回一个行号（肯定就是本身行号）

对于没有的（FIND出错），返回一个行号（只要是空的就行）

这种，有没有的我们肯定用IF来处理。

这样说下来，INDEX第二参数就升华为：

IF(ISERR(FIND(“旅游地点”， “拟处理数据”))，空行号,本身行号)

……

接下来其实就可以拼合了

因为，需要逐行去取行号，对于逐行两字的解读，我们就对返回的数字按大小一一取之，这是最简单的想法也是最可行的想法。

SMALL(IF一大串，ROW(A1))

拼合之：

INDEX(“拟处理数据”,SMALL(IF(ISERR(FIND(“旅游地点”,“拟处理数据”))，空行号,本身行号)，ROW(A1))

因为是引用函数，空单元格的引用结果是0，我们需要返回空，只需要：（这已经是后期修饰工作了）

T(INDEX(“拟处理数据”,SMALL(IF(ISERR(FIND(“旅游地点”，“拟处理数据”))，空行号,本身行号)，ROW(A1)))

或是

INDEX(“拟处理数据”,SMALL(IF(ISERR(FIND(“旅游地点”，“拟处理数据”))，空行号,本身行号)，ROW(A1))& ""

4. 去掉文字：

=INDEX(A:A,SMALL(IF(ISERR(FIND(C$2,A$2:A$17)),10000,ROW($2:$17)),ROW(A1)))&""

10000行肯定是空的，时常见到有人写一个4^8，表示的就是4的8次方，是2003版本能承受的最大行号。

也即是说4^8这个行号，一般肯定是空单元格。

=INDEX(A:A,SMALL(IF(ISERR(FIND(C$2,A$2:A$17)),4^8,ROW($2:$17)),ROW(A1)))&""

以上就是整个分析过程，希望你能认真的读完，然后对于这种题，也能一步步的思考，这样你的函数一定可以得到质的飞升。

原文始发于微信公众号（PPV课数据科学社区）：【学习】excel函数嵌套