《EXCEL应用培训》PPT课件.ppt

Miicrosoft Excel 应用技巧培训 周林波 森马服饰财务中心 2011年3月17日 课程简介 一、使用对象 熟悉Excel基础功能，同时有着深厚的学习兴趣， 打算进一步提高这方面技能的人员。 二、使用说明 没有严格的顺序要求，使用时可以有选择地学习 其中某部分，在编排时尽量考虑了功能的关联性 ，建议系统地学习和演练。 目的 一、抛砖引玉 通过讲解一些实用的功能，让大家认识到Excel功 能的强大。 二、会者不难 这些很实用很强大的功能其实很简单，关键在于 用心去学习，针对具体的工作采取行这有效的方 法。 目录 基础应用 数据处理及分析 函数 第一部分 第二部分 第三部分 图表的应用第四部分 其他高级应用第五部分 目录 第一部分Excel基础操作应用 摘 要 使用EXCEL的良好习惯 工作表基本操作技巧 养成良好的使用习惯（一） 1、使用同一名称来表述同一对象，而不要混杂使用简写、别称，以 便使用函数查找、引用。同理，公司不同部门间对同一对象亦应统一 名称或编码，以利数据的传递、引用。 2、不在字符串内、前后插入空格来排版，尽量使用对齐、缩进等功 能，以便使用函数查找、引用。如果你使用过Vlookup等函数你就可 体会到以上二条习惯的必要性。 3、在要使用公式的单元格，尽量不使用合并单元格功能，以便填充 公式、排序、筛选等。 4、排版时请使用顶端标题行、标题列功能，而不必笨拙、野蛮地每 隔N行强制性插入标题行、标题列。 5、数字及文本的对齐方式请使用Excel默认的格式，即数字靠右对齐 、文本靠左对齐；数字使用千位分隔符。 养成良好的使用习惯（二） 6、在计算结果可能出现#DIV/0!、#VALUE!、#N/A等错误，而其它单 元格又需对此单元格进行运算时，要养成联用IF和ISERROR等函数 来消除错误值的意识。 7、使用拆分窗口或冻结窗格功能，免除为看到标题行而大量滚屏， 以提高工作效率。 8、工作簿内工作表较多时，使用工作表标签的快捷菜单，快速选取 工作表。 9、在同一工作簿的不同工作表的相同单元格输入相同的数据或设置 相同的格式，请使用组合工作表功能，以提高效率。 10、养成使用状态栏进行临时性的求和、计数、求平均值的习惯。 绝对引用与相对引用 A1样样式R1C1样样 式 特性 $A$1R1C1绝对 引用,向右向下拖动公式不改变引用关 系. A$1R1C1行绝对 ,列相对混合引用,向下拖动公式不 改变引用关系. $A1R1C1行相对,列绝对 混合引用,向右拖动公式不 改变引用关系 A1R1C1相对引用,向右向下拖动公式均会改变引用 关系 快速切换相对引用,绝对引用 相对,绝对,混合引用大大方便了用户复制公式的操作, 不必逐个输入公式,一般一个汇总表格或同一个要求下的 公式我们只需编辑一个公式,通过对引用的控制解决问题. 但是,使用绝对引用和混合引用时需要手工在相应位置输 入美元符号,不太方便,事实上,Excel提供了快捷键F4,可在 编辑栏对引用单元格的引用类型进行快速切换. 如果你能很好的控制单元格的引用,那么你的Excel水 平就会有很大的提升. 善用鼠标、键盘 Ctrl+C、V快速复制、粘贴 Ctrl+箭头键 移动到当前数据区域的边缘 Ctrl+Home、End移动到工作表的开头、结尾 Alt + =（等号）用 SUM 函数插入“自动求和”公式 F4 或 Ctrl+Y 重复上一次操作 双击行列边界可设定合适行高列宽 快速移动单元格：按住Shift，将鼠标移到单元格区域的 边缘拖动单元格至目标单元格 数据录入 1、批量录入相同数据: 选中区域，左上角输入，Ctrl+Enter 2、正确输入日期 快速输入当前日期和时间：输入当前日“Ctrl+;”，输入当 前时间“Ctrl+Shift+;” 3、正确输入分数 输入数字空格值，或直接设置成分数格式再输入 4、输入长数字文本 先输入或者先设置文本格式 单元格格式 1、零值不显示 单击菜单“工具”“选项”，选择“视图”，取消勾选“零值”复选 框，当前工作簿中的“0”将不显示。 2、带格式的数值运算问题 单元格格式的影响只是显示内容，实际存储的内容并未改 变，有时我们需要使用显示值进行计算，常见做法有两种 ： （1）使用合适的函数进行四舍五入，常用的四舍五入的函 数有Round、Floor（不常用）。 （2）依次在菜单栏中点击“工具选项重新计算”，然后将工作 簿选项中的“以显示值为准”复选框勾中即可。 单元格格式 3、单元格内强制换行 组合键 4、显示单元格格式对话框 组合键 选择性粘贴 选择性粘贴是有选择地粘贴已复制单元格的信息，如格式、数值、文 本等，下面列举几项该功能的具体应用实例： 1、数值 将公式的计算结果粘贴为独立的数据 2、运算 将剪贴板单个数值与已有数据区域直接进行加减乘除运算，并将结果 显示在原数据区域。 3、转置 将表格的行列互换，就是说把一个横排的表变成竖排的或把一个竖排 的表变成横排的。 定位的应用 定位的作用就是快速选择数据区域或某些特殊对象，主要用于以下情况 1、如果数据区域很大，超过一屏幕，你要选定某个区域，如A1： D3000。如果直接使用鼠标选择，不仅眼花缭乱，还影响操作速度。 （1）直接在名称框中输入区域范围。 （2）利用组合键打开定位对话框，在引用位置中输入要定位的 单元格区域或区域名称。 2、选择特殊区域 （1）包含批注的单元格 如果工作表中很多单元格有批注，这些单元格内容特殊，需要将这些 单元格单独拷贝出来，可以在定位条件对话框中选择“批注” 定位的应用 （2）选择编辑时留下的各种对象 有时Excel文件会莫名其妙地变大，体积与存储的内容差 得很多，这可能是由于文件中包含很多看不见的对象，可 以在定位条件对话框中选择“对象”，按删除后体 积会变小。 （3）选择可见单元格区域 在数据汇总条件下，明细数据是隐藏的，如果需要将汇总 数据拷贝到别处，可以选择“可见单元格”，复制后粘贴到 其他位置即可。 复制图片 有时需要将Excel中的数据区域或图表等对象转换成图片形式，通常 有以下三种方法 1、使用键直接复制 选中源数据，按住的同时打开“编辑”菜单，会看到菜单发生了 变化，选择“复制图片”，数据源将暂存到剪贴板上，粘贴到目标区域 即可。 2、借助“画图”程序，将源数据粘贴到画图程序中，保存成图片。 3、利用照相机功能 首先将“照相机”命令显示在工具栏上，然后选中源数据，单击“照相机” 按钮，最后在目标位置单击鼠标即可。 总结：前两种方法生成的图片是静态的，源数据的变化对图片无影响 ；第三种方法生成的图片是动态的，会随着源数据的改变而变化。 自定义工具栏 一般的软件都支持工具栏自定义功能，Excel也不例外。工具栏自定 义就是根据自己的工作习惯和个人爱好生成自己的工具栏，通常是将 不常用的工具去掉，将常用的工具调出来，放在一起，达到方便使用 ，提高工作效率的目的。 下面列出几项常用的自定义命令，括号中为对应的菜单类别 1、照相机（工具） 2、跨越合并、摊销合并单元格（格式） 3、分散对齐、垂直分散对齐（格式） 4、粘贴数值、选定可见单元格（编辑） 5、自动筛选、全部显示（数据） 条件格式 条件格式的作用：单元格的内容或参数满足一定的条件才 应用某种特殊格式，通过灵活设置条件格式可以使具有特 殊特征的单元格突出显示。 （1）单元格式的条件格式设置包括对字体、边框、图案三 类的设置。 （2）条件格式可以指定最多三个条件：如果指定条件都不 符合，则单元格将保持原有的格式，如果同时符合多个指 定条件，则只应用第一个条件格式。 （3）条件格式可以通过选择性粘贴中的“格式”或格式刷应用 到其他单元格。 定义名称 名称的定义是EXCEL的一基础的技能，可是，如果你 掌握了，它将给你带来非常实惠的好处！ 建议使用简单易记的名称，不可使用类似A1的名称 ，因为它会和单元格的引用混淆。还有很多无效的名称， 系统会自动提示你。 引用位置：可以是工作表中的任意单元格，可以是公 式，也可以是文本。 定义名称的好处 减少输入的工作量 如果你在一个文档中 要输入很多相同的文本，建 议使用名称。例如：定义 DATA = “I LOVE YOU， EXCEL！”，你在任何单元格 中输入“=DATA”，都会显示“I LOVE YOU， EXCEL！” 超出某些公式的嵌套 例如IF函数的嵌套最 多为七重，这时定义为多个 名称就可以解决问题了。也 许有人要说，使用辅助单元 格也可以。当然可以，不过 辅助单元格要防止被无意间 被删除。 定义名称的好处 破除单元格输入最大量 名称的引用位置中的 字符最大允许量也是有限制 的，你可以分割为两个或多 个名称。同上所述，辅助单 元格也可以解决此问题不过 不如名称方便 某些只能在名称中使用 例如由公式计算结果 的函数，在A1中输入 =1+2+3，然后定义名称 RESULT = EVALUATE(Sheet1!$A1)，最 后你在B1中写入=RESULT，B1 就会显示6了。 工作表的打印 1、打印部分工作表 如果希望打印工作簿中的某几张工作表，可以在按住Ctrl键的同时， 逐个单击待打印的工作表的标签，最后单击工具栏上的“打印”按钮。 如果希望打印的部分工作表具有连续的页码，应该在选中这些工作表 后，设置页眉或页脚中打印页次，再进行打印。 2、打印工作表的特定区域 如果希望打印一张工作表中的部分区域而不是全部区域时，可以先选 定待打印的单元格区域，然后单击菜单“文件”“打印区域”“设置打 印区域”。（设置好后，可以取消） 另一方法，选择区域后，文件打印选定区域 工作表的打印 3、重复打印顶端标题行和左侧标题行 有些表格清单很长，需多页显示，要求打印时每页都要有标题行，如 何实现？ 1)文件页面设置工作表 2)设置顶端标题行或左端标题行 4、在报表的每一页上打印公司Logo 在“页面设置”“页眉页脚”中进行设置（插入图片） 5、打印批注 1)在“工具”“选项”“视图”显示“批注和标识符” 2)“页面设置”“工作表”“打印”“批注”“如同工作表显示” 目录 第二部分数据处理及分析 摘 要 数据排序 数据筛选 数据有效性 数据分列 组及分级显示 排序：在使用排序的时候要注意一个问题：如果你首先选 中了一列，然后使用工具栏上的“升序”或“降序”按钮进行排 序，那么进行排列的就只是所选中的部分的数据，而不是 整个工作表中的记录。 应用：隔行插入空行 数据的排序 数据的筛选 1、自动筛选 通配符: 其中问号（？）代表任意单个字符，星号 （*）代表任意一组字符 2、高级筛选 “选择不重复的记录”具体应用举例 数据的有效性 1、给用户提供一个选择列表 2、限定输入内容的类型或大小 3、自定义设置（输入信息，出错警告，输入法） 应用举例：创建动态下拉列表 数据的分列 如果需要导入txt 等类型文件，或者把一批单元格 的数据按照特定的规律分开 应用举例：数据分列 组及分级显示 当一个表格的数据有几万行，几百列时，数据再怎么整理也会显的杂 乱无章。这时候通过对数据进行分级显示，可以使这些庞大的数据具 有层次感，并能让数据按需显示，看起来更生动。 1、自动建立分级显示 如果数据很规范，可以通过使用分类汇总功能轻易的得到分级显示。 这也是大家常用的一种方法。 2、手动建立分级显示 当数据是一些不规则的数据或者文本时，可以使用手动建立分级显示 。方法是选择中父项目下的子项目的整行或者整列，然后用鼠标单击 菜单栏的数据选项，选择组及分级显示,然后选择组合。 数据透视表 本次培训不介绍 目录 第三部分函数 摘 要 了解和更正公式错误 文本函数 查找函数 计算求和函数 错误值： 输入到单元格中 的数据太长或单元格公式 所产生的结果太大，使结 果在单元格中显示不下。 或是日期和时间格式的单 元格做减法，出现了负值 。 增加列的宽度， 使结果能够完全显示。如 果是由日期或时间相减产 生了负值引起的，可以改 变单元格的格式，比如改 为文本格式，结果为负的 时间量 含义 ： 办法 ： 错误值： DIV/0! 试图除以0。这个 错误的产生通常有下面几 种情况：除数为0、在公 式中除数使用了空单元格 或是包含零值单元格的单 元格引用 修改单元格引用 ，或者在用作除数的单元 格中输入不为零的值 含义 ： 办法 ： 错误值：VALUE! 输入引用文本项的 数学公式。如果使用了不 正确的参数或运算符，或 者当执行自动更正公式功 能时不能更正公式，都将 产生错误信息VALUE! 。 这时应确认公式或函 数所需的运算符或参数正 确，并且公式引用的单元 格中包含有效的数值。例 如，单元格C4中有一个数 字或逻辑值，而单元格D4 包含文本，则在计算公式 =C4D4时，系统不能将 文本转换为正确的数据类 型，因而返回错误值 VALUE!。 含义 ： 办法 ： 错误值： N/A 无信息可用于所 要执行的计算。在建立模 型时，用户可以在单元格 中输入#N/A，以表明正 在等待数据。任何引用含 有#N/A值的单元格都将 返回#N/A 在等待数据的单 元格内填充上数据。 含义 ： 办法 ： 错误值：REF! 删除了被公式引用的 单元格范围。 恢复被引用的单元格 范围，或是重新设定 引用范围。 含义 ： 办法 ： 错误值：NAME? 在公式中使用了 Excel所不能识别的文本 ，比如可能是输错了名称 ，或是输