大家好，我是爱聊Excel的小胖子廖晨，今天要聊的是在一次制作自动增减内容的工资条，你会做吗？一文中使用过的函数，查找定位函数中的最佳搭档match和index,还有个不成文的说法与这两个函数有关：查询用的好，5大函数离不了，分别为index,match,lookup,hlookup,vlookup。今天我们只说index,match函数的用法。只用前面的两个函数是无法实现，需借助INDIRCET函数来拼接生成查找下一个员工姓名的引用范围，开始位置为上一员工相对位置1，结束位置不变。
大家好，我是爱聊Excel的小胖子廖晨，今天要聊的是在一次制作自动增减内容的工资条，你会做吗？这有实例！一文中使用过的函数，查找定位函数中的最佳搭档match和index,还有个不成文的说法与这两个函数有关：查询用的好，5大函数离不了，分别为index,match,lookup,hlookup,vlookup。今天我们只说index,match函数的用法。
函数之术术在道德经中的解释具体的操作方法，为下乘
函数中的“术”其实就是了解函数功能，具体的使用方法？
MATCH功能：查找值在引用区域中的相对引用位置；
语法结构：MTACH(值,引用范围,[查找模式])
值：查找的值，支持通配符“*?~”（必填）
引用范围：可谓引用的范围或数组，引用范围只能包含行或列，否则返回#N/A;(必填)
查找模式：设定为3个值：1，0，-1；具体信息如下
提示：当为1时，若数据非升序，结果不可预测（慎用）。
MATCH函数示意图（图1）
我最常用的就是精准查找，因为模糊查找有条件约束，所以不太常用，下面我们就详细了解一下精准查找的用法：
面试题：有一张业绩表包含部门，姓名，销售业绩，行数（辅助列）引用范围为A1:D14,用match函数查询姓名在B2:B14的位置(是不是简单令人发指，不过笔试题越简单，坑就比较多，考察的内容比你想象的要多的多)。
解：公式比较简单，若查找“张冶”所在的位置，只需在结果单元格F1录入=match(“张冶”,B2:B14,0),回车；
如果这是一道面试的题的话，这么写答案只怕就是个及格分，因为我们制作某项功能的时候，需要考虑易维护和管理，在这个问题上，其实就是增加一个辅助单元格F2为姓名录入入口，然后将G2的公式变为=match(E1,B2:B14,0);
如果你做了上面的内容认为就能拿满分的话，你就太天真了，最多80分，除非你把另外的两个参数也做成可变的，比如查找模式引用的辅助单元格做成可选列表且加条件约束只能为-1，0，1；难点如何动态生成查找用的引用范围，有兴趣的你可以玩玩！不过在工作中，看留给你的时间是否充裕，没有时间做最简就好！
创建查询模式编码列表示意图（图2）
INDEX功能：通过设定引用范围的行列号，读取引用范围或数组中相应位置的值
语法结构：INDEX(引用范围,行号,[列号],[区域编号])
引用范围：即支持单区域引用，也支持多区域引用，当遇到多区域引用的，区域编号则会起到作用
行号：准确的说行号不太准确，因为当引用范围只在行或列内时，它表示引用范围的相对位置，自动识别为行号或列号；
列号：当引用范围同时包含行或列的单元格，才需填写列号；
区域编号：当引用范围非连续多个区域时，可以使用区域编号来指定读取某区域的单元格，从1开始，如果设定小于1的值，则返回#VALUE;
接下来我们将通过例子来熟悉一下INDEX函数的用法：
例：读取B2:B14引用范围中的第5行的值是什么？结果单元格录入公式=index(b2:b14,5)；
如果将引用范围换成A1:G1,读取第5列的内容公式依然=index(A1:G1,5),
面试题：在工作表中B2:G13中，第1行每一个单元格的数字全为1，第2行每个单元格的数字全为2，依次累加，在h5单元格的公式=index(B2:G13,2,0)，则H6的公式为=sum(index(B2:G13,2,0)),两个最终的分别是什么？
解：第1个结果为#VALUE,第2个结果为12，因为B到G为6个数，恰巧第2行数字全是2，即6*2=12;
知识点：当引用范围即包含行和列的单元格时，行号或列号为0时，则表示所在位置的引用范围行或列的内容，不过在单元格单独输入公式则返回#VALUE，而用sum包裹则等效=sum(B3:G3)；
面试题公式示意图（图3）
函数之法法在道德经中解释为一套体系的原理和规则，中乘
通过上面的介绍，我们不难了解到，MATCH函数可以查询某些值的在引用范围中的相对位置,而INDEX函数则可以通过输入位置编号就能读取相应位置的值，哪么问题来了，如何才能查找上面“业绩表”中的同一部门所有的员工呢？
思路：其实查询某个部门所有的销售人员，只需查出这一部门在引用范围的所有的相对位置即可，再用INDEX依次读取销售人员的姓名就好，那么问题来了，该如何读取一个部门的所有位置信息？而MATCH精准查找只能返回第一次的位置，哪我们依次从第一次出现的位置 1开始查，依次循环是不是就能找到所有的位置了呢？只用前面的两个函数是无法实现，需借助INDIRCET函数来拼接生成查找下一个员工姓名的引用范围，开始位置为上一员工相对位置 1，结束位置不变。
操作:
1.F1:H1分别输入“查询部门“、”员工姓名“、”位置“，在F2录入查询的部门的名称”部门1“，在H2输入=MATCH(F2,A:A,0),回车；
2.第1个元格的相对位置 1即H2 1,所下一个位置的引用范围字符串=”A”&H2 1&”:A100”,转化成引用范围=INDIRECT("A"&H2 1&":A100”),H3的公式=MATCH($F$2,INDIRECT("A"&H2 1&":A100”),0)，再加上第1个员工的相对位置即公式为=MATCH($F$2,INDIRECT("A"&H2 1&":A100"),0) H2；最后容错处理，最终为=IFERROR(MATCH($F$2,INDIRECT("A"&H2 1&":A30"),0) H2,"")
3.快速批量扩填H3的公式方法有拖拽法和快捷键法：
4.然后在G2输入=index(B:B,h2),容错后公式为=IFERROR(INDEX(B:B,H2),"")鼠标移至G2的右下角变为＋，双击鼠标左键，就能完成公式填充，并于H3引用范围相对齐（有时间会专门写一篇总结技巧的优缺点和使用场景）最后选中H列，CTRL 0隐藏辅助列
案例示意图（图4）
问：处理这类问题，数据大时，查询汇总填充的公式多了占用空间，少了会频繁二次增添公式，填充公式量该怎么掌握呢？
答：2个类型数据，查询的填充公式要做到整体数据量的75%；
3个类型，查询公式的量为50%；
4个类型，公式的填充量为40%；并非绝对,纯属个人经验总结。
其实函数“法“的阶段就是通过已知的条件中，发现解决问题的规律，然后再去选择合适的函数或函数组合；选择合适函数必须先过函数“术”的阶段，大概花一个月的时间来熟悉函数的分类，常用的函数60-70个足以和常用的功能技巧，而找到解决问题的规律，就需要长时间的积累，如果开始没有思路可以找一些做好的项目，开始可以抄，抄的时候，需要注意3点，
虽然简单却很关键，不可略去；抄过4-5个后，可以尝试自己做，不要贪快，因为学好的捷径就是用正确方法上花费时间，至于时间的多少就看你的悟性和学习能力。
聊完函数之“法"后就是函数第三阶段“道”，但“道“却不是1，2个函数就能说明白的，不过可以先解释一下什么是“道”？
“道“在道德经中解释为万物变迁循环中的亘古规则，上乘；而函数的“道“，就是自己设计，编写自己需要的功能,所使用的模块编程的设计思想，要点有12个字：约定优于配置，配置高于逻辑；字不多，学成难，因为需要你大量的知识积累，不光需要你编程语言的知识，也需要算法，设计思维和计算机知识的加持，才能成道。
好了，今天的文章就到这了，文章的最后给大家准备了一个思考题，问：部门员工表中，能用INDEX和MATCH函数实现查询销售4的王城银所在的位置？（注：不用辅助列，不用其他函数，一个公式搞定），欲知公式如何写，下文分解，最后一句良言：看遍千文，不如实践一篇，希望你通过实现有所收获，欢迎你来审查文中纰漏，留言给我，我会立马改正。不过不要一看有些难度就放弃，你只不过是学前的我，我只是学后你而已！喜欢我就关注吧，我是一个爱聊Excel小胖子，廖晨！
