1.1.1 招标信息数据规范化
 
  公司主要分成商务部、财务部、项目部、业务部、人力资源部。说明:文中涉及到的所有表格都是经过特殊处理过的数据。
 
  首先就是对商务部的招标信息表格进行处理。招标信息是从网站获取的,每天查找到跟公司业务有关的信息,然后复制粘贴到Excel中。卢子看了下招标信息的Excel表格,发现了一些问题。
 
  如图1-7所示项目金额非常乱,单位不统一,有的是以万为单位,有的是以元为单位,还有就是里面有的还包含其他内容,如“人民币:”,这样的数据如果没有进一步处理,很难将这些数据利用起来,连最基础的求和都做不了。
 
 
Office Excel 数据处理相关知识,将不规范的日期和数据处理为标准规范的数据
 
 
  卢子看到这里大概有了一个想法,就是先将数字提取出来,然后再统一单位。
 
  在F2输入公式:=-LOOKUP(1,-MID(B2,MIN(FIND(ROW($1:$10)-1,B2&1/17)),ROW($1:$15))),按Ctrl+Shift+Enter三键结束,双击向下填充公式。
 
  公式的大概意思是:
  
  MIN(FIND(ROW($1:$10)-1,B2&1/17)
 
  ROW($1:$10)-1就是得到0-9,B2&1/17其中1/17包含了0-9这10个数字,这样保证一定可以找到数字,不会出错,MIN函数就是获取第1位数字的位置。
 
  MID(B2,MIN(FIND(ROW($1:$10)-1,B2&1/17)),ROW($1:$15))
 
  从第1位数字开始提取1到15位,因为在Excel中允许的最大数字是15位,这样保证能够提取到所有数字。如果觉得不够直观,可以在编辑栏用鼠标选择这部分,然后按F9键,这样就可以解读公式。
Office Excel 数据处理相关知识,将不规范的日期和数据处理为标准规范的数据
 
  -MID(B2,MIN(FIND(ROW($1:$10)-1,B2&1/17)),ROW($1:$15))
 
  将非数字的转换成错误值,数字变成负数,同样也可以用F9键查看。
Office Excel 数据处理相关知识,将不规范的日期和数据处理为标准规范的数据 
 
  LOOKUP函数忽略错误值查找最后满足条件的值,用1就查找到-34.51,-LOOKUP就是将负数转变成正数。
 
  在G2输入公式:=IF(COUNTIF(B2,"*万*"),1,10000),按Enter结束,双击向下填充公式。
 
  用COUNTIF判断是否包含万字,包含的话就返回1,否则返回10000,这样就能够统一单位。
 
  现在把这2条公式合并起来,就能够获取最终的答案。
 
  先把原先的2条公式清除,在F2输入公式:=-LOOKUP(1,-MID(B2,MIN(FIND(ROW($1:$10)-1,B2&1/17)),ROW($1:$15)))/IF(COUNTIF(B2,"*万*"),1,10000),按Ctrl+Shift+Enter三键结束,双击向下填充公式。如图1-8所示。
 
Office Excel 数据处理相关知识,将不规范的日期和数据处理为标准规范的数据
 
 
 
  如图1-9所示,日期格式不统一,有三种形式:年月日、以.跟-作为分隔符号的,以.为分隔符号这种是不标准日期,需要用分列处理,而其他2种格式通过设置单元格格式即可改变。
 
Office Excel 数据处理相关知识,将不规范的日期和数据处理为标准规范的数据
 
 
  Step 01如图1 10所示,选择E列,切换到“数据”选项卡,单击“分列”按钮,在弹出的“文本分列向导”对话框,保持默认不变,连续单击2次“下一步”按钮。
 
 
Office Excel 数据处理相关知识,将不规范的日期和数据处理为标准规范的数据
 
 
  Step 02如图1 11所示,选择“日期”格式,单击“完成”按钮。
 
 
Office Excel 数据处理相关知识,将不规范的日期和数据处理为标准规范的数据
 
 
  Step 03如图1 12所示,选择E列,将单元格设置为“短日期”格式。
 
 
Office Excel 数据处理相关知识,将不规范的日期和数据处理为标准规范的数据
 
 
  Step 04如图1 13所示,进行美化处理。
 
Office Excel 数据处理相关知识,将不规范的日期和数据处理为标准规范的数据 
 
 
  处理完后,卢子把表格发给媛媛,跟她说了2个注意点:
 
  1、项目金额统一以万元为单位,不要录入带单位的,只录入纯数字;
 
  2、记录时间以-作为分隔符号,不要录入其他格式。
 

 

发表评论

您的电子邮箱地址不会被公开。 必填项已用*标注