关于EXCEL中VLOOKUP函数的用法.ppt

1、1,行政部/计算机中心 | 威望于品质 孚信于用户 2020/8/6 | 版权所有,未经书面授权不得转载,关于EXCEL中VLOOKUP函数的用法2020年8月6日,2,VLOOKUP函数简介,“Lookup”汉语里是“查找”的意思 在Excel中与“Lookup”相关的函数有三个：VLOOKUP、HLOOKUP和LOOKUP。 VLOOKUP 中的 V 表示垂直方向。当查找值位于需查找的数据区域左边的一列时，可以使用 VLOOKUP，而不用 HLOOKUP。 VLOOKUP功能：在表格区域的首列查找指定的数值，并由此返回表格区域中该数值所在行中指定列处的数值。 表格区域的“首列”，就是这个区

2、域的第一纵列，此列右边依次为第2列、3列。假定某表格区域为B2:E10，那么，B2:B10为第1列、C2:C10为第2列。,3,语法,VLOOKUP(Lookup_Value,Table_Array, Col_Index_Num,Range_Lookup),VLOOKUP （查找值，区域，列序号，逻辑值）,4,参数详解,VLOOKUP（查找值，区域，列序号，逻辑值） 四个参数详解： “查找值”：为需要在区域第一列中查找的数值，它可以是数值、引用或文字符串。 “区域”：表格中的一个区域，可以为两列或多列数据，如“B2:E10”，也可以使用对区域名称的引用。 特别要注意的是区域第一列中的值必须是由

3、“查找值”搜索的值。这些值可以是文本、数字或逻辑值。不区分大小写。,5,参数详解,“列序号”：即希望区域中待返回的匹配值的列序号，为1时，返回第一列中的数值，为2时，返回第二列中的数值，以此类推；若列序号小于1，函数VLOOKUP 返回错误值 #VALUE!；如果大于区域的列数，函数VLOOKUP返回错误值 #REF!。 “逻辑值”：为TRUE（值为1）或FALSE（值为0）。它指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。 如果为 TRUE 或省略，则返回近似匹配值。也就是说，如果找不到精确匹配值，则返回小于“查找值”的最大数值，如果“查找值”小于“区域”第一列中的最小数值，返回错

4、误值 #N/A。 如果“逻辑值”为FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。如果“查找值”为文本时，“逻辑值”一般应为 FALSE 。,6,VLOOKUP使用举例,例：VLOOKUP(A2,Sheet2!$A1:$B10,2,FALSE) 说明：在工作表Sheet2的$A1:$区域中查找当前表中A2中的内容，如果查找到，就返回表SHEET2中B2中的内容，因为B2位区域中的第二列，所以VLOOKUP的第三个参数使用2，表示如果满足条件，就返回查询区域的第二列，最后的参数FALSE表示精确查找。 Office官网示例.xls 物流处商品销售装机预测工

5、作表.xls,7,步骤一：整理数据格式,1、汇总订货编号 新建工作表Sheet1，将各工作表中的订货编号值粘贴至Sheet1表第1列 处理“装机情况” 表时，需在订货编号前加00，以304开头的订货编号除外。,在订货编号后插入1列， 在B2单元格中输入公式=IF(LEFT(A2,3)=“304”,A2,“00”&A2) ，整列复制公式。选中整列复制后，在Sheet1表中编辑选择性粘贴选择“数值”确定 。,8,步骤一：整理数据格式,1、汇总订货编号 处理“预测”表，有的订货编号中含有空格字符，有的订货编号前需加00，304开头的订货编号前不需加00。 操作：先去除“订货编号”字符中的空格(见下图

6、)，然后在订货编号后插入1列“订货编号1”，在D2单元格中输入公式：=IF(LEFT(C2,2)=“00”,C2,IF(LEFT(C2,3)=“304”,C2,“00”&C2)，整列复制公式。选中整列复制后，在Sheet1表中编辑选择性粘贴选择“数值”确定。 排序Sheet1表中的订货编号。,敲一下空格键,此处不填,9,步骤一：整理数据格式,1、汇总订货编号 数据筛选高级筛选，勾选“选择不重复的记录” 先在视图工具栏自定义中设置工具栏中出现“选定可见单元格”按钮，全部选定筛选后的数据点击“选定可见单元格” 复制选择性粘贴到商品销售表中第1列。,10,步骤一：整理数据格式,2、汇总“预测”表中的

7、数据 “预测”表中的数据先按处理后的订货编号1进行排序 数据分类汇总，分类字段选择“订货编号1”，汇总方式选择“求和”，选定汇总项。 点击“2”(见右下图)，隐藏明细数据行。,11,步骤一：整理数据格式,2、汇总“预测”表中的数据 选定“预测”表中筛选后的数据点击“选定可见单元格”复制新建工作表“预测汇总”选择性粘贴 选择“数值”确定。,12,步骤一：整理数据格式,2、汇总“预测”表中的数据 使用查找和替换去除订货编号1中的空格及“汇总”。,13,步骤二：使用VLOOKUP拼接表格,1、将其它工作表的表头复制到“商品销售”表第一行。,14,步骤二：使用VLOOKUP拼接表格,2、拼接“5月库存

8、”表数据 “商品销售”工作表B2直接输入公式=VLOOKUP($A2,5月库存!$B:$G,2,0) ，返回错误值 #N/A。前面讲过，如果函数 VLOOKUP 找不到“查找值” 且“逻辑值”为 FALSE，函数 VLOOKUP 返回错误值 #N/A。,15,步骤二：使用VLOOKUP拼接表格,如何输入方便地输入B2公式：=VLOOKUP($A2,5月库存!$B:$G,2,0) ，插入函数，搜索函数中输入vlookup，确定。弹出的框中分别选择或输入函数的四个参数。,16,步骤二：使用VLOOKUP拼接表格,ISNA函数：检查 #N/A 是否为错误值 #N/A (TRUE) 。 IF函数：根据

9、ISNA的结果判断，如果VLOOKUP 返回错误值 #N/A ，则ISNA为TRUE，返回空值，否则返回VLOOKUP查找结果。 B2单元格输入公式 =IF(ISNA(VLOOKUP($A2,5月库存!$B:$G,2,0),“”, VLOOKUP($A2, 5月库存!$B:$G,2,0)。,17,步骤二：使用VLOOKUP拼接表格,3、$A2的$符号是绝对列，横向复制时$后的列序号不变，纵向复制时行序号递增。 4、横向复制后需修改公式中的列序号，一般连续的数据区域，列序号递增。,18,步骤二：使用VLOOKUP拼接表格,5、拼接“商品收发”表数据： “商品收发”表数据有很多列，在VLOOKUP

10、公式的列序号参数中使用COLUMN函数，横向复制公式后列序号自动递增。,19,步骤二：使用VLOOKUP拼接表格,6、使用区域名称拼接“装机情况”表数据： 选定“装机情况”表B1:M1672，插入名称定义，输入名称“装机”。在VLOOKUP公式的区域参数中使用已经定义的名称“装机”来替代“装机情况”表数据区域。,20,几点说明,在“区域”第1列中搜索文本值时，请确保 “区域”第1列中的数据没有前导空格、尾随空格、不一致的直引号（ 或 “）、弯引号（或“）或非打印字符。在上述情况下，VLOOKUP 可能返回不正确或意外的值。 在搜索数字或日期值时，请确保 “区域”第1列中的数据没有保存为文本值。 如果“逻辑值”为 FALSE