精品excel中级培训教程(从入门到精通)
合集下载
- 1、下载文档前请自行甄别文档内容的完整性，平台不提供额外的编辑、内容补充、找答案等附加服务。
- 2、"仅部分预览"的文档,不可在线预览部分如存在完整性等问题,可反馈申请退款(可完整预览的文档不适用该条件!)。
- 3、如文档侵犯您的权益，请联系客服反馈,我们会尽快为您处理(人工客服工作时间：9:00-18:30)。
请使用 ?（问号） 若要查找 任何单个字符 例如，sm?th 查找“smith”和“smyth” 任何字符数 例如，*east 查找“Northeast”和 “Southeast” 问号、星号或波形符 ~（波形符）后跟 ?、* 或 ~ 例如，“fy91~?”将会查找“fy91?”
*（星号）
四、数据筛选（10Min）
二、文本的处理（5Min）
1、字符串的编辑
实例1：工作中经常使用除法进行两个数据的相除；得到的结果往往不是整数； 此时如果要显示固定位数怎么办？常规工具条中的减少小数位数只能在显示上 “欺骗”视觉，实际在单元格内还是多位数； 字符串的编辑函数FIxed是解决方法之一：
当结果是非数字类型时,乘1即可变为数字类型
三、使用函数（40Min）
5、统计函数
1）获取数组的最大/最小值————MAX,MAXA/MIN,MINA MAX与MIN功能是获取所有数据的最小值； MAXA与MINA功能是获取所有非空单元格的最小值 2）单元格的统计函数 COUNT与COUNTA 统计单元格个数； COUNTBLANK 统计空白单元格个数；COUNTIF：按条件统计； COUNTIFS：按多重条件统计
2）. 文本运算符：
3）、比较运算符 比较运算符 =(等号) 运算符含义 相等 示 例
“&”号,可以将文本连接起来.
B1=C1,若B1中单元格内的值确实与C1中 的值相等,则产生逻辑真值TRUE,若不相 等,则产生逻辑假值FALSE B1<C1 B1>C1,若B1中数值为6,C1中数值为4,则 条件成立产生逻辑真值TRUE,否则产生逻 辑假值FALSE B1>=C1 B1<>C1 B1<=C1
前言
Excel用户层次分类原则 刚刚开始接触
新手
初级用户
基本掌握常用功能
熟练使用常用功能+数据透视表+公式
中级用户
高级用户
……+数组公式+VBA简单编程
Excel技能+专业知识+行业经验
专家
一、公式与函数基础（10Min）
1、认识公式
1.1、常量：指直接输入公式中的数字或者文本值，即不用计算的值；
一、公式与函数基础（10Min）
2、函数的输入与编辑
2.2、函数的记忆式键入 1、将光标移到单元格中，在其中输入等号和公式内容的前几位字母时，会在单 元格下方显示与这些字母相关的有效函数；（左下图） 2、如果不清楚出现的这些函数如何选择；亦可直接在编辑栏点击2003版本的 “=”号，2007版本的“fx”，在弹出的对话框中根据函数解释进行选择；
三、使用函数（40Min）
2、查询函数
Hlookup/Vlookup使用技巧 2）当行/列序列号不同时，重新输入公式比较慢，如何解决？
首先确保数据区域已经被全部选中； 鼠标点击单元格右下角，填充公式后，手动更改行数/列数即可
三、使用函数（40Min）
2、查询函数
手中有一个部门的电话号码清单，现在有其中一个人的电话号码，需要 知道这个人是谁？查找指定数值——lookup函数
二、文本的处理（5Min）
1、字符串的编辑
实例3：我司的新物料编码规则已经限制了编码中的字符数量；新编码是12位， 旧编码17位；当面临大量编码时；如何快速区分新旧编码与错误编码？——使 用Len函数统计字符长度；
二、文本的处理（5Min）
2、返回相应值
实例1：仍然以物料编码为例：新物料编码规则中，前三位代表物料三级分类； 比如：主机面壳都是以BCA开头； 如何在大量编码中，按照物料三级分类统计 数量？——Left函数；
四、数据汇总（10Min）
3、合并计算
当数据行不多时，合并计算是数据汇总的又一个好工具；数据菜单，合并计算； 举例：按照车间，汇总“损耗工时”以及“损耗工时（HR） 3）得到如下结果；增添标题行即可得到汇总结果；
三、使用函数（40Min）
2、查询函数
无作业工时的统计界面如下，如果想知道某个部门在某一个月的无作业 工时，如何解决？——Hlookup（水平查找）
需要在数据区域 首行搜索的值 原始的数据 区域 满足条件2的单元格 在数据区域的行数
0：找不到时：返回#N/A； 1：找不到时：返回0
三、使用函数（40Min）
设置方法：
数据菜单——数据有效性 ——选择设置——序列
四、数据汇总（10Min）
1、分类汇总的计算方法
分类汇总是对数据清单进行数据分析的一种方法.在数据中实现对符合某一个 相同特性的数据行，按照特性信息进行数据汇总的功能；
举例：还是无作业工时：如果希望按照车间，来统计损耗工时以及损耗工时（HR）；
二、文本的处理（5Min）
1、字符串的编辑
实例2：统计并显示到岗人员；表格中显示每一天的到岗数，需要展示为“某 日到岗人员为**人”，使用CONCATENATE函数合并字符串；
实例2：运算符&的功能与此相似；但需手工输入；逻辑性较差 该功能在后续的多重条件VLOOKUP 中可以起到很好的辅助作用；
3、单元格和范围的引用
实例：旧的美元与人民币汇率为6.8；在下表中要求计算每行对应的人民币金额； 当汇率发生变化时，只能将每行的公式重新输入；此时使用单元格的引用更高效；
3.1、相对引用 相对引用指公式中的单元格位置将随着公式单元格的位置而改变 3.2、绝对引用 绝对引用是指公式和函数中的位置是固定不变的. 绝对引用是在列字母和行数字之前都加上美元符号”$”,如$A$4,$C$6 3.3、混合引用 混合引用是指在一个单元格引用中,既有绝对引用,也有相对引用.
课程大纲
公式与函数基础（10Min）P2-P8 认识公式 公式/函数的输入与编辑 单元格和范围的引用 文本的处理（5Min）P9-P13 字符串的编辑 字符串的截取/返回相应值 使用函数（45Min）P14-P28 日期及时间函数（5Min） 查询函数（10Min） 逻辑判断函数（10Min） 数学函数（5Min） 统计函数（5Min） 函数使用范例（10Min） 数据筛选（10Min） P29-P31 自动筛选 高级筛选 数据有效性设置及应用 数据汇总（5Min）P32-P36 分类汇总的计算方法 数据的组合与取消 合并计算 制作与使用图表（5Min） P37 使用数据透视表（15Min） 创建数据透视表 编辑数据透视表 宏的录制与运用（10Min） 宏的功能 录制与运行宏 Excel小技巧分享与讨论（10Min）
3）计算条件平均值————AVERAGEIF
四、数据筛选（10Min）
1、自动筛选：快捷键：Alt+D+F+F 2、高级筛选
数据——筛选——高级筛选； 方式1；在原有区域直接显示筛选结果；不满足条件部分隐藏不显示； 列表区域：原始数据区域； 条件区域：筛选条件区域；
2、高级筛选
通配符：以下通配符可作为筛选以及查找和替换内容时的比较条件 :
三、使用函数（40Min）
4、数学函数
1）举例：产品标准重量85KG，计算多件产品与标准重量的偏差；— ———ABS（取绝对值）
三、使用函数（40Min）
4、数学函数
2）举例：计算两数相除的余数；————MOD 成品的包装运输有数量限制，假如一个卡通箱只能包60PCS，客户的订单情况 如下；想知道整包装出货后仍有多少数量剩余？
注意：数据区域的值需要以升序排序，否则可能无法提供正确的值
三、使用函数（40Min）
3、逻辑判断函数
1）：逻辑判断入门
三、使用函数（40Min）
3、逻辑判断函数
1）：取交集进行逻辑判断
——同时满足所有条件才 Ture
2）：取并进行逻辑判断 ——只要其中一个满足就 Ture
3）：根据不同条件返回 不同值
单击 数据--组及分级显示--组合按钮
四、数据汇总（10Min）
3、合并计算
当数据行不多时，合并计算是数据汇总的又一个好工具； 举例：按照车间，汇总“损耗工时”以及“损耗工时（HR） 1）鼠标点击一处空白区域，存放合并计算结果；注意该区域不能和数据区域重叠 2）点击数据菜单：合并计算： *引用位置中 起始位置为：“车间”（B列） 结束位置为：“损耗工时（HR)”(H列） *标签位置：选中“首行”及“最左列” 确定
Fra Baidu bibliotek
方法2：数 据，分列
方法1
三、使用函数（40Min）
1、日期及时间函数
很多时候，我们需要了解当前日期/时间；但又不能老是手动调整，怎 么办？ 方法1：Today（）函数与Now（）函数
方法2：使用快捷键 1)Ctrl+； 显示当前日期 2)Ctrl+shift+；显示当前时间 3)“Ctrl+;”，然后按空格键 最后按“Ctrl+Shift+;显示当 前日期和时间 区别： Now和Today函数是动态的； 但快捷键得到的是静态的；
2、查询函数
如果无作业工时的统计界面如下，想知道某个部门在某一个月的无作业 工时，又如何解决？——Vlookup（竖直查找）
需要在数据区域 首列搜索的值PMC部 原始的数据 区域 满足条件2的单元格 在数据区域的列数
三、使用函数（40Min）
2、查询函数
Hlookup/Vlookup使用技巧 1）如何获取查询满足条件的列序列号
3）两数相除的整数部分：——QUOTIENT 函数
三、使用函数（40Min）
4、数学函数
4）条件求和；————SUMIf 各部门Q1 无作业工时汇总如下，如何快速取得一个或多个部门的Q1无作 业工时之和？
三、使用函数（40Min）
4、数学函数
5）舍入和取整函数————Ceiling/Floor（按条件向上/下舍入） Even/ODD（按条件向上舍入为偶数/奇数），INT：向下取整；
一、公式与函数基础（10Min）
2、公式的输入与编辑
2.3、公式编辑 1）、双击公式所在的单元格，在单元格内直接更改 2）、选中公式所在的单元格，然后单击公式编辑栏，在编辑栏内重新输入正确 的公式 3）、选中公式所在的单元格，按F2键激活单元格的公式，在其中进行修改
一、公式与函数基础（10Min）
1）首先按照车间进行排序 2）数据菜单---选择分类汇总——
四、数据汇总（10Min）
举例：希望按照车间，来统计损耗工时以及损耗工时（HR）；
点击按钮2
点击按钮1
四、数据汇总（10Min）
2、数据的组合与取消
在同一个Excel sheet表中；当数据行/列非常多，对于大部分客户来说，只希 望阅读到重点字段，可使用组合按钮；使报表外观更直观，简洁； 举例：在下表中，如果只需要关注车间及损耗工时字段；除了使用隐藏按钮外； 亦可使用组合按钮 选中“车间”与“损耗工时”之间的数据列 点击：“损耗工时”上方按钮，自动合并上一步骤选中列；按钮由—变成+
<(小于号) >(大于号)
小于 大于
>=(大于等于号) 大于等于 <>(不等号) 不等于 <=(小于等于号) 小于等于
一、公式与函数基础（10Min）
1.2、公式中的运算符
4）. 引用运算符（实例中应该是C列，不是D列）
一、公式与函数基础（10Min）
2、公式的输入与编辑
2.1、公式的输入 将光标移到单元格中，在其中输入等号和公式内容即可；输入过程中（左下 图），输入的字符会同时显示在单元格和公式编辑栏； 输入完成后按enter键， 再次选中该单元格，可见只有在编辑栏才会显示公式，在单元格内则只显示结果 （右下图）
3、数据有效性设置及应用
举例：在意见收集时，经常碰到收集意见太发散的情况；比如以下情况；对一 次Team building 进行意见收集；得到左下图的结果。作为一个活动组织者，如 何统计最终意见？ 解决办法：在发出意见收集表之前，使用数据有效性设置功能，给出规定的几类 选项，要求Team成员做“选择题”而非“自由回答”
1.2、公式中的运算符： 1）算术运算符
算术运算符号 运算符含义 示例
+(加号)
-(减号) *(星号 /(斜杠)
加
减 乘 除
2+3=5
3-1=2 3*2=6 6/2=3
%(百分号)
^(脱字号)
百分号
乘方
50%
4^3=43=64； 2^4=16
一、公式与函数基础（10Min）
1、认识公式
1.2、公式中的运算符
前三位代码截取OK后， 如何进行数量统计？ 参考数据汇总章节~~
二、文本的处理（5Min）
2、返回相应值
实例2：right与MID函数；与Left函数类似，截取数据右侧或者中间固定位数 的数据； 实例3：在公司的物料编码描述中，可以看到完整的物料描述非常长；如果只 想保留最开始的中文描述，怎么办？
*（星号）
四、数据筛选（10Min）
二、文本的处理（5Min）
1、字符串的编辑
实例1：工作中经常使用除法进行两个数据的相除；得到的结果往往不是整数； 此时如果要显示固定位数怎么办？常规工具条中的减少小数位数只能在显示上 “欺骗”视觉，实际在单元格内还是多位数； 字符串的编辑函数FIxed是解决方法之一：
当结果是非数字类型时,乘1即可变为数字类型
三、使用函数（40Min）
5、统计函数
1）获取数组的最大/最小值————MAX,MAXA/MIN,MINA MAX与MIN功能是获取所有数据的最小值； MAXA与MINA功能是获取所有非空单元格的最小值 2）单元格的统计函数 COUNT与COUNTA 统计单元格个数； COUNTBLANK 统计空白单元格个数；COUNTIF：按条件统计； COUNTIFS：按多重条件统计
2）. 文本运算符：
3）、比较运算符 比较运算符 =(等号) 运算符含义 相等 示 例
“&”号,可以将文本连接起来.
B1=C1,若B1中单元格内的值确实与C1中 的值相等,则产生逻辑真值TRUE,若不相 等,则产生逻辑假值FALSE B1<C1 B1>C1,若B1中数值为6,C1中数值为4,则 条件成立产生逻辑真值TRUE,否则产生逻 辑假值FALSE B1>=C1 B1<>C1 B1<=C1
前言
Excel用户层次分类原则 刚刚开始接触
新手
初级用户
基本掌握常用功能
熟练使用常用功能+数据透视表+公式
中级用户
高级用户
……+数组公式+VBA简单编程
Excel技能+专业知识+行业经验
专家
一、公式与函数基础（10Min）
1、认识公式
1.1、常量：指直接输入公式中的数字或者文本值，即不用计算的值；
一、公式与函数基础（10Min）
2、函数的输入与编辑
2.2、函数的记忆式键入 1、将光标移到单元格中，在其中输入等号和公式内容的前几位字母时，会在单 元格下方显示与这些字母相关的有效函数；（左下图） 2、如果不清楚出现的这些函数如何选择；亦可直接在编辑栏点击2003版本的 “=”号，2007版本的“fx”，在弹出的对话框中根据函数解释进行选择；
三、使用函数（40Min）
2、查询函数
Hlookup/Vlookup使用技巧 2）当行/列序列号不同时，重新输入公式比较慢，如何解决？
首先确保数据区域已经被全部选中； 鼠标点击单元格右下角，填充公式后，手动更改行数/列数即可
三、使用函数（40Min）
2、查询函数
手中有一个部门的电话号码清单，现在有其中一个人的电话号码，需要 知道这个人是谁？查找指定数值——lookup函数
二、文本的处理（5Min）
1、字符串的编辑
实例3：我司的新物料编码规则已经限制了编码中的字符数量；新编码是12位， 旧编码17位；当面临大量编码时；如何快速区分新旧编码与错误编码？——使 用Len函数统计字符长度；
二、文本的处理（5Min）
2、返回相应值
实例1：仍然以物料编码为例：新物料编码规则中，前三位代表物料三级分类； 比如：主机面壳都是以BCA开头； 如何在大量编码中，按照物料三级分类统计 数量？——Left函数；
四、数据汇总（10Min）
3、合并计算
当数据行不多时，合并计算是数据汇总的又一个好工具；数据菜单，合并计算； 举例：按照车间，汇总“损耗工时”以及“损耗工时（HR） 3）得到如下结果；增添标题行即可得到汇总结果；
三、使用函数（40Min）
2、查询函数
无作业工时的统计界面如下，如果想知道某个部门在某一个月的无作业 工时，如何解决？——Hlookup（水平查找）
需要在数据区域 首行搜索的值 原始的数据 区域 满足条件2的单元格 在数据区域的行数
0：找不到时：返回#N/A； 1：找不到时：返回0
三、使用函数（40Min）
设置方法：
数据菜单——数据有效性 ——选择设置——序列
四、数据汇总（10Min）
1、分类汇总的计算方法
分类汇总是对数据清单进行数据分析的一种方法.在数据中实现对符合某一个 相同特性的数据行，按照特性信息进行数据汇总的功能；
举例：还是无作业工时：如果希望按照车间，来统计损耗工时以及损耗工时（HR）；
二、文本的处理（5Min）
1、字符串的编辑
实例2：统计并显示到岗人员；表格中显示每一天的到岗数，需要展示为“某 日到岗人员为**人”，使用CONCATENATE函数合并字符串；
实例2：运算符&的功能与此相似；但需手工输入；逻辑性较差 该功能在后续的多重条件VLOOKUP 中可以起到很好的辅助作用；
3、单元格和范围的引用
实例：旧的美元与人民币汇率为6.8；在下表中要求计算每行对应的人民币金额； 当汇率发生变化时，只能将每行的公式重新输入；此时使用单元格的引用更高效；
3.1、相对引用 相对引用指公式中的单元格位置将随着公式单元格的位置而改变 3.2、绝对引用 绝对引用是指公式和函数中的位置是固定不变的. 绝对引用是在列字母和行数字之前都加上美元符号”$”,如$A$4,$C$6 3.3、混合引用 混合引用是指在一个单元格引用中,既有绝对引用,也有相对引用.
课程大纲
公式与函数基础（10Min）P2-P8 认识公式 公式/函数的输入与编辑 单元格和范围的引用 文本的处理（5Min）P9-P13 字符串的编辑 字符串的截取/返回相应值 使用函数（45Min）P14-P28 日期及时间函数（5Min） 查询函数（10Min） 逻辑判断函数（10Min） 数学函数（5Min） 统计函数（5Min） 函数使用范例（10Min） 数据筛选（10Min） P29-P31 自动筛选 高级筛选 数据有效性设置及应用 数据汇总（5Min）P32-P36 分类汇总的计算方法 数据的组合与取消 合并计算 制作与使用图表（5Min） P37 使用数据透视表（15Min） 创建数据透视表 编辑数据透视表 宏的录制与运用（10Min） 宏的功能 录制与运行宏 Excel小技巧分享与讨论（10Min）
3）计算条件平均值————AVERAGEIF
四、数据筛选（10Min）
1、自动筛选：快捷键：Alt+D+F+F 2、高级筛选
数据——筛选——高级筛选； 方式1；在原有区域直接显示筛选结果；不满足条件部分隐藏不显示； 列表区域：原始数据区域； 条件区域：筛选条件区域；
2、高级筛选
通配符：以下通配符可作为筛选以及查找和替换内容时的比较条件 :
三、使用函数（40Min）
4、数学函数
1）举例：产品标准重量85KG，计算多件产品与标准重量的偏差；— ———ABS（取绝对值）
三、使用函数（40Min）
4、数学函数
2）举例：计算两数相除的余数；————MOD 成品的包装运输有数量限制，假如一个卡通箱只能包60PCS，客户的订单情况 如下；想知道整包装出货后仍有多少数量剩余？
注意：数据区域的值需要以升序排序，否则可能无法提供正确的值
三、使用函数（40Min）
3、逻辑判断函数
1）：逻辑判断入门
三、使用函数（40Min）
3、逻辑判断函数
1）：取交集进行逻辑判断
——同时满足所有条件才 Ture
2）：取并进行逻辑判断 ——只要其中一个满足就 Ture
3）：根据不同条件返回 不同值
单击 数据--组及分级显示--组合按钮
四、数据汇总（10Min）
3、合并计算
当数据行不多时，合并计算是数据汇总的又一个好工具； 举例：按照车间，汇总“损耗工时”以及“损耗工时（HR） 1）鼠标点击一处空白区域，存放合并计算结果；注意该区域不能和数据区域重叠 2）点击数据菜单：合并计算： *引用位置中 起始位置为：“车间”（B列） 结束位置为：“损耗工时（HR)”(H列） *标签位置：选中“首行”及“最左列” 确定
Fra Baidu bibliotek
方法2：数 据，分列
方法1
三、使用函数（40Min）
1、日期及时间函数
很多时候，我们需要了解当前日期/时间；但又不能老是手动调整，怎 么办？ 方法1：Today（）函数与Now（）函数
方法2：使用快捷键 1)Ctrl+； 显示当前日期 2)Ctrl+shift+；显示当前时间 3)“Ctrl+;”，然后按空格键 最后按“Ctrl+Shift+;显示当 前日期和时间 区别： Now和Today函数是动态的； 但快捷键得到的是静态的；
2、查询函数
如果无作业工时的统计界面如下，想知道某个部门在某一个月的无作业 工时，又如何解决？——Vlookup（竖直查找）
需要在数据区域 首列搜索的值PMC部 原始的数据 区域 满足条件2的单元格 在数据区域的列数
三、使用函数（40Min）
2、查询函数
Hlookup/Vlookup使用技巧 1）如何获取查询满足条件的列序列号
3）两数相除的整数部分：——QUOTIENT 函数
三、使用函数（40Min）
4、数学函数
4）条件求和；————SUMIf 各部门Q1 无作业工时汇总如下，如何快速取得一个或多个部门的Q1无作 业工时之和？
三、使用函数（40Min）
4、数学函数
5）舍入和取整函数————Ceiling/Floor（按条件向上/下舍入） Even/ODD（按条件向上舍入为偶数/奇数），INT：向下取整；
一、公式与函数基础（10Min）
2、公式的输入与编辑
2.3、公式编辑 1）、双击公式所在的单元格，在单元格内直接更改 2）、选中公式所在的单元格，然后单击公式编辑栏，在编辑栏内重新输入正确 的公式 3）、选中公式所在的单元格，按F2键激活单元格的公式，在其中进行修改
一、公式与函数基础（10Min）
1）首先按照车间进行排序 2）数据菜单---选择分类汇总——
四、数据汇总（10Min）
举例：希望按照车间，来统计损耗工时以及损耗工时（HR）；
点击按钮2
点击按钮1
四、数据汇总（10Min）
2、数据的组合与取消
在同一个Excel sheet表中；当数据行/列非常多，对于大部分客户来说，只希 望阅读到重点字段，可使用组合按钮；使报表外观更直观，简洁； 举例：在下表中，如果只需要关注车间及损耗工时字段；除了使用隐藏按钮外； 亦可使用组合按钮 选中“车间”与“损耗工时”之间的数据列 点击：“损耗工时”上方按钮，自动合并上一步骤选中列；按钮由—变成+
<(小于号) >(大于号)
小于 大于
>=(大于等于号) 大于等于 <>(不等号) 不等于 <=(小于等于号) 小于等于
一、公式与函数基础（10Min）
1.2、公式中的运算符
4）. 引用运算符（实例中应该是C列，不是D列）
一、公式与函数基础（10Min）
2、公式的输入与编辑
2.1、公式的输入 将光标移到单元格中，在其中输入等号和公式内容即可；输入过程中（左下 图），输入的字符会同时显示在单元格和公式编辑栏； 输入完成后按enter键， 再次选中该单元格，可见只有在编辑栏才会显示公式，在单元格内则只显示结果 （右下图）
3、数据有效性设置及应用
举例：在意见收集时，经常碰到收集意见太发散的情况；比如以下情况；对一 次Team building 进行意见收集；得到左下图的结果。作为一个活动组织者，如 何统计最终意见？ 解决办法：在发出意见收集表之前，使用数据有效性设置功能，给出规定的几类 选项，要求Team成员做“选择题”而非“自由回答”
1.2、公式中的运算符： 1）算术运算符
算术运算符号 运算符含义 示例
+(加号)
-(减号) *(星号 /(斜杠)
加
减 乘 除
2+3=5
3-1=2 3*2=6 6/2=3
%(百分号)
^(脱字号)
百分号
乘方
50%
4^3=43=64； 2^4=16
一、公式与函数基础（10Min）
1、认识公式
1.2、公式中的运算符
前三位代码截取OK后， 如何进行数量统计？ 参考数据汇总章节~~
二、文本的处理（5Min）
2、返回相应值
实例2：right与MID函数；与Left函数类似，截取数据右侧或者中间固定位数 的数据； 实例3：在公司的物料编码描述中，可以看到完整的物料描述非常长；如果只 想保留最开始的中文描述，怎么办？