《Excel高级应用》自学指导书

《计算机科学与技术》函授本科段《Excel高级应用》自学指导第五学期编者：刘汉英桂林工学院电计系计算机教研室一课程简介“Excel高级应用”是一门基础课。

Excel是当前最流行的电子表格处理软件之一，也是Office办公套装软件的一个重要组成部分。

它既可以制作普通的表格，又可以实现简单的加、减、乘、除运算，还能够通过内置的函数完成诸如逻辑判断、时间运算、财务管理、信息统计、科学计算等复杂的运算。

Excel还可以将数据表格以各式各样的图表形式展示出来，或者进行排序、筛选和分类汇总等类似数据库的操作。

二学习目标要求学生通过本课程的学习，学会制作出美观大方的表格，利用公式与函数功能对表格中的数据进行处理，制作和修饰图表，进行数据清单的管理以及与其他软件综合应用等。

三课程要求本课程结合具体的实例详细介绍了Excel的基本知识和高级应用技巧，要求学生通过学习掌握：制作表格，认识处理工作表、快速输入数据、编辑表格和排版表格的应用技巧；在工作表中绘制图形、插入图片、添加艺术字，制作图文并茂的报表，并将其打印出来；利用公式与函数对数据进行计算；格式化图表；利用记录单管理数据，对数据清单进行排序、筛选、分类汇总、使用数据透视表重新组织数据清单和统计数据；利用单变量求解、模拟运算表、使用方案以及规划求解获取所需的结果；保护工作簿、保护工作表、在同一工作表中让不同用户拥有不同的权限、保护单元格以及共享工作簿；宏的录制、运行和管理方法；利用Visual Basic编写一些简单的程序来提高工作效率；在Excel中插入数学公式、导入文本文件、数据库、Access、Web 页的数据、邮件合并和使用超链接等。

四教材《中文Excel应用教程》王诚君王鸿编清华大学出版社 2005年2月第一版五六考核方式：作业20%+考勤20%+考试60%第1章创建一个简单的表格㈠熟练掌握内容:Excel窗口的组成、Excel的基本概念、数据的输入方法、区域选定方法、设置工作表的格式。

㈡难点:选定不相邻的单元格区域。

㈢要点：1.Excel窗口由标题栏、菜单栏、工具栏、编辑栏、工作表、工作表标签、状态栏、任务窗格组成。

2.工作簿是Excel用来计算和存储数据的文件，扩展名为.xls，可含有一个或多个工作表。

工作表是工作簿的重要组成部分，又称为电子表格。

默认情况下，一个新的工作簿含有3个工作表。

每张工作表包括256列和65536行，每一列的列标由A、B、C等字母表示；每一行的行号由1、2、3等数字表示，行与列的交叉处形成一个单元格，是Excel进行工作的基本单位，单元格D4指位于第D列和第4行交叉点上的单元格。

若要表示一个连续的单元格区域，可用该区域左上角和右下角单元格表示，中间用冒号(:)分隔。

3.每张工作表只有一个单元格是活动单元格。

只有在活动单元格中才能输入和编辑数据。

4.如果要使单元格中的数据强行换到下一行，可以按Alt+Enter组合键。

5.在输入数字时加上一个单撇号(’),Excel会将该数字作为文本，使它沿单元格左边对齐。

默认情况下，数字沿单元格右边对齐。

为了避免将输入的分数视为日期，应该在分数前加上0和空格。

如输入“0 1/2”。

如果要输入当天的日期，按Ctrl+;组合键，如果要输入当前的时间，按Ctrl+Shift+;组合键。

6.如果要选定整行，单击该行的行号，如果要选定整列，单击该列的列标。

如果要选定不相邻的行或列，先选定第一行或第一列，按住Ctrl键，单击其他的行号或列标。

7.调整列宽和行高可以使用拖动法和菜单命令。

