Excel 2007条件格式进阶应用两则

【 tulaoshi.com - Excel教程 】

在Excel应用条件格式可以让符合特定条件的单元格数据以醒目的方式突出显示出来，便于我们对工作表数据进行更好地分析。通常我们只要选中相应的单元格，然后设置条件格式，就可以达到目的。但是如果我们需要设置格式的单元格是根据别的单元格条件来设置呢?或者我们要设置的条件比较复杂呢?

图1：单元格

以图1所示的工作表为例。工作表中A2:J1032为某市一次大型拉练考试的成绩表。各学科成绩分别在C至G列，总分成绩在H列，而学校名称和专业名称分别在I列和J列。

一、突出显示指定学校的学生成绩

现在成绩表已经根据英语学科按从高到低降序排序。为了更方便比较，我们希望能把化工学校的学生所在的行用不同的颜色区别出来。在不改变现有排序、也不做筛选的情况下，用条件格式是最方便的了。

选中A2单元格，点击功能区开始选项卡样式功能组中的条件格式按钮，在弹出的菜单中点击新建规则命令，打开新建格式规则对话框。在选择规则类型列表中点击使用公式确定要设置格式的单元格项目，然后在下方的为符合此公式的值设置格式输入框中输入公式=$I2="化工学校"，然后再点击下方的格式按钮，在打开的对话框中设置单元格的填充颜色，如图2所示。点击确定关闭对话框。

图2：新建格式

再选中A2单元格，然后点击功能区开始选项卡剪贴板功能组中的格式刷工具，把A2:J1032的所有单元格依次刷一遍，复制A2单元格的格式至所有单元格，呵呵，看到了吧?刷到之处，凡是化工学校的学生所在行都填充了我们所指定的颜色。很容易吧?

用这种办法，我们还可以设置别的什么条件来突出显示某一部分学生成绩。比如，我们可以将公式改成=$H2=600来突出显示那些总分超过600分的学生。

二、突出显示一定比例的学生

分析学生成绩时经常要看一下总分或某学科名列前茅的前20%的学生成绩分布，那么我们就会希望能把这些学生的相应成绩突出显示出来。这项工作如果针对某学科用RANK函数排出名次，再进行排序，再计算出前20%的学生人数，然后再对符合条件的成绩填充颜色，也不是不能完成。但是这肯定太麻烦。更何况有多个学科、总分等成绩，都这么来一遍的话，那头都会大了。还有呢，肯定会有名次相同的情况的，这在确定前20%的人数时也是需要小心对待的。

但如果我们把这个工作交给条件格式来做的话，那问题就简单多了。

比如我们要对总分列(H2:H1032)前20%的学生成绩填充颜色。而我们又是在Excel 2007中完成这项工作的话，那么我们就选中H2:H1032单元格区域，然后点击功能区中的条件格式按钮，然后在弹出的菜单中点击项目选取规则→值最大的10%项命令，打开10%最小的值对话框。这对话框的名称有点乱，呵呵，不知道是不是Excel 2007的Bug?不管它了，如图3所示，在对话框左侧的调节框中将比例值调整为20%，然后我们可以在右侧的下拉列表中选择自定义格式，然后在打开的对话框中为单元格指定格式。确定后就可以立刻将前20%的高分学生成绩突出显示出来了。

图3：比例值设置

如果需要其它学科也同样突出前20%的高分学生，那么也只需要用格式刷将此格式刷到其它学科成绩上去即可。

如果说我们使用是Excel 2007以前的版本，那也不要紧。选中H2:H1032单元格区域后，点击菜单命令格式→条件格式，打开条件格式对话框。如图4所示，在左侧的下拉列表中选择公式，然后在右侧的输入框中输入公式=LARGE(H$2:H$1032,INT(COUNT(H$2:H$1032)*20%))=H2，再点击格式按钮，设置好单元格格式，确定后就可以了。

图4：设置条件格式

上面的公式看起来复杂，其实说白了也很简单，首先中间的INT(COUNT(H$2:H$1032)*20%)部分是对整个数据区域求出20%的数据个数并且向下取整数。然后我们用LARGE函数求到了在此区域中前20%中最小的一个数据，拿这个数值与当前的单元格数据作比较。只要它小于当前单元格数据，那么该单元格当然是名列前20%的喽。