这15组Excel常用函数,解决我们工作中99%的难题

Skill成长课堂2020-10-16 08:11:52


文|罂粟姐姐

源|欢乐PPT(ID:huanleppt)

  

有句话说:Excel除了不能生孩子,其他的事情都能干。


——院长大大



工作六年,亲密接触Excel六年,遇到了各种Excel难题,也帮人解决了很多Excel难题,也走过很多弯路,在不断的学习、总结和分享过程中,发现大多数人的Excel问题还是基础薄弱,基本功不扎实,因此总结了15个职场常用的Excel函数,希望大家能够融会贯通、学以致用。


- 01 -

日期与时间函数


1.TODAY、NOW、YEAR、MONTH、DAY、WEEKDAY函数


这是一组非常简单但是又最为常用的日期函数,通常与其他嵌套使用,实现更多样化的结果。



案例:

一般情况下,男60岁退休,女55岁退休(政策有变,先不予考虑)。我们可以根据员工的出生日期和性别来确定退休日期。


公式=DATE(YEAR(C12)+(B12="男")*5+55,MONTH(C12),DAY(C12))



2.DATEIF函数


DATEDIF函数是Excel的一个隐藏函数,不在插入函数的函数库里,在Excel帮助文件里也没有它的身影,是非常重要的日期函数。


案例:

现有两个日期,通过DATEIF函数计算两个日期之间的天数、月数和年数,计算结果及结果说明如下。



- 02 -

统计分析函数


3.COUNT/COUNTA/COUNTBLANK函数

COUNT函数是最常见的统计函数,主要用于统计单元格区域或数组中数值的个数,文本、空白单元格、逻辑值或错误值无法用COUNT函数来统计。


COUNTA函数可以返回非空文本的单元格个数,COUNTBLANK函数可以计算指定单元格区域中空白单元格的个数。



案例:

现有4组数据,需要将每组数据的最后一个数字提取出来。


公式=INDEX(B19:F19,COUNTA(B19:F19))



4.LARGE和SMALL函数

LARGE和SMALL函数代表第几大的数和第几小的数,常与其他函数组合使用。需要注意的是这两个函数对于数据表中相同的数会顺序排位返回相应的值。



案例:

现在有班级成绩,要求80分以上的升序排列,80分以下的不显示。


公式=IFERROR(SMALL(IF($B$17:$B$28>80,$B$17:$B$28,""),ROW(A1)),"")



5.MAX和MIN函数

LARGE和SMALL函数是第几大,第几小,而MAX和MIN函数是极值。

案例:

有一个100万的项目招标,报价分为100分,评分条件如下:总价等于价格平均值的,得基本分80分;总价低于价格平均值的,每低2%,加2分,最多100分;不含税总价高于评标基准值的,每高2%,扣4分,最低60分。


最高100分,最低60分公式=MAX(MIN(D2,100),60)



6.COUNTIF/SUMIF/AVERAGEIF函数


COUNTIF/SUMIF/AVERAGEIF函数是对指定区域中符合指定条件的单元格计数/求和/求平均值。



案例:

现有一公司销售数据,需计算销售员“杨过”在2016年的总体表现。



7.COUNTIFS/SUMIFS/AVERAGEIFS函数

COUNTIFS/SUMIFS/AVERAGEIFS函数主要为计算多个区域中满足给定条件的单元格的个数/和/平均值。



案例:

现有一公司销售数据,需计算销售员“杨过”在2016年第四季度的总体表现。


- 03 -

查找引用函数


查找是数据分析处理的一项重要工作,而正确地引用数据则是准确查找的关键。Excel提供有丰富的函数满足我们对数据查找与引用的需求。


8.VLOOKUP函数

VLOOKUP函数在Excel函数江湖中的地位无人能及,有很多人就用VLOOKUP函数+数据透视表横扫职场。



案例:

现在一组学生成绩,需提取部分学生的成绩。


函数公式解读:


9.INDEX+MATCH函数

在Excel中,MATCH函数可以用于查找数据的位置,而INDEX函数则可以根据查找到的位置返回实际的单元格引用,因此,在实际工作中这两个函数完美组合可以解决很多问题。



案例:

现在一组学生成绩,需根据选定姓名和学科查找成绩。


公式

=INDEX($A$1:$D$15,MATCH($F2,$A$1:$A$15,0),MATCH($G2,$A$1:$D$1,0))



- 04 -

逻辑信息函数


