Excel 小技巧 | 您所在的位置:网站首页 › if函数判断一个单元格是否为空 › Excel 小技巧 |
AND函数对两个条件判断,如果同时符合,IF函数返回“有”,否则为无。 条件求和 如下图所示,使用SUMIF函数计算一班的总成绩: =SUMIF(D2:D5,F2,C2:C5) SUMIF用法是: =SUMIF(条件区域,指定的求和条件,求和的区域) 用通俗的话描述就是: 如果D2:D5区域的班级等于F2单元格的“一班”,就对C2:C5单元格对应的区域求和。 条件计数函数的使用 条件计数函数:COUNTIFS,它是一个多条件计数函数 它的语法是:=COUNTIFS(第一个判断区域, 第一个条件, 第二个判断区域, 第二个条件,......) 这个函数的判断区域与条件都是成对出现的 最多可以设置127对条件。 下面就是统计的【原料车间且等级为3】的人员数 多条件计数 要求:统计统计部门为生产,并且岗位为主操的人数 公式为: =COUNTIFS(B2:B9,F2,C2:C9,G2) COUNTIFS函数统计条件区域中,符合多个指定条件的单元格个数。常规用法为: =COUNTIFS(条件区域1,指定条件1,条件区域2,指定条件2……) 条件查找 VLOOKUP函数一直是大众情人般的存在,函数的语法为: VLOOKUP(要找谁,在哪儿找,返回第几列的内容,精确找还是近似找) 如下图,要查询F5单元格中的员工姓名是什么职务。 =VLOOKUP($F$5,$B$1:$D$10,2,0) 使用该函数时,需要注意以下几点: 1、第4参数一般用0(或FASLE)以精确匹配方式进行查找。 2、第3参数中的列号,不能理解为工作表中实际的列号,而是指定返回值在查找范围中的第几列。 3、如果查找值与数据区域关键字的数据类型不一致,会返回错误值#N/A。 4、查找值必须位于查询区域中的第一列。 多条件查找 如下图所示,要求查询部门为生产,并且岗位为部长的姓名。 公式为: =LOOKUP(1,0/((B2:B9=F2)*(C2:C9=G2)),A2:A9) LOOKUP函数多条件查询写法为: =LOOKUP(1,0/((条件区域1=条件1)*(条件区域2=条件2)),查询区域) 条件求和函数 函数SUMIFS:它是一个多条件求和函数。 它的语法:=SUMIFS(求和区域, 第一个判断区域, 第一个条件, 第二个判断区域, 第二个条件,.....) 这个函数与AVERAGEIFS函数非常形似,只不过就是将第一参数改为了,求和的区域,其他的都是一模一样的 下面就是计算的【原料车间且等级等于3级】的薪资之和 条件求均值函数 AVERAGEIFS函数:它是一个均值函数,作用是根据多个条件求平均值, 语法:=AVERAGEIFS (求平均值的区域, 第一个判断区域, 第一个条件, 第二个判断区域, 第二个条件,....) 这个函数除了第一参数之外,判断区域与条件也都是成对出现的,它与COUNTIFS的区域就是将第一参数设置为求平均值的区域而已,最多也是设置127对条件 下图计算的是【原料车间且等级为3】的平均薪资 条件最大值函数 函数MAXIFS:它的作用是根据条件求最大值 它的语法:=MAXIFS(求最大值区域, 第一个判断区域, 第一个条件, 第二个判断区域, 第二个条件,....) 下面计算的是【原料车间且等级为3】的最大薪资 条件最小值函数 函数MINIFS:它的作用是根据条件求最小值。 它的语法:=MINIFS(求最小值区域, 第一个判断区域, 第一个条件, 第二个判断区域, 第二个条件,....) 用法与前面的几个函数完全一致,就不再多做介绍了,在这里我们关键是需要牢记函数名,只要每个函数的作用即可 下面就是计算的【原料车间且等级等于3级】的最小薪资 在这里我们没有介绍单条件统计数函数如:COUNTIF、SUMIF等 因为多条件统计函数完全可以代替单条件统计函数 且更加实用,所以我们只要牢记多条件统计函数即可 计算文本算式 如下图,要计算单元格中的文本算式,先单击第一个要输入公式的单元格,定义名称 : 计算 = EVALUATE(C2) 然后在单元格中输入公式: =计算 合并多个单元格内容 连接合并多个单元格中的内容,可以使用&符号完成。如下图,要合并A列的姓名和B列的电话号码,可以使用公式: =A2&B$1&B2 合并带格式的单元格内容 合并带有格式的内容时,Excel默认按常规格式进行合并,但是如果是日期、时间或是其他有格式的数值,结果就会让人大失所望了: 如何才能正确连接出需要的字符串呢?其实很简单,C2公式为: =A2&TEXT(B2,” y年m月d日”) 首先使用TEXT函数,把B列的日期变成具有特定样式的字符串,然后再与A列的姓名连接,就变成了最终需要的样式。 比较大小写的单词是否相同 如果在A1和A2单元格中分别输入大小写的单词,使用以下公式判断时,Excel会默认二者是相同的: =A2=B2 如需区别大小写,可以使用公式: =EXACT(A2,B2) EXACT函数 区分大小写,但忽略格式上的差异。 提取混合内容中的姓名 如下图,要从A列姓名电话中提取出姓名,除了使用高版本的自动填充功能,还可以使用公式完成: =LEFT(A2,LENB(A2)-LEN(A2)) LENB函数将每个汉字(双字节字符)的字符数按2计数,LEN函数则对所有的字符都按1计数。因此“LENB(A2)-LEN(A2)”返回的结果就是文本字符串中的汉字个数。 LEFT函数从文本字符串的第一个字符开始,返回指定个数的字符,最终提取出员工姓名。 根据身份证号码提取出生年月 计算公式为: =1*TEXT(MID(B2,7,8),”0-00-00″) 首先使用MID函数从B2单元格的第7位开始,提取出表示出生年月的8个字符,结果为: “19780215” 再使用TEXT函数将字符串转换为日期样式: “1978-02-15” 然后通过*1计算,将其转换为真正的日期。最后设置为日期格式即可。 替换部分电话号码 如下图所示,要将手机号码的中间四位换成星号,公式为: =SUBSTITUTE(B2,MID(B2,4,4),”****”,1) SUBSTITUTE函数的用法是: SUBSTITUTE(要替换的文本,旧文本,新文本,[替换第几个]) 先使用MID函数取得B列号码中的中间4位,再用“*****”替换掉这部分内容。 最后一个参数使用1,表示只替换第一次出现的内容。比如第九行的电话号码是13801010101,最后四位和中间四位相同,如果不指定1,就会全部替换掉了。 屏蔽函数公式返回的错误值 在使用函数公式过程中,经常会返回一些诸如#N/A、#NAME?之类的错误值,要屏蔽这些错误值其实很简单,只需在原公式外侧加上一个IFERROR函数就好。 IFERROR函数的用法为: =IFERROR(原公式,出现错误时要返回的内容) 如果公式正确,就返回原有计算结果,如果公式返回的是错误值,就返回用户指定的显示内容。 四舍五入函数 ROUND函数这个想必大家经常用到吧,就是对数值按指定的位数四舍五入。比如: =ROUND(8/9,3) 就是将8/9的计算结果四舍五入到三位小数,结果为0.889。 取整的间隔小时数 计算两个时间的间隔小时数,不足一小时部分舍去,计算加班时经常会用到,说多了都是泪…… =TEXT(B2-B1,”[h]”) 提取日期时间中的日期值 要从日期时间数据中提取出日期,可以使用以下公式: =INT(A2) 要继续提取时间,只需要做个减法,就欧了: 生成随机数 RANDBETWEEN能够在指定范围内生成一组随机数据,对于广大质检、监理、统计人员来说,这的确是一个伟大的函数。 函数的用法是: =RANDBETWEEN(数字下限,数字上限) 比如以下公式,就是生成60到100之间的随机数: =RANDBETWEEN(60,100) 02 如何进行区间统计 区间统计,就是对一个指定的数据区域进行数据统计 对于这样的问题,其实非常的简单,我们拆分下就懂了 我们想要在下图中统计下【20-30】岁之间的员工这个 这个【20-30】它就是一个区间 我们完全可以将这个区间拆分为两个条件 然后根据这两个条件进行多条件计数即可 条件一:年龄大于等于20岁 条件二:年龄小于等于30岁 上面这两个条件构成的区域,就是我们需要统计的数据区域,公式如下图所示 如何进行区间求和 区间求和的原理与区间计数相似,我也是将1个区间拆分为2个条件计数,在这里我们想要计算下【原料车间】,【20-30】岁之前的薪资的总和,这个时候就需要SUMIFS来设置三组条件,具体的公式如下图所示 03 如何进行关键字统计 关键字统计就是字面意思,统计的条件是一个关键字 只要区域包含关键字,就会被统计 在Excel中实现这样的操作,一般是借助通配符*号来实现的 就是为关键字的前后各数一个*号就好了 比如现在我们想要统计下3级员工车间总的薪资 就可以将条件设置为【*车间*】,然后再用SUMIFS进行条件去求和就好了 这个方法适用于上面提到的所有函数 它都是可以使用的 好了,今天就简单分享到这里返回搜狐,查看更多 |
CopyRight 2018-2019 实验室设备网 版权所有 |