EXCEL中vlookup函数的使用课件.ppt

1、Excel与数据处理与数据处理 第第2版版本章学习目标本章学习目标1、掌握查询、掌握查询大工作表大工作表数据的方法数据的方法2、用、用lookup函数函数查询数据的方法查询数据的方法3、用、用Vlookup函数函数查询数据的方法查询数据的方法4、index和和match相结合查询数据的方法相结合查询数据的方法5、用、用D函数函数查询数据的方法查询数据的方法6、文本查询文本查询的方法的方法7、indirect和和名字名字相结合查询数据的方法相结合查询数据的方法8、用、用choose查询数据的方法查询数据的方法9.1 查找大工作表的特定数据行查找大工作表的特定数据行 1、概述、概述n当工作表数据行

2、较多时，要查看其中的某行数当工作表数据行较多时，要查看其中的某行数据并非易事。利用据并非易事。利用Excel提供的查找菜单或记提供的查找菜单或记录单功能就能够很快定位到特定数据行，实现录单功能就能够很快定位到特定数据行，实现高效的查找。高效的查找。n本节案例本节案例n【例例9.1】某单位有某单位有600多名职工，其医疗档多名职工，其医疗档案表如所示，现要从中查看李大友的医疗费用案表如所示，现要从中查看李大友的医疗费用情况。情况。9.1 查找大工作表的特定数据行查找大工作表的特定数据行 9.1 查找大工作表的特定数据行查找大工作表的特定数据行 n2、用菜单命令进行数据查找n选择选择“编辑编辑”|

3、“查找查找”菜单命令，菜单命令，Excel会弹出图（会弹出图（a）n在在“查找查找”标签对话框的标签对话框的“查找内容查找内容”中输入要查找的内中输入要查找的内容，然后单击容，然后单击“查找全部查找全部”或或“查找下一个查找下一个”按钮按钮 9.1 查找大工作表的特定数据行查找大工作表的特定数据行 n2、利用记录单查找、利用记录单查找n1）选择）选择“数据数据”|“记录单记录单”菜单命令菜单命令n2）单击记录单对话框中的）单击记录单对话框中的“条件条件”按钮，并在弹出的对按钮，并在弹出的对话框中填写查询条件。话框中填写查询条件。9.2 查找及引用函数查找及引用函数n1、概述、概述n查找引用函数

4、能通过单元格引用地址、行、列查找引用函数能通过单元格引用地址、行、列对工作表的单元格进行访问，还能够从单元格对工作表的单元格进行访问，还能够从单元格的引用地址中求出单元格所在的行或列，进而的引用地址中求出单元格所在的行或列，进而查获更多的信息。当需要从一个工作表查询特查获更多的信息。当需要从一个工作表查询特定的值、单元格内容、格式或选择单元格区域定的值、单元格内容、格式或选择单元格区域时，这类函数特别有用。时，这类函数特别有用。n在大数据表、不同工作薄或工作表之间查询数在大数据表、不同工作薄或工作表之间查询数据时，这类函数很有用。据时，这类函数很有用。n有时，将查询结果用于公式计算，能够事半功

5、有时，将查询结果用于公式计算，能够事半功倍。倍。查询与引用函数查询与引用函数nCOLUMN(reference)n返回给定引用的列标。返回给定引用的列标。nReference：需要得到其列标的单元格或单元格区：需要得到其列标的单元格或单元格区域。域。n如果省略如果省略 reference，则假定为是对函数，则假定为是对函数COLUMN 所所在单元格的引用。在单元格的引用。n如果如果 reference 为一个单元格区域，并且函数为一个单元格区域，并且函数 COLUMN 作为水平数组输入，则函数作为水平数组输入，则函数 COLUMN 将将 reference 中的列标以水平数组的形式返回。中的列

6、标以水平数组的形式返回。n注意：注意：Reference 不能引用多个区域。不能引用多个区域。查询与引用函数查询与引用函数nROW(reference)n返回给定引用的行号。返回给定引用的行号。nReference为需要得到其行号的单元格或单元格区为需要得到其行号的单元格或单元格区域。域。n如果省略如果省略 reference，则假定是对函数，则假定是对函数 ROW 所在单元所在单元格的引用。格的引用。n如果如果 reference 为一个单元格区域，并且函数为一个单元格区域，并且函数 ROW 作作为垂直数组输入，则函数为垂直数组输入，则函数 ROW 将将 reference 的行号以的行号以

7、垂直数组的形式返回。垂直数组的形式返回。n注意：注意：Reference 不能对多个区域进行引用。不能对多个区域进行引用。查询与引用函数查询与引用函数nAREAS(reference)n返回引用中包含的区域个数。返回引用中包含的区域个数。nReference为对某一单元格或单元格区域的引用，也可以为对某一单元格或单元格区域的引用，也可以引用多个区域。如果需要将几个引用指定为一个参数，引用多个区域。如果需要将几个引用指定为一个参数，则必须用括号括起来。则必须用括号括起来。nCOLUMNS(array)n返回数组或引用的列数。返回数组或引用的列数。nArray为需要得到其列数的数组、数组公式或对单

