如此实用的7类Excel函数公式都不掌握,那就真的Out了

2019-02-19  豪门巨室

函数和公式,是Excel中的灵魂,也是非常实用的技巧,如果你对常用的函数公式能够熟练地掌握,对于提高工作效率将会有很大的帮助!


一、“重复”类。

1、查找重复内容,重复即提示。

如此实用的7类Excel函数公式都不掌握,那就真的Out了

方法:

在目标单元格中输入公式:=IF(COUNTIF(E:E,E3)>1,'重复','')。

解读:

先用Countif函数统计当前单元格的值在E列中的数量,然后用If函数进行判断,如果大于1,返回“重复”,否则返回空值。


2、重复内容首次出现时不予提示。

如此实用的7类Excel函数公式都不掌握,那就真的Out了

方法:

在目标单元格中输入公式:=IF(COUNTIF(E$3:E3,E3)>1,'重复','')。

解读:

将本示例中的公式和上一示例中的公式进行对比,结果只是参数做了修改,将原来的范围E:E变为E$3:E3,而统计结果却不相同,其实范围E:E是绝对引用,暨统计范围为E列的所有单元格,而为了达到“重复内容”首次出现时不予提示的目的,我们将统计范围定位从开始单元格到当前单元格,所以,重复内容首次出现时不予提示。


3、重复内容首次出现时提示重复。

如此实用的7类Excel函数公式都不掌握,那就真的Out了

方法:

在目标单元格中输入公式:=IF(COUNTIF(E3:E99,E3)>1,'重复','')。

解读:

1、从公示的计算过程请参阅示例2的解读。

2、此公式在应用时要区分对待,如果重复内容进出线一次,则重复内容仅在首次出现时提示,如果有多次重复,仅最后一次不提示重复。


二、身份证号类。

1、根据出生年月计算年龄。

如此实用的7类Excel函数公式都不掌握,那就真的Out了

方法:

在目标单元格中输入公式:=DATEDIF(E3,TODAY(),'y')。

解读:

Datedif函数为系统隐藏函数,其主要作用为按指定的统计方式统计两个日期之间相差的值。其中“y”的意思为按年统计。


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

如此实用的7类Excel函数公式都不掌握,那就真的Out了

方法:

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

解读:

1、首先利用Mid函数提取D3字符串中从第7位开始,长度为8的字符。

2、利用Text函数将其转换为“00-00-00”的格式。


3、根据身份证号码判断性别。

如此实用的7类Excel函数公式都不掌握,那就真的Out了

方法:

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

解读:

1、首先用Mid函数提取D3字符串中从17位开始,长度为1,暨第17位的数字。

2、利用Mod函数求余。

3、利用If函数判断,余数为1,暨第17位的数为奇数,则返回“男”、如果余数为0,暨第17位的数为偶数,则返回“女”。


三、常用汇总函数。

如此实用的7类Excel函数公式都不掌握,那就真的Out了

方法:

在对应的目标单元格中输入公式:=SUM(D:D)、=MAX(D:D)、=MIN(D:D)、=AVERAGE(D:D)、=COUNTA(B3:B9)。


四、成绩排名。

1、美式排名。

如此实用的7类Excel函数公式都不掌握,那就真的Out了

方法:

在目标单元格中输入公式:=RANK.EQ(D3,D:D)。

解读:

从排序的结果来看,出现了“跳跃”的情况,如果你对这一结果不满意,请继续往下阅读。


2、中国式排名。

如此实用的7类Excel函数公式都不掌握,那就真的Out了

方法:

在目标单元格中输入公式:=SUMPRODUCT(($D$3:$D$9>D3)/COUNTIF($D$3:$D$9,$D$3:$D$9)) 1。

解读:

从排序的结果中我们可以看出,名次没有“跳跃”的情况,是不是更符合我们的实际需要了?


五、个数类统计。

1、单条件计数。

如此实用的7类Excel函数公式都不掌握,那就真的Out了

方法:

在目标单元格中输入公式:=COUNTIF(D3:D9,'>='&G3)。

解读:

Countif函数为单条件计数函数,其语法结构为=Countif(统计范围,统计条件)。


2、连续范围计数。

如此实用的7类Excel函数公式都不掌握,那就真的Out了

方法:

1、在目标单元格中输入公式:=FREQUENCY(D3:D9,G3:G6)。

2、Ctrl Shift Enter填充。

解读:

1、函数Frequency的作用为:以一列垂直数组返回一组数据的频率分布。公式中D3:D9为数据,G3:G6为垂直数组。

2、因为要循环多次计算,所以填充时用Ctrl Shift Enter,而不是常规的Ctrl Enter哦!

3、结果解读:30分以下的个数为0;31-40的个数为1;41-50的个数为:0;51-60的个数为:3;61-70的个数为:3人。


3、单条件统计平均值。

如此实用的7类Excel函数公式都不掌握,那就真的Out了

方法:

在目标单元格中输入公式:=AVERAGEIF(C3:C9,G3,D3:G9)。

解读:

函数Averageif为按条件计算平均值函数,语法结构为:=Averageif(条件范围,条件,数值范围)。


4、多条件统计平均值。

如此实用的7类Excel函数公式都不掌握,那就真的Out了

方法:

在目标单元格中输入公式:=AVERAGEIFS(D3:D9,C3:C9,G3,D3:D9,'>='&H3)。

解读:

Averageif是函数为多条件计算平均值,其语法结构为:=Averageifs(数值范围,条件1范围,条件1,条件2范围,条件2……条件N范围,条件N)。


六、统计不重复值。

如此实用的7类Excel函数公式都不掌握,那就真的Out了

方法:

在目标单元格中输入公式:=SUMPRODUCT(1/COUNTIF(E3:E9,E3:E9))。


七、提取不重复的内容。

如此实用的7类Excel函数公式都不掌握,那就真的Out了

方法:

1、在目标单元格中输入公式:=INDEX(E:E,2 MATCH(,COUNTIF(F$2:F2,E$3:E$9),))&''。

2、用Ctrl Shift Enter填充。

3、拖动填充柄填充其他单元格。


结束语:

本文针对不同的情况,对常用的函数公式进行了罗列,对简单的用法也进行了讲解和示范,如果能够熟练掌握,对于工作效率的提高绝对不是一点点哦!

学习过程中如果有不同的见解,欢迎大家在留言区留言讨论哦!


    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。如发现有害或侵权内容,请点击这里 或 拨打24小时举报电话:4000070609 与我们联系。

    猜你喜欢

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多
    喜欢该文的人也喜欢 更多