4. 数据处理
2018-07-31 本文已影响0人
柄志
数据清洗
数据清洗就是将重复的数据筛选清除、将缺失的数据补充完善、将错误的数据纠正或删除
处理重复数据
查找重复数据
COUNTIF函数
- COUNTIF(range,criteria)：统计满足某个条件的单元格的数量
- range：要统计的单元格的范围
- criteria：计算条件，可为数字、文本或表达式
高级筛选
- 数据-排序和筛选-高级：（高级筛选）-选择不重复的记录
条件格式
- 开始-条件格式-突出显示单元格规则-重复值
数据透视表
- 将字段同时拖到“行”标签和“值”汇总区域
- 值字段设置选择“计数”
删除重复数据
菜单操作
- 数据-数据工具-删除重复项（选择包含重复值的列）
排序
- 略
筛选
- 略
处理缺失数据
缺失值最常见的表现形式是空值或错误的标识符。
处理缺失值的方法
- 用一个样本统计量的值代替缺失的值（例如平均值）
- 用一个统计模型计算出来的值去代替缺失值
- 将有缺失值的记录删除
- 将有缺失值的记录保留，仅在相应的分析中做必要的排除
定位输入
- Ctrl+G
- Ctrl+Enter
查找替换
- Ctrl+H
检查逻辑错误
IF函数
- IF(logical_test,value_if_true,value_if_false)：执行真假值判断，根据逻辑计算的真假值，返回结果
- logical_test：计算结果为TRUE或FALSE的表达式
- value_if_true、value_if_fals：分别为TRUE、FALSE时的返回值
- 条件表达式用比较运算符(<、=、>)建立，“<>”表示不等于
- 值若是数字可直接书写，若是文本数据需用双引号(" ")标记
条件格式
- 开始-条件格式-突出显示单元格规格-其他规则-使用公式确定要设置格式的单元格
- OR(logical1[,logical2,…)：至少一个参数为真，则返回TRUE
- AND(logical1[,logical2,…)：所有参数为真，才返回TRUE
数据加工
数据加工时在数据清洗的基础上对数据字段进行信息提取、计算、分组、转换等加工，得到需要的数据表
数据抽取
字段分列
分列
- 数据-数据工具-分列-分隔符号（或固定宽度）
LEFT、RIGHT函数
- LEFT(text,[num_chars])：得到字符串左边指定个数的字符
- RIGHT(text,[num_chars])：得到字符串右边指定个数的字符
- MID(text,star_num,num_chars)：得到文本字符串中指定起始位置起返回的指定长度的字符
字段合并
&
- TEXT(value,format_text)：根据指定的数字格式将数字转成文本
- value：数值、能够返回数值的公式或对数值单元格的引用
- format_text：文字形式的数字格式
CONCATENATE函数
- CONCATENATE(text1,text2,…)：将多个文本字符串合并成一个
- text可以是字符串、数字或对单个单元格的引用
字段匹配
VLOOKUP函数
VLOOKUP函数的作用是在表格的首列查找指定的数据，并返回指定的数据所在行中的指定列处的单元格
- VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)
- lookup_value：要在第一列查找的值
- table_array：包含数据的单元格区域，第一列应当是由lookup_value搜索的值
- col_index_num：应返回匹配值在table_array中的列序号
- range_lookup：逻辑值，若要近似匹配使用TRUE（或0）或省略，若要精确匹配使用FALSE
- 近似匹配识别小于等于lookup_value的值
- 一般使用精确匹配
HLOOKUP函数
HLOOKUP函数的作用是在表格的首行查找指定的数据，并返回指定的数据所在列中的指定行处的单元格
- HLOOKUP(lookup_value,table_array,row_index_num,range_lookup)
- lookup_value：要在第一行查找的值
- table_array：包含数据的单元格区域，第一列应当是由lookup_value搜索的值
- row_index_num：应返回匹配值在table_array中的行序号
- range_lookup：逻辑值，若要近似匹配使用TRUE（或0）或省略，若要精确匹配使用FALSE
- 近似匹配识别小于等于lookup_value的值
- 一般使用精确匹配
SEARCH函数
- SEARCH(find_test,within_text,start_num)：返回指定的字符串在原始字符串中首次出现的位置
- find_test：要查找的文本字符串
- within_text：被搜索字符串
- start_num：数字值，指定从被搜索字符串左侧第几个字符开始查找，省略则为1
- 可以使用问号（？）和星号（*）作为通配符，分别可代表任何一个字符和任何字符串，两个符号前加“~”表示转义
数据计算
简单计算
- 字段通过加、减、乘、除等简单算术运算即可计算
函数计算
总和与平均值
- SUM()
- AVEGAGE()
日期
- DATE(year,month,day)
- DATEIF(start_date,end_date,unit)
- start_date、end_date：起始与结束日期
- unit：所需信息的返回时间单位代码，有"y"、"m"、"d"、"md"、"ym"、"yd"6种形式
- "y"返回时间段中的整年数
- "m”返回时间段中的整月数
- "d"返回时间段中的天数
- "md"返回起始日期与结束日期的同月间隔天数，忽略日期中的月份和年份
- "yd"返回起始日期与结束日期的同年间隔天数，忽略日期中的年份
- "ym"返回起始日期与结束日期的间隔月数。忽略日期中年份
数据分组
- 可使用VLOOKUP函数的近似匹配
数据转换
数据表的行列转换
选择性粘贴
- 开始-剪切板-粘贴-选择性粘贴-转置
- Ctrl+Alt+V
数据抽样
RAND函数
- 返回[0,1]的均匀分布随机数
- 每次计算工作表时都将返回一个新的数值
- 编辑状态按f9键生成的随机数将永久保存，不再返回新值
