[办公技巧] 成为公司的Excel大神,学会这10组公式就够了!
作者:精品资源网 日期:2021-12-16 01:45:19 浏览:1036 分类:玩转PC/软件
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-27 [软件应用] 谷歌商店,可以访问了
- 03-11 [玩软件] 50M+/S!直接拉满!
- 03-09 [网络技巧] 如何使用IPv6地址直接访问http,https服务及Windows共享文件夹[UNC路径]
- 03-06 [玩软件] 2024最新Telegram中文设置教程和最全电报繁体简体中文语言包链接
- 10-11 [系统技巧] win10优化:可以禁用的服务
- 09-17 [系统技巧] 用手机蓝牙锁定计算机,不用第三方软件,利用手机蓝牙,实现电脑人离即锁屏-电脑蓝牙怎么使用...
- 09-17 [系统技巧] 设置计算机电源方案20分钟后,Window 7中如何根据自己的实际需求详细设置电源方案...
- 09-17 [系统技巧] 计算机频繁读写硬盘可能存在,win10电脑空闲时刻会偷偷读写硬盘的处理方法
- 09-17 [系统技巧] system占用cpu解决(ntoskrnl.exe)
- 09-11 [电脑技巧] 电脑便签软件怎么开启AI自动设置提醒功能?
- 09-06 [玩软件] 解决Edge浏览器需要按两次Ctrl + C才能复制
- 08-02 [系统技巧] 关于系统无法在线升级和微软商店无法下载报0x80004002修复方案
取消回复欢迎 你 发表评论:
- 精品推荐!
-
- 最新文章
- 热门文章
- 热评文章
[影视] 不亲密朋友 WEB-DL版下载/(Not) Close Friends/Not Friends/Phuean (Mai) Sanit/他的最后一个朋友(台)/第151个朋友(港) 2023 เพื่อน (ไม่) สนิท 7.14G
[影视] 周处除三害 WEB-DL版下载/The Pig, the Snake, and the Pigeon 2023 周處除三害 6.34G
[影视] 真实的谎言 4K蓝光原盘下载+高清MKV版/真实谎言/魔鬼大帝:真实的谎言 1994 True Lies 78.4GB
[硬件检测] 内存测试工具 MemTestPro 汉化版(7.0)
[硬件检测] 专业硬件检测工具 | HWiNFO(8.0.5400.0)
[系统加速] 利用内存给硬盘加速 | RamCache III 去限制版(1.01.12)
[游戏资讯] 最近热门手游有哪些,装备全靠打的手游有哪些游戏,好玩的角色扮演游戏手游
[云图志] 【皇马】Adidas Y-3 山本耀司联名广告 图集
[素材] 视频剪辑BGM音频素材包合集
[网页开发] FrontPage 2003 简体中文版
[图像制作] Adobe Illustrator 2024 v28.1.0.141 破解版
[思维导图] MindManager_2023_v23.1.240_中文破解版
[思维导图] EdrawMax(万兴亿图图示破解版)13.0.2.1071
[视频剪辑] Filmora(万兴喵影2023)13.0.60.5095 破解版
[下载工具] Internet Download Manager 6.42.7 (IDM)
[漫画合集] 漫威 DC 漫画全集 PDF
[系统安全] Deep Freeze 8.71.020.5734 冰点还原破解版
[动画] 假如...? 第一至二季[全18集][简繁英字幕].What.If.S01-2[MP4][打包合集]
[资料] 3000 套电影电视剧 LOGO 宣传片常用音效合集包
[辅助下载] 4K Video Downloader_v4.30.0.5655破解版
[影视] 海王2:失落的王国 WEB-DL版下载/Aquaman 2/水人2/水行侠 失落王国(台)/水行侠与失落王国(港)/海王与失落的王国/潜水侠2 2023 Aquaman and the Lost Kingdom 24.97G
[影视] 三大队 WEB-DL版下载/Endless Journey/请转告局长,三大队任务完成了 2023 三大队 6.7G
[文艺晚会] 中央电视台-1987西游记晚会齐天乐 1080P全高清修复完整版
[电影] 孤注一掷 [1080p].No.More.Bets.2023.Chinese.CAMRip.x264[MP4]
[剧集] 繁花 (2023)[全30集][打包]
[影视] 如何做爱 WEB-DL版下载/第一次 2023 How to Have Sex 5.33G
[影视] 拿破仑 WEB-DL版下载/士兵行囊 / Kitbag 2023 Napoleon 26.61G
[影视] 涉过愤怒的海 WEB-DL版下载/怒海 / Across the Furious Sea 2023 涉过愤怒的海 26.3G
[影视] 首尔之春 WEB-DL版下载 /12.12: The Day 2023 8.06GB
[影视] 年会不能停! WEB-DL版下载/Johnny Keep Walking! 2023 年会不能停! 21.71G
- 最新评论
-
值得拥有Bernery 评论于:03-15 东方快车三月 评论于:03-15 周处除三害三月 评论于:03-14 有些**源确实没字幕精品资源网 评论于:03-12 多谢楼主分享Bernery 评论于:03-04 之前下的没有中文字幕啊Bernery 评论于:03-04 热辣滚烫三月 评论于:02-29 感谢楼主,龙年大吉!三月 评论于:02-25 龙年大吉!三月 评论于:02-25 龙年大吉三月 评论于:02-25
- 热门tag