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)))&""
以上就是整个分析过程,希望你能认真的读完,然后对于这种题,也能一步步的思考,这样你的函数一定可以得到质的飞升。