《七周成为数据分析师》学习笔记(一)——Excel函数篇

大家好,我是丁丁。《七周成为数据分析师》是我在知乎上看到的一个回答,作者是秦路。整个回答一共包含了24篇文章,从Excel到Python,由基础知识到统计学原理,由浅入深。

我在此记录下每一章节的学习笔记,一方面是作为一个备份,另外也希望能对各位同好有所帮助。


这是第一篇《Excel函数篇》。

Excel有很多强大的函数,函数可以被我们想象成一个盒子,专门负责将输入转换成输出,不同的函数对应不同的输出。

=Vlookup(lookup_value,table_array,col_index_num,[range_lookup])

上文的Vlookup就是一个经典函数。函数中包含参数,括号里的部分都是参数。可以把参数想象成开关,不同的开关组合决定了函数的输入和输出。

=Vlookup(参数1,参数2,参数3,参数4)

应用Excel函数最重要的能力是学会搜索。


一、清洗处理类

主要对文本、格式、脏数据进行清洗和转换。

Trim

清除字符串两边的空格。

MySQL:Trim;Python:strip

Concatenate

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

合并单元格中的内容,类似&连接符,但单元格数量多时效率更快。

MySQL:concat

Replace

=Replace(被替换字符串,起始位置,替换字符数,替换字符串)

替换单元格的字符串

MySQL:Replace;Python:Replace

Substitute

全局替换,没有起始位置

Left/Right/Mid

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

截取指定字符串中的字符。

MySQL:Left/Right/Mid

Len/Lenb

返回字符串长度。Len中,中文计算为一个字符长度;Lenb中,中文计算为两个字符长度。

MySQL:Len/Lenb;Python:Len/Lenb

Find

=Find(指定字符串,查找范围,起始位置)

返回指定字符串出现的位置。

MySQL:find_in_set;Python:Find

类似Find,大小写不敏感,且支持*通配符。

Text

将数值转化为制定的文本格式。


二、关联匹配类

进行多表关联或行列比对时用到。

Lookup

=Lookup(指定字符,查找区域,比对区域)

在查找区域中查找指定字符,返回比对区域中相同位置的字符。

Vlookup

=Vlookup(指定字符,查找区域,比对第几列,是否精确匹配)

在查找区域内查找指定字符,返回比对列中相同位置的字符。

Index

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

返回制定单元格中的值。

Match

=Match(指定字符,查找区域,查找方式)

与Lookup类似,可按指定方式查找,返回字符所在位置。

Row

返回单元格所在行。

Column

返回单元格所在列。

Offset

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

建立坐标系,已指定点作为参照,通过偏移量返回新的值。


三、逻辑运算类

逻辑运算返回的均是布尔类型,True和False。

IF

经典的如果条件函数,也有用ifs取代if(and())的用法。

MySQL:IF;Python:IF

And

与函数,全部满足为True,否则返回False,常用于多条件判断。

MySQL:And;Python:And

Or

或函数,满足其一即为True,全不满足返回False,常用于多条件判断。

MySQL:Or;Python:Or

IS系列

判断检验函数,常用ISERR、ISERROR、ISNA、ISTEXT,可与IF嵌套使用。


四、计算统计类

基础计算、分析、统计函数。

Sum/Sumif/Sumifs

统计满足条件的单元格总和。

MySQL:Sum/Sumif/Sumifs;Python:Sum/Sumif/Sumifs

Sumproduct

Sumproduct(数组1,数组2,数组3……)

返回相应数组或区域乘积的和。

MySQL:Sumproduct

Count/Countif/Countifs

统计区域内满足条件的字符串个数。

MySQL:Count/Countif/Countifs

Max

返回数组或区域内的最大值。

MySQL:Max;Python:Max

Min

返回数组或区域内的最小值。

MySQL:Min;Python:Min

Rank

返回制定值在区域内的排名,重复值同一排名。

MySQL:row_number()

Rand/Randbetween

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

MySQL:Rand/Randbetween

Averagea/Averageaif/Averageaifs

返回符合条件范围的平均值。

MySQL:Averagea/Averageaif/Averageaifs;Python:mean

Quartile

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

计算指定区域的四分位数,参数为0/1/2/3/4,分别代表最小值/25%/50%/75%/最大值。

Stdev

求标准差。

Substotal

=Substotal(指定区域,参数)

汇总函数,包含平均值、计数、极值、乘积、标准差、求和、方差等。

Int/Round

取整函数,Int向下取整,Round按小数位取整。


五、时间序列类

用于处理时间格式以及转换。

Year

返回日期中的年。

MySQL:Year

Month

返回日期中的月。

MySQL:Month

Weekday

=Weekday(指定时间,参数)

返回指定时间在一周中的天数,参数为1表示从星期日开始,参数为2表示从星期一开始。

MySQL:Weekday

Weeknum

=Weeknum(指定时间,参数)

返回指定时间是一年中的第几个星期,参数类同Weekday。

MySQL:Week

Day

返回日期中的日。

MySQL:Day

Date

=Date(年,月,日)

时间转换函数。

MySQL:date_format

Now

返回当前时间戳。

MySQL:Now

Today

返回今天的日期。

MySQL:Today

Datediff

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

计算开始日期与结束日期的差。参数决定返回的是年/月等。

MySQL:DateDiff

------ 本文结束 ------
0%