效率工具:数据分析中常见的Excel函数都在这里了
数据分析:学习Excel的数据分析工具 #生活技巧# #工作学习技巧# #专业技能培训#
题记
最近,不知怎么的突然迷上了Excel,虽说用云笔记整理信息更方便,但是印象和有道,说实话,插入表格功能都很鸡肋,在真正提炼结构化知识时,还是不如Excel来得更高效。
加上工作中也会常常用Excel做数据分析,便随手把记录下来的常用函数整理了一下,方便以后随手查阅。
Excel有很多强大的函数,不过大多数程序猿一般看不上,认为那都是别人才会干的事。其实不然,用好了能大大提高个工作和生活效率。
这篇文章主要介绍各种函数的用途。实战以后有空再讲解。
在Excel中,一般公式长这样:
=Vlookup( lookup_value ,table_array,col_index_num,[range_lookup] )1
Vlookup是一个经典函数。函数中包含参数,括号里的部分都是参数。换成伪代码就是:
=Vlookup( 参数1,参数2,参数3,参数4 )1
至于为什么非要这样,原理就不必深究了。按理,都不需要特别记忆怎么使用函数?有哪些函数?因为绝大部分函数网上已经有相应的解释,图文结合,非常详尽。
不过,每次都需要联网搜索,还要耗费注意力筛选哪篇文章讲得更好,长期下来有点浪费时间。所以,更好的办法还是应该自己根据的使用情况总结成一个可用工具类(工具思维)。
1. 清洗处理类
主要是文本、格式以及脏数据的清洗和转换。很多数据并不是直接拿来就能用的,需要经过数据分析的清理。数据越多,这个步骤花费的时间越长。
Trim=Trim(单元格)
清除掉字符串两边的空格。
MySQL有同名函数,Python有近似函数strip。
Concatenate=Concatenate(单元格1,单元格2……)
合并单元格中的内容,还有另一种合并方式是& 。”我”&”很”&”帅” = 我很帅。当需要合并的内容过多时,concatenate的效率快也优雅。
MySQL有近似函数concat。
Replace=Replace(指定字符串,哪个位置开始替换,替换几个字符,替换成什么)
替换掉单元格的字符串,清洗使用较多。
MySQL中有同名函数,Python中有同名函数。如下图示例:
Substitute=Substitute(指定字符串,要替换的字符串,新的字符串 , [指定事件])
和replace接近,区别是替换为全局替换,没有起始位置的概念
Left/Right/Mid=Mid(指定字符串,开始位置,截取长度)
截取字符串中的字符。Left/Right(指定字符串,截取长度)。left为从左,right为从右,mid如上文示意。
MySQL中有同名函数。
Len/Lenb返回字符串的长度,在len中,中文计算为一个,在lenb中,中文计算为两个。
MySQL中有同名函数,Python中有同名函数。
Find=Find(要查找字符,指定字符串,第几个字符)
查找某字符串出现的位置,可以指定为第几次出现,与Left/Right/Mid结合能完成简单的文本提取
MySQL中有近似函数 find_in_set,Python中有同名函数。
Search和Find类似,区别是Search大小写不敏感,但支持*通配符
Text将数值转化为指定的文本格式,可以和时间序列函数一起看
2、关联匹配类
在进行多表关联或者行列比对时用到的函数,越复杂的表用得越多。多说一句,良好的表习惯可以减少这类函数的使用。
Lookup=Lookup(查找的值,值所在的位置,返回相应位置的值)
最被忽略的函数,功能性和Vlookup一样,但是引申有数组匹配和二分法。
Vlookup=Vlookup(查找的值,哪里找,找哪个位置的值,是否精准匹配)
Excel第一大难关,因为涉及的逻辑对新手较复杂,通俗的理解是查找到某个值然后黏贴过来。
Index=Index(查找的区域,区域内第几行,区域内第几列)
和Match组合,媲美Vlookup,但是功能更强大。
Match=Match(查找指定的值,查找所在区域,查找方式的参数)
和Lookup类似,但是可以按照指定方式查找,比如大于、小于或等于。返回值所在的位置。
Row返回单元格所在的行
Column返回单元格所在的列
Offset=Offset(指定点,偏移多少行,偏移多少列,返回多少行,返回多少列)
建立坐标系,以坐标系为原点,返回距离原点的值或者区域。正数代表向下或向左,负数则相反。
3、逻辑运算类
数据分析中不得不用到逻辑运算,逻辑运算返回的均是布尔类型,True和False。很多复杂的数据分析会牵扯到较多的逻辑运算
IF经典的如果但是,在后期的Python中,也会经常用到,当然会有许多更优雅的写法。也有ifs用法,取代if(and())的写法。
MySQL中有同名函数,Python中有同名函数。
全部参数为True,则返回True,经常用于多条件判断。
MySQL中有同名函数,Python中有同名函数。
只要参数有一个True,则返回Ture,经常用于多条件判断。
MySQL中有同名函数,Python中有同名函数。
常用判断检验,返回的都是布尔数值True和False。常用ISERR,ISERROR,ISNA,ISTEXT,可以和IF嵌套使用。
4.计算统计类
常用的基础计算、分析、统计函数,以描述性统计为准。
Sum/Sumif/Sumifs统计满足条件的单元格总和,SQL有中同名函数。
MySQL中有同名函数,Python中有同名函数。
统计总和相关,如果有两列数据销量和单价,现在要求卖出增加,用sumproduct是最方便的。
MySQL中有同名函数。
统计满足条件的字符串个数
MySQL中有同名函数,Python中有同名函数。
返回数组或引用区域的最大值
MySQL中有同名函数,Python中有同名函数。
返回数组或引用区域的最小值
MySQL中有同名函数,Python中有同名函数。
排序,返回指定值在引用区域的排名,重复值同一排名。
SQL中有近似函数row_number() 。
常用随机抽样,前者返回0~1之间的随机值,后者可以指定范围。
MySQL中有同名函数。
求平均值,也有Averageaif,Averageaifs
MySQL中有同名函数,python有近似函数mean。
=Quartile(指定区域,分位参数)
计算四分位数,比如1~100的数字中,25分位就是按从小到大排列,在25%位置的数字,即25。参数0代表最小值,参数4代表最大值,1~3对应25、50(中位数)、75分位
【图片】
Stdev求标准差,统计型函数,后续数据分析再讲到
Substotal=Substotal(引用区域,参数)
汇总型函数,将平均值、计数、最大最小、相乘、标准差、求和、方差等参数化,换言之,只要会了这个函数,上面的都可以抛弃掉了。
Int/Round取整函数,int向下取整,round按小数位取数。比如:
round(3.1415,2) =3.14 ; round(3.1415,1)=3.112
5、时间序列类
专门用于处理时间格式以及转换,时间序列在金融、财务等数据分析中占有较大比重。时机序列的处理函数比我列举了还要复杂,比如时区、分片、复杂计算等。这里只做一个简单概述。
Year返回日期中的年
MySQL中有同名函数。
返回日期中的月
MySQL中有同名函数。
=Weekday(指定时间,参数)
返回指定时间为一周中的第几天,参数为1代表从星期日开始算作第一天,参数为2代表从星期一开始算作第一天(中西方差异)。我们中国用2为参数即可。
MySQL中有同名函数。
=Weeknum(指定时间,参数)
返回一年中的第几个星期,后面的参数类同weekday,意思是从周日算还是周一。
MySQL中有近似函数 week。
返回日期中的日(第几号)
MySQL中有同名函数。
=Date(年,月,日)
时间转换函数,等于将year(),month(),day()合并
MySQL中有近似函数 date_format。
返回当前时间戳,动态函数
MySQL中有同名函数。
返回今天的日期,动态函数
MySQL中有同名函数。
=Datedif(开始日期,结束日期,参数)
日期计算函数,计算两日期的差。参数决定返回的是年还是月等。
MySQL中有近似函数 DateDiff。
小结
到此整理汇总就完了,算是做了一次复习。
部分用法参考网络搜集信息,如有错误,欢迎大家补充指正。
网址:效率工具:数据分析中常见的Excel函数都在这里了 https://www.yuejiaxmz.com/news/view/147904
相关内容
【数据分析】15组Excel函数,解决数据分析中80%的难题!EXCEL函数及数据分析技巧整理备用
Excel中SUMIF函数如何使用
掌握excel合并两列内容的简单方法,提升数据处理效率
Excel高效筛选户数:轻松掌握数据整理技巧(excel 筛选户数)
如何在电脑上下载Excel表格提升工作效率和数据管理能力
如何给Excel表格排序提高数据处理效率的实用技巧
快速辨别Excel中两列数据是否相同
在Excel中,A1和B1中分别有数据10和20,在C1中输入公式=A1&
管理经济学:需求与需求函数分析.ppt