EXCEL常规函数汇总(一)

笔者认为,数据的价值不仅仅只体现在企业中,个人也可以体会到数据的魅力,用技术力量探索行为密码,让大数据助跑每一个人,欢迎直筒们关注我的公众号,一起讨论数据中有趣的事情~

个人公众号为:livandata

平时数据分析过程中,经常会用到Excel的一些操作,此处做一些整理,方便使用。

1、vlookup(根据什么找,到哪里找,找哪个,0)函数的应用:主要是做匹配,通过一个字段关联两个表,然后将两个表匹配出来。

这一功能类似于SQL中的联表搜索:select  t1.a0, t2.a1, t2.a2 from t1, t2 where t1.a0=t2.a0

vlookup语句为:vlookup(a0, t2, a1a2, 0):其中a0即为条件,根据a0找到t2表中的a1和a2字段。

说明为:常规方法解析:https://jingyan.baidu.com/article/5552ef47e3228b518ffbc91c.html

五种深度应用:http://baijiahao.baidu.com/s?id=1601340239955498785&wfr=spider&for=pc

2、iferror(value,value_if_error)函数的应用:表示判断value的正确性,如果value正确则返回正确结果,否则返回value_if_error。

在C2单元格输入公式=IFERROR(A2/B2,"错误")

说明为:https://jingyan.baidu.com/article/36d6ed1f5296f21bce488354.html

3、row(单元格)函数的应用:Row函数用于返回单元格的行号,Rows函数用于返回数组或引用单元格的行数。

说明为:https://baijiahao.baidu.com/s?id=1614135728627759828&wfr=spider&for=pc

4、match(查找值,查找范围,0)函数的应用:Match函数用于返回查找值在数组的位置,表示查找小于或等于 lookup_value 的最大值。

这一功能类似于SQL中的条件搜索:select index(a0) from t1 where a0='90'

说明为:https://baijiahao.baidu.com/s?id=1611778949509455169&wfr=spider&for=pc

5、index(查询范围,行,列)函数的应用:返回表或区域中的值或对值的引用。

返回对应的值,下面的函数返回B4的数据。

说明为:https://jingyan.baidu.com/article/a24b33cd146efb19fe002be4.html

6、ABS(需要计算的值)函数的应用:取绝对值=abs(-2)得2。

7、MAX&MIN(需要计算的数组)函数:取数组中的最大值和最小值=max(A3:A13)得最大值。

8、round(需要舍的值,舍的位数)函数的应用:四舍五入=ROUND(2.52,1),保留一位小数。

9、ROUNDUP(需要舍的值,舍的位数)函数的应用:按照指定的小数位数向上舍入,即不管是几都进一,roundup(月份/3,0)为第几季。

10、ROUNDDOWN(需要舍的值,舍的位数)函数的应用:按照指定的小数位数向下舍入,不管是几都舍去,rounddown((月份+2)/3,0)为第几季。

说明为:https://jingyan.baidu.com/article/3a2f7c2ecdd9e126afd611f4.html

11、MID(text, start_num, num_chars)函数:从某一段字符串中截取出指定数量的字符,=mid(c2, 7, 8):从第七位开始,截取8个字符。

12、MOD(分子,分母)函数:取余函数。

13、IF(logic_condition, true_result, false_result)函数:某一条件成立后执行第一个,否则执行第二个。

=IF(AND(A2<500,B2="未到期"),"补款","")

说明:两个条件同时成立用AND,任一个成立用OR函数。

14、SUMIF(条件范围,条件,求和范围):条件求和函数。

15、SUMIFS(求和范围,条件1范围,条件1,条件2范围,条件2,……条件N范围,条件N):条件求和函数。

例如:

求男生的总成绩和男生中分数大于等于80分的总成绩。

方法:

=SUMIF(D3:D9,"男",C3:C9)

=SUMIFS(C3:C9,C3:C9,">=80",D3:D9,"男")。

16、COUNTIF(条件范围,条件)函数:条件计数函数

17、COUNTIFS(条件范围1,条件1,条件范围2,条件2……条件范围N,条件N)函数:条件计数函数。

例如:

计算男生的人数或男生中成绩>=80分的人数。

方法:

=COUNTIF(D3:D9,"男")

=COUNTIFS(D3:D9,"男",C3:C9,">=80")。

18、DATEDIF(需要计算的时间,当前日期,按照年份)函数:计算出年龄。

=DATEDIF(TEXT(MID(C3,7,8),"00-00-00"),TODAY(),"y")&"周岁"。

19、SUMPRODUCT+COUNTIF函数:对成绩进行分组排名。

=countifs($B$3:$B$9,B3,$C$9, ">"&C3)+1:计算比自己大的值的个数。

=sumproduct(($B$3:$B$12=B3)*($C$3:$C$12>C3))+1:分两步:其一、大于当下值的组合为true,小于为false,其二、两个相乘再求和即为有多少个比他大的,加1即为第几名。

说明为:https://baijiahao.baidu.com/s?id=1615486352994963545&wfr=spider&for=pc

sumproduct(array1,array2,array3, ...)函数:数组中对应行的值乘积之和。

https://jingyan.baidu.com/article/3c343ff7074afe0d36796353.html

20、SUBSTITUTE(要替换的文本,旧文本,新文本,[替换第几个]):替换函数。

替换电话号码的部分数值:

 

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

21、取整的间隔小时数:=TEXT(B2-B1,"[h]"):B1、B2为两个时间值。

22、生成随机数:=RANDBETWEEN(数字下限,数字上限)

23、提取时间中的日期值:提取日期时间中的日期值=INT(A2);要继续提取时间,只需要做个减法,就可以了。

24、提取混合内容中的姓名:=LEFT(A2,LENB(A2)-LEN(A2))

LENB函数将每个汉字(双字节字符)的字符数按2计数,LEN函数则对所有的字符都按1计数。

因此“LENB(A2)-LEN(A2)”返回的结果就是文本字符串中的汉字个数。

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

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

不区分大小写:=A2=B2

区分大小写:=EXACT(A2,B2):EXACT函数 区分大小写,但忽略格式上的差异。

26、合并带格式的单元格内容:

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

TEXT()函数:把B列的日期变成具有特定样式的字符串,然后再与A列的姓名连接

27、合并多个单元格内容:

=A2&B$1&B2

28、多条件查找:要求查询部门为生产,并且岗位为部长的姓名

 

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

总结:

1、常用的几个函数组合:https://baijiahao.baidu.com/s?id=1607346377232210818&wfr=spider&for=pc

2、方法总结为:https://jingyan.baidu.com/article/636f38bb9bfc2dd6b946104c.html

3、常用快捷键:https://mbd.baidu.com/newspage/data/landingsuper?context=%7B%22nid%22%3A%22news_9272714227277649820%22%7D&n_type=1&p_from=4

 

 

已标记关键词 清除标记
©️2020 CSDN 皮肤主题: 精致技术 设计师:CSDN官方博客 返回首页