Excel函数大全:工作中最常用的函数汇总 |
您所在的位置:网站首页 › excel数据函数公式大全 › Excel函数大全:工作中最常用的函数汇总 |
最近接触了很多数据处理和分析的工作,Excel、SPSS、SQL都慢慢熟练起来,后两者还没到复盘的时候,Excel我早就想复盘了。 今天要复盘的是近一年我使用最多的函数,如果吸收完这一篇,可以抵一年的经验。 为什么这么说呢?日常工具最重要的是使用场景,需要用时才会去找使用方法,然后在反复使用过程中逐渐熟练。我说的这个一年经验,就是避免你瞎找、不系统的找、不全面的吸收,等等…… 下面直接进入正题,先看我今天要分享的函数,如下图: 接下来按照顺序一个一个分享: 1、平均值系列 案例图: 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、求和系列 案例图: 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、统计个数系列 案例图: 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、匹配系列 案例图: 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、“用户比较”系列 案例图: 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、判断系列 案例图: 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。 如果喜欢我的文章,请关注我的公众号:倔牛的人生 也请多多分享、点赞,我对数据特别敏感,哈哈哈哈哈哈,谢谢大家~ 想全面学习也可以看下这本书 |
今日新闻 |
推荐新闻 |
CopyRight 2018-2019 办公设备维修网 版权所有 豫ICP备15022753号-3 |