11组Excel常用公式，2小时才整理好的，直接套用即可

好久没跟大家分享公式了，今天就跟大家分享几组Excel中的常用公式，都是固定的套路，以后遇到类似的问题，直接套用，一定记得点赞收藏下，不然就找不到了

一、身份证号码判断重复

公式：=IF(COUNTIF($B$4:$B$11,B4&"*")=1,"","是")

Excel的精度只有15位，超过了的纯数字就不能用常规的方法判断，在这里将B4连接一个星号，将其强制变成文本格式就可以代入判断了

如果COUNTIF结果大于1就表示数据重复，最用使用IF函数做判断输出结果

图片

二、计算退休年龄

公式：=EDATE(C4,IF(B4="男",60,55)*12)+1

首先用IF函数判断性别，男60退休，女55退休，乘以12得到总的月份，最后利用EDATE函数返回对应的日期，最后再加1向后延伸1天

EDATE可以返回某一个日期后几个月的日期

图片

三、身份证号码计算年龄

公式：=DATEDIF(TEXT(MID(B3,7,8),"0-00-00"),TODAY(),"Y")

首先利用MID函数提取到对应的出生日期，之后利用TEXT函数来讲数据转换为日期格式，最后可以利用DATEDIF函数来计算两个日期之间的差值就可以得到年龄了

图片

四、身份证号码计算性别

公式：=IF(MOD(MID(B3,17,1),2),"男","女")

身份证号码的第十七位是性别代码，偶数为女性，奇数为男性，首先使用MID将其提取出来，之后使用MOD函数来判断其奇偶性，最后利用IF函数根据奇数偶数来做判断即可

图片

五.不重复计数

公式：=COUNTA(UNIQUE(B4:B12))

UNIQUE的作用是提取不重复的数据，得到不重复的数据后，我们再使用COUNTA函数来统计个数即可，这个函数需要版本支持，如果你的你版本较低，可以使用下面的方法

公式：=SUMPRODUCT(1/COUNTIF(B4:B12,B4:B12))

图片

六、一对多查询

公式：=FILTER(B4:B18,A4:A18=D4)

FILTER函数是一个筛选函数，筛选的本质就是通过1个值筛选到多个结果，所以可以利用它来进行一对多查询，操作也是比较简单的

图片

七、根据关键字求和

公式：=SUMIF(B4:B15,"*车间*",D4:D15)

在这里关键点是通配符的应用，我们需要要在关键字的前后各添加一个【*】号的通配符，就可以进行关键字求和了，星号表示任意多个字符，只要数据中出现【车间】的关键字，就会被匹配

图片

八、合并同类项

公式：=TEXTJOIN(",",TRUE,IF($B$4:$B$18=E8,$C$4:$C$18,""))

首先我们使用IF函数来进行判断，得到班级对应的所有姓名，最后使用TEXTJOIN函数来将这些姓名都连接在1个单元格中，TEXTJOIN的作用是根据分隔符来进行数据的连接

图片

九、自动排序

公式：=SORT(A4:B13,2,-1)

SORT是一个排序函数，第一参数设置为排序的区域，第二参数用于指定根据那一列进行排序，第三参数用来设置升序还是降序

图片

十、多表格汇总

公式：=TOCOL('1月:3月'!A2:A15,3)

首先输入公式，然后点击1月的sheet，然后按照shift点击3月的sheet名字，之后选中对应的文具区域，可以多选一些，第二参数设置为3忽略空白与错误，点击回车，然后向右拖动公式即可

图片

十一、提取前几个数字

公式：=TAKE(SORT(A2:C13,3,-1),3)

SORT函数是一个排序函数，第三参数为-1会将数据从大到小排列，最后使用TAKE函数来提取前几个数据，设置为3就是前3个数据，设置为-3就是后3个

图片

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。