excel根据a列提取b列内容_Excel常用函数公式21例,建议收藏

发布时间:2021-11-30 15:15:05

下面是21个常用的Excel函数公式的用法,学会这些套路,让工作效率再提高一些。建议收藏!








1、IF函数条件判断


IF函数是最常用的判断类函数之一,能完成非此即彼的判断。


如下图,考核得分的标准为9分,要判断B列的考核成绩是否合格。


=IF(B4>=9,"合格","不合格")





IF,相当于普通话的“如果”,常规用法是:


IF(判断的条件,符合条件时的结果,不符合条件时的结果)


2、多条件判断


如下图所示,如果部门为生产、岗位为主操 有高温补助。在D列使用公式:


=IF(AND(B3="生产",C3="主操"),"有","无")





AND函数对两个条件判断,如果同时符合,IF函数返回“有”,否则为无。


3、条件求和


如下图所示,使用SUMIF函数计算方小文的总成绩:


=SUMIF(A2:A16,E2,B2:B16)





SUMIF用法是:


=SUMIF(条件区域,指定的求和条件,求和的区域)


用通俗的话描述就是:


如果D2:D5区域的班级等于F2单元格的“一班”,就对C2:C5单元格对应的区域求和。


4、多条件求和


如下图所示,要统计部门为生产,并且岗位为主操的补助总额。


公式为:


=SUMIFS(D2:D10,B2:B10,F2,C2:C10,G2)





SUMIFS用法是:


=SUMIFS(求和的区域,条件区域1,指定的求和条件1,条件区域2,指定的求和条件2,……)


5、条件计数


如下图,要统计指定店铺的业务笔数。也就是统计B列中有多少个指定的店铺名称。


=COUNTIF(B2:B12,E3)





COUNTIF函数统计条件区域中,符合指定条件的单元格个数。常规用法为:


=COUNTIF(条件区域,指定条件)


6、多条件计数


要求:统计小组及格人数


公式为:


=COUNTIFS(C2:C10,F2,D2:D10,G2)





COUNTIFS函数统计条件区域中,符合多个指定条件的单元格个数。常规用法为:


=COUNTIFS(条件区域1,指定条件1,条件区域2,指定条件2……)


7、条件查找


VLOOKUP函数一直是大众情人般的存在,函数的语法为:


VLOOKUP(要找谁,在哪儿找,返回第几列的内容,精确找还是*似找)


如下图,要查询F2单元格中的员工姓名是哪个小组。


=VLOOKUP(F2,A2:D10,4,0)





使用该函数时,需要注意以下几点:


1、第4参数一般用0(或FASLE)以精确匹配方式进行查找。


2、第3参数中的列号,不能理解为工作表中实际的列号,而是指定返回值在查找范围中的第几列。


3、如果查找值与数据区域关键字的数据类型不一致,会返回错误值#N/A。


4、查找值必须位于查询区域中的第一列。


8、多条件查找


如下图所示,要求查询部门为生产,并且岗位为部长的姓名。


公式为:


=LOOKUP(1,0/((B2:B9=F2)*(C2:C9=G2)),A2:A9)





LOOKUP函数多条件查询写法为:


=LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),查询区域)


9、计算文本算式


如下图,要计算单元格中的文本算式,先单击第一个要输入公式的单元格,定义名称 :


计算 = EVALUATE(C2)


然后在单元格中输入公式:


=计算





10、合并多个单元格内容


要连接合并多个单元格中的内容,可以使用&符号完成。如下图,要合并A列的姓名和B列的电话号码,可以使用公式:


=A2&B$1&B2





11、合并带格式的单元格内容


合并带有格式的内容时,Excel默*闯9娓袷浇泻喜ⅲ侨绻侨掌凇⑹奔浠蚴瞧渌懈袷降氖担峁突崛萌舜笫耍





如何才能正确连接出需要的字符串呢?其实很简单,C2公式为:


