下载

1下载券

加入VIP
  • 专属下载特权
  • 现金文档折扣购买
  • VIP免费专区
  • 千万文档免费下载

上传资料

关闭

关闭

关闭

封号提示

内容

首页 Excel高级应用课程学习指导

Excel高级应用课程学习指导.doc

Excel高级应用课程学习指导

aceowen
2011-03-19 0人阅读 举报 0 0 暂无简介

简介:本文档为《Excel高级应用课程学习指导doc》,可适用于IT/计算机领域

《Excel高级应用》《Excel高级应用》课程学习指导材料编写邹旭敏适用专业:软件工程适用层次:本科四川大学网络教育学院二四年三月《Excel高级应用》课程学习指导材料编写:邹旭敏审稿(签字):审批(主管教学负责人签字):本课程学习指导材料根据该课程教学大纲的要求参照现行采用教材《Excel与数据处理》(杜茂康主编电子工业出版社年)以及课程学习光盘并结合远程网络业余教育的教学特点和教学规律进行编写适用于软件工程本科学生。第一部分课程的学习目的及总体要求一、课程的学习目的系统学习电子表格软件Excel掌握如何运用Excel进行各类数据处理。二、课程的总体要求掌握与Excel相关的基础知识能正确输入Excel的各类数据和格式化工作表及单元格数据掌握Excel公式、函数、图表的使用能对工作表中的数据进行正确排序、筛选、分类和汇总学会应用Excel提供的数据分析工具进行模拟运算、单变量求解、规划分析了解Excel的数据库以及与外部数据库的数据交换了解VBA及宏在Excel中的应用。第二部分课程学习的基本要求及重点难点内容分析第一章电子表格软件Excel、本章学习要求⑴应熟悉的内容)利用自定义填充序列限制数据的有效性范围)Excel公式的运算符及其优先级)函数SUM、AVERAGE、MAX、MIN、COUNT、COUNTIF、IF的使用)如何管理工作簿窗口和隐藏工作表。⑵应掌握的内容)公式的基本概念和输入方法)单元格引用的概念A引用样式下的相对引用、绝对引用和混合引用⑶应熟练掌握的内容)Excel的启动、退出方法默认设置下Excel工作窗口的组成)对工作簿、工作表、行列、单元格进行的各种操作)各类数据和有规律数据的输入。、本章重点难点分析⑴Excel基础知识启动Windows操作系统后可以利用“开始”菜单、“我的电脑”“资源管理器”、桌面快捷方式等多种方式启动Excel。退出Excel的方法也有多种如单击Excel窗口右上方的关闭图标、执行“文件”菜单中的“退出”命令等。Excel中处理和存储数据的文件称工作簿它以独立的文件形式存储在磁盘上扩展名为xls。启动Excel后可以建立新工作簿打开、保存或关闭旧工作簿。Excel窗口由应用程序窗口和文档窗口两大部分组成。默认情况下Excel的工作窗口主要由标题栏、菜单栏、工具栏、编辑栏、文档窗口、状态栏等几部分组成。在Excel中可以同时打开多个工作簿。默认情况下一个工作簿对应一个窗口。利用工作簿窗口右上角按钮可最大化、最小化、还原工作簿窗口。执行“窗口”菜单中的相应命令可以新建、重排、拆分、冻结、隐藏和切换工作簿窗口。一个工作簿可以包含多张相互独立的工作表。一张工作表由行×列组成行用数字编号(称行号)列用字母编号(称列标)行与列相交而成的格子称为单元格。单元格是处理数据的基本单位。多个相邻或不相邻的单元格构成区域。工作表中边框加粗加黑、右下方有填充柄的单元格称为活动单元格代表当前接收输入或编辑数据的单元格。使用Excel时经常需要对整张工作表以及工作表中的某些行、列、单元格进行操作。除了选取操作之外这些操作都可以通过系统菜单和相应的右键快捷菜单命令来实现。⑵数据的输入使用Excel时常常需要输入大量的数据:文本、数值、日期时间、公式、逻辑值等。输入数据时先选定单元格(区域)然后由键盘向编辑栏或单元格输入数据再按Enter键或单击编辑栏中的√按钮确定(×或Esc键取消)。输入有规律的数据时可以采用复制的方式进行:)拖动鼠标选中已输入数据的单元格(区域)用鼠标左键按住当前单元格区域边框右下方的填充柄并沿行列方向拖动Excel将自动填写鼠标拖过的单元格。)填充复制在序列开始或结束的单元格中输入数据并选中此单元格执行“编辑”菜单“填充”选项中的“序列”命令在序列对话框中依次指定数据填充的方向、类型、步长值、终止值等。Excel将序列中的数据视为有规律的数据可以采用复制的方式进行输入。通过执行“工具”菜单中的“选项”命令在选项对话框的“自定义序列”标签中可以自定义填充序列。为确保数据输入的规范和正确可以先执行“数据”菜单中的“有效性”命令来设置单元格允许输入的数据类型、范围以及输入提示信息、输入错误提示信息然后利用“审核”工具栏中的“圈释无效数据”按钮进行检验。⑶Excel公式公式是以等号(=)开始的由数值、单元格引用、名称、函数和运算符组成的集合。利用公式可以进行数学运算、比较工作表数据、合并文本、引用其他单元格。运用公式求解问题时先输入原始数据然后将运算方法转换为Excel能接收的公式输入到存放结果的单元格中。Excel的运算符包括:算术运算符、比较运算符、文本运算符和引用运算符。、本章典型例题分析按要求完成表一中的运算:⑴用绝对引用写出B:B区域中各单元格计算总价的公式总价=单价×数量⑵用相对引用写出G:G区域中各单元格计算总价的公式⑶将K中的公式复制到K:K区域写出区域K:K中各单元格的计算结果。答:⑴在B单元格中输入“=$B$*$A$”、在B单元格中输入“=$B$*$A$”。⑵在G单元格中输入公式“=E*F”然后将其复制到G。⑶区域K:K中各个单元格的值分别是、、。表一ABDEFGIJK使用绝对引用计算使用相对引用计算填充各单元格的值单价数量总价产品名单价数量总价=SUM(I$:$J)特级花茶  一级花茶  、本章作业上机完成教材第页上的题。第二章数据的格式化、本章学习要求⑴应熟悉的内容)网格线的隐藏与显示、三维表格的设置、工作表背景的添加与取消)自定义格式代码设置单元格数据格式)模板、样式的使用页面视图的调整。⑵应掌握的内容)“自动套用格式”格式化工作表)Excel打印区域等打印选项的设置。⑶应熟练掌握的内容单元格边框和底纹的设置、单元格文本和数据的格式化、条件格式的使用。、本章重点难点分析建立工作表后需要对工作表及其数据进行格式化使工作表表达的信息更清晰。对工作表的格式化操作包括对工作表本身以及单元格数据进行格式化。对工作表的格式化操作可以采用Excel提供的内置格式也可以自定义格式。选中需要格式化的区域执行“格式”菜单中的“自动套用格式”命令在自动套用格式对话框作相应的格式配置后可以采用Excel提供的内置格式格式化工作表。选中待格式化的单元格(区域)单击“格式”工具栏中的相应按钮或执行“格式”菜单中的“单元格”命令在单元格格式对话框相应的标签中进行设置后可以自行设置工作表边框底纹、格式化单元格数据。执行“格式”菜单中的“条件格式”命令时还可对当前单元格(区域)的数据进行条件格式。在Excel中既可以将自己经常使用的工作表、计算方式或报表制成模板也可将自行设计的格式保存为样式待需要时引用。格式化的工作表在打印前需要执行“文件”菜单中的相应命令进行打印区域和页面设置。、本章典型例题分析对表二中的工作表格式化成表三所示的样式:⑴建立表名“职工工资发放表”行高为字体为隶书号、居中、红色⑵设置表格外框为粗实线内框为细实现表头为双线⑶将列标题字体颜色设为蓝色各单元格数据居中对齐⑷以货币格式显示应发工资使用条件格式将工资低于的用红色显示。表二 ABCDEFG代号单位姓名基本工资津贴奖金应发工资计算机系王五通信学院刘六表三 ABCDEFG职工工资发放表代号单位姓名基本工资津贴奖金应发工资计算机系王五¥通信学院刘六¥答:⑴单击行号执行“插入”菜单之“行”命令插入新行后执行“格式”菜单“行”选项中的“行高”命令在行高对话框中输入选中A单元格输入“职工工资发放表”选中A单元格在格式工具栏“字体”下拉列表框中选择隶书、在“字号”下拉列表框中选择、在“字体颜色”下拉列表框中选择红色选中A:G区域单击格式工具栏中的“合并及居中”按钮。⑵先选取区域A:G依次在格式工具栏“边框”下拉列表框中选择“所有框线”、“粗匣框线”再选取区域A:G在格式工具栏“边框”下拉列表框中选择“双底框线”。⑶先选取区域A:G在格式工具栏“字体颜色”下拉列表框中选择“蓝色”再选中区域A:G单击格式工具栏中的“居中”按钮。⑷选中区域G:G先单击格式工具栏中的“货币样式”按钮再执行“格式”菜单中的“条件格式”命令在条件格式对话框中设置“单元格数值小于字体为红色”。、本章作业上机完成教材第页上的、、题。第三章公式及函数的应用、本章学习要求⑴应熟悉的内容)错误单元格的追踪各种错误信息可能产生的原因)公式的循环引用循环引用迭代次数的更改)RC样式、标志的概念Excel帮助系统的使用)常用的数学三角函数、日期时间函数、信息函数、查询引用工作表函数。⑵应掌握的内容)数据公式、常量数组、二维数组的使用)单元格、公式、常量的命名和使用)自动求和按钮Σ及IF、SUM、SUMIF、AVERAGE、AVERAGEA、AND、OR、COUNT、COUNTA、COUNTBLANK、COUNTIF、MOD、RAND、ROUND、INT、TRUNC、MAX、MAXA、MIN、MINA函数的使用。⑶应熟练掌握的内容)A引用样式下的相对引用、绝对引用和混合引用)函数的语法、调用规则和输入方法。、本章重点难点分析⑴数组公式公式是以等号(=)开始的由数值、单元格引用、名称、函数和运算符组成的集合。一般公式只能对一个或多个参数进行计算返回一个结果数组公式则可以同时处理两组或两组以上的数据得出一个或多个结果。数组公式中的参数必须是矩阵形式的区域数组或常量数组。输入数组公式时先确定单元格区域输入公式内容后按CtrlShiftEnter键。编辑时不能直接输入数组公式标志{}存放区域的大小、形状要满足运算结果的要求数组公式所涉及到的源数据区域或结果区域应作为整体进行操作修改后按CtrlShiftEnter才能生效。在数组公式中直接输入的数据构成数组常量。数组常量中的数据要用大括号{}括起不同列的数据用逗号分隔、不同行的数据用分号分隔。数组公式中参数的维数与运算类型不匹配时Excel将自动扩展维数较低的参数。使用二维数组可以处理多行和多列的数据。⑵错误信息当公式中参数的个数、类型、取值超过规定的范围导致Excel不能正确求解出结果时Excel将给出相应的出错信息。引起错误的可能不是公式本身此时可以使用审核工具查找向公式提供错误值的单元格。方法是:执行“工具”菜单“审核”选项中的“显示审核工具栏”命令审核工具栏显示后再选中出现错误值的单元格单击审核工具栏中的“追踪错误”按钮。蓝色箭头表明引用单元格和从属单元格同时位于当前工作表中(箭头所指为从属单元格)红色箭头表明引用单元格出现错误值“表格图表黑箭头”表明引用单元格和从属单元格位于不同的工作表或工作簿中。双击箭头可以选定箭头另一端的单元格。⑶循环引用循环引用是指某个公式直接或间接引用了公式本身所在的单元格。计算这类公式时Excel将使用前一次迭代的结果来计算循环引用的每个单元格。更改迭代次数的方法是:执行“工具”菜单中的“选项”命令选中选项对话框“重新计算”标签中的“反复操作”复选框在“最多迭代次数”编辑框中键入迭代次数在“最大误差”编辑框中键入所需的数值。⑷名字的应用)单元格(区域)的引用引用的作用在于标识工作表中的单元格(区域)指明数据所在的位置。通过引用可以在公式中使用不同单元格的数据或重复使用同一单元格中的数值。引用方式分为:绝对引用、相对引用和混合引用。跨行列复制公式时绝对引用的行列不会自动调整而相对引用的行列将作相应的调整。新公式中相对引用行列的改变量等于目标单元格相对于源单元格的行列增量。默认状态下Excel采用A引用样式:用列标A~IV标志列用行号~标志行。引用单元格时顺序输入列标和行号引用区域时输入左上角单元格引用冒号右下角单元格引用。执行“工具”菜单的“选项”命令选中选项对话框“常规”标签中“设置”下的“RC引用样式”复选框后可以采用RC引用样式。它用“R行号C列序”来标识单元格的位置RC表示绝对引用RC表示相对引用。数据区域的首行或最左列常常含有描述性的文本(又称标志)。执行“工具”菜单中的“选项”命令选中选项对话框“重新计算”标签“工作簿选项”下的“接受公式标志”复选框后在公式中可以使用这些标志来引用相应的数据。列标引用全列数据行标引用全行数据“列标空格行标”引用列与行相关的单元格。也可以先用描述性的文本为单元格区域、公式或常量命名然后利用名字来引用已命名的单元格区域、公式或常量。)名字的定义和应用名字是代表单元格、单元格区域、公式或常量的字符串在公式中使用名称可以使公式结构更易理解。命名的方式有多种:①执行“插入”菜单“名称”选项中的“定义”命令在定义名称对话框中的“当前工作簿的名称”编辑框输入名字在“引用位置”编辑框输入引用区域。②选定需要命名的区域(包含行列标志)执行“插入”菜单“名称”选项中的“指定”命令在指定名称对话框中指定用于命名的标志位置。③选定需要命名的单元格(区域)单击编辑栏左侧的名称框进入编辑状态输入名字后按ENTER键。已定义的名字是对单元格区域的绝对引用可以在相同的工作表、不同的工作表、不同的工作簿中引用。在公式中使用名字时可以直接输入也可通过执行“插入”菜单“名称”选项之“粘贴”命令、在粘贴名称对话框中进行选择。⑸函数Excel函数本质上是一些预定义的公式它将接收到的参数按特定的顺序或结构进行运算返回运算结果。Excel函数由函数名和参数列表两部分组成其语法是:函数名(参数参数…)。Excel函数可以应用在公式、表达式、函数中可直接输入函数也可使用函数向导。在公式的编辑过程中需要使用函数向导时可以先单击编辑栏左侧的函数下拉列表框然后选择需要的函数(或单击“其它函数”从粘贴函数对话框中选择)、利用“公式选项板”输入相关参数。直接输入“=”开始编辑公式时编辑过程中还可以通过执行“插入”菜单中的“函数”命令(或单击工具栏fx按钮)从粘贴函数对话框中选择需要的函数。使用函数的过程中遇到问题时可以直接按F键或单击常用工具栏中的OFFICE助手工具按钮?或执行帮助菜单中的帮助命令进入Excel的帮助界面查看相关的帮助信息。、本章典型例题分析⑴A引用样式下$A$、A$、$A、A、:、:、H:H、H:J、A:E表示什么意义?答:$A$绝对引用第一行第A列的单元格A$绝对引用第一行相对引用第A列的单元格$A相对引用第一行绝对引用第A列的单元格A相对引用第行第A列的单元格:相对引用第行所有的单元格:相对引用第~行所有的单元格H:H相对引用第H列所有的单元格H:J相对引用第H~J列所有的单元格A:E相对引用第行到第行A~E列之间的×单元格。⑵按要求计算表四所示工作表中的数据:)利用数组公式计算奖金奖金金额为基本工资的结果取整)先指定列标题为列数据名称再利用数组公式计算奖金)利用常用工具栏中的自动求和按钮Σ计算应发工资)用函数在G单元格中统计应发工资不足¥的职工人数。答:)选取区域F:F输入公式“=ROUND(*D:D,)”后按CtrlShiftEnter键。)先选定区域A:G执行“插入”菜单“名称”选项之“指定”命令在指定名称对话框中指定标志的位置(首行)再选取区域F:F输入公式“=ROUND(*基本工资,)”后按CtrlShiftEnter键。)选取单元格区域G:G单击常用工具栏中的自动求和按钮Σ。)选取单元格G输入公式“=COUNTIF(应发工资,"<")”。表四 ABCDEFG职工工资发放表代号单位姓名基本工资津贴奖金应发工资信息中心张三计算机系王五通信学院刘六应发工资不足¥的职工人数、本章作业上机完成教材第页上的题。第四章图表处理、本章学习要求⑴应熟悉的内容)Excel图表的基本组成、类型)三维图表的格式化、组合图的建立、数据点标记的修改、文本或剪贴画的插入)数据地图的创建和格式化。⑵应掌握的内容修改和格式化已建图表、为已建图表添加趋势线、插入文本批注。⑶应熟练掌握的内容Excel处理图表的方式和Excel图表的建立。、本章重点难点分析Excel图表主要由图表区、绘图区、数据点、数据系列、网格线、数值轴、分类轴、刻度线、图例、标题等图表对象组成。不同的图表类型组成元素略有不同。Excel图表可分为标准类型和自定义类型两大类。Excel对图表有两种不同的处理方式:一种是嵌入式图表它将图表作为一个对象直接嵌入到数据所在的工作表中另一种是图表工作表它将图表作为特定的工作表。选中图表执行“图表”菜单中的“位置”命令可实现嵌入式图表←→图表工作表间的互换。Excel图表与源工作表数据是相链接的工作表数据发生变化时图表将自动更新。建立图表前必须建立相关的源数据工作表。以默认的图表类型和格式创建图表时先选定数据源区域然后按F键创建工作表图表或单击工具栏中的“默认图表”按钮创建嵌入式图表。也可以先执行“插入”菜单中的“图表”命令然后在“图表向导”对话框中依次设置创建图表的类型、数据源区域、数据系列及产生方式、图表格式和插入方式。图表建立后可以使用图表工具栏设置图表对象的格式更改图表的类型、数据系列的产生方式和文字的方向隐藏或显示图例、数据表。也可以使用图表菜单对已建图表进行修改。选中图表对象后菜单栏中的数据菜单将被图表菜单所代替利用图表菜单中的命令可修改图表的类型和数据源更改图表选项和图表方式添加数据系列和趋势线等。执行“格式”菜单中与所选图表对象相同的命令可以对图表对象进行格式化。选中图表后单击“绘图”工具栏中的“文本框”按钮可在图表中加入一些说明性文字。、本章典型例题分析利用趋势线法求经验公式中的常数a和b使它能与表五给出的数据相拟合。表五ABCDEFGHIxy答:)绘制XY散点图选取区域A:I中的某个单元格执行“插入”菜单中的“图表”命令在“图表向导”对话框中依次指定图表类型“XY散点图”中的子类型“散点图。比较成对的数值”、数据源区域A:I、数据系列按行方式产生、图表格式和插入方式。)添加趋势线选中图表执行“图表”菜单中的“添加趋势线”命令在“添加趋势线”对话框的“类型”标签中选择趋势预测回归分析类型为“指数”型在“添加趋势线”对话框的“选项”标签中选中“显示公式”复选框后确定。)分析图表趋势线上的公式就是所求的方程如图其中a=、b=。、本章作业上机完成教材第页上的题。第五章数据分析处理、本章学习要求⑴应熟悉的内容)Excel数据的排序顺序及对特殊编号的排序)如何进行分类汇总和分级显示区分查找与筛选、多重分类汇总与嵌套分类汇总)数据透视图的组成与建立链接和嵌入的异同。⑵应掌握的内容)数据清单的概念及对工作表的式要求)如何实现自定义排序和多关键字排序)数据记录单的使用)数据透视表的组成、建立和修改)通过选择性粘贴建立数据链接通过合并计算汇总不同工作簿中的数据。⑶应熟练掌握的内容)对数值型、文本型数据的排序)Excel条件的表示如何对数据清单进行自动筛选、高级筛选)如何建立分类汇总、使用分级显示查看数据。、本章重点难点分析⑴数据清单Excel中的数据清单是指满足一定要求的工作表这类工作表中同一行数据存在一定的关系、同一列数据类型相同。查询、排序或汇总数据时Excel自动将数据清单视作数据库处理数据清单中的列对应数据库中的字段列标志对应数据库的字段名行对应数据库的记录。Excel工作表要成为数据清单必须满足:相似项位于同一列且首行使用带格式的列标使用单元格下边框区分标志行和其他行数据每张工作表只能有一个数据清单作为清单的数据区域与其他数据之间至少要留有一个空列或空行而区域内则不能有空行和空列在单元格中输入文本数据时文本前面或后面不能输入多余的空格关键数据放于清单的顶部或底部显示所有的行和列。⑵数据排序Excel可以根据单元格中的数值进行升序和降序排列。Excel升序的排列次序为:数字→字母→逻辑值→错误值→汉字→空白单元格。降序排序时除了空格在最后之外其他数据的排列次序反转。排序时先选中数据清单中的某个非空单元格然后执行“数据”菜单“排序”命令或单击常用工具栏中的“升序”或“降序”工具按钮。使用菜单命令进行排序时可以设置主次关键字、自定义排列次序、指定排列方向、是否区分大小写、按拼音还是笔画对汉字进行排序等参数使用工具按钮则以默认方式排序。自定义排序次序只适用于主要关键字。排序时一次最多只能设置三个关键字段。多于三个关键字段时需进行多次排序排序的准则是先次后主最后排序的是主关键字。对于用连字符()分隔的账号等特殊编号排序时应使编号中的各个部分长度相同或者将编号的不同部分输入不同的列进行多关键字排序。⑶条件区域和条件表示Excel条件可以是:文本、数值、比较式、结果是逻辑值的计算公式这些条件可以出现在公式、函数和专门建立的条件区域中。条件区域是指一组存放查找条件的单元格区域由条件标记行和条件行组成。条件区域中同一列中的多个条件构成“或”条件、同一行中的多个条件构成“与”条件。使用公式创建条件区域时不能用清单中的列标作为条件标记公式中用关键字段作比较时必须使用该列第一个数据单元格的相对引用其它单元格引用必须用绝对引用。查找文本时可以使用通配符?和*。⑷查找与筛选在Excel中可以通过记录单、自动筛选和高级筛选等方式从数据清单中查找或筛选出符合一定条件的数据行。查找可以在不改变源工作表记录的情况下从数据清单中提取出满足条件的数据行。筛选则将原数据清单中不满足条件的行暂时隐藏只显示满足条件的行。数据记录单是一种对话框利用它可以查找、编辑、删除、添加一行完整的记录。在Excel中只要数据区域的首行具有列标志就可以先选取数据区域中的某个单元格然后执行“数据”菜单中的“记录单”命令来查找或编辑数据。选取数据区域中的某个单元格执行“数据”菜单“筛选”选项之“自动筛选”命令在筛选字段列标志的下拉列表框中设置筛选条件后可对清单数据进行自动筛选。对同一列数据应用两个以上的筛选条件或者需要将筛选后的记录复制到其他位置时须利用高级筛选。首先建立条件区域(一般位于数据清单的上方与数据清单之间至少留一个空白行)然后单击数据清单中的某一单元格执行“数据”菜单“筛选”选项之“高级筛选”命令在高级筛选对话框中依次设置保存筛选结果的方式、数据区域、条件区域等参数。⑸分类与汇总分类汇总是一种汇总数据的方法它通过分级显示和分类汇总从大量信息中提取有用信息。分类汇总的前提是:数据区域的每一列都具有列标题并按分类汇总关键字段排序。建立分类汇总时先以汇总列为关键字对数据清单进行排序然后单击要数据清单中的某一单元格执行“数据”菜单中的“分类汇总”命令在分类汇总对话框中依次设置分类字段、汇总方式、需要汇总的项、显示方式。分类汇总后单击工作表左侧增加的分级显示符号中的按钮可以显示隐藏本级的全部明细数据单击行分级按钮、、…可以显示指定分级的明细数据。以同一关键字作分类字段进行的多次分类汇总称多重分类汇总以不同的关键字作分类字段进行的多次分类汇总称嵌套分类汇总。建立嵌套分类汇总前必须对数据清单排序第一级汇总关键字作排序的第一关键字第二级汇总关键字作排序的第二关键字依次类推。⑹数据透视表数据透视表是一种用于快速汇总大量数据的交互式表格可以随时调整其行、列、页字段及字段项来组织、汇总数据。通过数据透视表可以查看数据来源、显示数据项的明细数据、制作数据透视图表、创建分页显示和多重数据透视表。它主要由行字段、列字段、页字段、数据字段、数据区、字段项和字段下拉箭头等元素组成。建立数据透视表时先单击数据清单的某一单元格然后执行“数据”菜单中的“数据透视表和图表报告”命令在向导对话框中依次选择数据源“Excel数据清单”、报表类型“数据透视表”、数据源区域、透视表的显示位置设置报表的布局。数据透视表建立后可以修改透视表的概要函数、显示方式及版式增减行、列中的数据项调整字段、重新组织数据透视表。⑺数据的链接、嵌入与合并计算Excel可以在与其它支持OLE的程序之间复制信息信息既可以复制为链接对象也可以复制为嵌入对象。在目标文件中作为链接对象的信息与源文件保持着的联系源文件中相应信息更新时目标文件中的链接对象也随之更新。嵌入对象则不同目标文件中嵌入的信息是目标文件的一部分与源文件没有联系需在目标文件中打开相应的应用程序进行编辑。一个工作簿可以通过链接共享另一个工作簿的数据链接的对象可以是单元格、区域、命名公式、常量或工作表。源工作簿、目标工作簿可以单独打开或关闭源工作簿先于目标工作簿打开时目标工作簿打开时将自动使用源工作簿中的数据更新。在Excel中可以通过“选择性粘贴”建立链接方法是:先同时打开源文件和目标文件并在源文件中选取、复制相关的信息然后在目标文件中执行“编辑”菜单中的“选择性粘贴”命令在对话框中单击“粘贴链接”、选取相应的格式后确定。在Excel中还可以合并计算来源于同一工作簿的不同工作表或不同工作簿中的数据。方法是:先打开存放原始数据工作表的工作簿和建立或打开汇总工作簿选取存放合并计算结果区域的左上角单元格然后执行“数据”菜单中的“合并计算”命令在合并计算对话框中依次指定汇总函数、源数据区域。、本章典型例题分析⑴对表六中的数据分别进行下列操作:)先自定义填充序列“通讯学院、计算机系、信息中心”然后以单位为关键字对数据进行自定义排序)使用记录单删除代号为的职工记录)使用自动筛选功能筛选出应发工资处于前两位的职工记录。)使用高级筛选功能筛选出应发工资低于平均水平的记录。表六ABCDEFG代号单位姓名基本工资津贴奖金应发工资信息中心张三计算机系王五通信学院刘六答:)先执行“工具”菜单中的“选项”命令选中选项对话框中的“自定义序列”标签选择“自定义序列”列表框中的新序列在“输入序列”编辑列表框中依次输入通讯学院、计算机系、信息中心每输入一个元素后按ENTER键整个序列输入完毕后确定。然后执行“数据”菜单中的“排序”命令在排序对话框中选择单位作主要关键并单击选项按钮从排序选项对话框的“自定义排序次序”下拉列表框中选取自定义顺序后确定。)单击数据清单中的某个单元格执行“数据菜单”中的“记录单”命令在对话框中单击条件按钮在空记录单的代号字段中输入单击下一条按钮找到待删除的记录后单击删除按钮。)单击数据清单中的任一单元格执行“数据”菜单“筛选”选项中的“自动筛选”命令从应发工资右侧的下拉列表中选择“前个”在对话框中设置显示最大的项。)如表七先在I:I建立条件区域I为空I输入“=G<AVERAGE($G$:$G$)”单击数据清单A:G中的任一单元格执行“数据”菜单“筛选”选项中的“高级筛选”命令在对话框中单击“将筛选结果复制到其他位置”按扭并输入数据区域引用A:G、条件区域引用I:I、存放结果的区域引用A后确定。表七ABCDEFGI代号单位姓名基本工资津贴奖金应发工资信息中心张三¥TRUE计算机系王五¥通信学院刘六¥代号单位姓名基本工资津贴奖金应发工资通信学院刘六¥信息中心张三¥⑵对表八中的数据进行下列操作:)以分店为关键字创建分类汇总对销售求和只显示前级汇总数据。)以分店为页字段、运动为行字段、季度为列字段建立数据透视表对销售求和。答:)选取分店数据列中的某个单元格单击常用工具栏中的升序按钮对数据清单进行排序执行“数据”菜单中的“分类汇总”命令在分类汇”对话框中依次设定以分店作分类字段、以求和作汇总函数、以销售作汇总项并选择替换当前分类汇总、汇总结果总显示在数据下方等选项后确定单击工作表左侧分级显示符号中的行分级按钮结果如图。)单击数据清单的某一单元格执行“数据”菜单中的“数据透视表和图表报告”命令在向导对话框中依次选择数据源“Excel数据清单”、报表类“数据透视表”、数据源区域A:D、数据透视表的显示位置“新建工作表”后单击完成按钮设置报表布局将分店、运动、季度的字段按钮从“数据透视表”工具栏中分别拖到页、行、列字段区域中将销售字段拖动到数据区域结果图。表八ABCD销售统计表分店运动季度销售一分店高尔夫三季度¥,一分店高尔夫四季度¥,一分店网球三季度¥一分店网球四季度¥,二分店高尔夫三季度¥,二分店高尔夫四季度¥,二分店网球三季度¥,二分店网球四季度¥,图图、本章作业上机完成教材第页上的题。第六章数据分析工具的应用、本章学习要求⑴应熟悉的内容)加载宏的概念和安装方法)引用单元格、从属单元格的基本概念能利用审核工具追踪单元格)模拟运算表、单变量求解、方案分析、规划求解的基本概念和应用)使用各种数据分析工具的一般步骤。⑵应掌握的内容)如何设置单元格数据的有效性和圈释无效数据)规划求解的步骤及如何使用“规划求解”求解极值问题。⑶应熟练掌握的内容单变量模拟运算表、双变量模拟运算表的创建单变量求解的应用。、本章重点难点分析⑴Excel加载宏加载宏是可以安装到计算机中的程序组件为Excel添加命令和函数、扩展Excel功能。默认安装OFFICE时许多分析工具如规划求解等都不会被安装。要使用这些工具必须先安装提供这些分析工具的加载宏。⑵模拟运算表Excel模拟运算表实质是一个显示公式结果随公式中某些变量变化的单元格区域在Excel中可以构建单变量和双变量两种模拟运算表。模拟运算表为设计、对比不同方案的运算结果带来了方便。建立模拟运算表时先要在工作表中输入引用输入单元格的公式及运算时用以替代输入单元格的数据并选中这些单元格区域然后执行“数据”菜单中的“模拟运算表”命令在模拟运算表对话框中输入引用的单元格。⑶单变量求解单变量求解的实质是求解一元方程。进行单变量求解时Excel不断改变可变单元格中的数值直到引用该单元格的公式(置于目标单元格)得出符合要求的结果(目标值)。运用单变量求解时先要在工作表中建立数据表指定可变单元格和在目标单元格中输入公式然后执行“工具”菜单中的“单变量求解”命令在单变量求解对话框中依次指定目标单元格、目标值和可变单元格。⑷规划求解规划求解是在一定的限制条件下利用数学方法进行运算使对前景的规划达到最优的方法。它研究的内容包括:在现有的人力、物力资源条件下如何统筹安排使完成的任务最多或者为完成既定的任务如何统筹安排使消耗的人力、物力资源最少其实质是求解极值问题。进行规划求解时Excel将对工作表中与目标单元格公式直接或间接有联系的一组单元格进行调整为目标单元格中的公式找到最优化的结果。求解过程中值可以修改的单元格为可变单元格目标单元格中的公式为目标函数为求解问题设置的限制条件称为约束条件。进行规划求解时先要在工作表中建立规划求解模型确定目标单元格、可变单元格和约束条件之间的数量对应关系然后单击模型中的某一单元格执行“工具”菜单中的“规划求解”命令在“规划求解参数”对话框中依次指定目标单元格、目标值范围、可变单元格和约束条件在“求解规划结果”对话框中指定保存规划求解报告的方式。、本章典型例题分析⑴某开发商想贷款万元建立一个山林果园现有多种不同的利息和不同的年限可供选择分别用单变量和双变量模拟运算表计算各种情况下的月偿还额。利息可以在、、、、中选择偿还年限可以在年、年、年、年、年中选择。答:)使用单变量模拟运算表①如图在工作表中建立数据区域。在B中输入贷款金额在B:B区域中输入贷款利息在C:G区域中输入贷款年限。②在C中输入公式“=PMT(B,C,$B$)”在D中输入公式“=PMT(B,D,$B$)”在E中输入公式“=PMT(B,E,$B$)”在F中输入公式“=PMT(B,F,$B$)”在G中输入公式“=PMT(B,G,$B$)”。③选定区域B:G执行“数据”菜单中的“模拟运算表”命令在模拟运算表对话框中“输入引用列的单元格”$B$后确定。图⑵使用双变量模拟运算表图①如图在B输入贷款金额在B:B输入贷款利息在C:G输入贷款年限。②在B单元格中输入公式“=PMT(B,A,$B$)”。③选定单元格区域B:G执行“数据”菜单中的“模拟运算表”命令在模拟运算表对话框中“输入引用行的单元格”$A$、“输入引用列的单元格”$B$。⑵已知贷款利率为每月偿还万元需要多少年才能还清万元贷款?)如图在工作表中建立数据区域输入年利息、月偿还额万元、贷款金额(由PV函数产生))执行“工具”菜单中的“单变量求解”命令在单变量求解对话框中输入目标单元格$B$、输入目标值、指定可变单元格$B$后按钮。图⑶设A、B两个发货点分别有同种货物吨和吨它们供应需求量为吨、吨、吨的甲、乙、丙三个收货点其运费如表九所示。应如何分配运量才能使总运费最低?表九ABCDE运费甲收货点乙收货点丙收货点货物总量A发货点¥¥¥B发货点¥¥¥需求量答:)按表十建立规划求解模型。在A:E建立一个用于统计运货量和收货量的数据区域在A:B建立一个用于计算总成本的数据区域命名B:D为运量。)进行规划求解。单击模型中的任一单元格执行“工具”菜单中的“规划求解”命令。)如图在“规划求解参数”对话框的“目标单元格”编辑框中键入B、在等于标题下单击“最小值”选项、在“可变单元格”编辑框中键入可变单元格的名称“运量”、添加约束条件$B$:$D$=$B$:$D$、$E$:$E$=$E$:$E$、运量>=后单击“求解”按钮。表十ABCDE运费甲收货点乙收货点丙收货点货物总量A发货点¥¥¥B发货点¥¥¥需求量运量甲收货点乙收货点丙收货点运货量A发货点=SUM(B:D)B发货点=SUM(B:D)收货量=BB=CC=DD成本=SUM(B:D*B:D)图、本章作业⑴分别用单变量模拟运算表和双变量模拟运算表计算利率、、下的年、年、年还清的万元住房贷款的每月还贷额。⑵已知贷款利率为、还贷期限为年若每年偿还万元可以一次贷款多少元?⑶有一线性方程如下:利用规划求解x、x计算的最优解使有目标函数S=xx值最大。第七章Excel与财务分析、本章学习要求⑴应熟悉的内容常用折旧函数、投资函数、偿还率函数和债券分析函数的使用方法。⑵应掌握的内容PMT、PV、FV、NPER、RATE函数的使用。、本章重点难点分析⑴PMT函数计算采用固定利率、等额分期付款方式投资(或贷款)的每期付款或收益额。语法:PMT(利率付款总期数现值未来值付款类型)⑵PV函数计算采用固定利率、等额分期付款方式投资(或贷款)的现值。语法:PV(利率付款总期数每期付款或收益额未来值付款类型)⑶FV函数计算采用固定利率、等额分期付款方式投资(或贷款)的未来值。语法:FV(利率付款总期数每期付款或收益额现值付款类型)⑷NPER函数计算采用固定利率、等额分期付款方式投资(或贷款)的总期数。语法:NPER(利率每期付款或收益额现值未来值付款类型)⑸RATE函数计算采用固定利率、等额分期付款方式投资(或贷款)的利率。语法:RATE(付款总期数每期付款或收益额现值未来值付款类型预期利率)、本章典型例题分析⑴偿还期个月、年利率的¥,贷款的月支付额是多少?PMT(,,)=¥,⑵回报率、购买成本¥,、今后年每月末收益¥的保险金的现值是多少?PV(,*,,,)=¥,结果是负值表示支出现金。现值(¥,)的小于实际支付(¥,)表明这是一项不合算的投资。⑶将¥本金以年利率存入银行帐户并在未来个月的每月初存入¥一年后该帐户的存款额是多少?FV(,,,,)=¥⑷年利率、每月偿还¥,需要多少月才能偿还¥,的银行贷款?NPER(,,)=月⑸月偿还额为¥,的年期¥,贷款的利率是多少?RATE(*,,)=、本章作业⑴设年利率为若要在年后达到$,存款现在起每月应在银行存多少钱?⑵折现率为、未来年每年末收益¥,的现值是多少?第八章数据库及外部数据的获取、本章学习要求⑴应熟悉的内容)Excel的数据库特征及访问外部数据库的条件)MSQuery的作用、安装及使用方法)Excel与FoxPro、Access、文本文件进行数据交换的方法。⑵应掌握的内容)数据库函数D函数的语法结构)查阅向导的使用方法)建立MSQuery数据源的方法能正确使用查询向导查询外部数据库。⑶应熟练掌握的内容通过打开和保存的方式实现Excel与其他文件类型的相互转换。、本章重点难点分析⑴数据库函数D函数Execl提供了个D函数用于分析存储在数据库中的数据。D函数语法:Dfunction(数据区域数据列条件区域)数据库函数返回数据清单中与条件区域设定条件相匹配的信息。参数“数据区域”代表构成数据库的单元格区域可以是包含列标志行的区域引用或名称“数据列”指定函数所使用的数据列可以是带引号的列标志或数据列在数据清单中的列序号“条件区域”用来设定函数的匹配条件可以是条件区域的引用形式或名称。条件区域中可以位于工作表中除数据库下方之外的任意位置但不能与数据库相重迭至少包含数据库中的一个列标志和条件单元格。⑵查阅向导的使用在数据库操作中经常需要查找满足一定条件的数据记录在Excel中可以通过记录单、自动筛选、MATCH函数和查阅向导来完成。使用查阅向导时先选中数据库中某个非空单元格然后执行“工具”菜单“向导”选项之“查阅”命令依次在查阅向导对话框中设定包括行列标志的数据库区域、待查数据所处行的行标志和所处列的列标志、选择显示结果的方式以及指定显示结果的单元格。⑶MSQuery数据源MSQuery是一个十分灵活而且功能强大的应用程序既可以单独使用也可以在Excel中调用将外部数据库中的数据导入Excel工作表。MSQuery通过数据源、ODBC驱动程序访问外部数据库它可以根据条件筛选数据所导入的数据自动更新并且可以通过关键字连接多个外部数据表。使用“查询向导”查询外部数据库时执行“数据”菜单“获取外部数据”选项子菜单之“新建数据库查询”命令先选择或建立数据源然后依次在“查询向导”对话框中指定所需要的列、筛选条件、排序方式及保存结果的方式。⑷Excel与其他文件类型的转换Excel除了使用MSQuery查询各种外部数据库文件外还可以打开各种类型的文件以及将Excel工作簿保存为其他格式的文件。、本章典型例题分析⑴用DCOUNT函数统计电视产量介于到之间的记录数。表十一ABCDEF某电器厂产品利润数据库产品类型单价产量成本利润电视英寸空调窗机电冰箱H电视英寸空调壁挂机电冰箱H电视英寸答:)先在数据区域上方建立条件区域如表十二)在A单元格中输入“电视产量介于到之间的记录数”选中A~F单元格区域单击格式工具栏中的‘合并及居中’按钮在G单元格中输入公式:=DCOUNT(A:F"产量"A:G)将得到运算结果。表十二ABCDEFG产品类型单价产量成本利润产量电视  >  <电视产量介于到之间的记录数⑵在Excel中打开一个扩展名为txt的文本文件处理后将其保存为Web文件。答:)单击常用工具栏中的“打开”按钮在打开对话框的“文件类型”列表中选择“文本文件”在“查找范围”列表中找到需要打开的文件单击“打开”按钮。)在Excel中完成处理操作后单击“文件”菜单中的“另存为”命令在另存为对话框的“查找范围”列表中选择保存文件的位置、在“文件名”编辑框为文件命名、在“保存类型”列表中选择“Web页”后保存。、本章作业⑴用DCOUNT函数统计成绩单中外语不及格的人数。表十三ABCDEFG成绩单姓名系别性别计算机外语哲学平均分王五电子工程男朱键机械制造男马七计算机男牛平通信工程女⑵在Excel中建立一个工作簿文件将它保存为纯文本文件。第九章宏与VBA、本章学习要求⑴应熟悉的内容宏的基本概念和VBA程序设计的基本语法。⑵应掌握的内容如何通过“工具”菜单“自定义”命令修改、添加自定义菜单或工具按钮。⑶应熟练掌握的内容宏的录制、保存和运行方法。、本章重点难点分析⑴Excel宏Excel宏是由用户编写或录制的存储在VB模块中的程序它由一系列命令和函数构成。在Excel中可以用Excel提供的宏录制工具录制宏也可以使用VB编辑器编写宏。宏录制工具是Excel提供的一种软件工具它能记录用户的操作、并将其转换成VBA程序代码。录制宏之前应先制订计划、确定宏所要执行的步骤和命令。录制宏时先激活某个空工作表执行“工具”菜单“宏”选项中的“录制新宏”命令在录制新宏对话框中键入宏的名称、指定快捷键和存放位置在工作表中执行需要记录的操作后单击“停止录制”工具栏上的“停止录制”按钮。Excel的宏总是保存在工作簿中的录制的新宏可以保存在当前工作簿、新建工作簿或个人宏工作簿Personalxls中。保存在工作簿中的宏只有在该工作簿打开时才能供其他工作簿引用Excel启动时会自动打开并隐藏个人宏工作簿。已录制的宏可以在Excel中运行也可以在VB编辑器中运行。执行宏的过程中可以按ESC键中止。执行宏时可以使用快捷键或执行“工具”菜单“宏”选项之“宏”命令在宏对话框中指定需要运行的宏或单击工具栏上预先指定宏的自定义工具按钮或单击改变工作表中预先指定宏的控件。⑵自定义菜单或工具Excel菜单显示了一系列命令通过它可以执行所有的Excel指令。一般情况下菜单位于屏幕顶部的菜单栏中。菜单中不同的标记具有不同的含义:浅灰色菜单项表示不具备此命令的执行条件暂时禁用向右的黑三角形表示该菜单项有下一级子菜单省略号…表示执行该菜单项会弹出一个设置对话框√表示该菜单处于显示状态再次单击则会关闭。Excel除

用户评价(0)

关闭

新课改视野下建构高中语文教学实验成果报告(32KB)

抱歉,积分不足下载失败,请稍后再试!

提示

试读已结束,如需要继续阅读或者下载,敬请购买!

文档小程序码

使用微信“扫一扫”扫码寻找文档

1

打开微信

2

扫描小程序码

3

发布寻找信息

4

等待寻找结果

我知道了
评分:

/26

Excel高级应用课程学习指导

VIP

在线
客服

免费
邮箱

爱问共享资料服务号

扫描关注领取更多福利