数据分析正式篇之第一节

数据清洗类关联匹配类逻辑运算类计算统计类时间序列类 欢迎来到煊煊周末小课堂！蹭了非常善良的热心学长的网课，五次感谢！让我们一起祝福他飞黄腾达！ 除了上帝，任何人都必须用数据说话------爱德华兹·戴明 网课终于到了正式篇，兜兜转转又回到了Excel，以下是Excel的五大类函数…

数据清洗类

trim：去除单元格两边的空格，不去中间（这个感觉也可以用替换）

格式：=trim(text)

concatenate：连接不同单元格的内容

格式：=concatenate（A1，B1，C1）

如图： 当然也可以自定义用什么连接

比如：=concatenate（A1，"年"，B1，"月"，C1，"日"）

&：这个符号也能起到连接作用了，和上面的concatenate差不多 比如键入=A1&B1&C1和=A1&"年"&B1&"月"&C1&"日"就可以达到和上面一样的效果

left，right，mid：用来提取字符串左边，右边，中间的字符串。

格式：=mid(text,start_num,num_chars) =left(text,num_chars)

比如：=mid(A2,7,8)代表将A2格内的内容从第7个字符开始提取，提取8个 =left(A2,4)代表将A2格内的内容从左边提取提取4个 replace，substitute：替换字符串中的连续几个字符或某个字符

格式：=replace(old_text,start_num,num_chars,new_text) =substitute(text,old_text,new_text,instance_num)

例如：将手机号的后四位隐藏 用substitute和上面的right函数结合达成这个效果：

需要注意的是：对于substitute来说，如果old_text这个参数是自己写的话 例如“a”，那么单元格内的所有a都会被替换，这是replace做不到的.

len，lenb：计算字符串长度，区别就是后者一个中文字符长度为2，其他没区别

关联匹配类

vlookup:纵向查找

格式：=vlookup（lookup_value,table_array,col_index_num,range_lookup） 第二个参数是在哪一个表查，第三个参数是查表里的哪个列的列号 第四个就是是否近似匹配。

举个例子，有一个商品信息表如下：

现在我要在vlookup查询表中查询上辣条的价格

属性也是一个道理，在A2那里加上绝对引用，然后下拉就可以填充所有行了。 使用column()获取列号的函数替换上面函数中的2，就可以不用单个写获取第几列, 只需要向右拉即可。

hlookup：横向查找，和vlookup一个格式，懒得写了

index：根据位置返回单元格的值 match：根据单元格的值返回位置

格式：=index(array,row_num,column_num) =match(lookup_value,lookup_array,match_type) 这里提到这两个函数主要是这个两个结合可以替代vlookup函数，因为vlookup函数是 纵向查找，但是如果查找的列在它左边就会找不到，所以会有点限制，这个时候 用index+match会好一点。

rank：返回一列数字的数字排位，排位是相对于列表中的其他值的大小

格式：=rank(number,ref,order)

如上图，就把第一列的值排名了，这里说一点就是如果值一样，那么排名也是一样的 比如排名1,2,3,3,5,6

逻辑运算类

if，and，or：

格式：if(条件，正确的话，错误的话） or（条件1，条件2） and（条件1，条件2）

计算统计类

count，countif，countifs：计数

格式：=count(A:A) =countif(B:B,"杭州") =countifs(B:B,"杭州",C:C,"会计")

如图： sum,sunif,sumifs:求和

格式：=sum(A:A) =sumif(B:B,"杭州",A:A) =sumifs(A:A,B:B,"杭州",C:C,"会计") sumif里面A:A是求和区域，前面的是条件 sumifs里面A:A是求和区域，后面的是条件，记得区分哦

如图： average,averageif,averageifs:求平均值，用法和上面的sum几乎一模一样，这里不写了 quartile：返回一组数据的四分位点

格式：=quartile.inc（A:A,0/1/2/3/4） 0是最小值，1是第一个四分位点 2是第二个四分位点，3是第三个四分位点，4是最大值

substotal：万能，一般的计算公式里面都有，下面放个图看看

这么多，自己选就行了

时间序列类

year，month，day：返回对应于某个日期的年，月，日

格式：=year（A1） =month（A1） =day（A1）

就这样，很简单的 today（）是返回当前日期，now（）是返回日期时间 除了这几个，还可以求两个日期之间的间隔：

相差年数：datedif(第一个日期，第二个日期，"y") 相差月数：datedif(第一个日期，第二个日期，"m") 相差日数：datedif(第一个日期，第二个日期，"d")

上面的datedif（）的第三个参数除了y,m,d以外还有其他的参数，可以点击F1查看帮助文档噢

没了,下周见…