[办公技巧] 成为公司的Excel大神,学会这10组公式就够了!
作者:精品下载站 日期:2021-12-16 01:45:03 浏览:807 分类:玩软件
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公式,大家可以收藏下,以后遇到类似的问题,直接套用即可
猜你还喜欢
- 03-12 [玩转工具] Rufus:快速创建 USB 启动盘的最佳工具
- 12-13 [软件故障] 修复 Windows 11/10 上的 Google Chrome 屏幕闪烁问题
- 12-13 [软件故障] 修复Windows 11上的Google Chrome屏幕闪烁问题
- 04-19 [软件技巧] 5个在Chrome浏览器上恢复已删除的历史记录的顶级方法
- 03-29 [软件技巧] 剪映专业版怎么把两个草稿合在一起
- 03-29 [玩软件] Linux 虚拟机使用 xshell 连接 (debian、kali、CentOS)
- 03-29 [软件问题] 解决 win10 命令行下运行 python 弹出 Windows 应用商店
- 03-29 [软件技巧] Anaconda安装教程以及Anaconda和pip配置国内镜像
- 03-29 [玩软件] LatticePHP-使用PHP生成点阵图
- 03-29 [软件问题] chrome、Edge浏览器显示“您的浏览器受管理”是被植入病毒或其它程序了吗?
- 03-29 [环境问题] 为Nginx和Apache配置多版本PHP、切割多个conf文件
- 03-29 [软件玩法] 配置frp实现内网穿透
取消回复欢迎 你 发表评论:
- 精品推荐!
-
- 最新文章
- 热门文章
- 热评文章
[短剧] 2025年06月03日 精选+付费短剧推荐25部
[软件合集] 25年6月3日 精选软件44个
[短剧合集] 2025年06月2日 精选+付费短剧推荐39部
[软件合集] 25年6月2日 精选软件18个
[软件合集] 25年6月1日 精选软件15个
[短剧合集] 2025年06月1日 精选+付费短剧推荐59部
[短剧] 2025年05月31日 精选+付费短剧推荐58部
[软件合集] 25年5月31日 精选软件66个
[电影] 黄沙漫天(2025) 4K.EDRMAX.杜比全景声 / 4K杜比视界/杜比全景声
[风口福利] 短视频红利新风口!炬焰创作者平台重磅激励来袭
[剧集] [央视][笑傲江湖][2001][DVD-RMVB][高清][40集全]李亚鹏、许晴、苗乙乙
[电视剧] 欢乐颂.5部全 (2016-2024)
[电视剧] [突围] [45集全] [WEB-MP4/每集1.5GB] [国语/内嵌中文字幕] [4K-2160P] [无水印]
[影视] 【稀有资源】香港老片 艺坛照妖镜之96应召名册 (1996)
[剧集] 神经风云(2023)(完结).4K
[剧集] [BT] [TVB] [黑夜彩虹(2003)] [全21集] [粤语中字] [TV-RMVB]
[资源] B站充电视频合集,包含多位重量级up主,全是大佬真金白银买来的~【99GB】
[影视] 内地绝版高清录像带 [mpg]
[书籍] 古今奇书禁书三教九流资料大合集 猎奇必备珍藏资源PDF版 1.14G
[美图] 2W美女个美女小姐姐,饱眼福
[电视剧] [突围] [45集全] [WEB-MP4/每集1.5GB] [国语/内嵌中文字幕] [4K-2160P] [无水印]
[剧集] [央视][笑傲江湖][2001][DVD-RMVB][高清][40集全]李亚鹏、许晴、苗乙乙
[电影] 美国队长4 4K原盘REMUX 杜比视界 内封简繁英双语字幕 49G
[电影] 死神来了(1-6)大合集!
[软件合集] 25年05月13日 精选软件16个
[精品软件] 25年05月15日 精选软件18个
[绝版资源] 南与北 第1-2季 合集 North and South (1985) /美国/豆瓣: 8.8[1080P][中文字幕]
[软件] 25年05月14日 精选软件57个
[短剧] 2025年05月14日 精选+付费短剧推荐39部
[短剧] 2025年05月15日 精选+付费短剧推荐36部
- 最新评论
-
- 热门tag