完美Excel

又是暂新的一年！
在这里，给大家送上迟到的祝福：Happy New Year！
找到了Andy Pope制作的两个Excel工作簿，与大家分享Excel带来的乐趣。

• 显示“Happy New Year”的LCD屏。

如下图：

使用标准的XY图表构造，通过工作表公式计算图表数据，通过滚动条人工控制显示屏移动，也可以使用VBA自动控制移动。也可以通过Alphabet工作表构造自已的字体形状。
原工作簿下载：

原文链接：http://www.andypope.info/fun/pixilate.htm

• 在工作表中欣赏“焰火”。

如下图：

用户能够控制发射焰火的数量，以及火花的数量和重力的影响。这也是通过使用XY图表实现的。
原工作簿下载：

原文链接：http://www.andypope.info/fun/fireworks.htm
哈哈……过瘾吧！这又一次验证了：实现不是问题，就怕你没有想象力和创造力。

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

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

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

在工作中，零星工程项目是最令人头疼的，又杂又乱，项目繁多且大小不一，一直不好管理。这几天，试着利用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》。

条件格式的新功能也扩展到了对象模型。Excel通常允许用户通过对象模型访问单元格的格式，例如用户可以使用Range对象的interior类访问单元格的格式。

Selection.Interior.Color

返回单元格的背景色。
然而，Selection.Interior.Color不会考虑应用到单元格的条件格式。在Excel 2010中，引入了名为DisplayFormat的新类，属于Range对象。这个类提供了一种访问由条件格式规则和像表样式格式单元格那样应用到的格式的方式，而不会影响到已有的单元格格式属性。
例如，下面的代码获取活动单元格的填充色（在考虑到应用到单元格的任何条件格式之后）：

Selection.DisplayFormat.Interior.Color

让我们看一个如何使用DisplayFormat对象的简单示例。一个书店基于销售目标的程度如何给它的雇员提供红利，红利按下列方式发放：

• 15%红利——销售目标满足了本年每季度的目标。
• 5%红利——销售目标满足了本年至少一个季度的目标。
• 没有红利——没有满足任何销售目标。

在每年开始，书店汇总每个雇员的销售目标如下：

然后，书店在单独的工作表中记录实际的销售并使用条件格式高亮显示雇员没有满足销售目标的季度。这些季度使用如下所示的红色背景高亮显示：

由于红利在每年末取决于销售人员满足其目标来发放，因此可以设想计算红利百分比的公式将相当复杂。使用新的DisplayFormat对象，可以容易地编写自已定义的函数，利用条件格式规则中的智能来计算红利。
本例中，计算雇员红利的函数如下：

Function BonusAward(quarters As Range) As Double
    Dim iBelowTarget As Integer ' 在目标之下的单元格数
    ' 初始化变量
    BonusAward = 0.15 '默认15%红利-满足所有目标
    iBelowTarget = 0
    ' 遍历每个单元格
    For Each qCell In quarters
        '如果条件格式单元格背景不是红色(255)
        If qCell.DisplayFormat.Interior.Color > 255 Then
            BonusAward = 0.05 ' 发放更少的红利 (failed quarter)
            iBelowTarget = iBelowTarget + 1
        End If
    Next qCell
    ' 如果所有的季度都在目标之下则发放零红利
    If iBelowTarget = quarters.Cells.Count Then BonusAward = 0
End Function

DisplayFormat对象使用许多已存在的类和属性，例如Borders、Characters、Font、Interior和Style，提供了对条件格式结果的访问。由于使用现有的类和属性，因此意味着可以容易地扩展现有的代码来使用它。

本文初译自Excel Team Blog，仅供参考。

下面介绍Excel 2010中一些新的条件格式功能：

• 交叉表引用
• 健壮的错误处理
• 性能增强

交叉表引用
在Excel 2010中，可以创建引用工作簿中不同工作表的条件格式。这意味着，在使用条件格式时，不再需要复制或链接数据到同一工作表。
例如，看看某连锁书店每月销售数据，该书店采用如下方式组织销售数据：

• 每个位置有自已的工作表
• 最后一个工作表包含全公司累计销售数据，使用每个部门的平均值。

下面我们应用条件格式，以便能够容易判断是否位于本地分支机构的部门与该公司其它分支机构相比做得更好或更差。让我们使用San Francisco分支机构作为我们的示例。我们将应用绿色背景填充那些比平均值高的部门。
步骤1 打开San Francisco所在的工作表，选择希望应用条件格式的销售列中的单元格。单击“开始—条件格式—高亮单元格规则—大于”。

步骤2 单击对话框中RefEdit图标，导航到希望比较其中的值的工作表。本例中，导航到全公司工作表，单击部门列中第一行的平均值。

步骤3 下面是一个极好的小技巧——条件格式中的引用遵循所有的绝对和相对引用规则。单元格引用相对于所应用到的单元格区域中的活动单元格（本例中是单元格区域的左上单元格）。
删除行号前的$符号，修改引用为相对于行。

结果是在San Francisco工作表中的每个部门与Company工作表中相同类型的部门相比较。
步骤4 单击“确定”，建立了一个取决于工作簿中其它工作表的条件格式规则。

错误处理
你曾经使用过发现某个公式结果为错的基于条件格式的区域（例如图标集、色阶和数据条）吗？在Excel 2007中，单元格区域中的错误将导致该区域的所有单元格都不能应用条件格式。
在Excel 2010中，条件格式悄悄地忽略了这些错误，继续应用条件格式到该区域中剩下的单元格。

性能增强
在Excel 2010中已经提高了格式的性能。对于条件格式，我们存储所使用的相互依赖的公式，因此不需要经常重新评估整个条件格式。我们也使带状的表和数据透视表样式更快，因此这将影响诸如刷新工作表中数据透视表或滚动大的表等操作。在上述所有情况下，我们也缓存了单元格的格式，因此如果滚动关闭屏幕并返回，或者切换工作表并返回，我们将记住单元格格式，可以显示得更快。

原文见Excel Team Blog。