=A2&TEXT(B2," y年m月d日")





首先使用TEXT函数,把B列的日期变成具有特定样式的字符串,然后再与A列的姓名连接,就变成了最终需要的样式。


12、比较大小写的单词是否相同


如果在A1和A2单元格中分别输入大小写的单词,使用以下公式判断时,Excel会默认二者是相同的:


=A2=B2





如需区别大小写,可以使用公式:


=EXACT(A2,B2)





EXACT函数 区分大小写,但忽略格式上的差异。


13、提取混合内容中的姓名


如下图,要从A列姓名电话中提取出姓名,除了使用高版本的自动填充功能,还可以使用公式完成:


=LEFT(A2,LENB(A2)-LEN(A2))





LENB函数将每个汉字(双字节字符)的字符数按2计数,LEN函数则对所有的字符都按1计数。因此“LENB(A2)-LEN(A2)”返回的结果就是文本字符串中的汉字个数。


LEFT函数从文本字符串的第一个字符开始,返回指定个数的字符,最终提取出员工姓名。


14、根据身份证号码提取出生年月


计算公式为:


=1*TEXT(MID(B2,7,8),"0-00-00")





首先使用MID函数从B2单元格的第7位开始,提取出表示出生年月的8个字符,结果为:


"19820319"


再使用TEXT函数将字符串转换为日期样式:


"1982/3/19"


然后通过*1计算,将其转换为真正的日期。最后设置为日期格式即可。


15、替换部分电话号码


如下图所示,要将手机号码的中间四位换成星号,公式为:


=SUBSTITUTE(B2,MID(B2,4,4),"****",1)





SUBSTITUTE函数的用法是:


SUBSTITUTE(要替换的文本,旧文本,新文本,[替换第几个])


先使用MID函数取得B列号码中的中间4位,再用“*****”替换掉这部分内容。


最后一个参数使用1,表示只替换第一次出现的内容。比如第九行的电话号码是13801010101,最后四位和中间四位相同,如果不指定1,就会全部替换掉了。


16、屏蔽函数公式返回的错误值


在使用函数公式过程中,经常会返回一些诸如#N/A、#NAME?之类的错误值,要屏蔽这些错误值其实很简单,只需在原公式外侧加上一个IFERROR函数就好。


IFERROR函数的用法为:


=IFERROR(原公式,出现错误时要返回的内容)


如果公式正确,就返回原有计算结果,如果公式返回的是错误值,就返回用户指定的显示内容。


17、四舍五入函数


ROUND函数这个想必大家经常用到吧,就是对数值按指定的位数四舍五入。比如:


=ROUND(8/9,3)


就是将8/9的计算结果四舍五入到三位小数,结果为0.889。


18、取整的间隔小时数


计算两个时间的间隔小时数,不足一小时部分舍去,计算加班时经常会用到,说多了都是泪……


=TEXT(B2-B1,"[h]")





19、提取日期时间中的日期值


要从日期时间数据中提取出日期,可以使用以下公式:


=INT(A2)





要继续提取时间,只需要做个减法,就欧了:





20、生成随机数


RANDBETWEEN能够在指定范围内生成一组随机数据,对于广大质检、监理、统计人员来说,这的确是一个伟大的函数。


函数的用法是:


=RANDBETWEEN(数字下限,数字上限)


比如以下公式,就是生成60到100之间的随机数:


=RANDBETWEEN(60,100)





21、万能查询函数XLOOKUP


以下是2020最新版office365的新函数XLOOKUP,几乎一个公式完成了所有查询函数所需的功能。


完全可以代替LOOKUP、VLOOKUP、HLOOKUP、INDEX、MATCH组合。





向左查询





错误提示





*似比对





横向查询





模糊查询





逆向查询





双向筛选





转发后,私信:“Excel常用函数公式21例”,可获得文中的练*资料







相关资源:按一查找并红色显示 Excel VBA

