数据分析——跟随秦路的第123篇，EXCEL基础函数、技巧、实战

excel的一些常用函数，在mysql中有很多是同名的，python也类似。

帖子用来自我学习记录，

原址：https://zhuanlan.zhihu.com/p/23345231

原文：https://zhuanlan.zhihu.com/p/23618955

原文：https://zhuanlan.zhihu.com/p/24084300

一、基础函数

一、数据清洗类

1、trim() 

可去除单元格首尾空格，且在中间保留一位空格

若要去除所有，可用ctrl+H替换，或用substitute(A1," ","")

clean()删除空行（Alt+Enter的反向）

2、concatenate()

合并单元格

3、replace()、substitute()

replace(A1,起始位置，替换几个字符，替换成什么)

4、mid、left、right

mid(字符串，起始位置，引用几个)

5、len、lenb

字符数，可搭配left等使用，lenb计算字节数

6、find、search

find(字符串，单元格，start_num默认为1)，ps即使start_num<>1,find返回值仍是从1开始计数

返回一个字符串在另一个字符串出现的起始位置，所以常与Left搭配使用，eg=LEFT(A2,FIND("@",A2,1)-1)

search一样，只不过模糊一些，不区分大小写，还可以使用通配符*(替代多个字符)和？(替代单个字符)

 二、关联匹配——(待研究)

1、lookup、vlookup

2、index、match

3、offset

offset(参考系，向下，向右，高度，宽度)

参考系可以是单元格也可以是类似B2:D4这样的区域

offset与sum搭配，可以实现自动求和

三、逻辑判断

1、if、and、or

if(and(条件1，条件2)，true的返回值，false的返回值)

2、iserr、iserror、isna、istext

四、计算统计

1、sum、sumif、sumifs

sumif(条件区域，条件，求和区域)

sumifs(求和区域，区域1，条件1，区域2，条件2)

2、sumproduct

sumproduct(array1,array2,......) 

注：array的维度必须是一样的，逗号分割的各个参数必须为数字型数据。

如果是判断的结果逻辑值，就要乘1转换为数字。如果不用逗号，直接用*号连接，就相当于乘法运算，就不必添加*1

乘积求和，还有多条件求和、多条件计数等功能

https://jingyan.baidu.com/article/3c343ff7074afe0d36796353.html

3、count、countif、countifs

4、rand、randbetween 随机抽样

5、quantile(指定区域，分位参数)、stdev 标准差

6、subtotal 分类汇总

五、时间函数

1、weekday(指定日期，参数) 

参数=1，周日算作第一天；参数=2，周一算作第一天

2、weeknum（指定日期，参数） 返回一年中的第几个星期，参数同上

3、today、now（）时间戳

4、datedif 两个日期的时间差

datedif(开始日期，结束日期，参数) 参数确定返回年还是月份等

二、技巧

1、设置 自定义下拉菜单

（数据-数据验证） 数据有效性是一种约束，针对单元格限制其输入，也就是让其只能固定几个值。下拉菜单是一种高阶应用，通过允许下拉箭头即可。 ps：选择序列形式，用英文下的逗号隔开

2、条件格式

可以新建规则，用于标识排名前几（或大于小于等）的单元格等。

3、数据工具

数据-删除重复值、分列、快速填充*

4、自定义名称

公式-定义名称，可以为一个区域定义，sum等可以直接引用该名称

5、多级分组显示

（数据-组合） 快捷键：Alt+Shift+→

当excel行数or列数太多，可以用分组达到快速切换和隐藏的目的。

6、分析工具库

分析工具库是高阶分析的利器，包含很多统计计算，检验功能等工具。Excel是默认不安装的，要安装需要加载项，在工具菜单下（不同版本安装方式会有一点小差异）。

分析工具库是统计包，规划求解是计算最优解，类似决策树。这两者的分析方法以后详细论述。

7、第三方插件

（插入-应用商店）微软的power系列很强大，要下载的

三、实战

数据分析的步骤：明确目的、搜集数据、整理数据、分析、形成报告、反馈。

1、首先，数据分析以业务为核心，所以现有目标，才有分析。而不是反过来通过数据想直接得出结论。

2、数据整理：

1）有无缺失

2）数据是否一致：有统一的命名和标准（后续可能要找出强相关的出来）

3）是否有脏数据：去重、分列提取关键等等

3、分析 多维度的去分析数据

最后

以上就是柔弱芝麻为你收集整理的数据分析——跟随秦路的第123篇，EXCEL基础函数、技巧、实战的全部内容，希望文章能够帮你解决数据分析——跟随秦路的第123篇，EXCEL基础函数、技巧、实战所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。