Excel动态报表与数据查找

1、动态报表与数据查找Excel与数据处理 第3版学习目标1、理解表及其结构化引用2、掌握动态报表的构造和数据分析方法3、掌握查询大工作表数据的方法4、用lookup函数查询数据的方法5、用Vlookup函数查询数据的方法6、index和match相结合查询数据的方法7、用D函数查询数据的方法8、文本查询的方法9、indirect和名字相结合查询数据的方法10、用choose查询数据的方法1 表与动态报表1、工作表的缺限日常工作中，类似于这样的“日志工作表很常见，每天都向此工作表添加数据，由于数据行的不确定性，为统计工作带来了一定的困难！Excel 2007的表可以解决这一难题！1.1 表1、表的

2、概念表也称表格，是一系列包含相关数据的行和列，这些行和列与工作表中其他行和列中的数据分开管理。表是Excel2007中的特殊对象其实早就有了，对应于Excel 2003中的列表，只是它比列表具有更多的功能，包含有格式化功能以外的许多特性。表中包括的主要内容有表区域、表数据区域、汇总行、标题行、列标题、调整大小控制点等，如图8.2所示。表具有动态特性。当表中的数据行发生变化之后，针对于表的各种计算公式会以表中的最新数据为依据进行自动调整，重新计算出正确的结果。 1.1 表2、表的建立表与普通工作表区域可以随时转换，可以将普通工作表转换成表，也可以将表转换成普通工作表。1、建立普通工作表，每列有标

3、题2、单击“插入|“表按钮3、弹出“创立表对话框，单击“确定按钮就会建立该数据区域对应的表，如下页所示1.1 表表名称标题行控制点汇总行表区域数据区域计算列1.1 表2、表的结构表：是指包括标题和汇总行在内的整个区域 。表名称：每个表都有一个名称，应用表名称可以引用表中的数据。标题行：表区域的第一行，常用描述性文字表示。在默认情况下，表中每一列都在标题行中启用了筛选功能，利用此功能可以快速筛选表中的数据或对表进行排序。数据区域：数据区域是指除开表标题和汇总行之外的区域，是表存放数据的单元格区域。1.1 表2、表的结构表：是指包括标题和汇总行在内的整个区域 。表名称：每个表都有一个名称，应用表名

4、称可以引用表中的数据。标题行：表区域的第一行，常用描述性文字表示。在默认情况下，表中每一列都在标题行中启用了筛选功能，利用此功能可以快速筛选表中的数据或对表进行排序。数据区域：数据区域是指除开表标题和汇总行之外的区域，是表存放数据的单元格区域。1.1 表2、表的结构汇总行：汇总行位于表的最下方，在最后一个数据行下面。在默认情况下，汇总行是不显示在汇总行中，可以对相应列中的表数据进行各种类型的汇总计算，如计数、求平均数、求总和等。大小调整控制点：在表的右下角，用鼠标上下左右拖动它，可以可以扩大或缩小表所对应的区域。排序和筛选：Excel会自动将筛选器下拉列表添加在表的标题行中，通过它可以实现工作

5、表数据筛选。 显示和计算表数据总计：可以快速地对表中的数据进行汇总，方法为：在表的末尾显示一个总计行，然后使用在每个总计行单元格的下拉列表中提供的函数 1.1 表2、表的结构使用计算列：要使用一个适用于表中每一行的公式，可以创立计算列。计算列会自动扩展以包含其他行，从而使公式可以立即扩展到这些行。动态扩展：表中的数据区域具有动态特性，可以灵活地向表中添加或删除数据行。当在表下边相邻的空行或表右边相邻的空列中输入数据时，Excel就会自动对表进行扩展，将输入了数据的相邻行或列添加到表中。拖动表的大小控制点，让它包括相邻的工作表行或工作表列，这些被包括的行或列就会被添加到表中；在表中任意位置插入行

6、或列，插入的行和列就会成为表的有效组成局部。 1.1 表计算列和动态扩展H列是计列：在H2中输入公式：=F2*H2回车后，Excel就会自动填充H列的计算公式在I2中输入21，表会动态扩展将I列包括到表中，但I列不是计算列，计算列应包括公式。在J2中输入“=2，这是一个公式，J2是计算列，Excel会将此公式填充J列在第8行任一单元格输入数据，Excel就会自动扩展表区域，将第8行包括到表中1.2 结构化引用和动态报表1、结构化引用表是一个自包含对象，是一个完整的结构，表区域、数据区域、汇总行、标题行、列标题、数据行和数据列等都是表结构的组成局部。在对表进行计算的公式中，可以引用表中的单元格，

