Excel常用函数笔记

常用清洗处理类函数
1、Trim 清除掉字符串两边的空格，将格式转化为文本，如需转为数值则=VALUE(TRIM(单元格))。

substitute对指定的字符串进行替换，可去掉所有空格，=substitute(text,old_text,new_text,[instance_num])=substitute（需要替换的文本，旧文本，新文本，第N个旧文本）
2、Concatenate，=Concatenate(单元格1，单元格2……)合并单元格中的内容，也可以用&&&。

3、Replace=Replace（指定字符串，哪个位置开始替换，替换几个字符，替换成什么）替换掉单元格的字符串，清洗使用较多。

如新文本为文本格式，需加“”且在英文状态下输入。

4、Left／Right／Mid
=Mid(指定字符串，开始位置，截取长度)
5、Len／Lenb
返回字符串的长度，在len中，中文计算为一个，在lenb中，中文计算为两个。

Find=Find（要查找字符，指定字符串，第几个字符）
查找某字符串出现的位置，可以指定为第几次出现，与Left／Right／Mid结合能完成简单的文本提取
6、Search和Find类似，区别是Search大小写不敏感，但支持＊通配符
7、Lookup
=Lookup（查找的值，值所在的位置，返回相应位置的值）
最被忽略的函数，功能性和Vlookup一样，但是引申有数组匹配和二分法。

8、Index＝Index（查找的区域，区域内第几行，区域内第几列）和Match组
合，媲美Vlookup，但是功能更强大。

Match＝Match（查找指定的值，查找所在区域，查找方式的参数）
和Lookup类似，但是可以按照指定方式查找，比如大于、小于或等于。

返回值所在的位置。

9、Row返回单元格所在的行；Column返回单元格所在的列；
每个季度合计行的余数都为1，利用这个特性进行跨行求和。

输入公式=SUM(IF(MOD(ROW(B2:B17),4)=1,B2:B17))
10、Offset＝Offset（指定点，偏移多少行，偏移多少列，返回多少行，返回多少列）建立坐标系，以坐标系为原点，返回距离原点的值或者区域。

正数代表向下或向右，负数则相反。

逻辑运算类
1、IF、And、Or、IS系列
计算统计类
1、Sum／Sumif／Sumifs，统计满足条件的单元格总和。

2、Sumproduct统计总和相关，=sumproduct（A2:A8,B2:B8）可转化为
=sumproduct（数组1，数组2）=sumproduct（｛1；2；3；4；5；6；7｝，｛1；2；3；4；5；6；7｝）=1*1+2*2+3*3+4*4+5*5+6*6+7*7=140。

3、Count／Countif／Countifs统计满足条件的字符串个数
4、Max返回数组或引用区域的最大值；Min返回数组或引用区域的最小值；Rank排序，返回指定值在引用区域的排名，重复值同一排名。

5、Rand／Randbetween常用随机抽样，前者返回0~1之间的随机值，后者可以指定范围。

6、Averagea求平均值，也有Averageaif，Averageaifs
7、Quartile=Quartile（指定区域，分位参数）
计算四分位数，比如1~100的数字中，25分位就是按从小到大排列，在25%位置的数字，即25。

参数0代表最小值，参数4代表最大值，1~3对应25、50（中位数）、75分位
8、Substotal=Substotal（引用区域，参数）
汇总型函数，将平均值、计数、最大最小、相乘、标准差、求和、方差等参数化。

9、Int／Round
取整函数，int向下取整，round按小数位取数。

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

时机序列的处理函数比我列举了还要复杂，比如时区、分片、复杂计算等。

1、Year返回日期中的年；Month返回日期中的月；Weekday=Weekday(指定时间，参数)返回指定时间为一周中的第几天，参数为1代表从星期日开始算作第一天，参数为2代表从星期一开始算作第一天（中西方差异）。

我们中国用2为参数即可。

2、Weeknum=Weeknum(指定时间，参数)返回一年中的第几个星期，后面的参数类同weekday，意思是从周日算还是周一。

3、Day返回日期中的日（第几号）；Date=Date（年，月，日）时间转换函数，等于将year()，month()，day()合并；
Now返回当前时间戳，动态函数；Today返回今天的日期，动态函数Datedif=Datedif（开始日期，结束日期，参数）日期计算函数，计算两日期的
差。

参数决定返回的是年还是月等。

4、timevalue函数把时间转化为0到0.999999999的小数数值，即0:00:00 (12:00:00 AM) 到23:59:59 (11:59:59 PM) 之间的时间。

value函数可以将表示数字的文本字符串转换成可运算的数字。