excel中如何快速比对两列数据是否一致实现对于数值正反向的核对？

来源：蜘蛛抓取(WebSpider) 时间：2023-03-25 00:59 标签： 两个数据表自动核对

第十三天：EXCEL：用得最多的按列查找函数VLOOKUP，懂的人自然懂
朋友们，大家好！
在日常工作中，我们经常用EXCEL函数处理表册，函数的运用，大幅度提升了我们的工作效率。笔者集合工作实际，把最常用的EXCEL函数进行逐一详细讲解，课程中有大量案例，为便于朋友们更加深入了解各个函数的用法，将于每天上午7:00同步发布视频教程和图文教程（包含公式）。只要不懈努力和不断实践，通过30天的系统学习，你也能成为EXCEL函数高手，从此告别加班，让同事和朋友刮目相看。
今天，我将和大家一起分享按列查找函数VLOOKUP的用法，函数中最受欢迎的有三大家族，一个是以SUM函数为首的求和家族，一个是以VLOOKUP函数为首的查找引用家族，另一个是以IF函数为首的逻辑函数家族，学好这三大家族的函数，就能完成80%的工作。VLOOKUP函数的基本用法很多人都会，但更深层次的用法大部分人没用过，我们利用一节课的时间进行剖析。
一、VLOOKUP函数基础知识
VLOOKUP函数定义：VLOOKUP函数是EXCEL中的一个纵向查找函数，它与LOOKUP函数和HLOOKUP函数属于一类函数，在工作中广泛应用，可以用来核对数据，多个表格之间快速导入数据等函数功能。功能是按列查找，最终返回该列所需查询序列所对应的值，与之对应的HLOOKUP是按行查找。
语法：VLOOKUP(查找值,查找区域,区域中包含要返回值的列号,返回近似匹配或精确匹配)
查找值：是指需要在数据表中进行查找的数值，可以为数值、引用或文本字符串。当VLOOKUP函数第1参数省略查找值时，表示用0查找。请记住，查阅值应该始终位于所在区域的第一列，这样VLOOKUP才能正常工作。例如：如果查阅值位于B2单元格内，那么区域应该以B开头。
查找区域：是指需要在其中查找数据的数据表，可以是对区域或区域名称的引用。
区域中包含要返回值的列号：为“查找区域”中查找数据的数据列序号。当值为1时，返回“查找区域”第一列的数值，当值为2时，返回“查找区域”第二列的数值，以此类推。如果值小于1，函数VLOOKUP返回错误值#VALUE!；如果值大于“查找区域”的总列数，函数VLOOKUP返回错误值#REF!。
返回近似匹配或精确匹配：为逻辑值，指明函数VLOOKUP查找时是精确匹配，还是近似匹配。如果为FALSE或0，则返回精确匹配，如果找不到，则返回错误值#N/A。如果为TRUE或1，函数VLOOKUP将查找近似匹配值，也就是说，如果找不到精确匹配值，则返回小于“查找值”的最大数值。
注意：VLOOKUP函数在进行近似匹配时的查找规则是从第一个数据开始匹配，没有匹配到一样的值就继续和下一个值进行匹配，直到遇到大于查找值的值，此时返回上一个数据（近似匹配时应对查找值所在列进行升序排列）。如果省略该参数，则默认为1，即近似匹配。
二、VLOOKUP函数案例实践
清楚了VLOOKUP函数的定义、语法及相关知识，下面，我们进行几个案例解析。
（一）制作学生成绩通知单
某学校在组织学生考试后，把全年级学生的考试成绩统计在一个表格里，需要单独发送学生的成绩通知单，涉及语文、数学、英语三门课，需要用到VLOOKUP函数进行查找。
在I2单元格输入公式：=VLOOKUP("姓名5",$B$3:$F$10,2,FALSE)，按回车键即可查找到学生“姓名5”的语文成绩。见下图：
VLOOKUP函数可以对单元格区域的第一列查找，然后返回该区域相同行指定列的值。如果单元格区域的第一列有两个及以上相同的需要查找的值，查找到的值只会是排在最前面的那一个。
本例中，第1参数要查找的值是“姓名5”，位于B列，第2参数要查找的区域是B3:F10，第3参数2表示要查找的语文成绩位于选择的查找区域第二列（注意：不是工作表第2列的值），第4参数FALSE表示精确匹配查找。
本例中，数学成绩排在查找区域B3:F10的第3列，英语成绩排在查找区域B3:F10的第4列，总分排在查找区域B3:F10的第5列。相当于区域B列、C列、D列、E列、F列对应1、2、3、4、5。
（二）根据笔试和面试成绩评定等次
某部门招考专业技术岗位人员，考试采取笔试+面试的方式，根据笔试和面试成绩平均分确定等次，小于60分为“差”，大于等于60分小于80分为“及格”，大于等于80分小于90分为“良好”，90分及以上为“优秀”。我们可以预先设置好分数段和对应的等次，然后用VLOOKUP函数进行模糊查找。
在E3单元格输入公式：=VLOOKUP(D3,$G$3:$H$6,2,TRUE)，按回车键即可评定“姓名1”的等次。见下图：
当VLOOKUP函数的第4参数设置为TRUE时，表示的是模糊查找，需要查找的值所在的列就必须按照升序进行排列，否则不能返回正确结果。在进行模糊查找时，VLOOKUP函数查找的是小于等于所需查找值的最大值。
本例中，D3表示要查找的“姓名1”的平均分；$G$3:$H$6表示分数段和等次区域，将D3单元格的值在该区域内查找；“2”表示$G$3:$H$6区域的第二列，即等次所在的列；TRUE在VLOOKUP函数中表示模糊查找。
本例是通过引用分数段和等次单元格区域进行判定，我们也可以运用IF函数多层嵌套的方式进行等次评定，公式可写成：=IF(D3<60,"差",IF(D3<80,"及格",IF(D3<90,"良好","优秀")))，两种方法的结果是完全相同的。
（三）根据学生姓名查找学号
某学校要通过学生的姓名查找学生“姓名4”的学号，学号在第1列，姓名在第2列，由于VLOOKUP函数只能对单元格区域的第1列查找指定的值，而我们查找的姓名却在第2列，这时候需要用IF函数对单元格区域作一定处理，使姓名位于单元格区域的第1列。有的朋友会问，那为什么不直接把学号列剪切到姓名列后面呢？当然这样操作也是可以的。不过这里为了讲解VLOOKUP和IF函数的使用方法，我们用下面的方法处理。
在D13单元格输入公式：=VLOOKUP("姓名4",IF({1,0},B3:B10,A3:A10),2,FALSE)，按回车键即可查找出学生“姓名4”的学号。见下图：
本例通过在IF函数中使用数组实现两列数据位置调换，对于需要查找到值不在单元格区域第1列的情况，一般使用LOOKUP函数进行查找，公式可写成：=LOOKUP("姓名4",B3:B10,A3:A10)，两种方法的结果完全相同。
知识拓展：
很多人对IF({1,0}……感到迷惑，大部分都来自于VLOOKUP函数从右向左查找的公式：=VLOOKUP(B6,IF({1,0},B3:B10,A3:A10),2,FALSE)，想彻底搞清楚它的原由，我们还得从IF的基本用法说起。
=IF(A1>=0,"正数","负数")这个很容易理解，如果条件成立，返回第一个值，条件不成立返回第二个。
A1>=0（条件表达式）的结果只有两种：A1的值大于等于0时，结果是TRUE，否则结果是FALSE，而在IF结构中，所有非0的数字都等同于TRUE，而0相当于FALSE，由此，下面两个公式的结果就可以理解了。
=IF(1,"正数","负数")结果为：正数
=IF(0,"正数","负数")结果为：负数
IF不仅可以返回一个值，也可以返回区域引用。所以上面这个例子的公式大家应该理解了。
=IF(1,B3:B10,A3:A10)返回的是B3:B10
=IF(0,B3:B10,A3:A10)返回的是A3:A10
而如果IF函数第一个参数是一个数组{1,0}，如本例的=IF({1,0},B3:B10,A3:A10)参数为数组时，会分别进行计算。先用1作为参数判断，返回B3:B10，再用0作为参数判断，又返回一个结果A3:A10，两个结果会重新组合成一个数组，返回的将不是一组2个值，而是2列8行的一组值。
可以理解为，把B3:B10的值放在1的位置，把A3:A10的值放在0的位置，重构成一个2列8行的数组，使用IF函数将原B列放在第1列，A列放在第2列。两列数调换了位置，就可以用VLOOKUP函数从左向右查找。
说到底，并不是VLOOKUP函数可以实现反向查找，而是IF({1,0},B3:B10,A3:A10)把B列和A列的数据调换了位置而已。
（四）根据供货商和产品名称查询价格
某公司共有3个供货商供应A4纸、打印机耗材、席位牌等多种产品，现在需要根据某家供货商和产品名称查询单价。本例是一个多条件查找的问题，一般思路是将其转换为单条件查找，可以使用文本连接符将需要查找的条件连接为一个字符串，对于查找的列也作同样的处理，然后使用IF函数将处理后的列与要返回结果的所在列组成一个区域，最后使用VLOOKUP函数在这个新的区域中查找。
在C14单元格输入公式：=VLOOKUP(A14&B14,IF({1,0},A3:A10&B3:B10,C3:C10),2,FALSE)，按【Ctrl+Shift+Enter】三键组合即可根据供货商和产品名称查询到对应的价格。见下图：
使用文本连接符连接在一起的两列单元格，连接之后相当于一列单元格。因此，在本例中VLOOKUP函数的第3个参数为2，而不是3。
对于多条件查找问题，如果最后返回的结果为数值型数据，且在查找单元格中符合条件的结果只有一个，该问题就可以转换为多条件求和的问题。本例就可以转换为多条件求和，解决多条件求和类问题的方法有很多，比如使用条件表达式的乘积、SUMIFS函数等。
使用条件表达式的乘积公式可写成：=SUM((A3:A10=A14)*(B3:B10=B14)*C3:C10)（【Ctrl+Shift+Enter】三键组合）。
使用SUMIFS函数公式可写成：=SUMIFS(C3:C10,A3:A10,A14,B3:B10,B14)。
本例中的以上三种方法的计算结果完全相同。
以上就是VLOOKUP函数的几个经典案例，想要学习好EXCEL函数，要学会多个函数的组合，后续课程中我们会讲到多函数结合的用法，能进行更为复杂的计算，对提升工作效率有很大帮助。
个人建议：在学习EXCEL函数时，首先要熟悉函数的功能和语法，尽量多写，通过反复书写和实践，对照文中的案例，举一反三，我相信，你的EXCEL技巧会得到快速提升，日积月累，必成大器！
感谢朋友们的支持，如果你有好的意见建议和问题，欢迎在评论区留言交流，期待你的精彩！

第一，lookup函数用法介绍；第二，通过实例讲解lookup函数经典的条件查找解法，通用公式基本可以写为：LOOKUP(2,1/(条件),查找数组或区域)或LOOKUP(1,0/(条件),查找数组或区域)。
　　第一部分：lookup函数用法介绍
　　lookup函数和vlookup函数是excel中最常用的两个查找函数。vlookup函数能做到的lookup函数同样可以做到，而且可以做得更好。
　　LOOKUP函数有两种语法形式：向量和数组。本期就向量形式的展开交流和探讨。
　　向量形式的语法为：LOOKUP(lookup_value,lookup_vector,result_vector)
　　其中的参数意义如下：
　　Lookup_value：为所要查找的数值。Lookup_value 可以为数字、文本、逻辑值或包含数值的名称或引用。　　Lookup_vector：为只包含一行或一列的区域。Lookup_vector 的数值可以为文本、数字或逻辑值。Lookup_vector的数值必须按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE,否则， LOOKUP不能返回正确的结果。文本不区分大小写。　　Result_vector:只包含一行或一列的区域，其大小必须与 lookup_vector 相同。　　比如lookup(A1,B1:B10,C2:C11),其中C2:C11的尺寸要与B1:B10相同，且如果A1对应B列中的位置是B2的话，那么返回的将是C3的值。
　　LOOKUP函数说明：
　　第一，如果函数 LOOKUP 找不到 lookup_value，则查找 lookup_vector 中小于或等于 lookup_value 的最大数值。这就是为何返回最后一个满足条件的值的原理。　　第二，如果 lookup_value 小于 lookup_vector 中的最小值，函数 LOOKUP 返回错误值 #N/A。　　利用这个特性，我们可以用=LOOKUP(1,0/(条件),引用区域)这样一个通用公式来作查找引用。
　　第二部分：lookup函数实例运用
　　运用一：模糊查找
　　模糊查找的核心是第二个参数排序必须是升序，否则会导致查找值错误。下图所示的表1是按升序排序的，表2没有排序。
　　分别在表1和表2下面对应的单元格输入公式。
　　表1的数据源是按升序排序的，根据lookup函数用法：=LOOKUP(要查找的数据,查找范围,结果)，在C24单元格设置公式：=LOOKUP(B24,$B$5:$B$17,$C$5:$C$17)，然后下拉得到正确结果。
　　表2的数据源是没有排序的，在J24单元格输入公式：=LOOKUP(I24,$I$5:$I$17,$J$5:$J$17) ，然后下拉，发现J25单元格得到的结果是H126，显然不对。通过表2的源数据可以看到I25单元格对应的值应该为J8单元格的值H142。
　　为什么会出错呢?这就印证了第一部分的用法介绍中所讲到的：Lookup_vector的数值必须按升序排序：...、-2、-1、0、1、2、...、A-Z、FALSE、TRUE,否则， LOOKUP不能返回正确的结果。文本不区分大小写。
　　模糊查找，数据源一定要以升序先进行排序，否则就会出错。在数据源没有排序的情况下，如何才能查找到正确结果?LOOKUP函数有一个经典的条件查找解法，可以很好的解决此问题。
　　在第一部分有提到，通用公式基本可以写为：LOOKUP(2,1/(条件),查找数组或区域) 或LOOKUP(1,0/(条件),查找数组或区域)。
　　公式中的2、1、0等数字的含义是什么? 首先，条件是一组逻辑判断的值或逻辑运算得到的由TRUE和FALSE组成或者0与非0组成的数组，因而：0/(条件)的作用是用于构建一个由0或者#DIV!0错误组成的值。比如数据源中能查找到对应值就是ture，没有就是false。形式如：0/True=0，0/false=#DIV0!，查找到就0，没有就是错误值。
　　如果 LOOKUP 函数找不到 lookup_value (即：1)，则它与 lookup_vector 中小于或等于 lookup_value 的最大值(即：0)匹配。
　　也就是说，要在一个由0和#DIV!0组成的数组中查找1，肯定找不到1，因而将返回小于或等于1的最大值(也就是0)匹配。用大于0的数来查找0，肯定能查到最后一个满足条件的。
　　以上的原理，被俗称为“以大欺小法”。这种技巧在LOOKUP函数上的运用是很常见的。
　　利用上面的原理，不管有没有排序，只要使用上面的“以大欺小法”都能得到正确结果。比如上面实例中，在J25单元格输入公式：=LOOKUP(1,0/(I25=$I$5:$I$17),$J$5:$J$17)，就可以了。
　　运用二：精确查找
　　第一，查找的数据没有对应值，可以利用ISNA(ISERROR)函数屏蔽错误值。
　　如上图所示，表3是数据源，在下面左边根据“番号”查找“俗称”。单击C51单元格，输入公式=LOOKUP(1,0/(B51=$B$42:$B$45),$C$42:$C$45)，然后下拉可以看到下面的C52和C54单元格出现错误值。这种情况可以利用ISNA(ISERROR)函数屏蔽错误值。
　　只要在公式外面嵌套个if(isna(lookup(),"",lookup())，这样的形式就可以把错误值屏蔽。在H51单元格，输入这样的公式：=IF(ISNA(LOOKUP(1,0/(G51=$B$42:$B$45),$C$42:$C$45)),"",LOOKUP(1,0/(G51=$B$42:$B$45),$C$42:$C$45))，下拉，就可以屏蔽错误值了。将错误值屏蔽了，表格就好看多了。
　　上面公式中，""，是显示空的意思，错误就显示空，没有就查找。
　　第二，借助错误值来判定产品是否存在。
　　下图所示根据左边的数据源，来判定右边对应的数据是否在番号列中。
　　只需要嵌套一个isna函数就可以做到，如果没有存在就错误，有存在就......这样的形式。在H62单元格输入公式：=IF(ISNA(LOOKUP(1,0/(G62=$B$62:$B$74))),"否","是")，下拉就即可得出结果。
　　“图啥”网友问：iserror与isna函数的区别。ISNA只屏蔽#N/A错误，ISERROR屏蔽所有错误。
　　第三，LOOKUP函数多条件查找。
　　如上图所示，根据“俗称”和“订单号”来查找“订单数”和“尾数”，可以套用这样的公式：=LOOKUP(1,0/(条件(1)*(2)*(3).。。。。。),引用区域)，用*或&将各个条件连接起来，*就是和的意思。
　　此题有两种方法：　　第一，在K112单元格输入公式：=LOOKUP(1,0/(($I112=$B$112:$B$120)*($J112=$C$112:$C$120)),D$112:D$120)，复制公式就可以得到结果。　　第二，另外也可以使用这个公式：=LOOKUP(1,0/($I112&$J112=$B$112:$B$120&$C$112:$C$120),D$112:D$120)
　　第四，含某个字符查找。
　　按照上图所示，根据左边的数据源，来对含有某个字符进行查找。单击G128单元格，输入公式：=LOOKUP(1,0/(FIND($F128,$B$128:$B$131)),B$128:B$131)，就可以得到结果。
　　VLOOKUP函数与lookup函数对比：
　　第一，在多条件查找方面，就能看出lookup函数好用。用vlookup多条件查找，最简单的方法就是借用辅助列。　　第二， VLOOKUP函数对于反向查找是需要嵌套其余函数才能实现，而LOOKUP函数没有正反之分，因此在这方面LOOKUP函数会更加容易实现。　　第三， vlookup在查找字符方面，可以使用*号类通配符。LOOKUP是不支持通配符的，但可以使用FIND (查找字符，数据源区域)的形式代替。

我要回帖

更多关于 两个数据表自动核对 的文章

 

随机推荐