7、也可以直接引用行、列、数据区域、汇总行或标题行等表结构，称为结构化引用。 结构化引用最大的优点是对于动态报表的自动识别，无论表的数据区域怎样变化，结构化引用的单元格区域都能够随之进行自动调整。这样便在最大程度上减少了在表中添加和删除行或列时重写公式的需要。 1.2 结构化引用和动态报表【例8.2】在例8.1的销售工作表中建立统计报表，计算以下数据：每位职工销售各种品牌电视机的总数量、总的销售记录数、所有电视机的平均销售价格、所有产品的销售总数量以及商店工作人员的名单。 表K3单元格中的公式，其中包括结构化引用通过表，删除冗余数据得到的不重复名单1.2 结构化引用和动态报表1、结构化引用语法结合

8、Page 223理解公式中的含义，弄清楚结构化引用的真实用法！1.2 结构化引用和动态报表H列是计算列，公中应用对表3的结构化引用，其中的#此行表示公式所在数据行，单价和数量都是表中的数据列的结构化引用2、常用结核化引用1.2 结构化引用和动态报表K列公中应用了对表3的结构化引用，无论A：H列的表中是否会增加或删除数据，K列的计算公式都会根据表中的最新数据进行计算，得到正确的结果。这些计算公式因为应用了对表的结构化引用而具有动态计算的能力。2、常用结核化引用1.2 结构化引用和动态报表3、通过结构化引用删除冗余数据行在Excel的普通工作表中，要删除其中的重复数据行并不容易。将普通工作表转换成

9、表，然后利用表提供的“删除重复项功能，就能轻松地得到数据行不重复的报表。 1、包括重复数据的区域2、转换成表3、选择删除重复项4、删除重复项后的表1.2 结构化引用和动态报表4、通过数组公式引用表 表的名称、列标题、各种特殊项如#全部、汇总等事实相当于对应区域的名称，可以通过数组公式在不同的工作表中引用它们。 1.2 结构化引用和动态报表5、在不同工作表中对表进行结构化引用【例8.3】某超市从多家供给商处进购各种食品，进购的情况如图a所示。计算超市应付给各供给商的总订货费，如图b所示 1、将源数据区域转换成表2、复制源数据区域的供给商，将它转换成表表，并通过表删除其中的重复数据行3、在B2中输

