Excelchapter9.ppt

《Excelchapter9.ppt》由会员分享，可在线阅读，更多相关《Excelchapter9.ppt（42页珍藏版）》请在三一办公上搜索。

1、第9章 数据查找,Excel与数据处理 第2版,本章学习目标,1、掌握查询大工作表数据的方法2、用lookup函数查询数据的方法3、用Vlookup函数查询数据的方法4、index和match相结合查询数据的方法5、用D函数查询数据的方法6、文本查询的方法7、indirect和名字相结合查询数据的方法8、用choose查询数据的方法,9.1 查找大工作表的特定数据行,1、概述当工作表数据行较多时，要查看其中的某行数据并非易事。利用Excel提供的查找菜单或记录单功能就能够很快定位到特定数据行，实现高效的查找。本节案例【例9.1】某单位有600多名职工，其医疗档案表如所示，现要从中查看李大友的医

2、疗费用情况。,9.1 查找大工作表的特定数据行,9.1 查找大工作表的特定数据行,2、用菜单命令进行数据查找（1）选择“编辑”|“查找”菜单命令，Excel会弹出图（a）（2）在“查找”标签对话框的“查找内容”中输入要查找的内容，然后单击“查找全部”或“查找下一个”按钮,9.1 查找大工作表的特定数据行,2、利用记录单查找1）选择“数据”|“记录单”菜单命令2）单击记录单对话框中的“条件”按钮，并在弹出的对话框中填写查询条件。,9.2 查找及引用函数,1、概述查找引用函数能通过单元格引用地址、行、列对工作表的单元格进行访问，还能够从单元格的引用地址中求出单元格所在的行或列，进而查获更多的信息。

3、当需要从一个工作表查询特定的值、单元格内容、格式或选择单元格区域时，这类函数特别有用。在大数据表、不同工作薄或工作表之间查询数据时，这类函数很有用。有时，将查询结果用于公式计算，能够事半功倍。,9.2 查找及引用函数,1、用Lookup函数进行表查找Lookup在一个大表中找出特定数据，并在其它工作表中引用查找结果，在工作中应用较广。功能 从给定的向量（单行或单列单元格区域）或数组中查询出需要的数值。格式Lookup(x,r1,r2)其中：x是要查找的内容，它可以是数字、文本、逻辑值或包含数值的名称或引用。r1、r2都是只包含一行或一列的单元格区域，其值可以是文本、数字或逻辑值。r2的大小必须

4、与r1相同。Lookup函数在r1所在的行或列中查找值为x的单元格，找到后返回r2中与r1同行或同列的单元格中的值。,9.2 查找及引用函数,案例【例9.2】某蔬菜供应商在一个工作表中保存蔬菜的单价和出产地，如图（a）所示。在另一工作表中保存销售记录，如图（b）所示。在图（b）中，蔬菜名和数量是实际输入的数据，产地和单价需要根据产品名从图（a）所示的蔬菜单价表中查询输入,9.2 查找及引用函数,案例解决方法（1）建立图（a）所示的蔬菜单价表，并按升序对该工作表进行排序，排序主关键字为“蔬菜”。（2）输入图（b）的A列数据，和第1、2行的标题。在B3中输入下述查找公式，然后向下填充复制该公式，就

5、可找出各蔬菜的产地。=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、用Vl

6、ookup函数进行表查找功能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的数值

7、，如果找到，就返回与找到数据同行第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】假设所得税的税率如图

8、的A1:B10区域所示。其中的含义是：0500的税率为0%，5001000的税率为1%，10001500的税率为3%，4000以上的税率为20%。某公司的职工收入数据如图的D1:J11所示，现在计算每位职工应缴的所得税。,9.2 查找及引用函数,I列的所得税率的计算方法如下。在I3单元格中输入下述公式，然后向下复制此公式，就能够计算出每位职工的所得税率。=Vlookup(H3,$A$3:$B$10,2,1),9.2 查找及引用函数,（2）用Vlookup进行精确查找概述精确查找就是指查找数据完全匹配的查找，Vlookup函数具有此项功能。在大表中查找特定数据，或查找不同工作表中的数据，特别是工

9、作表数据较多，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

10、: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 查找及引用函数,在图的

11、B3单元格输入下述查找公式，然后向下复制此公式，就能查找到所有旧号码的账号。=VLOOKUP(A3,旧账号!A$2:$B$6884,2,0),9.2 查找及引用函数,注意在Excel中还有一个常用的查找函数Hlookup，其用法与Vlookup函数完全相同。它按行方式进行数据查找，而Vlookup函数按列方式进行数据查找。因时间所限，这里不作介绍，同学们可从Excel的帮助信息中查找该函数的用法。,9.2 查找及引用函数,3、用match和index函数构造灵活的查询 概述Match函数提供了比lookup（或Vlookup、Hlookup）函数更多的灵活性，它可以在工作表的一行（或一列）中进

