EXCEL使用技巧.ppt

主讲 Excel使用技巧 Excel可以做什么 Excel MicrosoftOffice组件之一功能 对数据进行记录 计算与分析应用 管理 财经 金融 统计等领域 完成商业和工业任务个人事务处理专业科学运算 统计分析熟练掌握Excel的使用技巧 提高学习和工作效率 1 目录 Excel概述 基本操作技巧 数据处理与分析技巧 公式与函数 数据图表化 数据透视表 Excel2007 Excel2010 Excel2016 2 第一部分Excel概述 PART01 Excel基本概念 Excel界面 1 Excel概述 1 1基本概念工作簿 Excel创建的文件 Book1 2 3 工作表 工作簿的组成部分 存储和处理数据的主要文档数据工作表 Sheet1 2 3 图表工作表 Chart1 2 3 单元格 工作表存储数据的最基本单元行列交叉点 列标A B C 行标1 2 3 A1 B2 C3 单元格区域 多个单元格组成的矩形区域左上角单元格 右下角单元格 A1 F9 4 1 Excel概述 1 1基本概念工作簿 工作表 单元格之间的关系方格 横格 一页纸 笔记本 单元格 工作表 工作簿 5 1 Excel概述 1 2Excel界面 活动单元格 工作表标签 行标题1 列标题 1 048 576 A XFD 16 384 6 1 Excel概述 1 2Excel界面功能区 Excel2016菜单栏 选项卡 集中某一项核心任务 组 相关项的组合 命令 执行某一具体任务 更多地显示出以前隐藏在菜单和工具栏中的命令和功能 快速访问工具栏 Excel2010工具栏 固定显示最常用的命令 对话框启动器 点击获取更多选项 来做更细部的设定 7 1 Excel概述 1 2Excel界面文件选项卡Excel选项 Excel工具菜单 选项 8 1 Excel概述 1 2Excel界面名称框 编辑栏 编辑栏 显示活动单元格中的数据输入 编辑数据 值 公式 名称框 显示活动单元格的地址 名称定义名称 快速定位单元格或区域 取消 确认 插入函数向导 9 1 Excel概述 1 2Excel界面状态栏单元格模式就绪 输入 编辑自动计算结果平均值 计数 最值 求和 10 1 Excel概述 1 2Excel界面活动单元格 当前选中的单元格 黑色边框右下角 填充柄 扩展 填充序列 选中的单元格区域黑色边框 高亮活动单元格反色填充柄 11 第二部分基本操作技巧 PART02 工作簿 工作表快捷操作及美化 单元格 2 基本操作 2 1工作簿创建空白工作簿基于模板基于现有工作簿保存打开关闭 13 2 基本操作 2 2工作表 冻结窗格例题1视图 窗口 冻结窗口 冻结拆分窗格 14 2 基本操作 2 2工作表 拆分窗格视图 窗口 拆分 以选定单元格左上角位置进行拆分 15 2 基本操作 2 2工作表 并排查看视图 窗口 新建窗口 全部重排 垂直并排 同步滚轮 16 2 基本操作 2 2工作表 快速美化快速美化 套用表格格式 两种方法 单击数据表 开始 套用表格格式 Ctrl T组合键 技巧 将表格转换为普通数据区域 淡季表格中任意一个单元格 表格工具 设计选项卡 转换为区域按钮 单击数据表 按Ctrl T快捷键 17 2 基本操作 2 2工作表 快速美化例题2快速美化 使用格式刷 单元格样式选中设置好的表头区域 单击开始 样式组 单元格样式下拉箭头 新建单元格样式 创建自定义样式 快速美化其他工作表或工作簿 18 2 基本操作 2 2工作表 设置格式与高级打印单击页面布局 页面设置 打印标题 设置打印标题后 打印长表格时每页都有标题行 19 2 基本操作 2 2工作表 设置格式与高级打印例1分页打印 选择要分页处左上角的单元格 页面布局 分隔符 插入分页符 插入分页符后 Excel自动按选定的单元格的左边框和上边框 将工作表划分为多个打印区域 20 2 基本操作 2 2工作表 打印工作簿单击文件 打印 在打印设置中设置 打印整个工作簿 21 2 基本操作 2 2工作表 工作表的移动与复制选择 Shift 连续 Ctrl 不连续 单击工作表标签命令 插入 删除 重命名 移动 复制 右键 22 2 基本操作 2 3单元格 定位单元格的技巧 定位单元格 鼠标左键单击 1 Ctrl Home快速定位A1单元格2 Ctrl 方向键快速定位水平或垂直方向的的始末单元格3 Ctrl End快速定位已使用区域右下角单元格4 名称框定位 当要定位的单元格不在当前屏幕内时 23 2 基本操作 2 3单元格 选取单元格的高招例3 快速准确的选取单元格区域 1 Shift 对角单元格 快速选中任意目标区域2 Ctrl Shift Home 快速从活动单元格到A1区域3 Ctrl Shift 方向键 快速选中当前行 列数据区域4 Ctrl A 快速选中当前数据区域 全选 5 Ctrl 单击工作表标签 多表批量操作 24 2 基本操作 2 3单元格 输入数据手动输入数据多个单元格 区域同时输入相同数据 Ctrl Enter单元格内数据多行显示 自动换行 Alt Enter自动输入数据填充柄 自动填充向外拖动 扩展填充向内拖动 清除 25 第三部分数据处理与分析技巧 PART03 粘贴与分列 查找定位 数据有效性 条件格式 数据排序 筛选技巧 分类汇总 合并计算 3 数据处理与分析技巧 3 1粘贴选择性粘贴 转置 实现行列内容快速转换让原始区域在粘贴后行列互换 并能自动调整所有公式转置后正常计算 27 3 数据处理与分析技巧 3 1粘贴例3选择性粘贴 转换为数值选择性粘贴 运算 结构相同 28 3 数据处理与分析技巧 3 1分列根据分隔符号 字符个数将目标拆分成多列 1 确定数据拆分方式2 选择分隔符号或设置分列线3 为拆分后的各列设置数据格式 29 3 数据处理与分析技巧 3 1分列例4根据固定宽度方式拆分单元格 从身份证号码中提取 地区代码 和 出生日期 30 3 数据处理与分析技巧 3 2查找与替换例5查找与替换基础应用开始 编辑 查找和选择 查找 Ctrl F 替换 Ctrl H 31 3 数据处理与分析技巧 3 2查找与替换按格式查找替换单元格内容 32 3 数据处理与分析技巧 3 2查找与替换合并单元格填入数据选中合并单元格 取消合并 定位空值 方向上键 Ctrl Enter批量录入 33 3 数据处理与分析技巧 3 3数据有效性 选中单元格时出现提示信息 输入无效数据时显示出错警告 数据类型 数值范围 数据范围 34 3 数据处理与分析技巧 3 3数据有效性例6圈释无效数据 找出不符合要求数据选择数据区域 为数据添加有效性 使用圈释无效数据功能 35 3 数据处理与分析技巧 3 4条件格式例7条件格式 基于条件更改单元格区域的外观 36 3 数据处理与分析技巧 3 5数据排序例8普通排序 内置序列自定义排序 自定义序列单列 行排序 多列 行排序 37 3 数据处理与分析技巧 3 6数据筛选自动筛选 自定义筛选 仅显示满足条件的数据 隐藏不满足条件的数据 自动筛选 删除重复项 快速删除工作表重复栏目 38 3 数据处理与分析技巧 3 6数据筛选例10高级筛选 根据多个条件进行筛选列出条件 文本 表达式 高级筛选中选择列表与条件区域 39 3 数据处理与分析技巧 3 7分类汇总例9对数据按类别进行汇总 统计分析分类汇总之前一定要先进行排序 40 3 数据处理与分析技巧 3 8合并计算3例题对多张明细表快速合并计算生成汇总表 多张表格式需相同 41 第四部分公式与函数基础 PART04 公式基础技巧 常用函数 4 公式与函数 4 1公式公式和函数的区别与联系公式 对数据执行计算和操作的等式函数 预先定义的计算公式 实现复杂计算和操作 公式 A1 A2 A3 A100 100 函数 AVERAGE A1 A100 43 4 公式与函数 4 1公式公式的组成以等号开始 引用 单元格 区域 名称 运算符 函数 常量 数字或文本 44 4 公式与函数 4 1公式例10相对引用 绝对引用相对引用 公式所在单元格位置改变 则引用改变示例 B2 B3绝对引用 公式所在单元格位置改变 引用固定不变示例 B2 SUM B 2 B 12 通过 固定行号列标 45 4 公式与函数 4 2函数常用函数SUM number1 number2 求和函数AVERAGE number1 number2 算术平均函数IF logical test value if true value if false 判断函数COUNTIF range criteria 条件计数函数SUMIF range criteria sum range 条件求和函数VLOOKUP lookup value table array col index num range lookup 纵向查找函数 46 4 公式与函数 4 2函数SUM函数 SUM Number1 Number2 用途 计算所有参数数值的和 47 4 公式与函数 4 2函数AVERAGE函数 AVERAGE number1 number2 用途 计算所有参数数值的平均值 48 4 公式与函数 4 2函数例11IF函数 IF Logical Value if true Value if false 用途 判断是否满足条件 如果满足 就返回一个值 如果不满足则返回另一个值 49 4 公式与函数 4 2函数例13COUNTIF函数 COUNTIF Range Criteria 用途 计算符合特定条件的个数 要计算其中非空单元格数目的区域 以数字 表达式 文本形式定义的条件 50 4 公式与函数 4 2函数例12SUMIF函数 SUMIF Range Criteria Sum Range 用途 对满足条件的单元格求和 条件区域 求和条件 实际求和区域 51 4 公式与函数 4 2函数例14VLOOKUP函数 按列查询 要求查询的函数在第一列VLOOKUP lookup value table array col index num range lookup 查找的值 查找的区域 返回数据在区域的第几列数 模糊匹配 TRUS 省略 精确匹配 FALSE 0 52 第五部分数据图表化 PART05 图表基础 创建图表 编辑美化图表 5 数据图表化 5 1图表图表是数据的图形化表示图表构成图表区绘图区数据系列 某一行 列 横坐标轴 分类轴 纵坐标轴 数值轴 网格线图例标题 图表标题 横纵坐标轴标题 数据标签 54 5 数据图表化 5 1图表常用类型柱形图 显示一段时间内的数据变化 各项之间的比较情况折线图 显示在相等时间间隔下数据的变化趋势饼图 显示一个数据系列中各项大小与各项总和的比例条形图 显示各项之间的比较情况 适于分类轴标签很长的情况 面积图 强调数量随时间变化的程度 引起对总值趋势的注意 55 5 数据图表化 5 1图表 其他类型股价图 显示股价的波动 显示温度的波动曲面图 显示两组数据之间的不同组合圆环图 显示各个部分与整体之间的关系气泡图 对成组的三个变量进行比较雷达图 比较若干数据系列的聚合值 56 5 数据图表化 5 2快速创建图表方法一 使用功能区命令方法二 使用快捷键 Alt F1组合键 方法三 选定数据区域创建 57 5 数据图表化 5 2快速编辑图表 快速编辑项目 图表元素 样式及颜色 数据系列 类别 58 5 数据图表化 5 3编辑美化图表 柱形图 数据按大小顺序排列 突出强调一部分分类标签文字横排 便于阅读柱与柱之间的间距要小于柱的宽度去掉图例 数据直接标注的柱形上拒绝三维立体 颜色不超过三种亮色暗色交替 保证黑白打印可读去掉所有辅助网络线 59 5 数据图表化 5 3编辑美化图表 折线图 线条要足够粗 明显粗过所有非数据元素多条折线时 需要强调的那根 用最粗线型或最深的颜色表示不要使用图例 直接标记在折线后拒绝三维立体 删除网络线等辅助元素不好放太多线条 3 必要时分开做图标 60 5 数据图表化 5 3编辑美化图表 饼图 适合强调某部分 次要部分可合并去掉标注线和图例 数据标注在扇区拒绝三维饼图 避免五颜六色扇区分离 填充暖色 特效 白边框突出 技巧 旋转扇区 设置扇区分离调整图表布局 61 5 数据图表化 5 3编辑美化图表5例题 复合图表 两轴线柱组合图适合数据相差较大的图表利用次坐标轴完成设置新版本直接可选择最匹配的模板 62 第六部分数据透视表 PART06 结构与布局 分组与计算 排序与筛选 6 数据透视表 6 1结构与布局创建 单击数据区域任一单元格选择区域 插入透视表 选择基础数据区域 透视表存放位置 64 表格区 字段清单区 版面设置区 6 数据透视表 6 1结构与布局透视表界面 字段清单区 版面设置区 表格区在透视表字段列