Excel 常用技巧实战训练讲义

Excel 常用技巧实战训练（For HR）

主讲人：李晓春

二○○六年十一月

前言

本课程为Excel使用提高班，学习本课程您应该了解Excel的基本界面和菜单，熟

悉基本操作，如插入、复制、粘贴、删除、基本四则运算、设置格式、合并单元格、查找、替换、排序、筛选、对单元格的操作、对行（列）的操作、制作简单图表等，如对上述内容一无所知，则立即停止本课程的学习，否则将会给您带来巨大的痛苦。

课程进度视学员对知识点的掌握情况而定。

如有疑问，请随时提出。

掌握本课程的内容，您将成为Excel高手，您的工作效率将提高N倍（N>1），您

将体会到高效率工作带给您的成就感和乐趣！

课程目标

掌握电子表格常用函数的用法

掌握电子表格常用技巧

掌握数据透视表的基本用法

一、常用函数

1. &的用法

用途：合并两个或两个以上的字符，可以用来解决重名问题

语法：Text1&Text2

3&5=35

“您”&“好”&“吗”=您好吗

“后勤部”&“王三亮”=后勤部王三亮

2. SUM函数

用途：求和函数，计算多个数值的和

语法：SUM(number1,number2, ...)

SUM(单元格1:单元格N)

SUM(单元格1:单元格N1,单元格N2:单元格Nn,…)

3. IF函数

用途：用来计算某条件真或假时，对应的数值

语法：IF(logical_test,value_if_true,value_if_false)

IF(条件,条件为真时的值,条件为假时的值)

技巧：可以进行多重判断，如：

IF(条件1,值1,IF(条件2,值2,值3))

例1：A列的数值在600～5000之间，设定B的数据等于A，但数值在1000～4000之间。

B1=if(A1<1000,1000,if(A1>4000,4000,A1))

例2：A列为性别（男、女），B列为对应的称谓（先生、女士）。

B1=if(A1=“男”,“先生”,if(A1=“女”,“女士”,“性别错”))

4. VLOOKUP函数

用途：用来以某一数值为基准，查找其对应的其他数值

语法：VLOOKUP(lookup_value,table_array,col_index_num,0)

VLOOKUP(基准值,查询区域,目标值所在列数,0)

注意： (1) 不能有合并单元格；

(2) 基准值不能有重复数据；

(3) 基准值必须在查询区域的第一列；

(4) 目标值所在列数，不能大于查询区域总列数。

实战用法：已知员工的身份证号码（姓名），以其为基准值，到人事数据库中，查询该身份证号码（姓名）对应的员工其他信息。

※以姓名为基准值，在遇有重名情况时，查询结果会有误。

5. ROUND函数

用途：用来解决四舍五入问题，不能用来限制屏幕显示。

语法：ROUND(number,num_digits)

ROUND(待处理的数值,四舍五入到小数点后的位数)

ROUND(3.5525,3) =3.553，ROUND(3.55,3) =3.55

6. LEN函数

用途：用来计算字符（如身份证号码）的个数，空格也视为字符

语法：LEN(text)

例：LEN(510231*********)=15

LEN(510231************)=18

LEN(“您好”)=2

LEN(“您□好□”)=4

若A1=12345，LEN(A1)=5，LEN(“A1”)=2；（注意“”的用法）

实例：判断身份证号码（A1）长度是否为15位或18位

B1=if(LEN(A1)=15,A1,IF(LEN(A1)=18,A1,“身份证号码错”))

7. MID函数

用途：通过身份证号码（A1=510231*********）求出生日期

语法：MID(text,start_num,num_chars)

MID(待判断字符,开始字符序数,所取字符个数)

例：MID(1234567,3,4)=3456

19&MID(A1,7,2)&MID(A1,9,2)&MID(A1,11,2)=19790831

19&MID(A1,7,6)=19790831

问题：如何变成1979-08-31？

19&MID(A1,7,2)& “-” &MID(A1,9,2) “-” &MID(A1,11,2)=1979-08-31 实例：A1为身份证号码（15位或18位），求出生日期

B1=IF(LEN(A1)=15,19&MID(A1,7,6),IF(LEN(A1)=18,MID(A1,7,8),“身份证号码错”)) 8. DATE函数

用途：用来计算日期或判断年龄，也可用来将MID求出的出生日期转换为标准的日期格式

语法：DATE(year,month,day)

DATE(2005,3,1)=2005-3-1

其他几个日期函数：YEAR、MONTH、DAY、TODAY

实例1：已知A1为身份证号码，B1=19790831为通过MID函数求得其出生日期，请将其转换为日期格式。

C1=DATE(MID(B1,1,4),MID(B1,5,2),MID(B1,7,2))

实例2：已知小李出生日期为A1(1982-4-14)，用DATE函数表示其今年的生日，判断小李今年是否已满18岁?

