启明公司内部学习资料office2007-excel讲义1PPT优秀课件

1、主讲教师：刘燕彬主讲教师：刘燕ttp:/Excel数据工作表格式化数据工作表格式化名称名称计算与有效性计算与有效性高效创建电子表格高效创建电子表格基本数据操作技巧基本数据操作技巧公式计算与函数公式计算与函ttp:/基本数据操作技巧基本数据操作技巧工作表的基本操作工作表的基本操作快速准确快速准确输入数据输入数据第一部分第一部ttp:/工作表的基本操作工作表的基本操作Office Excel 2007 Office Excel 2007 支持每个工作表中支持每个工作表中最多有最多有 100 100 万行和万行和 1

2、6,000 16,000 列列可以在同一个工作簿中使用无限多的格可以在同一个工作簿中使用无限多的格式类型；每个单元格的单元格引用数量式类型；每个单元格的单元格引用数量从从 8,000 8,000 增长到了任意数量增长到了任意数量Office Excel 2007 Office Excel 2007 还支持最多还支持最多 1600 1600 万种万种颜色颜ttp:/工作表的基本操作工作表的基本操作 隐藏单元格中的数据隐藏单元格中的数据数据隐藏在单元格中数据隐藏在单元格中 操作：单元格操作：单元格数字数字;隐藏单元格中的公式隐藏单元格中的公式 操作操作: :单元格单元格

3、保护保护隐藏隐藏 工具工具保护工作表保护工作表 限制编辑区域限制编辑区域通过保护限制编辑区域通过保护限制编辑区域 操作操作: :工具工具保护工作表保护工作表允许数据编辑区域允许数据编辑区ttp:/快速准确输入数据快速准确输入数据 分数的输入分数的输入例如例如: :输入输入1/21/2 日期的输入日期的输入例如例如: :输入当前日期和当输入当前日期和当前时间前时间 两列数据的互换两列数据的互换 文本的输入文本的输入例如例如: :输入输入”001”001” 自动换行和强行换行自动换行和强行换行自动换行自动换行 强行换行强行换行: :可以在字符的可以在字符的任意位置换行显

4、示任意位置换行显ttp:/快速准确输入数据快速准确输入数据WordWord表单转换为表单转换为ExcelExcel工作表工作表 避免避免WordWord中的长文本在中的长文本在ExcelExcel中出错显示中出错显示操作操作: :1.1. 在在WordWord中将表格转换为文本中将表格转换为文本2.2. 保存为纯文本文件保存为纯文本文件3.3. 在在ExcelExcel中导入数据中导入数ttp:/规范高效创建电子表格规范高效创建电子表格表体的基本格式应用表体的基本格式应用表体的特殊格式应用表体的特殊格式应用数字格式的扩展应用数字格式的扩展

5、应用条件格式及高级应用条件格式及高级应用第二部分第二部ttp:/表体的基本格式应用表体的基本格式应用 普通格式化表格普通格式化表格对表格格式化是管理数据的基础对表格格式化是管理数据的基础, ,在在20072007中提供中提供了像了像20032003一样的基本格式化功能一样的基本格式化功能 普通格式化表格的方法普通格式化表格的方法字体、边框、图案、对齐方式、数字类型字体、边框、图案、对齐方式、数字类ttp:/表体的基本格式应用表体的基本格式应用 边框的设置边框的设置添加边框的方法一添加边框的方法一 内边框操作内边框操作: :选中表体选中表体

6、, ,单击开始单击开始字体字体边框边框选择选择样式和颜色样式和颜色全部边框全部边框 外边框操作外边框操作: :选中表体选中表体, ,单击单击边框边框选择样式和颜色选择样式和颜色外边框外边框添加边框的方法二添加边框的方法二 操作操作: :选择绘图边框选择绘图边框, ,拖动可以绘制外边框拖动可以绘制外边框 操作操作: :选择绘图边框网格选择绘图边框网格, ,拖动可以绘制内边框拖动可以绘制内边框新增功ttp:/表体的基本格式应用表体的基本格式应用 套用表格样式套用表格样式20072007中表格样式丰富多彩中表格样式丰富多彩, ,除了增强表格美观程除了增强表格美观程度外度外

7、, ,还增加了筛选和列表汇总行的功能还增加了筛选和列表汇总行的功能操作操作: :选中表体选中表体, ,开始开始样式样式套用表格格式套用表格格式, ,自自动在标题位置出现筛选动在标题位置出现筛选, ,在表体上单击右键在表体上单击右键, ,表格表格汇总行汇总行, ,可以汇总表格数据可以汇总表格数据 转换为普通表格样式转换为普通表格样式操作操作: :在表体上单击右键在表体上单击右键, ,表格表格转换为区域转换为区域新增功ttp:/表体的基本格式应用表体的基本格式应用12筛选筛选汇总行汇总ttp:/表体的基本格式应用表体的基本格式应用 单元格样式

