在EXCEL中,我们常常使用vlookup函数查找相同的数据,可以跨工作表查找,同时还能跨工作并查找,非常方便!
仍然,问题出在vlookup函数只能查找相同值的第一个值。如果我们要想查找相同值的最后一个值,就不能使用该函数了,得使用如下方法,请认真阅读,或许对您有帮助。
下面,我们使用的函数是LOOKUP函数。
如下图,在Sheet2中,我们存放两列数据,A列为操作员的编号,B列为操作员的操作日期。
现在,我们来分析问题,每个操作员,都是按照时间顺序来进行记录的,同时,每个操作员的操作编号,存在重复。
问题是,每个操作员都可能操作重复,如何能快速的指出每个操作员最后一次的操作时间?
方法非常简单,现在,我们借助Sheet1来查找Sheet2中重复操作员的最后一个时间。
在Sheet1中的A列,只要我们给出一个操作员的编号,那么,就在对应的B列中,得出该操作员编号所对应的在Sheet2中所记录下来的最后一次操作日期。
就可以得出结果,其它的使用填充工具往下拉就可以了。
知识拓展:公式说明
LOOKUP(1,0/(数据查找范围=条件),满足条件后需要返回的数据范围)
VLookUp函数是 Excel 中用于按行查找指定值的函数;它至少要有三个参数,第四个参数为可选项,用于确定匹配选项(精确匹配或近似匹配),如果省略,默认为近似匹配。VLookUp函数涉及的查找情况比较多,如果设置不正确可能返回多种错误值。
VLookUp函数的使用方法特别多,本篇将先介绍它的语法、基本使用方法和常用方法,其中基本使用方法介绍了 6 个实例,常用方法介绍了 3 个实例,实例包含多种返回错误值、在查找值中使用通配符问题(?)和星号(*)、逆向查找、近似匹配和一对多查找实例。
中文表达式:VLOOKUP(查找值, 查找区域, 返回列号, [匹配选项])
(1)、Col_Index_Num 以指定区域左边一列为第一列,例如指定区域为 B2:D8,则以 B 列为第一列,若把 Col_Index_Num 设置为 2,则返回 C 列的值。
D、如果公式缺少引号,通常返回名称错误值 #NAME?。
(4)查找数字或日期时,不能把选中区域第一列设置为文本,否则可能返回不正确的值。
(5)如果把 Range_LookUp 设置为 False(精确匹配)且 LookUp_Value 为文本,则可在 LookUp_Value 中使用通配符问号(?)和星号(*);问号表示任意一个字符,星号表示一个或多个字符;如果要查找问号或星号,需要在它们前面加转义字符 ~,例如查找问题,应该这样表示 ~?。
(6)在 Table_Array 中不能有前后空格、单双引号和非打印字符,如果有这些字符,应该用函数或替换的方法把它们去掉,否则可能返回意外值。
1、假如要查找“粉红衬衫”的“销量”。选中 A11 单元格,把公式 =VLOOKUP(A11,B2:E8,4,) 复制到 B11,按回车,返回查找结果 892;操作过程步骤,如图1所示:
A、A11 是公式 =VLOOKUP(A11,B2:E8,4,) 中的查找值,B2:E8 为查找区域,4 为返回列号(即以 B 列为第一列,返回第四列,即 E 列的值),公式省略了第四个参数 Range_LookUp,默认选择 True(近似匹配);公式的意思是:在 B2:E8 这片单元格区域中查找 A11,在 B6 找到后,返回 B6 在 E 列的对应值 E6,即返回 892。
B、如果省略第四个参数 Range_LookUp,第三个参数后既可省略逗号(,)又可加上逗号(,)。
1、双击 B11 单元格,把公式 =VLOOKUP(A11,D2:E8,2,TRUE) 复制到 B11,按回车,返回错误值 #N/A;框选 D2:D8,选择“数据”选项卡,单击“升序”图标,打开“排序提醒”窗口,选择“扩展选定区域”,单击“确定”,则每行按“价格”“升序”按序,B11 中的值随即变为 892;选中 A11 单元格,输入 36,单击一下 B11,则 B11 中的值变为 错误值 #N/A;操作过程步骤,如图2所示:
A、公式 =VLOOKUP(A11,D2:E8,2,TRUE) 把参数 Range_LookUp 设置为 True,未对“价格”排序之前,返回错误值 #N/A(A11 中的 38.8 是 D 列中的最小值),对“价格”按“升序”排序后,能返回正确的查找值 892,说明把参数 Range_LookUp 设置为近似匹配时,需要对选中区域的第一列排序才能确保返回正确的查找值。
B、即使对第一列排序,如果查找值(36)小于第一列最小值(38.8),同样会返回错误值 #N/A。
2、把参数 Range_LookUp 设置为 False(精确匹配)时,不管有没有对选中区域的第一列排序,如果没有找到值,都返回错误值 #N/A。
(四)公式缺少引号返回名称错误值 #NAME? 实例
1、双击 B11 单元格,把公式 =VLOOKUP(“黑色T恤”,B2:E8,4,FALSE) 复制到 B11,按回车,返回名称错误值 #NAME?;双击 B11,把公式中的全角双引号改为半角双引号,按回车,返回查找值 982;操作过程步骤,如图4所示:
2、全角双引号不能用于把公式的文本括起来,如果查找文本中有全角双引号,外面还要加半角双引号,例如 =VLOOKUP("“黑色T恤”",B2:E8,4,FALSE)。
(五)在查找值 LookUp_Value 中使用通配符问号(?)或星号(*)的实例
(1)在查找值中使用通配符问号(?)
1、假如查找“产品名称”以“粉红”开头且后面只有两个字的销量和查找 ? 对应的销量。双击 B11 单元格,把公式 =VLOOKUP(A11,B2:E8,4,) 复制到 B11,按回车,返回查找结果 892;双击 A11 单元格,把内容改为 ~?,单击一下 B11,B11 中的数值变为 982;操作过程步骤,如图5所示:
A、公式 =VLOOKUP(A11,B2:E8,4,) 中 A11(粉红??)表示以“粉红”开头且后面只有两个字的文本,它正好是“粉红衬衫”,B3 和 B6 的服装名称也以“粉红”开头,但“粉红”后不止两个字,所以不满足条件。
B、把 A11 中的内容改为 ~?,表示要查找问号,恰好返回 ? 对应的销量 982。Excel 中 ~ 显示为 - 是字体原因,换种字体(如宋体)即显示正常。
(2)在查找值中使用通配符星号(*)
1、假如查找“产品名称”以“粉红”开头的、以“T恤”结尾和以“白色”开头且以“衬衫”结尾的销量。双击 B10 单元格,把公式 =VLOOKUP("粉红*",B2:E8,4,) 复制到 B10,按回车,返回查找结果 329;双击 B10,把查找值 "粉红*" 改为 "*T恤",按回车,返回查找结果 638;双击 B10,把查找值 "*T恤" 改为 "白色*衬衫",按回车,返回查找结果 897;操作过程步骤,如图6所示:
公式 =VLOOKUP("粉红*",B2:E8,4,) 中 "粉红*" 表示查找以“粉红”开头的服装、*T恤" 表示查找以任意字符开头且以“T恤”结尾的服装、白色*衬衫"表示查找以“白色”开头且以“衬衫”结尾的服装。
(六)参数 Table_Array 中有前后空格的实例
A、B4 中的内容就是“绿色T恤”,但公式 =VLOOKUP(A11,B2:E8,4,) 返回错误值 #N/A,而加去掉文本前后空格的函数 Trim 后,能返回正确的值,说明 B4 中有空格。
1、假如要根据平均分查找对应学生的评定。选中 J9 单元格,输入 =a4,按回车,返回“黄月语”;双击 K9,把公式 =VLOOKUP(H4,J3:K6,2) 复制到 K9,按回车,返回“黄月语”的评定“良”;操作过程步骤,如图8所示:
A、公式 =VLOOKUP(H4,J3:K6,2) 意思是在 J3:K6(评定表)中查找“黄月语”的平均分(H4)对应的评定,由于 H4 中的数字为 86,评定表中并没有这个值,因此只能用近似匹配(即省略参数 Range_LookUp 或把它设置为 True),从而选出 80 到 89 分段的评定。
B、提示:评定表中的分数必须按“升序”排序,否则可能返回不正确的值。
A、{2,1} 是Choose函数数组形式的用于指定返回哪个值的索引号,A2:A8 和 B2:B8 是返回值,数组中的 2 代表 A2:A8 中的值,1 代表 B2:B8 中的值;执行时,首先从数组中取出 2,然后从 B2:B8 中取出 B2(即“白色纯棉T恤”)和从 A2:A8 中取出 A2(即“WS-580”),最后把这两个元素组成数组第一行的元素,即 "白色纯棉T恤","WS-580";;第二次从 B2:B8 中取出 B3(即“粉红长袖衬衫”)和从 A2:A8 中取出 A3(即“WS-560”),组成数组第二个元素 "粉红长袖衬衫","WS-560";;最后返回数组
1、假如要查找指定“部门”(销售部)的所有员工姓名。双击 F2 单元格,输入部门“销售部”,双击 A2,把公式 =(C2=$F$2)+A1 复制到 A2,按回车,返回 1,选中 A2,单击 A2 右下角的单元格填充柄,则 A3 到 A8 全用相应数字填充;双击 G2,把公式 =IFERROR(VLOOKUP(ROW(A1),A1:C$8,2,0),"") 复制到 G2,按回车,返回查找结果“林语彤”,选中 G2,把鼠标移到 G2 右下角的单元格填充柄上,按住左键,往下拖,一直拖到最后一行,则返回“销售部”的所有员工;再双击 F2,把“销售”改为“财务”,按回车,返回所有“财务部”的员工姓名;操作过程步骤,如图10所示:
A、公式 =(C2=$F$2)+A1 用于把属于同一部门的员工进行递增编号,例如属于“销售部”的三个员工被编号为 1、2、3,属于“财务部”的两个员工被编号为 2、3。
B、C2 表示对列和行都是相对引用,往下拖时,会变为 C3、C4、……;往右拖时,会变为 D2、D3、……。$F$2 表示对列和行都是绝对引用,往下或往右拖时,F2 不会变。
除以上VLookUp函数的使用方法外,VLookUp函数还可以用If函数组合多条件查找、查找重复项、与Match函数组合查找等,这些内容将在后续文章中介绍。