excel高级应用功能及技巧研究报告

1、OFFICE系列培训之系列培训之- ExcelExcel高级应用高级应用 Excel高级应用 让您高效应用Excel 第2页 序言 Excel用户水平的5个层次 刚刚开始接触刚刚开始接触 基本掌握常用功能基本掌握常用功能 熟练使用常用功能熟练使用常用功能+ +数据透视表数据透视表+ +公式公式 + +数组公式数组公式+VBA+VBA简单编程简单编程 ExcelExcel技能技能+ +专业知识专业知识+ +行业经验行业经验 新手新手 初级用户初级用户 中级用户中级用户 高级用户高级用户 专家专家 Excel高级应用 让您高效应用Excel 第3页 知识结构 ExcelExcel数据处理数据处理

2、与操作与操作 ExcelExcel函数与公式函数与公式 ExcelExcel图表与图形图表与图形 Excel VBAExcel VBA与宏与宏 Excel Excel 数据分析数据分析 Excel高级应用 让您高效应用Excel 问题 p 如何在单元格中录入分数、上下标、特殊符号、身份证号 码？ p 如何在单元格区域中快速填充指定的序列？ p 如何按照特定的序列排序？ p 如何按照多个条件筛选数据？ p 如何隐藏数字的显示？ p 请问函数“=IF(3,6,9)”的返回值是多少？ 第4页 Excel高级应用 让您高效应用Excel 第5页 提纲 一、高级功能应用一、高级功能应用 二、应用技巧及快

3、捷键二、应用技巧及快捷键 三、枢纽分析表（数据透视表）三、枢纽分析表（数据透视表） Excel高级应用 让您高效应用Excel 第6页 成功，从改变习惯开始 q一些常用的操作习惯 DIY自己的工作区 管理常用的Excel文件 冻结窗格 保护工作表 隐藏工作表 q限制输入内容 验证/条件 条件格式 自动格式设定 Excel高级应用 让您高效应用Excel 高级功能应用高级功能应用 q 冻结窗格 -當有一个表很多行（项次）时，你想在表的后面也想看到 标题. 第7页 Excel高级应用 让您高效应用Excel Excel高级应用 让您高效应用Excel Excel高级应用 让您高效应用Excel E

4、xcel中的易容术中的易容术 p 所见非所得-数字的72变 都是空格惹的祸 另类的隐藏方法 录入数据自动显示单位 手机号码分段显示 快速输入员工性别 第10页 Excel高级应用 让您高效应用Excel 高级功能应用高级功能应用 如你希望某些单元格控制输入数据的范围或只可选择预定的 值，你会怎样处理 q 验证/条件（资料-验证） 整数 实数 清单 日期 时间 文字长度 自定义 第11页 Excel高级应用 让您高效应用Excel 高级功能应用高级功能应用 不满足条件也能录入 数据验证的局限性 注意使用复制粘贴功能可以数据验证的限制 第12页 Excel高级应用 让您高效应用Excel 高级功能