8、单元格样式自动套用格式提供了大量表体模板自动套用格式提供了大量表体模板, ,而单元格样而单元格样式提供了大量单元格格式模板式提供了大量单元格格式模板, ,以及自定义单元以及自定义单元格样式格样式 套用超连接样式套用超连接样式操作操作: :选中表体选中表体, ,开始开始样式样式单元格样式单元格样式 修改样式修改样式操作操作: :在单元格样式上单击右键在单元格样式上单击右键, ,选择选择”修改修改”新增功ttp:/表体的基本格式应用表体的基本格式应用 自定义单元格样式自定义单元格样式可以自定义单元格的样式可以自定义单元格的样式, ,操作操作: :开始开始样式样式单元格样

9、式单元格样式新建样式新建样式14010-51905758Http:/表体的表体的特殊格式应用特殊格式应用 合并单元格合并单元格合并后居中和合并单元格的区别合并后居中和合并单元格的区别 合并后居中合并后居中: :只保留左上角数据只保留左上角数据, ,并居中显示并居中显示 合并单元格合并单元格: :只保留左上角数据只保留左上角数据, ,右对齐显示右对齐显示合并单元格和跨越合并的区别合并单元格和跨越合并的区别 合并单元格合并单元格: :当选择一个区间时当选择一个区间时, ,只保留左上角数据只保留左上角数据, ,右对齐显示右对齐显示 跨越合并跨越合并: :当选择一个区间时当选择一个区间时, ,每行保留

10、左边数据每行保留左边数据, ,右右对齐显示对齐显示15新增功ttp:/表体的表体的特殊格式应用特殊格式应用 保留原数据合并保留原数据合并在数据合并后不能直接进行筛选，这是个比较在数据合并后不能直接进行筛选，这是个比较大的问题，影响了数据分析工作，如何解决这大的问题，影响了数据分析工作，如何解决这个问题，在这里介绍一种保留原数据的合并方个问题，在这里介绍一种保留原数据的合并方式式保留原数据是指在合并后原数据隐藏并保留着保留原数据是指在合并后原数据隐藏并保留着，这样在筛选时这个单元格就会参与进来，这样在筛选时这个单元格就会参与进来操作：先选中空白表格合并，再用格式刷完成

11、操作：先选中空白表格合并，再用格式刷完成保留原数据的合并保留原数据的合ttp:/表体的特殊格式应用表体的特殊格式应用 绘制斜线表头绘制斜线表头为了清晰表示行和列标题的属性，绘制斜线表为了清晰表示行和列标题的属性，绘制斜线表头头 特殊数字类型转换特殊数字类型转换数字大写的转换数字大写的转换加中文单位的转换加中文单位的转ttp:/数字格式的扩展应用数字格式的扩展应用 数字格式的组成数字格式的组成 自定义格式代码可以为自定义格式代码可以为4 4种类型的数值指定种类型的数值指定不同的格式不同的格式大于条件值；小于条件值；等于条件值；文本格式大于条

12、件值；小于条件值；等于条件值；文本格式正数格式；负数格式；零值格式；文本格式正数格式；负数格式；零值格式；文本格ttp:/数字格式的自定义应用数字格式的自定义应用 使用颜色使用颜色ExcelExcel识别的颜色名为：识别的颜色名为： 黑色黑色 、 红色红色 、 白色白色 、 蓝色蓝色 、 绿色绿色 、 青色青色 和和 洋红洋红 。ExcelExcel也识别按也识别按 颜颜色色XX指定的颜色，代表指定的颜色，代表5656种颜色种颜色 添加描述文本添加描述文本在数字的前面或后面添加文本，在数字的前面或后面添加文本，“元元” 创建条件表达式格式创建条件表达式格式可以使用六

13、种逻辑符号来设计一个条件格式：可以使用六种逻辑符号来设计一个条件格式： （大于（大于）、）、=（大于等于）、（大于等于）、 （小于）、（小于）、=（小于等于）、（小于等于）、= =（等于）、（等于）、（不等于）（不等于）, , 表达式表达式ttp:/条件格式条件格式 条件格式是一条件格式是一种根据条件以格式区分数据种根据条件以格式区分数据的方式，是数据分析的基础的方式，是数据分析的基础操作操作: :开始开始样式样式条件格式条件格式 条件格式的条件格式的分类：单元格数值和公式分类：单元格数值和公式单元格单元格数值的数值的约束约束 包括五种：突出显示单元格规则、项目选取

