EXCEL工作表的熟练应用技巧.ppt

《EXCEL工作表的熟练应用技巧.ppt》由会员分享，可在线阅读，更多相关《EXCEL工作表的熟练应用技巧.ppt（127页珍藏版）》请在三一办公上搜索。

1、EXCEL在财务 销售及管理工作中的高级运用,企业管理软件(ERP)与EXCEL1、Excel 散2、ERP 思想 3、ERP EXCEL 结合,培训前言,让Excel与众不同的技巧各类公式和函数的使用制作让领导满意的图表宏和自定义函数其他实用工具及技巧,内 容,一让Excel与众不同的技巧高效（工具栏、快捷键）安全（单元格、工作表和工作簿保护大全）便捷（引用、链接、超级链接和高级打印格式）美观（专业、规范）,内 容,第一步：打开自定义开关（三种方法）右键菜单或工具栏 自定义工具-自定义视图-工具栏-自定义,一(1).高 效,优化自己的工具栏和菜单,一(1).高 效,优化自己的工具栏和菜单,第

2、二步：选择 命令-类别，通过拖动可实现：在现有的工具栏内添加按钮在现有的工具栏内删除按钮在现有的菜单内添加命令在现有的菜单内删除命令创建自己的工具栏创建自己的菜单,一(1).高 效,优化自己的工具栏和菜单,练习：把下列工具按钮拖入工具栏内,一(1).高 效,熟练使用常用键盘快捷键,Excel 常用键盘快捷键：Alt+Enter 在同一单元格中新增一行 F4 重复上一个操作 Alt+F11 显示VB 编辑器 Ctrl+空格 选中整列 Shift+空格 选中整行 Ctrl+C 将所选项复制到剪贴板 Ctrl+V 从剪贴板中复制内容到选中单元格 Ctrl+Z 快速撤消操作Ctrl+Y快速恢复操作详细

3、键盘快捷键见文件：,一(2).安 全,单元格保护,1.锁定单元格和隐藏单元格公式的步骤：第一步：设置单元格格式（格式-单元格-保护）,参见文件案例-保护.xls,一(2).安 全,单元格保护,1.锁定单元格和隐藏单元格公式的步骤：第二步：保护工作表（工具-保护-保护工作表）,一(2).安 全,单元格保护,2.隐藏整行或整列的步骤：（方法一）选中某行或某列单击鼠标右键选择：隐藏,一(2).安 全,单元格保护,3.隐藏整行或整列的步骤：（方法二：分级显示）选中需要隐藏几行或几列数据-组及分级显示-组合,一(2).安 全,单元格保护,3.隐藏整行或整列的步骤：（方法二：分级显示）单击 或 可在显示和

4、隐藏之间进行切换,一(2).安 全,单元格保护,4.隐藏部分单元格内容的技巧（将文字颜色设为和背景色一样）,一(2).安 全,单元格保护,5.隐藏部分单元格内容的技巧（将单元格式修改为自定义；）,一(2).安 全,工作表保护,1.隐藏工作表步骤：（方法一）格式-工作表-隐藏,一(2).安 全,工作表保护,2.隐藏工作表步骤：（方法二）切换到 VB 编辑器(Alt+F11)选中需隐藏的工作表将 Visible 属性改为,一(2).安 全,工作簿保护,1.设置工作簿密码的步骤：(Excel 2000)文件-另存为-工具-常规选项设置打开权限密码或修改权限密码,一(2).安 全,工作簿保护,2.设置

5、工作簿密码的步骤:(Excel 2003)工具-选项-安全性设置打开权限密码或修改权限密码,一(3).便 捷,引用、链接和超级链接,引用同一工作表中其他单元格，或同一工作簿中其他工作表中的单元格称为“引用”。引用其他工作簿中的单元格称“外部引用”，或“链接”。超链接则是完全不同的概念。它类似于 Web 浏览器中超链接：单击一个超链接就会打开链接的目标，而不会把任何值传送到链接的目标。,几乎所有的公式都有单元格或区域引用.有三种类型的引用:相对引用:复制公式时,引用单元格的行或列会改变 绝对引用:复制公式时,引用单元格的行和列都不会改变 混合引用:行或列中,有一个是相对引用,另一个是绝对引用在单

