Excel实战培训

Excel 实战技巧培训
21
操作技巧进阶——综合案例分析
一份市场活动赠品申请表的编制案例
名称+数据有效性+条件 格式+工作表保护的表格
单纯的表格
VS
Excel 实战技巧培训
22
操作与技巧进阶——高级筛选
高级筛选及举例
参考一份网络上的教程。请看案例
Excel 实战技巧培训
23
目录
00 01 02 03 04 05 06 07
函数与公式——热身运动 Excel运算符
让我们一起来看看Excel的常用运算符
Excel 实战技巧培训
25
函数与公式——热身运动 公式？函数？
公式：是以等号“=”开头，通过使用运算符将数据、函数等元素 按一定顺序连接在一起，从而实现对工作表中的数据执行计算的等 式。例如 C1=A1+A2+A3+A4+B1+B2+B3+B4
Excel 实战技巧培训
18
操作与技巧进阶——名称的运用
关于名称的约定
名称不得与单元格地址相同。包括R1C1样式。 名称不得使用空格。 名称不得以数字开头或单独使用数字。 Excel识别名称时，不区分大小写。 名称是一种特殊形式的“公式”，因此其长度、 内部嵌套等同样受到限制，比如内部长度最大 16,384个字符。 • 名称所用字符不能超过255个字符。 • 当名称引用单元格区域时，要考虑相对引用和绝 对引用。 • • • • •
Excel 实战技巧培训
19
操作与技巧进阶——名称的运用
定义名称的三种方法
方法1:插入名称
案例2:使用名称框
案例3:根据所选内容创建（批量）
Excel 实战技巧培训
20
操作与技巧进阶——名称的运用
关于名称的案例
案例1:在名称中使用常量 案例2:在名称中使用函数 案例3:动态名称的运用 案例4:如何一次性删除所有定义的名称
前言 操作与技巧
——原来可以如此简单
函数与公式
——Excel的精髓之处
数据透视表
——百变星君
图表
——秀的是专 业、拼的是创 意
总结 与作 业
1
2
3
4
5
6
记住三个 函数如此简单 F吧，他们很棒 热身运动，现在开始 不离不弃的好兄 TRUE vs FALSE F1, 什么是函数 弟 逻辑判断是函数的灵魂 如何快速、准确找 F4, 相对？还是绝对？ 数据汇总统计函数 到适合的函数 由你说了算 查找引用函数 兵马未动，逻辑先 F9, 一把“庖丁解牛” 文本处理函数 行 的宝刀 璀璨明珠—Sumproduct
Excel 实战技巧培训
15
操作与技巧进阶——条件格式
条件格式概述
案例1:利用条件格式标注达成率
案例2:用条件格式判断列是否有重复 案例3:用条件格式自动实现间隔底纹 案例4:用条件格式自动为小计总计设定格式
Excel 实战技巧培训
16
操作与技巧进阶——名称的运用
名称是什么东东？
名称：实际上是对Excel“对象”的命名。而 所谓对象可以是： • 单元格 • 单元格区域 • 数值、数组常量 • 公式 • 宏表函数
案例3:综合案例
Excel 实战技巧培训
31
函数起步——F9,一把“庖丁解牛”的宝刀
F9——应用举例
解析统计函数:IF+COUNTIF+SUMIF 的综合 应用
解析文本函数:FIND+MID的综合应用 解析复杂数组公式:其实就是这么简单
Excel 实战技巧培训
32
目录
00 01 02 03 04 05 06 07
Excel 实战技巧培训
10
操作与技巧基础——效率加倍的快捷键
Excel 实战技巧培训
11
目录
00 01 02 03 04 05 06 07
前言 操作与技巧
——原来可以如此简单
函数与公式
——Excel的精髓之处
数据透视表
——百变星君
图表
——秀的是专 业、拼的是创 意
总结 与作 业
1
2
基础中的进阶 基础中的基础 单元格格式 排序 保护工作表/工作簿 筛选 名称的运用 查找与替换 数据有效性 定位 条件格式 选择性粘贴 高级筛选 效率加倍的快捷键
Excel 实战技巧培训
33
函数如此简单——TRUE vs FALSE
Excel的逻辑值只有2个：TRUE FALSE
TRUE FALSE是函数的关键，可以这么说：任何 函数都是在解决TRUE 还是 FALSE的问题
TRUE FALSE 与运算符进行各种计算时，请牢 记：TRUE=1 FALSE=0，请务必牢记
——Excel的精髓之处
数据透视表
——百变星君
图表
——秀的是专 业、拼的是创 意
总结 与作 业
1

