完美Excel

这是Debra Dalgleish在Excel工作表中为大家创建的一棵圣诞树，她希望：

Merry Christmas! I hope you’re relaxing today, and spending time with friends and family.

这棵圣诞树中的亮光使用Excel条件格式创建，并且当按下F9键时，亮光会变化。当然，只能在Excel 2007或更高版本的Excel中使用。
下载地址：

有时候，我们希望用户只能修改工作表中某些区域的数据，其他部分不能修改。例如，下图所示，用户只能在单元格B1中输入数据，单击其他单元格时，工作表无反应，双击其他单元格时，光标将定位在B1单元格中。

步骤1 选择单元格B1。
步骤2 单击“开始——单元格——格式”，出现下图所示的菜单。可以看到倒数第二个命令“锁定单元格”前有活动标记，这是一个切换单元格锁定的命令，这里单击该命令，取消单元格锁定。

当然，也可以在“设置单元格格式”对话框中取消单元格锁定，如下图所示，调出“设置单元格格式”对话框，取消“锁定”前的复选即可。

步骤3 单击“开始——单元格——保护工作表”，打开如下图所示的对话框。注意，对话框下方的列表中，默认选择了允许用户对锁定单元格和未锁定单元格都进行操作。本示例要求用户仅能操作工作表中的B1单元格，因此取消“选定锁定单元格”前的复选，如下图所示。单击“确定”。

步骤4 测试工作表。此时，仅能在B1单元格中修改数据。

概述
当需要以一种格式采集数据，然后将数据转换为另一种格式，最后放置结果到另一系统中例如数据库时，Excel是一种极好的工具。在本文中，Excel充当中间工具，即作为一种数据转换工具将数据从一个系统转移到另一个系统。
本示例考虑一个简单的数据集，介绍整理数据的步骤，并在数据集中执行不同的转换，使之成为更合适的格式。
示例数据
下面是本示例练习里讨论的数据，可以将其复制并粘贴到电子表格里。
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()函数可以用于移除非打印字符。
步骤4：将DOB转换为实际的日期
DOB值不会被Excel作为日期来识别，需要将其转换为MM/DD/YYYY格式。使用DATE()函数和MID()函数将获取所需要的结果。使用MID()函数从DOB字段中提取日期中的YEAR、MONTH和DAY部分，使用DATE()函数将结果转换为日期：
1、在P2中输入下列公式：
=DATE(MID(E2,1,4),MID(E2,5,2),MID(E2,7,2))
2、向下填充该公式到数据行(本例中为P2:P10)
结果如下图所示：

步骤5：填充Date of Letter
对Date of Letter字段不需要改变，因此复制并粘贴这些值，或者用公式指向它们以便自动完成表格创建。
步骤6：小写E-Mail地址
为使表达一致，应该小写电子邮件地址，因为电子邮件不会区分大小写。使用LOWER()函数来完成这项操作。
1、在单元格R2中，输入公式：=LOWER(G2)
2、下拉公式填充全部的数据行
结果如图所示：

步骤7：格式化Home Phone
Home Phone值难以阅读，且不是正确的格式，需要处理为(NNN) NNN-NNNN格式。
与SSN一样，可以通过单元格格式或在合适的位置插入字母来完成。
方式一：通过单元格格式改变显示
1、选择数据存储的单元格H2：H10
2、激活“设置单元格格式”对话框(Ctrl+1)
3、在“数值”选项卡中，单击“自定义”类别
4、在输入框中，输入字符串：(000) 000-0000
最终的结果如下图所示：

方法二：插入括号和连字号
需要使用公式来完成这些操作。该公式使用MID()和连接操作符创建正确的值。
1、在单元格S2中，输入公式：
=”(” & MID(H2,1,3) & “) ” & MID(H2,4,3) & “-” & MID(H2,7,4)
2、下拉公式到所有数据行(本例中为S2：S10)
结果如下图所示：

步骤8：从Address中移除额外的空格
使用TRIM()函数移除额外的空格，与处理Name字段一样。
步骤9：添加City值到新表中
由于创建一个完整的新表取代原来的数据，因此需要City中的值，即使它们中没什么。如果有大量的数据想要，可能不想手工查看它们。此时，可以使用TRIM()和CLEAN()。
步骤10：使所有的State值为大写
State字段包含小写的值，该字段所有的字符需要变为大写。PROPER()和LOWER()已经用于清除数据，因此使用UPPER()很容易转换小写字符成为大写。
1、在V2中输入公式：=UPPER(K2)
2、下拉公式至所有数据行(本例中为V2：V10)
结果如图所示：

步骤11：填充Postal Code
从L2：L10中复制并粘贴到W2：W10中。
步骤12：确定最终的数值
现在，已使用所需要的格式完成了创建新表。应该移除公式，仅保留值，然后删除原始数据来完成最终的表。
通过复制N1：W10区域，并选择性粘贴其值到A1：J10区域中，然后删除多余的列，完成最终的表如下图所示。

额外的讨论：使用多于3个关键值排序文本
可能一些用户需要使用多于3个关键字来对数据排序。Excel 2003仅支持3个层级的关键字，而Excel 2007支持更多。本示例无须使用Excel 2007，这里介绍如何使用Excel 2003来完成该操作。
假设原始数据设置需要通过State/City/Last Name/First Name/Middle Name来排序，考虑下列数据组：

