计算机文化基础第5章Excel.ppt

第5章 电子表格软件 Excel 2003的使用,紧跟计算机技术发展步伐，以社会需求为向导，以实际应用为中心 贯彻案例驱动与项目实践相结合的教学思维，注重操作技能训练 覆盖全国计算机等级考试(一级MS Office)内容，兼顾常用Office高级技能,主 要 内 容,一、基本操作,二、公式和函数,三、数据图表,五、综合举例,四、数据管理,(一)工作表基础,(二)工作表的编辑,(三)工作表的格式化,一、基本操作,(1)几个概念 行号和列号 行号用数字表示，从165536。列号用英文大写字母表示，由A、B、Z、AA、AB、组成，共256列。 单元格 单元格是Excel工作表的基本元素，是独立操作的最小单位单元格.根据其位置来命名的，如E5单元格就是第E列和第5行相交处的单元格。,(一)工作表基础,单元格区域 一组被选中的单元格，对单元格区域的操作就是对该区域内的所有单元格的操作。可以给单元格区域起个名字，即单元格区域的命名，它显示在地址栏中。 工作表 是Excel完成一个完整作业的基本单位。工作表通过工作表标签来标识，用户可以通过单击工作表标签使之成为当前活动工作表。 工作簿 工作簿是Excel存储在磁盘上的最小独立单位。工作簿默认含有3张工作表，最多可以包含255张工作表。它所包含的工作表以标签的形式排列在状态栏的上方。,(2)Excel 2003窗口,(3)工作表数据的输入 1.单元格区域的选定 在输入和编辑单元格内容之前，必须先选定单元格，被选定的单元格称为活动单元格。 2.数据的输入 输入字符型数据 字符型数据是指首字符为字母、汉字或其他符号组成的字符串，在单元格中默认是左对齐。对于数字串作为字符型数据输入时，要在输入项前面添加撇号“ ”或者是“ =”数字串“ ” 。如 091234、 =“091245“, 输入数值型数据 有效的数字可以包含下列字符：0 1 2 3 4 5 6 7 8 9 （ ） / $ ￥ % . ，E e 等。 允许使用科学记数法。 加$或￥货币符号，计算时不受影响。 纯小数可省略点前的0。允许加分节符。 分数输入：以零开头，空格键，再输入分数。 只保留15位数字精度 输入逻辑值型数据 可以直接输入逻辑值TRUE(真)或FALSE(假)。 数据之间进行比较运算时，Excel判断之后，在单元格中自动产生的运算结果TRUE或FALSE。, 输入日期/时间型数据 日期型数据的常用格式：年/月/日或 年-月-日 插入当前系统日期：按Ctrl+ 分号。 插入当前系统时间：按Ctrl+Shift+ ；(分号) 输入批注信息 选定某单元格，单击“插入”菜单的“批注”命令，在系统弹出的批注编辑框中输入批注信息，然后单击编辑框外某一点表示确定。 一旦单元格中存有批注信息，其右上角会出现一个红色的三角形标记，当鼠标指针指向该单元格时就会显示这些批注信息。,3.有规律数据的输入 鼠标拖动输入 数值数据： 复制数据 - 直接拖动填充柄 增1或减1 - Ctrl键+拖动填充柄 文本型数字数据： 复制数据 - Ctrl键+拖动填充柄 增1或减1 - 直接拖动填充柄 按序列填充数据： 先选定序列的初始值，按住鼠标键拖动填充柄，在松开鼠标键后，在弹出的快捷菜单中选择不同序列类型，就可精确地按指定序列类型填充数据。,使用填充命令填充数据： 选择“编辑”“填充”命令时会出现级联菜单，有“向下填充”、“向右填充”、“向上填充”、“向左填充”以及“序列”等命令，选择不同的命令可以将内容填充至不同位置的单元格，如果选定“序列”命令则以指定序列进行填充。,4.单元格的编辑 移动和复制单元格 选定需要移动和复制的单元格。用鼠标将选定区域拖到粘贴区域，直接拖动为移动；按住Ctrl键拖动为复制。 选择性粘贴 复制特定内容：选定需要复 制的单元格，进行“复制”操作。 选定粘贴区域的左上角单元格， 选择“编辑”“选择性粘贴”， 在打开的对话框选择所需选项。,插入单元格、行和列 选择“插入”“单元格”命令，或选择“行”或“列”，或单击右键，在弹出的快捷菜单中选择相应命令。 单元格、行或列的删除与清除 删除单元格、行或列是指将选定的单元格从工作表中移走，并自动调整周围的单元格填补删除后的空缺。 清除单元格、行或列是指将选定的单元格中的内容、格式或批注等从工作表中删除，单元格仍保留在工作表中。 设置表格的行高和列宽 选择“格式”菜单中的“行”或“列”命令，在相应的级联菜单和对话框中输入确定值。,1.工作表的选定与切换 单击工作表标签即可选定需要操作的工作表。 当需要从一个工作表中切换到其他工作表时，可单击相应工作表的工作表标签。如果工作簿中包含多张工作表，而所需工作表标签不可见，可单击工作表标签左端的左右滚动按钮，显示其他标签，然后再单击相应的标签。,(二)工作表的编辑,2.工作表的添加 ：有两种方法。 选择“插入”菜单中的“工作表”命令，Excel将在当前工作表前添加一个新的工作表。 在工作表标签栏中，用鼠标右键单击工作表名字，在弹出的快捷菜单中选择“插入”命令，就可在当前工作表前插入一个新的工作表。,3.工作表的删除 先选中需要删除的工作表，选择“编辑”“删除工作表”命令，在打开的对话框中单击“确定”按钮，将删除工作表。 在工作表标签栏中，用鼠标右键单击需要删除的工作表名字，在弹出的快捷菜单中再选择“删除”命令，就可将当前工作表删除。,4.工作表的重命名 单击“格式”“工作表”“重命名”，工作表标签栏的当前工作表名称将会反白显示，即可修改工作表的名字。 在工作表标签栏中，用鼠标右键单击工作表名字，弹出快捷菜单，选择“重命名”命令，工作表名字反相显示后就可将当前工作表重命名。 双击需要重命名的工作表标签，输入新的名称覆盖原有名称。,5.工作表的移动或复制 (1)将一个工作簿上的某个工作表移动到其他的工作簿中 用“编辑”“移动或复制工作表”命令 用“复制” “粘贴”命令 如果在同一个工作簿中复制工作表，则选中对话框中的“建立副本”复选框，或按下Ctrl键并用鼠标单击要复制的工作表标签将其拖动到新位置，然后同时松开Ctrl键和鼠标。 (2)将同一工作簿的工作表顺序进行重排 在同一个工作簿中移动工作表只需用鼠标拖动工作表标签到新位置即可。,6.工作表的列表功能 选择要指定为列表的数据区域，单击“数据”“列表”“创建列表”选项，所选择的数据区域使用列表标识符突出显示，常用的列表相关功能显示于“列表”工具栏中。 创建列表后，系统将自动为列表中的每一列启用自动筛选下拉列表，并添加插入行作为列表的最后一行。如果单击“列表”工具栏中的“切换汇总行”选项，则将在插入行下显示汇总行。,(三)工作表的格式化,选择“格式”“单元格”，打开“单元格格式”对话框，其中有“数字”、“对齐”、“字体”、“边框”、“图案”和“保护”6个选项卡，对单元格进行设置。,1数据的格式化 用“数字”选项卡来设置单元格中的数据格式：“数字”选项卡用来设置单元格中数字的数据格式。在“分类”列表框中有十多种不同类别的数据，选定某一类别的数据后，将在右侧显示出该类别数据不同的数据格式列表，以及有关的设置选项。,通过格式工具栏上格式化数字按钮来进行设置：这些按钮有“货币样式”、“百分比样式”、“千位分隔符样式”、“增加小数位数”和“减少小数位数”等。,2单元格内容的对齐 Excel中默认的数据对齐方式：文本自动左对齐，数字自动右对齐。还可以在“单元格格式”对话框的“对齐”标签中选择各种对齐方式，包括： 水平对齐：左对齐、右对齐、居中对齐 垂直对齐：靠上对齐、靠下对齐、居中对齐 旋转：在“方向”框中，可以将选定的单元格内容完成从-90+90的旋转 文本控制：自动换行、合并单元格等功能,3. 折行输入 Excel中的换行操作有两种，一是自动换行，即文本自动根据单元格的大小进行换行；二是手工强行换行。 (1)自动换行 在“单元格格式”对话框的“对齐”选项卡中，选中“自动换行”后，被设置的单元格就具备了自动换行的功能。 (2)手工强制换行 在向单元格输入内容过程中，当需要强行换行时，只需按“Alt+Enter”，续输入的内容就会从下一行开始显示，而不管是否达到单元格的最大宽度。,4表格内容字体的设置 方法是：先选定要设置字体的单元格或区域，然后在“单元格格式”对话框中打开“字体”选项卡，该选项卡同Word的字体设置选项卡类似，再根据表格要求进行各项设置，设置完毕后单击“确定”按钮。,5表格边框的设置 设置边框的步骤为：选定所要设置的区域，选择“格式”菜单中的“单元格”命令，在对话框中打开“边框”选项卡，可以通过“边框”设置边框线或表格中的框线，在“样式”中列出了Excel提供的各种样式的线型，还可通过“颜色”下拉列表框选择边框的色彩。,6底纹的设置 首先选择需要设置底纹的表格区域，然后选择“格式”菜单的“单元格”命令，再在“单元格格式”对话框中，打开“图案”选项卡，在“颜色”列表中选择背景颜色，还可在“图案”下拉列表框选择底纹图案，最后单击“确定”按钮。,7表格列宽和行高的设置 鼠标移动到要调整宽度的列的标题右侧的边线上。鼠标的形状变为左右双箭头时，按住鼠标左键。水平方向上拖动鼠标调整列宽。列宽调整到满意的时候，释放鼠标左键。,(一)公式运算符,(二)公式的使用,(三)函数的使用,二、公式和函数,运算符是公式中不可缺少的组成部分，完成对公式中的元素进行特定类型的运算。Excel运算符有：算术运算符、比较运算符、文本运算符和引用运算符,它们都是英文符号。,(一)公式运算符,1.公式的创建 创建公式可以直接在单元格中输入，也可以在编辑栏里面输入。如在“学生成绩1”工作簿的sheet1工作表中输入计算“总成绩”的公式。操作如下： (1)选择要输入公式的单元格F2。 (2)输入计算表达式“=C2+D2+E2” 。 (3)按回车键。 输入公式要注意以下两点： 无论任何公式，必须以等号(即“= ”)开头，否则Excel会把输入的公式当一般文本处理。 公式中的运算符号必须是半角符号。,(二)公式的使用,2.公式的编辑 公式建立后需要对其进行编辑，其操作主要包括公式的修改、公式的移动和复制。 (1)公式的自动复制 (2)公式的编辑和修改 (3)公式的复制和选择性粘贴 3.单元格的引用 (1)含义：单元格作为一个整体以单元格地址的描述形式参与运算称为单元格引用。 引用方式： Office Excel 2003提供了三种不同的引用类型：相对引用、绝对引用和混合引用。,相对引用,相对引用的格式是直接用单元格或者单元格区域名，如A1、D2等。例：在G2中输入公式D2+E2+F2,相对引用是指当把一个含有单元格地址的公式复制到一个新的位置或者用一个公式填入一个区域时，公式中的单元格地址会随着改变。,绝对引用,绝对引用的单元格名的行和列前面都有“$”符,如$A$1等。例:在F2中输入公式“$C$2+$D$2+$E$2”,绝对引用是指在把公式复制或填入到新位置时，使其中的单元格地址保持不变。,混合引用,例：在F2中输入公式“C2+$D$2+E2”,混合引用是指在一个单元格地址中，既有绝对地址引用又有相对地址引用。,1.函数的创建 直接输入： 每一个函数的输入都要以等号“=”开头，然后输入函数名和一对括号，括号内是一个或多个参数，参数之间用逗号分隔。例如： =SUM(A1：B10，C10，15） 计算A1到B10区域、C10单元格和数字15的总和，其中SUM为求和函数名，A1:B10，C10，15都是SUM函数的参数。,(三)函数的使用,使用函数向导插入函数 以求学生的总成绩为例： 选择要插入函数的单元格，执行“插入”“函数”命令。 在“插入函数”“类别” 中选择“常用函数” 中的“SUM” 函数，单击“确定”按钮后弹出“函数参数”对话框。 单击umber1文本框右侧的折叠按钮，弹出折叠后的“函数参数”对话框，选择所需单元格区域，单击函数参数图中的折叠按钮，返回“函数参数”对话框，单击“确定” 。,3.几个常用函数 (1)AVERAGE函数 -求出所有参数的算术平均值。 例：求出每个学生的平均成绩。 (2)COUNTIF函数 -统计某个单元格区域中符合指定条件的单元格数目。 例：统计优秀生的个数(平均成绩=85分 ) (3)IF函数 -根据指定条件的逻辑判断的真假结果，返回相对应的内容。 例：用IF函数填充“成绩等级”列，平均成绩=85分的为“优秀”，其他为“一般”。,(4) SUMIF函数-计算符合指定条件的单元格区域内的数值和。 例：计算优秀生的总成绩。 (5)RANK函数 -返回某数字在一列数字中相对于其他数值的大小排位。 例：找出第1个优秀生在平均成绩中的升序排位。 (6)DATE函数 -给出指定数值的日期。 (7)INT函数 -将数值向下取整为最接近的整数。,例题1：对Cjtj.xls工作簿中的单元格进行填充。 将第1列单元格设为“数值型”，用拖动方式在sheet1工作表中填充第一列的序号：从112 将第2列单元格设为“文本型”，用拖动方式填充第二列的年度：从19982009、间隔1年。 将第3列单元格设为“日期型”，类型取“Mar-01”，利用“序列”对话框填充第三列日期：自2007年1月开始，每个单元格间隔3个月。 利用“序列”填充数值型数据： A.“数量1“以10为起点，按步长4，等差级数增长。 B.“数量2“以10为起点，按步长2，等比级数增长,最多为5120。 C.“数量3”以40为起点，按步长1，等差级数增长。,例题2：对BGZB.xls工作簿中的Sheet1进行操作： 填充“报销比例”列，工龄19年报销比例为0.75，10年及以上报销比例为0.85。 根据报销比例，填充“实报金额”列，实报金额=实用金额*报销比例 例题3：对Ckd.xls工作簿中的Sheet1 进行操作： A.根据表中数据填充“本息”。 本息金额(1期限年利率100) 利用公式进行计算，再用拖动方法完成相对引用 。 B.根据“存入日”和“期限”数据，填充“到期日”。 利用“序列”对话框填充“到期日”。,用函数向导来计算： 把光标定位到E2单元格后，打开函数向导对话框，选择“日期与时间”的DATE函数之后，DATE函数有三个数值型参数：”Year”、”Month”、”Day”。 Year参数要求是一个年份，所以需要再次调用函数即year()函数来求得到期日的年份，即用year(存入日)把“存入日”这个日期型的数据转换成了数值型，再与期限求和得到到期日的年份，month()和day()的作用都是把日期型数据转换成数值型数据分别得到到期日的月份和日子，最后用date()函数把这三个数值型的数据再转换为日期型及“到期日”。 =DATE(YEAR(A2)+B2,MONTH(A2),DAY(A2) 再用拖动方法完成相对引用 。,4.单元格中显示的错误信息 #NULL 使用了不正确的区域运算符或单元格引用。 #NUM！ 公式或函数中某个数字参数有问题。 #REF！ 单元格引用无效 #DIV/O! 公式被 0（零）除 #VALUE！ 使用错误的参数或运算对象类型 #！ 单元格中的内容太长显示不下。 #NAME？ 公式中使用了Excel不能识别的文本。 #N/A 函数或公式中没有可用数值时,(一)创建图表,(二)编辑图表,三、数据图表,1图表结构 图表是由多个基本图素组成的： 图表区：整个图表及其包含的元素。 绘图区：在二维图表中，以坐标轴为界并包含全部数据系列的区域；在三维图表中，绘图区以坐标轴为界并包含数据系列、分类名称、刻度线和坐标轴标题。 图表标题： 数据分类： 数据标志： 坐标轴 刻度线： 网格线：。 图例,(一)创建图表,2创建图表（图表向导）,.,例4：簇状柱形图的建立 打开Tb1.xls工作簿文件，按如下要求操作Sheet1 A.建立图表工作表： 图表类型：柱形图、簇状柱形图； 图表数据源：分类轴为“年度”； 数值轴为“平均成绩”。 图表选项：无图例；标题为“年度平均成绩”。 图表位置：新工作表（图表工作表）； 图表名称：“年度平均成绩“。 B.编辑工作表： 图表标题：黑体、字号36、蓝色。 分类轴：宋体、字号16、红色 数值轴：宋体、字号16、红色；数字无小数点。,例5：折线图的建立 打开jsgz.xls工作簿，对Shtt1工作表进行操作： A.增加序号列并填充；填充实发工资； B.绘制折线图 图标类型：数据点折线图。 图表源数据： 数值轴为“基本工资、岗位津贴、实发工资”； 分类轴为“姓名”。 图表选项：图表标题：“教师工资表”； 图例：靠上。 图表位置：作为新工作表插入； 工作表名：教师工资图表。,例6：三维饼图的建立 打开ZGGZ.XLS工作簿，对Shtt1进行操作： A.在A83-D83单元格分别输入“教授人数”、“副教授人数”“讲师人数”和“助教人数”。在相应单元格下统计各职称人数。(用CONTIF函数) B.建立图示的图表工作表。 图表类型：三维饼图。 图表源数据：数值轴为不同职称的人数。 图表选项：图表标题“职称情况图表”；图例“靠右”；显示百分比和类别名称。 图表位置：作为新工作表插入；工作表名 “职称情况表图表”。,图表的编辑与格式化是指按要求对图表内容、图表格式、图表布局和外观进行编辑和设置的操作。图表的格式化包括： (1)图表文字的格式化、坐标轴刻度的格式化、数据标志的颜色改变、网格线的设置、图表格式的自动套用等。 (2)对图表中的图例进行添加、删除和移动，对图表中的数据系列或数据点进行添加和删除等。 (3)改变当前的图表类型或改变数据源，以及图表的位置等。,(二)编辑图表,(一)数据排序,(二)数据筛选,(三)数据分类汇总,(四)数据透视表,四、数据管理,在数据清单中，可根据字段内容按升序或降序对记录进行排序，排序方式有： 1.普通排序 在普通排序时，默认的排序规则是： 数字：从最小的负数到最大的正数进行排序。 字母：按字母先后顺序排序。 汉字：按照汉语拼音各个字母从左到右进行字母排序。 逻辑值：按照升序排序，FALSE在前，TRUE在后。 空格：始终排在最后。,(一)数据排序,2.单字段排序：一种方法是直接单击“升序”按钮或“降序”按钮，实现按某一字段内容进行排序操作；另一种方法是选择“数据”“排序”，在打开的“排序”对话框“主要关键字”下拉列表框中，选择某一字段名作为排序的主关键字。 3. 多列字段排序：如果要对多个字段排序，则在“排序”对话框的“主要关键字”、“次要关键字”及“第三关键字”下拉列表框中，依次选择字段名作为关键字。,例7：打开“学生成绩.xls”工作簿，对Shtt3工作表进行操作： 1.对“总成绩”按降序排列。 2.主关键字：总成绩 次要关键字：数学 第三关键字：计算机,1.自动筛选 自动筛选是将不满足条件的记录暂时隐藏起来，屏幕上只剩下满足条件的记录。 (1)筛选方法 (2)自动筛选的筛选条件,例8：在“学生成绩”工作簿的SHTT4中进行“自动筛选”,找出计算机成绩大于等于70，且小于85的记录。,自动筛选,(二)数据筛选,高级筛选的三要素： 数据区 条件区 结果区,2.高级筛选 多个条件对数据进行筛选，可以保留原数据清单的显示，将筛选的结果显示到工作表的其他区域。 设定高级筛选的条件 高级筛选方法,例9：打开ZGGZ.XLS工作簿，对Shtt2进行操作： （1）在“出生日期”后面增加一列“年龄”。 在第一行前面插入一行，行高为20磅。 合并及居中A1:J1单元格，输入文本“员工基本情况表”，黑体、14磅、蓝色。 将A2:J2单元格文字设置为黑体、20磅、居中。底纹设置为“浅绿色”。 （2）填充数据 填充“年龄”列，格式设置为“数值”型，负数形式为第四种，无小数点。（年龄（today()-出生日期）/365，要求系统日期正确）,（3）对 Sheet2工作表进行筛选： 条件：筛选出职称为“教授”或“副教授”，基本工资介于600和950之间（包括600和950）的职工的数据。 要求：使用高级筛选。 条件区：起始单元格定位在L20 复制到：起始单元格定位在L30,分类汇总是指对数据库中的某列数据做求和、求平均值、计数等计算。是对数据清单进行数据分析的一种方法，要对数据分类汇总，首先要对其进行排序。 例10：1.单个分类字段的分类汇总 对“职工档案管理”工作簿按“职称”对“基本工资”、“奖金”字段进行求和。 选择“数据”“排序”命令，按“职称”字段进行排序。 选择“数据”“分类汇总”命令，选择“分类字段”“职称” ，“汇总方式”“求和” ，“选定汇总项”“工资”和 “奖金” 。,(三)数据分类汇总,2.多个分类字段的分类汇总 当分类字段是多个时，可先按一个字段进行分类汇总，之后再将更小分组的分类汇总插入到现有的分类汇总组中。例如按“部门”计数，按“职称”对“工资”、“奖金”求和。其具体操作步骤如下： 对数据清单中的“部门”和“职称”两个字段进行排序，其中主关键字是“部门”字段。 选择“数据”中的“分类汇总”命令，选择“分类字段”“部门” ， “汇总方式”“计数” ，“选定汇总项”“部门”。完成按“部门”的计数操作。 单击“职工档案管理”中的任意单元格，选择“分类字段”“职称” ，“汇总方式” “求和” ，“选定汇总项”“工资”和“奖金” ，并取消选中“替换当前分类汇总”复选框。,3.分级显示数据 分类汇总的左上角有一排数字按钮。第一层代表总的汇总结果范围；第二层显示第一、二层的记录，依此类推。按钮用于显示明细数据按钮则是用于隐藏明细数据。 4.删除分类汇总 打开包含分类汇总的数据清单，并单击任意单元格。 选择“数据”中“分类汇总”命令，在弹出的“分类汇总”对话框中单击“全部删除”按钮。,数据透视表是一种用于对数据进行交叉分析的三维表格。它的表格结构是不固定的，可以随时根据需求进行调整，从而得出不同的视图。 1.数据透视表有关概念 由7部分组成：页字段、页字段项、数据字段、数据项、行字段、列字段、数据区域。,(四)数据透视表,2.数据透视表的创建 使用Microsoft Excel 提供的数据透视表和数据透视图向导就可以完成数据透视表的创建。它的操作步骤共分三步：选择数据源类型、选择数据源区域、指定数据透视表位置。 例11：对“小家电订货单.xls”中的sheet1创建数据透视表： 数据透视表的行为“城市”；列为“联系人”；数据为“订货金额”。 将页设置为“地区”。,4.数据透视表布局的更改 单击“数据透视表”中某一单元格，单击“数据透视表”工具栏中的“数据透视表向导”按钮，在“数据透视表和数据透视图向导版式”对话框中，将“字段”按钮拖动到行、列或页所需的位置。 5.数据透视表中分类汇总方式的修改 在Excel中，默认的汇总方式为求和汇总。若要在已有的数据透视表中修改汇总方式，则可采用如下步骤：选中要修改汇总方式的字段名，单击“数据透视表”工具栏中的“数据透视表字段”按钮，打开“数据透视表字段”对话框，在“汇总方式”列表框中选择所需的汇总方式。,五、综合举例,例1(自学) 打开