拒绝加班！工作中必会的15个excel函数

有人会说，现在网上excel技巧太多，一眼看过去感觉各个都好牛逼，恨不得全部收藏起来。可是，能真正能用到的时候并不多，因为学习的知识都太散了，也不能及时进行总结整理。

前面我介绍了有关于数据整理中的一些小技巧，本次将为大家介绍的是excel函数与公式的应用。

直接上香喷喷的干货啦！！！

一、计算函数

函数1：SUM、SUMIF函数

在工作表中对多个数据进行求和的时候用SUM函数；需要按指定条件对指定单元格求和，使用SUMIF函数。

表达式：

SUM（需要求和的参数）

SUMIF（条件判断的单元格区域，条件，求和的实际单元格）

实例1:

计算2017年上半年所有产品的销售额和2017年上半年联想的销售额：

1.在C14单元格中输入计算公式“=SUM(E3：E13)”，计算上半年所有产品销售额，如图1：

2.在C15单元格中输入计算公式“=SUMIF(B3:B13,"联想",E3:E13)”，计算上半年联想的销售额，如图2：

函数2：AND、OR函数

AND函数常用在需要判断工作表汇总中的数据都为真的时候；只需要判断多个参数值中是否有一个的参数值为真时，用OR函数。

表达式：

AND(所有待检验的表达式，用逗号隔开)——结论显示TRUE或FALSE；

OR(所有待验证的表达式，用逗号隔开)——结论显示TRUE或FALSE。

实例2：

在学生成绩表中，需要统计是否有同学所有课程分数均超过60分，和是否有同学有课程分数超过90分。

1.统计是否有同学所有课程分数均超过60分，输入公式"=AND(B2>60,C2>60,D2>60)"，按ENTER键，返回TRUE代表所有课程都超过60分，FALSE则代表有课程分数低于60分,如图3；

2.统计是否有同学的课程分数超过90分，输入公式"=OR(B3>90,C3>90,D3>90)"，按ENTER键，返回TRUE代表有课程超过90分，FALSE则代表没有课程分数超过90分，如图4；

函数3：ABS函数

ABS函数可以返回数值的绝对值。

表达式：

ABS（数值）

实例3：

将员工上月与本月销售额进行比较，得出结论。

方法：选中单元格D2，输入公式为"=IF(C2>B2，"提高"，“下降”)&ABS(C2-B2)&"元""，按enter键得到结果，如图5：

函数4：INT函数

INT函数是将数字向下舍入到最接近的整数：

表达式：

INT(需要进行向下舍入的整数)

实例4：

需要计算销售员3个月的平均销售量。

方法：选中A15，输入公式“=INT(SUM(E2：E13)/12)”，按enter键得出结果，如图6：

函数5：RAND函数

RAND函数用于返回大于等于0并小于1的随机分布实数。

表达式：

RAND()——无参数

实例5：

要随机生成彩票的7位开奖号码，可以使用INT和RAND函数来实现。

方法：在C2中输入公式"=INT(RAND()*10)"，按enter键，如图7：

函数6：AVERAGE函数

用于计算所有参数的算数平均值。

表达式：

AVERAGE(需要求平均值的数字)

实例6：

忽略0值求出平均销售额

方法：选中B14，输入公式“=AVERAGE(IF(B2:B130，B2:B13))”,按“shift+ctrl+enter”键即可忽略0值，如图8：

二、文本函数

函数7：&函数

&函数是连接字符串。

表达式：

字符串&字符串。

实例7：

人事表格中将分开的省市等信息连接起来。

方法：在D2中输入公式"=A2&82&C2"，按enter，得到结果,如图9：

函数8：UPPER、LOWER、PROPER函数

UPPER函数是将小写字母转换为大写字母；

LOWER函数则相反，是将大写转换为小写字母；

PROPER函数会将开头字母转换为大写，其他的转换为小写；

表达式：

UPPER（要转换的字符）

LOWER（要转换的字符）

PROPER（要转换的字符）

实例8：

将以下原字符串，按照不同的要求进行转换：

1.小写——大写，如图10：

2.大写——小写，如图11：

3.首字母大写，如图12：

函数9：TEXT函数

TEXT函数可以将被转换数值转换成指定的格式。

表达式：

TEXT(需转换的数值，指定的格式)

实例9：

根据员工的出生日期可以判定当天是星期几。

方法：选定 C2，输入公式为"=TEXT(B2，"aaaa")",按enter键可以看到结果,如图13：

函数10：LEFT、RIGHT、MID函数

LEFT函数是用来从某个字符串中截取左侧的一个或几个字符；

RIGFT函数则是用来从某个字符串中截取右侧的一个或几个字符；

MID函数是从某个字符串中截取中间的一个或几个字符。

表达式：

LEFT(要提取的字符串，提取的个数)

RIGFT(要提取的字符串，提取的个数)

