概述

　　当需要以一种格式采集数据，然后操纵该数据到另一种格式里，最后放置该结果到另一种过程例如数据库时，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()函数可以用于移除非打印字符。