8、元格区为需要得到其列数的数组、数组公式或对单元格区域的引用。域的引用。nROWS(array)n返回引用或数组的行数。返回引用或数组的行数。9.2 查找及引用函数查找及引用函数1、用用Lookup函数进行表查找函数进行表查找 Lookup在一个大表中找出特定数据，并在其它工作在一个大表中找出特定数据，并在其它工作表中引用查找结果，在工作中应用较广。表中引用查找结果，在工作中应用较广。n功能功能 n从给定的向量（单行或单列单元格区域）或数组中查从给定的向量（单行或单列单元格区域）或数组中查询出需要的数值。询出需要的数值。n格式格式nLookup(x,r1,r2)n其中：其中：x是要查找的内容，它

9、可以是数字、文本、逻辑是要查找的内容，它可以是数字、文本、逻辑值或包含数值的名称或引用。值或包含数值的名称或引用。nr1、r2都是只包含一行或一列的单元格区域，其值可都是只包含一行或一列的单元格区域，其值可以是文本、数字或逻辑值。以是文本、数字或逻辑值。r2的大小必须与的大小必须与r1相同。相同。nLookup函数在函数在r1所在的行或列中查找值为所在的行或列中查找值为x的单元格，的单元格，找到后返回找到后返回r2中与中与r1同行或同列的单元格中的值。同行或同列的单元格中的值。9.2 查找及引用函数查找及引用函数n案例案例n【例例9.2】某蔬菜供应商在一个工作表中保存蔬菜的单价某蔬菜供应商在一

10、个工作表中保存蔬菜的单价和出产地，如图（和出产地，如图（a）所示。在另一工作表中保存销售记）所示。在另一工作表中保存销售记录，如图（录，如图（b）所示。在图（）所示。在图（b）中，蔬菜名和数量是实）中，蔬菜名和数量是实际输入的数据，产地和单价需要根据产品名从图（际输入的数据，产地和单价需要根据产品名从图（a）所）所示的蔬菜单价表中查询输入示的蔬菜单价表中查询输入 9.2 查找及引用函数查找及引用函数n案例解决方法案例解决方法n（1）建立图（）建立图（a）所示的蔬菜单价表，并按升序对该工）所示的蔬菜单价表，并按升序对该工作表进行排序，排序主关键字为作表进行排序，排序主关键字为“蔬菜蔬菜”。n（2

11、）输入图（）输入图（b）的）的A列数据，和第列数据，和第1、2行的标题。在行的标题。在B3中输入下述查找公式，然后向下填充复制该公式，就中输入下述查找公式，然后向下填充复制该公式，就可找出各蔬菜的产地。可找出各蔬菜的产地。n=LOOKUP(A3,蔬菜单价表蔬菜单价表!$A$2:$A$11,蔬菜蔬菜单价表单价表!$C$2:$C$11)n查找蔬菜单价的方法与此完全类似，只需要在图查找蔬菜单价的方法与此完全类似，只需要在图（b）的）的D3单元格中输入下述公式，然后向下复单元格中输入下述公式，然后向下复制该公式就行了。制该公式就行了。n=LOOKUP(A3,蔬菜单价表蔬菜单价表!$A$2:$A$11,

12、蔬菜蔬菜单价表单价表!$B$2:$B$11)9.2 查找及引用函数查找及引用函数n注意：注意：n r1中的内容必须按升序排序，查找的字符中的内容必须按升序排序，查找的字符文本不区分大小写。否则，文本不区分大小写。否则，Lookup函数不能函数不能返回正确的结果。返回正确的结果。n如果如果Lookup函数找不到函数找不到x，则查找，则查找r1中小于中小于或等于或等于x的最大数值。如果的最大数值。如果x小于小于r1中的最小值，中的最小值，Lookup函数返回错误值函数返回错误值“#N/A”。9.2 查找及引用函数查找及引用函数2、用、用Vlookup函数进行表查找函数进行表查找n功能功能nVloo

13、kup按列查找的方式从指定数据表区域的最左列按列查找的方式从指定数据表区域的最左列查找特定数据，它能够返回查找区域中与找到单元格查找特定数据，它能够返回查找区域中与找到单元格位于相同行不同列的单元格内容位于相同行不同列的单元格内容。n格式格式nVlookup(x,table,n,f)n其中，其中，x是要查找的值；是要查找的值；table是一个单元格区域；是一个单元格区域；nn中中table区域中要返回的数据所在列的序号。区域中要返回的数据所在列的序号。n=1时，时，返回返回 table 第第1列中的数值；列中的数值；n=2时，返回时，返回 table 第第2列中的数值；以此类推。列中的数值；以

14、此类推。nf是一个逻辑值，表示查找的方式。是一个逻辑值，表示查找的方式。当其为当其为true（或（或1）时，表示模糊查找；当它为时，表示模糊查找；当它为false（或（或0）时，表示精）时，表示精确查找。确查找。9.2 查找及引用函数查找及引用函数n说明：说明：nVlookup函数在函数在table区域的第区域的第1列中查找值为列中查找值为x的数的数值，如果找到，就返回与找到数据同行第值，如果找到，就返回与找到数据同行第n列单元格中列单元格中的数据。当的数据。当f为为true时，时，table的第的第1列数据必须按升序列数据必须按升序排列，否则找不到正确的结果；当排列，否则找不到正确的结果；当