12、行数据查找，并返回数据在行（或列）中的位置。如果需要找出数据在某行（或某列）的位置，就应该使用Match函数而不是Lookup函数 在多数情况下，Match函数的结果并不是所需要的最终答案，而是作为lookup（Vlookup，Hlookup）的第3个参数，或作为Index函数的参数,9.2 查找及引用函数,Match格式Match(x，r，f)其中x是要查找的数值，r可以是一个数组常量，或某列（或行）连续的单元格区域，其中可能包含有要查找的x。f用于指定match的查找方式，它可以是-1，0或1，表9-1给出了这几个取值的含义。功能Match(x，r，f)表示的意思是：在数组或连续的单元格区

13、域r中查找x，并返回x在r中的位置编号。当f为0是，match进行精确查找，当f为1（或-1）时，match进行模糊查找。,9.2 查找及引用函数,Match查找的方式,9.2 查找及引用函数,Index函数格式Index(Area，r，c，n)其中，Area是1个或多个单元格区域；r是某行的行序号，c是某列的列序号，该函数返回指定的行与列交叉处的单元格引用。如果r等于0，则返回整行单元格引用，如果c等于0，则返回整列单元格引用。当Area包括多个单元格区域时，n=1就表示结果来自于Area中的第1个区域，n=2表示结果来源于第2个单元格区域。如果省略n表示结果来源于第1个单元格区域。功能In

14、dex（Area，r，c，n）的功能是返回Area中第n个单元格区域中的r行，c列交叉处的单元格引用。,9.2 查找及引用函数,Index&match案例【例9.6】某地域中各县的蔬菜销售单价表如图的A4:J18区域所示，希望能够快捷地查找到某地某蔬菜的单价。最好是输入地名和蔬菜名，就能看到对应的蔬菜单价，如图B1:D3区域所示。,2在D3单元格中输入公式：=INDEX(A5:J18,MATCH(B3,A5:A18,0),MATCH(C3,A5:J5,0),9.2 查找及引用函数,4、用Indirect和名字查询数据概述Indirect函数返回由文字串指定的引用。该函数能够对引用进行计算，并显

15、示引用的内容。当需要更改公式中单元格的引用，而不更改公式本身时，可使用该函数。格式Indirect(ref_text,A1)其中ref_text为对单元格的引用，它可以是单元格的名字、引用或字符串。A1为一逻辑值，指明包含在单元格ref_text中的引用的类型。如果A1为TRUE或省略，ref_text被解释为A1样式的引用。否则ref_text被解释为R1C1样式的引用,9.2 查找及引用函数,例例如，若单元格A1包含文本“B2”，且单元格B2包含数值1.333，则INDIRECT($A$1)=1.333；如果将单元格A1中的文本改为“C5”，而单元格C5中包含数值45，则INDIRECT(

16、$A$1)=45；如果B3包含文本“George”，而名字为George的单元格包含数值10，则INDIRECT($B$3)=10。案例Indirect函数可以构造非常灵活而高效的查询，用名字作它的参数，还能使问题简化。【例9.7】某单位的职称工资表如图（a）所示，职工基本档案表如图（b）所示。假设图（b）中除了E列的职称工资外，其它数据都建立完毕，现在要输入每位职工的职称工资。,9.2 查找及引用函数,（1）指定图（a）中A2:B11区域的最左列为名字。（2）在图（b）的E2单元格中输入下述公式，然后向下复制此公式，就能求出所有职工的职称工资。=Indirect(D2),9.2 查找及引用函

17、数,5、Choose函数格式Choose(n,v1,v2,.v29)其中n是一个整数值，用以指明待选参数的序号。n必须为129之间的数字或者是包含数字129的公式或单元格引用。如果n为1，函数的值就为v1；如果为2，函数返回v2，以此类推 v1，v2，为129个数值参数，可以是数字、单元格引用，或者已定义的名称、公式、函数或文本。功能利用索引从参数清单中选择需要的数值，,9.2 查找及引用函数,案例【例9.8】某学校为了提高教学质量，让学生对教师的授课情况进行评价。评价采用百分制，如图的E列所示。现要将学生评价转换成等级制。,转换规则是：060为不及格，6070为及格，7080为中，8090为

18、良，90100为优。用Choose函数进行转换的方法是，在F2单元格输入公式：=CHOOSE(IF(E260,1,INT(E2-50)/10)+1),不及格,及格,中,良,优),9.3 用数据库函数进行查找统计,1、概述在Excel中，数据库是指每列数据都有标题的数据表。Excel提供大约12个专用数据库函数来简化这种数据表的数据统计和数据查找工作，这些函数都以D开头，所以也称为D函数。D函数有相同的调用形式，相同参数表，格式如下。Dname(database,field,criteria)其中的Dname是函数名，它可以是Dsum、Daverage、Dget、Dcount、Dcounta、D

