Excel操作与技巧
1
2022-9-21
目录
操作与技巧
函数与公式
数据透视表与图表
表格润色
操作与技巧
- 排序
- 筛选
- 查找与替换
- 定位
- 选择性粘贴
- 数据有效性
- 分列
- 效率加倍的快捷键
操作与技巧
- 几种典型的排序方法：
- 自定义序列法：如按数值高低进行排序
- 对排序选项进行设定：如对单元格填充颜色排序，（需注意：对带公式的单元格进行排序）
- 筛选的类型：
- 按文本特征筛选、按数字特征筛选、按日期特征筛选、按字体颜色或单元格颜色筛选
- 单元格格式：
- 单元格的格式有：数字格式、对齐、字体、边框、填充、保护 6种属性。
4
2022-9-21
幻灯片5
操作与技巧
- 选择性粘贴：
- 区别数值、公式、格式等
- 不同粘贴方式。
- 乘、除、转置等高效方式。
操作与技巧
- 快捷键：
- 快捷键有很多，熟练掌握自己
- 常用的快捷键，可有效提高操
- 作表单速度。
操作与技巧
- 数据有效性的作用
- 限制单元格输入或圈识无效数据
- 做单元格下拉条
操作与技巧
分列
函数与公式
Excel 运算及其含义、语法
- 什么是公式，什么是函数
- 函数基础
- 常用函数应用
函数与公式
Excel 运算及其含义、语法
|
运算符号 |
运算功能 |
优先级 |
|
（） |
括号 |
1 |
|
- |
负号 |
2 |
|
% |
百分号 |
3 |
|
Λ |
乘方 |
4 |
|
*与/ |
乘、除法 |
5 |
|
与- |
加、减法 |
6 |
|
& |
文本连接 |
7 |
|
=、<、>、>=、<=、<> |
等于、小于、大于、小于等于、大于等于、不等于 |
8 |
函数与公式
什么是公式，什么是函数
公式：公式必须以“=”开头,即在Excel单元格中,凡以等号开头的
输入数据都被认为是公式,通过运算符号将数据、函数等元素按一定
顺序连接在一起,从而实现对工作表中的数据执行计算的等式。
例如：C1=A1 A2 A3 A4 B1 B2 B3 B4
函数：简而言之,函数就是预先定义好了的公式,比如上例中的公式
可以用函数C1=SUM(A1:B4)代替。函数不仅可以简化公式,而且具有
仅用运算符连接的一般公式所不能替代的功能,如查找引用,逻辑判断等。
函数与公式
函数基础
- 单元格的相对引用和绝对引用
- A1
- $A1 锁定第A列
- A$1 锁定第一行
- $A$1 锁定第A列，第一行
- 函数的语法
- 由函数名 括号 参数组成，
- 例 求和函数：sum（number1,number2, ...)
- TRUE VS FALSE
- Excel的逻辑值只有两个： True、False
- True False 与运算符进行各种计算式，需牢记True=1、False=0
函数与公式
一、文本函数
- upper
- lower
- replace
- clean
- trim
- concat
- text
- textjion
- 二、统计函数
- Max/Min
- Count是最常用的对数字类单元格计数的函数
- Countif（次数、区间判断）
- Counta（计算非空单元格）对文本类单元格进行计数的函数
函数与公式
三、数字函数
- Sumif
- Sumproduct返回对应的数组或区域的乘积之和，语法形式为SUMPRODUCT(array1, [array2], [array3], ...)
- 四、数字函数
- If/Ifs；IF（条件，表达式1，表达式2），其中，表达式可以是数字、函数、单元和或单元格区域
- Or/And
- if、or、and 双条件筛选
函数与公式
五、数据源的引用
- 相对引用、绝对引用、混合引用
- 跨表引用
- 六、查找和引用函数
- lookup：模糊查找、多条件查找、代替IF函数的多层嵌套
- vlookup：精确和模糊（简称或关键词）查找、多条件匹配
- 自动生成大批量序号/编码
- 统计游泳成绩的金、银、铜牌
函数与公式
|
显示结果 |
存在问题 |
|
##### |
单元格所在列不够宽，或使用了负的日期时间 |
|
#VALUE |
在需要输入数值或逻辑值的单元格输入文本 |
|
#REF! |
引用了无效的单元格 |
|
#N/A |
引用的单元格内没有可以使用的数值 |
|
#NUM |
参数不对 |
|
#NAME? |
含有EXCEL不能识别的名字或字符 |
|
#DIV/0! |
除数为0 |
函数与公式
常见问题解决
1、问题：设置了公式后，结果不能实时更新。
解决思路：检查系统设置的重算方法是否为“自动重算”。
方法：进入“公式”菜单下的“计算选项”页面，设置“自动”即可。
2、问题：用SUM求和后，发现总值不对。
解决思路：检查数据是否全为数值型。
方法：数值型通常自动靠右，文本型通常自动靠左（左上角会出现绿三角标志）。
选中相关单元格，右击“选择”设置单元格格式“菜单下的”数字“页面，设置成”数值“型，
将文本型值重新手动输入即可。
3、问题：设置了公式后，全表复制使用，但结果值不对。
解决思路，检查确认公式是绝对引用还是相对引用，会引发错误。
方法：绝对引用和相对引用的快捷切换键是F4。
4、问题：页面固定，使用滚动条无法翻页。
解决思路：取消窗口冻结。
方法：在”视图“菜单下面选择”冻结窗口“下拉菜单选择”
取消冻结窗口“。
5、问题：双击鼠标自动往下填充数值时，数据不能按想法出现递增。
解决思路：组合Ctrl键即可。
方法：在双击鼠标的同事按下Ctrl键，可实现数据递增。
数据透视表与图表
表格润色
表格润色
- 润色小技巧二
- 报表名称（在表格抬头，通常字号略大）
- 表头（在表格主体的顶端与侧端，通常可用颜色着重标注）
- 各字段数据值（如有文字内容应相对简略，避免视觉疲劳）
- 报表需要：
- 合适的列宽和行高（鼠标放置需求列或行线的最末端，双击即可）
- 当表格过长或过宽时，应使用“冻结窗口”功能。
- 润色小技巧一
- 超链接：在excel制作超链接和PPT类似；可用文字、图片、图形标识。
- 条件格式（字体颜色、数据条）
- 表格样式
12022-9-21操作与技巧函数与公式数据透视表与图表表格润色
1234目录22022-9-21Ø 排序Ø 筛选Ø 查找与替换Ø 定位Ø 选择性粘贴Ø 数据有效性Ø 分列Ø 效率加倍的快捷键32022-9-211操作与技巧Ø几种典型的排序方法：ü自定义序列法：如按数值高低进行排序ü对排序选项进行设定：如对单元格填充颜色排序，（需注意：对带公式的单元格进行排序）Ø筛选的类型：按文本特征筛选、按数字特征筛选、按日期特征筛选、按字体颜色或单元格颜色筛选Ø单元格格式：单元格的格式有：数字格式、对齐、字体、边框、填充、保护 6种属性。42022-9-211操作与技巧Ø选择性粘贴：区别数值、公式、格式等不同粘贴方式。Ø乘、除、转置等高效方式。52022-9-211操作与技巧Ø快捷键：快捷键有很多，熟练掌握自己常用的快捷键，可有效提高操作表单速度。
62022-9-211操作与技巧Ø数据有效性的作用ü限制单元格输入或圈识无效数据ü做单元格下拉条72022-9-211操作与技巧分列82022-9-211操作与技巧2函数与公式2ØExcel 运算及其含义、语法Ø什么是公式，什么是函数Ø函数基础Ø常用函数应用92022-9-21运算符号运算功能优先级（）括号1-负号2%百分号3Λ乘方4*与/乘、除法5 与-加、减法6&文本连接7=、<、>、>=、<=、<>等于、小于、大于、小于等于、大于等于、不等于8ØExcel 运算及其含义、语法102022-9-21函数与公式2公式：公式必须以“=”开头,即在Excel单元格中,凡以等号开头的输入数据都被认为是公式,通过运算符号将数据、函数等元素按一定顺序连接在一起,从而实现对工作表中的数据执行计算的等式。
例如：C1=A1 A2 A3 A4 B1 B2 B3 B4Ø什么是公式，什么是函数函数：简而言之,函数就是预先定义好了的公式,比如上例中的公式可以用函数C1=SUM(A1:B4)代替。函数不仅可以简化公式,而且具有仅用运算符连接的一般公式所不能替代的功能,如查找引用,逻辑判断等。
112022-9-21函数与公式2ü函数的语法由函数名 括号 参数组成，例 求和函数：sum（number1,number2, ...)Ø函数基础üTRUE VS FALSEExcel的逻辑值只有两个： True、FalseTrue False 与运算符进行各种计算式，需牢记True=1、False=0ü单元格的相对引用和绝对引用A1$A1 锁定第A列A$1 锁定第一行$A$1 锁定第A列，第一行122022-9-21函数与公式22022-9-2113函数与公式2一、文本函数üupperülowerüreplaceücleanütrimüconcatütextütextjion二、统计函数üMax/MinüCount是最常用的对数字类单元格计数的函数üCountif（次数、区间判断）üCounta（计算非空单元格）对文本类单元格进行计数的函数
2022-9-2114三、数字函数üSumifüSumproduct返回对应的数组或区域的乘积之和，语法形式为SUMPRODUCT(array1, [array2], [array3], ...)函数与公式2四、数字函数üIf/Ifs；IF（条件，表达式1，表达式2），其中，表达式可以是数字、函数、单元和或单元格区域üOr/Andüif、or、and 双条件筛选
2022-9-2115六、查找和引用函数ülookup：模糊查找、多条件查找、代替IF函数的多层嵌套üvlookup：精确和模糊（简称或关键词）查找、多条件匹配ü自动生成大批量序号/编码ü统计游泳成绩的金、银、铜牌函数与公式2五、数据源的引用ü相对引用、绝对引用、混合引用ü跨表引用2022-9-2116显示结果存在问题#####单元格所在列不够宽，或使用了负的日期时间#VALUE在需要输入数值或逻辑值的单元格输入文本#REF!引用了无效的单元格#N/A引用的单元格内没有可以使用的数值#NUM参数不对#NAME?含有EXCEL不能识别的名字或字符#DIV/0!除数为0函数与公式
22022-9-2117常见问题解决1、问题：设置了公式后，结果不能实时更新。 解决思路：检查系统设置的重算方法是否为“自动重算”。 方法：进入“公式”菜单下的“计算选项”页面，设置“自动”即可。2、问题：用SUM求和后，发现总值不对。 解决思路：检查数据是否全为数值型。 方法：数值型通常自动靠右，文本型通常自动靠左（左上角会出现绿三角标志）。
选中相关单元格，右击“选择”设置单元格格式“菜单下的”数字“页面，设置成”数值“型，将文本型值重新手动输入即可。3、问题：设置了公式后，全表复制使用，但结果值不对。 解决思路，检查确认公式是绝对引用还是相对引用，会引发错误。 方法：绝对引用和相对引用的快捷切换键是F4。4、问题：页面固定，使用滚动条无法翻页。 解决思路：取消窗口冻结。 方法：在”视图“菜单下面选择”冻结窗口“下拉菜单选择”取消冻结窗口“。
5、问题：双击鼠标自动往下填充数值时，数据不能按想法出现递增。 解决思路：组合Ctrl键即可。 方法：在双击鼠标的同事按下Ctrl键，可实现数据递增。函数与公式2数据透视表与图表
3182022-9-21表格润色4192022-9-21202022-9-21润色小技巧二报表名称（在表格抬头，通常字号略大）ü表头（在表格主体的顶端与侧端，通常可用颜色着重标注）ü各字段数据值（如有文字内容应相对简略，避免视觉疲劳）报表需要：ü合适的列宽和行高（鼠标放置需求列或行线的最末端，双击即可）ü当表格过长或过宽时，应使用“冻结窗口”功能。润色小技巧一ü超链接：在excel制作超链接和PPT类似；可用文字、图片、图形标识。ü条件格式（字体颜色、数据条）ü表格样式表格润色4212022-9-21THANKS
,
