巧用VLOOKUP和IF函数合并工作表

时间:2022-07-28 04:40:42

巧用VLOOKUP和IF函数合并工作表

【摘 要】Excel有很强的数据处理功能,利用其内置的函数可以帮助我们高效、快速的完成日常工作。本文通过一个实际的例子介绍了VLOOKUP和IF函数合并工作表的方法,以避免重复输入数据。

【关键词】VLOOKUP函数;IF函数;合并

Excel是微软办公套装软件的一个重要的组成部分,它可以进行各种数据的处理、统计分析和辅助决策操作,广泛地应用于管理、统计财经、金融等众多领域。Excel之所以功能强大、应用广泛主要是其内置了非常丰富的函数,Excel函数一共有11类,主要有数学和三角函数、统计函数、文本函数日期与时间函数、查找和引用函数、财务函数、逻辑函数等。在这些函数中大多数人比较熟悉的是SUM、AVERAGE和COUNT之类的常用函数,其他函数使用较少。其实Excel中还有很多函数可以帮助我们高效、快速的完成工作,例如把VLOOKUP和IF这两个函数结合起来合并工作表,可起到事半功倍的效果。

1 问题的提出

每到学期结束学校各部门都需要统计教师的工作量,笔者所在学校教师的工作量分两块,一个是由教务部门统计的课堂教学工作量,另一个是由其他部门如学工处、团委统计的非课堂教学工作量。有些教师既有课堂教学工作量,又有非课堂教学工作量,而有些教师只有课堂教学工作量或者只有非课堂教学工作量(如图1和图2所示), 现在需要将两张表合并成一张表。由于两张表中的教师相互有交叉,直接复制粘贴行不通。此时如果使用VLOOKUP函数进行查找引用,结合IF函数就可轻松完成任务。下面我们将介绍要用到的两个函数。

图1 工作量统计表1 图2 工作量统计表2

2 函数介绍

2.1 VLOOKUP函数

函数功能:VLOOKUP是按列查找,最终返回该列所需查询列序所对应的值;Vlookup函数在Excel中广泛运用,特别是在做报表、登记数据和查找数据等方面。

函数格式:VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

其中:

Lookup_value :需要在其中查找匹配数据的开始单元格

Table_array:两列或多列数据(用绝对地址)

Col_index_num:为 table_array 中待返回的匹配值的列序号。值为1 时,返回 table_array 第一列中的数值;值为 2时,返回 table_array 第二列中的数值,以此类推。

Range_lookup:为逻辑值,指定希望 VLOOKUP 查找精确的匹配值还是近似匹配值(如果为 TRUE 或省略,则返回精确匹配值或近似匹配值。此时第一列中的值必须以升序排序;否则 VLOOKUP 可能无法返回正确的值(可以事先对工作表按升序进行排序);如果为 FALSE,VLOOKUP 将只寻找精确匹配值。在此情况下,第一列的值不需要排序。)

2.2 IF函数

函数功能:IF函数用于执行真假值判断后,根据逻辑测试的真假值返回不同的结果,因此If函数也称之为条件函数。它的应用很广泛,可以使用函数 IF 对数值和公式进行条件检测。

函数格式:IF(logical_test,value_if_true,value_if_false)。

其中:Logical_test是一个计算结果为 TRUE 或 FALSE 的任意值或表达式。本参数可使用任何比较运算符。

Value_if_true是在logical_test 为 TRUE 时返回的值,Value_if_true 也可以是其他公式。Value_if_false logical_test 为 FALSE 时返回的值。Value_if_false 也可以是其他公式。

总之,IF函数的第一个参数的结果为真的话,则将第二个参数Value_if_true的值作为函数的返回值,如果为假则将第三个参数Value_if_false的值作为函数的返回值。IF函数可以嵌套七层,用 value_if_false 及 value_if_true 参数可以构造复杂的检测条件。

3 解决方法

第一步:引用工作量统计表2中的数据填充工作量统计表1中相应单元格。为此,在工作量统计表1的C2单元格输入以下内容:“=VLOOKUP(A2,工作量统计表2!$A$2:$C$21,3,FALSE)”。含义是在工作量统计表2的A2:C21单元格区域中查找与A2值相同的那行所对应的第3列的值填充到工作量统计表1的C2单元格。然后按住填充柄向下拖拉到C16单元格释放鼠标。此时可以看到由于两个工作表中的人员并不全部相同,工作量统计表1中部分单元格的值在工作量统计表2中找不到,显示的内容为“#N/A”,如图3所示。

图3 引用统计表2数据

第二步:引用工作量统计表1中的数据填充工作量统计表2的相应单元格。在工作量统计表2的D2单元格输入以下内容:“=VLOOKUP(A2,工作量统计表1!$A$2:$D$16,4,FALSE)”。意思是在工作量统计表1的A2:D16单元格区域中查找与A2值相同的那行所对应的第4列的值填充到工作量统计表2的D2单元格。按住填充柄向下拖拉到D16单元格,同样也会有部分单元格显示为“#N/A”。

第三步:将工作量统计表1的A1:D16单元格区域复制并选择性粘贴到一个新工作表中(在选择性粘贴时选择“数值”),再将工作量统计表2的A2:D21单元格区域也选择性粘贴到该工作表,使两张工作表的数据合并在一起。

第四步:在新工作表中选择C2:D36单元格区域,使用查找替换功能将“#N/A”删除。

第五步:在新工作表中按工号作升序排序,可以看到由于两张表中的人员有部分重复,所以此时新工作表中有部分人员出现了两次,因此要想办法删除重复记录。

第六步:在新工作表的E1和F1单元格分别输入“重复否1”和“重复否2”,然后在E2单元格输入以下内容:“=IF(A3=A2,“是”,“否”)”,拖拉填充柄到E36。选择E2:E36单元格区域,选择性粘贴到F列(选择性粘贴时选择“数值”),如图4所示。

图4 选择性粘贴

第七步:在新工作表中删除“重复否1”列,然后按“重复否2”字段作升序排序,可以看到从第29行开始到36行都是重复的记录(如图5所示),删除这些记录,再删除“重复否2”列,并将工作重命名为“合并”,至此问题就解决了。

如果要查找替换的数据是按行排列的,那么与VLOOKUP 相类似可以用HLOOKUP函数来完成。通过以上例子我们看到利用Excel提供的函数可以帮助我们解决很多实际问题,把几个函数结合起来更是可以成倍地提高工作效率。

图5 标记重复记录

上一篇:对《操作系统》教学的思考 下一篇:三电平光伏逆变器故障诊断研究