excel函数公式大全(成为大神学会这10组公式就够了)
Hello,大家好,今天跟大家分享10组常用的Excel公式,大家如果在以后遇到类似的问题,直接套用函数即可,让你成为同事眼中的Excel大神!废话不多说,让我们直接开始吧!
一、身份证号码计算出生日期
公式:=--TEXT(MID(A2,7,8),"0-00-00")
使用方法:复制公式将A2更改为你表中的单元格位置即可
原理概述:首先利用mid函数将身份证号码中的出生日期提取出来,随后利用text函数将提取出来的字符串设置为日期的显示样式,最后利用2个减号将其设置为常规格式。为什么要使用减号呢?这是因为text函数是文本函数,它的结果也是文本格式,文本格式下的日期是不能使用函数计算的,所以需要转换格式
二、身份证号码计算年龄
公式:=DATEDIF(TEXT(MID(A2,7,8),"0-00-00"),TODAY(),"y")
使用方法:复制公式后,只需更改A2为表格中对应的单元格即可
原理概述:首先利用TEXT(MID(A2,7,8),"0-00-00")来提取出出生日期,随后利用DATEDIF来计算出生日期与今天相差了多少年,TODAY()函数的作用是获取今天的日期
三、身份证号码计算性别
公式:=IF(MOD(MID(A2,17,1),2)=1,"男","女")
使用方法:复制公式,更改A2为对应的单元格位置即可
原理概述:身份证号码第17位是性别代码,偶数为女性,奇数为男性。首先使用mid函数将17位的数字提取出来,随后利用mod函数判断其奇偶性,最后利用if判断返回对应的结果
四、多条件查询
公式:=LOOKUP(1,0/((A2:A8=E2)*(B2:B8=F2)),C2:C8)
使用方法:复制公式更改为对应的单元格区域即可,格式为:=LOOKUP(1,0/(条件1*条件2*条件3),需要返回的结果列)
原理概述:这是一个数组公式,讲解起来比较复杂,主要是利用了逻辑值相乘,可以把true看做是1,false看着是0,两两相乘,结果为1对应的就是我们需要查找的数据
五、通过关键字查找数据
公式:=VLOOKUP("*"&D2&"*",$A$1:$B$8,1,FALSE)
使用方法:D2需要更改为表格中对应的查找值,A1:B8需要更改表格中对应的查找区域并绝对引用
原理概述: *号是一个通配符,他表示任意多个字符,我们将查找值得前后分别连接两个星号,这样的话就可以实现通过关键字查找数据
六、反向查找
公式:=INDEX(A1:A10,MATCH(E2,B1:B10,0))
使用方法:复制公式替换为自己表中的单元格数据即可,A1:A10是需要返回的结果列,E2是查找值,B1:B10是查找的数据区域
原理概括:所谓的反向查找就是找到查找值左侧的数据,利用vlookup是非常复杂的,但是利用index+match却非常的简单,首先用match函数查找数据位置,随后利用index函数返回对应的结果即可
七、统计重复数据
公式:=IF(COUNTIF($A$2:$A$10,A2)=1,"","是")
使用方法:更改A2:A10与A2为你表中对应的单元格位置即可,A2:A10是统计的数据区域,需要进行绝对引用,A2是第一个数据
原理概述:主要是利用countif进行条件计数,如果数据不存在重复,它的计数结果就是1,否则的话结果就大于1,最后利用if函数做一个判断即可
八、统计不重复的个数
公式:=SUMPRODUCT(1/COUNTIF(A2:A10,A2:A10))
使用方法:复制公式后,只需要更改COUNTIF函数的第一第二参数为需要统计的数据区域即可
原理概述:首先利用countif函数计算重复值,随后用1除以这个结果,最后利用sumproduct求和,比如一个值重复出现3次,就会得到3个1/3,它们相加后结果是1
九、计算人数
公式:=LEN(B2)-LEN(SUBSTITUTE(B2,","))+1
使用方法:更改公式中的B2为你表格中的单元格,在SUBSTITUTE更换为你表格中的分符号。
原理概述:在这里每个姓名都是利用逗号隔开的,首先先计算下每个单元格的字符数,随后利用SUBSTITUTE函数将逗号都替换掉,然后再计算下字符数,两者相减就是逗号的个数,然后再加1就会得到人数
十、拆分姓名与电话
提取姓名:=LEFT(A2,LENB(A2)-LEN(A2))
提取电话:=RIGHT(A2,LEN(A2)-(LENB(A2)-LEN(A2)))
使用方法:将公式中的A2替换为自己表格中的单元格位置即可
原理概括:中文在excel中会占据2个字节,而数值仅仅只会占用1个字节,先利用len函数得到字符数,再利用lenb计算字节数,两者相减就会得到汉字的个数,随后利用left或者right函数提取数据即可
以上就是今天分享的所有的Excel公式,大家可以收藏下,以后遇到类似的问题,直接套用即可
与excel相关文章
两个excel表快速核对数据差异(两张表格vlookup怎么用)
2022-06-21
相信大家在工作的时候,经常会碰到一个问题,那就是我们当我们接受到别人发过来的数据的时候,我们需要核对别人的数据名单和我们的是否一致。如果数据有问题,那可能就涉及重做的情
vlookup函数两张表匹配(excel表1和表2数据匹配)
2022-06-21
举个实例的例子来说明,我们模拟了数据,左边是表1,是1月份工资表数据,右边是表2,是2月份工资表数据。 然后每个月的员工数据不是固定的,有些是重复,有些是单一表里面有的,然后我们现
vlookup函数的使用方法(vlookup函数通俗易懂的理解)
2022-06-21
最近来了很多新朋友,都不约而同地想要我讲解下vlookup函数的使用方法,今天就跟大家讲解下vlookup函数的基本用法,以及我们再使用vlookup函数的时候需要注意的几个点 一、vlooku
excel饼图制作流程(如何通过表格数据生成饼状图)
2022-06-20
今天教大家给饼图添加一张背景图,看上去就好像一张图片被饼图切割成了若干块。 案例: 用下图 1 的数据表制作饼图,并且给饼图添加一张规定的背景图。效果如下图 2 所示。
批量填充快捷键excel(excel快速填充的方法)
2022-06-20
Hello,大家好,已经很久没跟大家分享Excel动图小技巧了,今天动图技巧又开课了,今天跟大家准备了15个Excel动图小技巧,除了动图之外,还会配备文字讲解,让你一看就懂,一学就会,快速提高
excel合并拆分单元格快捷键(Excel数据批量拆分合并与加密)
2022-06-20
在使用Excel进行数据处理的时候,我们经常会涉及到将单元格里面的数据,进行拆分、合并、提取、加密等数据处理,很多人看到这些操作,都绝对会需要用到很复杂的操作技巧才能实现。
Excel身份证号提取年龄公式(把身份证号码转化为年龄方法)
2022-06-20
作为老师,经常要整理几十个甚至是上百个学生的身份证信息,看着那一串串密密麻麻的数字,是不是觉得头大? 别担心,今天教你几个快捷小技巧,让你轻轻松松通过一个身份证号,快速提取很
常用alt快捷键大全(Excel中10个Alt键相关的快捷操作)
2022-06-13
今天分享一组与Alt键相关的操作技巧,熟练掌握,可以提高表格制作效率哟! 01.Alt + = 键快速求和 选中求和单元格和求和结果存放单元格区域,按下Atl+=键快速完成行、列同时求和。
春节统计图怎么画(excel制作统计图的方法)
2022-04-21
excel制作统计图 统计图是利用点、线、面、体等绘制成的几何图形,用来表示各种数量之间的关系及其变动情况的图形总称。常用的类型有条形统计图、扇形统计图、折线统计图等。
业绩表格怎么做(用Excel制作的销售业绩汇报表)
2022-04-05
用Excel制作表格,数据处理很重要,可以帮我们节省时间,提高工作效率。但美化表格同样重要,毕竟很多表格要对外公开、呈报老板的,老板看到漂亮的表格,心情也好,工资翻番都有可能哟!
与12306售票退票有新变化相关文章
与海洋逃生筏生存模拟下载相关文章
与海洋逃生筏生存模拟OceanEscape相关文章
与九乐棋牌手机版本下载相关文章
战神4怎么传送-战神4传送门制作方法
2022-11-19
战神4怎么传送?战神4传送门制作方法,在游戏中想要实现快速移动,是需要有道具协助的,很多玩家不知道怎么实现快速移动,其实传送门就是一个非常方便的选择,不知道怎么制作的玩家就一起来看看吧。
与九乐棋牌手机版本安卓版下载相关文章
战神4怎么传送-战神4传送门制作方法
2022-11-19
战神4怎么传送?战神4传送门制作方法,在游戏中想要实现快速移动,是需要有道具协助的,很多玩家不知道怎么实现快速移动,其实传送门就是一个非常方便的选择,不知道怎么制作的玩家就一起来看看吧。