分享

实用性100%的12个Excel技巧,提高效率不是一点点哦!

 hercules028 2019-03-13

一、IF+Countif:查找重复值。

方法:

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

解读:

1、Countif函数的作用是:计算指定的单元格区域中满足条件的单元格数。语法结构是:=Countif(条件范围,条件)。

2、用IF函数判断Countif的统计结果,如果大于1,返回“重复”。


二、Text+Mid:从身份证号码中提取出生年月。

方法1:

在目标单元格中输入公式:=TEXT(MID(C3,7,8),'0!/00!/00')。

方法2:

1、在目标单元格的第一单元格中输入出生年月。

2、选定所有目标单元格,包括第一个输入出生年月的单元格。

3、快捷键Ctrl+E。

解读:

公式=TEXT(MID(C3,7,8),'0!/00!/00')中利用Mid函数提取出生年月,然后用Text对其设置格式。


三、Datedif:计算年龄

方法:

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

解读:

1、Datedif函数为系统隐藏函数,功能为:按照指定的方式计算两个时间之间的差值。

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

3、年龄就是当前年份减去出生年份,所以公式中按年统计哦!


四、IF+Mod+Mid:从身份证号中计算年龄

方法:

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

解读:

1、身份证号码中的第17位代表性别,如果为奇数,则为“男”,偶数为“女”。

2、用Mid函数提取身份中号中的第17位,然后用Mod函数求余,如果为奇数,则余数为1,暨为True,然后用IF函数判断,则返回“男”;如果为偶数,则余数为0,暨为False,然后用IF函数判断,则返回“女”。


五、Vlookup:查询引用

方法:

在目标单元格中国输入公式:=VLOOKUP(H3,B3:D9,3,0)。

解读:

Vlookup函数是常用的查询引用函数,语法结构:=Vlookup(查找值,查找范围,返回查找范围中第X列的值,匹配模式),其中匹配模式用代码0或1表示,0为精准查询,1为模糊查询。


六、限制输入单元格内容的长度

目的:限制单元格的文本长度为18。

方法:

1、选定目标单元格。

2、【数据】-【数据验证】-【设置】,选择【允许】中的【文本长度】,【数据】中的【等于】。

3、在【长度】中输入18。

4、单击【出错警告】标签,输入提示信息并【确定】。


七、内容重复时提示。

目的:当单元格值相同时,背景填充为“红色”。

方法:

1、选定目标单元格。

2、【条件格式】-【新建规则】,选择【新建规则类型】中的【使用公式确定要设置格式的单元格】。

3、在【为符合此公式的值设置格式】中输入:=COUNTIF($B:$B,B3)>1并单击右下角的【格式】-【填充】。

4、选择填充色并【确定】-【确定】。

解读:

当B列单元格中的值重复时,背景色填充为指定的颜色。


八、禁止内容重复。

方法:

1、选定目标单元格。

2、【数据】-【数据验证】-【设置】。

3、选择【允许】中的【自定义】,在【公式】中输入:=COUNTIF($B:$B,B3)=1。

4、单击【出错警告】标签,输入警告信息并【确定】。


九、批量行内排序。

方法:

在目标单元格中输入公式:=LARGE($D3:$O3,COLUMN(A1))或=SMALL($D3:$O3,COLUMN(A1))。

解读:

1、Large函数的作用是返回指定范围中第K个最大值,而Small正好相反,是返回指定范围中的第K个最小值。

2、借助Column函数返回表格的列数,从而达到排序的目的。


十、根据值的范围填充指定的颜色。

方法:

1、选定目标单元格。

2、【条件格式】-【新建规则】,单击【选择规则类型】中的【使用公式确定要设置格式的单元格】,并在【为符合此公式的值设置格式】中输入分别输入:=D3<60;=(AND(D3>60,D3<85));=D3>85。

3、分别输入公式时,单击右下角的【格式】-【填充】,填充【红色】、【绿色】、【蓝色】。

解读:

没输入一个公式,需要单击【格式】-【填充】并选取填充色一次,暨本示例共需填充三次颜色哦!


十一、判断单元格中是否包含指定文本

目的:判断“地区”中是否包含“海”。

方法:

在目标单元格中输入公式:=IF(COUNTIF(E3,'*海*')=1,'是','')。

解读:

利用Countif函数统计当前单元格中“海”的个数,如果=1,返回“是”,否则返回“”。


十二、返回指定范围内不重复值的个数。

方法:

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

2、利用Sum+Countif组合函数时,需要用Ctrl+Shift+Enter填充。而Sumproduct+Countif只需回车或Ctrl+Enter填充。


结束语:

        今天的内容就到此为止了,对于12个实用技巧,你Get到了吗?欢迎在留言区留言讨论哦!


    本站是提供个人知识管理的网络存储空间,所有内容均由用户发布,不代表本站观点。请注意甄别内容中的联系方式、诱导购买等信息,谨防诈骗。如发现有害或侵权内容,请点击一键举报。
    转藏 全屏 打印 分享 献花(0

    0条评论

    发表

    请遵守用户 评论公约

    类似文章 更多