VLOOKUP函数在信息链接中应用技巧.docVIP

VLOOKUP函数在信息链接中应用技巧 　　 摘 要：在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）动