6、元格引用的列字母或行号前加入美元符号$便可创建绝对引用或混合引用,可用F4键在四种引用类型中循环选择.选用适当的引用类型可以提高输入/拷贝公式的效率.,引用,一(3).便 捷,一个简单的例子(相对引用):,一(3).便 捷,参见文件案例-引用.xls,复杂一点的例子(混合引用):,一(3).便 捷,参见文件案例-引用.xls,练习：在D4单元格中输入公式，然后拷贝到D4:I13区域中所有的单元格,一(3).便 捷,参见文件案例-引用.xls,一(3).便 捷,链接,当打开含有链接的 Excel 文件时，通常 Excel 会提示您需不需要更新链接，最好选择不更新。当确实需要更新链接的内容时，可手

7、动更新：编辑-链接（如下页所示）我们甚至可以允许链接的文件更名,一(3).便 捷,链接,参见文件案例-链接.xls,一(3).便 捷,超级链接,在 Excel 中可以建立下列超链接：链接到 Web 站点 链接到其他已有文档 链接到一个还不存在的文档（Excel 将在您单击超链接时创建文档）链接到本文档其他的地方 链接到一个电子邮件地址（单击超链接时，Excel 会启动默认的邮件程序）,一(3).便 捷,引用、链接和超级链接,链接,引用,超链接,参见文件案例-链接.xls,打印格式的设定尽量把内容打印在一页内如果内容较多需分页打印,则应为每页设置好行列标题打印的文件应有公司的 Logo,文件名,

8、页码,日期等如果需要,可打印行号列标用黑白打印机打印时,最好选用单色打印一个文件的多张表可同时打印文件路径,一(3).便 捷,可以考虑:不显示网格线选用您独特的字体,如 Tahoma,Verdana(公司另有规定除外)表头,标题和正文应选用不同的字号和图案以示区别规范使用数字格式 没有必要把所有的数据都放在一张表内尽量纵向安排数据,以方便浏览设置合适的显示比例,以尽量在一屏显示全部数据(Ctrl+鼠标滚动轮)固定窗格为每一张工作表命名一个有意义的名称删除多余的工作表,一(4).美 观,参见文件案例-美观,内 容,二各类公式和函数的使用公式的定义及使用公式的基本技巧不使用公式就知道某些计算结果的

9、技巧计算复杂公式中某些中间结果的技巧如何定义名称和在公式中使用名称几个常用函数的定义及实例数据库函数六种公式错误值的理解及解决,定义所有的公式都以等号（=）开始公式中可使用的运算符有：加（+）、减（-）、乘（*）、除（/）、乘方（)、括号（()），运算规则和数学中的一样,二(1).公式的定义及基本技巧,基本技巧在公式中尽量使用引用而少输入数值可用鼠标点击单元格来输入引用，而不必手工输入适当使用括号或空格以易于阅读和理解当公式较长时，可用 Alt+Enter 强行换行，以方便阅读按F2 或双击单元格可进入公式编辑状态可按F9 进行公式的重新计算既可以复制公式，也可以复制公式的计算值,二(1).公

10、式的定义及基本技巧,在 Excel 的工作表中，选中某些连续或不连续的单元格，那么这些单元格的一些计算（求和、平均值、最大或最小等）会自动显示在状态栏，而不用输入公式计算。如下图：,二(2).不使用公式就知道计算结果的技巧,如果只想知道某个复杂公式中某部分的计算结果，可选中该部分然后按 F9，该部分公式就会显示为计算值。如下图：,二(3).计算复杂公式中某些中间结果的技巧,参见文件案例-薪酬.xls,注意：该部分应该是完整的能计算出结果的公式检查完后可按 ESC 键退出，而不能按回车键，否则选中的部分就会被计算结果代替,二(3).计算复杂公式中某些中间结果的技巧,参见文件案例-薪酬.xls,在

11、 Excel2003 中可用公式求值工具一步步测算每一部分的计算结果。如下图：,二(3).计算复杂公式中某些中间结果的技巧,二(3).计算复杂公式中某些中间结果的技巧,参见文件案例-函数应用.xls,二(3).计算复杂公式中某些中间结果的技巧,参见文件案例-函数应用.xls,一个例子,二(4).定义和使用名称1/4,参见文件案例-名称.xls,定义名称Ctrl+F3,二(4).定义和使用名称2/4,在公式中插入名称 F3,二(4).定义和使用名称3/4,创建名称列表 F3,二(4).定义和使用名称4/4,IFANDCOUNTACOUNTIF SUMIFSUMPRODUCTVLOOKUPHLOO

12、KUPROUND,二(5).熟练使用常用函数1/9,参见文件案例-函数.xls,IF,二(5).熟练使用常用函数2/9,参见文件案例-函数.xls,AND,二(5).熟练使用常用函数3/9,参见文件案例-函数.xls,COUNTA,二(5).熟练使用常用函数4/9,参见文件案例-函数.xls,COUNTIF,二(5).熟练使用常用函数5/9,参见文件案例-函数.xls/练习-统计不及格人数.xls,SUMIF,二(5).熟练使用常用函数6/9,参见文件案例-函数.xls,SUMPRODUCT,二(5).熟练使用常用函数7/9,参见文件案例-函数.xls,VLOOKUP,二(5).熟练使用常用函

