完美Excel

经常需要在多个工作表中重复某些数据。可以使用Excel的组合工具，使得在某工作表中输入数据时能够同时将数据输入到多个工作表中。然而，也有更快速且灵活的方式，即使用VBA代码。
在Excel中，有一项能使数据一次放置在多个位置的功能，称之为组。其操作是通过将工作表组合在一起，以便它们在工作簿内建立链接。
手工组合工作表
要手工使用组的功能，只需简单地单击将要输入数据的工作表，并在单击想要放置数据的工作表的标签名时按住Ctrl键。此时，当在工作表的任意单元格中输入数据时，这些数据也将自动输入到已组合的其它工作表中。
要取消工作表组合，只需单击除组合的工作表之外的其它任一工作表标签，或者在工作表标签中单击右键，选择“取消组合工作表”命令。
提示：当将工作表组合在一起时，能够看到标题栏中在工作簿名称后面添加了一对方括号，其中显示“工作组”。这能很方便地查看工作表是否是组合的。建议在完成同步输入数据操作后，取消工作表组合。
虽然上面介绍的方法很容易，但也意味着需要记住工作表是否处于组合状态，并且在不需要时取消组合，否则将存在覆盖其它工作表数据的风险。例如，您可能想仅对某特定的单元格区域同步输入数据，如果在其它区域输入数据时，忘记取消工作表组合则可能覆盖掉其它工作表中已存在的数据或者输入不需要的数据。
自动组合工作表
能够通过使用一些非常简单的VBA代码克服上述缺点。要使这些代码能够工作，必须将它们放置在Sheet对象的私有模块中。即在VBE中右击工作表名称，选择“查看代码”或者双击工作表名称。然后，使用Excel的工作表事件之一，并将代码放置在事件过程中。
首先命名希望成组的单元格区域，以便在其它工作表中自动显示数据。然后，在工作表模块中输入下面的代码：
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
If Not Intersect(Range(”MyRange”), Target) Is Nothing Then
‘有目的的将工作表Sheet5放置在第一位,使之成为活动工作表
Sheets(Array(”Sheet5″, “Sheet3″, “Sheet1″)).Select
Else
Me.Select
End If
End Sub
在代码中，使用了命名的区域MyRange，可以将MyRange修改为您工作表中使用的单元格区域名称，同时修改代码中三个工作表名称为希望组合的工作表名称。

图：自动组合工作表的代码
注意，代码中在数组中使用的第一个工作表名必须是包含代码的工作表，并且您将在该工作表中输入数据。
一旦编写好代码，每次选择工作表中的任意单元格时，代码就会检查是否所选的单元格在命名区域MyRange中。如果在该区域，那么代码将自动组合你希望组合的工作表。如果不在，将通过激活正处理的工作表而取消组合。这项技巧的优点是不需要人工组合工作表，因而没有因为忘记取消组合而造成的危险，同时节省了时间并避免产生障碍。
如果希望在其它工作表中显示相同的数据，但不是出现在相同的单元格地址，那么使用如下的代码：
Private Sub Worksheet_Change(ByVal Target As Range)
If Not Intersect(Range(”MyRange”), Target) Is Nothing Then
With Range(”MyRange”)
.Copy Destination:=Sheets(”Sheet3″).Range(”A1″)
.Copy Destination:=Sheets(”Sheet1″).Range(”D10″)
End With
End If
End Sub
上面的代码也需要放置在Sheet对象的模块中，具体操作与前面内容相同。

注：初译自《Excel Hacks》，仅供参考。

在Excel中，能够同时显示多个工作簿，并且可以有在不同窗口排列的工作簿自定义视图，然后将视图工作区保存为.xlw文件并在需要时使用它们。
有时，当在Excel中进行操作时，可能需要在屏幕中打开多个工作簿，使之更容易使用或者从多个工作簿中查看数据。下面介绍如何以整洁且有组织的方式进行这项操作。
首先，打开所有需要的工作簿。
技巧：要一次打开多个工作簿，选择“Office按钮─—打开”，在“打开”对话框中，在按住Ctrl键的同时选择想要打开的工作簿，然后单击“打开”按钮。
从任一工作簿中选择“视图─—窗口─—全部重排”，如果“当前活动工作簿的窗口”复选框已选中，则取消选中，然后选择窗口排列方式并单击“确定”。
如果选择“平铺”，将以平铺的形式显示工作簿，如图1所示。
选择“水平并排”使得一个工作簿在另一个工作簿的顶部，如图2所示。
选择“垂直并排”使得一个工作簿紧挨着另一个工作簿，如图3所示。
选择“层叠”将使一个工作簿在另一个工作簿的上方，如图4所示。

图1：平铺方式排列的四个工作簿

图2：水平排列的四个工作簿

图3：垂直排列的四个工作簿

图4：层叠的四个工作簿
一旦工作簿按预想的视图显示后，就很容易在工作簿之间移动数据，例如复制、粘贴、拖放等。
如果需要反复创建某个视图，那么能够将其保存为工作区（workspace），即选择“视图─—窗口─—保存工作区”，在“文件名”中输入工作区的文件名，单击“确定”。当保存工作区时，文件的扩展名为.xlw而不是.xlsx。要将某工作簿恢复为完整的窗口，只需双击标题栏。
无论何时需要打开这些同样的工作簿，只需简单地打开.xlw文件。任何对在.xlw文件中的工作簿所作的变化都将在关闭工作区时自动保存，或者可以单独保存工作簿。
如果花些时间为需要在多个打开的工作簿中重复操作而设置自定义视图，将会发现这些操作将更容易管理。可以为不同的重复任务使用不同的视图，这取决于具体的任务或者您的感觉。