14、规则、包括五种：突出显示单元格规则、项目选取规则、数据条、色阶、图标集数据条、色阶、图标集新增功ttp:/条件格式条件格式 公式的约束公式的约束格式格式:=:=公式公式 表达式表达式 结果结果 管理规则管理规则管理规则的删除、建立和编辑管理规则的删除、建立和编ttp:/条件格式条件格式 练习练习突出显示为突出显示为“助学基金助学基金”项目捐款的金额项目捐款的金额突出显示捐款金额的重突出显示捐款金额的重复值复值突出显示捐款金额最多突出显示捐款金额最多和最少的和最少的5 5个名单个名单制作带有交互的条件格制作带有交互的条件格式式010-519

15、05758Http:/制作带有交互的条件格式制作带有交互的条件格ttp:/条件格式条件格式 我们经常会提出这样的问题：是否可以按我们经常会提出这样的问题：是否可以按照颜色进行排序和筛选，在照颜色进行排序和筛选，在20032003中显然是很中显然是很难完成的，但难完成的，但20072007中可以很轻松的实现中可以很轻松的实现 按颜色排序按颜色排序操作操作: :在条件格式设置完，数据在条件格式设置完，数据排序排序/ /筛选筛选 按颜色筛选按颜色筛选操作操作: :在条件格式设置完，数据在条件格式设置完，数据筛选筛选新增功ttp:/条件格式条件格

16、式排序排序筛选筛ttp:/选择性粘贴功能选择性粘贴功能 批量修改数据批量修改数据例如：对产品价格数据经常发生变化，如何将例如：对产品价格数据经常发生变化，如何将数据的变化反映在原有单元格中？数据的变化反映在原有单元格中？利用选择性粘贴功能实现该操作利用选择性粘贴功能实现该操作 操作：选择变化操作：选择变化数据数据复制复制选择性粘贴到原有产选择性粘贴到原有产品价格上品价格上 实现工作表的转置实现工作表的转置例如例如: :工作表行的字段太多影响了工作表的美观工作表行的字段太多影响了工作表的美观, ,可以将行和列转换可以将行和列转换, ,将行的字段显示在列上将行的字段显示

17、在列上 操作：操作：选择性粘贴选择性粘贴转置转ttp:/数据的合并与拆分数据的合并与拆分 有时我们要对单元格中的数据进行合并有时我们要对单元格中的数据进行合并, ,而而并不是合并单元格并不是合并单元格, ,如何实现数据的合并如何实现数据的合并, ,如如何执行反操作拆分数据呢何执行反操作拆分数据呢? ? 将多个单元格中的数据将多个单元格中的数据合并合并“&”&”符号符号操作操作:=:=a1&a2a1&a2 拆分单元格数据拆分单元格数据操作操作: :数据数据数据工具数据工具分分列ttp:/第三部分第三部分名称计算名称计算与有效性与有效性名称与

18、引用计算名称与引用计算利用利用有效性控制数据有效性控制数据输入输入序列的填充序列的填ttp:/29单元格命名单元格命名 定义单个名称定义单个名称操作操作：公式：公式定义名称定义名称 批量定义名称批量定义名称操作操作：公式：公式根据所选内容创建根据所选内容创建 批量删除名称批量删除名称20072007增加了批量删除名称和对名称进行筛选增加了批量删除名称和对名称进行筛选的功能的功能, ,为应用名称提供了很大的方便为应用名称提供了很大的方便操作：公式操作：公式管理名称管理名称新增功ttp:/30引用函数与名称结合引用函数与名称结合 Indire

19、ctIndirect函数的介绍函数的介绍返回由文本字符串指定的引用返回由文本字符串指定的引用 结构结构:indirect(ref_text,a1):indirect(ref_text,a1)Ref_text:Ref_text:对单元格的引用对单元格的引用A1:A1:如果是如果是TRUETRUE或省略或省略, ,对对a1-a1-样式的引用样式的引用, ,如果如果是是False,False,对对R1C1-R1C1-样式的引用样式的引用. . 对指定区域求和对指定区域求和公式公式:=sum(indirect(:=sum(indirect(名称名称ttp:/31利用有效性控

20、制数据输入利用有效性控制数据输入 数据有效性数据有效性数值范围的有效性数值范围的有效性出错信息出错信息警告警告 利用序列控制输入内容利用序列控制输入内容制作一级下拉列表制作一级下拉列表 设置序列的数据源引用设置序列的数据源引用 跨工作表数据源的引用跨工作表数据源的引用 制作二级下拉列表制作二级下拉列ttp:/序列自动填充序列自动填充 按时间顺序自动填充按时间顺序自动填充 有规律文本序列的填充有规律文本序列的填充 有规律数字序列的填充有规律数字序列的填充使用填充柄填充等差序列使用填充柄填充等差序列使用填充柄填充等比序列使用填充柄填充等比序列 自定义填充自定义填充当填充一列系统中没有的序列时当填充一列系统中没有的序列时, ,首先要定义该序首先要定义该序列列, ,然后再进行自动填充然后再进行自动填ttp:/第四部分第四部分公式计算与函数公式计算与函数公式计算公式计算条件与统计函数的使用条件与统计函数的使用010-5