《EXCEL》教案.doc

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