九乡新闻网自学学英语方法:：Excel 2007条件格式进阶应用两则
来源：百度文库 编辑：九乡新闻网 时间：2024/04/25 13:16:00
Excel:用DSUM函数完成指定条件求和
以图1所示的工作表为例。工作表中A2:J1032为某市一次大型拉练考试的成绩表。各学科成绩分别在C至G列，总分成绩在H列，而学校名称和专业名称分别在I列和J列。
一、突出显示指定学校的学生成绩
现在成绩表已经根据英语学科按从高到低降序排序。为了更方便比较，我们希望能把“化工学校”的学生所在的行用不同的颜色区别出来。在不改变现有排序、也不做筛选的情况下，用条件格式是最方便的了。
选中A2单元格，点击功能区“开始”选项卡“样式”功能组中的“条件格式”按钮，在弹出的菜单中点击“新建规则”命令，打开“新建格式规则”对话框。在“选择规则类型”列表中点击“使用公式确定要设置格式的单元格”项目，然后在下方的“为符合此公式的值设置格式”输入框中输入公式“=$I2="化工学校"”，然后再点击下方的“格式”按钮，在打开的对话框中设置单元格的填充颜色，如图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%的高分学生成绩突出显示出来了。
如果需要其它学科也同样突出前20%的高分学生，那么也只需要用“格式刷”将此格式“刷”到其它学科成绩上去即可。
如果说我们使用是Excel 2007以前的版本，那也不要紧。选中H2:H1032单元格区域后，点击菜单命令“格式→条件格式”，打开“条件格式”对话框。如图4所示，在左侧的下拉列表中选择“公式”，然后在右侧的输入框中输入公式“=LARGE(H$2:H$1032,INT(COUNT(H$2:H$1032)*20%))<=H2”，再点击“格式”按钮，设置好单元格格式，确定后就可以了。
上面的公式看起来复杂，其实说白了也很简单，首先中间的INT(COUNT(H$2:H$1032)*20%)部分是对整个数据区域求出20%的数据个数并且向下取整数。然后我们用LARGE函数求到了在此区域中前20%中最小的一个数据，拿这个数值与当前的单元格数据作比较。只要它小于当前单元格数据，那么该单元格当然是名列前20%的喽。