数据分析 - Excel 函数, 技巧, 分析工具

数据分析工具使用场景

函数

分类

文本清洗函数

FIND - 查找

find 函数查询指定位置的某字符的的位置

快捷键 : 选中后双击右下角的 + 可以快速生成当前一列结果

FIND("查询字符", 查询位置)

LEFT - 左截取

left 截取指定位置左侧的指定位数字符

LEFT(截取位置, 截取数量)

 

不想要 k 可以减一前移去掉

现在是公式的计算结果, 选择复制后粘贴为数值, 然后删除掉Q列中间值

RIGHT - 右截取

用法同 LEFT 

从右边截取

多出来的 "-", "k" 选中这列之后替换成 "" 即可删除

CONCATENATE - 拼接

拼接函数, 选择单元格, 然后拼接字符串, 可以连续往后追加拼接

简单的拼接也可以用 & 来实现

REPLACE - 替换

替换函数, REPLACE(替换位置, 替换开始位置,替换几个,替换字符)

 

replace 的替换和 ctrl + f 的替换是没什么区别的

SUBSTITUTE - 替换

快捷键 shift + F3 可以调出公式生成器

 这里选择  SUBSTITUTE 函数进行替换

 

实现结果同直接敲函数类似, 简单的替换也可以适用 ctrl + f 的查找替换进行

函数形式可以更加细化的指定替换个数进行操作

MID - 中间截取

从中间截取

TRIM - 去左右空格

去除前后的空格, 类似 python 中的 strip , 中间的空格无法去除

LEN - 查长度

计算字符长度

 

关联匹配函数

关联匹配函数主要用于多个表的合并相关操作

VLOOKUP - 匹配填充

搜索里面的值来查找对应区域的值

先制造一个场景

表A 中的缺失了一列数据 companyFullName . 而这列数据在表 B 中有一个可以根据 表 A 中的 companyId 列映射到的列

需要用 A 表中的 companyId 列 去匹配 B 的 companyId 列 对应的 companyFullName 列进行填充

A 表　　　　　　　　　　　　　　　　　　　　　　　　B表

    

B 表的数据先坐下去重

vlookup 填充 - 参数 (搜索值, 区域, 第几列, 是否精确匹配)   默认模糊匹配, 设置 false 表示精确匹配

INDEX - 根据索引取值

根据索引返回内容, 类似于 python 中对二维数组的操作根据索引取值

index(区域, 行索引, 列索引) - 根据选中的区域 , 行, 列 交叉找出值

如果选值超过了区域就会报错拿不到数据

MATCH - 查找值在区域的索引

参数 - 查找值(元素), 查找区域, 匹配模式(设置 0 即可)

如果存在多个值可以匹配到的话, 会返回第一个的索引

联合使用

函数可以嵌套使用

MATCH 的结果作为 INDEX 的一个参数使用

ROW - 返回行号

COLUM - 返回列号

OFFSET - 偏移

应用场景还是不多,  直接拉官方的说明文档了

逻辑运算函数

True / False

excel 中的 True 和 False 是可以直接转化为数组的 , 分别是 1 和 0 

比如 True + True = 2,  False + False = 0 这样子

逻辑与或操作的时候也是和 python 一致

逻辑运算

可以判断筛选, 比如这里的 u , v, w 分别表示三个条件

 

然后 x 表示三个条件的累加,

比如 3 就是满足三个条件,  0 就是满足 0 个条件 

然后就可以根据筛选拿出来相关的选项了

IF - 判断

if 的使用则是 满足添加选取第一个, 不满足选取第二个

当然 if 也是可以继续嵌套下去的

除了这些以外还有 is 系列进行判断是否什么什么类型

计算统计函数

SUM / SUMPRODUCT - 求和

 sum 就是简单的求和, 用 + 也可以

 sum\product 则可以实现更高阶的自动乘算后求和

这里是选中了的蓝色区域和红色区域一一乘算后在求和, 这样更加方便

COUNT - 计数

就是单纯的计算计数, 不计算为空的单元格计数

