掌握这15个Excel函数技巧,你就是excel高手

发布时间:2021-08-24 Excel技巧

  Excel的灵魂在于数据的分析与统计,而分析与统计就离不开函数或公式,今天要给大家分享的15个函数公式,是工作中常用的,可以直接套用。

  一、从身份证号码中提取出生年月。

  函数1:Text。

  作用:将指定的值用指定的代码转换为文本。

  语法结构:=Text(值,格式代码)。

  函数2:Mid。

  作用:返回文本字符串中从指定位置开始,长度为指定值的字符串。

  语法结构:=Mid(字符串,开始位置,字符长度)。

  目的:从身份证号码中提取出生年月。

  

 

  方法:在目标单元格中输入公式:=TEXT(MID(C3,7,8),00-00-00)。

  解读:首先利用Mid函数提取“出生年月”的8位数字,然后用Text将其设置为“00-00-00”格式。

  二、从身份证号码中提取性别。

  函数1:If

  作用:根据指定条件返回相应的值。

  语法结构:=If(判断条件,条件为真时的返回值,条件为假时的返回值)。

  函数2:Mod

  作用:返回两数相除的余数。

  语法结构:=Mod(被除数,除数)。

  目的:根据身份证号码提取性别。

  

 

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

  解读:身份证号码的第17位代表性别,如果为奇数,则为男性,如果为偶数,则为女性,所以公式中,首先用Mid函数提取第17位,并作为Mod函数的被除数,除数为2,计算余数,然后用IF函数判断余数,如果余数为1(即奇数),则返回“男”,如果余数为0(即偶数),则返回“女”。

  三、根据身份证号码计算年龄。

  函数:datedif

  作用:以指定的方式统计两个日期之间的差值。

  语法结构:=Datedif(开始日期,结束日期,统计方式)。常见的统计方式有“Y”、“M”、“D”,即“年”、“月”、“日”。

  目的:根据身份证号码计算年龄。

  

 

  方法:在目标单元格中输入公式:=DATEDIF(TEXT(MID(C3,7,8),00-00-00),TODAY(),y)。

  解读:1、Datedif函数是系统隐藏函数,作用就是计算两个日期之间的差值。

  2、Today()函数的作用为获取当天的日期。

  3、上述公式中的年龄是随着日期的变化自动更新的,原因在于Today()是变化的,具有很强的实用性。

  四、行列快速求和。

  目的:按“销售员”、“季度”汇总数据。

  

 

  方法:选定目标单元格,包括求和区域,快捷键Alt+=。

  解读:Alt+=其实就是Sum函数的快捷键。

  五、合并单元格求和。

  目的:按“部门”计算销售额。

  

 

  方法:在目标单元格中输入公式:=SUM(I4:I11)-SUM(J5:J11)。

  解读:1、合并单元格的值存储在当前合并区域的左上角单元格中。

  2、当前区域的和值减去除合并单元格的值之外的和值,则为当前合并单元格区域的和值。

  六、合并单元格计数。

  函数:Counta。

  功能:统计指定区域中非空单元格的个数。

  语法结构:=Counta(值或单元格引用)。

  目的:统计“部门”人数。

  

 

  方法:在目标单元格中输入公式:=COUNTA(I4:I11)-SUM(J5:J11)。

  解读:公式中首先用Counta统计出I4:I11区域非空单元格的个数,即7个,然后减去除当前合并单元格之外的人数,则为当前部门的人数。

  七、合并单元格填充序号。

  函数:Max。

  作用:计算最大值。

  语法结构:=Max(值或单元格引用)。

  目的:填充不规则合并单元格的序号。

  

 

  方法:在目标单元格中输入公式:=MAX(A$3:A3)+1。

  解读:也可以用Counta函数来填充,公式为:=COUNTA(A$2:A2)。

  八、按照类别填充序号。

  函数:countif

  功能:计算指定区域中满足条件的单元格个数。

  语法结构:=Countif(条件范围,条件)。

  目的:根据“部门”填充按顺序填充序号。

  

 

  方法:在目标单元格中输入公式:=COUNTIF(D$4:D4,D4)。

  解读:此公式的关键在于理解参数的引用方式,每次计算时都是从D4单元格开始,条件为D列当前行的值。

  九、查找重复值。

  目的:检查“销售员”的姓名是否有重复。

  

 

  方法:在目标单元格中输入公式:=IF(COUNTIF(B$4:B$10,B4)1,重复,)。

  解读:用If函数判断Countif的统计值和1的比较结果,如果成立,则返回“重复”,否则返回空值。

  十、单条件计数。

  目的:按性别统计销售员人数。

  

 

  方法:在目标单元格中输入公式:=COUNTIF(C4:C10,M4)。

  十一、多条件计数。

  函数:Countifs。

  功能:统计一组给定条件下的单元格个数。

  语法结构:=Countifs(条件1范围,条件1,[条件2范围],[条件2]……[条件N范围],[条件N])。

  目的:按“性别”统计销售总额在指定范围内的人数。

  

 

  方法:在目标单元格中输入公式:=COUNTIFS(C4:C10,M4,I4:I10,N4)。

  解读:当多个条件同时符合时,此条数据才被统计,当然也可以实现单条件计数的功能。

  十二、单条件求和。

  函数:Sumif。

  功能:对满足条件的单元格求和。

  语法结构:=Sumif(条件范围,条件,[求和范围]),当“条件范围”和“求和范围”相同时,可以省略“求和范围”。

  目的:按“性别”统计总销售额。

  

 

  方法:在目标单元格中输入公式:=SUMIF(C4:C10,M4,I4:I10)。

  十三、多条件求和

  函数:Sumifs。

  功能:对一组给定条件指定的单元格求和。

  语法结构:=Sumifs(求和范围,条件1范围,条件1,[条件2范围],[条件2]……[条件N范围],[条件N])。

  目的:按“性别”统计指定范围内的销售总额 。

  

 

  方法:在目标单元格中输入公式:=SUMIFS(I4:I10,C4:C10,M4,I4:I10,N4)。

  解读:当多个条件同时符合时,此条数据才被统计,当然也可以实现单条件求和的功能。

  十四、生成指定范围的随机值。

  函数:Randbetween。

  功能:生成指定范围内的随机值。

  语法结构:=Randbetween(最小值,最大值)。

  目的:生成200-500之间的随机值。

  

 

  方法:在目标单元格中输入公式:=RANDBETWEEN(200,500)。

  解读:如果对生成的随机值不满意,可以按F9刷新,直到满意为止。

  十五、排名。

  函数:Rank。

  功能:对指定范围内的值按照指定的顺序排序。

  语法结构:=Rank(值,数据范围,[排序方式])。排序方式分为0和1两种,即降序和升序,省略时默认为降序。

  目的:对总“销售额”按降序排序。

  

 

  方法:在目标单元格中输入公式:=RANK(I4,I$4:I$10)。

  解读:省略第三个参数时默认为降序排序。

 

  结束语:本文从实际出发,对工作中常用的函数公式进行了解读,如果能够熟练掌握,你也可以是别人眼中的高手哦!