数据处理

步骤一、数据清洗

1.重复数据处理

•函数法:countif(rang,criteria)对满足指定条件的单元格进行计数。

例:重复标记=countif(A:A,A2)

第二次重复标记=countif($A$2:A2,A2)

$A$2:绝对引用，拖拉公式时2不变，从A2开始。

•高级筛选法

选中区域-数据-排序和筛选-高级

•条件格式法

选中区域-样式-条件格式-突出显示单元格规则-重复值

•数据透视表法

插入-数据透视表

•删除重复数据

1)通过菜单操作删除重复项

数据-数据工具-删除重复数据

2)通过排序删除重复项

结合函数法，对第二次重复标记得出的值进行排序，前提:复制粘贴数值去除公式。

开始-编辑-排序和筛选-降序-（删除数值大于1的前面几项即可）

3)结合函数法，对第二次重复标记得出的值进行筛选。

数据-筛选-数字筛选-自定义筛选-不等于1

2.缺失数据处理

•定位输入（查找空白格）

开始-编辑-查找和选择下拉-定位条件（快捷键:ctrl+G）-空值

处理缺失值的四种方法:

法1:用一个样本统计值替代缺失值，最典型的方法是，用样本平均值代替缺失值。

注:用“ctrl+G”快捷键定位到空白单元格后，可输入平均值，再按“ctrl+enter”即可更改所有被选中的空格。

法2:用统计模型计算值代替（专业软件）

法3:将缺失值删除

法4:保留（数量大，缺失值不多时）

3.错误数据处理

错误形式:

*被调查者输入的选项不符合要求

*录入错误

•利用IF函数检查错误

IF（logical_test,value_if_true,value_If_false）:执行真假值判断，根据逻辑计算的真假值，返回结果。

IF（表示计算结果为true或false的表达式,为true返回的值，为false返回的值）如，IF(countif(b3:h3,"<>0")>3",错误","正确")

•利用条件格式标记错误

开始-条件格式-突出显示单元格规则-其他规则-使用公式，如=OR(b3=0,b3=1)=false 即找出选项中不等于0和1的选项。

步骤二:数据加工

1.数据抽取

•字段分列

1)菜单法:数据-分列-分格符号-空格

2)函数法:left、right函数

left(text,[num_chars]):得到字符串左部指定个数的字符。

•字段合并(用&或concatenate函数)

如Mr.林迟到比例为10%

1)a3&"迟到比例为"&text(b3,0%)

文本函数:text(值域，文本格式)

2)函数concatenate(text1,text2,...)将几个文本字符串合并为一个文本字符串。

=concatenate(a3,"迟到比例为",text(b3,0%))

•字段匹配

Vlookup(在数据表第一列中查找的值，查找的范围，返回的值在查找范围的第几列，近似/精准匹配)

2.数据计算

•简单计算(+ _ * /)

•函数计算

1)平均值与总和

average(number1,number2,...)

sum(number1,number2,...)

2)日期的加减法

date(year,month,day)含有三个参数，分别代表年月日

如，给2011年1月1日加3年、1个月和5天

=date(year(a2)+3,month(a2)+1,day(a2)+5)

注:插入动态日期:today()、now()

3)用函数datedif计算工龄

datedif(start_date,end_date,unit):返回两个日期之间的年/月/日间隔数。

unit有(Y/M/D/YM/MD/YD)六种形式。

YM:忽略日期中的日和年  MD:忽略日期中的年和月  YD忽略日期中的年

如，入职日期2006-10-1(b1表示)

现在日期2010-11-7(b2表示)

工龄(年):=datedif(b1,b2,y)&"年"

工龄(年月):=datedif(b1,b2,y)&"年"&datedif(b1,b2,ym)&"月"

3.数据分组

先分好组，设好阈值，用vlookup函数进行数据分组。

分组的数据接近于阈值又不能小于阈值。

4.数据转换

•数据表的行列互换

选择数据复制后，选择粘贴区域，快捷键"ctrl+alt+v"，运算中选择"换置"

延伸:在区域中还可以进行"加减乘除"运算，减/除是粘贴区域减/除复制区域。

若是表中数据变负号，则在任意单元格输入“-1”，复制，选择性粘贴到该区域。

•多选题录入数据方式之间的转换

(即多重分类法转化为二分类法)

=if(isnumber(hlookup(1,$b3:$d3,1,0)),1,0)

若为5个选项，公式需要修改5次，才能拖拉复制公式，只需修改1-5数值。

数据抽样

rand()为返回[0,1]的均匀分布随机数，而且每次计算工作表时都将返回一个新的数值，若输入公式后按F9，则生成的随机数不再返回新的值。

随机抽样函数:=rand()*(b-a)+a

如，在1-5500中抽取1000个序号。

随机数为=int(rand()*5500)，其中int()为取整函数。