相关文档

  • 走进母爱作文700字
  • 素描文艺常识
  • 以希望之光为题的高中作文
  • 两个女仔的结局
  • 20150206--JS巩固与加强4
  • 装修墙面要刷底漆吗?它的作用大着呢
  • 如何在HTML页面中插入百度地图
  • 中考地理简答题汇编
  • 毛概实践报告心得体会
  • 朋友小孩满月祝福语2020年
  • 知物由学 | 只要5行代码,就可以实现AI图像识别
  • spring-boot开发微信小程序后台(一)
  • 军训之苦
  • 女性阴虱应该如何治疗
  • Visual Basic函数大全!
  • 关于边防证单位介绍信的范文
  • vivo计步器在哪里打开
  • 音乐音符
  • 梦见车祸人没事车坏了
  • 如何设置excel数据透视表每列报表筛选字段数?
  • 诺贝尔的介绍要100多字的
  • 欧式风格装饰墙效果图
  • VALSE2019
  • Three.js视频教程
  • 快乐的尚湖游
  • 中两个数做减法_合成器原理??什么是减法合成器?
  • 学生防控应急预案
  • 漫漫古情在路上作文900字
  • 如何评价看电影买半价票被评为凤凰男现象
  • 人教版小学三年级上册数学教学课件
  • 猜你喜欢

  • 2018-2019-灭火器年检-范文模板 (2页)
  • 检验科仪器故障应急预案
  • 中国琉璃饰品市场发展研究及投资前景报告(目录)
  • 机床夹具设计设计指导书
  • 2018-2019班主任年终个人思想总结范文与2018-2019班主任期末个人工作总结汇编.doc
  • 精选扶贫工作计划3篇
  • 人文地理学 第十章旅游地理
  • 高等教育自学考试学前教育史试卷及答案【试题.知识点】
  • 2018-2019年明水县崇德镇中心小学三年级上册科学期末复习无答案
  • 循证医学在中医临床教学中的方法探究
  • 浅谈信息技术与初中语文教学的整合
  • 6定额的组成与应用 17页PPT文档
  • 2020版新设计一轮复*数学(文)江苏专版讲义:第三章 第一节 导数的概念及导数的运算 含答案
  • 安徽行天下大药房连锁有限公司滨湖店企业信用报告-天眼查
  • 最新电站培训工作总结资料
  • 河南福韵企业管理咨询有限公司(企业信用报告)- 天眼查
  • 云南省2018年保育员三级考试试题试卷及答案
  • 函数关系的表示法
  • 存款准备金政策有效性分析论文
  • 【范文】小学家长会活动方案_2
  • 责任重于泰山_六年级作文
  • 投影机的安装方法
  • 2020年新年联欢会主持词及串词
  • (新人教版)中考物理复*九年级物理18.3 测量小灯泡的电功率 同步练*5(含答案)-非常好
  • 咖啡因对男性影响更大
  • 2013年九年级中考模拟数学试卷(2)及答案
  • 人教2011课标版小学语文一年级下册《课文 语文园地三 日积月累+和大人一起读:胖乎乎的小手》_52
  • 最新人教版二年级数学下册第六单元测试卷
  • 年底销售鼓舞士气的口号
  • 迷你弹力带在雾霾天气室内体育课运用
  • 红帽Linxuix源码方式安装VMware-tools
  • Nginx路径重写(rewrite)案例 - 根据$query_string进行重写
  • 最新-学*实践科学发展观活动实施意见 精品
  • 多参数分析仪研究
  • 主任任职表态发言
  • 简阳市常住人口有多少
  • 编办领导班子XX年述职述廉报告
  • 新牛津9a unit4 growing up integrated skills
  • 中心学校办公室主任述职述廉报告【共6页】
  • 【教育学习文章】一篇记者节的演讲稿
  • 企业保险销售工作计划精选
  • 分享阿里开源的一个流程图,工作流图插件g6
  • 电脑版