6 个科目有任意 3 门不及格就留级，Excel 自动判断且标红

这是读者的一个求助案例，之所以分享给大家是觉得比较经典，不仅涉及到公式和条件格式的协同使用，而且本例中的公式其实有多种解法，借此带大家再温习一下一些函数的用法。

案例：

下图 1 是全班同学的各科考试成绩，一共 6 个科目，要求如下：

不及格科目达到 3 个及以上就留级，小于 3 个则升级

在 H 列自动计算出升级还是留级，留级的字体用红色显示

效果如下图 2 所示。

解决方案 1：text+countif 函数

1. 在 H2 单元格中输入以下公式 --> 下拉复制公式：

=TEXT(COUNTIF(B2:G2,"2]留;[

公式释义：

COUNTIF(B2:G2,"

TEXT(...,"[>2]留;[

text 函数此处的用法相当于在“设置单元格格式”中自定义格式类型，因此最多只能设置 4 种类型，当中用三个英文半角的“;”隔开

当不及格单元格个数 >2 个时，返回“留”字，

&"级"：用“&”符号将上一步返回的文字与“级”字连接起来，最后显示“留级”或“升级”

有关 text 函数的详解，请参阅

2. 选中 H2:H19 区域 --> 选择菜单栏的“开始”-->“条件格式”-->“新建规则”

3. 在弹出的对话框中选择“使用公式确定要设置格式的单元格”--> 在公式区域输入以下公式 --> 点击“格式”按钮：

=$H2="留级"

* 公式中的行号需要设置为相对引用，这样才会在选定区域内动态读取对应的行。

4. 在弹出的对话框中选择“字体”选项卡 --> 将字体设置为红色 --> 点击“确定”

5. 点击“确定”

现在就实现了本案例的需求。

解决方案 2：if+countif 函数

1. 在 H2 单元格中输入以下公式 --> 下拉复制公式：

=IF(COUNTIF(B2:G2,"

公式释义：

这个公式跟上一个解决方案类似，只是将 text 函数换成了 if，用于判断不及格的单元格个数是否大于 2，为真则返回“留”，为假返回“升”

最后仍然用“&”符号将返回的文字与“级”字连接起来，显示“留级”或“升级”

由于上一个解决方案中已经设置过条件格式，所以此处不需要再重复设置，下拉公式后即自动实现字体变色效果。

Excel学习世界

转发、在看也是爱！