[办公技巧] 成为公司的Excel大神,学会这10组公式就够了!
作者:精品资源网 日期:2021-12-16 01:45:19 浏览:1037 分类:玩转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公式,大家可以收藏下,以后遇到类似的问题,直接套用即可
猜你还喜欢
- 06-26 [系统技巧] 远程桌面连接(mstsc)全攻略
- 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才能复制
取消回复欢迎 你 发表评论:
- 精品推荐!
-
- 最新文章
- 热门文章
- 热评文章
[游戏周边] 《黑神话:悟空》专题合集
[书籍资料] 古籍专题资源【135GB】
[攻略教程] 《百度》神龙红包活动怎么玩?
[攻略教程] 《冒险大作战》传奇榜前50名职业(9.03)
[攻略教程] 《冒险大作战》新职业征稿大赛奖励——台服
[攻略教程] 《冒险大作战》超级矿工卡使用规则
[攻略教程] 《冒险大作战》头部越战选手——回复法
[有声读物] 《刘兰芳评书集合》【46部】【播讲:刘兰芳】【WMA】
[攻略教程] 《光遇》9.3免费魔法怎么领取
[攻略教程] 《光遇》9.3大蜡烛在哪
[福利] 徐宅宅 性感热舞直播回放 超清画质
[写真] 栗子私房 VOL.0062 海边的白色少女 【30P】
[福利] 主播米娜 性感热舞直播回放 超清画质 视频合集
[福利] 主播亦亦 性感热舞直播回放 超清画质 37个视频合集
[文件编辑] WinHex 21.2 SR-2_x86_x64 绿色单文件版
[图片浏览] 2345看图王去广告安装版 x86 x64 11.3.0.10165
[Android] 阅读APP(小说软件)v3.23.032021 解除限制版
[电影] 惊声尖笑1-6【 4K 】
[福利] 6.8日吃瓜热门合集
[教育学习] 2024【武忠祥】考研数学领学班
[影视] 美国内战 4K蓝光原盘下载+高清MKV版/内战/帝国浩劫:美国内战(台)/美帝崩裂(港) 2024 Civil War 63.86G
[影视] 盟军敢死队 WEB-DL版下载/攻壳型战/无限制军团(港)/绝密型战/非君子作战部/非绅士战争部/非绅士特攻队(台) 2024 The Ministry of Ungentlemanly Warfare 10.6G
[影视] 特技狂人 WEB-DL版下载/坠落的人/特技猎人/特技玩家(台) 2024 The Fall Guy
[影视] 疯狂的麦克斯:狂暴女神 WEB-DL版下载/疯狂的麦克斯:弗瑞奥萨 / 芙莉欧莎:疯狂麦斯传奇篇章(台) / 芙莉欧莎:末日先锋传说(港) / 疯狂的麦克斯前传 / Mad Max: Furiosa / Furiosa 2024 Furiosa: A Mad Max Saga 26.45G
[课程]【黑马程序员】Python开发就业课 - 带源码课件
[福利] 徐宅宅 性感热舞直播回放 超清画质
[攻略教程] 《纸嫁衣7卿不负》第四章通关攻略
[福利] 主播亦亦 性感热舞直播回放 超清画质 37个视频合集
[写真] 栗子私房 VOL.0062 海边的白色少女 【30P】
[影视] 光荣之路 蓝光原盘下载+高清MKV版 1957 Paths of Glory 23.9G
- 最新评论
-
谢谢支持!!精品资源网 评论于:08-22 终于有了,谢谢分享Bernery 评论于:08-20 旧时的喜剧,哈哈哈精品资源网 评论于:08-01 这个电视剧应该是我最喜欢的了,心情不好的时候看看,听听贾队长怎么深入敌后抗日的故事,心里会好一些。zhoumuwang 评论于:07-30 很新啊Bernery 评论于:07-04 谢谢支持精品资源网 评论于:06-29 多谢楼主分享Bernery 评论于:06-17 下来看看,多谢楼主分享Bernery 评论于:06-17 感谢分享上善若水 评论于:06-04 多谢支持~~精品资源网 评论于:05-20
- 热门tag