(1) B1=DATE(year(today()),month(A1),day(A1))

(2) C1=IF((DATE(year(A1)+18,month(A1),day(A1)))>TODAY(),“未满”,“已满”)

9.DATEDIF

用途:用来计算工龄或年龄

语法:

DATEDIF(date1,date2.”Y”) “M”/”0”/”YM”/”MD”

例:A1=2005-03-01,A2=2006-06-

DATEDIF(A1,A2,”Y”)=1

DATEDIF(A1,A2,”M”)=15

DATEDIF(A1,A2,”D”)=471

DATEDIF(A1,A2,”YM”)=3

DATEDIF(A1,A2,”MD”)=15

小结

1. 本部分介绍了“&”、SUM函数、IF函数、VLOOKUP函数、ROUND函数、LEN函数、MID函数、DATE函数等的用法，需多加练习，灵活掌握，并要学会多个函数结合使用。在电子表格中大量使用公式，不仅可以提高工作效率，而且可以保证所处理数据的准确性。其前提是所选择的函数和设置的公式准确无误！

2. 设定公式后，不要手动修改表中的数据！

3. 修改公式一定要从第一个公式修改.

二、常用技巧

1. 冻结单元格

用途：使你的工作表显示更加友好、易读。

注意：所选定的单元格的位置。

2. 排序

注意：(1) 选准排序的区域；

(2) 拟排序的字段（升序、降序，有无标题行）；

(3) 最多可三个条件排序。

3. 筛选（常用自动筛选）

技巧：(1) 可进行快速排序

(2) 可自定义筛选条件

(3) 可筛选空白、非空白单元格

4. 粘贴

技巧：常用的选择性粘贴—公式、格式、数值、转置

5. 在函数公式中锁定单元格（区域）

用法：使用“$”字符

如：A$1，$A1，$A$1，$F1:$K1，$F$1:$K$30

6. 快速显示计算结果

在屏幕的右下角显示

可选择的项目：平均值、计数、计数值（数值计数）、最小值、最大值、求和用法：点击鼠标右健，然后进行选择。

7. 条件格式

高级用法，需重点掌握，熟练掌握！

在给大家的练习题中，使用了许多条件格式，用来判断结果是否正确。

一旦掌握，受益匪浅！

注意：(1) 条件可以是数值，也可以是公式；

(2) 最多可以有三个条件；

(3) 多个条件时，优先顺序根据设定的先后顺序而定。

(4) 多个条件不要相互矛盾。

举例：

(1) 对年龄设定条件格式

条件1：单元格数值，小于，18，设定格式

条件2：单元格数值，大于，50，设定格式

(2) 对身份证号码(A2)设定格式

条件1：公式，=(len(A2)=15)，设定格式

条件2：公式，=(len(A2)=18)，设定格式

(3)A1：姓名1，A2：姓名2，用条件格式验证A2是否等于A1？

方法1：A2单元格条件格式：单元格值，不等于，=A1，设定格式

方法2：A3（非A2）单元格条件格式：公式，=A2=A1，设定格式

(4)A1：出生日期（日期格式），用条件格式判断A1是否小于18或大于60?

对A1设置条件格式：

条件1：单元格值，大于，=date(year(today()-18),month(today()),day(today()))

条件2：单元格值，小于，=date(year(today()-60),month(today()),day(today())) 提示：

如果是用来判断设定条件格式的单元格的数值与某一数值/条件/其他单元格比较情况，设定条件格式选用“单元格数值”；否则，选用公式。

8. F4键

用途：重复上一个动作，可大大提高工作效率！

9. 查找与替换

查找：学会使用查找全部，如重名情况。

替换：对数据进行再处理，如删除姓名中的空格。

10. 隐藏

可隐藏的对象有：行、列、工作表。

用途：在不删除数据的情况下，使工作表界面更加友好。

11. 组和分级显示

用途：使工作表界面友好、易读，层次清晰。

12. 保护

用途：可对单元格、工作表进行保护设置，防止误操作更改数据或公式。

13. 有效性

属电子表格高级用法，使用有效性可有效地避免数据输入错误；

可设置的有效性包括序列、格式、日期、长度等

设置有效性为序列，可以直接输入，也可以从某一区域选取数据。

以下两个例子属高难度用法：

例1：设置身份证号码（A列）有效性公式，限制只能输入15位或18位数据：有效性为“自定义”

公式：=(LEN(A1)=15)+(LEN(A1)=18)

例2：设置出生日期有效性，限制只能输入年龄满18岁，未满60岁的值：有效性，日期，介于

开始日期：=DATE(YEAR(TODAY())-60,MONTH(TODAY()),DAY(TODAY())+1)

结束日期：=DATE(YEAR(TODAY())-18,MONTH(TODAY()),DAY(TODAY()))

三、数据透视表

特点：强大的统计功能

注意事项：不能有合并单元格

制作方法