可以创建一个新列(如列F)，使用文本联接将排序关键字连接在一起。在F2，输入下列公式：E2&D2&A2&C2&B2，将其标题命名为“SortKey”。
现在，该列中的排序与逐个排序关键字是相同的。当然，使用这种方式不能指定升序/降序。因此，Excel 2007更好。
附1：清除文本
Trim
移除单词之间除单个空格外的所有空格。在从另一应用程序中接收到具有不规则空格的文本中使用TRIM。
TRIM函数被设计从文本中移除7位ASCⅡ空格字符(值为32)。在Unicode字符集中，有一个额外的空格字符，称为非打断空格字符，其十进制值为160。该字符通常用于网页中作为HTML实体&nbsp。TRIM函数不能移除该字符。
Clean
从文本中移除所有非打印字符。使用CLEAN，清除从其它应用程序中导入的包含使用在操作系统中不可打印的文本。例如，可以使用CLEAN移除一些低级的计算机代码，这些代码经常在数据文件的开头和结尾，并且不可以被打印。
CLEAN函数被设计从文本中移除7位ASCⅡ代码中开头的32个非打印字符(值从0到31)。在Unicode字符集中，有一些额外的非打印字符(值为127、129、141、143、144和157)。CLEAN函数不能移除这些非打印字符。
使用SUBSTITUTE和CHAR从Unicode中进行高级清除
有时，文本值包含前导的、一连串的、或多个嵌入的空格字符(Unicode字符集值为32和160)，或者非打印字符(Unicode字符集值为0至31、127、129、141、143、144和157)。在排序、筛选或搜索时，这些字符有时会导致不可预料的结果。例如，用户不会注意添加额外的空格字符或者从可能在嵌入了包含非打印字符的文本里导入文本，会产生印刷上的错误。因为不容易注意这些字符，不可预料的结果可能难于理解。要移除这些不需要的字符，可以使用TRIM、CLEAN和SUBSTITUTE函数的组合。
TRIM函数从文本中移除单词之间除单个空格外的空格，CLEAN函数从文本中移除所有不可打印的字符。这两个函数被设计来处理7位ASCⅡ，这是ANSI字符集的一个子集(ANSE字符集：Microsoft Windows使用的一个8位字符集，允许使用键盘表示达到256个字符(0至255)，ASCⅡ字符集是ANSE集的一个子集)。在7位ASCⅡ里的前128个值(0至127)与Unicode字符集里前128个字符代表相同的字符，理解这一点是重要的。
TRIM函数被设计来从文本中清除7位ASCⅡ空格字符(值为32)。在Unicode字符集中，有一个称作非打断空格字符的额外的空格，有一个十进制值160。该字符通常用于网页中作为HTML实体&nbsp。TRIM函数不能移除这个非打断空格字符。
CLEAN函数被设计来从文本中清除7位ASCⅡ代码中开始的32个非打印字符(值从0到31)。在Unicode字符集中，有一些额外的非打印字符(值为127、129、141、143、144和157)。CLEAN函数不能移除这些额外的非打印字符。
要完成这项任务，使用SUBSTITUTE函数将Unicode字符中更高的值替换为TRIM和CLEAN函数被设计的7位ASCⅡ字符。

注：本文译至MS Excel团队博客。本译文只是初稿，并未进一步整理和校对，仅供参考。

有时，我们需要在一个主工作表中创建对其他工作表的链接，起导航作用。例如，下图的示例工作表，在“导航”工作表中建立对“销售清单”工作表、“费用清单”工作表的链接。

下面，介绍一种简单的快捷创建工作表链接的方法。
步骤1 首先需要保存工作簿，否则不能应用下面的技巧。
步骤2 选择“销售清单”工作表中的单元格A1，即包含文本“销售清单”的单元格。
步骤3 移动光标到单元格的边缘，当光标变为十字箭头时（如上图所示），按住鼠标右键。
步骤4 由于我们想将其拖到“导航”工作表，因此需要再按住Alt键，拖动该单元格到“导航”工作表标签上。如下图所示。

步骤5 此时，将激活“导航”工作表，释放Alt键（但仍需按住鼠标右键）。
步骤6 拖到单元格B3中，释放鼠标右键，在弹出菜单中选择“在此创建超链接”。

此时，将使用所拖动的单元格中的文本自动创建链接，如下图所示。

同理，可以创建对其他工作表的链接。
操作视频：

有时，在工作表中有很多数据，我们希望能够快速地从当前单元格移动到最后一行或最后一列。除了我们熟知的组合，以及或键组合外，这里再介绍一个操作技巧：
如下图所示的工作表，将近有1000行，11列，现在要移动到该表的最后一行，将鼠标移动到活动单元格的底部，光标变为十字箭头后双击，即可移到最后一列。

同理，鼠标移到活动单元格的右侧，当光标变为十字箭头后双击，可移至最右侧列的单元格。对于向上或向左移，可以采取同样的操作。
当然，如果同一列或同一行中的数据不连续，那么活动单元格会移至空单元格之上或之左的单元格。