Excel中的条件格式.doc

Excel中的条件格式 假如要在一个包含大量数据的Excel工作表中查找某些符合给定条件的内容，让Excel帮助查找将比人工查找快速和准确地多。通常的做法是用Excel内置的菜单，通过“编辑”“查找”来找到想要的内容（见图1），但这种查询方法功能有限，无法实现如下的功能：（图1）1、同时找到所有匹配的单元格。该查询功能一次只能找到一个单元格，如果选择“查找全部”，Excel仍然不能选定所有找到的单元格，只是给出了一个所有找到的单元格的地址列表，只能根据给出的单元格地址自已在工作表中查找。2、把找到的结果保留地长久一些，如对单元格永久性地突出标注。当“查找和替换”对话框关闭后这些单元格地址将消失，不留下任何痕迹。当然可以手工为这些找到的单元格设置和其它单元格不相同的格式，但是当表格中有许多这样的单元格时，需要一个个地设置格式，效率非常低。如果要实现上面的两项功能，要用到最有效的查询方法-“条件格式”。可以为工作表定义一个条件格式以突出显示满足给定条件的所有单元格。下面通过一个例子，一个从CD中搜集的歌曲的列表，由浅入深地介绍条件格式的用法。 （表）一、查找精确匹配需要在一个单元格中输入要查询的值，本例为B1单元格，然后按照下面的步骤设置条件格式：1选择列表中的所有单元格（A4：C11），在菜单中选择“格式(O)”“条件格式(D) .” （图2） 2在下拉菜单中选择“单元格数值”和“等于”,在最后的输入栏中选择B1单元格,或手工输入地址：=$B$1，注意不要漏掉“=”（如下图)。 （图3） 3点击“格式(F).”, 选择匹配的单元格以什么样的格式显示，本例为粗体加红色背景，操作方法是：在出现的“单元格格式”对话框中，在“字形”下选择“加粗”，再选择“图案”标签，选择红色，点击“确定”，这时在图3中显示“未设定格式”的地方将以粗体和红色背景的格式显示字符“AaBbCcYyZy”，点击“确定”关闭“条件格式”对话框。 如果在B1单元格输入要查找的内容，列表中所有匹配的单元格将按刚才设置的条件格式显示，改变B1单元格的内容，列表中匹配当前值的单元格以条件格式标出，其它单元格恢复原先格式（如下表）。 （表） （表）二、查找部分匹配上例中，只能查找和B1中的内容完全匹配的单元格，比如要查找Bee Gees的歌曲，找到的结果是不完全的，Bee Gees和Celine Dion二重唱不会被找到，因为它和B1中的内容不完全匹配。遗憾的是，条件格式没有 “包含单元格”之类的选项，但是却可以使用函数及公式。FIND函数可以用来查找子字符串，因此我们可以用以下的方法来进行部分匹配查询。1选择列表中的第一个单元格（本例为A4，见图2），在菜单中选择“格式(O)”“条件格式(D) .”。2在下拉菜单中选择 “公式”, 在公式栏内输入下面的公式：=AND(FIND($B$1,A4)0,$B$1)，再通过上面讲述的方法设置格式为粗体和背景为红（如下图)，点击“确定”。 （图4） 在使用条件格式的公式时，只需要公式的计算结果为TRUE或FALSE，结果为TRUE时给单元格设置格式，为FALSE时不设置（Excel用0表示FALSE，而大于0的数值均可表示TRUE）。公式中的 FIND($B$1,A4)返回$B$1在A4中出现的位置，所以如果$B$1被A4包含， FIND($B$1,A4)始终大于0，即值为TRUE。公式中的$B$1表示要查找的内容所在的单元格地址，A4表示当前单元格的地址，即在其中建立条件格式的单元格。因为要查找的内容为空时FIND函数始终返回1，如FIND(,abc)=1，所以还要确定$B$1的值不为空，否则$B$1未输入任何内容时整个列表将被设置格式。最后AND函数把两个条件判断结合起来，当两个条件判断值都为TRUE时单元格被设置格式。3现在把A4中的格式复制到列表的所有其它单元格中。注意和绝对地址$B$1相反，A4为相对地址，当把公式拷贝到其它单元格后，A4将相应变为公式所在的单元格地址。选择A4，在菜单中选择“编辑(E)”“复制(C)”。选择整个列表, 在菜单中选择“编辑(E)” “选择性粘贴(S) .”。在出现的窗口中选“格式”，点击“确定”。 （图5） 遗憾的是，不能只复制A4中的条件格式，而是把它所有的格式都复制了，因此你可能要对复制后的列表格式进行调整，以恢复原先的格式： （表4）如果在B1中输入内容，列表中所有包含此内容的单元格将被以设定的格式显示： （表5）三、使用通配符查找你可能发现FIND函数对大小写字母敏感，如果在B1中输入“bee gees”将找不到任何东西，该函数也不能使用通配符。如果要使用这些功能，应该用SEARCH函数代替FIND函数。1选择列表中的第一个单元格（本例为A4，见图2），在菜单中选择“格式(O)”“条件格式(D) .”，在出现的条件格式对话框中将公式改为：=AND(SEARCH($B$1,A4)0,$B$1)。2将格式拷贝到列表的其它单元格（“复制(C)”“选择性粘贴(S) .”），然后如表4所示调整格式。如果操作正确，将成功查找到“bee gees”（查询效果见表5，不过B1中输入的是“bee gees”）。还可以使用通配符，一个“?”代表一个单独的字符，一个“*”代表多个字符。如果要查找一个“?”或“*”符，要在前面加上 一个“”符。 （表6）四、标注整行你或许想不止把找到的单元格以特殊格式标注，而是把单元格所在的整行都标注出来，这也是可以实现的。这需要将公式进行修改，变检查单个单元格为检查单元格所在的行。1选择列表中的第一个单元格（本例为A4，见图2），在菜单中选择“格式(O)”“条件格式(D) .”，在出现的条件格式对话框中将公式改为：=AND(NOT(AND(ISERROR(SEARCH($B$1,$A4),ISERROR(SEARCH($B$1,$B4),ISERROR(SEARCH($B$1,$C4),$B$1)SEARCH (或 FIND)函数在当要查找的字符串未找到时返回“错误”（“错误”和FALSE是不同的），所以在上面的公式中SEARCH($B$1,A4)执行成功返回TRUE，执行不成功返回“错误”。因为单元格在条件格式结果为TRUE时以特殊格式标注，结果为其它时不标注，所以当结果为“错误”而非FALSE时不影响条件格式的执行效果。但是当用AND (或 OR)函数将几个逻辑值组合起来，必须保证这几个逻辑值的结果为TRUE或 FALSE而不能为“错误”。所以我们用ISERROR函数来判断SEARCH函数的结果。因为ISERROR函数在SEARCH执行出错（字符串未找到）时返回TRUE，未出错（找到字符串）时返回FALSE，必须用NOT函数对结果求反。请注意在其中进