Excel技巧与操作培训
四川三联家禽有限责任公司内部学习资料
2017年10月
Content
Excel
操作与技巧
2
3
1
4
操作与技巧
函数与公式
表格之美
数据透视表
2017年10月
四川三联家禽有限责任公司培训使用
2
2017年10月
四川三联家禽有限责任公司内部学习资料
null
1
操作与技巧
排序
筛选
查找与替换
定位
选择性粘贴
数据有效性
效率加倍的快捷键
排序
2017年10月
四川三联家禽有限责任公司内部学习资料
null
几种典型的排序方法：
1、自定义序列法
如：按职务高低进行排序
2、对排序选项进行设定
如：对单元格填充颜色排序
注意：对带公式的单元格进行排序
筛选的类型
2017年10月
四川三联家禽有限责任公司内部学习资料
null
按文本特征筛选、按数字特征筛选、按日期特征筛选、按字体颜色或单元格颜色筛选
选择性粘贴
2017年10月
四川三联家禽有限责任公司内部学习资料
null
区别数值、公式、格式、全部、等不同粘贴方式
学会运用乘、除、转置等高效方式
单元格格式
2017年10月
四川三联家禽有限责任公司内部学习资料
null
单元格的格式有：数字格式、对齐、字体、边框、填充、保护 6种属性
快速套用单元格样式
修改内置样式
自定义单元格样式
数字格式及自定义
日期格式
复制 Ctrl+C 粘贴 Ctrl+V
查找 Ctrl+F 替换 Ctrl+H
数据全选 Ctrl+Shift+End
撤销上一次的操作 Ctrl+Z
重做上一次的操作 Ctrl+Y
单元格内换行 Alt+Enter
快捷键有很多，熟练掌握自己常用的快捷键，可有效提高操作表单速度
快捷键
2017年10月
四川三联家禽有限责任公司内部学习资料
null
工作薄的保护
工作表、单元格的保护
工作表/工作簿的保护
2017年10月
四川三联家禽有限责任公司内部学习资料
null
总的来说利用设置密码只能对EXCEL表格提供一般安全性保护，安全等级并不高
数据有效性
2017年10月
四川三联家禽有限责任公司内部学习资料
null
数据有效性的作用：
1、限制单元格的输入或圈识无效数据
2、做单元格下拉表
利用数据有效性对单元格输入限制
单元格下拉表是数据有效性应用的重要内容，如一级下拉表、二级下拉表、三级下拉表
设置数据有效性（EXCEL）
2017年10月
四川三联家禽有限责任公司内部学习资料
null
2017年10月
四川三联家禽有限责任公司内部学习资料
null
2
函数与公式
EXCEL 运算符及其含义
什么是公式，什么是函数
函数基础
八大常用函数应用讲解
运算符
2017年10月
四川三联家禽有限责任公司内部学习资料
null
运算符 运算功能 优先级
( ) 括号 1
- 负号 2
% 百分号 3
^ 乘方 4
*与/ 乘、除法 5
+与- 加、减法 6
& 文本连接 7
=、<、>、<=、>=、<> 等于、小于、大于、小于等于、大于等于、不等于 8
公式？函数？
2017年10月
四川三联家禽有限责任公司内部学习资料
null
公式
公式：公式必须以等号“=”开头，即在Excel的单元格中，凡是以等号开头的输入数据都被认为是公式，通过使用运算符将数据、函数等元素按一定顺序连接在一起，从而实现对工作表中的数据执行计算的等式。如：C1=A1+A2+A3+A4+B1+B2+B3+B4
函数
函数：简而言之，函数就是预先定义好了的公式。比如上例中的公式就可以用函数C1=SUM(A1:B4)代替。
函数不仅可以简化公式，而且具有仅用运算符连接的一般公式所不能替代的功能。例如查找引用，逻辑判断等等。
函数的结构分析
2017年10月
四川三联家禽有限责任公司内部学习资料
null
引用：相对？绝对？由你说了算
2017年10月
四川三联家禽有限责任公司内部学习资料
null
相对
绝对
相对引用就是引用单元格或范围是可以跟随计算单元格位置变动而变动的。例如：=A1
绝对引用就是引用单元格或范围是不跟随计算单元格位置变动而变动的。
例如：=$A$1
2017年10月
四川三联家禽有限责任公司内部学习资料
null
单元格引用
引用运算符：（：冒号）、（，逗号）、（ 空格）
（1）：（冒号）——区域运算符，如B2:F6表示B2单元格到F6单元格矩形区域内的所有单元格。
（2），（逗号）——联合运算符，将多个引用合并为一个引用，如SUM(B5:B26,D4:D22)，表示B5至B26以及D4至D22所有单元格求和（SUM是求和函数）
（3） （空格） ——交叉运算符，如SUM(B5:B26 A4:D22)表示求B5至B26与A4至D22两区域交叉单元格之和，即B5至B22之和。
true vs false
2017年10月
四川三联家禽有限责任公司内部学习资料
null
Excel的逻辑值只有两个 :True False
True False与运算符进行各种计算时，请牢记 True=1 False=0 请务必牢记
2017年10月
四川三联家禽有限责任公司内部学习资料
null
常用函数简介
2017年10月
四川三联家禽有限责任公司内部学习资料
null
求和类函数最主要的有4个函数
SUM是最常用的求和函数， 但其用法却不简单。
SUMIF、SUMPRDUCT是条件求和函数，是求和函数中两个耀眼的明星，其应用十分的多变。
SUMIFS是多条件求和函数，其应用相对SUMIF要广泛。
SUMPRDUCT
SUMIF
SUMIFS
SUM
求和类函数
2017年10月
四川三联家禽有限责任公司内部学习资料
null
计数类函数最主要的有4个函数
COUNT是最常用的对数字类单元格计数的函数 。
COUNTA是对文本类单元格进行计数的函数。
COUNTIFS Excel2007中新增函数，为COUNTIF的扩展。用法与COUNTIF类似，但COUNTIF针对单一条件，而COUNTIFS可以实现多个条件同时求结果。
计数类函数
COUNTA
COUNT
COUNTIFS
COUNTIF
2017年10月
四川三联家禽有限责任公司内部学习资料
null
查找与引用类函数最主要的有9个函数
LOOKUP、HLOOKUP、VLOOKUP 是最常用的查找函数。
FIND、INDEX、MATCH、INDIRECT也是较常用的函数，但用法广泛且灵活多变。
OFFSET是一个动态查找函数，其用法十分灵活。
ROW与OFFSET搭配使用的情况较多，且多以复合加数组的形式运用。
查找与引用函数
LOOKUP
HLOOKUP
INDEX
FIND
INDIRECT
OFFSET
VLOOKUP
MATCH
ROW
2017年10月
四川三联家禽有限责任公司内部学习资料
null
文本类函数最主要的有4个函数
LEN函数是用于统计文本长度的。
LEFT函数是用于返回字符的函数。
MID函数也是一个返回字符的函数。
TEXT函数被称为“文本之王”,足见其用法广泛灵活。
LEN
MID
TEXT
文本类函数
LEFT
LEN
2017年10月
四川三联家禽有限责任公司内部学习资料
null
日期与时间类函数常用的有5个函数
DATEDIF函数用来计算两个日期之间的间隔天数、月数、年数。
YEAR函数返回以序列号表示的日期中的月份。
MONTH函数返回以序列号表示的日 期中的月份。
DATE函数代表特定日期的序列号。
TODAY函数返回系统当前日期。
YEAR
日期、时间类函数
DATEDIF
DATE
TODAY
MONTH
SUM、Average函数
2017年10月
四川三联家禽有限责任公司内部学习资料
null
用法：
sum(x1,x2,x3……x255)
Average (x1,x2,x3……x255)
其中，x1,x2 ……x255可以是数字，单元格或单元格区域的引用，也可以是表达式。
功能：
sum求所有参数x1,x2 ……x255的总和．
Average求所有参数x1,x2 ……x255的平均值
max和min
2017年10月
四川三联家禽有限责任公司内部学习资料
null
用法：
max(x1,x2,x3……x255)
min (x1,x2,x3……x255)
其中，x1,x2 ……x255可以是数字，单元格或单元格区域的引用，也可以是表达式。
功能：
max求所有参数x1,x2 ……x255的最大值．
min求所有参数x1,x2 ……x255的最小值
COUNT、countif 函数
2017年10月
四川三联家禽有限责任公司内部学习资料
null
用法：count(x1,x2,x3……x255)
其中，x1,x2 ……x255可以是数字，单元格或单元格区域的引用，也可以是表达式。
功能： 统计x1,x2 ……x255中数字的个数．
用法： COUNTIF(单元格区域, “条件”)
其中条件可以是比较式，但必须用双引号括起来．如“>90”，“<=60”
功能：统计指定单元格区域中满足条件的数字的个数．
if 函数
2017年10月
四川三联家禽有限责任公司内部学习资料
null
用法
IF(条件, 表达式1, 表达式2)
其中表达式可以是数字、函数、单元格或单元格区域。
功能
条件成立时，函数结果为表达式１的值；条件不成立时，函数的结果为表达式２的值．
if 函数的用法
2017年10月
四川三联家禽有限责任公司内部学习资料
null
IF函数可以嵌套使用，即在IF函数中还可以使用IF函数，最多可以嵌套7层。比如：
IF(A2>89,“A”,IF(A2>79,“B”))，
如果A2等于93，则该函数的结果是“A”；
如果A2等于80，则函数的结果是内层IF(A2>79,“B”)的结果，显然80>79，所以结果为“B”；
如果A2等于65呢？函数的结果就是IF(A2>79,"B")不成立时的取值，这时该函数的结果就是一个空值，因为IF(A2>79,"B")没有给出A2>79不成立时的取值，在这种情况下，Excel将返回一个空值（即什么结果都没有！）给该函数。
Vlookup函数查找
2017年10月
四川三联家禽有限责任公司内部学习资料
null
Vlookup函数能够从其它工作表（或本工作表）将某数据列中的数据查找出需要的数据。 VLOOKUP函数的用法如下：
VLOOKUP（x, table, n, r）
其中，x是要查找的值，table是一个单元格区域（即由工作表的1列或多列组成）。Excel将在table的第一列查找有无x这样的值（只在table的第1列查找有无x）
n是table中待返回的匹配值的列序号。
r可以取0或1，当r为0时，该函数将进行精确匹配查找；
当r=1时，table的第一列应按升序排列，否则找到的结果可能不正确，r=1表示Excel实行的是近似值查找
2017年10月
四川三联家禽有限责任公司内部学习资料
null
EXCEL可以提供几种函数来提取字符
LEFT：从一个字符串的起始返回确定数量的字符。
RIGHT：从字符串结尾返回确定数量的字符。
MID：在字符串中间的任意位置上返回确定数量的字符。
字符函数
2017年10月
四川三联家禽有限责任公司内部学习资料
null
如果是已知一个身份证号码(A1单元格)求年龄。可以使用如下公式取得出身年月日(假定当前单元格在B1)：
=MID(A1,7,4)&"年"&MID(A1,11,2)&"月"&MID(A1,13,2)&"日"
然后利用如下公式求年龄：
=datedif(B1,today(),”y”)
趣味应用
注意事项(一)
（1）括号（）要对应显示，不可出现单独的括号，或少括号
（2）输入法的半角全角、大小写需注意，如：“”不可输入成“” *不可输入成×
（3）如条件是寻找某个文字：南京，在函数中应输入为: “南京” 如条件是寻找某个单元格或数字：A5(A列第5行)或5 ，在函数中应输入为：A5 或5 ，无需加""符号
（4）函数中设置的计算范围需一致
（5）需在函数公式打开状态下,同时按下Ctrl+Shift+Enter, 当公式头尾出现花括号｛｝时才算计算完成
2017年10月
四川三联家禽有限责任公司内部学习资料
null
注意事项(二)
（1）源数据较大时，应将“菜单栏/工具”的重新计算调整为“手动计算”
（2）格式：不得出现空格，如：“黑电”和“黑 电”
（3）某一字段暂不需/无法统计，如是需量化的单元格，则需以“0”填充，
不可写“无”
（4）不可将明细全部删除后，在原有模板的基础上重写贴入明细再次使用，
将会改变计算范围，应重新使用新模板
（5）注意统计周期的输入
（6）注意EXCEL函数设置中某条件的计算列是否是需要的计算列
（7）如需从一个EXCEL表格的数据复制到另一表格中，应点击“菜单栏/
编辑”的链接中“断开链接”
2017年10月
四川三联家禽有限责任公司内部学习资料
null
2017年10月
四川三联家禽有限责任公司内部学习资料
null
3
数据透视表
数据透视表基础
创建基本数据透视表
在数据透视表内进行计算
用数据透视表对不同数据源进行分析
为什么要用数据透视？
2017年10月
四川三联家禽有限责任公司内部学习资料
null
通常，Excel的行为可分为两大类：计算数据和整理（格式化）数据。虽然很多内置工具和公式可以使这些任务很容易完成，但是数据透视表通常是计算、整理数据最有效的方法。它可以帮助我们以比传统的函数和公式更快更好的方式完成许多任务，有助于极大地提高必须使用EXCEL完成的大量的任务的效率，并减少错误，数据透视表不能做所有的事情，但哪怕只知道数据透视表的基本功能的用法，就可以让自己的数据分析能力和效率提高到的新水平。
什么时候用数据透视？
2017年10月
四川三联家禽有限责任公司内部学习资料
null
有大量交易数据，已很难通过有意义的方式分析和汇总
需要找出数据内部的关系并分组
需要找出数据中某一字段的一系列特定值
需要利用各种时间周期找出数据趋势
预期要频繁地查询数据分析的变化
需要创建常常包含新增加部分数据的分类汇总
需要将数据组织成易于制成图表的格式
数据透视表的结构
2017年10月
四川三联家禽有限责任公司内部学习资料
null
数据透视表的结构
值区域
行区域
列区域
报表筛选区域
数据透视表的数据源
2017年10月
四川三联家禽有限责任公司内部学习资料
null
确保数据以表格形式布局
避免在分节标题中存储数据
消除数据源中的空白和空单元格
数据源的第一行由字段标签或者各列信息的标题组成
数据源中的每一列表示唯一一类数据
数据源中的每一行表示各列中的单独一项
创建数据透视表
2017年10月
四川三联家禽有限责任公司内部学习资料
null
2017年10月
四川三联家禽有限责任公司内部学习资料
null
创建数据透视表
2017年10月
四川三联家禽有限责任公司内部学习资料
null
4
表格之美
Excel 格式化
套用表格式和自定义格式
主题格式化工作表
2017年10月
四川三联家禽有限责任公司内部学习资料
null
自动套用表格式
这些都是表格式，可以选择其中的任何一种应用于当前的工作表中
通过格式化能够突出显示重要的数据，添加提示信息，增加工作表的整体结构和组织性，美化工作表，使工作表数据的含义更加清晰
2017年10月
四川三联家禽有限责任公司内部学习资料
null
删除重复行可轻松去掉表中的重复数据。
转换为区域将表转换为普通工作表
镶边行、列即将表的奇数据行列设置为一种色彩，偶数行列设置为另一色彩
表工具的设计选项卡中有许多格式化表的功能按钮
当激活表中的任一单元格后（单击表中的任一单元格），
Excel在就会在功能区中显示出“表工
具|设计”选项卡（此选项卡平时处于隐藏不显状态）
报表排版
报表应当含有：
1、报表名称（在表格抬头，通常字号略大些）
2、表头（在表格主体的顶端与侧端，通常可用颜色着重标注）
3、各字段数据值（如有文字内容应相对简略，避免视觉疲劳）
报表需要：
1、合适的列宽和行高（鼠标放置需求列或行线的最末端，双击即可）
2、当表格过长或过宽时，应使用“冻结窗口”功能。
2017年10月
四川三联家禽有限责任公司内部学习资料
null
常见问题解决
1、问题：设置了公式后，结果不能实时更新。
解决思路：检查系统设置的重算方法是否为“自动重算”。
方法：进入“公式”菜单下的“计算选项”页面，设置“自动”即可。
2、问题：用SUM求和后，发现总值不对。
解决思路：检查数据是否全为数值型。
方法：数值型通常自动靠右，文本型通常自动靠左（左上角会出现绿三角标志）。
选中相关单元格，右击选择“设置单元格格式”菜单下的“数字”页面，设置成“数值”型，将文本型值重新手动输入即可。
2017-8-24
四川三联家禽责任有限公司
null
3、问题：设置了公式后，全表复制使用，但结果值不对。
解决思路：检查确认公式是绝对引用还是相对引用，会引发错误。
方法：绝对引用与相对引用的快捷切换键是F4。
4、问题：页面固定，使用滚动条无法翻页。
解决思路：取消窗口冻结。
方法：在“视图”菜单下面选择“冻结窗口”下拉菜单下选择“取消冻结窗口”。
5、问题：双击鼠标自动往下填充数值时，数据不能按想法出现递增。
解决思路：组合Ctrl键即可。
方法：在双击鼠标的同时按下Ctrl键，可实现数据递增。
2017年10月
四川三联家禽有限责任公司内部学习资料
null
常见问题解决
常见问题解决
2017年10月
四川三联家禽有限责任公司内部学习资料
null
Thank you!
2017年10月
四川三联家禽有限责任公司内部学习资料
null
Made by ZS
