Excel是财务人打交道最多的办公软件,熟练掌握Excel中的函数公式和操作技巧,能有效的提高工作效率,小空今天给大家分享一些常用公式。

IF函数条件判断

IF函数是常用的判断类函数了,能完成非黑即白这类的判断。

举个栗子,考核得分的标准为60分,要判断B列的考核成绩是否合格。

=IF(B4>=9,"合格","不合格")

excel常用的公式函数(Excel常用函数公式大全)(1)

IF,相当于普通话的“如果”的意思。

IF(IF(条件表达式,true,false),表达式为真时执行,表达式为假时执行).

VLOOKUP条件查找

VLOOKUP函数是Excel中的一个纵向查找函数:VLOOKUP(查找值,数据表,列序数【匹配条件】)

excel常用的公式函数(Excel常用函数公式大全)(2)

使用该函数时,需要注意以下几点:

1、第4参数一般用0(或FASLE)以精确匹配方式进行查找。

2、第3参数中的列号,不能理解为工作表中实际的列号,而是指定返回值在查找范围中的第几列。

3、如果查找值与数据区域关键字的数据类型不一致,会返回错误值#N/A。

4、查找值必须位于查询区域中的第一列。

如下图,要查询F4单元格中的员工姓名是什么职务。

excel常用的公式函数(Excel常用函数公式大全)(3)

根据身份证号码提取出生年月

C4单元格输入公式:=TEXT(Mid(B4,7,8),"0-00-00"),

向下复制填充。一次性提取所有身份证号码对应的出生日期。

excel常用的公式函数(Excel常用函数公式大全)(4)

首先用MID函数从B4单元格的第7位开始,提取出表示出生年月的8个字符,结果为:"19950904"

再使用TEXT函数将字符串转换为日期样式:"1995-09-04"

条件求和

SUMIF用法是:=SUMIF(区域,求和条件,求和的区域)

小空用通俗语解释一下:如果C4:C8区域的分数等于E4单元格的“一班”,就对B4:B8单元格对应的区域求和。

excel常用的公式函数(Excel常用函数公式大全)(5)

这里知道了条件,那可不可以多种条件求和呢!!!

当然是可以的,就是在公式加多一些条件

SUMIFS用法是:=SUMIFS(求和的区域,条件区域1,指定的求和条件1,条件区域2,指定的求和条件2,……)

从身份证号中提取性别

小空在上面提了怎么用公式提取出生年月日,那提取性别小伙伴们知道怎么设置公式吗?

在目标单元格中输入公式:=IF(MOD(MID(B4,17,1),2),"女","男")。

excel常用的公式函数(Excel常用函数公式大全)(6)

1、身份证号码中的第17位代表性别,如果为奇数,则为“男”,如果为偶数,则为“女”。

2、利用Mid函数提取第17位的值,利用Mod函数求余,最后用IF函数判断,如果求余结果为1(暨奇数),则返回“男”,如果其余结果为0(暨偶数),则返回“女”。

这样则可以得出结果了。

提取混合内容中的姓名

比如我们要用一列数据提取出姓名,除了使用高版本的自动填充功能,小空发现还可以使用公式完成:=LEFT(A2,LENB(A2)-LEN(A2))

excel常用的公式函数(Excel常用函数公式大全)(7)

LENB函数将每个汉字的字符数按2计数的,而LEN函数对所有的字符都按1计数。

所以“LENB(A2)-LEN(A2)”结果就是文本字符串中的汉字个数。LEFT函数从文本的第一个字符开始,返回指定个数的字符,到最终提取出员工姓名哦!

柒/合并多个单元格内容名

连接合并多个单元格中的内容,可以使用&符号完成。如图,要把合并A列和B列的数据合并在一起就可以使用公式:=A2&B2

excel常用的公式函数(Excel常用函数公式大全)(8)

捌/取整的间隔小时数

像计算员工加班时长时,经常会算加班小时不方便计算。像两个时间的间隔小时数,不足一小时部分舍去,这种在我们计算加班的时长时会用到 =TEXT(B2-B1,”[h]”)

excel常用的公式函数(Excel常用函数公式大全)(9)

1:如果是只有早上的上班时间和晚上下班打卡时间,我们计算加班时间,以1天8小时工作时间为例,这里采用满一小时才算加班=TEXT(C2-C1,"[h]")-8

excel常用的公式函数(Excel常用函数公式大全)(10)

2:如果是只有早上的上班时间和晚上下班打卡时间,我们计算加班时间,以1天8小时工作时间为例,这里采用满半小时才算加班=INT((HOUR(D2-D1)*60 MINUTE(D2-D1))/30)/2-8

excel常用的公式函数(Excel常用函数公式大全)(11)

玖/设置动态下拉菜单

小空发现有时候有些数据应用很多,一个个填又很麻烦,这时候我们就可以设置一个动态下拉菜单,可以选择数据。如下图所示,要根据A列的数据在C列生成下拉菜单,要求能随着A列数据的增减,下拉菜单中的内容也会自动调整选中要输入内容的单元格区域,数据→下拉菜单→序列

excel常用的公式函数(Excel常用函数公式大全)(12)

这样就可以直接选择数据了,不需要一个个地重复输入

excel常用的公式函数(Excel常用函数公式大全)(13)

拾/设置透视表

在工作在经常会应用过透视表,有些小伙伴就不知道怎么设置,以下如图示

excel常用的公式函数(Excel常用函数公式大全)(14)

透视表的应用很多,还有一些功能选择,大家有空可以探索一下,对于数据的整合都有用。

excel常用的公式函数(Excel常用函数公式大全)(15)

最后小空给大家整理了一份EX快捷键应用大全,可以保存起来哦~

excel常用的公式函数(Excel常用函数公式大全)(16)

,