15、f为为false时，时，table的第的第1列数据不需要排序。列数据不需要排序。n注意注意n如果如果Vlookup函数找不到函数找不到x，且，且f=true，则返回小，则返回小于等于于等于x的最大值。的最大值。n如果如果x小于小于table第第1列中的最小值，列中的最小值，Vlookup函数函数返回错误值返回错误值“#N/A”。n如果如果Vlookup函数找不到函数找不到x且且f=FALSE，Vlookup函数返回错误值函数返回错误值“#N/A”。9.2 查找及引用函数查找及引用函数n（1）用）用Vlookup进行模糊查找进行模糊查找n模糊查找也就是常说的近似查找，常用于数据模糊查找也就是常说

16、的近似查找，常用于数据转换或数据对照表中的数据查找。转换或数据对照表中的数据查找。n案例案例n【例例9.3】假设所得税的税率如图的假设所得税的税率如图的A1:B10区域所区域所示。其中的含义是：示。其中的含义是：n0500的税率为的税率为0%，n5001000的税率为的税率为1%，n10001500的税率为的税率为3%n，4000以上的税率为以上的税率为20%。n某公司的职工收入数据如图的某公司的职工收入数据如图的D1:J11所示，现在计所示，现在计算每位职工应缴的所得税。算每位职工应缴的所得税。9.2 查找及引用函数查找及引用函数nI列的所得税率的计算方法如下。列的所得税率的计算方法如下。n

17、在在I3单元格中输入下述公式，然后向下复制此公式，单元格中输入下述公式，然后向下复制此公式，就能够计算出每位职工的所得税率。就能够计算出每位职工的所得税率。n=Vlookup(H3,$A$3:$B$10,2,1)9.2 查找及引用函数查找及引用函数n（2）用Vlookup进行精确查找n概述n精确查找就是指查找数据完全匹配的精确查找就是指查找数据完全匹配的查找，查找，Vlookup函数具有此项功能。函数具有此项功能。在大表中查找特定数据，或查找不同在大表中查找特定数据，或查找不同工作表中的数据，特别是工作表数据工作表中的数据，特别是工作表数据较多，较多，Vlookup函数显得非常有效函数显得非常

18、有效9.2 查找及引用函数查找及引用函数n案例案例n【例例9.4】某校某专业期末考试的数据库成绩表如图的某校某专业期末考试的数据库成绩表如图的A:H列所示。由于人数较多，要查看某个同学的成绩非常列所示。由于人数较多，要查看某个同学的成绩非常困难。希望能按学号进行查找，即在困难。希望能按学号进行查找，即在K5输入某个学号后，输入某个学号后，就能自动显示出该学号所对应的姓名和各种成绩，如图的就能自动显示出该学号所对应的姓名和各种成绩，如图的J4:M16所示所示 9.2 查找及引用函数查找及引用函数n案例解决方法案例解决方法n（1）在）在M5中输入公式：中输入公式：=VLOOKUP(K5,A5:H2

19、27,2,0)n（2）在）在L6中输入公式：中输入公式：=VLOOKUP(K5,A5:H227,3,0)n（3）在）在L7中输入公式：中输入公式：=VLOOKUP(K5,A5:H227,4,0)n（4）在）在L9中输入公式：中输入公式：=VLOOKUP(K5,A5:H227,5,0)n（5）在）在L11中输入公式：中输入公式：=VLOOKUP(K5,A5:H227,6,0)n（6）在）在L13中输入公式：中输入公式：=VLOOKUP(K5,A5:H227,7,0)n（7）在）在L15中输入公式：中输入公式：=VLOOKUP(K5,A5:H227,8,0)9.2 查找及引用函数查找及引用函数n案

20、例案例2n【例例9.5】某电话公司的电话收费系统进行了某电话公司的电话收费系统进行了系统升级，图（系统升级，图（a）是系统升级前的电话号码）是系统升级前的电话号码和收费账号对照表，图（和收费账号对照表，图（b）的是升级后的收）的是升级后的收费表。升级后系统新加了一些号码，新加的号费表。升级后系统新加了一些号码，新加的号码要重新编制账号，但原有号码的账号则需要码要重新编制账号，但原有号码的账号则需要从旧系统中查询。也就是说，图（从旧系统中查询。也就是说，图（b）中的绝）中的绝大部分号码的账号（大部分号码的账号（B列数据）要从图（列数据）要从图（a）的）的B列查询。列查询。9.2 查找及引用函数查找及引用函数n在图的在图的B3单元格输入下述查找公式，然后向下复制此公单元格输入下述查找公式，然后向下复制此公式，就能查找到所有旧号码的账号。式，就能查找到所有旧号码的账号。n=VLOOKUP(A3,旧账号旧账号!A$2:$B$6884,2,0)