首先,该问题有哇众取宠之嫌,在日常工作中,因为VLookup函数更加简单、易用、直接,实际上VLookup函数的应用广泛程度是Index Match函数组合的10倍。 说Index Match函数比VLOOKUP函数好用,是因为Index Match组合比单纯使用VLookup函数更加灵活,功能更加强大,而且在多列数据匹配查找时能通过合适的方法来改善计算效率。 关于这两个函数怎么使用,前面的答者给出了非常详细的描述,在此不再赘述。以下我从两个方面来进一步说明Index Match函数组合比VLookup函数好用的地方。 一、结合“表格”(超级表),跨表引用巨轻松——那些你曾经看不懂的公式,原来是这么好用!大海 Excel到PowerBI很多时候,我们在写公式时,都需要进行跨表的数据引用,比如要通过VLookup函数引用其他表的数据参与计算,或做匹配等,经常要拿着鼠标到处找需要引用的那一列或那个区域,尤其当数据表很大、列数很多的时候,找起来更是费劲,但自从你将普通的表转成了'表格'(超级表:见文章《用了Excel这么久,还有辣么多人不知道“表格”!》),你就轻松了——因为你可以直接在写公式的时候得到相应的提示! 以下将通过一个简单的例子来见证'奇迹的时刻'。 只要在成绩表的右边,标题行上输入'学生姓名',然后回车,Excel将自动生成一个新的列,如下图所示: 接下来我们开始输入公式'=index(stu……' See?student表随着公式的输入出来了! Excel就是这么牛B,直接给你提示! 此时,如果还有多个表的话,我们可以通过键盘的上下箭头进行表的选择,当选到我们需要的表时,按Tab键即选中该表进入公式。 引用表后,我们还要指定要引用的列,这时,我们在表名后面输入'['——真正见证奇迹的时候!student表中所有的列名都出来了! 此时,同样地,如果列很多的话,我们可以直接输列名,或者可以通过键盘的上下箭头进行列的选择,当选到我们需要的列时,按Tab键即选中该列进入公式,然后输入']'完成列的引用。 为完成'学生姓名'的提取,我们继续,到match的时候,我们的lookup_value可是要用当前行的值,怎么办?——当然没问题,在'['后再输入'@',提示还在!是的,在Excel的'表格'中,对'[列名]'表示对整列的引用,'[@列名]'表示对该列当前行的引用,如下图所示: 公式全部输入完毕后,回车,该公式将自动填充到该列的所有单元格中,不需要再动鼠标了——就是这么方便!如下图所示: 至此,通过在公式输入时得到的提示,快速地实现了跨表的引用,当你开始习惯了这种输入的方法后,你将会发现原来通过鼠标到处找数据的过程是多么的痛苦,尤其是表很多、列很多的时候! 二、用Index Match函数提升多列大量数据匹配查询效率VLookup是Excel中进行数据匹配查询用得最广泛的函数,但是,随着企业数据量的不断增加,分析需求越来越复杂,越来越多的朋友明显感觉到VLookup函数在进行批量性的数据匹配过程中出现的卡顿问题也越来越严重。 那么,在数据量较大,需要批量进行数据匹配查找的情况下,是否有办法进行适当的改善,以提高数据的匹配查找效率呢? 以下用一个例子,分别对比了四种常用的数据匹配查找的方法,并在借鉴PowerQuery的合并查询思路的基础上,提出一个简单的公式改进思路,供大家参考。 一、测试数据 本次测试涉及数据概况及要求如下: 如下图所示: 二、4种数据匹配查找方法 1、VLookup函数,按常用全列匹配公式写法如下图所示: 2、Index Match函数,按常用全列匹配公式写法如下图所示: 3、Lookup函数,按常用全列匹配公式写法如下图所示: 4、Power Query合并查询,按常规表间合并操作如下图所示: 三、4种方法数据匹配查找方法用时对比 经过分别对以上4中方法单独执行多列同时填充(Power Query数据合并法单独执行数据刷新)并计算时间,结果如下表所示: 从运行用时来看: 四、对公式法的改进 我们在前面用VLookup、Index Match写公式的思路则是对每一个需要取的值,都是一次单独的匹配和单独的取值。也就是说,每次为了查找到一个数据,都需要从订单表的2万多条数据里搜索一遍,这种效率自然会很低。
那么,如果我们在公式中可以做到只匹配一次,后面所需要取的数据都跟着这次匹配的结果而直接得到,那么,效率是否会大有改善呢? 再回头看Index Match结合的公式,其中,Match函数用于确定所需要查找内容的位置,而Index用于提取该位置相应的值! 那么,如果我们只用Match一次把位置先找出来,后面所有的列都直接用这个位置去提取相应的值,会怎样? 于是,首先用Match函数构建一个辅助列,用于获取匹配位置,如下图所示: 然后,通过Index函数,直接根据辅助列的位置从订单表里读取相应的数据,如下图所示: 经执行公式的批量填充,结果: 五、结论 在批量性匹配查找多列数据的情况下,通过对Index和Match函数的分解使用,先单独获取所需要匹配数据的位置信息,然后再根据位置信息提取所需多列的数据,效率明显提升,所需匹配提取的列数越多,效率提升越明显。 当然,使用公式的方法,即使在一定程度上进行改进,和Power Query相比仍然有很大的差距。因此,在数据量较大,数据处理较为复杂的情况下,建议使用Power Query来进行。
以上提供了Index Match函数结合超级表以及分拆使用提高效率的两种应用方法,在很大程度上体现了Index Match比VLookup函数好用的地方,您可以根据实际情况选择使用。
私信“材料”直接下载系列训练材料】【Excel必备基础小动画】 【60 函数汇总案例】
【数据透视基础精选10篇】 【Power Query入门到实战80篇】 【Power Pivot 基础精选15篇】
我是大海,微软认证Excel专家,企业签约Power BI顾问让我们一起学习,共同进步!【您的关注和转发铸就我前行的动力!谢谢支持!】
|