MID(要提取的字符串，要提取的第一个字符的位置，提取的个数)

实例10：

需要从员工的身份证号码中提取出员工的出生地区、员工的性别、员工的出生日期：

1.出生日期：

（1）身份证号码前6位是出生地的信息，需要提取出前6位，再对照地区的编码进行转换；

（2）在C2中输入公式"=LETF(B2，6)",提取出编码,如图14：

（3）使用公式VLOOKUP将编码转换为地区，公式为“=VLOOKUP(C2：L:M,2，0)”,结果如图15:

2.员工的性别：

(1)18位身份证号码中倒数第二位是用来判断性别，奇数为男，偶数为女；

（2）在F2中输入公式"=IF(MOD(RIGHT(LEFT(B2，17),1),2)=0，"女","男")",如图16：

（先从身份证编码中提取出前17位，然后提取前17位的最右侧一位）

3.员工的出生日期：

(1)身份证号码的第7到15位对应的编码是出生日期；

(2)在F2中输入公式“=MID(B2，7，8)”，提取出的是文本类型的，没有办法直接转换成为日期格式,如图17：

(3)换一种方法，输入公式"=DATE(MID(B2，7，4),MID(B2，11，2),MID(B2，13，2))",按enter键，变换成数字格式，然后利用“设置单元格格式"转换为日期格式，显示最终结果如图18：

三、时间和日期函数

函数11：NOW、TODAY函数

NOW函数和TODAY函数都是用来返回当前日期的函数，不同点是TODAY函数只能返回当前的日期，NOW函数则可以返回当前的日期和时间。

表达式：

NOW()——没有参数

TODAY()——没有参数

实例11：

现在要做一个记账本，每次在消费之后需要及时进行更新，时间总是更新很麻烦，可以使用NOW函数和TODAY函数。

1.要记录到具体的时间点，输入公式"=NOW()"，如图19：

2.要记录到具体的日期，输入公式"=TODAY()"，如图20：

函数12：MONTH、YEAR、DAY函数

YEAR函数用来计算某个日期值中的年份，同理，MONTH、DAY函数则分别用来计算某个日期值中的月份和天数。

表达式：

YEAR(需要计算的日期)MONTH(需要计算的日期)DAY(需要计算的日期)

实例12：

使用MONTH、DAY函数来自动提醒员工的生日。

1.在表格中另起两列，一列为本月生日提醒，一列为本日生日提醒；

2.在“本月生日提醒”列中，输入公式"=IF(MONTH(B2)=MONTH(TODAY()),"本月"&DAY(B2)&"日过生日")"，按ENTER键，显示如下结果，如图21；

3.在“本日生日提醒”列中，输入公式"=IF(AND(MONTH(B10)=MONTH(TODAY()),DAY(B10)=DAY(TODAY()),"今天过生日","")"，如图22：

函数13：DATE函数

DATE函数主要用来计算某一特定日期的编号。

表达式：

DATE(year,month,day)

实例12：

人事部门现在需要统计员工合同到期时间，已知入职日期和合同的年限，需要计算出合同到期的时间。

方法：选中D2，输入公式"=DATE(YEAR(B2)+C2,MONTH(B2),DAY(B2))",按enter之后会得到合同到期时间，如图23：

四、IF函数和VLOOKUP函数

函数14：IF函数

IF函数可以根据指定的条件计算结果为TRUE或FALSE，并返回不同的值。

表达式：

IF（指定的表达条件，表达条件为TRUE时函数返回的值，表达条件为FALSE时函数返回的值）

实例14：

销售部在统计员工是否完成了本月的销售任务，已经统计了预定的销售任务和实际完成任务，输入公式如下“=IF(C2>B2，"完成","未完成")”，如图24：

函数15：vlookup函数

之所以把vlookup函数单独提出来，因为vlookup函数可以算得上是工作中最常用也最好用的一个函数了，学好vlookup函数十分重要。

VLOOKUP函数是查找函数，给定一个目标，可从制定的查找区域中查找返回想要查找的数值。

表达式：

VLOOKUP（查找值，查找范围，查找列数，精确或近似匹配）

实例15：

小张要做销售金额汇总表，一张表中包含的是销售数量信息，另一张表中包含的是销售价格，需要数量与单价相乘才可以计算金额，目前品类较多，如果将单价都输入数量表中计算工作量太大了，采用vlookup函数可轻松搞定。

方法：选中“产品售价”C2，输入公式为"=VLOOKUP(A2,J:L,3,0),如图25：

含义就是就是利用产品名称查找，查找的范围是“销售价格表E列到J列”，查找的值在J列(第2列)。

（TIPS：vlookup函数查找的条件是查找值具有唯一性，如果不是唯一值，默认查找到的第一个值，有可能会发生错误。）

获取更多视频资源、和大家一起畅聊职场、学习经验可以加下群哦~