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

Excel操作与技巧Excel操作与技巧12022-9-21目录操作与技巧函数与公式数据透视表与图表表格润色操作与技巧排序筛选查找与替换定位选择性粘贴数据有效性分列效率加倍的快捷键操作与技巧几种典型的排序方法：自定义序列。

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