论文部分内容阅读
摘 要：在EXCEL函数中，查找与引用类的函数有近20个，而其中的VLOOKUP函数在实现信息自动引用的应用中有着极高的应用价值，但由于该函数的参数中隐含的特性并未详细说明，因此常常会导致引用的结果发生错误，因此必须重视对该函数的参数认识，从而提高信息引用的准确性。
关键词：VLOOKUP函数；数据链接；应用技巧
中图分类号：G433 文献标识码：A 文章编号：1673-8454(2008)08-0031-02
在信息管理中，如何巧用EXCEL的函数功能将A表代码中所对应的信息准确地添加至B表，从而大大提高工作效率和准确度，是许多EXCEL使用者关注的问题。本文通过VLOOKUP函数在信息管理中的几则应用实例，阐述了该函数的使用技巧。
一、应用实例
1．实例1——在学籍管理中的应用
“籍贯”是毕业证书中必须有的一个字段，对于有上万人的高校来说，这些内容如果按姓名逐条输入，工作量相当大。为此可以利用已有的身份证号（其前几位具有地区特征）进行信息的自动引用。
（1） 建立地区代码对照表
关于地区与代码的对应信息可从网上获取，并复制到学生信息表中另一个区域X1：AA381（见表1）。
（2）在学生信息表中输入公式并向下复制
U4=VLOOKUP(LEFT(D4,4), X$1:AA$381,4,1) ①
该公式的意义是：根据所提取的身份证号码的左四位，从X1：AA381（号码地区对照表）中匹配添加第四列信息（即省市）。
2．实例2——在教材管理中的应用
一所上万人的高校，教材管理的工作量另人惊叹，VLOOKUP将再显其高效和神奇。
（1）建立教材目录表
教材目录表各列标签及列号如表2所示。
（2）建立出、入库帐工作表
《入库帐》和《出库帐》中均有“凭单号” 、“日期”、“书号”、“书名”、“主编”、“出版社”、“单价”、“数量”、 “码洋”等字段，不同的是前者有“供货单位” 、后者有 “领用部门”。
（3）输入公式
在《入库帐》工作表（见表3）中输入如下公式：
D2=IF(C2="","",VLOOKUP(C2,教材目录!A:E,2,0)) ②
E2=IF(C2="","",VLOOKUP(C2,教材目录!A:E,3,0)) ③
F2=IF(C2="","",VLOOKUP(C2,教材目录!A:E,4,0))④
G2=IF(C2="","",VLOOKUP(C2,教材目录!A:E,5,0)) ⑤
该组公式的意义是分别在D2、E2、F2、G2中添加与书号“7115113742”匹配的书名、主编、出版社、单价（若管理员未输入书号，则不显示）。
（4）复制公式
考虑到教材入库的信息不断增加，故向下复制填充的范围可选大一点，以足够一个学期的使用。
3． 实例3——在工资（兼课酬金）管理中的应用
“扣税”是工资表中的一个重要栏目，而税率与月收入有关，巧用VLOOKUP函数则很容易实现。
（1） 建立税率分段计算表
按国家税法规定输入税率分段对照表(见表4)；
（2） 输入公式并向下复制
在工资表中（见表4）中输入公式：
P2=VLOOKUP(O2,S$1:V$10,3)*O2-VLOOKUP(O2,S$1:V$10,4)⑥ 该公式的意义是：所得税=应纳税额的最高段的税率×应纳税额-多扣的低段税额。最后复制公式。
二、 应用技巧
1． VLOOKUP函数简介
（1） 功能
在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。
（2）语法：VLOOKUP(lookup_
value,table_array,col_index_numr
ange_lookup)
2．VLOOKUP函数在以上实例中的应用技巧
（1）模糊查找与精确查找的应用技巧
这体现在第四个参数的不同。若该参数为“0”，则为准确查找，若该参数省略或不为“0”，则为模糊查找。所谓精确查找，即若找不到则显示#N/A（对于教材管理，需要精确查找，若找不到，则表明在“教材目录”表中还缺少相关代码信息，用户必须补充相关信息）；而所谓模糊查找，是指当找不到时会按比该数小的最大数所对应的信息认定（对于例3，由于税率表是按段建立的，因此在表4中查找应纳税额4050的税率，必须采用模糊查找，即找比4050小的最大数为2000所对应的税率是0.15，再减去多扣的低段税额）。
（2）动态引用与绝对引用的应用技巧
这体现在第二个参数的不同。对于教材管理，由于“教材目录”表中的信息需要适时更新或增加，故引用的范围为《教材目录》表中A：E列（下界为工作表的最大行号，以保证动态变化的需要），而工资税率及身份证号与地区对照表均为固定，故引用的范围应绝对不变。
3.其他应用技巧
VLOOKUP函数中的第一个参数与第二个参数中的第一列的数据格式必须完全一致，若两者格式不同，则返回#N/A。在实例2中若C2中的书号“7115113742”为数值型格式，则在D2、E2、F2、G2中输入公式②~⑤后，其书名、主编、出版社、单价等均返回#N/A，因此，在实际查找引用前必须对不规范的数据进行处理，如统一转为文本格式或数字格式，确保第一个参数与第二个参数中的第一列的数据格式的一致性。
向下复制公式时，由于EXCEL默认为相对行引用，为保证引用范围不越界，第二个参数应采用行绝对引用，而例2中，因引用的范围已为工作表的最大行号，故毋需绝对引用。
第二个参数所对应的数据表可以建在主表中（如本文例1和例3），也可建在其他工作表或工作簿（如本文例2的“教材目录”表）。
三、 结束语
VLOOKUP函数的使用非常灵活，它不仅能根据代码准确提取信息，这从公式②~公式⑤中可以显而易见，只需改变参数3的数值，就能方便地添加教材、单价、出版社、主编等相关信息；而且还可将提出的数据按工程实际要求进行再次运算，如本文公式⑥前后两次使用了VLOOKUP函数就是一个很好的示例。此外，通过VLOOKUP函数还可将不同表的信息进行提取输出，解决了工作簿之间接口问题（如本文例2）。
当今的时代是信息化时代，我们的许多工作都与代码打交道，我们只有把握住VLOOKUP函数中的参数特性，才能发挥其在自动链接信息方面的准确、高效功能。
参考文献：
[1][美]John Walkenbach著，邱燕明，赵迎等译.Excel 2003公式与函数应用宝典[M].北京:电子工业出版社,2006.
[2]郑小玲等.EXCEL在信息管理中的应用[M].北京:人民邮电出版社,2004.
关键词：VLOOKUP函数；数据链接；应用技巧
中图分类号：G433 文献标识码：A 文章编号：1673-8454(2008)08-0031-02
在信息管理中，如何巧用EXCEL的函数功能将A表代码中所对应的信息准确地添加至B表，从而大大提高工作效率和准确度，是许多EXCEL使用者关注的问题。本文通过VLOOKUP函数在信息管理中的几则应用实例，阐述了该函数的使用技巧。
一、应用实例
1．实例1——在学籍管理中的应用
“籍贯”是毕业证书中必须有的一个字段，对于有上万人的高校来说，这些内容如果按姓名逐条输入，工作量相当大。为此可以利用已有的身份证号（其前几位具有地区特征）进行信息的自动引用。
（1） 建立地区代码对照表
关于地区与代码的对应信息可从网上获取，并复制到学生信息表中另一个区域X1：AA381（见表1）。
（2）在学生信息表中输入公式并向下复制
U4=VLOOKUP(LEFT(D4,4), X$1:AA$381,4,1) ①
该公式的意义是：根据所提取的身份证号码的左四位，从X1：AA381（号码地区对照表）中匹配添加第四列信息（即省市）。
2．实例2——在教材管理中的应用
一所上万人的高校，教材管理的工作量另人惊叹，VLOOKUP将再显其高效和神奇。
（1）建立教材目录表
教材目录表各列标签及列号如表2所示。
（2）建立出、入库帐工作表
《入库帐》和《出库帐》中均有“凭单号” 、“日期”、“书号”、“书名”、“主编”、“出版社”、“单价”、“数量”、 “码洋”等字段，不同的是前者有“供货单位” 、后者有 “领用部门”。
（3）输入公式
在《入库帐》工作表（见表3）中输入如下公式：
D2=IF(C2="","",VLOOKUP(C2,教材目录!A:E,2,0)) ②
E2=IF(C2="","",VLOOKUP(C2,教材目录!A:E,3,0)) ③
F2=IF(C2="","",VLOOKUP(C2,教材目录!A:E,4,0))④
G2=IF(C2="","",VLOOKUP(C2,教材目录!A:E,5,0)) ⑤
该组公式的意义是分别在D2、E2、F2、G2中添加与书号“7115113742”匹配的书名、主编、出版社、单价（若管理员未输入书号，则不显示）。
（4）复制公式
考虑到教材入库的信息不断增加，故向下复制填充的范围可选大一点，以足够一个学期的使用。
3． 实例3——在工资（兼课酬金）管理中的应用
“扣税”是工资表中的一个重要栏目，而税率与月收入有关，巧用VLOOKUP函数则很容易实现。
（1） 建立税率分段计算表
按国家税法规定输入税率分段对照表(见表4)；
（2） 输入公式并向下复制
在工资表中（见表4）中输入公式：
P2=VLOOKUP(O2,S$1:V$10,3)*O2-VLOOKUP(O2,S$1:V$10,4)⑥ 该公式的意义是：所得税=应纳税额的最高段的税率×应纳税额-多扣的低段税额。最后复制公式。
二、 应用技巧
1． VLOOKUP函数简介
（1） 功能
在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。
（2）语法：VLOOKUP(lookup_
value,table_array,col_index_numr
ange_lookup)
2．VLOOKUP函数在以上实例中的应用技巧
（1）模糊查找与精确查找的应用技巧
这体现在第四个参数的不同。若该参数为“0”，则为准确查找，若该参数省略或不为“0”，则为模糊查找。所谓精确查找，即若找不到则显示#N/A（对于教材管理，需要精确查找，若找不到，则表明在“教材目录”表中还缺少相关代码信息，用户必须补充相关信息）；而所谓模糊查找，是指当找不到时会按比该数小的最大数所对应的信息认定（对于例3，由于税率表是按段建立的，因此在表4中查找应纳税额4050的税率，必须采用模糊查找，即找比4050小的最大数为2000所对应的税率是0.15，再减去多扣的低段税额）。
（2）动态引用与绝对引用的应用技巧
这体现在第二个参数的不同。对于教材管理，由于“教材目录”表中的信息需要适时更新或增加，故引用的范围为《教材目录》表中A：E列（下界为工作表的最大行号，以保证动态变化的需要），而工资税率及身份证号与地区对照表均为固定，故引用的范围应绝对不变。
3.其他应用技巧
VLOOKUP函数中的第一个参数与第二个参数中的第一列的数据格式必须完全一致，若两者格式不同，则返回#N/A。在实例2中若C2中的书号“7115113742”为数值型格式，则在D2、E2、F2、G2中输入公式②~⑤后，其书名、主编、出版社、单价等均返回#N/A，因此，在实际查找引用前必须对不规范的数据进行处理，如统一转为文本格式或数字格式，确保第一个参数与第二个参数中的第一列的数据格式的一致性。
向下复制公式时，由于EXCEL默认为相对行引用，为保证引用范围不越界，第二个参数应采用行绝对引用，而例2中，因引用的范围已为工作表的最大行号，故毋需绝对引用。
第二个参数所对应的数据表可以建在主表中（如本文例1和例3），也可建在其他工作表或工作簿（如本文例2的“教材目录”表）。
三、 结束语
VLOOKUP函数的使用非常灵活，它不仅能根据代码准确提取信息，这从公式②~公式⑤中可以显而易见，只需改变参数3的数值，就能方便地添加教材、单价、出版社、主编等相关信息；而且还可将提出的数据按工程实际要求进行再次运算，如本文公式⑥前后两次使用了VLOOKUP函数就是一个很好的示例。此外，通过VLOOKUP函数还可将不同表的信息进行提取输出，解决了工作簿之间接口问题（如本文例2）。
当今的时代是信息化时代，我们的许多工作都与代码打交道，我们只有把握住VLOOKUP函数中的参数特性，才能发挥其在自动链接信息方面的准确、高效功能。
参考文献：
[1][美]John Walkenbach著，邱燕明，赵迎等译.Excel 2003公式与函数应用宝典[M].北京:电子工业出版社,2006.
[2]郑小玲等.EXCEL在信息管理中的应用[M].北京:人民邮电出版社,2004.