13、数8/9,参见文件案例-函数.xls/所得税模型.xls,ROUND,二(5).熟练使用常用函数9/9,参见文件案例-函数.xls,什么是数据库?数据库是一个内涵比较广泛的单词,在Excel 中我们通常指数据列表Excel 中的数据列表通常满足下列条件:首行由标签组成,每一个标签描述的是下面整列的内容.该标签被称为字段每一个字段都是唯一的除第一行外，其他的行被称为记录不包含空白行或列见下页示例,二(6).数据库及其函数1/4,二(6).数据库及其函数2/4,参见文件案例-函数.xls,数据库函数,二(6).数据库及其函数3/4,参见文件案例-函数.xls,数据库函数中条件的写法:可以放在数据列

14、表之外的工作表的任意位置,但通常在数据列表的上面或下面至少包含两行,第一行只包含某些或全部字段名其他行包括条件同一行的条件意味”与”不同行的条件意味”或”条件中可包含=,=,=等比较符条件中也可包含公式或单元格引用,二(6).数据库及其函数4/4,参见文件案例-函数.xls,六种公式错误值:,二(7).公式错误值的理解,参见文件案例-错误值.xls,三制作让领导满意的图表创建图表快捷键和创建图表标准四步曲识别、修改及美化图表的八要素如何在图表中使用次坐标轴如何在图表中插入对象如何创建动态图表复合饼图等其他图形的运用,内 容,F11,三(1).创建图表快捷键,参见文件案例-图表.xls,三(1)

15、.创建图表标准四步曲,3.设置图表选项,2.指定数据源,1.选择图表类型,4.指定图表放置位置,三(2).图表的八要素,图表标题,网格线,数据系列,背景区域,图例,坐标轴,数据表,数据标志,识别图表的八要素,修改图表的八要素,图表标题:命名,设置 图案,字体和对齐方式坐标轴:显示或隐藏,设置 图案,刻度,字体,数字格式和对齐方式网格线:显示或隐藏,设置 图案,刻度图例:显示或隐藏,设置 图案,字体和位置数据标志:显示或隐藏,设置 图案,字体,数字格式和对齐方式数据表:显示或隐藏,设置 图案,字体背景区域:设置 图案数据系列:设置 图案,次序,重叠比例,分类间距和系列线,三(2).图表的八要素,

16、三(3).在图表中使用次坐标轴,参见文件案例-图表.xls,(1)选中某一数据系列,右键进入数据系列格式-坐标轴-次坐标轴,三(3).在图表中使用次坐标轴,参见文件案例-图表.xls,(2)选中该数据系列,右键进入图表类型,选择另一种图型,三(3).在图表中使用次坐标轴,三(3).在图表中使用次坐标轴,三(4).在图表中插入对象,窗体按扭,三(5).创建动态图表,窗体按钮：练习,三(5).创建动态图表,参见文件练习-动态图表.xls,三(5).创建动态图表,函数 COLUMN函数 CELL函数 ADDRESS函数 INDIRECT图表函数 SERIES函数 OFFSET,参见文件案例-动态图表

17、.xls,三(5).创建动态图表,Column返回给定引用的列标。语法COLUMN(reference)Reference 为需要得到其列标的单元格。如果省略 reference，则假定为是对函数 COLUMN 所在单元格的引用。,三(5).创建动态图表,Cell返回某一引用区域的左上角单元格的格式、位置或内容等信息。语法CELL(info_type,reference)Info_type 为一个文本值，指定所需要的单元格信息的类型。Info_type 为“row”表示引用中单元格的行号。Reference 表示要获取其有关信息的单元格。如果忽略，则在 info_type 中所指定的信息将返回

18、给最后更改的单元格。,三(5).创建动态图表,Address按照给定的行号和列标，建立文本类型的单元格地址。语法ADDRESS(row_num,column_num,abs_num)Row_num 在单元格引用中使用的行号。Column_num 在单元格引用中使用的列标。Abs_num 指定返回的引用类型。Abs_num返回的引用类型:1 或省略-绝对引用;2绝对行号，相对列标;3相对行号，绝对列标;4相对引用,三(5).创建动态图表,Indirect返回由文本字符串指定的引用。此函数立即对引用进行计算，并显示其内容。语法INDIRECT(ref_text,a1)Ref_text 为对单元格的