MIN / MAX -  最小 / 最大值

最大最小值, 没啥说的

RINK - 查找排名

就是查看指定的数字的指定区域的排名

RANDBETWEEN - 随机区间内生成数字

 可以用来随机抽样等操作

AVERAGEA - 平均值

QUARTILE - 分位数

用于计算统计学中的分位数

就是4分分位盒图的那个

STDEV - 标准差

也是统计学中的概念

UBTOTAL - 瑞士军刀函数

参数 - 调用函数 , 调用引用

调用函数的传入数字, 下面表格对应相关的函数

INT - 取整

经常用于去除小数点后的操作, 从而化成整数, 只能向下取整

如果向上取整可以简单的 + 1 偷懒, 如果是 四舍五入的需求则需要 Round 函数

ROUND - 四舍五入取整

 第二个参数 取 0 则表示只对整数进行取整

 如果设置为1 , 则为小数点后取整, 1 则表示小数点后一位, 2 就是两位了

 如果设置 -数字 则反向往 10位数, 百位数拓展

COUNTIF / COUNTIFS - 条件计数

单条件就用 countif , 多条件就用 countifs 就可以了

SUMIF /SUMIFS - 条件求和

AVERAGEIF / AVERAGEIFS - 条件求均

时间序列函数

时间的本质是数字

YEAR / MONTH / DAY

参数直接跟单元格, 从而可以提取出年月日

WEEKDAY - 一周的第几天

  得出的结果是 1-7 表示一周的第几天

国内的话是从周一开始, 但是国外的话是从周日开始

后面的参数作为区分, 取 1 为周日开始, 取 2 从周一开始

WEEKNUM - 一年第几周

用法同上, 参数取 1/2 也是一样的

NOW -  返回当前时间 (秒)

 返回格式 - 

TODAY - 返回当前时间 (日)

 返回格式 - 

日期运算

日期的运算是可以直接相减的, 得出的结果是相差的天数

用 DAYS 函数也是一样的结果

DATE - 日期拼接

用于拆分的日期合并

技巧

快捷键

数据分列

 分列前　　　　　　　　　　　  　 分列后

          

数据筛选

 勾选筛选后会有小三角, 下拉弹窗即可

数据条 / 色阶

开始 - 条件格式 - 数据条/色阶 

选中不满意后也可以 开始 - 条件格式 - 清除选中格式

数据透视表

选定区域, 选定的区域大小不同候选选项数量也不同

 

修改下显示

数据透视表是带有切片功能的, 比复制粘贴快很多

双击选定的单元格即可展开所属的分类

冻结首行

 冻结后首行会固定不在被移走, 这样非常方便, 

数据引用

通常的数据引用就直接选中, 或者用行列号

可以使用 excel 的根据所选内容创建, 从而以类似别名的形式引用

在使用就可以使用别名了

在定义名称这里也可以查询到已定义的别名

引用别名这里很常用在下拉框操作中

下拉框

下拉框在数据工具中的这个标志的数据验证里面

选中后选中序列, 然后来源这里就可以使用数据引用的别名. 操作会很方便

下拉框里的内容就是这别名的, 选中后会将下拉的内容填充到区域内

对于一些比较麻烦的数据输入, 这样可以快速填充非常方便, 而且还设置了约束

防止数据输入不对出错

迷你图

在单元图里面直接显示折线, 比如下面的例子

插入迷你图之后可以看得出数据的波动趋势

数据去重

数据分析

位置

数据 - 分析 - 数据分析

加载

如果没有这个选项则表示没有加载通过以下方式加载选项

描述统计

加载后点击数据分析弹出窗口

这里以选中统计描述为例

统计结果

数据切片

以下面的数据透视表为例

切片器的位置, 需要配合透视图一起使用,            点击后会根据字段名进行选择, 类似于筛选功能       确定后会生成类似于下拉框的字段数据

                          

然后点击这里的选项就可以改变透视图的数据源了, 通常可以配合插入图形进行展示, 从而动态的变换图形信息

插入器也可以继续插入, 插入很多很多个