1、计算机基本应用能力项目Excel 电子表格处理讲义稿主讲教师：任鹏达 秦伟长治职业技术学院信息工程系计算机基本应用能力项目组二一二年二月目录概述 11.总体要求： 12.学习目标： 2任务一：创建工作表 4目的和要求 4Excel 基础 .4基本操作 7数据的输入 8作业 14任务二：建立“成绩统计”工作表和“补考名单”工作表 16目的和要求 16公式和函数的使用 16单元格引用与公式的复制 19区域命名 20条件格式的设置 21作业 21任务三：工作表的修饰 24目的和要求 24字体设置 24表头和标题 25单元格对齐方式 25单元格的数据格式化 25设置列宽、行高 25行列的隐藏 26自动
2、格式化 26格式的复制和删除 26表格边框 26背景设置 27作业 27任务四：数据管理和分析 29目的和要求 29数据清单 29数据排序 32数据筛选 33分类汇总 37作业 44任务五：页面设置与打印 46目的和要求： 46页眉和页脚 46标题行 47设置打印区域和分页 48打印预览和打印 50作业 50任务六：图表的应用 52目的和要求 52图表的分类 52创建图表 53编辑、修改图表 55图表的格式化 58作业 59任务七：数据透视表 60目的和要求： 60概述 60数据透视表术语解释 63创建和删除数据透视表 67编辑数据透视表 68细节数据显示控制 70作业 71项目考核 73Ex
3、cel 电子表格处理 概述1计算机基本应用能力项目Excel 电子表格处理概述Excel 2003 是微软公司开发的 Office 2003 中的主要成员之一，是集文字、数据、图形、图表及其它多媒体对象于一体的电子表格软件。它的核心功能是表格处理，同时还能进行统计计算、图表处理和数据分析。由于 Excel 具有十分友好的人机界面和强大的计算功能，因而深受广大办公、财务和统计人员的青睐，成为最受欢迎、最流行的电子表格软件。1.总体要求：以应用 Excel 电子表格处理软件对我系Excel 电子表格处理 概述2某一毕业班在校期间三年六学期的所有课程成绩进行相应处理为主线，将 Excel 电子表格处
4、理软件的使用技巧和方法划分为若干任务，并将学生应掌握的知识点贯穿其中。每项任务都应由教师先演示任务结果，再就完成该任务所应掌握的知识点和操作过程作进一步讲解和演示。学生在进行模仿操作的基础上进行完善，完成任务。学生在完成所有任务后，按要求对某一毕业班在校期间三年六学期的所有课程成绩进行相应分析处理，并做出最终处理结果作为该子项目的最终学习成果。通过该子项目的学习，使学生具备娴熟应用 Excel 电子表格处理软件分析处理各种数据的能力，进行个人数据处理、公司信息管理以及各行业的数据统计和分析处理。Excel 电子表格处理 概述32.学习目标：1) 掌握中文电子表格处理软件 Excel 2003基
5、本操作2) 了解工作表的管理3) 学会工作表的编辑操作4) 掌握公式和函数的应用5) 学会图表的创建与编辑6) 掌握数据排序、数据筛选、分类汇总的方法7) 掌握设置工作表页面和工作表的打印Excel 电子表格处理 创建工作表4任务一：创建工作表目的和要求了解 Excel 的基础知识；熟悉 Excel 的工作界面；掌握 Excel 的基本操作；能够通过各种方法向表格内输入不同类型的数据；能够熟练地创建、打开和保存 Excel 文档。（见学生原始成 绩表）Excel 基础一、启动、退出方法：1. 启动：常用的几种方法：（1） 双击桌面上的快捷图标Excel 电子表格处理 创建工作表5（2） 开始程
6、序Microsoft Excel（3） 双击任何一个 Excel 文件（4） 开始运行输入“Excel”可执行文件。2. 退出：（1） 单击右上角“关闭”按钮。（2） “文件”退出（3） 单击控制菜单关闭（4） 双击控制菜单（5） ALT+F4二、窗口组成：全选按钮填充柄编辑栏任务窗格水平分隔条垂直分隔条活动单元格工作表标签标题栏菜单栏格式工具栏常用工具栏地址栏工作区行号列标Excel 电子表格处理 创建工作表6三、基本概念（工作簿、工作表、单元格）：工作簿：是计算和储存数据的文件。一个工作簿就是一个 EXCEL 文件，其扩展名为“.XLS”。EXCEL 启动后，自动打开一个被命名为“Book
7、1”的工作簿。工作表：也称作电子表格。一个工作簿可以包含多个工作表（EXCEL2003 最多可创建 255 个工作表） ，这样可使一个文件中包含多种类型的相关信息，用户可以将若干相关工作表组成一个工作簿，操作时不必打开多个文件，而直接在同一个文件的不同工作表中方便地切换。默认情况下，EXCEL 的一个Excel 电子表格处理 创建工作表7工作簿中有 3 个工作表，名称分别为Sheet1、Sheet2、Sheet3，当前工作表为Sheet1，用户根据实际情况可以增减工作表和选择工作表。单元格：是组成工作表的最小单位。EXCEL 的工作表由 65536 行、256 列组成，每一行列交叉处即为一单元
8、格，有 65536256个。列标用字母及字母组合AZ，AAAZ，BABZ，IAIV 表示，行号用自然数 165536 表示。每个单元格用它所在的列标+行名来命名，如：A6、D20 等。每个单元格最多可容纳 32767 个字符。活动单元格：区域：连续、不连续 A1:C3，B12Excel 电子表格处理 创建工作表8基本操作一、 工作簿的创建、保存和打开二、 单元格的定位：直接定位、利用地址栏定位三、 行、列、单元格、区域的操作选定、插入、删除、清除内容数据的复制和移动四、 工作表的操作插入、删除、移动、重命名数据的输入在工作表中可输入两种数据常量和公式，两者的区别在于公式以等号（）开头。常量类型
9、有三类：文本型、数值型和日Excel 电子表格处理 创建工作表9期时间型。常量的输入方法：输入时不仅可直接从键盘输入还可自动输入，并且可检查其正确性。输入结束后按回车键、Tab 键或鼠标单击编辑栏的“”按钮均可确认输入。按ESC 或单击编辑栏的“”按钮可取消输入。1. 三种类型的数据的输入：（1） 文本输入：文本输入时向左对齐。有些数字如电话号码、邮编常作字符处理，此时只需在输入数字前加上一个单引号或设置单元格格式为文本，即当作字符左对齐。当输入的文字长度超出单元格宽度时，如右边单元格无内容，则扩展到右边列；否则将截断显示。Excel 电子表格处理 创建工作表10（2） 数值输入：数值包含09
10、、E、e、$、/（分号） 、%以及小数点和千分位符号（,）等特殊字符。如：$50,000。另外，Excel 还支持分数的输入，如： 12 3/4，在整数和分数之间应有一个空格，当分数小于 1 时，要写成 0 3/4，不写 0 会被 Excel 识别为日期 3 月 4 日。字符“￥”和“$”放在数字前会被解释为货币单位。数值型数据在单元格中一律靠右对齐。当输入数据太长时，Excel 自动以科学计数法表示，如：3.45E+12；当单元格容纳不下一个格式化的数字时，就用若干个“#”代替。Excel 的数字精度为 15 位，当长度超过Excel 电子表格处理 创建工作表1115 位时，Excel 会将
11、多余的数字转换为 0，如输入：1234512345123456 时，在计算中以1234512345123450 参加计算。（3） 日期时间数据输入：Excel 内置了一些日期时间的格式，当输入数据与这些相匹配时，Excel 将自动识别它们。EXCEL2003 已采用四位表示年份。其常见日期时间格式为“mm/dd/yy” 、 “dd-mm-yy”、 “hh:mm (AM/PM)”。默认方式：右对齐。输入日期时，可用斜杠（/）或减号（-）分割日期的年、月、日。输入时间时，按时:分:秒格式。若按 12 小时制输入时间，则应在时间数字的末尾空一格，随后键入字母 a 或 p，Excel 电子表格处理 创
12、建工作表12比如 7:20 a（但显示为 7:20 AM） ，缺少空格将被当作字符数据处理。同时输入日期和时间，在中间用空格分隔。如输入 1999 年 1 月 3 日下午 4:30，则可输入：1999/1/3 16:30 或 99/1/3 4:30 p（但时间均显示为 24 小时制） 。注：若要输入当天的日期，可按快捷键 Ctrl+；（分号）若要输入当前时间，可按快捷键 Ctrl + Shift + ：（冒号）或 Ctrl + Shift + ；（分号）2. 数据自动输入：如果输入有规律的数据，可使用 Excel的数据自动输入功能，它可以方便快捷地输入等差、等比以及预定义的数据填充序列。Exc
13、el 电子表格处理 创建工作表13（4） 自动填充自动填充是根据初始值决定以后的填充项，用鼠标拖曳初始值单元格右下角的填充柄，即可完成自动填充。自动填充可实现以下几种功能： 单个单元格内容为纯字符、纯数字或是公式，填充相当于数据复制。 单个单元格内容为文字数字混合体，填充时文字不变，最右边的数字递增。如初始值为 A1，填充为 A2， 单个单元格内容为 Excel 预设的自动填充序列中一员，按预设序列填充。如初始值为一月，自动填充为二月、三月、 可通过“工具选项自定义序列”来添加新序列并储存起来供以后填充时使用。Excel 电子表格处理 创建工作表14 如果有连续单元格存在等差关系，则先选中该区
14、域，再运用自动填充可自动输入其余的等差值，拖曳可由上往下或由左往右拖动，也可反方向进行。（5） 特别的自动填充：如果自动填充时，要考虑是否带格式或区域中是等差还是等比序列，则自动填充时按住鼠标右键，拖曳到填充的最后一个单元格释放，将出现“自动填充快捷菜单” ，可进行各种选择。（6） 产生一个序列：步骤如下： 在单元格输入初值并回车； 用鼠标单击选中第 1 个单元格或要填充的区域，选择“编辑填充序列” 。Excel 电子表格处理 创建工作表153. 输入有效数据： 可预先定义一个或多个单元格允许输入的数据类型、范围：选定单元格“数据有效性” 对已输入的数据可进行审核：选定数据设置有效性“工具审核
15、”显示“审核”工具栏单击“圈释无效数据” 。作业一创建我系某毕业班级学生的原始成绩表，结构如样本，并输入各种数据（身份证号输入本班学生的即可） 。将工作簿命名为“XX班学生成绩汇总表 1”，该工作簿应包含 7 个工作表，工作表名称依次为“学生原始成绩表” 、 “第一学期成绩表” 、 “第二学期成绩表” 、 “第三学期成Excel 电子表格处理 创建工作表15绩表” 、 “第四学期成绩表” 、 “第五学期成绩表” 、 “第六学期成绩表” 。Excel 电子表格处理 工作表数据统计17任务二：建立“成绩统计”工作表和“补考名单”工作表目的和要求掌握公式与常用函数的使用方法（Sum、Average、
16、Max、Min、Mid、If、Countif） ；掌握单元格区域命名的方法利用单元格区域名称实现在同一工作薄的不同工作表中数据共用；掌握条件格式的使方法。（见学生成绩统计表一、成绩统计表二、成绩统计表三、补考名单）Excel 电子表格处理 工作表数据统计18公式和函数的使用1.使用公式：公式以“”开头。（1） 公式运算符：公式中可使用的运算符包括：数学运算符、比较运算符、文本运算符。数学运算符：、*、/、%、等。比较运算符：、，其值为逻辑值：TRUE 或 FALSE文本运算符：（连接）将两个文本连接，其操作数可是带引号的文字，也可是单元格地址。运算的优先级：数学运算符文本运算符比较运算符。数学
17、运算符中：Excel 电子表格处理 工作表数据统计19%*、/ 、比较运算符：优先级相同同级运算从左向右依次运算。（2） 公式输入：在编辑栏输入：选定单元格输入“”和公式回车或单击编辑栏中的“” 。在单元格输入：双击单元格输入“”和公式回车或单击编辑栏中的“” 。2.使用函数：函数的形式为：函数的名称（参数 1，参数 2，）其中参数可以是常量、单元格、区域、区域名、公式或其他函数。Excel 电子表格处理 工作表数据统计20（1） 函数输入：有两种方法：一为粘贴函数；一为直接输入法。粘贴函数输入法：编辑栏旁的 按钮或“插入函数” ，出现“插入函数”对话框，进行相应设置。注：在参数框中输入无把握
18、的参数时可单击参数框右侧的 按钮，以暂时折叠对话框，方便选择单元格区域。直接输入法：如果对函数名和参数都很清楚，可直接输入函数。如“=average(a1:c2)”。注：掌握几个常用函数的使用：sum average midExcel 电子表格处理 工作表数据统计21max min ifcountif（2）自动求和：单元格引用与公式的复制单元格引用有三种方式：相对引用、绝对引用和混合引用。（1） 相对引用：如 A1、B5 等。方便公式的自动复制。（2） 绝对引用：在行列前均加上“$”符号，如$B$5、$AC$6。不能进行公式的自动复制。（3） 混合引用：在行号或列号前加上Excel 电子表格处
19、理 工作表数据统计22“$”符号，如$A1，A$1。引用同一工作簿的不同工作表中的单元格，需在工作表名与单元格引用之间加“！” 。如：Sheet2!B6+Sheet1!A4区域命名除了用冒号表示区域范围以外，还可自己对区域进行命名。一次定义一个名称：选中区域在地址栏输入名称或“插入名称定义”一次定义多个名称：“插入名称指定”区域命名的作用：增强公式的可读性；实现同一工作薄不同工作表的数据共用。Excel 电子表格处理 工作表数据统计23条件格式的设置在工作表中如果需要对某些单元格进行特殊格式设置时，可进行条件格式设置。如：对不及格的学生课程成绩突出显示等。选中单元格格式条件格式打开条件格式对话
20、框进行相应设置作业二将“XX 班学生成绩汇总表 1”复制到同一目录下，并将其重命名为“XX 班学生成绩汇总表 2”，在“XX 班学生成绩汇总表 2”中做如下操作（表结构 见样本）：1.在各学期成绩表中，利用 Mid 函数求出生日期，用 Sum 函数求总分，用 AverageExcel 电子表格处理 工作表数据统计24函数求平均分，用 If 函数对学生进行综合评价（平均分=90 为优；平均分=80 为良；平均分=70 为中；平均分=60 为及格；平均分60 为不及格） ，利用条件格式对不及格成绩进行突出显示，设定区域名称。2.在各学期成绩表中，利用区域名称和Max、Min、Average 函数求
21、各科最高分、最低分和平均分。3. 在各学期成绩表中，利用 Countif 函数和区域名称统计各科各分数段人数。4.创建各学期补考名单（使其位于每学期成绩表后） ，利用区域名称和 If 函数找出每学期各科需补考学生。Excel 电子表格处理 工作表的修饰25任务三：工作表的修饰目的和要求设置表头和标题；设置单元格对齐方式；设置数据格式；调整表格行高和列宽；设置行和列的隐藏；添加表格边框；设置背景颜色和图案；（见学生成绩统计表一修饰）字体设置同 wordExcel 电子表格处理 工作表的修饰26表头和标题设置通栏标题：选中单元格区域合并及居中按钮 输入内容单元格对齐方式选中单元格 或打开单元格格式对话框进行设置单元格的数据格式化“格式单元格”或 右击单元格设置单元格格式 或 通过“格式”工具栏设置列宽、行高列宽、行高的调整：用鼠标直接指向要调整的列宽（或行高）的列标（或行标）的Excel 电子表格处理 工作表的修饰27分隔线上。列宽、行高的精确调整：“格式列或行”列宽或行高、最适合的列宽或行高行列的隐藏行、列的隐藏或取消：选定行列“格式行列隐藏或取消隐藏”自动格式化“格式自动套用格式”格式的复制和删除格式的复制：用格式刷格式的删除：“编辑清除格式”表格边框通过格式工具栏
