excel函数学习（一）

# 学习记录

　　此文参考知乎专栏运营大湿兄的文章，对其进行整理归纳，虽然原文作者已经写得很好了，但是学习知识就得经过自己思考，再输出才能最大化的吸收知识，所以此篇文章即是对自己将要学习的excel函数的总结即思考，也是不断督促自己前进的动力。
　　excel工具在数据分析领域的重要应用之一便是如何在10万级别的数据量中快速获取我们想要的数据信息，并将其形象的展示出来，这就涉及到对数据的搜索查找。这也是我们利用EXCEL工具所需掌握的最重要的能力之一（或许没有之一，获取数据后的展示可以借助其他专用工具）。本次文章结构脉络如下：

excel五类函数

• 清洗处理类
• 关联匹配类
• 逻辑运算类
• 计算统计类
• 时间序列类

---

数据清洗处理类

• 主要是文本、格式以及脏数据的清洗和转换。很多数据并不是直接拿来就能用的，需要经过数据分析人员的清理。
• 常用函数总结归纳如下表，为方便比较记忆，将MySQL与Python中相同功能函数作为对比放入表中。

function name	Function	in MySQL	in Python
Trim	清除掉字符串两边的空格	~	strip
CONCATENATE(text1, text2,..)	字符串连接或合并函数	concat	~
Replace(old_text,start_num,num_chars，new_text)	replace（要替换的字符串，开始位置，替换个数，新的文本）	同	同
substitute(text,old_text,new_text,[instance_num])	substitute（需要替换的文本，旧文本，新文本，第N个旧文本）	~	~
Left／Right／Mid	Mid(指定字符串，开始位置，截取长度)	同	同
Len／Lenb	Len返回字符串中字符个数，Lenb返回字符串中字节个数，汉字与全角标点为2个字节	同	同
find(find_text,within_text,start_num)	Find（要查找的文本，文本所在的单元格，从第几个字符开始查找[可选，省略默认为1，从第一个开始查找]）	find_in_set	同
search	用法基本等同find，注意search函数大小写不敏感，支持通配符“*”	~	~
text(value,format_text）	将数值转化为设置单元格格式中的数字——格式类型	~	~

关联匹配类

• 进行多表关联或者行列比对时用到的函数，越复杂的表用得越多。
• 函数归类整理如下表。

function name	Function	in MySQL	in Python
LOOKUP	查找（目标条件，待查条件区域，对应结果区域）分为精确查找和非精确查找（返回最接近单元格内容）注意①要求查询条件按照升序排列②查询的条件可以高于查询条件列的最大值，不能低于	~	~
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])	VLOOKUP(查找值，查找范围，查找列数，精确匹配或者近似匹配）基本都会使用精确匹配，用法不懂可参看https://jingyan.baidu.com/article/73c3ce28db4da4e50243d95c.html	~	~
index(array,row_num,column_num)	①Index（查找的区域，区域内第几行，区域内第几列）连续区域查找②Index（（查找的区域1，查找的区域2，…）查找的区域，区域内第几行，区域内第几列，第几个区域）非连续区域查找	~	~
MATCH（lookup_value,lookuparray,match-type）	Match（查找指定的值，查找所在区域，查找方式的参数）查找方式参数默认为1，可为0或-1，返回所匹配位置	~	~
Row	返回单元格所在行	~	~
Column	返回单元格所在列	~	~
OFFSET(reference,rows,cols,height,width)	Offset（指定点，偏移多少行，偏移多少列，返回多少行，返回多少列）以坐标系为原点，返回距离原点的值或者区域。正数代表向下或向右，负数则相反	~	~

逻辑运算类

• 数据分析中不得不用到逻辑运算，逻辑运算返回的均是布尔类型，True和False

function name	Function	in MySQL	in Python
IF	IF(条件判断, 结果为真返回值, 结果为假返回值)	同	同
And	AND(logical1,logical2, …)	同	同
Or	Or(logical1,logical2, …)	同	同
IS	判断检验，返回的都是布尔数值True和False，常用ISERR（除去空值），ISERROR，ISNA，ISTEXT	同	同

计算统计类

• 常用的基础计算、分析、统计函数

function name	Function	in MySQL	in Python
Sum／Sumif／Sumifs	统计满足条件的单元格总和	同	同
SUMPRODUCT（array1,array2,array3, …）	①逗号分割的各个参数必须为数字型数据；②判断的结果逻辑值，就要乘1转换为数字；③不用逗号，直接用*号连接，就相当于乘法运算，就不必添加*1	同	~
Count／Countif／Countifs	统计满足条件的字符串个数，Countif（条件区域，条件），返回满足条件的计数	同	同
Max	返回数组或引用区域的最大值	同	同
Min	返回数组或引用区域的最小值	同	同
Rank	排序，返回指定值在引用区域的排名，重复值同一排名	row_number	~
Rand／Randbetween	常用随机抽样，前者返回0~1之间的随机值，后者可以指定范围,RANDBETWEEN（bottom,top），返回bottom和top之间（包含）的一个随机整数	同	~
Averagea	求平均值，可对行，列，或区域求平均值	同	mean
Quartile	Quartile（指定区域，分位参数）	~	~
Stdev	求标准差	~	~
Int／Round	取整函数，int向下取整，round按小数位取数，round(3.1415,2) =3.14	~	~

时间序列类

• 专门用于处理时间格式以及转换，时间序列在金融、财务等数据分析中占有较大比重

function name	Function	in MySQL	in Python
Weekday	Weekday(指定时间，参数)，返回指定时间为一周中的第几天，参数为1代表从星期日开始算作第一天，参数为2代表从星期一开始算作第一天	同	~
Weeknum	Weeknum(指定时间，参数)，返回一年中的第几个星期	week	~
Date	Date（年，月，日）,时间转换函数，等于将year()，month()，day()合并	~	~
Now	返回当前时间戳，动态函数	同	~
Today	返回今天的日期，动态函数	同	~
Datedif	Datedif（开始日期，结束日期，参数），日期计算函数，计算两日期的差。参数决定返回的是年还是月	DateDiff	~

总结

　　今天是第一天，万事开头难，中间难，结尾也难，不过有一个开始，就好好做下去吧，前路艰辛，自娱自乐，与己共勉。