【引用】EXCEL函数VLOOKUP如何引用其他工作簿

【引用】EXCEL函数VLOOKUP如何引用其他工作簿

2011-02-16 22:26:57|  分类： 其它软件 |  标签：vlookup  xls  excel   |字号大中小 订阅

本文引用自秋天的麦兜《EXCEL函数VLOOKUP如何引用其他工作簿》

　　在工作及生活中，经常会遇到这样的需求：在一个数据列表中查找是否包含指定的若干数据项，并根据查找结果返回另外一项数据。例如：我们已经有下图所示一张表，其中记录了梁山108将的基本信息，文件名称为 梁山108将名单.XLS，表名为 Sheet1，存储在 D:\Test 目录下（我们叫它表1）。

　　现在，我们又拿到了一个在征方腊等战役中阵亡的梁山好汉名单（我们叫它表2），但该名单中没有各位好汉的“绰号”，让人感觉有点遗憾。如何才能方便地把表1中“绰号”信息补充到表2中呢？当然你可以查一查表1敲进去，那样我可就只好去睡觉啦！这个方法只适合几十个数据项以下，如果有成千上百就不适用啦

　　此时，本小子特别钟情于用VLOOKUP函数，为便于演示其用法，我们先把表1中的“姓名”与“绰号”两列数据复制到表2中放在D和E两列（我们只要在这两列数据中查找即可，如果需要其他信息请比照执行）。然后在B2单元格输入下述公式：

　　这里的公式 =VLOOKUP(A2,D:E,2,FALSE) 的意思是说：在D:E这两列（暂称之为搜索区域）中查找A2单元格中的值“鲍旭”，找到后把搜索区域中与“鲍旭”在同一行的第2列数值（即“丧门神”）返回并显示在B2单元格中（详细用法请参见本文后面的注解）；如果在搜索区域中未找到指定的数值（即姓名），EXCEL会返回一个错误值：#N/A。如下图红色所示的“晁盖”，因为宋江要做老大，而晁天王无论在政治还是黑心上均斗不过江哥，不仅无端被“借刀杀”，就连个座次也没排上，千古奇冤啊

　　这样，我们就基本解决了开头提出的问题。在数据量较大、为保持表格整齐或者数据不允许拷来拷去时怎么办？当然我们可以把搜索区域的信息复制到同一个工作簿中的一张新表上，但最直接的办法还是在上述公式中引用其他工作簿中的相关区域，此例为：梁山108将名单.XLS 中表名为 Sheet1 的A:B两列。现在我们把刚刚复制到表2中的D:E两列信息删掉，上述公式自然会由于找不到相关的搜索区域而报错显示为“#REF!”，只要把公式中这个位置的信息换成表1中的A:B区域就行啦。首先要打开 梁山108将名单.XLS，然后如下图所示选中公式中的错误信息（即用鼠标涂黑）：

 　　切换到 梁山108将名单.XLS（ALT+TAB或用鼠标都可以），选中A:B两列，此时公式中的错误信息即被自动替换。

 　　键入回车，该公式就修改完毕，关闭 梁山108将名单.XLS，把公式复制到所有单元格，正确的公式如下图：

 　　在此可以看到，在EXCEL公式中引用其他工作簿的格式为：'目录\[文件名.xls]表名'!区域，注意两个单引号是必须的，文件名称两边要用[ ]括起来。但这种格式的引用将随着文件所在的目录名称长度而激增，阅读及使用均不方便，出错后也不容易查找。还好，EXCEL也允许我们给这样的区域自定义一个名称，并且在公式中直接使用。如下图选择菜单“插入”-“名称”-“定义”：

 　　打开“定义名称”对话框，在引用位置中输入正确的区域表达式，在名称中输入一个好记的名称，如 List ，点击确定之后EXCEL就把该区域表达式记忆下来，并且给它一个名字 List ，之后想用到这个区域时只要输入这个名称就行啦。

 　　如下图所示，是分别使用区域名称及定义名称的方式比较，其结果完全一致。另外，对于前面我们提到过的可怜人物“晁盖”，由于不在108将中，所以找不到其绰号而出错啦。为了保持表格的美观，我们常常也需要对这种情况进行处理，以便让其不显示那些看上去别扭的错误信息。在此，小子给出了使用另外两个函数ISERR及IF来解决此类问题的方法之一。

 

　　公式中的出现的List就是我们在上一步中定义的名称，它实际指代了那个长长的区域名称：'D:\Test\[梁山108将名单.xls]Sheet1'!$A:$B。

---

　　注：VLOOKUP函数用法（摘抄自Microsoft Excel 帮助文档）：

　　在表格数组的首列查找值，并由此返回表格数组当前行中其他列的值。VLOOKUP 中的 V 表示垂直方向。当比较值位于需要查找的数据左边的一列时，可以使用 VLOOKUP，而不用 HLOOKUP。
　　=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

　　参数详解：

• Lookup_value　为需要在表格数组 （数组:用于建立可生成多个结果或可对在行和列中排列的一组参数进行运算的单个公式。数组区域共用一个公式；数组常量是用作参数的一组常量。）第一列中查找的数值。Lookup_value 可以为数值或引用。若 lookup_value 小于 table_array 第一列中的最小值，VLOOKUP 将返回错误值 #N/A。
• Table_array　为两列或多列数据。请使用对区域的引用或区域名称。table_array 第一列中的值是由 lookup_value 搜索的值。这些值可以是文本、数字或逻辑值。不区分大小写。
• Col_index_num　为 table_array 中待返回的匹配值的列序号。Col_index_num 为 1 时，返回 table_array 第一列中的数值；Col_index_num 为 2，返回 table_array 第二列中的数值，以此类推。如果 col_index_num ：
  　　小于 1，VLOOKUP 返回错误值 #VALUE!。
  　　大于 table_array 的列数，VLOOKUP 返回错误值 #REF!。
• Range_lookup　为逻辑值，指定希望 VLOOKUP 查找精确的匹配值还是近似匹配值：
  如果为 TRUE 或省略，则返回精确匹配值或近似匹配值。也就是说，如果找不到精确匹配值，则返回小于 lookup_value 的最大数值。table_array 第一列中的值必须以升序排序；否则 VLOOKUP 可能无法返回正确的值。可以选择“数据”菜单上的“排序”命令，再选择“递增”，将这些值按升序排序。有关详细信息，请参阅默认排序次序。
  如果为 FALSE，VLOOKUP 将只寻找精确匹配值。在此情况下，table_array 第一列的值不需要排序。如果 table_array 第一列中有两个或多个值与 lookup_value 匹配，则使用第一个找到的值。如果找不到精确匹配值，则返回错误值 #N/A。