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