完美Excel

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

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

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

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

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

引言：在http://blog.contextures.com中，看到了一系列有趣的增强数据有效性功能的技巧，特收集于此，便于以后参考。
您可能已经熟悉Excel数据有效性功能，它能够提供一个含有一系列值的列表供选择，然而只能在列表中选项一个项目，并且重新选择其它项目时将会覆盖原来选择的项目。
下面的示例对数据有效性进行了改进，增强了其功能：

• 从数据有效性下拉列表中选择后，所选项目将被依次添加到相邻的列中。参见示例中的“SeparateColumns”工作表。
• 从数据有效性下拉列表中选择后，所选项目将被依次添加到该列右侧列的行中。参见示例中的“SeparateRows”工作表。
• 从数据有效性下拉列表中选择后，所选项目将被添加到相邻列中的相邻单元格，项目之间由逗号分隔。参见示例中的“CommaSeparated”工作表。
• 从数据有效性下拉列表中选择后，所选项目将被添加到相邻列的相邻单元格，项目之间在不同的行。参见示例中的“LineBreak”工作表。
• 从数据有效性下拉列表中选择后，所选项目将被添加到相邻列的相邻单元格中，每个项目在不同的行，并且能够在单元格中添加新项目，所添加的项目将自动添加到命名区域。参见示例中的“LineBreakAddSort”工作表。

示例下载：

操作演示：

在工作中，零星工程项目是最令人头疼的，又杂又乱，项目繁多且大小不一，一直不好管理。这几天，试着利用Excel做了一个简易的零星工程项目管理系统，以方便对零星工程项目的管理。当然，先试用，然后再作进一步的改进和完善。大家如果有兴趣，可以下载附件的示例，给我提提意见或建议。
（#1对于简单的数据库，Excel是一个好的选择。设计良好的Excel工作表和结构可以帮助很好地组织和管理数据，并制作报表）
设计数据输入工作表
下面是数据工作表，名为“工程清单”，共15列。

除序号外，其它列的信息为项目信息和跟踪信息。
考虑到查询的需要，当输入一个新项目后，有一些必填项，因此，在工作表中使用了条件格式，如果这些必填项未填写，该行将呈现红色，如下图所示。

只有当必填项填写完成，红色才消失。
条件格式公式如下：

=AND($A2<>“”,OR($E2=”",$G2=”",$I2=”",$K2=”",$M2=”",$N2=”"))

即当第1列不为空时，列E、列G、列I、列K、列M、列N必须要有输入。
（#2防错性设计。即利用Excel提供的功能提示用户必须的操作或不需要的操作）
同时，动态命名工作表中的列表数据。

名称：ProjectList
公式：=OFFSET(工程清单!$B$1,0,-1,COUNTA(工程清单!$B:$B),15)

这样，添加数据后，命名区域立即更新。
（#3动态命名。使用公式动态命名区域，可以保持使用工作表中最新的数据。）
设计查询工作表
在“工作查询”工作表中，选择相应的查询条件后，下方显示满足条件的所有数据记录。

黄色单元格区域可以选择条件。选择后，相应的记录在下方显示。
在这个工作表中，有5个命名区域：

SelSQ=工作查询!$B$3
SelMKS=工作查询!$C$3
SelWG=工作查询!$B$6
SelYS=工作查询!$C$6
SelEmployee=工作查询!$F$3
ExtractProjects=工作查询!$A$8:$O$8

其中，B3、C3、B6、C6都使用了简单的数据有效性，F3中的数据有效性使用了名称，即“人员列表”工作表中获取人员姓名（下文讲述）。
该工作表模块对应的代码为：

Private Sub Worksheet_Change(ByVal Target As Range)
    Dim rngCrit As Range
    Set rngCrit = Nothing
    Application.EnableEvents = False
    On Error GoTo exitHandler
    Select Case Target.Address
        Case Range("SelSQ").Address
            Set rngCrit = wksCrit.Range("CriteriaSQ")
            Range("SelMKS").ClearContents
            Range("SelWG").ClearContents
            Range("SelYS").ClearContents
            Range("SelEmployee").ClearContents
        Case Range("SelMKS").Address
            Set rngCrit = wksCrit.Range("CriteriaMKS")
            Range("SelSQ").ClearContents
            Range("SelWG").ClearContents
            Range("SelYS").ClearContents
            Range("SelEmployee").ClearContents
        Case Range("SelWG").Address
            Set rngCrit = wksCrit.Range("CriteriaWG")
            Range("SelSQ").ClearContents
            Range("SelMKS").ClearContents
            Range("SelYS").ClearContents
            Range("SelEmployee").ClearContents
        Case Range("SelYS").Address
            Set rngCrit = wksCrit.Range("CriteriaYS")
            Range("SelSQ").ClearContents
            Range("SelMKS").ClearContents
            Range("SelWG").ClearContents
            Range("SelEmployee").ClearContents
        Case Range("SelEmployee").Address
            Set rngCrit = wksCrit.Range("CriteriaEmployee")
            Range("SelSQ").ClearContents
            Range("SelMKS").ClearContents
            Range("SelWG").ClearContents
            Range("SelYS").ClearContents
    End Select
 
    If Not rngCrit Is Nothing Then
        rngCrit.Cells(2, 1).Value = Target.Value
        wksProjects.Range("ProjectsList").AdvancedFilter _
            Action:=xlFilterCopy, _
            CriteriaRange:=rngCrit, _
            CopyToRange:=Range("ExtractProjects"), _
            Unique:=False
    End If
 