㈤作业：1．2．5第2章制作工作簿中的其他工作表㈠熟练掌握内容：指定“文件”菜单的底部列出的文件数、选定多个工作表、移动和复制工作表、单元格数据、查找与替换数据、使用条件格式、隐藏行或列。

㈡难点：工作组、快速填充数据、设置数据有效性、设置数字格式、拆分窗口、冻结行或列。

㈢要点当选定多个工作表时，在标题栏的文件名旁边出现“[工作组]”字样。

当向工作组内的一个工作表中输入数据或进行格式化时，工作组中的其他工作表也出现同样的数据与格式。

㈣作业9．10．11第3章表格的美化与打印㈠熟练掌握内容：在工作表中插入艺术字、图片。

㈡了解内容：图片的编辑、工作表有多页时，每页均打印表头。

㈢要点：为了将排版的表格打印出来，需要进行页面设置，例如选择纸张大小、页边距、页面方向、页眉和页脚、工作表的设置等。

用户可以打印整个工作簿、一个工作表，也可以打印工作表的一部分。

㈣作业5．6第4章公式与函数的使用㈠熟练掌握内容:输入公式、公式中的运算符、自动求和、快速计算。

㈡难点:文本运算符、运算符的优先级、相对引用、绝对引用和混合引用、嵌套函数、创建数组公式。

㈢要点1．在Excel中，可以利用文本运算符(&)将文本连接起来。

在公式中使用文本运算符时，以=开头，输入文本的第一段(文本或单元格引用)，加入文本运算符(&),输入下一段(文本或单元格引用)。

2．比较运算符可以比较两个数值并产生逻辑值TRUE或FLASE。

3．引用运算符可以将单元格区域合并计算。

4．如果公式中同时用到了多个运算符，则要按运算符的优先级进行运算，先乘除后加减。

若要更改求值的顺序，则将公式中要先计算的部分用圆括号括起来。

5．引用的作用是标识工作表的单元格或单元格区域，指明公式中所用的数据位置。

能过引用，可以在公式中使用工作表不同部分的数据，或在多个公式中使用同一单元格的数值，还可以引用相同工作簿不同工作表的单元格。

6．引用分为相对引用、绝对引用和混合引用。

公式中的相对单元格引用是基于包含公式和单元格引用的单元格的相对位置。

绝对引用指向工作表中固定位置的单元格，它的位置与包含公式的单元格无关。

混合引用指公式中参数的行采用相对引用、列采用绝对引用；或列采用绝对引用、行采用相对引用，公式中相对引用部分随公式复制而变化，绝对引用部分不随公式复制而变化。

7．函数是预定义的内置公式。

它使用参数的特定数值，按照语法的特定顺序进行计算。

参数可以是数字、文本、逻辑值、数组、错误值或者单元格引用。

给定的参数必须能够产生有效的值。

参数也可以是常量、公式或其他函数。

函数的语法以函数名称开始，后面是左圆括号，以逗号隔开的参数和右圆括号。

如果函数以公式的形式出现，在函数名称前面键入等号(=)。

8．一个函数可以用作其他函数的参数。

当函数被用作参数时，此函数称为嵌套函数。

9．数组公式可以同时进行多个计算并返回一种或多种结果，每个结果显示在一个单元格中。

数组公式可以看成是有多重数值的公式，与单值公式的不同之处在于它可以产生一个以上的结果。

一个数组公式可以占用一个或多个单元格。

数组公式对两组或多组数组参数的数值进行运算。

每个数组参数必须有相同数量的行和列。

㈣作业1．4．5第5章使用图表展示表格信息㈠熟练掌握内容：图表常用术语、图表类型、为数据系列添加趋势线、设置图表格式。

㈡要点：1．图表的常用术语：图表区域、绘图区、图表标题、数据点、数据系列、数据标志、坐标轴、网络线、图例、背景墙及基底。

2．图表有柱形图、条形图、折线图、饼图、XY散点图、面积图、圆环图、雷达图、曲面图、气泡图、股价图、圆锥、圆柱、棱锥图等。

