（推荐课件）EXCEL常用技巧

EXCEL常用技巧 1 1 EXCEL学习指南 2 EXCEL2003基础课程 郭安定 EXCEL基本操作 菜单 功能介绍 讲解比较清晰 建议初学者学习EXCEL2007新特性初探 郭安定 EXCEL2007新增功能介绍EXCEL2007视频基础教程网友制作 可以对EXCLE2007了解和学习 从基础概念开始微软EXCEL2003实用技巧微软教程 EXCEL技巧提高篇 适合具有一定基础的学习者EXCEL2003提高实战技巧精粹 EXCELHOME 实战技巧应用提高 比较实用 讲解清晰 适合有一定基础的学习者所附实例表有参考价值EXCEL图表实战技巧精粹 EXCELHOME Excel图表提高篇 适合需要稍复杂图表应用的人员EXCELVBA技巧精粹 EXCELHOME ExcelVBA应用的技巧 附电子书 范例和部分视频演示EXCEL2007VBA办公范例应用Excel2007VBA应用 附范例 EXCEL视频操作教程介绍 光盘 3 EXCEL学习方法提示 EXCEL概述优秀的数据分析软件 行业标准 基本技能 知识一定时期不会淘汰 功能十分强大 可以组合扩展 常用的只有5 功能 不仅是办公软件 科学计算 EXCEL学习建议 全面了解功能 知之后方有用之的可能 形成数据字段规范习惯 能够更好的发挥EXCEL的功能 建议遇到问题随时在网上搜索解决问题的办法 资源很丰富 建议使用首选EXCEL自带基本功能和函数 VBA能不用尽量不用 编程 建立使用数据表和统计图体现问题的思维 多看 用EXCEL解决一类问题途径很多 熟练应用基础上会游刃有余 4 2 EXCEL应知基础概念 5 工作表 单元格 公式输入 工作簿与工作表了解概念和基本设置 字体 表数设置 标签颜色等 单元格EXCEL2003单元格的范围 256列 65536行默认单元格名称 A1 B5等 R1C1单元格名称 R1C1 R5C6等 自定义单元格名称单元格属性 可以自定义公式输入单元格用等于号 输入公式或点击fx输入公式常用公式求和 平均 计数等直接点击即时统计显示求和 计数 平均值等 右键定制 输入公式用点选单元格的方式要善于使用公式的帮助菜单了解函数功能 6 工作表表的复制与移动 点击右键弹出菜单 选择本工作簿 则可以在本工簿中复制一个本工作表 是拷贝还是移动 如果是拷贝 打勾 选择新工作簿 则可以将本工作表生成一个新的工作簿 7 图表生成功能 图表功能是EXCEL的重要功能 必须掌握 教程均涉及 建议按教程自学 本处不再详述 图表多练习设置如何设置属性如果数据能动态更新或计算 图表也能成为动态图表 8 打印设置 页面设置的方法 选取 文件 页面设置 进入页面设置界面 顶端表头设置整页打印设置显示页设置 9 符号 绝对引用和相对引用 批量输入公式 在一个单元格的公式录入完成后 拖动单元格可以快速批量输入公式 应掌握引用单元格的变化规律 观察是绝对引用还是相对引用 一般只研究1个单元格中的公式即可 在公式中 A3 行 列均可随着单元格的拖动相对变化 A3 钉住列 行随着单元格的拖动相对变化A 3 钉住行 列随着单元格的拖动相对变化 A 3 行 列均钉住 单元格拖动不发生变化快捷转换键 鼠标放在单元格F4举例 10 常用功能键 F2进入单元格编辑状态 ESC退出 减少使用鼠标的频次 提高编辑效率F4相对引用与绝对引用变换 快速设置相对引用 绝对引用 混合引用F5定位 查找功能的扩展 举例 复制分类汇总结果 F9公式调试配合公式的帮助 解读公式功能和结果 学习EXCEL掌握 11 3 EXCEL常用函数与技巧 12 对明细表进行汇总计数 求和函数 一般 主要用于明细表 计数 COUNT C2 C100 对数值计数COUNTA筛选后的序号求和 SUM C2 C100 筛选 主要用于明细表 计数 SUBTOTAL 9 C2 C100 求和 SUBTOTAL 3 C2 C100 条件 主要用于统计表 计数 COUNTIF C2 C100 22 求和 SUMPRODUCT C2 C10022 C2 C100 举例 13 扩展 多条件匹配求和的应用 SUMPRODUCT A3 A10 条件1 C2 I2 条件2 B3 B10 条件3 C3 I9 在一个表的特定区域中查找引用同时满足多个条件数据之和 如果满足条件的必须为唯一值 可用于查找标准 如果表中符合条件的数不止一个 则会返回它们的和 实例 14 逻辑判断函数IF 判断条件 条件成立的结果 条件不成立的结果 文本连接符 文本1 静态日期 取日期的年 月 日取年 year 取月 month 取日 day 组合日期 DATE YEAR A1 MONTH A1 DAY A1 年龄计算方法 用年 月 日计算 用出生日期到当前日期的天数差折算实例 日期等常用函数 15 高级筛选 数据筛选 点击 数据 筛选 自动筛选 打勾取消筛选 点击 数据 筛选 自动筛选 去勾全部显示 点击 数据 筛选 全部显示 高级筛选 点击 数据 筛选 高级筛选 高级筛选的用处 筛选条件可以多个 条件可以事先写好 筛选的条例更加清晰 避免出错 利用 高级筛选 功能可以在一个明细表中提取不重复的值 举例 16 条件格式 条件格式用于数据表的辅助提示功能 选定要操作的单元格 点击 格式 条件格式 在 设置 界面进行设定条件格式 注意 条件格式如果设定公式 也存在绝对引用和相对引用的关系 格式刷刷格式 即会产生相对引用 已经设定条件格式的单元格拖拽 条件格式会自动复制到拖拽的单元格中EXCEL2003版条件格式最多可以设置3个 第一个权限最高 实例 继续添加条件 删除已添加的条件 17 工作表的保护 当进行 保护工作表 时所发生的结果 选定允许编辑的单元格 录入时 指定单元格可以填入 其他不能更改EXCEL工作表保护功能只能做一般性保护 设置了一道屏障 但仍比较容易破解 重要信息不能依靠此方式 18 数据有效性 数据有效性的使用主要在于约束数据规范格式 对后续的方便统计工作会很有帮助 选定要操作的单元格 点击 数据 有效性 在 设置 界面 允许 框内选取对数据的约束类型 选择 序列 设置填写的序列的内容 填写时可有下拉菜单 序列的内容以逗号 小写 分割 见右图 实例 小写逗号 默认已选择 19 数组公式 数组公式对一组或多组值执行多重计算 并返回一个或多个结果 可以是一个或多个数值 或是一组或多组数值 并在编辑栏可以看到以 括起来的公式按Ctrl Shift Enter键录入公式 20 选择单元格 快捷键Ctrl F不仅可以选择数值 公式内容 而且可以选择格式选择格式完成后一定要 清除查找格式 21 MACH INDEX函数的使用 多条件引用文本型数据范例 MATCH value array type 返回在指定方式下与指定数值匹配的数组中元素的相应位置value为需要在array中查找的数值 array可能包含所要查找的数值的连续单元格区域 type为数字 1 0或1 1 查找小于或等于value的最大数值 0 查找等于value的第一个数值 1 查找大于或等于value的最小数值 如果省略type 则假设为1 INDEX reference row num column num area num 返回 多个或单个 指定数据区域中的某行某列的值reference指的是要检索的范围 row num指的是指定返回的行序号column num指的是指定返回的列序号 多列区域时 单列可以取消 area num指的是返回该区域中行和列的交叉域 可省略 默认1 22 OFFSET函数 OFFSET 参数1 参数2 参数3 参数4height 参数5width 返回 参数1 单元格 左上角 往下数 参数2 行往右数 参数3 列的单元格的值 按偏移后单元格为起点选定下 参数4height 行 右 参数5width 列的一个区域 如果行数和列数偏移量超出工作表边缘 函数OFFSET返回错误值 REF 如果省略height或width 则假设其高度或宽度与 参数1 相同 函数OFFSET实际上并不移动任何单元格或更改选定区域 它只是返回一个引用 函数OFFSET可用于任何需要将引用作为参数的函数 例如 公式SUM OFFSET C2 1 2 3 1 将计算比单元格C2靠下1行并靠右2列的3行1列的区域的总值 23 数据条件匹配引用 VLOOKUP C8 表A1 A100 3 0 两张表有相同元素的列 为将一张表的数据关联到另一张表中 常用此函数 一般先实现一个单元格的内容引用 通过拖拽的方式实现批量引用 拖拽时注意绝对引用和相对引用 批量匹配实例 填写0 或者FALSE 填写被查找表自起点列后的第N列 被查找表的区域 第一列必须包含查找的关键字 本表中的关键字单元格 0为精确匹配查找 1为模糊匹配 一般不用1 24 选择性粘贴 在固化数据表时 使用选择性粘贴 数值 在整理数据时 有时会用到运算型的选择性粘贴 在整理数据时有时候会用到选择性粘贴 转置 举例 25 EXCEL表内容图片化复制 按住Shift键后 点击菜单栏的 编辑 下拉菜单中会有 复制图片 可以将选中的内容复制成图片 粘贴到其他地方 不按Shift键显示 按Shift键显示 制作幻灯片时常用 一般用文档编写时 保护原始文件用 EXCEL列数过多时 图片复制粘贴更方便 举例 26 数据透视表 实现快速统计基础是内容规范的明细表因为此功能使对明细表的统计效率较高 建议EXCEL统计工作尽量从明细表做起 利用此方法生成统计结果 如果有相对稳定格式的明细表 可以利用公式实现统计表 实例 27 其他常用功能与技巧 演示 长数字输入 输入前面加 比如输入身份证号 快速调整行宽 列宽 整行整列选中调整EXCEL文档保存加密码 保存界面 工具 安全选项 拖拽单元格 自动添加序列 右下角 自动填充选项 单元格内的文本强制换行 Alt 回车单元格显示公式 Ctrl 排序 筛选状态下升序 降序 排序界面选项 可以扩充排序功能 跨栏居中 右键 设置单元格格式 对齐 水平居中 下大纲模式 Shift Alt 左方向键 取消大纲 Shift Alt 右方向键 28 4 EXCEL解决问题实例操作 29 EXCEL解决问题实例 正向应用 1 现有一个EXCEL名单 多于实际人员数 初步判断存在重复记录 请快速确定哪条记录重复 2 一个单位现两个名单人数对不上 请找出对不上的原因何在 3 现有工时统计标准见附表1 上级要求每月上报工时统计报表见附表2 请设计一个统计方案 高效实现统计工作 使用数据透视表 利用公式 原始文件 30