第4章电子表格处理软件Excel2010

第4章电子表格处理软件Excel20101大学计算机24.1Excel2010工作环境4.2Excel2010的基本操作4.3制作图表4.4数据管理和分析4.5打印工作表4.6Excel2010高级应用实例本章主要内容本章重点：工作表的建立、编辑和格式化，图表制作，数据管理和分析Office家族成员之一——电子表格软件表格标题数据文字数值自动填充公式函数图表图形对象数据清单排序筛选分类汇总公式自动处理图表直观显示数据管理功能3编辑栏列号名字框行号当前单元格工作表标签工作表区域工作簿655363－255256Sheet1!A1Excel2010的工作窗口4.1Excel2010工作环境Excel文件扩展名为.xlsx4显示和选定单元格、图表项、绘图对象等取消操作确定操作“编辑公式”状态EnterTabEsc或或编辑输入区5工作簿是计算和存储数据的文件，其扩展名.xlsx。新建的第一个工作薄的默认名：book1.xls。每一个工作簿可由一个或多个工作表组成，在默认的情况下是由3个工作表组成的。工作簿工作表■一个工作表最多有65536行、256列。■行号：1~65536列标：A~Z;AA~AZ…■工作表由工作表标签区别，如sheet1、sheet2、sheet36工作表中行和列的交叉部分称为单元格，是存放数据的最小单元。单元格的地址表示形式：列标+行号如：A5、D8为了区分不同工作表的单元格，需要在地址前加工作表名称，如Sheet1!B3单元格7⑴输入数据直接输入快速输入数据自动填充数据获取外部数据⑵使用公式和函数计算数据“记忆式输入”“选择列表输入”4.2Excel2010的基本操作8直接输入数据包括：文字。如果要将一个数字作为文字输入，数字前要加单引号“’”数值。可采用十进制形式或科学计数形式；如果数字以“+”或数字“0”开头，“+”和“0”会自动省略；分数的输入应先输入“0”和空格日期和时间。日期输入可用“/”或“－”分隔符，如：2006/10/08，2006-10-08；时间输入用冒号“：”分隔，如：21：56：15。两者之间用空格分隔返回9“记忆式”输入“下拉列表”输入（仅对文本数据有效）“从下拉列表中选择”命令，或者按“Alt+↓”键返回10自动填充数据分三种情况：复制数据。选中一个单元格，直接拖曳，便会产生相同数据，如果不是相同数据，则需要在拖曳的同时按Ctrl键填充序列数据。可通过“编辑/填充/序列”命令，在“序列”对话框中进行序列有关内容的选择填充用户自定义序列数据返回11选择“数据”选项卡“获取外部数据”组中的相应按钮，可导入其他数据库（如Access、FoxPro、Lotus123等）产生的文件和文本文件。在数据输入过程中，有可能会输入无效数据，因此通常在输入前利用“数据”选项卡“数据工具”组中“数据有效性”按钮下拉菜单中的“数据有效性”命令设置数据的有效性规则。返回12（1）公式Excel的公式由运算符、数值、字符串、变量和函数组成。◦公式必须以等号“=”开始◦Excel中的运算符运算符运算功能优先级()括号1-负号2%算术运算符3^4*与/5+与-6&文本运算符7=、、、=、=、关系运算符813引用运算符含义示例：区域运算符：包括两个引用在内的所有单元格的引用SUM(A1:A2)，联合操作符：对多个引用合并为一个引用SUM（A2，C4，A10）空格交叉操作符：产生对同时隶属于两个引用的单元格区域的引用SUM（C2：E10B4：D6）引用运算符：引用运算符可以将单元格区域合并起来进行计算四类运算符的优先级从高到低依次为：“引用运算符”、“算术运算符”、“文本运算符”、“关系运算符”，当优先级相同时，自左向右进行计算。14例：有“公司员工工资表.xlsx”,使用公式计算每位员工的实发工资。15（2）函数是预先定义好的公式，它由函数名、括号及括号内的参数组成。其中参数可以是常量、单元格、单元格区域、公式及其他函数。多个参数之间用“，”分隔。函数输入有两种方法：直接输入法和插入函数法（更为常用）16下图为用函数计算示例17相对引用。当公式在复制或填入到新位置时，公式不变，单元格地址随着位置的不同而变化，它是Excel默认的引用方式，如：B1，A2：C4绝对引用。指公式复制或填入到新位置时，单元格地址保持不变。设置时只需在行号和列号前加“$”符号，如$B$1混合引用。指在一个单元格地址中，既有相对引用又有绝对引用，如$B1或B$1。$B1是列不变，行变化；B$1是列变化，行不变。（3）公式中单元格的引用18例：设公式在F2单元格，公式：=D2+E2其中D2、E2为相对引用公式：=$D$2+$E$2其中D2、E2为绝对引用公式：=$D2+$E2其中D2、E2为混合引用注意：该公式复制到其他单元格，各种引用的区别就会显现。19“绝对引用”示例－－工资评价20选取范围方法单元格鼠标单击或按方向键多个连续单元格从选择区域左上角拖曳至右下角；单击选择区域左上角单元格，按住Shift键，单击选择区域右下角单元格多个不连续单元格按住Ctrl键的同时，用鼠标作单元格选择或区域选择整行或整列单击工作表相应的行号和列号相邻行或列鼠标拖曳行号或列号整个表格单击工作表左上角行列交叉的按钮；选择“编辑/全选”命令；按快捷键Ctrl+A单个工作表单击工作表标签连续多个工作表单击第一个工作表标签，然后按住Shift键，单击所要选择的最后一个工作表标签21单元格数据的编辑单元格内容的清除和删除、移动和复制单元格、行、列的编辑工作表的编辑工作表的插入、移动、复制、删除、重命名等22格式化数据设置数据格式，对数据进行字符格式化调整工作表的列宽和行高设置对齐方式添加边框和底纹使用条件格式自动套用格式23通过“开始”选项卡“数字”组中的相应按钮，或单击其右下角的对话框启动器打开“设置单元格格式”对话框，在“数字”标签中完成。24精确调整行高和列宽，通过“开始”选项卡“单元格”组“格式”按钮下拉菜单中的“行高”和“列宽”命令执行。25“开始”选项卡“对齐方式”组中的相应按钮来完成。或单击该组右下角的对话框启动器，打开“设置单元格格式”对话框，在“对齐”标签中进行。在右键快捷菜单中选择“设置单元格格式”命令打开“设置单元格格式”对话框，在“边框”和“填充”选项卡中进行设置。26工资表格式化效果27条件格式可以使数据在满足不同的条件时，显示不同的格式，非常实用。单击“开始”选项卡“单元格”组中的“条件格式”按钮，在下拉菜单中选择对应的命令。设置条件格式效果28对工作表设置条件格式：将基本工资大于1000的单元格设置成“浅红填充色深红色文本”效果，基本工资小于500的单元格设置成蓝色、加双下划线。设置条件格式效果自动套用格式是一组已定义好的格式的组合，包括数字、字体、对齐、边框、颜色、行高和列宽等格式。Excel提供了许多种漂亮专业的表格自动套用格式，可以快速实现工作表格式化。通过“开始”选项卡“样式”组中的“套用表格格式”按钮来实现。29创建图表（只需要选择源数据，然后单击“插入”选项卡“图表”组中对应图表类型的下拉按钮，在下拉列表中选择具体的类型即可）编辑图表调整图表的位置和大小更改图表的类型添加和删除数据修改图表项添加趋势线设置三维视图格式选中图表，通过“图表工具”选项卡中的相应功能来实现。该选项卡在选定图表后便会自动出现，它包括3个标签，分别是：“设计”、“布局”和“格式”30图表组成31图表制作根据工作表中的姓名、基本工资、奖金、实发工资产生一个三维簇状柱形图。32数据清单，又称为数据列表。是Excel工作表中单元格构成的矩形区域，即一张二维表。它与工作表的不同之处在于：数据清单中的每一行称为记录，每一列称为字段，第一行为表头数据清单中不允许有空行或空列；不能有数据完全相同的两行记录；字段名必须唯一，每一字段的数据类型必须相同；4.4.1建立数据清单33作用：以便于比较、查找、分类简单排序（1个关键字）单击“数据”选项卡“排序和筛选”组中的“升序排序”按钮、，降序排序”按钮、或“排序”按钮或复杂排序（两个以上）“排序”按钮“排序”对话框对员工工资表排序，首先按“部门”升序排列，然后按“基本工资”降序排列，基本工资相同时再按“奖金”降序排列。341、概念◦数据筛选就是将数据表中所有不满足条件的记录行暂时隐藏起来，只显示那些满足条件的数据行。2、Excel的数据筛选方式自动筛选(“数据”选项卡“排序和筛选”组中的“筛选”按钮来实现）（实现单字段筛选、多字段的逻辑与关系）高级筛选（使用“数据”选项卡“排序和筛选”组中的“高级”按钮。（实现多字段的逻辑或关系）例如，筛选出销售部基本工资=1000,奖金＝1000的记录例如，筛选出销售部基本工资=1000,奖金＝1000或财务部基本工资1000的记录35数据筛选自动筛选（如下图）（实现单字段筛选、多字段的逻辑与关系）高级筛选（实现多字段的逻辑或关系）例如，筛选出销售部基本工资=1000,奖金＝1000的记录例如，筛选出销售部基本工资=1000,奖金＝1000或财务部基本工资1000的记录36在公司员工工资表中筛选出销售部基本工资大于等于1000，奖金大于等于1000的记录。37条件区域分类汇总是对数据清单按某个字段进行分类，将字段值相同的连续记录作为一类，进行求和、平均、计数等汇总运算。分为：简单汇总（指对数据清单的某个字段仅统一做一种方式的汇总）嵌套汇总（指对同一个字段进行多种方式的汇总）前提：对分类字段排序38在公司员工工资表中，求各部门基本工资、实发工资和奖金的平均值。步骤：1）首先对“部门”排序2）单击“数据”选项卡“分级显示”组“分类汇总”按钮简单汇总39在上例求各部门基本工资、实发工资和奖金的平均值的基础上再统计各部门人数。步骤：先按上例的方法求平均值，再在平均值汇总的基础上计数嵌套汇总注意：“替换当前分类汇总”复选框不能选中。40如果要对多个字段进行分类汇总，需要需要利用数据透视表。单击“插入”选项卡“表格”组中的“数据透视表”的下拉按钮，选择“数据透视表”命令，打开“创建数据透视表”对话框，确认下选择要分析的数据的范围以及数据透视表的放置位置。然后单击“确定”按钮。此时出现“数据透视表字段列表”窗格，把要分类的字段拖入行标签、列标签位置，使之成为透视表的行、列标题，要汇总的字段拖入∑数值区。41统计各部门各职务的人数。42数据透视表字段列表”窗格数据透视表统计结果创建好数据透视表之后，可以通过“数据透视表工具”选项卡修改它，主要内容有：更改数据透视表布局改变汇总方式数据更新43（1）数据链接Excel允许同时操作多个工作表或工作簿，通过工作簿的链接，使它们具有一定的联系。修改其中一个工作簿的数据，Excel会通过它们的链接关系，自动修改其他工作表或工作簿中的数据。链接让一个工作簿可以共享其他工作簿中的数据，可以链接单元格、单元格区域、公式、常量或工作表。通过“复制”和“选择性粘贴”建立链接。44（2）合并计算Excel提供了“合并计算”的功能，可以对多张工作表中的数据同时进行计算汇总。包括求和（SUM），求平均数（AVERAGE），求最大、最小值(MAX、MIN)，计数（COUNT），求标准差（STDDEV）等运算。按位置进行合并计算是最常用的方法，它要求参与合并计算的所有工作表数据的对应位置都相同，即各工作表的结构完全一样，这时，就可以把各工作表中对应位置的单元格数据进行合并。通过单击“数据”选项卡“数据工具”组中的“合并计算”按钮，弹出“合并计算”对话框进行操作。4546各地区第二季度电视机销售统计各地区第一季度电视机销售统计“合并计算”对话框创建了链接源数据的合并结果（1）日期时间函数①YEAR函数功能：返回某日期对应的年份，返回值为1900到9999之间的整数。格式：YEAR(serial_number)。例：YEAR（1996/8/1）=1996②TODAY函数功