Excel学习(一)Excel函数篇

Reference:七周数据分析师-excel

excel-百度经验-黄小新

excel中的绝对引用和相对引用

A.清洗处理类

主要是文本、格式以及脏数据的清洗和转换。很多数据并不是直接拿来就能用的,需要经过数据分析人员的清理。数据越多,这个步骤花费的时间越长。

(1)Trim                                         百度经验:excel-trim的用法

清除掉字符串两边的空格。

MySQL有同名函数,Python有近似函数strip。

(2)Concatenate                             百度经验:excel-concatenate

=Concatenate(单元格1,单元格2……)

合并单元格中的内容,还有另一种合并方式是& 。"我"&"很"&"帅" = 我很帅。当需要合并的内容过多时,concatenate的效率快也优雅。

MySQL有近似函数concat。

(3)Replace                                      百度经验: excel-replace

=Replace(指定字符串,哪个位置开始替换,替换几个字符,替换成什么)

替换掉单元格的字符串,清洗使用较多。

MySQL中有同名函数,Python中有同名函数。

(4)Substitute                                     百度经验:substitute

和replace接近,区别是替换为全局替换,没有起始位置的概念

(5)Left/Right/Mid                          百度经验:excel-left

=Mid(指定字符串,开始位置,截取长度)

截取字符串中的字符。Left/Right(指定字符串,截取长度)。left为从左,right为从右,mid如上文示意。

MySQL中有同名函数。

(6)Len/Lenb                                      

返回字符串的长度,在len中,中文计算为一个,在lenb中,中文计算为两个。

MySQL中有同名函数,Python中有同名函数。

(7)Find                                                    百度经验:  excel-find

=Find(要查找字符,指定字符串,第几个字符)

查找某字符串出现的位置,可以指定为第几次出现,与Left/Right/Mid结合能完成简单的文本提取

MySQL中有近似函数 find_in_setPython中有同名函数。

(8)Search                                               百度经验:excel-search

和Find类似,区别是Search大小写不敏感,但支持*通配符

(9)Text                                                    百度经验:excel-text

将数值转化为指定的文本格式,可以和时间序列函数一起看


关联匹配类

在进行多表关联或者行列比对时用到的函数,越复杂的表用得越多。多说一句,良好的表习惯可以减少这类函数的使用。

(1)Lookup                                               百度经验:excel-lookup

=Lookup(查找的值,值所在的位置,返回相应位置的值)

最被忽略的函数,功能性和Vlookup一样,但是引申有数组匹配和二分法。

(2)Vlookup                                              百度经验: excel-vlookup

=Vlookup(查找的值,哪里找,找哪个位置的值,是否精准匹配)

Excel第一大难关,因为涉及的逻辑对新手较复杂,通俗的理解是查找到某个值然后黏贴过来。

(3)Index                                                     百度经验:excel-index

=Index(查找的区域,区域内第几行,区域内第几列)

和Match组合,媲美Vlookup,但是功能更强大。

(4)Match                                                   百度经验:excel-match

=Match(查找指定的值,查找所在区域,查找方式的参数)

和Lookup类似,但是可以按照指定方式查找,比如大于、小于或等于。返回值所在的位置。

(5)Row                                                     百度经验:excel-row

返回单元格所在的行

(6)Column

返回单元格所在的列

(7)Offset                                                       百度经验:excel-offset

=Offset(指定点,偏移多少行,偏移多少列,返回多少行,返回多少列)

建立坐标系,以坐标系为原点,返回距离原点的值或者区域。正数代表向下或向右,负数则相反。


逻辑运算类

数据分析中不得不用到逻辑运算,逻辑运算返回的均是布尔类型,True和False。很多复杂的数据分析会牵扯到较多的逻辑运算

(1)IF                                                                  百度经验: excel-if

经典的如果但是,在后期的Python中,也会经常用到,当然会有许多更优雅的写法。也有ifs用法,取代if(and())的写法。

MySQL中有同名函数,Python中有同名函数。

(2)And                                                               百度经验:excel-and

全部参数为True,则返回True,经常用于多条件判断。

MySQL中有同名函数,Python中有同名函数。

(3)Or                                                                  百度经验:excel-or

只要参数有一个True,则返回Ture,经常用于多条件判断。

MySQL中有同名函数,Python中有同名函数。

(4)IS系列                                                             百度经验:excel-is系列

常用判断检验,返回的都是布尔数值True和False。常用ISERR,ISERROR,ISNA,ISTEXT,可以和IF嵌套使用。


计算统计类

常用的基础计算、分析、统计函数,以描述性统计为准。具体含义在后续的统计章节再展开。

(1)Sum/Sumif/Sumifs                              

百度经验:excel-sumif       excel-sum   excel-sumifs

