这里介绍一组经常使用Excel函数公式的用法。 记住这些做法,把工作效率再降一点吧。
1、IF函数的条件判断
IF函数是最常用的判断函数之一,可以完成否则的判断。
如下图所示,评价得分标准为9分,必须判断B列的评价成绩是否合格。
=if(B4=9,“通过”,“失败”
IF相当于标准语的“tala”,通常的用法如下。
IF (符合判断的条件、条件时的结果、不符合条件时的结果) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) ) )。
2、多条件判断
如下图所示,部门以生产、岗位为中心接受高温补助的情况。 在d列中使用表达式:
=if(and ) B2=“生产”、C2=“主体操”)、“有”、“无”)
AND函数判断两个条件,如果同时满足,则IF函数返回“有”,否则不满足。
3、条件合计
如下图所示,使用SUMIF函数计算一组总成绩。
=sumif(d2:d5,F2,C2:C5 ) ) )。
SUMIF的使用方法如下。
=SUMIF (条件区域,指定的相加条件,相加区域) ) ) ) ) ) ) ) ) ) ) )。
用简单易懂的话来说明:
如果D2:D5区域中的类等于F2单元的“一对”,则对C2:C5单元对应的区域进行求和。
4、多条件合计
如下图所示,应统计部门生产且岗位为主体操作的补助总额。
公式如下。
=sumifs(d2:d9,B2:B9,F2,C2:C9,G2 ) )
SUMIFS的使用方法如下。
=SUMIFS (合计的区域、条件区域1、指定的合计条件1、条件区域2、指定的合计条件2、 )
5、条件计数
如下图所示,统计指定店铺的业务件数。 也就是说,统计b列中指定的店铺名称有几个。
=countif(b2:b12,E3 ) )。
在COUNTIF函数统计条件区域中,满足指定条件的单元格数。 常见用法如下
=COUNTIF (条件区域,指定条件) ) ) ) ) ) ) ) ) ) ) )。
6、多条件计数
要求:统计部门统计生产且岗位为主体操作的人数
公式如下。
=countifs(b2:b9,F2,C2:C9,G2 ) ) ) ) ) ) ) ) ) )。)
在COUNTIFS函数统计条件区域中,满足指定多个条件的单元格数。 常见用法如下
=COUNTIFS (条件区域1、指定条件1、条件区域2、指定条件2……)
7、条件检索
VLOOKUP函数一直是大侠恋人般的存在,函数语法如下。
VLOOKUP (要找谁、在哪里找、返回第几列的内容、准确地找还是近似地找)
查看F5单元格中的员工姓名是哪个职务,如下图所示。
=vlookup($f5、5、5、b 1:133601:1: d $ 10、2、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列的电话号码,请使用以下公式,如下图所示:
=A2B$1B2
11、合并格式化单元格的内容
合并格式化内容时,Excel默认采用普通格式合并,但对于日期、时间或其他格式化数值,则会很失望。
如何正确连接所需的字符串? 其实很简单。 C2的公式如下。
=A2Text(B2,' y年m月d日) ) ) )。
首先使用TEXT函数将b列的日期转换为具有特定模式的字符串,然后与a列的名称连接,最终得到所需的模式。
12、比较大小写单词是否相同
如果在A1和A2单元格中分别输入了大小写单词,则使用以下公式确定时,Excel默认相同:
=A2=B2
要区分大小写,请使用以下表达式:
=EX
ACT(A2,B2)EXACT函数 区分大小写,但忽略格式上的差异。
13、提取混合内容中的姓名
如下图,要从A列姓名电话中提取出姓名,除了使用高版本的自动填充功能,还可以使用公式完成:
=LEFT(A2,LENB(A2)-LEN(A2))
LENB函数将每个汉字(双字节字符)的字符数按2计数,LEN函数则对所有的字符都按1计数。因此“LENB(A2)-LEN(A2)”返回的结果就是文本字符串中的汉字个数。
LEFT函数从文本字符串的第一个字符开始,返回指定个数的字符,最终提取出员工姓名。
14、根据身份证号码提取出生年月
计算公式为:
=1TEXT(MID(B2,7,8),“0-00-00”)
首先使用MID函数从B2单元格的第7位开始,提取出表示出生年月的8个字符,结果为:
“19780215”
再使用TEXT函数将字符串转换为日期样式:
“1978-02-15”
然后通过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