巧用EXCEL函数神器，解决实际工作问题

林　腾



巧用EXCEL函数神器，解决实际工作问题

林腾

摘要：在日常工作中，计算机已成为一种常用工具，它通过使用各种软件来完成相关工作，本文从实际使用的角度出发，探讨使用EXCEL函数解决日常实际工作问题的方法与途径。

关键词：EXCEL 函数；匹配；问题

当今世界已进入信息高速发展的时代，在现代的学习、工作及生活中，计算机已经成为人类不可缺少的一种工具，它与各种软件的完美结合能成为我们工作上的“好帮手”，解决现实工作的实际问题。在各类软件中， Microsoft Office是目前普及最深、应用最广的软件，被广泛使用于教学、工作、学习及生活各领域，其中EXCEL作为它的的组件之一，具有图表制作、数据统计、分析及决策等强大功能，是目前最流行的电子表格系统，进行数据处理和分析的软件工具。

一、EXCEL的使用现状

EXCEL在使用上具有界面友好，容易学习，可操作性强等特点，然而，在现实的使用领域中，由于使用者对EXCEL的认知不够深入，EXCEL大多只被用做一些简单的事情，例如：用来制作表格(EXCEL的表格功能确实比WORD制表要强大，相对复杂的表格制作起来也十分容易)，数据处理(大都是如：输入数据、设置数据的格式、SUM、AVERAGE等简单计算)，对使用EXCEL函数解决一些综合应用，了解甚少。

二、EXCEL的函数介绍

在日常办公中，使用最多的是数据处理。EXCEL函数是EXCEL处理数据的一个重要手段，它是能够完成特定功能的程序，是系统预定义的一些公式，它们使用一些称为参数的特定数值按特定的顺序或结构进行计算，然后把计算的结果存放在某个单元格中。EXCEL函数一共有11类，分别是数据库函数、日期与时间函数、工程函数、财务函数、信息函数、逻辑函数、查询和引用函数、数学和三角函数、统计函数、文本函数以及用户自定义函数。通过熟悉和掌握EXCEL函数的使用，对解决实际工作问题将有很大的帮助，可大大提高工作效率。本文将用实例来展示EXCEL在实际工作中的综合应用。

三、使用EXCEL函数解决实际问题

在管理工作中经常需要查找既定条件的数据，如果是在同一张表格内简单的查找，用排序、筛选或IF函数就可解决问题，但如果在不同表格中查找数据，用上述的方法就会稍为困难。这时可使用查询和引用函数中的VLOOKUP函数解决此问题。

(一)VLOOKUP函数

VLOOKUP函数定义：在表格或数值数组的首列匹配查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。其含义可理解为指定条件在指定区域垂直方向查找数据。

函数格式：VLOOKUP(lookup_value，table_array，col_index_num，range_lookup)

格式解释：VLOOKUP(要查找的内容，搜索的区域，从查找区域首列开始到要找的内容的列数，近似匹配还是精确匹配查找方式)

参数定义：

Lookup_value需要在数组第一列中查找的数值，可以为数值、引用或文本字符串；.Table_array为需要在其中查找数据的数据表，可以使用对区域或区域名称的引用；

Col_index_num为table_array中待返回的匹配值的列序号；

Range_lookup为一逻辑值，指明函数VLOOKUP返回时是精确匹配还是近似匹配，如果为TRUE或省略，则返回近似匹配值；如果range_value为FALSE，函数VLOOKUP将返回精确匹配值，如果找不到，则返回错误值#N/A.。

(二)实例演示

例1:利用信息平台给部分职工发送工作信息，需要在职工通讯录文件找出符合的职工。

图1　Sheet 1职工通讯录

图2　Sheet 2要查找的内容

图3　Sheet 2使用VLOOKUP函数结果

解决问题过程：

1.全体职工联系信息存放于“职工通讯录.xls”文件的表1中(即Sheet1)，如图1所示，要在Sheet2的B列、C列中查找相关人员的“移动电话”及“部门”信息，如图2所示。

2.在Sheet2！B2单元格输入函数：=VLOOKUP(A2， Sheet1!$B$2:$E$7，4，0)，确认后可获相应数据，其中“Sheet1!”是引用的工作表名称；“$B$2:$E$7”是在Sheet1数据的查找范围；“4”查找到与A2(“陈一”)相同的数据后显示第4列的内容；“0”表示使用精确匹配。余下的B3、B4单元格用填充方式完成。

3.在Sheet2！C2单元格输入函数：=VLOOKUP(A2，Sheet1!$C$2:$E$7，2，0)，确认后可获相应数据，C3、C4单元格用填充方式完成。最终结果如图3所示。

