有句话说: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″)

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

打赏 赞(3)
微信
支付宝
微信二维码图片

微信扫描二维码打赏

支付宝二维码图片

支付宝扫描二维码打赏