注：初译自《Excel Hacks》，仅供参考。

在Excel中，我们可以使用“数据有效性”功能来创建下拉列表，供用户从中选择。例如，如下图所示：

①选择主数据工作表中的B列。
②调出“数据有效性”对话框，选择“允许”下拉列表框中的“序列”。
③在“来源”中输入要提供用户选择的文本，例如：请假,出差,年休。
④单击“确定”后，在主数据工作表的B列中任一单元格单击，出现带有数据的可供选择的列表，如下图。

当然，也可以将需要提供的数据放置在工作表中的某单元格区域内，然后在“来源”中输入该单元格区域地址。
上述方法对于固定的或者说事先知道在列表中提供的数据时，非常合适。然而，如果下拉列表中的数据需要不断增加，或者需要调整，仍然按照上述方法的话，则需要重复上述步骤。
下面提供一种方法，能够动态改变列表中的数据，而无需每次调出“数据有效性”对话框。先看看下图：

①在基础数据工作表中，在列A单元格中依次输入想要显示在下拉列表中的数据，如“请假”、“出差”、“换休”。
注：将这些数据输入在单独的工作表中，能够避免在主工作表中操作时无意中修改这些数据。
②调出“新建名称”对话框，在“名称”中输入“List”。
③在“范围”列表中选择“工作簿”。
④在“备注”中输入一些说明文字。
⑤在“引用位置”输入公式“=OFFSET(基础数据!$A$1,0,0,COUNTA(基础数据!$A:$A),1)”。
现在，回到主数据工作表，如下图所示。

①选择列C。
②调出“数据有效性”对话框，选择“允许”下拉框中的“序列”。
③在“来源”中输入公式“=List”，即与前面定义的名称连接。
此时，单击列C中的任一单元格，将出现下拉列表框，包括在基础数据工作表中列A的数据，如下图所示。

现在，您在基础数据表中添加数据，如加班，此时在主数据工作表的列C中单元格的下拉列表中会自动出现添加的“加班”项，如下图所示。

Excel条件格式功能是一项十分强大且便利的功能。下面介绍自已在使用Excel过程中，利用条件格式功能的其中一个应用场景。
在一个工作表中，有许多条记录，如何使Excel自动标识出满足特定条件的记录呢？
例如：下图所示的工作表中有多条记录，要求当字段“是否完成”列中为“否”时，突出标识该行（本例中将该行突出显示为红色），使用户清楚没有完成的工作；当为“是”时，突出标识的背景色自动消失。

现在开始实现这样的效果，先看看下图：

①选择工作表的A列至E列。
②单击菜单“格式”——“条件格式”。
③在“条件格式”对话框中，选择“条件1”下拉框中的“公式”项。
④在“公式”框右侧的文本框中输入公式“=$E1=”是””。
⑤单击下方的“格式”按钮，在“单元格格式”对话框的“图案”选项卡中，选择颜色，这里是红色。
⑥单击“确定”按钮，关闭“条件格式”对话框。此时的效果如下图：

此时，当将E列中单元格的数据改为“是”时，该行的红色底纹会自动消失；如果为“否”，则该行会自动加上红色底纹。

概述
当需要以一种格式采集数据，然后操纵该数据到另一种格式里，最后放置该结果到另一种过程例如数据库时，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 2007来完成该操作。
假设原始数据设置需要通过State/City/Last Name/First Name/Middle Name来排序，考虑下列数据组：

可以创建一个新列(如列F)，使用文本联接将排序关键字连接在一起。在F2，输入下列公式：E2&D2&A2&C2&B2，将其标题命名为“SortKey”。
现在，排序该单个的列中与逐个排序关键字是相同的。当然，使用这种方式不能指定升序/降序。因此，Excel 2007更好。
附1：清除文本
Trim
移除单词之间除单个空格外的所有空格。在从另一应用程序中接收到的具有不规则空格的文本中使用TRIM。
TRIM函数被设计从文本中移除7位ASCII空格字符(值为32)。在Unicode字符集中，有一个额外的空格字符，称为非打断空格字符，其十进制值为160。该字符通常用于网页中作为HTML实体 。TRIM函数不能移除该字符。
Clean
从文本中移除所有非打印字符。使用CLEAN，清除从其它应用程序中导入的包含使用在操作系统中不可打印的文本。例如，可以使用CLEAN移除一些低级的计算机代码，这些代码经常在数据文件的开头和结尾，并且不可以被打印。
CLEAN函数被设计从文本中移除7位ASCII代码中开头的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位ASCII，这是ANSI字符集的一个子集(ANSE字符集：Microsoft Windows使用的一个8位字符集，允许使用键盘表示达到256个字符(0至255)，ASCII字符集是ANSE集的一个子集)。在7位ASCII里的前128个值(0至127)与Unicode字符集里前128个字符代表相同的字符，理解这一点是重要的。
TRIM函数被设计来从文本中清除7位ASCII空格字符(值为32)。在Unicode字符集中，有一个称作非打断空格字符的额外的空格，有一个十进制值160。该字符通常用于网页中作为HTML实体 。TRIM函数不能移除这个非打断空格字符。
CLEAN函数被设计来从文本中清除7位ASCII代码中开始的32个非打印字符(值从0到31)。在Unicode字符集中，有一些额外的非打印字符(值为127、129、141、143、144和157)。CLEAN函数不能移除这些额外的非打印字符。
要完成这项任务，使用SUBSTITUTE函数将Unicode字符中更高的值替换为TRIM和CLEAN函数被设计的7位ASCII字符。
注：本文译至MS Excel团队博客。本译文只是初稿，并未进一步整理和校对，仅供参考。