统计满足条件的单元格总和,SQL有中同名函数。

MySQL中有同名函数,Python中有同名函数。

(2)Sumproduct                      百度经验:excel-sumproduct

统计总和相关,如果有两列数据销量和单价,现在要求卖出增加,用sumproduct是最方便的。

MySQL中有同名函数。

(3)Count/Countif/Countifs   百度经验:excel-countif           excel-count

统计满足条件的字符串个数

MySQL中有同名函数,Python中有同名函数。

(4)Max                               百度经验:excel-max

返回数组或引用区域的最大值

MySQL中有同名函数,Python中有同名函数。

(5)Min                                百度经验:

返回数组或引用区域的最小值

MySQL中有同名函数,Python中有同名函数。

(6)Rank                            百度经验:excel-rank

排序,返回指定值在引用区域的排名,重复值同一排名。

SQL中有近似函数row_number() 。

(7)Rand/Randbetween    百度经验:excel-rand      excel-randbetween

常用随机抽样,前者返回0~1之间的随机值,后者可以指定范围。

MySQL中有同名函数。

(8)Averagea                         百度经验:excel-average和averagea区别

求平均值,也有Averageaif,Averageaifs

MySQL中有同名函数,python有近似函数mean。

区别average函数和averagea函数,averagea函数的作用是求数值的算术平均值,功能与average相同,计算规则上有少许不同。

(9)Quartile

=Quartile(指定区域,分位参数)

计算四分位数,比如1~100的数字中,25分位就是按从小到大排列,在25%位置的数字,即25。参数0代表最小值,参数4代表最大值,1~3对应25、50(中位数)、75分位

(10)Stdev                                      百度经验:excel-stdev

求标准差,统计型函数,后续数据分析再讲到

(11)Substotal                                 百度经验:excel-subtotal                     

=Substotal(引用区域,参数)

汇总型函数,将平均值、计数、最大最小、相乘、标准差、求和、方差等参数化,换言之,只要会了这个函数,上面的都可以抛弃掉了。

(12)Int/Round

取整函数,int向下取整,round按小数位取数。

round(3.1415,2) =3.14 ;

round(3.1415,1)=3.1


时间序列类

专门用于处理时间格式以及转换,时间序列在金融、财务等数据分析中占有较大比重。时机序列的处理函数比我列举了还要复杂,比如时区、分片、复杂计算等。这里只做一个简单概述。

(1)Year                             百度经验:excel-year,month,day

返回日期中的年

MySQL中有同名函数。

(2)Month

返回日期中的月

MySQL中有同名函数。

(3)Weekday                                                         百度经验:excel-weekday

=Weekday(指定时间,参数)

返回指定时间为一周中的第几天,参数为1代表从星期日开始算作第一天,参数为2代表从星期一开始算作第一天(中西方差异)。我们中国用2为参数即可。

MySQL中有同名函数。

(4)Weeknum

=Weeknum(指定时间,参数)

返回一年中的第几个星期,后面的参数类同weekday,意思是从周日算还是周一。

MySQL中有近似函数 week。

(5)Day

返回日期中的日(第几号)

MySQL中有同名函数。

(6)Date                                                               百度经验:excel_date

=Date(年,月,日)

时间转换函数,等于将year(),month(),day()合并

MySQL中有近似函数date_format。

(7)Now                                                  百度经验:excel_now

返回当前时间戳,动态函数

MySQL中有同名函数。

(8)Today

返回今天的日期,动态函数

MySQL中有同名函数。

(9)Datedif                            百度经验:excel--datedif

=Datedif(开始日期,结束日期,参数)

日期计算函数,计算两日期的差。参数决定返回的是年还是月等。

MySQL中有近似函数DateDiff。

推荐阅读更多精彩内容

  • 摘要:本文是《七周成为数据分析师》的Excel函数篇教程,如果想要了解写作初衷,可以先行阅读七周指南。温馨提示:如...
    阿里云云栖号阅读 376评论 0 20
  • 世界上的数据分析师分为两类,使用Excel的分析师,和其他分析师。 每一个数据新人的入门工具都离不开Excel。因...
    习惯研究所所长阅读 182评论 0 1
  • 清洗处理类 主要是文本、格式以及脏数据的清洗和转换。很多数据并不是直接拿来就能用的,需要经过数据分析人员的清理。数...
    FansMaker阅读 144评论 0 1
  • 当我们蓦然回首,回看那坎坷岁月,那酸甜苦辣,那情深之处,我们或许只会微微一笑。笑当年的年少无知,傻头傻脑,但一会儿...
    黄轩励阅读 118评论 2 4
  • 一,读2《月亮与六便士》,164/590。 100天读33本书,不开国际玩笑。 二,...
    欣雪儿阅读 40评论 0 0