㈢作业1.3.5第6章数据清单的管理㈠熟练掌握内容：数据排序方法、数据筛选、分类汇总、数据透视表、数据透视图。

㈡难点：高级筛选、分类合并计算数据㈢要点1．数据清单是包含相关数据的一系列工作表数据行。

一般把数据清单看作一个数据库。

数据清单中的列被认为是数据库的字段，数据清单中的列标题被认为是数据库的字段名，数据清单中的每一行被认为是数据库中的一条记录。

2．数据排序是按一定规则对数据进行整理和排列，这样可以为数据的进一步处理作好准备。

排序所依据的特征值称为“关键字”，最多可以有3个“关键字”，依次称为“主要关键字”、“次要关键字”、“第三关键字”。

先根据“主要关键字”进行排序，若遇到某些行其主关键字的值相同，而无法区分时，然后根据其“次要关键字”的值进行区分。

3．Excel提供了两种合并计算数据的方法。

一是通过位置，适用于源区域有相同位置的数据汇总；二是通过分类，适用于源区域没有相同布局的数据汇总。

4．数据透视表是一种对大量数据快速汇总和建立交叉列表的交互式表格，可以转换行和列来查看源数据的不同汇总结果，而且可以显示感兴趣区域的明细数据。

数据透视表是一种动态工作表，它提供一种以不同角度观看数据清单的简便方法。

5．数据透视图以图表形式展示数据透视表中的数据。

㈣作业4．7第7章用假设方法求解问题㈠掌握内容：单变量求解、模拟运算表。

㈡了解内容：。

㈢要点1．假设分析是指模型中某一变量的值、某一语句组发生变化后，所求得的模型解与原模型的比较分析。

即系统允许用户提问“如果……”，系统回答“怎么样……”。

2．单变量求解是解决假定一个公式想取得某一结果值，其中变量的引用单元格应取值为多少的问题。

变量的引用单元格只能是一个，公式对单元格的引用可以是直接的，也可以是间接的。

Excel根据所提供的目标值，将引有单元格的值不断调整，直至达到所要求的公式的目标值，变量的值才确定。

3．模拟运算表是假设公式中的变量有一组替换值，代入公式取得一组结果值时使用的。

这组结果值可以构成一个模拟运算表。

模拟运算表有两种类型：单变量模拟运算表和双变量模拟运算表。

4．方案是一组可变单元格的输入值，并且按照用户指定的名字保存起来。

每个可变单元格的集合代表一组假设分析的前提，将其用于一个工作簿模型，以便观察对模型其他部分的影响。

方案用于预测工作表模型结果的一组数值。

用户可以在工作表中创建并保存多组不同的数值，并且在这些新方案之间任意切换，从而查看不同的方案结果。

㈣作业1．2第8章数据的保护与共享㈠掌握内容：保护工作表与工作簿、共享工作薄。

㈡难点：工作表中实现不同的权限、特殊单元格的锁定。

㈢要点:1．用户可以针对整个工作簿指定保护以防止未经授权人的许可而随意被打开、存取或修改工作簿。

2．Excel提供3级工作簿保护的层次：打开权限密码保护，防止不知道密码的人打开工作簿；修改权限密码保护，使不知道密码的人能够查看工作簿，但是不能保存修改；建议只读保护，能够选择是否将工作簿作为只读文件打开，或作为读写文件打开它。

㈣作业2第9章 Excel宏操作㈠掌握内容：宏的使用。

㈡难点：使用VBA控制Excel对象、自定义工具栏、菜单。

㈢要点：1．宏是将一系列的操作命令和指令组合在一起，形成一个命令，从而实现任务执行自动化的一种方法。

可以创建并执行一个宏，以便替代人工进行一系列费时而又重复的操作。

2．Excel提供两种创建宏的方法：利用宏录制和Visual Basic编辑器。

㈣作业：1．2第10章综合应用㈠掌握内容：嵌入公式、编辑超链接。