需要注意的是，查找数据的范围要定义好，最好使用绝对地址$B$2:$E$7，不能使用B2:E7，否则在拖动鼠标填充时，其单元格范围会随着发生变化，产生错误的结果。另外如果在引用外部工作簿或其他工作表时，必须定义好引用名称，还有就是要注意数据源是否有重复的数据，例如姓名相同的情况，若有应提前处理，如可加入标识符以便区别。

例2：工资管理中，工资总表由Sheet1汇总表、Sheet2加班费、Sheet3奖金等表组成，需要将其他各分项表数据汇总统计，除了基本工资外，并不是每人在各分项表都有数据，例如：“Sheet2加班费”表中“赵二”、“李四”等数据为0(如图4所示)，“Sheet3奖金”表中，只有部分员工有数据，如何将各分项表的内容加入到汇总表中。

图4　Sheet 2加班费

图5　Sheet 3奖金

图6　Sheet1-初步汇总结果

图7　Sheet1-最终汇总结果

解决问题过程：

1.各分项表的数据最终都集中到Sheet1即汇总表中汇总，因此应在Sheet1所需填入数据的单元格进行数据的提取，即寻找各分项表中的所需数据，仍然可使用VLOOKUP函数。

2.提取加班费：如图6所示，在Sheet1中E2单元格输入函数：=VLOOKUP(B2，Sheet2加班费!B:D，3，0)，确认后可获相应数据，其中“Sheet2加班费!”是引用的工作表名称；“B:D”是将数据的查找范围固定在B至D列；“3”查找到与B2(“陈一”)相同的数据后显示第3列的“加班费”内容；“0”表示使用精确匹配。余下的E3至E7单元格用填充方式完成。

3.提取奖金：在Sheet1中F2单元格输入函数：=VLOOKUP(B2，Sheet3奖金!B:D，3，0)，确认后可获相应的奖金数据，参数说明与上一点相同，余下的F3至F7单元格用填充方式完成后，汇总的合计数据就可自动计算出来，结果如图6所示。

(三) 继续探究

在例2中，函数计算后的结果图6中，由于在F列奖金的运算中，有些数据在Sheet3奖金中找不到，函数只能返回错误值“#N/A”，由此对汇总合计也产生影响，造成自动计算的结果也是错误值“#N/A”。应想办法对VLOOKUP函数的返回值“#N/A”作出相应处理，以免影响计算结果。在EXCEL函数中，通过使用ISERROR和IF函数，可解决此问题。

1.IF函数是常用的逻辑函数，这里不再累述。ISERROR函数是一个测试错误的函数，它的格式是：ISERROR(value)，语法是：ISERROR 值为任意错误值(#N/A、#VALUE!、#REF!、#DIV/0!、#NUM!、#NAME?或#NULL!)。如果测试值为错误的时候，当前得到的值为“TRUE”，否则将为“FALSE”。

2.将例2的Sheet1中F2单元格改为=IF(ISERROR(VLOOKUP(B2，Sheet3“奖金”!B:D，3，FALSE))，“0”，VLOOKUP(B2，Sheet3“奖金”!B:D，3，0))，其中VLOOKUP(B2，Sheet3“奖金”!B:D，3，0)是原来的内容，运算中当是找到匹配值时它输出具体数值，找不到匹配值时则输出错误值“#N/A”，ISERROR(VLOOKUP(B2，Sheet3“奖金”!B:D，3，FALSE)则会在VLOOKUP函数运算找到匹配值时它输出为“FALSE”，找不到匹配值时则输出“TRUE”，

3.IF函数判断ISERROR函数的值为“TRUE”时，F2单元格显示“0”，值为“FALSE”时，F2单元格则显示VLOOKUP函数运算找到匹配值。经过运算，所有错误值“#N/A”均已消失，汇总结果正确，最终结果如图七所示。

四、结语

在使用VLOOKUP函数运算中，通过参数灵活的设置，可以实现简单或复杂的数据匹配查找，再加上ISERROR和IF等函数的嵌套使用，更可以使输出的结果“如你所愿”，本文只是通过几个函数的运用，解决了一些实际问题，充分体现了EXCEL函数的强大。EXCEL函数的数量足足有几百个之多，而我们平时常用的可能只有二三十个，其还有大量的潜能未能展现。因此，我们应该在平时的学习和工作中，不断了解新的函数，尝试和使用新的函数，使EXCEL函数成为我们工作上的“神器”，让我们在工作中更加“得心应手”。

参考文献：

[1]卓越文化.Excel2007电子表格[M].北京: 电子工业出版社，2010：190-191.

[2]马军.Excel数据处理与图表应用实例精讲[J]北京：科学出版社，2006.

责任编辑朱守锂

收稿日期：2016-03-18

作者简介：林腾(1969-)，男，广东省湛江机电学校计算机讲师，职业指导师。研究方向：计算机信息技术。(广东 湛江/524018)

中图分类号：G712

文献标识码：A

文章编号：1005-1422(2016)06-0066-03