5、应用高级功能应用 如你希望某些单元格在输入不同范围的数据时显示不同的格 式（职工的年龄在16岁以下显示红色,以上则不显示颜色） q 条件格式(格式-设定格式化的条件） 第13页 Excel高级应用 让您高效应用Excel 高级功能应用高级功能应用 q 自动格式设定（格式-自动格式设定） 第14页 Excel高级应用 让您高效应用Excel n自动套用格式在Excel2007里面好像没 有，请问大家有什么办法吗？ 第15页 Excel高级应用 让您高效应用Excel 第16页 提纲 一、高级功能应用一、高级功能应用 二、应用技巧及快捷键二、应用技巧及快捷键 三、枢纽分析表（数据透视表）三、枢纽分

6、析表（数据透视表） Excel高级应用 让您高效应用Excel 数据录入技巧 p 特殊数据的录入 录入长数字 录入分数 q 录入特殊符号 直接插入符号 插入“a”或“r” （在字体下拉列表中选择“Marlett”字体，输入a或b为 “a”输入r 为“r” 使用Alt+ASC码 第17页 Excel高级应用 让您高效应用Excel 应用技巧及快捷键应用技巧及快捷键 q 认识自动填充 自动填充 等差填充 日期填充 自定义序列填充 快速自动填充 oCtrl+D（往下自动填充） oCtrl+R（往右自动填充） 第18页 Excel高级应用 让您高效应用Excel 应用技巧及快捷键应用技巧及快捷键 p

7、批量输入 选中批量输入单元格 输入资料 Ctrl+Enter（回车） p 快速复制一个单元格的资料至另一单元格 （Ctrl+用鼠标点原单元格资料并拖至目标单元格） p 换行 自动换行（格式/右键-存储格式-对齐方式-自动换列） 单元格换行（Alt+Enter） 第19页 Excel高级应用 让您高效应用Excel 应用技巧及快捷键应用技巧及快捷键 q 快速恢隐藏的列（将鼠标指针放置在被隐藏列标的分割线 上，并拖动） q 自动适配行高或列宽（将鼠标指针放在列标或行标的分割 线上，并双击） q 在同一单元格中输入多个测试值（工具-选项-编辑-按 Enter后，移动选取范围） 第20页 Excel高

8、级应用 让您高效应用Excel 应用技巧及快捷键应用技巧及快捷键 q 快速选择单元格 快速选择整个工作表的单元格（鼠标点击左上角的方块） 快速选择整列或整行（鼠标点击列的方块或行的方块） 快速选择整列或整行的资料 快速选择所连续单元格的资料（Shift+ / ） 快速选择所有资料（Shift+Alt+ + ） 选择不连续列或行或单元格资料（Ctrl+鼠标点击对应的列 或行或单元格） 快速跳至单行首、列首、行尾、列尾（Ctrl + 箭头） 选定整个工作表：Ctrl+A 第21页 Excel高级应用 让您高效应用Excel 应用技巧及快捷键应用技巧及快捷键 q 快速选择单元格 移动到工作表的开头：

9、CTRL+HOME 移动到工作表的最后一个单元格。：CTRL+END 移动到工作簿中下一个工作表：CTRL+PAGE DOWN 移动到工作簿中前一个工作表：CTRL+PAGE UP 第22页 Excel高级应用 让您高效应用Excel 应用技巧及快捷键应用技巧及快捷键 p 使用名称框选取定位 快速定位 快速选定区域 用名称选择区域 p 快速条件定位 批量计算合计数 批量清除公式计算结果 报表以万元显示 复制可见单元格 第23页 Excel高级应用 让您高效应用Excel 应用技巧及快捷键应用技巧及快捷键 q 快捷键 插入新工作表：SHIFT+F11 重复上一次操作：F4 或 CTRL+Y 完成

10、单元格输入并在选定区域中上移：SHIFT+ENTER 完成单元格输入并在选定区域中右移：TAB 完成单元格输入并在选定区域中左移：SHIFT+TAB 显示“单元格格式”对话框：CTRL+1 第24页 Excel高级应用 让您高效应用Excel 应用技巧及快捷键应用技巧及快捷键 q 快捷键 应用外边框：CTRL+SHIFT+ 输入时间：Ctrl+Shift+： 在公式中，显示“插入函数”对话框：Shift+F3 删除选中的单元格：Ctrl+- 插入空白单元格：Ctrl+Shift+= 第27页 Excel高级应用 让您高效应用Excel 应用技巧及快捷键应用技巧及快捷键 q 快捷键 应用“常规”

11、数字格式：Ctrl+Shift+ 应用“货币”格式：Ctrl+Shift+$ 应用“百分比”格式：Ctrl+Shift+% 第28页 Excel高级应用 让您高效应用Excel 数据分析的常规武器数据分析的常规武器 p 数据分析的常规武器 排序 筛选 合并计算 小计/分类汇总 第29页 Excel高级应用 让您高效应用Excel 数据分析的常规武器数据分析的常规武器 p 排序 常规的排序操作 使用“排序”对话框 对星期进行排序 把所有红色单元格放在报表的最前面 按行排序 按自定义序列排序 第30页 Excel高级应用 让您高效应用Excel 数据分析的常规武器数据分析的常规武器 q 筛选 自动

12、筛选（资料-筛选-自动筛选） 自定义筛选 第31页 Excel高级应用 让您高效应用Excel 数据分析的常规武器数据分析的常规武器 p 高级筛选 在1列中查找唯一值 把满足条件的纪录复制到1个新工作表 第32页 Excel高级应用 让您高效应用Excel 数据分析的常规武器数据分析的常规武器 q 合并计算 两种合并计算方法 按位置合并 勾选“标签位置”下的“首行”和“最左列”复选框 按项目合并 勾选“标签位置”下的“首行”和“最左列”复选框 注意两种方法的区别 第33页 Excel高级应用 让您高效应用Excel 数据分析的常规武器数据分析的常规武器 你想按一定的属性或类型对资料进行分组并可

13、收缩和展开， 你会怎样处理？ q 小计/分类汇总(资料-分类汇总） 在数据表中加入分类汇总 从分类汇总的数据中仅复制汇总行 在汇总行使用加粗的字体，颜色为红色 第34页 Excel高级应用 让您高效应用Excel 第35页 提纲 一、高级功能应用一、高级功能应用 二、应用技巧及快捷键二、应用技巧及快捷键 三、枢纽分析表（数据透视表）三、枢纽分析表（数据透视表） Excel高级应用 让您高效应用Excel 数据分析的终极武器数据分析的终极武器 q 你的工作表有大量数据，你怎样了解这些数据的含义？ q 这些数据能怎样解答你的问题？ q 你怎样从不同的角度看这些数据？ 让我们一起了学习怎样解决这些疑

14、问。 第36页 Excel高级应用 让您高效应用Excel 数据分析的终极武器数据分析的终极武器 p 认识数据透视表 数据透视表是Excel提供的一种交互式报表，可以根据不 同的分析目的进行汇总、分析、浏览数据，得到想要的分 析结果，它是一种动态数据分析工具。在数据透视表中可 以交换行和列来查看原数据的不同汇总结果。 Excel2007中数据透视表和以前的版本相比变化比较大， 我们可以通过经典数据透视表布局选项恢复2003版中的拖 动字段到透视表的操作。 在数据透视表字段列表对话框下方有4个区域，分别是报 表筛选、行标签、列标签和数值，对于2003版的页、行、 列、数据。 第37页 Excel

15、高级应用 让您高效应用Excel 枢纽分析表例子枢纽分析表例子 q 查看此资料，怎样能达到以下的分析目的？ 不同产品组的采购收货金额是多少？ 不同产品组各币别分别的金额是多少？ 各币别各产品组的金额是多少？ 各产品组在各月份的金额是多少？ 各币别在各月份的金额是多少？ 各月分的收货项数？ .? 第38页 Excel高级应用 让您高效应用Excel 准备工作 q 你想得到什么样的结果？ q 你想从什么样的角度分析这些数据？ q 原始数据是否有你想得到的数据？ 第39页 Excel高级应用 让您高效应用Excel 枢纽分析表向导枢纽分析表向导 把光标方到数据区域：资料枢纽分析表及图表精灵 第40页

16、 选择表或图 Excel高级应用 让您高效应用Excel 枢纽分析表向导枢纽分析表向导 如使用精灵之前光标已放到数据区域，则自动选择 第41页 选择数据区域 Excel高级应用 让您高效应用Excel 枢纽分析表向导枢纽分析表向导 选项 第42页 指定新生成的 分析表或图表位置 Excel高级应用 让您高效应用Excel 枢纽分析表向导枢纽分析表向导 选择你要选择的汇总方式 第43页 把对应字段拖到 对应的位置 Excel高级应用 让您高效应用Excel 枢纽分析表向导枢纽分析表向导 设置计算方式，如加总，平均，最大，最小 第44页 点击设置 计算方式 Excel高级应用 让您高效应用Excel 枢纽分析表向导枢纽分析表向导 选择计算方式 第45页 选择你想要 的计算方式 Excel高级应用 让您高效应用Excel 枢纽分析表向导枢纽分析表向导 按产品组汇总的金额分析表 第46页 如想知道4-PASS产品组 的详细数数据？ Excel高级应用 让您高效应用Excel 枢纽分析表向导枢纽分析表向导