最实用的10类函数公式你都不会,还怎么成为Excel达人?

在Excel中,相对于高大上的一些技巧,更为实用的其实还是一些基础函数公式,例如:文本截取类函数Left、Mid、Right;数字类:Randbetween、Round等。

一、文本截取。

方法:

在目标单元格中输入公式:=LEFT(B3,4)、=MID(B3,6,3)、=RIGHT(B3,3)。

解读:

1、Left函数:从一个指定字符串中从工作开始返回指定个数的字符。语法结构:=Left(字符串,长度)。

2、Mid函数:从一个指定字符串的指定位置开始,返回指定长度的字符。语法结构:=Mid(字符串,开始位置,字符长度)。

3、Right函数:从一个字符串中的右侧开始,返回指定长度的字符。语法结构:=Right(字符串,长度)。其实Left和Right函数的作用类似,Left函数从左开始,Right从右开始而已。

二、生成随机数。

方法:

1、在目标单元格中输入公式:=RAND()或=RANDBETWEEN(10,90)。

2、如果要更重新生成随机数,按F9即可。

解读:

1、函数Rand生成的随机数范围为0-1。

2、Ran的between生成的随机数为指定范围之内的,其语法为:=Randbetween(最小值,最大值),包括最小值和最大值。

三、四舍五入并保留指定的位数。

方法:

在目标单元格中输入公式:=ROUND(F3,2)。

解读:

1、从图中我们可以看出对F3:F9区域的随机数进行了四舍五入。用到的函数为Round,其语法结构为:=Round(数字或单元格引用,保留小数的位数)。

2、利用F9重新生成随机数时,四舍五入的值也随着发生变化。

四、隐藏公式错误值。

方法:

在目标单元格中输入公式:=IFERROR(G3/F3,"")。

解读:

1、直接计算完成率的时候,由于部分没有“计划指标”,出现错误。用Iferror函数处理后,成功的隐藏了错误值。

2、Iferror函数的作用为:如果表达式或公式有错误,返回指定的值,如果没有错误,返回表达式或公式的值。其语法结构为:=Iferror(表达式或公式,表达式或公式有错误时返回的值)。

五、条件判断。

目的:通过“完成率”判断工作任务是否合格,如果大于等于0.5,则为“合格”,否则为“不合格”。

方法:

在目标单元格中输入公式:=IF(H3>=0.5,"合格","不合格")。

解读:

If函数为我们最常的函数,其语法为:=If(判断条件,条件成立时的返回值公式,条件不成立时的返回值或公式)。

六、字母大小写转换。

方法:

在目标单元格中输入公式:=PROPER(B3)、=UPPER(B3)或=LOWER(D3)。

解读:

1、公式:=PROPER(B3)的作用是将给定的字母首字母大写。

2、公式:=UPPER(B3)的作用是将给定的字母全部大写。

3、公式:=LOWER(D3)的作用是将给定的字母全部小写。

七、统计排名。

方法:

在目标单元格中输入公式:=RANK(C3,C$3:C$9,0)或=SUMPRODUCT(($C$3:$C$9>C3)/COUNTIF($C$3:$C$9,$C$3:$C$9))+1。

解读:

1、公式:=RANK(C3,C$3:C$9,0)对应的排名结果一般情况下我们称之为“美式排名”,例如两个98分为第2名,而下一个97分直接成了第4名,“跳跃式”增长,并不符合我们的习惯。其函数的语法结构为:=Rank(参与排序的值,值所在的范围,0或1)。其中0代表降序,1代表升序。

2、公式:=SUMPRODUCT(($C$3:$C$9>C3)/COUNTIF($C$3:$C$9,$C$3:$C$9))+1对应的排名结果一般情况下我们称之为“中国式排名”,例如两个98分为第2名,而下一个97分则为第3名,没有“跳跃”的情况,更符合我们的实际需求和习惯。

八、计数统计。

方法:

在目标单元格中输入公式:=COUNTA(C3:C12)、=COUNT(E3:E12)、=COUNTBLANK(E3:E9)、=COUNTIF(E3:E12,">=90")、=COUNTIFS(B3:B12,"1-1",D3:D12,"男",E3:E12,">=90")。

解读:

1、应考人数:暨为班级所有人员数。而Counta函数的功能为计算区域中非空单元格的个数,语法结构为:=Counta(统计数据的区域)。

2、实考人数:暨为有分值的人的个数,及数值的个数。而Count函数的功能为计算区域中数字单元格的个数,语法结构为=Count(统计数据的区域)。

3、缺考人数:暨为成绩为空值或标注为“缺考”的人数。而Countblank函数的功能功能为计算区域中空单元格的个数,语法结构为:=Countblank(统计数据的区域)。如果缺考人员被标记为“缺考”,则用到Countif函数。

4、成绩大于等于90分的人数:单条件计数,所以用到Countif函数,语法结构为:=Countif(条件范围,条件)。

5、1-1班男生成绩大于等于90分的人数:多条件计数,所以用到Countifs函数,语法结构为:=Countifs(条件1范围,条件1,条件2范围,条件2……条件N范围,条件N),其含义就是同时满足所有条件的个数。

九、多表求和。

目的:计算工作表1到工作表4中的销量之和。

方法:

在目标单元格中输入公式:=SUM('1:4'!D3)。

解读:

1、单引号为特别引用的意思,数字1和4为工作表的名称,可以自定义。其中1未开始工作表的名称,4位最后一个工作表的名称,'1:4'的意思就是工作表1开始至工作表4结尾中间所有的工作表。

2、感叹号(!)可以理解为特别引用的意思,!D3意思就是特指D3单元格。

3、公式:=SUM('1:4'!D3)就可以理解为求从表1开始到表4结束中间所有表的D3单元格的和。

十、重复数据。

目的:根据“销量”生成微型迷你图。

方法:在目标单元格中输入公式:=REPT("|",D3)。

解读:

1、Rept函数的功能为:根据指定次数重复文本。语法结构为:=Rept(文本字符串,重复次数)。

结束语:

本文主要讲解了常用函数的实用技巧,相对于高大上的函数公式,基础函数的实用性和使用频率更好,所以,作为职场的我们必须掌握哦!学习过程中如果有任何问题,欢迎大家在留言区留言讨论哦!

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20181110A000FE00?refer=cp_1026
  • 腾讯「云+社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 yunjia_community@tencent.com 删除。

扫码关注云+社区

领取腾讯云代金券