19、引用如果 a1 为 TRUE 或省略，ref_text 被解释为 A1-样式的引用。如果 a1 为 FALSE，ref_text 被解释为 R1C1-样式的引用。,三(5).创建动态图表,Series该函数是一个特殊的函数,不能在工作表中使用.它只在生成 Excel 图表时自动生成,用来定义图表系列.但可以对它进行修改.语法 Series(名称,分类标志,值,次序)名称可选出现在图例中的名称分类标志可选出现在分类轴上的标志值必需Excel 将绘制的值次序必需系列的绘制次序,三(5).创建动态图表,Offset以指定的引用为参照系，通过给定偏移量得到新的引用。返回的引用可以为一个单元格或单元格区

20、域。语法OFFSET(reference,rows,cols,height,width)Reference 作为偏移量参照系的引用区域。Reference 必须为对单元格或相连单元格区域的引用；否则，函数 OFFSET 返回错误值#VALUE!。,三(5).创建动态图表,OffsetRows 相对于偏移量参照系的左上角单元格，上（下）偏移的行数。行数可为正数（代表在起始引用的下方）或负数（代表在起始引用的上方）。Cols 相对于偏移量参照系的左上角单元格，左（右）偏移的列数。列数可为正数（代表在起始引用的右边）或负数（代表在起始引用的左边）。Height 高度，即所要返回的引用区域的行数。He

21、ight 必须为正数。Width 宽度，即所要返回的引用区域的列数。Width 必须为正数。如果省略 height 或 width，则假设其高度或宽度与 reference 相同。,三(6).其他图表-复合饼图,参见文件案例-其他图表.xls,三(6).其他图表-雷达图,参见文件案例-其他图表.xls,三(6).其他图表-变化分析图,参见文件案例-其他图表.xls,三(6).其他图表-气泡图,参见文件案例-其他图表.xls,四宏和自定义函数什么是宏怎样录制宏怎样编写宏常用宏编写语句自定义函数的编写及实例,内 容,定义宏:一组指令,告诉 Excel 执行一个或多个操作.两种创建宏的方法a.录制b

22、.在VBE中新建,四(1).什么是宏,录制宏的步骤a.启动宏录制器b.命名宏,指定快捷键,保存位置及描述c.执行所要录制的操作d.停止宏录制器使用宏录制器的优点:快速;帮助学习使用宏录制器的缺点:某些语句不能通过录制而必须编写;常产生多余的代码.,四(2).录制宏,编写宏的步骤启动 VBE 编辑器选择:插入-模块输入 Sub,后面加宏的名称和()输入 VBA 代码输入 End Sub 结束,四(3).编写宏 1/2,VBE 窗口介绍,四(3).编写宏 2/2,最常用的宏语句MsgBox“text”If Then ElseFor Next,四(4).宏语句,参见文件案例-宏.xls,四(4).宏

23、语句,语法If condition ThenstatementsEnd if或If condition ThenstatementsElseelsestatementsEnd If,参见文件案例-宏.xls,四(4).宏语句,语法For counter=start To end Step stepstatementsNext counter,参见文件案例-宏.xls,定义:由用户自己编写是两种VBA程序中的一种和其他任何内置工作表函数一样可被公式调用以 Function 开始,以 End Function 结束需指定参数只执行计算而不执行操作,四(5).自定义函数1/2,创建自定义函数的步骤:

24、打开 VBE 编辑器选择:插入-模块输入 Function,后面加上函数名,并在括号内输入参数(如果需要)输入 VBA 代码输入 End Function 结束,四(5).自定义函数2/2,参见文件案例-自定义函数.xls,五其他实用工具及技巧 1.自动更正2.按照特定的顺序排序3.公式审核及追踪引用4.三维公式5.合并计算6.条件格式7.数据的有效性8.高级筛选9.数据透视表,内 容,五(1).自动更正,“工具”-“自动更正选项”,五(2).按照特定的顺序排序,参见文件案例-按照特定的顺序排序.xls,五(3).公式审核及追踪引用1/3,追踪引用单元格,追踪从属单元格,取消所有追踪箭头,追踪

25、引用单元格,五(3).公式审核及追踪引用2/3,参见文件案例-函数.xls-SUMIF,五(3).公式审核及追踪引用3/3,参见文件案例-函数.xls-SUMIF,五(4).三维公式,参见文件案例-三维公式.xls,按位置合并:源工作表布局同目标工作表完全相同打开目标工作表选中将要接受合并数据的区域选择 数据-合并计算,选择函数类型用鼠标选择源数据区域单击“添加“按钮单击“确定“按钮更新:选中合并数据区域选择 数据-合并计算单击“确定“按钮,五(5).合并计算1/2,参见文件案例-合并计算函数.xls,按类别合并:源工作表布局同目标工作表不完全相同打开目标工作表选中将要接受合并数据的区域的首个