10、入公式：=SUM(IF(进货单供给商=表5#此行,供给商,进货单单价*(进货单订购量+进货单再订购量)按Ctrl+shift+Enter,就会自动生成B列的计算公式1.2 结构化引用和动态报表6、表的应用和普通工作表区域的转换表能够方便地构造日常工作中的动态报表，在各种不同的公式中通过对表的结构化引用，不仅能够使公式含义清楚，而且能够扩展公式的计算能力，实现对动态报表的各类计算。在日常工作中，应该大量用表来保存各种业务数据，制作工作报表。 单击表中任一单元格单击“转换为区域，可将表转换为普通区域2 D函数与动态报表1、关于D函数Excel将每个数据列都有标题的数据表称为数据库，并提供了大约12

11、个专用函数来简化这种数据表的数据统计和数据查找工作，这些函数都以D开头，所以也称为D函数。D函数有相同的调用形式，其语法形式如下：Dname(database,field,criteria) 其中，Dname是函数名；database是一个单元格区域，要求该区域中的每列数据都必须有标题；field是database区域中某列数据的列标题称为字段，出现在字符串中；criteria称为条件区域，它与高级筛选条件区域的含义和构造方法完全相同。 2.1 D函数函数名功能DAVERAGE返回所选数据库列的平均值DCOUNT计算数据库中包含数字的单元格的数量DCOUNTA计算数据库中非空单元格的数量DGE

12、T从数据库中提取符合指定条件的一行数据DMAX返回所选数据库列中的最大值DMIN返回所选数据库列的最小值DPRODUCT将数据库中符合条件的数据行值相乘DSTDEV基于选择的数据库列的样本估算标准偏差DSTDEVP基于所选数据库列的样本总体计算标准偏差DSUM对数据库中符合条件的数据行的数字求和DVAR基于所选数据库列的样本估算方差DVARP基于所选数据库列的样本总体计算方差2.1 D函数条件区域数据库区域D函数的调用2.1 D函数与表结合构造动态数据分析报表D函数具有动态计算能力用D函数对数据表进行条件统计非常方便，同时D函数还具有动态计算的能力。在数据库中应用D函时，其动态计算能力依赖于第

13、一个参数的范围设置【例8.4】某商店在工作表中保存库存和进货记录，如图8.9中A:J列所示。由于随时可能会添加进货记录，因此工作表中的数据行是不确定的。计算表中各种商品的总库存量、第一次订购量和再订购量的总和，以及每类产品的总平均费用。 2.1 D函数与表结合构造动态数据分析报表应用D函数和普通数据库字段计算出的汇总数据在D函数应用表的结构化引用计算出的汇总数据。无论表区域的数据如何扩展，这些统计数据都会实时计算，自动更新，具有完全的动态数据分析能力各单元格的公式设置请参考Page 229-230页的介绍。第229页描述有误，应该在N3、N4、N5、N6中输入公式3 查找大工作表的特定数据行

14、1、概述当工作表数据行较多时，要查看其中的某行数据并非易事。利用Excel提供的查找菜单或记录单功能就能够很快定位到特定数据行，实现高效的查找。 本节案例【例8.5】某单位有600多名职工，其医疗档案表如所示，现要从中查看李大友的医疗费用情况。5 查找大工作表的特定数据行 3 查找大工作表的特定数据行 1、精确数据查找1、单击“开始选项卡2、单击“查找和选择中的“查找命令3、在“查找内容中输入查找内容4、单击“查找全部可以将光标定位到找到的数据行上3 查找大工作表的特定数据行 2、模糊数据查找1、单击“开始选项卡2、单击“查找和选择中的“查找命令3、在“查找内容中输入查找内容，*代表任意符号4

15、、单击“查找全部可以将光标定位到找到的数据行上4 查找及引用函数1、概述查找引用函数能通过单元格引用地址、行、列对工作表的单元格进行访问，还能够从单元格的引用地址中求出单元格所在的行或列，进而查获更多的信息。当需要从一个工作表查询特定的值、单元格内容、格式或选择单元格区域时，这类函数特别有用。在大数据表、不同工作薄或工作表之间查询数据时，这类函数很有用。有时，将查询结果用于公式计算，能够事半功倍。4.1用Lookup函数进行表查找Lookup函数Lookup函数在一个大表中找出特定数据，并在其它工作表中引用查找结果，在工作中应用较广。功能 从给定的向量单行或单列单元格区域或数组中查询出需要的数

16、值。格式Lookup(x, r1, r2)其中：x是要查找的内容，它可以是数字、文本、逻辑值或包含数值的名称或引用。r1、r2都是只包含一行或一列的单元格区域，其值可以是文本、数字或逻辑值。r2的大小必须与r1相同。Lookup函数在r1所在的行或列中查找值为x的单元格，找到后返回r2中与r1同行或同列的单元格中的值。4.1用Lookup函数进行表查找1、用lookup函数在普通工作表中查找数据【例8.6】某蔬菜供给商在一个工作表中保存蔬菜的单价和出产地，如图a所示。在另一工作表中保存销售记录，如图b所示。在图b中，蔬菜名和数量是实际输入的数据，产地和单价需要根据产品名从图a所示的蔬菜单价表中

17、查询输入 D3中输入下述公式：=LOOKUP(A3,蔬菜单价表!$A$2:$A$11,蔬菜单价表!$B$2:$B$11)向下复制此公式，查出蔬菜的单价在B3中输入下述查找公式：=LOOKUP(A3,蔬菜单价表!$A$2:$A$11,蔬菜单价表!$C$2:$C$11)向下复制此公式，查出蔬菜的产地4.1用Lookup函数进行表查找案例解决方法1建立图a所示的蔬菜单价表，并按升序对该工作表进行排序，排序主关键字为“蔬菜。2输入图b的A列数据，和第1、2行的标题。在B3中输入下述查找公式，然后向下填充复制该公式，就可找出各蔬菜的产地。=LOOKUP(A3,蔬菜单价表!$A$2:$A$11,蔬菜单价表

18、!$C$2:$C$11)查找蔬菜单价的方法与此完全类似，只需要在图b的D3单元格中输入下述公式，然后向下复制该公式就行了。=LOOKUP(A3,蔬菜单价表!$A$2:$A$11,蔬菜单价表!$B$2:$B$11)4.1用Lookup函数进行表查找注意： r1中的内容必须按升序排序，查找的字符文本不区分大小写。否那么， Lookup函数不能返回正确的结果。如果Lookup函数找不到x，那么查找r1中小于或等于x的最大数值。如果x小于r1中的最小值，Lookup函数返回错误值“#N/A。 4.1用Lookup函数进行表查找2、在lookup函数通过对表的结构化引用查找数据4.2用Vlookup函数

19、进行表查找Vlookup函数功能Vlookup按列查找的方式从指定数据表区域的最左列查找特定数据，它能够返回查找区域中与找到单元格位于相同行不同列的单元格内容 。格式Vlookup (x, table, n, f)其中，x是要查找的值；table是一个单元格区域；n中table区域中要返回的数据所在列的序号。n=1时，返回 table 第1列中的数值；n=2时，返回 table 第2列中的数值；以此类推。f是一个逻辑值，表示查找的方式。 当其为true或1时，表示模糊查找；当它为false或0时，表示精确查找。4.2用Vlookup函数进行表查找说明：Vlookup函数在table区域的第1列

20、中查找值为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。4.2用Vlookup函数进行表查找1用Vlookup进行模糊查找模糊查找也就是常说的近似查找，常用于数据转换或数据对照表中的数据查找。案例【例8.

21、7】假设所得税的税率如图的A1:B10区域所示。其中的含义是：0500的税率为0%，5001000的税率为1%，10001500的税率为3%，4000以上的税率为20%。某公司的职工收入数据如图的D1:J11所示，现在计算每位职工应缴的所得税。4.2用Vlookup函数进行表查找I列的所得税率的计算方法如下。在I3单元格中输入下述公式，然后向下复制此公式，就能够计算出每位职工的所得税率。=Vlookup (H3,$A$3:$B$10,2,1)Page 234图8.14有误，应为此图4.2用Vlookup函数进行表查找2用Vlookup进行精确查找概述精确查找就是指查找数据完全匹配的查找，Vlo

22、okup函数具有此项功能。在大表中查找特定数据，或查找不同工作表中的数据，特别是工作表数据较多， Vlookup函数显得非常有效 4.2用Vlookup函数进行表查找案例【例8.8】某校某专业期末考试的数据库成绩表如图的A:H列所示。由于人数较多，要查看某个同学的成绩非常困难。希望能按学号进行查找，即在K5输入某个学号后，就能自动显示出该学号所对应的姓名和各种成绩，如图的J4:M16所示 4.2用Vlookup函数进行表查找案例解决方法1在M5中输入公式：=VLOOKUP(K5,A5:H227,2,0)2在L6中输入公式：=VLOOKUP(K5,A5:H227,3,0)3在L7中输入公式：=V

23、LOOKUP(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)4.2用Vlookup函数进行表查找案例2【例8.9】某 公司的 收费系统进行了系统升级，图a是系统升级前的 号码和收费账号对照表，图b的是升级后的收费表。升级后系统新加了一些号码，新加的号码要重新编制账号，但原有号码的账号那么需要从旧系统中查询。也就是说，图b中的绝大局部

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

25、、Hlookup函数更多的灵活性，它可以在工作表的一行或一列中进行数据查找，并返回数据在行或列中的位置。如果需要找出数据在某行或某列的位置，就应该使用Match函数而不是Lookup函数 在多数情况下，Match函数的结果并不是所需要的最终答案，而是作为lookupVlookup，Hlookup的第3个参数，或作为Index函数的参数 4.3用match和index函数构造灵活的查询Match格式Match ( x， r，f )其中x是要查找的数值，r可以是一个数组常量，或某列或行连续的单元格区域，其中可能包含有要查找的x。f用于指定match的查找方式，它可以是-1，0或1，表9-1给出了这

26、几个取值的含义。功能Match(x，r，f)表示的意思是：在数组或连续的单元格区域r中查找x，并返回x在r中的位置编号。当f为0是，match进行精确查找，当f为1或-1时，match进行模糊查找。4.3用match和index函数构造灵活的查询取值函 数 功 能-1r必须按降序排列，查找大于或等于 x的最小数值0r 不必排序，查找等于x的第一个数值1r必须按升序排列，查找小于或等于x的最大数值Match查找的方式4.3用match和index函数构造灵活的查询2、Index函数格式Index(Area，r，c，n)其中，Area是1个或多个单元格区域；r是某行的行序号，c是某列的列序号，该函

27、数返回指定的行与列交叉处的单元格引用。如果r等于0，那么返回整行单元格引用，如果c等于0，那么返回整列单元格引用。当Area包括多个单元格区域时，n=1就表示结果来自于Area中的第1个区域，n=2表示结果来源于第2个单元格区域。如果省略n表示结果来源于第1个单元格区域。功能IndexArea，r，c，n的功能是返回Area中第n个单元格区域中的r行，c列交叉处的单元格引用。4.3用match和index函数构造灵活的查询Index&match案例【例8.10】某地域中各县的蔬菜销售单价表如图的A4:J18区域所示，希望能够快捷地查找到某地某蔬菜的单价。最好是输入地名和蔬菜名，就能看到对应的蔬

28、菜单价，如图B1:D3区域所示。 2在D3单元格中输入公式：=INDEX(A5:J18,MATCH(B3,A5:A18,0),MATCH(C3,A5:J5,0)4.4用Indirect和名字查询数据用Indirect和名字查询数据概述Indirect函数返回由文字串指定的引用。该函数能够对引用进行计算，并显示引用的内容。当需要更改公式中单元格的引用，而不更改公式本身时，可使用该函数。 格式Indirect(ref_text, A1)其中ref_text为对单元格的引用，它可以是单元格的名字、引用或字符串。A1为一逻辑值，指明包含在单元格ref_text中的引用的类型。如果A1为TRUE或省略，

29、ref_text被解释为A1样式的引用。否那么ref_text被解释为R1C1样式的引用 4.4用Indirect和名字查询数据例例如，假设单元格A1包含文本“B2，且单元格B2包含数值1.333，那么INDIRECT($A$1)=1.333；如果将单元格A1中的文本改为“C5，而单元格C5中包含数值45，那么INDIRECT($A$1)=45；如果B3包含文本“George，而名字为George的单元格包含数值10，那么INDIRECT($B$3)=10。案例Indirect函数可以构造非常灵活而高效的查询，用名字作它的参数，还能使问题简化。【例8.11】某单位的职称工资表如图a所示，职工根

30、本档案表如图b所示。假设图b中除了E列的职称工资外，其它数据都建立完毕，现在要输入每位职工的职称工资。 4.4用Indirect和名字查询数据1指定图a中A2:B11区域的最左列为名字。2在图b的E2单元格中输入下述公式，然后向下复制此公式，就能求出所有职工的职称工资。=Indirect (D2)4.5 Choose函数查询Choose函数格式Choose(n, v1, v2, .v254)其中n是一个整数值，用以指明待选参数的序号。n必须为1254之间的数字或者是包含数字1254的公式或单元格引用。如果n为1，函数的值就为v1；如果为2，函数返回v2，以此类推 v1，v2，为1254个数值参

31、数，可以是数字、单元格引用，或者已定义的名称、公式、函数或文本。功能利用索引从参数清单中选择需要的数值， 4.5 Choose函数查询案例【例8.12】某学校为了提高教学质量，让学生对教师的授课情况进行评价。评价采用百分制，如图的E列所示。现要将学生评价转换成等级制。转换规那么是：060为不及格，6070为及格，7080为中，8090为良，90100为优。用Choose函数进行转换的方法是，在F2单元格输入公式：=CHOOSE(IF(E260,1,INT(E2-50)/10)+1),不及格,及格,中,良,优)5 用数据库函数进行查找统计 1、概述在Excel中，数据库是指每列数据都有标题的数据

32、表。Excel提供大约12个专用数据库函数来简化这种数据表的数据统计和数据查找工作，这些函数都以D开头，所以也称为D函数。D函数有相同的调用形式，相同参数表，格式如下。Dname(database,field,criteria)其中的Dname是函数名，它可以是Dsum、Daverage、Dget、Dcount、Dcounta、Dmax、Dmin等。各函数的功能如其名字所示，Dsum求总和，Daverage求平均数，Dget查找数据，Dcount统计数字个数，Dcounta统计文本和数据的个数，Dmax求最大数，Dmin求最小数。5 用数据库函数进行查找统计案例【例8.13】某校某专业共有22

33、4名学生，某次期末考试的“数据库系统应用课程的成绩表如下图。现在要查找每位学生的成绩，希望输入学号后，就能够得到该生的各种详细数据，如图的J1:M8区域所示。此外，还希望对各班的考试情况进行简单的统计分析，能够随时查看各班的考试人数，最高成绩，上下成绩，及缺考人数等，如图的J10:N17区域所示。 5 用数据库函数进行查找统计5 用数据库函数进行查找统计案例解决方法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输入计算最