EXCEL函数与操作的使用及技巧.ppt

,EXCEL使用技巧,培训室：Mark,一个EXCEL工作簿，最多可插入多少张工作表？ 一个EXCEL工作表，最大可有多少行？ 一个EXCEL工作表，最多可有多少列？,关于EXCEL，你知道吗？,255,65536,256,.菜单及工具按钮 . 使用工作簿/工作表 . 编辑及格式化 . 公式及函数 . 管理和分析数据 . 图表 . 页面设置及打印,EXCEL的使用技巧,.菜单及工具按钮,1、工具栏的打开和使用： 方法一：窗口上方右健，将需要用的工具栏前面单击划上“”，工具栏即显示出来，反之，则关闭。 方法二：“菜单”“工具栏”，在右侧下拉菜单中将需要用的工具栏前面单击划上“”，工具栏即显示出来，反之，则关闭。 工具栏的移动和调整。,.菜单及工具按钮,2. 自定义工具栏和菜单 在现有的工具栏/菜单内添加或删除按钮/命令 创建自己的工具栏和菜单 “符号”工具栏 打开自定义开关: - 右键菜单或工具栏 自定义 - 工具 - 自定义 - 视图 - 工具栏 - 自定义,. 菜单及工具按钮 . 使用工作簿/工作表 . 编辑及格式化 . 公式及函数 . 管理和分析数据 . 图表 . 页面设置及打印,EXCEL的使用技巧,1、 工作簿 工作簿窗口: 多个工作簿窗口的排列 “窗口” “重排窗口” : 平铺, 水平排列, 垂直排列和层叠 “并排比较”：横向比较，同时翻动； 隐藏工作簿 (为什么打开了一个工作簿却什么也没看到 ? ) 工作簿的安全性:“工具”“选项”“安全性”,. 使用工作簿/工作表,2、 工作表 开始工作之前的一些基本考虑: 尽量采用长的而不是宽的工作表以方便用Page Up, Page Down 快速流览. 不必把所有的信息信息放在一张工作表内. 为工作表添加必要的抬头 为工作表命名简短而有意义的名称 删除多余的空白工作表 定义好所有的打印设置以方便使用者需要时直接打印,. 使用工作簿/工作表,3. 工作表基本操作和使用 插入和删除工作表 命名和重命名工作表 移动和复制工作表：不同工作簿之间表格的移动或复制； 工作表颜色标签 工作表的显示: 拆分和冻结工作表(窗口, 冻结窗口，拆分 ) 缩放工作表 (显示比例, 对工作簿的全屏显示 ) 显示方式 (普通, 分页预览) 保护工作表 ( 锁定单元格, 隐藏单元格公式 ) 隐藏工作表 ( 格式 工作表 隐藏. 注意和隐藏工作簿的区别: 窗口 隐藏 ),. 使用工作簿/工作表,. 菜单及工具按钮 . 使用工作簿/工作表 . 编辑及格式化 . 公式及函数 . 管理和分析数据 . 图表 . 页面设置及打印,EXCEL的使用技巧,1、编辑 复制, 剪切和粘贴 ( 灵活运用快捷键和自动填充功能 ) 选择性粘贴 “转置”功能 插入和删除 撤消及重复以前的操作 查找, 替换.(快捷键回顾) alt+enter 的用处，在同一单元格中换行；,. 编辑及格式化,2、格式化 格式化单元格 ( 字体, 对齐, 边框, 颜色, 图案, 大小) 条件格式,. 编辑及格式化,练习：制作家润多超市门店培训专干联系表,. 菜单及工具按钮 . 使用工作簿/工作表 . 编辑及格式化 . 公式及函数 . 管理和分析数据 . 图表 . 页面设置及打印,EXCEL的使用技巧,1、公式 所有的公式都是以 = 开头 公式包含的运算符: + - * / ( ) 混合引用 ( 巧用 F4 键 )：“固定” 如何不用公式而迅速获得常用计算结果 ( 状态栏 ),. 公式及函数,2、函数 一个函数就是一个预定义的公式 所有的函数都包括一个函数名和一系列的参数 工作中常用的函数: 数学函数: SUM，SUMIF，AVERAGE，COUNT， OUNTIF 查找函数: VLOOKUP；,. 公式及函数,. 公式及函数,常用函数：SUM及SUMIF 求和的用法： 在需要求和的列或行的最后一个单元格上单击快捷键： 在需要求和的单元格里面输入公式； SUMIF：按条件求和,SUM、SUMIF、AVERAGE、COUNT、COUNTIF公式操作举例,. 公式及函数,VLOOKUP的使用（在不同表格之间根据匹配值以列抓取数据）,Lookup_value为“需在数据表第一列中查找的数据”，可以是数值、文本字符串或引用。 （以什么值为匹配依据） Table_array 为“需要在其中查找数据的数据表”，可以使用单元格区域或区域名称等。（数据抓取范围，注意混合引用） Col_index_num 为table_array 中待返回的匹配值的列序号。 （需要的数据与匹配数据相距列数） Range_lookup 为一逻辑值，指明函数 VLOOKUP 返回时是精确匹配还是近似匹配。如果为 TRUE 或省略，则返回近似匹配值，也就是说，如果找不到精确匹配值，则返回小于lookup_value 的最大数值；如果 range_value 为 FALSE，函数 VLOOKUP 将返回精确匹配值。如果找不到，则返回错误值 #N/A。（一般用FALSE）,如何在”V2”表中，快速的将员工工号、入司时间两栏填满？,3、组合公式的创建:,. 公式及函数,如何在“数组公式”表格中，一次性快速计算出总销售额？,4 。三维公式及引用 三维引用包含单元格或区域引用，前面加上工作表名称的范围，其格式一般为“工作表标签！单元格引用” 。 如果需要使用三维引用来引用多个工作表上的同一单元格或区域，可按以下步骤进行操作： （1）单击需要输入公式的单元格。 （2）输入（等号），如果需要使用函数，再输入函数名称，接着再键入左圆括号。 （3）单击需要引用的第一个工作表标签。 （4）按住Shift键，单击需要引用的最后一个工作表标签。 （5）选定需要引用的单元格或单元格区域。 （6）完成公式，按回车键键确认。,. 公式及函数,如何在快速汇总各门店定级情况统计表？,5、&(文本运算符),. 公式及函数,MID(提取文本内容)： MID(text,start_num,num_chars) Text:是包含要提取字符的文本字符串，可以直接输入含有目标文字的单元格名称。 Start_num:是文本中要提取的第一个字符的位置。文本中第一个字符的.start_num 为 1，以此类推。 Num_chars:指定希望 MID 从文本中返回字符的个数。,如何计算出“MID”表格中各员工的年龄？,. 菜单及工具按钮 . 使用工作簿/工作表 . 编辑及格式化 . 公式及函数 . 管理和分析数据 . 图表 . 页面设置及打印,EXCEL的使用技巧,1、自动筛选： 自动筛选适用于简单条件，通常是在一个数据清单的一列中，查找相同的值，利用“自动筛选”的功能，用户可在具有大量记录的数据清单中快速查找出符合多重条件的记录。 用户一次只能对工作表中一个数据清单使用筛选命令，如果要在其他数据清单中使用该命令，则需清除本次筛选。 自动筛选是最常用，最简便的信息查找方式。 不足之处：在筛选的状态下，无法进行序列号自动填充。,.管理和分析数据,2、筛选与排序：,.管理和分析数据,字段排序快捷键在自动筛选的下拉菜单中（ Excel2003版以上），但只能按一个字段进行排序； 排序操作：“数据”“排序”，可依次按照三个关键字段进行排序； “有标题行”与“无标题行”选项的区别；,3、自定义排序： “工具”-“选项”-“自定义序列”：将所需进行的自定义序列导入电脑； 导入方式：可以直接输入，也可通过现有的列直接导入； “数据”-“排序”-“选项”-“排序选项”-“自定义排序选项”,.管理和分析数据,宜昌店定级表格中，如何将表格按照生鲜、食品、非食品的课别排序？,4、分类汇总：可以自动对所选数据进行汇总，并插入汇总行。,.管理和分析数据,选定工作表，单击“数据”-“分类汇总”命令； “分类字段”：以哪个字段数据为分类汇总依据。 “汇总方式”：是需要对汇总项进行求和？平均值？计数？ “选定汇总项”：对哪个字段数据进行汇总？ 对一个工作表，可多次进行分类汇总操作。,宜昌店定级表格中，如何按照求各课别参考人数、平均成绩？,5、数据透视表：是一种交互式报表，可快速合并和比较大量数据。,.管理和分析数据,.管理和分析数据,.管理和分析数据,.管理和分析数据,.管理和分析数据,应用举例： 销售表中：“长沙区” 如何查看各门店各部门每年的销售平均值？ 数据透视表的行、列互换； “页字段”的用处；,.管理和分析数据,数据透视表工具栏： 数据透视表向导： 刷新数据：当数据源值修改时，透视表数据自动修改； 字段设置：可轻松改变数据值的统计方法（求和、平均等11种） 设置透视表的字段排序：“字段设置”、“高级”、“排序”（手动、升序、降序、按数据源排序） 改变报表的显示方式： “字段设置”、“布局” 数据显示方式： “字段设置”、“选项”。,.管理和分析数据,数据透视表图表： 数据透视表工具栏：点击“图表”图标即可； 所有格式及显示均可调整；,数据透视表格式调整： 选中表格后，右键“设置单元格格式” 不可调整：合并单元格； 注：数据透视表数据源表表头中不能有合并单元格，字段名不能为空；,.管理和分析数据,. 菜单及工具按钮 . 使用工作簿/工作表 . 编辑及格式化 . 公式及函数 . 管理和分析数据 . 图表 . 页面设置及打印,EXCEL的使用技巧,创建一个新图表 修改及美化图表的外观 使用图表的一些原则,.图表,1、创建一个新图表 创建图表快捷键: F11,3. 设置图表 选项,2. 指定数 据源,1. 选择图表类型,4. 指定图表放 置位置,.图表,标准四步曲:,如何在图表1中，快速的作出某店05年与06年人工成本总额对比表？,2、修改及美化图表的外观 图表标题: 命名 , 设置 图案, 字体和对齐方式 坐标轴: 显示或隐藏 , 设置 图案, 刻度, 字体, 数字格式和对齐方式 网格线: 显示或隐藏 , 设置 图案, 刻度 图例: 显示或隐藏 , 设置 图案, 字体和位置 数字标志: 显示或隐藏 , 设置 图案, 字体, 数字格式和对齐方式 数据表: 显示或隐藏 , 设置 图案, 字体 背景区域: 设置 图案 数据系列: 设置 图案,次序, 重叠比例, 分类间距和系列线,.图表,练习：制作家润多超市培训调查总结图表,3、使用图表的一些原则 选用适当的图表类型 不要企图在同一张图表内同时表示超过两组的数据 不必在图表内显示太多的数字 选择恰当的坐标刻度 充分利用图表的彩色视觉功能 在图表内添加适当的对象以突出重点,.图表,. 菜单及工具按钮 . 使用工作簿/工作表 . 编辑及格式化 . 公式及函数 . 管理和分析数据 . 图表 . 页面设置及打印,EXCEL的使用技巧,1、