最近接触了很多数据处理和分析的工作,Excel、SPSS、SQL都慢慢熟练起来,后两者还没到复盘的时候,Excel我早就想复盘了。
今天要复盘的是近一年我使用最多的函数,如果吸收完这一篇,可以抵一年的经验。
为什么这么说呢?日常工具最重要的是使用场景,需要用时才会去找使用方法,然后在反复使用过程中逐渐熟练。我说的这个一年经验,就是避免你瞎找、不系统的找、不全面的吸收,等等……
下面直接进入正题,先看我今天要分享的函数,如下图:
![5b75ba4f9e1759b61ff92477b463cfaf.png](https://i-blog.csdnimg.cn/blog_migrate/f1ff361eacfc9db5f3a9e88bb62fca16.jpeg)
接下来按照顺序一个一个分享:
1、平均值系列
案例图:
![23a1ddaf2a173d5bca9c5cbbc4722d52.png](https://i-blog.csdnimg.cn/blog_migrate/e5cb6e66f1cb2c5ceedc546feabd5916.jpeg)
AVERAGE()
说明:无条件求平均值
需求:求MATH课程的平均分
公式:AVERAGE(C2:C9)
结果:55
AVERAGEIF()
说明:单条件求平均值
需求:求GRAGE = 3的MATH课程的平均分
公式:AVERAGEIF(B2:B9,3,C2:C9)
结果:75
AVERAGEIFS()
说明:多条件求平均值
需求:求GRAGE = 3,EGLISH>=90 的MATH课程的平均分
公式:AVERAGEIFS(C2:C9,B2:B9,3,D2:D9,">=90")
结果:80
需要注意的点:
AVERAGEIF(),求值范围在后,条件在前。
AVERAGEIFS(),求值范围在前,条件在后。
2、求和系列
案例图:
![23a1ddaf2a173d5bca9c5cbbc4722d52.png](https://i-blog.csdnimg.cn/blog_migrate/e5cb6e66f1cb2c5ceedc546feabd5916.jpeg)
SUM()
说明:无条件求和
需求:求MATH课程的和
公式:SUM(C2:C9)
结果:440
SUMPRODUCT()
说明:对乘积求和
需求:求MATH和EGLISH课程乘积的和
公式:SUMPRODUCT(C2:C9,D2:D9)
结果:25600
SUMIF()
说明:单条件求和
需求:求GRAGE = 3的MATH课程的和
公式:SUMIF(B2:B9,3,C2:C9)
结果:300
SUMIFS()
说明:多条件求和
需求:求GRAGE = 3,EGLISH>=90 的MATH课程的和
公式:SUMIFS(C2:C9,B2:B9,"=3",D2:D9,">=90")
结果:80
小贴士:
求和系列和求平均值系列,函数使用方式相似。
3、统计个数系列
案例图:
![23a1ddaf2a173d5bca9c5cbbc4722d52.png](https://i-blog.csdnimg.cn/blog_migrate/e5cb6e66f1cb2c5ceedc546feabd5916.jpeg)
COUNT()
说明:无条件统计个数
需求:求总人数
公式:COUNT(B2:B9)
结果:8
需要注意的点:COUNT()只统计数字
COUNTIF()
说明:单条件统计个数
需求:求MATH>=80的个数
公式:COUNTIF(C2:C9,">=80")
结果:2
COUNTIFS()
说明:多条件统计个数
需求:求GRAGE = 3,MATH>=70 的个数
公式:COUNTIFS(B2:B9,3,C2:C9,">=70")
结果:3
4、匹配系列
案例图:
![23a1ddaf2a173d5bca9c5cbbc4722d52.png](https://i-blog.csdnimg.cn/blog_migrate/e5cb6e66f1cb2c5ceedc546feabd5916.jpeg)
VLOOKUP()
说明:按条件搜寻区域,并匹配目标结果
需求:找出NAME = zhao 的CHINESE成绩
公式:VLOOKUP(A2,G2:H9,2,0)
结果:20
小贴士:实际使用过程中,VLOOKUP()可能匹配不到结果,在表格中展示出"#N/A",可以与IFERROR()搭配使用
FIND()
说明:查找目标值在字符串中的位置
需求:求“WEwe”中“e”的位置
公式:FIND("e",WEwe)
结果:4
注:FIND()函数是精准查找,区分大小写,同功能的SEARCH()函数,不区分大小写
5、“用户比较”系列
案例图:
![23a1ddaf2a173d5bca9c5cbbc4722d52.png](https://i-blog.csdnimg.cn/blog_migrate/e5cb6e66f1cb2c5ceedc546feabd5916.jpeg)
MIN()
说明:求最小值
需求:求MATH课程的最小值
公式:MIN(C2:C9)
结果:20
AVERAGE()
说明:无条件求平均值
需求:求MATH课程的平均分
公式:AVERAGE(C2:C9)
结果:55
MEDIAN()
说明:求中值
需求:求MATH课程的中值
公式:MEDIAN(C2:C9)
结果:55
MAX()
说明:求最大值
需求:求MATH课程的最大值
公式:MAX(C2:C9)
结果:90
小贴士:
为什么说这几个函数是“用于比较”系列呢?一般在对比多组数据之间的优劣时,我们需要找一个参考标准(AVERAGE、MEDIAN),高于标准我们会说还不错,低于标准我们会说还差点意思。极端值有时候我们可以拿出来“怼人”(min)或者“做标榜”(max)。
6、判断系列
案例图:
![23a1ddaf2a173d5bca9c5cbbc4722d52.png](https://i-blog.csdnimg.cn/blog_migrate/e5cb6e66f1cb2c5ceedc546feabd5916.jpeg)
IF()
说明:判断是否符合目标条件,返回TRUE、FALSE
需求:若MATH>=80 和 EGLISH>=80为“优秀”,那么“zheng”优秀么?
公式:IF(AND(C8>=80,D8>=80),"优秀","差点意思")
结果:优秀
ISNUMBER()
说明:判断是否为数字,返回TRUE、FALSE
需求:判断A9单元格是否为数字?
公式:ISNUMBER(A9)
结果:FALSE
小妙用:ISNUMBER()结合FIND()函数,可以起到简单搜索的作用。
【
需求:判断字符“写字楼”,是否在字符串“蒸包机写字楼社区连锁便利店”中?
公式:ISNUMBER(FIND("写字楼","蒸包机写字楼社区连锁便利店"))
结果:TRUE
其他:为了方便之后计算,可以转成0、1数值:ISNUMBER(FIND("写字楼","蒸包机写字楼社区连锁便利店")) +0
之前写CRM的文章中提到“立地数据”,需要基于立地数据,分析判断一个便利店适合售卖的商品是什么?
所以,我经常会把所有立地数据字段合并一条长字符串,然后从长字符串中检索是否包含某个字段,能极大的简化数据处理过程(如最近在研究的相关性推荐,在没有系统化之前,我需要利用Excel处理数据,从立地数据中挑选影响因素、确定权重系数等等)。
】
7、其他系列
ROUND()
说明:按指定条件保留小数位数
需求:对3.1234保留两位小数
公式:ROUND(3.1234,2)
结果:3.12
小贴士:ROUND()函数我经常用来展示ROI,假设投入3,产出17,ROI="1:"&ROUND(17/3,2) [ 结果:1:5.67 ],这样写函数会让整个展现形式都特别好看。
IFERROR()
说明:计算结果为错误值时,返回指定值,否则返回计算结果
需求:回到上文中VLOOKUP()函数,如果遇到匹配不到的值,会显示"#N/A"错误值,如何消去呢?
公式:IFERROR(VLOOKUP(A2,G2:H9,2,0),”错误“)[这里我们假设VLOOKUP()函数没有匹配到数据]
结果:错误
8、时间系列
NOW()
说明:时间函数,精确到秒
需求:对外出具数据报表时,需要展示截止最后一刻的时间(精确到秒)
公式:NOW()
结果:2019-6-15 5:20
TODAY()
说明:时间函数,精确到秒
需求:对外出具数据报表时,需要展示截止最后一刻的时间(精确到日)
公式:TODAY()
结果:2019-6-15
小贴士:TODAY()函数使用场景会更多一些,如统计最近七天的订单,就可以取时间范围在 [ TODAY()-6,TODAY() ] 的订单。
以上,就是我这一年的Excel函数经验,希望对大家有所帮助。
如果需要我一个一个按照业务场景细讲,读者们可以在下方留言,我也期待有时间给大家简单分享一下实战Excel。
如果喜欢我的文章,请关注我的公众号:倔牛的人生
也请多多分享、点赞,我对数据特别敏感,哈哈哈哈哈哈,谢谢大家~
想全面学习也可以看下这本书