在Excel2007中操作和管理数据实例操作

概述

当需要以一种格式采集数据，然后操纵该数据到另一种格式里，最后放置该结果到另一种过程例如数据库时，Excel是一种极好的工具。在本文中，Excel2007是一种中间工具，作为一种数据转换工具将数据从一种系统转移到另一种系统。
本示例考虑一个简单的数据集，介绍整理数据的步骤，并在数据集中执行不同的转换，成为更合适的格式。

示例数据

这里是本示例练习里讨论的数据，复制(导入)并粘贴到电子表格里。
SSN,Last Name,Middle Name,First Name,DOB,Date of Letter,E-mail Address,Home Phone,Address,City,State,Postal Code
123456789,freehafer    ,drew   ,nancy,19700101,11/1/2007,NancyF@northwindtraders.com,1235550102,123 1st   Avenue,Seattle  ,wa,09999
123456789,cencini        ,a   ,andrew,19700101,11/1/2007,AndrewC@northwindtraders.com,1235550102,123 2nd Avenue,Bellevue,wa,09999
123456789,kotas      ,bollen,jan,19700101,11/1/2007,JanK@northwindtraders.com,1235550102,123 3rd Avenue,Redmond  ,wa,09999
123456789,sergienko      ,,mariya,19700101,11/1/2007,MariyaS@northwindtraders.com,1235550102,123     4th Avenue,Kirkland,wa,09999
123456789,thorpe     ,j ,steven,19700101,11/1/2007,steven@northwindtraders.com,1235550102,123  5th Avenue,Seattle,wa,09999
123456789,neipper    ,john,michael,19700101,11/1/2007,MichaelN@northwindtraders.com,1235550102,123 6th Avenue,Redmond,wa,09999
123456789,zare       ,b ,robert,19700101,11/1/2007,RobertZ@northwindtraders.com,1235550102,123 7th Avenue,Seattle,wa,09999
123456789,giussani       ,,laura,19700101,11/1/2007,LauraG@northwindtraders.com,1235550102,123 8th Avenue,Redmond,wa,09999
123456789,hellung-larsen      ,marie,anne,19700101,11/1/2007,AnneHL@northwindtraders.com,1235550102,123 9th Avenue,Seattle   ,wa,09999
注意与该数据相关的一些事项：
  SSN没有被格式化，只是原始的数字
  Last Name全部小写并且包含额外的空格
  一些人根本没有中间名，一些人是简写，一些人是完全拼写
  Date of Birth (DOB)是YYYYMMDD，而Date of Letter是MM/D/YYYY格式
  E-Mail Address包含大写和小写字符
  Home Phone没有格式化
  Address有额外的空格
  State全部是小写
  Postal Code有前导零并且不应该略去
这里是为处理准备这些数据所做的操作：
  当将数据导入Excel中时不能失去前导零
  格式SSN，在合适的地方放置破折号
  从姓名字段和Address字段中移除额外的空格
  在一个字段里获取所有的名称，以“Last Name, First Name Middle Initial.”的形式
  转换DOB成MM/DD/YYYY格式
  使E-Mail Address值全都成为小写
  根据标准的US电话号码数字格式(NNN) NNN-NNNN格式Home Phone字段
  大写State值
  根据这些排序关键字排序数据: State / City / Last Name / First Name / Middle Name (not middle initial)

步骤1：导入数据并且不要意外移去前导零！

对Excel，Postal Code值看起来像数字，这是装载该文件时对待的缺省方式。(不巧的是，Excel不能读取标题“Postal Code”并切换它为文本字段)

有一种方式覆盖这种缺省方式，使用“文本导入向导”：

1、“文件”-“打开”该文本文件。
2、文本导入向导——步骤1(共3步)：选择“分隔符号”，然后单击“下一步”。
3、文本导入向导——步骤2(共3步)：选择“逗号”作为分隔符，然后单击“下一步”(注意，其它的分隔符可以选择，甚至在数据使用的字符不在UI列表中时可以指定自定义的分隔符)。
4、文本导入向导——步骤3(共3步)：滚动到Postal Code字段并选择，然后设置“列数据格式”为文本，然后单击“完成”。
在列标题之间双击，使得列内容适合于列，结果如下图所示。


 
现在，数据已导入到工作表中而没有失去关键的信息。下一步处理这些格式为报告和上载。

步骤2：格式SSN

SSN值需要被转换为NNN-NN-NNNN的形式，可使用下面的两种方式完成。
1、改变显示数据的方式，但仍保持其实质为数字，或者
2、在数据里插入连字号。

方式一：通过单元格格式修改显示

1、选择存储数据的单元格区域A2:A10；
2、调出“设置单元格格式”对话框(Ctrl+1)；
3、在数值选项卡中，单击“自定义”类；
4、在“类型”中，输入字符串：000-00-0000；
5、单击“确定”。
下图为最终的结果。


 
注意，实质上的值为“123456789”(可在公式编辑栏中看到)。格式化单元格来显示连字号。

方式二：插入连字号

需要公式来完成该操作。本示例将使用从原始数据中创建新的数据表来转换。
公式将使用MID()函数提取SSN中的前3个数字，然后使用“&”来连接连字号，接着使用MID()函数获取中间的2个数字，使用“&”插入另一个连字号，最后再使用MID()来取得SSN中最后的4个数字。
1、复制并粘贴A1：L1中的标题到N1：Y1中。
2、在N2中，输入下面的公式：
=MID(A2,1,3)&”-”&MID(A2,4,2)&”-”&MID(A2,6,4)
3、向下填充公式到所有数据行(本例为N2：N10)。
下图是应用公式后的结果：


 
步骤3：处理姓名
处理姓名的几个问题：
1、没有在正确的情形下
2、包含额外的空格
3、仅需要中间的大写
“&”操作符用于使用逗号和空格连接Last Name里的值，“&”操作符用于从First Name中添加值，并添加空格到结果中，LEFT()函数与“&”操作符一起用于从Middle Name中创建和添加中间的大写字母。最后两步将这些函数连在一起，并使用TRIM()函数移除额外的空格。同时，使用PROPER()函数来设置正确的字母拼写。

1、在这里，在O1：Q1中的标题“Last Name”、“Middle Name”和“First Name”不再需要。删除列P和列Q，并重命名O1中的“Last Name”为“Name”，且公式将在列O中输入。
2、在O2中输入下列函数：=PROPER(TRIM(TRIM(B2)&”, “&D2&” “&LEFT(C2,1)))
结果如下图所示：

注意：额外的空格不会是数据中唯一不需要的字符，CLEAN()函数可以用于移除非打印字符。