19、max、Dmin等。各函数的功能如其名字所示，Dsum求总和，Daverage求平均数，Dget查找数据，Dcount统计数字个数，Dcounta统计文本和数据的个数，Dmax求最大数，Dmin求最小数。,9.3 用数据库函数进行查找统计,案例【例9.9】某校某专业共有224名学生，某次期末考试的“数据库系统应用”课程的成绩表如图所示。现在要查找每位学生的成绩，希望输入学号后，就能够得到该生的各种详细数据，如图的J1:M8区域所示。此外，还希望对各班的考试情况进行简单的统计分析，能够随时查看各班的考试人数，最高成绩，高低成绩，及缺考人数等，如图的J10:N17区域所示。,9.3 用数据库函数进

20、行查找统计,9.3 用数据库函数进行查找统计,案例解决方法（1）在K13中输入计算上机平均成绩的公式：=DAVERAGE(A4:H227,上机成绩,J12:J13)（2）在M13输入计算综合平均成绩的公式：=DAVERAGE(A4:H227,综合成绩,J12:J13)（3）在K15输入计算缺考人数的公式：=DCOUNTA(A4:H227,期末考试成绩,J12:J13)-DCOUNT(A4:H227,期末考试成绩,J12:J13)（4）在M15输入计算最高成绩的公式：=DMAX(A4:H227,期末考试成绩,J12:J13)（5）在K17输入计算最低成绩的公式：=DMIN(A4:H227,期末考

21、试成绩,J12:J13)（6）在M17输入计算考试人数的公式：=DCOUNT(A4:H227,淘汰率为4%下的成绩,J12:J13),9.4 用数组公式进行查找统计,数组公式在进行数据的汇总、查询方面有着较强的实用效能，能够解决实际工作中的许多问题。案例【例9.10】某电脑配件经销商将每天的销售数据记录在Excel的工作表中，如图的A1:E18区域所示。该经销商希望随时查看各配件的累计汇总数据，以便为进购做出调整。累计汇总数据表如图中的G1:I10所示，希望在I3中输入月份的数字时，就累计出从1月到该月各种电脑配件的销售总数量和销售总金额。,9.4 用数组公式进行查找统计,9.4 用数组公式进

22、行查找统计,案例解决方法（1）为了简化输入，可建立I3单元格的“有效性”列表输入，从列表中选中月份。（2）在H5中输入累计CPU销售数量的数组公式。即公式输入完后，按Ctrl+shift+Enter=SUM(IF(MONTH($A$3:$A$18)=$I$3,IF($B$3:$B$18=G5,$C$3:$C$18),0)（3）将H5中的公式向下填充复制到H10（4）在I5单元格中输入下面的数组公式然后向下复制即可。=SUM(IF(MONTH($A$3:$A$18)=$I$3,IF($B$3:$B$18=G5,$E$3:$E$18),0),9.5 查找和对比文本,1、概述文本查找不同于一般的数值

23、查找，它具有一定的特殊性，常涉及到较多的Excel文本函数（详情请见第3.11节）。往往要执行计算文本长度，取子串，查找子串在文本中的起始位置等操作。2、案例【例9.10】某单位的会计凭证如图所示，现要判断摘要中的单位和销售单位是否一致。判定方法是从F列的销售单位中取出中文子串，然后查看此子串是否能够在C列的摘要中找到。如果找到就在G列同行显示相同，如果没有找到就在G列同行显示不同。比如，F2中的销售单位是“SCFG 四川飞哥”，其中文子串是“四川飞哥”，而在C2的摘要中正好包括有“四川飞哥”，所以应在G2中填写“相同”。,9.5 查找和对比文本,要得到G列的查询结果，需要用到多个字符处理函数，如Len、Right、Left、Mid、Find、Search等，再结合IF函数和错误处理函数ISERR就能轻松得到处理结果。这在大型数据表的数据查询中是非常有用的。得到G列查询结果的过程如下,9.5 查找和对比文本,The End,谢谢大家！,