Excel指导书.doc

第1章 Excel高级应用课程目标 能够正确地分割窗口、冻结窗口 掌握公式的概念，能创建和应用数组公式 掌握EXCEL内建函数，并能利用这些函数对文档进行统计、处理 掌握数据列表的概念，能设计数据列表，利用自动筛选、高级筛选，以及数据库函数来筛选数据列表，能排序数据列表，创建分类汇总 了解数据透视表的概念，能创建数据透视表，在数据透视表中创建计算字段或计算项目，并能组合数据透视表中的项目课程内容 掌握特殊数据录入方法 掌握数组的概念和数组公式的应用 理解常用函数的作用，掌握常用函数的应用（包括逻辑函数、数学函数、统计函数、日期和时间函数、文本函数、查找和引用函数、数据库函数、财务函数等） 掌握排序和筛选的方法 掌握建立数据透视表和数据透视图的方法1.1 数据录入Excel允许在工作表的单元格中输入文本、数值、日期和时间、批注、公式等多种类型的信息。在前面的基础篇中已经介绍了基本数据类型的输入，下面主要介绍一些特殊类型的数据输入方法和几种更高效的数据输入方法。1.1.1 用Enter输入相同数据要建立如图 21所示的名单表，假设“民族”列中前9位同学都是“汉族”，是相同的数据。则可以用如一的方法实现：(1) 选中区域E2：E10。(2) 输入“汉族”。(3) 按Ctrl + Enter。图 11 名单1.1.2 输入连续的编号连续编号（或等差、等比性质的数据）应该采用复制或序列填充的方式进行输入。比如公司或单位的职工编号、电话号码、手机号码、设备编号等。如要在图 11的“序号”列中输入连续的序号，格式为“1,2,3,.”。输入步骤如下：(1) 在A2,A3单元格分别输入“1”和“2”。(2) 选中A2，A3单元格，向下填充。(3) 如果要输入的序号是等差序列，如“1，3，5”，则在A2,A3单元格分别输入“1”和“3”，然后向下填充。1.1.3 数字作为文本数据输入有些数据如“身份证号码”等，虽然这种数据由数字组成，但输入后不需要进行数学运算，并且以数值形式保存时会因为长度过长要进行舍入而造成数据错误。所以需要把它们当作文本录入。输入的方法是：先输入一个英文标点下的单撇号，再输入身份证号码。如：，输入完成按回车后，单撇号会自动隐藏。 再比如要输入一种产品编号，格式如：“002165”。在数字前包含零。如果直接输入，则开头的零会被舍去。所以需要先输入单撇号，再输入数字串。1.1.4 条件格式在Excel中提供了一个功能非常独特的数据管理功能条件格式。通过设置数据条件格式，可以让单元格中的数据满足指定条件时就以特殊的标记（如设为红色）显示出来。该功能可以让单元格根据不同的应用环境所设置的条件发生变化。实例 1：条件格式应用实例要求：已有一张学生成绩表，如图 22所示，将成绩区域中各科成绩小于60的单元格字体设为红色。图 12 成绩表操作步骤：1. 选中所有的成绩区域（C2:E39）。2. 打开菜单“格式”“条件格式”，弹出条件格式对话框，如图 23所示。3. 在对话框中选择相应的条件选项。并单击“格式”按钮设置文本的颜色为红色。单击“确定”完成设置。图 13 条件格式1.1.5 下拉列表项目个数少而规范的数据，比如职称、学历、工种、单位及产品类型等，这类数据适宜采用Excel的“数据有效性”检验方式，以下拉列表的方式输入 。例如有一人员基本信息表，如图 24所示。其中“学历”类型包括：高中,大专,本科,硕士研究生,博士研究生。则该列数据用下拉列表的方式输入更为快捷有效。图 14 人员基本信息表操作步骤为：(1) 选中D2:D10单元格。(2) 打开菜单“数据”“有效性”，如图 25所示。在“允许”下拉框中选择“序列”，在“来源”框中输入：高中,大专,本科,硕士研究生,博士研究生，单击确定按钮。注意中间用半角英文标点中的逗号分隔。(3) 单击D单元格，在该单元格的右侧出现下拉按钮，单击该下拉按钮即可选择下拉项。图 15数据有效性1.2 数组和数组公式相关原理数组就是单元的集合或是一组处理的值的集合。可以写一个数组公式，即输入一个单个的公式，它执行多个输入操作并产生多个结果，每个结果显示在一个单元格区域中。数组公式可以看成有多重数值的公式，与单值公式的不同之处在它可以产生一个以上的结果。一个数组公式可以占用一个或多个单元格区域，数组的元素可以多达6500个。数组的优势是：(1) 数组公式可以确保公式一致。(2) 数组可以存储在内存中，因此使用数组公式可以加快公式的执行时间。(3) 使用数组公式可以防止公式意外更改。应用了数组公式的单元格，不能单独修改，只能同时更改所有使用了该公式的单元格。实例 1：数组公式应用实例要求：已有一学生成绩表，如图 26所示。使用数组公式,对Sheet1计算总分,将其计算机结果保存到表中的“总分”列中。图 16 学生成绩表操作步骤：(1) 选中F2:F13。(2) 在编辑栏中输入“=”，用鼠标选择D2:D13。(3) 接着在编辑栏中输入“+”，用鼠标选择E2:E13。(4) 按Ctrl+ Shift + Enter。(5) 输入完成后的数据公式形式为： =D2:D13+E2:E13。1.3 函数1.3.1 单元格的引用单元格的引用是指单元格在表中的坐标位置的标识。EXCEL单元格的引用包括相对应用、绝对引用和混合引用等。相对引用公式中的相对单元格引用（例如：A1）是基于包含公式和单元格引用的单元格的相对位置。如果公式所在单元格的位置改变，引用也随之改变。如果多行或多列地复制公式，引用会自动调整。默认情况下，新公式使用相对引用。例如，如果将单元格 B2 中的相对引用复制到单元格 B3，将自动从“=A1”调整到“=A2”。绝对引用绝对引用（例如 $F$6）总是在指定位置引用单元格F6。如果公式所在单元格的位置改变，绝对引用的单元格始终保持不变。如果多行或多列地复制公式，绝对引用将不作调整。默认情况下，新公式使用相对引用，需要将它们转换为绝对引用。例如，如果将单元格 B2 中的绝对引用复制到单元格 B3，则在两个单元格中一样，都是 $F$6。混合引用混合引用具有绝对列和相对行，或是绝对行和相对列。绝对引用列采用 $A1、$B1 等形式。绝对引用行采用 A$1、B$1 等形式。如果公式所在单元格的位置改变，则相对引用改变，而绝对引用不变。如果多行或多列地复制公式，相对引用自动调整，而绝对引用不作调整。例如，如果将一个混合引用从 A2 复制到 B3，它将从 =A$1 调整到 =B$1。在Excel中输入公式时，只要正确使用F4键，就能简单地对单元格的相对引用和绝对引用进行切换。1.3.2 逻辑函数用来判断真假值，或者进行复合检验的Excel函数，称为逻辑函数。在Excel中提供了六种逻辑函数。即AND、OR、NOT、FALSE、IF、TRUE函数。下面介绍其中的AND和IF函数。一、AND函数所有参数的逻辑值为真时返回 TRUE；只要一个参数的逻辑值为假即返回 FALSE。简言之，就是当AND的参数全部满足某一条件时，返回结果为TRUE，否则为FALSE。语法为AND(logical1,logical2, .)，其中Logical1, logical2, . 表示待检测的1到30个条件值，各条件值可能为TRUE，可能为 FALSE。 参数必须是逻辑值，或者包含逻辑值的数组或引用，下面举例说明。实例1 判别成绩是否优秀实例要求：有一学生成绩表，如图 27所示。使用逻辑函数判断Sheet1中每个同学的每门功课是否均大于或等于80，如果是，保存结果为TRUE，否则，保存结果为FALSE，将结果保存在表中的“优秀”列中。图 17 学生成绩表操作步骤(1) 单击F2单元格，找到并打开AND函数。(2) 在3个参数中分别输入3个判断表达式：“C2=80”，“D2=80”，“E2=80”。如图 28所示，单击确定完成函数输入。并向下填充。图 18 AND函数二、IF函数 IF函数用于执行真假值判断后，根据逻辑测试的真假值返回不同的结果，因此If函数也称之为条件函数。它的应用很广泛，可以使用函数 IF 对数值和公式进行它的语法为IF(logical_test,value_if_true,value_if_false)。其中Logical_test表示计算结果为TRUE或FALSE的任意值或表达式，该参数可使用任何比较运算符。 Value_if_true显示在logical_test 为 TRUE 时返回的值。Value_if_false显示在 logical_test为FALSE 时返回的值。简言之，如果第一个参数logical_test返回的结果为真的话，则执行第二个参数Value_if_true的结果，否则执行第三个参数Value_if_false的结果。IF函数可以嵌套七层，用value_if_false 及 value_if_true 参数可以构造复杂的检测条件。实例2 IF函数应用实例要求：使用逻辑函数，判断Sheet1中的“是否=35女性”，将结果保存在Sheet1中的“是否=9，作用是判断C2单元格中的日期值的月份是否大于或等于9。单击确定按钮完成输入，并向下充填该列。图 116 是否9月以后出生(3) 单击H2单元格，在编辑栏中输入公式：=G2-F2 。说明：两个时间值相减就是总的用时。(4) 单击H2单元格，找到并打开IF函数，其参数如图 217所示。单击确定按钮完成输入，并下向填充该列。图 117 计费时间1.3.5 文本函数EXCEL2003的提供的常用文本函数有：CONCATENATE，MID，REPLACE，UPPER等。函数的功能如表 24所示。表 14 常用文本函数函数功能CONCATENATE将多个文本字符合并成一个MID返回文本字符串中从指定位置开始的特定数目的字符REPLACE将一个字符串的部分字符替换成其它字符串UPPER将文本转换成大写形式LOWER将文本转换成小写形式FIND返回一个字符串在另一个字符串中出现的起始位置LEN返回文本串中字符的个数TRIM删除文本中的空格实例1 ： 员工信息修改及提取实例要求：已有某公司职工资料表，部分内容如图 218。(1) 将电话号码升位，升位的方法是在“0571”后加“8”。(2) 从身份证号码中取得该员工的出生日期，中间用“-”隔开，如“1989-12-16”。图 118 某公司职工资料表操作步骤：(1) 单击F2单元格，找到并打开REPLACE函数，该函数有4个参数，Old_text指原来的字符串，Start_num指从第几位字符开始替换，Num_chars指共要替换几个字符，该题是要插入“8”，所以不用替换字符。具体参数如图 219所示。单击“确定”完成输入，并向下填充。然后，将该列数据用“选择性粘贴”的方法，把数值复制到原来的“电话号码”列，并删除F列的数据。图 119 REPLACE函数(2) 单击F2单元格，找到并打开CONCATENATE函数。如图 220所示输入各个参数。其中“MID(B2,7,4)”的含义是从B2单元格的身份证号码中取第7位开始的4位字符。图 120 CONCATENATE函数1.3.6 查找与引用函数在工作表中，可以利用查找与引用函数功能按指定的条件对数据进行快速查询、选择和引用。查找与引用函数用于查找列表或表格中的值。常用的查找和引用函数如表 25所示。表 15 常用查找与引用函数函数功能VLOOKUP在表格或数值数组的首列查找指定的数值，并由此返回表格或数组当前行中指定列处的数值。当比较值位于数据表首列时，可以使用函数 VLOOKUP 代替函数 HLOOKUP。HLOOKUP在表格或数值数组的首行查找指定的数值，并由此返回表格或数组当前列中指定行处的数值。实例1 ： 职工信息查找实例要求：已有某公司的职工资料表，部分内容如图 221所示。分别从右侧的岗位标准表和通讯补贴表中查找数据，并填充到对应的岗位标准和通讯补贴列中。图 121 职工资料表操作步骤：1. 单击D2单元格，找到并打开VLOOKUP函数，输入如图 222所示的参数。其中将第2个参数改为绝对地址（按F4键），使填充该公式时该区域保持不变。单击确定按钮完成输入，并向下填充。图 122 VLOOKUP函数2. 单击E2单元格，找到并打开HLOOKUP函数，输入如图 223所示的4个参数。单击确定按钮，并向下填充。图 123 HLOOKUP函数1.3.7 数据库函数当需要分析数据清单中的数值是否符合特定条件时，可以使用数据库函数。如果能灵活运用这类函数，就可以方便地分析数据库中的数据信息。在所有的数据库函数中，根据各自函数所具有的功能不同，要分为两大类：（1）数据库信息函数，这类函数的主要功能是直接获取数据库中的信息。（2）数据库分析函数，这类函数的主要功能是分析数据库的数据信息。常用的数据库函数见表 26所示。表 16 常用数据库函数函数功能DCOUNTA计算机数据库中非空单元格的个数DGET从数据库中提取满足指定条件的单个记录DSUM对数据库中满足条件的记录的字段列中的数字求和DAVERAGE返回选定数据库项的平均值DMAX返回选定数据库项中的最大值DMIN返回选定数据库项中的最小值实例1 学生体育成绩统计实例要求：已有学生体育成绩表，部分内容如图 224所示。 要求：(1) 统计男生人数。(2) 统计男生100米跑平均成绩。(3) 求100米成绩16秒的所有女生的铅球成绩之和。(4) 求100米成绩11秒的女生的姓名。(5) 跑得最快的男生的成绩。图 124 学生体育成绩操作步骤：(1) 单击I2单元格，找到并打开DCOUNTA函数，输入如图 225所示的参数。其中Database参数表示要统计的区域，可以选整个数据区，也可以只选性别列。Field参数可以用列标，也可以用该列在整个数据库中的序号（如:1）。单击确定按钮完成输入。图 125 DCOUNTA函数(2) 单击I3单元格，找到并打开DAVERAGE函数，函数的参数如图 226所示。其中Database参数为整个数据区域，Field为要计算平均值的列。单击确定按钮完成输入。图 126 DAVERAGE函数(3) 单击I4单元格，找到并打开DSUM函数，函数的参数如图 227所示。单击确定按钮完成输入。图 127 DSUM函数(4) 单击I5单元格，找到并打开DGET函数，函数的参数如图 228所示。单击确定按钮完成输入。图 128 DGET函数(5) 单击I6单元格，找到并打开DMIN函数，函数的参数如图 229所示。单击确定按钮完成输入。图 129 DMIN函数1.3.8 财务函数财务函数是财务计算和财务分析的专业工具，有了这些函数，可以很方便地解决复杂的财务运算，在提高财务工作效率的同时，更有效地保障了财务数据计算的准确性。常用的财务函数如表 27所示。表 17 常用的财务函数函数功能DB用固定余额递减法，计算一笔资产在给定期间内的折旧值DDB用双倍余额递减法或其他指定方法，计算一笔资产在给定期间内的折旧值FV基于固定利率及等额分期付款方式，返回某项投资的未来值IPMT基于固定利率及等额分期付款方式，返回给定期数内对投资的利息偿还额。IRR返回由数值代表的一组现金流的内部收益率。ISPMT计算特定投资期内要支付的利息。MIRR返回某一连续期间内现金流的修正内部收益率。NPER基于固定利率及等额分期付款方式，返回某项投资的总期数。NPV通过使用贴现率以及一系列未来支出（负值）和收入（正值），返回一项投资的净现值。PMT基于固定利率及等额分期付款方式，返回贷款的每期付款额。PPMT基于固定利率及等额分期付款方式，返回投资在某一给定期间内的本金偿还额。PV返回投资的现值。现值为一系列未来付款的当前值的累积和。RATE返回年金的各期利率。SLN返回某项资产在一个期间中的线性折旧值。SYD返回某项资产按年限总和折旧法计算的指定期间的折旧值。VDB使用双倍余额递减法或其他指定的方法，返回指定的任何期间内（包括部分期间）的资产折旧值。实例1贷款计算实例要求：某人的银行贷款信息如图 230所示，试求：(1) 按年偿还贷款金额（年末）。(2) 第9个月的贷款利息金额。图 130 银行贷款信息操作步骤：(1) 单击E2单元格，找到并打开PMT函数，函数的参数如图 231所示。单击确定按钮完成输入。图 131 PMT函数(2) 单击E3单元格，找到并打开IPMT函数，函数的参数如图 232所示。其中，Rate参数指月利率（年利率/12）；Per参数指第9期；Nper参数指还款总期数（15年12期）；Pv指总贷款金额。单击确定按钮完成输入。图 132 IPMT函数实例2 计算资产折旧实例要求：根据“固定资产情况”表(见图 233)，利用财务函数，对以下条件进行计算：(1) 计算机“每天折旧值”，并将结果填入到E2单元格；(2) 计算机“每月折旧值”，并将结果填入到E3单元格；(3) 计算机“每年折旧值”，并将结果填入到E4单元格；图 133 固定资产折旧操作步骤：(1) 单击E2单元格，找到并打开SLN函数，函数的参数如图 234所示。其中Cost参数指固定资产金额；Salvage参数指资产残值；Life参数指折旧计算的周期总数，这里求每天的折旧值，所以要用年限乘以365天。单击确定按钮完成输入。图 134 SLN函数(2) 单击E3单元格，找到并打开SLN函数，完成输入后的函数公式为：=SLN(B2,B3,B4*12) 。(3) 单击E4单元格，找到并打开SLN函数，完成输入后的函数公式为：=SLN(B2,B3,B4) 。1.4 排序和筛选相关原理数据排序的功能是按一定的规则对数据进行整理和排列，为进一步处理数据做好准备。Excel2003提供了多种对数据列表进行排序的方法，即可以按升序或降序进行排序，也可以按用户自定义的方式进行排序。数据筛选是一种用于查找数据的快速方法，筛选将数据列表中所有不满足条件的记录暂时隐藏起来，只显示满足条件的记录行，以供用户浏览和分析。Excel2003提供了自动筛选和高级筛选两种筛选数据的方式。自动筛选一般用于简单的条件筛选。高级筛选一般用于条件较复杂的筛选操作，其筛选的结果可显示在原数据表格中，不符合条件的记录被隐藏起来；也可以在新的位置显示筛选结果，不符合的条件的记录同时保留在数据表中而不会被隐藏起来，这样就更加便于进行数据的比对了。实例1 对部分记录排序实例要求：有学生成绩表如图 235所示，sheet1表中包括两个班级的学生成绩，要求对3班的所有同学以总分为关键字降序排序，1班同学的记录位置保持不变。图 135 学生成绩表操作步骤：(1) 选中A6：F15单元格。(2) 打开菜单“数据”“排序”，在“我的数据区域”处选择“无标题行”，主要关键字选“列F”，并选降序，确定。如图 236所示。图 136 排序实例2 高级筛选实例要求：对Sheet1进行高级筛选，筛选条件为:“性别”-男，“年龄”3,“所属部门”:市场1部、“销售金额”:1000(b) 将筛选结果保存在Sheet2中。(6) 根据Sheetl的“9月份销售统计表”中的数据，新建一个数据透视图Chartl,要求:(a)该图形显示每位经办人的总销售额情况。(b)x坐标设置为“经办人”；(c)数据区域设置为“销售金额”；(d)求和项为销售金额；(e)将对应的数据诱视表保存在Sheet3中。操作步骤：(1) 首先来求产品名称。因为产品的型号和名称是以列的方式保存在“企业销售产品清单”中，所以应该用VLOOKUP函数，而不是HLOOKUP函数。单击C3单元格，打开VLOOKUP函数，该函数的四个参数如图 244所示。其中Table_array参数中的绝对地址引用可以通过按F4键来实现。按确定完成函数输入。双击C3单元格的填充柄，填充所有产品的名称。同理，填充产品单价列。图 144 VLOOKUP函数说明：如果Table_array参数没有设为绝对应用，则在对该函数向下填充时，该地址会变化