excel简单实用技巧（Excel操作与技巧）

Excel操作与技巧

1

2022-9-21

目录

操作与技巧

函数与公式

数据透视表与图表

表格润色

操作与技巧

• 排序
• 筛选
• 查找与替换
• 定位
• 选择性粘贴
• 数据有效性
• 分列
• 效率加倍的快捷键

操作与技巧

• 几种典型的排序方法：
• 自定义序列法：如按数值高低进行排序
• 对排序选项进行设定：如对单元格填充颜色排序，（需注意：对带公式的单元格进行排序）
• 筛选的类型：
• 按文本特征筛选、按数字特征筛选、按日期特征筛选、按字体颜色或单元格颜色筛选
• 单元格格式：
• 单元格的格式有：数字格式、对齐、字体、边框、填充、保护 6种属性。

4

2022-9-21

幻灯片5

操作与技巧

• 选择性粘贴：
• 区别数值、公式、格式等
• 不同粘贴方式。
• 乘、除、转置等高效方式。

操作与技巧

• 快捷键：
• 快捷键有很多，熟练掌握自己
• 常用的快捷键，可有效提高操
• 作表单速度。

操作与技巧

• 数据有效性的作用
• 限制单元格输入或圈识无效数据
• 做单元格下拉条

操作与技巧

分列

函数与公式

Excel 运算及其含义、语法

• 什么是公式，什么是函数
• 函数基础
• 常用函数应用

函数与公式

Excel 运算及其含义、语法

函数与公式

什么是公式，什么是函数

公式：公式必须以“=”开头,即在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：精确和模糊（简称或关键词）查找、多条件匹配
• 自动生成大批量序号/编码
• 统计游泳成绩的金、银、铜牌

函数与公式

函数与公式

常见问题解决

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