26、单元格选择 数据-合并计算,选择函数类型选中 标签位置-最左列用鼠标选择源数据区域(包括行标签)单击“添加“按钮单击“确定“按钮更新:选中合并数据区域首个单元格选择 数据-合并计算单击“确定“按钮,五(5).合并计算2/2,参见文件案例-合并计算类别.xls,条件格式是一种“暗含”的格式，即只有当单元格中的数值符合某一给定的条件时，这种格式才显现出来。具体步骤参见文件设置单元格条件格式的步骤.doc,五(6).条件格式1/2,最多可设置三个条件,条件可以是判断单元格的值,也可以是根据单元格的公式,五(6).条件格式2/2,参见文件案例-条件格式.xls,1设置,五(7).数据的有效性,参见文件

27、案例数据有效性.xls,2输入信息,五(7).数据的有效性,3出错警告,五(7).数据的有效性,高级筛选是对数据列表进行操作，数据列表一般符合下列原则：首行由标签组成,每一个标签描述的是下面整列的内容.该标签被称为字段 每一个字段都是唯一的 除第一行外，其他的行被称为记录 不包含空白行或列具体步骤参见文件：高级筛选操作步骤.doc高级筛选的条件一般单独放置在数据列表之外的某个地方，写法如下：至少包含两行,第一行只包含某些或全部字段名 其他行包括条件 同一行的条件意味”与”不同行的条件意味”或”条件中可包含=,=,=等比较符 条件中也可包含公式或单元格引用,五(8).高级筛选1/2,五(8).高

28、级筛选2/2,参见文件案例高级筛选.xls,入门-创建数据透视表的步骤:规范您的数据列表（数据库）明确您的问题/要求使用数据透视表向导创建数据透视表报告,五(9).数据透视表1/13,规范您的数据列表（数据库）:首行由标签组成,每一个标签描述的是下面整列的内容.该标签被称为 字段每一个字段都是唯一的不包含空白行或列,五(9).数据透视表2/13,明确您的问题/要求:每个销售员的总订单额是多少?每个销售员一月份的订单额是多少?每一地区总订单额是多少?,五(9).数据透视表3/13,参见文件案例数据透视表.xls,使用数据透视表向导:,五(9).数据透视表4/13,参见文件案例数据透视表.xls,

29、使用数据透视表向导:你也可以在第一步直接按“完成”键取默认值:使用Excel列表或数据库作为数据来源创建数据透视表报告(而非数据透视图)使用列表里的所有数据新建一个新的工作表以放置数据透视表,五(9).数据透视表5/13,创建数据透视表报告:,五(9).数据透视表6/13,页字段,字段列表,列字段,行字段,数据区域,数据透视表工具栏,参见文件案例数据透视表.xls,修改数据透视表的基本技巧:增加字段（拖放）在行字段,列字段,页字段之间转换(拖,放)合并标志（表选项-合并标志）重命名字段（Just do it)设置数字格式（字段设置-数字）显示明细数据（双击）创建项组合(数据组合)排序更改汇总方

30、式添加新汇总字段增加计算字段,五(9).数据透视表7/13,参见文件案例数据透视表.xls,数据组合：,五(9).数据透视表8/13,排序：,五(9).数据透视表9/13,更改汇总方式：选中任一数据区域单元格-“字段设置”-更改汇总函数,五(9).数据透视表10/13,添加新汇总字段：从字段列表中选中你想要汇总的字段（可以重复）拖入数据区域 选中对应汇总行的任一数据区域单元格-“字段设置”-更改汇总函数,五(9).数据透视表11/13,增加计算字段：选中对应汇总行的任一数据区域单元格-“字段设置”-选项,五(9).数据透视表12/13,问题：如何按下列方式显示？,五(9).数据透视表13/13,步骤(按月显示)：1.将订单日期拖入行字段,五(9).数据透视表,步骤(按月显示)：2.右键单击订单日期字段，选择 组合,五(9).数据透视表,步骤(按月显示)：3.选择起止日期和步长,五(9).数据透视表,步骤(按月显示)：4.结果如下,五(9).数据透视表,步骤(按地区汇总)：1.右键单击地区字段，选择字段设置,五(9).数据透视表,步骤(按地区汇总)：2.选择求和,五(9).数据透视表,步骤(按地区汇总)：3.结果如下,五(9).数据透视表,