2
3
4
5
6
记住三个F吧，他们很棒 热身运动，现在开始 运算符及其含义 F1,不离不弃的好兄 Excel 弟 什么是公式、什么是函 数 F4,相对？还是绝对？ 由你说了算 如何快速、准确找到适 F9,一把“庖丁解牛” 合的函数 的宝刀
Excel 实战技巧培训
7
操作与技巧基础——查找与替换
查找与替换基本操作
案例1:如何清除或修改特定格式的单元格内容 案例2:如何查找批注中的内容 案例3:如何在整个工作簿查找 案例4:如何删除工作表中的通配符和特定字符
Excel 实战技巧培训
8
操作与技巧基础——定位
定位的概念和基本操作
案例1:如何披量清除工作表上的各种对象
VBA编程
VBA编程
透视表 函数、图表
透视表
函数、图表
基本操作
基本操作
Excel 实战技巧培训
3
前言——关于本次培训
构成
知识点 20% 实例讲解 80%
注重
解决问题的思路 学习Excel的方法 采用Excel 2007 进行讲解，尽量向
说明
低版本兼容
Excel 实战技巧培训
4ቤተ መጻሕፍቲ ባይዱ
目录
00 01 02 03 04 05 06 07
保护工作表/保护工作簿
案例1:保护工作表基础操作
案例2:如何限定用户只能选择可编辑的区域 案例3:如何在已保护的工作表中运用筛选 案例4:如何对工作簿加密
Excel 实战技巧培训
14
操作与技巧进阶——数据有效性
数据有效性的概述
案例1:如何利用数据有效性控制单元格的输入
案例2:如何限制重复值的录入 案例3:用有效性制作二级下拉菜单 案例4:利用有效性圈释不符合要求的数据
35
函数如此简单——逻辑判断是函数的灵魂
通过案例，轻松掌握逻辑运算
案例1:是否能申请经济适用房
案例2:如何根据性别和职务计算补贴
案例3:多层判断——彻底理清逻辑关系
Excel 实战技巧培训
36
函数如此简单——数据汇总统计类函数
注：数据汇总统计类将全部采用案例形式进行讲解
汇总求和类函数，关键词:sum、sumif、sumifs、subtotal 案例1:汇总多个表格中相同单元格的数据（sum) 案例2:如何汇总某品名的销售数量 （sumif) 案例3:深入理解sumif函数的“相对位置” 案例4:如何汇总月薪在2000-4000元的工资总额（sumifs) 案例5:如何忽略隐藏的行汇总数据(subtotal) 案例6:如何始终生成连续的商品序号(subtotal)
Excel 实战技巧培训
38
函数如此简单——数据汇总统计类函数
极值均值类函数，关键词:min,max,median,large,small,average
案例1:如何设置提成奖金的上下限（max,min,medeian) 案例2:如何不计重复返回一组数据中次大，次小值(large,small) 案例3:如何统计前十名成绩的平均分(average,large) 案例4:如何根据条件计算平均分(averageif,averageifs)
通过示例文件，我们来详细解释一下TRUE 和 FALSE
Excel 实战技巧培训
34
函数如此简单——逻辑判断是函数的灵魂
串联开关等同于逻辑函数 AND,即多个条 件同时满足： 比如——TRUE*TRUE
并联开关等同于逻辑函数 OR,即只要满 足一个条件： 比如——TRUE+FALSE
Excel 实战技巧培训
前言 操作与技巧
——原来可以如此简单
函数与公式
——Excel的精髓之处
数据透视表
——百变星君
图表
——秀的是专 业、拼的是创 意
总结 与作 业
1
2
热身运动，现在开始 Excel运算符及其 含义 什么是公式、什么 是函数 如何快速、准确找 到适合的函数
3
4
5
6
Excel 实战技巧培训
24
Excel 实战技巧培训
27
函数与公式——热身运动 快速选择函数
快速选择、输入函数的方法 通过“公式库”功能区快速选择输入 通过开头字母的提示快速输入 通过插入函数输入
直接输入
Excel 实战技巧培训
28
目录
00 01 02 03 04 05 06 07
前言 操作与技巧
——原来可以如此简单
函数与公式
Excel 实战技巧培训
37
函数如此简单——数据汇总统计类函数
数据计数类函数，关键词:count,countif,countifs
案例1:如何统计成绩表中的及格人数(countif,countifs) 案例2:如何统计产品规格中带“*”号的产品个数(countif) 案例3:如何统计35岁以上有房有车的人员名单 案例4:如何统计不重复的数据个数
Excel实战技巧培训
2015-4-22 吴启鹤
wuqh
目录
00 01 02 03 04 05 06 07
前言 操作与技巧
——原来可以如此简单
函数与公式
——Excel的精髓之处
数据透视表
——百变星君
图表
——专业的我 也可以做到
总结 与作 业
1
2
3
4
5
6
Excel 实战技巧培训
2
前言——Excel有些什么
排序的基本操作
案例1:如何按汉字笔画排序 案例2:如何用排序制作工资条 案例3:如何按行排序 案例4:如何对合并单元格相邻的数据区域排序
案例5:如何按单元格颜色排序（2007以上版本)
Excel 实战技巧培训
6
操作与技巧基础——筛选（自动筛选)
筛选的基本操作
案例1:如何对双行标题列表进行筛选 案例2:筛选中数据区域的注意事项 案例3:如何进行反向选择 案例4:如何不增加辅助列对日期按星期筛选
前言 操作与技巧
——原来可以如此简单
函数与公式
——Excel的精髓之处
数据透视表
——百变星君
图表
——秀的是专 业、拼的是创 意
总结 与作 业
1
2
基础中的基础 排序 筛选 查找与替换 定位 选择性粘贴 效率加倍的快捷键
3
4
5
6
Excel 实战技巧培训
5
操作与技巧基础——排序
Excel 实战技巧培训
17
操作与技巧进阶——名称的运用
为什么要使用名称
名称的优点： • 使公式、有效性、条件格式等等易于理解，比如 用“税率”代替单元格引用. • 简化公式 • 利于安全，易于维护 • 可以在条件格式、有效性中引用其他工作表单元 格或常量数组 • 调用Excel4.0宏表函数必须使用名称 • 可以在控件中调用单元格内容 • Excel的几乎任何地方那个都可以使用名称
案例2:如何运用定位行列差异单元格
案例3:如何利用定位将数据填充完整（测试题1)
Excel 实战技巧培训
9
操作与技巧基础——选择性粘贴
选择性粘贴的基本操作和意义
案例1:如何将公式转为数值 案例2:如何直接对某个区域进行加减乘除运算 案例3:如何只复制单元格中的有效性设置 案例4:关于转置功能的运用
函数：简而言之，函数就是预先定义好了的公式。比如上例中的公式 就可以用函数C1=SUM(A1:B4)代替。 函数不仅可以简化公式，而且具有仅用运算符连接的一般公式 所不能替代的功能。例如查找引用，逻辑判断等等。
Excel 实战技巧培训
26
函数与公式——函数的结钩分析
函数由函数名称，左括号，半角逗号分隔的参数，右括号组成，并且函数可以 嵌套，即一个函数的结果可以作为另一个函数的参数
3
4
5
6
Excel 实战技巧培训
12
操作与技巧进阶——单元格格式
单元格格式概述（Ctrl+1）
案例1:来浏览一些“千奇百怪”的自定义格式 案例2:如何对手机号码进行分段显示 案例3:如何对不同范围的数值使用不同格式 案例4:如何将自定义格式的显示转换成真实数据
Excel 实战技巧培训
13
操作与技巧进阶——工作表/工作簿的保护
Excel 实战技巧培训
29
函数起步——F1,不离不弃的好兄弟
F1，F1 哪里不会点哪里
自学函数的捷径全在 这里了 记住：excel本身就是 最好的参考书
Excel 实战技巧培训
30
函数起步——F4,相对？绝对？由你说了算
何为引用，有何影响？
案例1:三种引用的同时理解
案例2:为什么vlookup会出现错误值