《vlookup函数》PPT课件

Excel与数据处理第2版本章学习目标1、掌握查询大工作表数据的方法2、用lookup函数查询数据的方法3、用Vlookup函数查询数据的方法4、index和match相结合查询数据的方法5、用D函数查询数据的方法6、文本查询的方法7、indirect和名字相结合查询数据的方法8、用choose查询数据的方法9.1查找大工作表的特定数据行1、概述当工作表数据行较多时，要查看其中的某行数据并非易事。利用Excel提供的查找菜单或记录单功能就能够很快定位到特定数据行，实现高效的查找。本节案例【例9.1】某单位有600多名职工，其医疗档案表如所示，现要从中查看李大友的医疗费用情况。9.1查找大工作表的特定数据行9.1查找大工作表的特定数据行2、用菜单命令进行数据查找选择“编辑”|“查找”菜单命令，Excel会弹出图（a）在“查找”标签对话框的“查找内容”中输入要查找的内容，然后单击“查找全部”或“查找下一个”按钮9.1查找大工作表的特定数据行2、利用记录单查找1）选择“数据”|“记录单”菜单命令2）单击记录单对话框中的“条件”按钮，并在弹出的对话框中填写查询条件。9.2查找及引用函数1、概述查找引用函数能通过单元格引用地址、行、列对工作表的单元格进行访问，还能够从单元格的引用地址中求出单元格所在的行或列，进而查获更多的信息。当需要从一个工作表查询特定的值、单元格内容、格式或选择单元格区域时，这类函数特别有用。在大数据表、不同工作薄或工作表之间查询数据时，这类函数很有用。有时，将查询结果用于公式计算，能够事半功倍。查询与引用函数COLUMN(reference)返回给定引用的列标。Reference：需要得到其列标的单元格或单元格区域。如果省略reference，则假定为是对函数COLUMN所在单元格的引用。如果reference为一个单元格区域，并且函数COLUMN作为水平数组输入，则函数COLUMN将reference中的列标以水平数组的形式返回。注意：Reference不能引用多个区域。查询与引用函数ROW(reference)返回给定引用的行号。Reference为需要得到其行号的单元格或单元格区域。如果省略reference，则假定是对函数ROW所在单元格的引用。如果reference为一个单元格区域，并且函数ROW作为垂直数组输入，则函数ROW将reference的行号以垂直数组的形式返回。注意：Reference不能对多个区域进行引用。查询与引用函数AREAS(reference)返回引用中包含的区域个数。Reference为对某一单元格或单元格区域的引用，也可以引用多个区域。如果需要将几个引用指定为一个参数，则必须用括号括起来。COLUMNS(array)返回数组或引用的列数。Array为需要得到其列数的数组、数组公式或对单元格区域的引用。ROWS(array)返回引用或数组的行数。9.2查找及引用函数1、用Lookup函数进行表查找Lookup在一个大表中找出特定数据，并在其它工作表中引用查找结果，在工作中应用较广。功能从给定的向量（单行或单列单元格区域）或数组中查询出需要的数值。格式Lookup(x,r1,r2)其中：x是要查找的内容，它可以是数字、文本、逻辑值或包含数值的名称或引用。r1、r2都是只包含一行或一列的单元格区域，其值可以是文本、数字或逻辑值。r2的大小必须与r1相同。Lookup函数在r1所在的行或列中查找值为x的单元格，找到后返回r2中与r1同行或同列的单元格中的值。9.2查找及引用函数案例【例9.2】某蔬菜供应商在一个工作表中保存蔬菜的单价和出产地，如图（a）所示。在另一工作表中保存销售记录，如图（b）所示。在图（b）中，蔬菜名和数量是实际输入的数据，产地和单价需要根据产品名从图（a）所示的蔬菜单价表中查询输入9.2查找及引用函数案例解决方法（1）建立图（a）所示的蔬菜单价表，并按升序对该工作表进行排序，排序主关键字为“蔬菜”。（2）输入图（b）的A列数据，和第1、2行的标题。在B3中输入下述查找公式，然后向下填充复制该公式，就可找出各蔬菜的产地。=LOOKUP(A3,蔬菜单价表!$A$2:$A$11,蔬菜单价表!$C$2:$C$11)查找蔬菜单价的方法与此完全类似，只需要在图（b）的D3单元格中输入下述公式，然后向下复制该公式就行了。=LOOKUP(A3,蔬菜单价表!$A$2:$A$11,蔬菜单价表!$B$2:$B$11)9.2查找及引用函数注意：①r1中的内容必须按升序排序，查找的字符文本不区分大小写。否则，Lookup函数不能返回正确的结果。②如果Lookup函数找不到x，则查找r1中小于或等于x的最大数值。如果x小于r1中的最小值，Lookup函数返回错误值“#N/A”。9.2查找及引用函数2、用Vlookup函数进行表查找功能Vlookup按列查找的方式从指定数据表区域的最左列查找特定数据，它能够返回查找区域中与找到单元格位于相同行不同列的单元格内容。格式Vlookup(x,table,n,f)其中，x是要查找的值；table是一个单元格区域；n中table区域中要返回的数据所在列的序号。n=1时，返回table第1列中的数值；n=2时，返回table第2列中的数值；以此类推。f是一个逻辑值，表示查找的方式。当其为true（或1）时，表示模糊查找；当它为false（或0）时，表示精确查找。9.2查找及引用函数说明：Vlookup函数在table区域的第1列中查找值为x的数值，如果找到，就返回与找到数据同行第n列单元格中的数据。当f为true时，table的第1列数据必须按升序排列，否则找不到正确的结果；当f为false时，table的第1列数据不需要排序。注意①如果Vlookup函数找不到x，且f=true，则返回小于等于x的最大值。②如果x小于table第1列中的最小值，Vlookup函数返回错误值“#N/A”。③如果Vlookup函数找不到x且f=FALSE，Vlookup函数返回错误值“#N/A”。9.2查找及引用函数（1）用Vlookup进行模糊查找模糊查找也就是常说的近似查找，常用于数据转换或数据对照表中的数据查找。案例【例9.3】假设所得税的税率如图的A1:B10区域所示。其中的含义是：0~500的税率为0%，500~1000的税率为1%，1000~1500的税率为3%……，4000以上的税率为20%。某公司的职工收入数据如图的D1:J11所示，现在计算每位职工应缴的所得税。9.2查找及引用函数I列的所得税率的计算方法如下。在I3单元格中输入下述公式，然后向下复制此公式，就能够计算出每位职工的所得税率。=Vlookup(H3,$A$3:$B$10,2,1)9.2查找及引用函数（2）用Vlookup进行精确查找概述精确查找就是指查找数据完全匹配的查找，Vlookup函数具有此项功能。在大表中查找特定数据，或查找不同工作表中的数据，特别是工作表数据较多，Vlookup函数显得非常有效9.2查找及引用函数案例【例9.4】某校某专业期末考试的数据库成绩表如图的A:H列所示。由于人数较多，要查看某个同学的成绩非常困难。希望能按学号进行查找，即在K5输入某个学号后，就能自动显示出该学号所对应的姓名和各种成绩，如图的J4:M16所示9.2查找及引用函数案例解决方法（1）在M5中输入公式：=VLOOKUP(K5,A5:H227,2,0)（2）在L6中输入公式：=VLOOKUP(K5,A5:H227,3,0)（3）在L7中输入公式：=VLOOKUP(K5,A5:H227,4,0)（4）在L9中输入公式：=VLOOKUP(K5,A5:H227,5,0)（5）在L11中输入公式：=VLOOKUP(K5,A5:H227,6,0)（6）在L13中输入公式：=VLOOKUP(K5,A5:H227,7,0)（7）在L15中输入公式：=VLOOKUP(K5,A5:H227,8,0)9.2查找及引用函数案例2【例9.5】某电话公司的电话收费系统进行了系统升级，图（a）是系统升级前的电话号码和收费账号对照表，图（b）的是升级后的收费表。升级后系统新加了一些号码，新加的号码要重新编制账号，但原有号码的账号则需要从旧系统中查询。也就是说，图（b）中的绝大部分号码的账号（B列数据）要从图（a）的B列查询。9.2查找及引用函数在图的B3单元格输入下述查找公式，然后向下复制此公式，就能查找到所有旧号码的账号。=VLOOKUP(A3,旧账号!A$2:$B$6884,2,0)9.2查找及引用函数注意在Excel中还有一个常用的查找函数Hlookup，其用法与Vlookup函数完全相同。它按行方式进行数据查找，而Vlookup函数按列方式进行数据查找。因时间所限，这里不作介绍，同学们可从Excel的帮助信息中查找该函数的用法。