10.AND/OR函数

逻辑判断是指一个有具体意义并能判断真假的陈述语句,常用的逻辑关系有三种,即“与”、“或”、“非”,在Excel中分别用AND函数、OR函数、NOT函数表示。我们工作中最常用的就是AND函数和OR函数。



案例:

公司计划组织员工免费参加培训,条件是25岁以下的女性或者40岁以下的男性,现有公司人员信息,需判断哪些符合,哪些不符合?

公式=IF(OR(AND(B3="女",C3<=25),AND(B3="男",C3<=40)),"符合","不符合")



11.ISNUMBER/ISERROR/IFERROR函数

ISNUMBER(value),如果VALUE为数字,将返回TRUE,否则将返回FALSE。

ISERROR(expression),其中expression可以是任何有效表达式,用来判断公式计算是否出错,如果出错,则返回TURE,如果正确,则返回FALSE。通常与IF配套使用。

IFERROR(value, value_if_error),检查参数是否为错误值,如果是错误值,则返回第二参数,不过不是错误值,则保留第一个参数结果。


注意:07版及以上,出现了IFERROR,可以替代ISERROR的使用



案例:

现有一公司入职年限及岗位工资表,需要根据入职年限匹配岗位工资。

公式=IFERROR(VLOOKUP(D12,$A$10:$B$22,2,0),"查询错误")


- 05 -

文本处理函数


12.FIND/SEARCH函数

FIND和SEARCH都是查找函数,两者的相同点是查找特定字符在字符串中起始位置编号的函数,不同点是对于英文字母大小写的区分以及是否支持使用通配符查找。



案例:

现有一组公司名称数据,现在需要判断公司名称中是否包含“科技”两个字。


公式=IF(ISNUMBER(FIND($B$12,A13)),"包含","不包含")



13. MID/LEFT/RIGHT函数

MID/LEFT/RIGHT三个字符串截取函数通常与其他函数组合使用,用处非常广泛。


案例:

现有一组身份证号码,需根据身份证号码提取“出生日期”属性。

公式=--TEXT((LEN(A2)=15)*19&MID(A2,7,6+(LEN(A2)=18)*2),"0-00-00")


14.SUBSTITUTE函数

这是一个强大但是被低估了甚至很少有人使用的函数。


案例:

现有一组数据,需提取每个单元格最后一个字符。

公式=MID(A2,FIND("Z",SUBSTITUTE(A2,"→","Z",LEN(A2)-LEN(SUBSTITUTE(A2,"→",""))))+1,1)


- 06 -

数学计算函数


15.INT和MOD函数

MOD函数用于计算两数相除的余数,INT函数用于舍去数值的小数部分保留整数部分。是非常简单的两个函数,但是在与其他函数嵌套的过程中发挥着非常重要的作用。


案例1:制作黑白相间的棋盘

在“条件格式”-“新建规则”-“使用公式确定要设置格式的单元格”-“为符合此公式的值设置格式”对话框中输入公式:

=or(and(mod(row(),2)=1,mod(column(),2)=1),and(mod(row(),2)=0,mod(column(),2)=0))



案例2:

现有一组身份证号码,需根据身份证号码提取“性别”属性。

公式=IF(MOD(MID(A2,15,3),2)=1,"男","女")




掌握以上15条函数,可以解决工作中99%Excel问题。关于Excel函数使用,欢迎留言探讨。



- 作者 -


罂粟姐姐,公众号“欢乐PPT”专栏作者,全网Excel系列文章阅读量千万,新书《人人都能学会的Excel数据分析》正在全网热卖,欢迎订购!公众号:欢乐PPT(ID:huanleppt)。


 猜你喜欢 


这10条赚钱心得,你绝不能错过

Ctrl+E有多牛,你真的知道吗?


--提升实力,拒绝平庸分界线--


以前觉得自己Excel玩得很好,但慢慢你会发现,其实Excel里面还有很多你没有用过的功能和技巧。


当掌握这些功能以后,你又会有一个很大的提升。

学习Excel是一个很漫长的过程,而且,只是运用现有的知识来完成手上的工作,提升速度会非常慢。


有什么办法可以快速地提高你的Excel水平?当然就是通过系统的学习啊!


为此,院长在唯库开设了一门“高效有趣学Excel,轻松入门到高阶”的课程。


如果你想快速、有效地提高你的Excel水平,那就快来报名吧!

点赞一次=小编工资涨5毛↓↓