exitHandler:
    Application.EnableEvents = True
    Exit Sub
errHandler:
    Resume exitHandler
End Sub

代码前段的Case语句表示，当选择其中一个命名区域时，将查询条件工作表（下文介绍）中对应的查询条件区域赋值给对象变量，将其它命名区域清空。
代码后段表示，将当前区域的值赋给相应的查询条件区域，然后利用该区域的值作为高级筛选的筛选条件，并将筛选后的结果复制到当前工作表指定区域。
（#4高级筛选。利用高级筛选获取所需要的数据）
设计查询条件工作表
每一个用于筛选的条件，都存放在“查询条件”工作表中。

这样，在“工作查询”工作表中选择相应的条件后，“查询条件”工作表中相应的值得到更新，并在高级筛选中应用。
“查询条件”工作表中命名的区域为：

CriteriaSQ=查询条件!$B$1:$B$2
CriteriaMKS=查询条件!$D$1:$D$2
CriteriaWG=查询条件!$F$1:$F$2
CriteriaYS=查询条件!$H$1:$H$2
CriteriaEmployee=查询条件!$J$1:$J$2

人员列表
在“人员列表”工作表中，使用数据透视表功能从工程清单中获取人员名单，并在筛选条件中使用。

在该工作表中，定义了动态名称用于获取最新的人员列表：

EmployeeList=OFFSET(人员列表!$B$3,1,0,COUNTA(人员列表!$B:$B)-COUNTA(人员列表!$B$1:$B$3),1)

同时，当工作簿刚打开时，以及切换“工程清单”工作表时，更新数据透视表，以获取最新的数据：

Private Sub Workbook_Open()
    Dim pt As PivotTable
    For Each pt In wksLists.PivotTables
        pt.RefreshTable
    Next pt
End Sub
 
Private Sub Worksheet_Deactivate()
    Dim pt As PivotTable
    For Each pt In wksLists.PivotTables
        pt.RefreshTable
    Next pt
End Sub

（#5数据透视表。使用数据透视表可以容易地获得所需要的数据）
完整的示例
“工程清单”工作表用于输入数据，“工作查询”工作表用于查询，以便了解工作实施进展情况。“查询条件”和“人员列表”工作表不需要做任何操作。

示例下载

应用演示

注：本示例的灵感来源于http://blog.contextures.com中的文章《Create a Movie Collection Database in Excel》。

使用名称和公式，我们可以动态更新数据有效性中的项目，而不必每次调出数据有效性对话框来修改。

通常，我们可以将项目数据存放在一个单独的工作表中，以便不会受到数据工作表操作时的影响。例如，我们需要在数据工作表Sheet1中定义数据有效性，此时我们可以在工作表Sheet2中放置数据有效性项目，比如在Sheet2的列A。使用“定义名称”命令，调出“新建名称”对话框，在“名称”文本框中输入名称，本例中为“公司名称”，选择“范围”为工作簿，在“引用位置”中输入公式：

=OFFSET(Sheet2!$A$1,0,0,COUNTA(Sheet2!$A:$A),1)

然后，选择工作表Sheet1中需要定义数据有效性的单元格，在“数据有效性”对话框中“允许”下拉表中选择“序列”，在“来源”中输入公式：

=公司名称

此时，当您在Sheet2的列A中添加项目后，数据有效性项目会自动更新。
录制了一段操作视频：

有时，我们可能想隐藏Excel单元格中的数据，使得他人不能随便发现这些数据。这里，介绍一个小技巧——使用自定义格式实现隐藏Excel单元格数据。

首先，选择要隐藏数据的单元格。
接着，调出“设置单元格格式”对话框，选择“数字”选项卡，单击左侧分类中的“自定义”，在右侧“类型”中输入自定义格式“;;;”，单击“确定”。

图：设置自定义格式
此时，在Excel工作表中，将看不到单元格中的数据，但是选择单元格后，在编辑栏中仍然可以看到其内容。