完美Excel

Excel中的条件格式功能是一项十分强大且便利的功能。使用条件格式，可以在很大程度上改进电子表格的设计和可读性，允许指定多个条件来确定单元格的行为，根据单元格的内容自动地应用单元格的格式。当然，可以针对单元格区域设定多个条件，但Excel只会应用一个条件所对应的格式，即按顺序测试条件，如果该单元格满足某条件，则应用相应的格式规则，而忽略其他条件测试。
（除特别说明外，以下内容采用Excel 2003版本）
在使用条件格式时，首先选择要应用条件格式的单元格或单元格区域，然后单击菜单“格式─条件格式”，出现如图1所示的“条件格式”对话框。在Excel 2003及以前的版本中，条件格式最多只能设置三个条件。

图1：选择“单元格数值”时的条件格式对话框
此时，若在第一个组合框中选择“单元格数值”，则右侧的组合框中将提供“介于”、“未介于”、“等于”、“不等于”、“大于”、“小于”、“大于或等于”、“小于或等于”等选项，并且在其右侧的输入框中可以输入相应的数值，也可以选择工作表中的单元格。然后，单击“格式”按钮，设置当条件为真时所应用的格式。
其中，选择“介于”时，包括设置的最大值和最小值，而选择“未介于”时，不包括设置的最大值和最小值。
若在第一个组合框中选择“公式”，则“条件格式”对话框如图2所示。

图2：选择“公式”时的条件格式对话框
此时，可在右侧的输入框中输入公式或者选择含有公式的单元格。注意，公式的值必须返回True或False。当公式返回True时，将应用条件格式；否则，不会应用设定的格式。这也从另一个侧面可以看出，对Excel公式与函数掌握的熟练程度，有助于灵活运用条件格式。换句话说，正是由于公式的强大功能，使得条件格式才能发挥其魅力。
关于条件格式，请注意下面的几点：

• 复制单元格并将其粘贴到包含条件格式的单元格或者单元格区域中，将会删除该单元格或单元格区域中的条件格式，Excel不会给您任何警告信息。如果非得使用粘贴，并且要保留条件格式，那么可使用“选择性粘贴”功能。
• 当复制一个包含条件格式的单元格时，将同时复制该单元格的条件格式。在包含条件格式的单元格区域中插入行或者列时，在新的单元格中将有相同的条件格式。
• 如果要删除条件格式，仅在含有条件格式的单元格中按Delete键，不会删除条件格式。要删除条件格式，需要使用“编辑─—清除─—格式”命令或者“编辑─—清除─—全部”命令。还可以使用“条件格式”对话框，删除其中的条件。
• 复制某单元格到含有条件格式的单元格中，也将清除条件格式。
• 要快速查看所有包含条件格式的单元格，使用Excel的“定位”对话框，单击“定位条件”按钮，在“定位条件”中选择“条件格式”选项，如图3所示。
  
  图3：利用“定位条件”中的“条件格式”选项选择所有包含条件格式的单元格

下面是《Escape From Excel Hell》一书中关于条件格式的两个示例，供参考。
示例1：在条件格式中使用公式且公式引用另一个单元格中的内容，如图4所示。

图4：条件公式中引用另一个单元格中的内容
对单元格区域C6：D20应用条件格式，三个条件公式的含义分别为，其左侧B列相应单元格的值若大于且等于1，则单元格底纹为粉红色且字体加粗；若大于0，则单元格底纹为靛蓝色；若小于且等于0，则单元格底纹为浅灰色且字体为斜体。
示例2：在条件中使用其他工作表或工作簿中的内容
在应用条件格式时，通常不能直接引用其他工作表或工作簿，但如果为需要引用的单元格区域定义了名称，那么可以在条件中通过名称来引用其他工作表或工作簿中的内容。如下图5所示。

图5：通过定义名称，从而在条件格式中引用另一工作表中的数据
如图5，在工作表SearchAnExternalWorksheet中内容为“Trans 01”的单元格中应用了条件格式，在条件公式中引用了另一个工作表LookupTable中名称为MyLookupTable的单元格区域中的内容，如果相应的收益（损失）值大于1000则该单元格内容使用绿底斜体字显示，若小于-1000则该单元格内容使用红底粗体字显示。
还有一种方法来引用其他工作表中的单元格。例如在需要使用条件格式的工作表中的某单元格中输入公式来引用其他工作表中的单元格，然后在条件格式中引用此单元格。
下面借用自已在学习中看到的关于条件格式的一些示例，对条件格式作进一步详细的介绍。
示例3：标识包含文本的单元格
如图6所示，在单元格区域B2：C4中包含数字和文本，现在要对该区域中的文本应用特定的格式，以标识包含文本的单元格。选择B2：C4，单击“格式─—条件格式”，在“条件格式”对话框最左侧的组合框中选择“公式”，在右侧输入公式“=ISTEXT(B2)”，单击“格式”，设置字体为粗体，底纹为灰色。

