Excel--基础数据处理

作者: 原来是仙女阿 | 来源:发表于2020-07-02 18:18 被阅读0次

常见函数

文本清洗类
编码方式：ASCII/GB2312/GBK/GB18030/unicode等

函数名	参数	含义
Find	find(找什么，在哪找，开始位置)	查找一个字符串在另一个字符串中出现的起始位置
Left/Right	(文本，截取长度)	从文本最左/右开始截取指定长度的字符串
Mid	mid(文本，从哪开始，截取长度)	从字符串指定起始位置截取指定长度的字符串
Subsitute	substitute(文本，老字符串，新字符串，指定替换哪个(若出现多个老字符串)	替换指定字符串
text	text(数值，文字形式的数字格式) 数字格式参数常用代码	根据指定的数值格式将数字转为文本
Concatenate	concatenate(文本1，文本2，...)	合并字符串
Trim	trim(文本)	删除多余空格(会保留英文字符串作为词分隔符的空格)
Replace	replace(文本，开始位置，替换长度，用什么替换)	用新字符串替换旧字符串中指定位置的部分字符
Len	len(文本)	字符串长度

关联匹配类

函数名	参数	含义
Lookup	向量形式(lookup_value, lookup_vector, [result_vector]) lookup_vector 中的值必须按升序排列数组形式(不建议使用)	从单行或单列或从数组中查找一个值
Vlookup	vlookup(查找值，在哪找，第几列，是否精确匹配)	查找
Index	数组形式和引用形式	返回特定行列交叉处单元格的值或引用
Row/Column	(引用)	返回一引用的行号/列号
Offset	offset(reference, rows, cols, [height], [width])	以指定的引用为参照系，通过给定偏移量返回新的引用
Match	match(查找值，在哪找，匹配方式)	返回符合特定值在特定顺序的项在数组中的相对位置
Hyperlink	hyperlink(链接，名字)	创建超链接

逻辑运算类

函数名	参数	含义
And/Or	(逻辑值1，逻辑值2，...)	判断True/False
If	if(逻辑，如果为真怎么办，反之怎么办)	if语句
IsXX	好多函数	判断是不是XX
Not	not(逻辑)	对参数的逻辑值求反
False/True	false()/True()	返回逻辑值false/true

计算统计类

函数名	参数	含义
Sum		求和
Sumproduct		返回每个数组的乘积的和
Count		计数
Max/Min		最大值/最小值
Int		向下取整
Round		四舍五入
Rank	rank(数字，数组，升序/降序)	排名
Rand/Randbetween		返回一个介于[0,1)/指定的数字之间的随机数
Average		平均值
STDEV/STDEVP	样本等于总体时，就用stdevp；抽样小于总体时，用stdev	基于样本估算标准偏差/整个样本总体的标准偏差
Quatile	quartile(数组，几分位)	四分位数
Subtotal		返回一个数据列表或数据库的分类汇总

时间序列类

函数名	参数	含义
Year/Month/Day	year(日期)/month(日期)/day(日期)	年/月/日
Date	date(年，月，日)	返回在Excel日期时间代码中代表日期的数字
Weekday	weekday(日期，类型)类型一般用2	星期几
Weeknum	weeknum(日期，类型)类型一般用2	一年中的第几周
Now	now()	当前日期时间
Today	today()	当前日期

常用技巧
- 快捷键
  快速移动/选择：Ctrl+方向键/Ctrl+Shift +方向键
  选择整张表：Ctrl+A
- 功能
  - 冻结窗格【视图】
  - 分列/删除重复项【数据】
  - 数据验证-列表（自定义下拉菜单）【数据】
  - 分析工具库/数据分析【数据】
  - 数据透视表/图/切片器/迷你图【插入】
  - 条件格式/数据条/色阶【开始】
  - 自定义名称【公式】
数据可视化-常用图表
- 散点图：描述数据之间的规律
  散点图可以手动划分象限，并以不同颜色的散点区分。
  不同象限数据分布
  1.使用if函数将数据划分为a,b,c,d四组
  2.选择数据-添加系列-选择四组数据
  3.设置坐标轴格式-横/纵坐标轴交叉-坐标轴值
- 折线图：描述数据随时间变化的趋势
- 面积图：注重数据类别随时间趋势的变化关系
- 柱形图：描述类别之间的数量关系
- 旭日图：描述数据的层次结构
- 漏斗图：描述转化过程
  使用堆积条形图绘制漏斗图。
  1.设置辅助列a,b。a/b=(总人数-参与人数)/2
  2.选择数据插入堆积条形图。
  堆积条形图
  3.设置坐标轴格式-逆序类别
  4.分别选中两个辅助列，设置填充格式为无填充
  漏斗图
- 甘特图：描述项目进度
  带日期线的甘特图。甘特图也是使用堆积条形图画的。
  1.选择数据绘制堆积条形图。
  2.选择数据-添加系列（‘开始时间’要在‘需要天数’上面）/水平轴标签
  3.设置坐标轴格式-逆序类别/设置日期的填充格式为无填充
  4.添加辅助列a,b。a是数字列（递增），b是日期列（日期线）b
  5.选择数据-添加系列b
  6.更改系列图表类型-将系列b改为带直线的散点图
  7.选择数据-编辑b-填入x值（日期列）/y值（数字列）
  带日期线的甘特图
- 标靶图：衡量业务完成情况
  标靶图
- 箱线图：研究和观察数据分布（基于分位数的估计）
  箱线图
- 直方图：描述数据分布的情况
  借助RAWGraphs可视化展示数据（Circle Packing/Beeswarm）
  借助高德地图API进行地理位置可视化