图6：对包含文本的单元格应用格式
如果要对不同的单元格区域应用该条件格式，那么ISTEXT函数的参数应该是该区域左上角的单元格。
示例4：标识两个列表中不相同的数据
有时，要找出两个列表区域中不相同的数据，并将该数据所在的单元格标识出来。如图7所示，有两个列表区域A1：B12和D1：E12，现在要求找出区域D1：E12中与区域A1：B12有不同数据的单元格并将该单元格标记为红色底纹。

图7：需要比较的两个列表区域
现在，按下列步骤来完成上述要求：
第1步：将单元格区域A2：B12命名为OldData。
第2步：选择单元格区域D2：E12，单击菜单“格式——条件格式”。
第3步：在“条件格式”对话框中，在“条件”组合框中选择“公式”，在右侧框中输入公式“=COUNTIF(OldData,D2)=0”。单击“格式”按钮，在弹出的对话框中选择图案为红色，如图8所示。

图8：设置条件格式
第4步：单击“确定”按钮，此时如图9所示，该列表中与左侧列表含有不同的数据的单元格被标上红色底纹。

图9：条件格式应用后，标识出不同的单元格
有时，可能列表在不同的工作表中，如图10所示，要找出工作表Sheet2中与区域OldData中不同的数据单元格。与上述步骤3相同，选择工作表Sheet2中的单元格区域A2:B12，调出“条件格式”对话框，并作如图10所示的设置。

图10：选择区域A2：B12，设置条件格式
单击“确定”按钮后，效果如图11所示，标识出了与工作表Sheet1中区域OldData不同的数据。这也充分展示了定义名称的好处。

图11：应用条件格式后，为与OldData区域不同的数据单元格标上底纹
当然，本示例只是演示，数据很少。如果要比较大量的数据，光凭人工查找核对，费时费力且易出错，通过应用条件格式，将能够很快达到要求。
示例5：绘制进度图表
灵活使用Excel的条件格式，可以绘制简单的工作进度图表。例如，要绘制如图12所示的进度图，其中开始和结束分别代表项目开始和结束的周。

图12：完成后的进度图效果。在项目之间增加了空行，使图示更清晰。
完成图12的步骤如下：
第1步：在工作表中输入数据，并进行相应的格式化操作。
第2步：选择单元格区域D3：O15，单击“格式——条件格式”。
第3步：在“条件格式”对话框中选择“公式”并输入下列公式：
=AND(D$2>=$B3,D$2<=$C3)
然后，单击“格式”按钮，应用相应的格式，如图13所示。

图13：设置条件格式
单击“确定”按钮后，完成最终的进度图如图12所示。此时，如果在工作表中更改开始和结束时间，进度条会相应的更改。
示例文档下载：ConditionalFormatByGanttChart.xls
如果需要为不同项目的进度条应用不同的格式或颜色，则需要使用Excel 2007。在Excel 2007中提供了更为丰富的条件格式选择和更优美的界面，我们将详细探讨。这里，利用Excel 2007，为图12所示的进度图制作更丰富的效果。最终效果如图14所示。

图14：设置具有不同颜色的进度条
完成图14的步骤如下：
第1步：在Excel 2007工作表中输入数据，并进行相应的格式化操作。
第2步：选择单元格区域D3：O3。在“开始”选项卡中选择“条件格式——管理规则”，打开“条件格式规则管理器”对话框。单击“新建规则”按钮，打开“新建格式规则”对话框。选择“使用公式确定要设置格式的单元格”，在“为符合此公式的值设置格式”中输入公式：
=AND($B3<=D$2,$C3>=D$2)
单击“格式”按钮，在“填充”选项卡中选择红色，单击“确定”按钮关闭“设置单元格格式”对话框。单击“确定”按钮关闭“新建格式规则”对话框，此时的对话框如图15所示。

图15：设置规则
第3步：对单元格区域D5：O5、D7：O7、D9：O9、D11：O11、D13：O13、D15：O15分别重复第2步的操作。但是，在输入公式时要针对具体的行进行修改，并对格式填充颜色进行相应的更改。
完成后的进度图如图14所示。
示例文档下载：ConditionalFormatByGanttChart.xlsx
最后，介绍一个在自已实际工作中，使用Excel条件格式的一个例子。
在一个工作表中，有许多条记录，如何使Excel自动标识出满足特定条件的记录呢？
例如：下图所示的工作表中有多条记录，要求当字段“是否完成”列中单元格数据为“否”时，突出标识该行（本例中将该行突出显示为红色），使用户清楚没有完成的工作；当为“是”时，突出标识的背景色自动消失。
现在开始实现这样的效果，先看看下图：

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

此时，在E列中单元格的数据改为“是”时，该行的红色底纹会自动消失；如果为“否”，则该行会自动加上红色底纹。
示例文档下载：AutoConditionalFormat.xls
在Excel 2007中，Microsoft对条件格式功能进行了扩展，使得条件格式功能更为强大，使用更加方便，我以后会陆续与大家分享。