excel怎么筛选出想要的数据标红(excel筛选不及格门数用哪个函数)

这是读者的一个求助案例,之所以分享给大家是觉得比较经典,不仅涉及到公式和条件格式的协同使用,而且本例中的公式其实有多种解法,借此带大家再温习一下一些函数的用法。案例:下图 1 是全班同学的各科考试成绩,一共 6 个科目,要求如下:不及格科目达到 3 个及以上就留级,小于 3 个则升级在 H 列自…

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

案例:

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

  • 不及格科目达到 3 个及以上就留级,小于 3 个则升级
  • 在 H 列自动计算出升级还是留级,留级的字体用红色显示

效果如下图 2 所示。


excel怎么筛选出想要的数据标红(excel筛选不及格门数用哪个函数)


excel怎么筛选出想要的数据标红(excel筛选不及格门数用哪个函数)

解决方案 1:text+countif 函数

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

=TEXT(COUNTif(B2:G2,"<60"),"[>2]留;[<3]升")&"级"

公式释义:

  • COUNTIF(B2:G2,"<60"):统计 B2:G2 区域中,不及格的单元格个数
  • TEXT(…,"[>2]留;[<3]升"):
    • text 函数此处的用法相当于在“设置单元格格式”中自定义格式类型,因此最多只能设置 4 种类型,当中用三个英文半角的“;”隔开
    • 当不及格单元格个数 >2 个时,返回“留”字,<3 个则则返回“升”字
  • &"级":用“&”符号将上一步返回的文字与“级”字连接起来,最后显示“留级”或“升级”

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


excel怎么筛选出想要的数据标红(excel筛选不及格门数用哪个函数)


excel怎么筛选出想要的数据标红(excel筛选不及格门数用哪个函数)


excel怎么筛选出想要的数据标红(excel筛选不及格门数用哪个函数)

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


excel怎么筛选出想要的数据标红(excel筛选不及格门数用哪个函数)

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

=$H2="留级"

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


excel怎么筛选出想要的数据标红(excel筛选不及格门数用哪个函数)

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


excel怎么筛选出想要的数据标红(excel筛选不及格门数用哪个函数)

5. 点击“确定”


excel怎么筛选出想要的数据标红(excel筛选不及格门数用哪个函数)

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


excel怎么筛选出想要的数据标红(excel筛选不及格门数用哪个函数)

解决方案 2:if+countif 函数

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

=IF(COUNTIF(B2:G2,"<60")>2,"留","升")&"级"

公式释义:

  • 这个公式跟上一个解决方案类似,只是将 text 函数换成了 if,用于判断不及格的单元格个数是否大于 2,为真则返回“留”,为假返回“升”
  • 最后仍然用“&”符号将返回的文字与“级”字连接起来,显示“留级”或“升级”


excel怎么筛选出想要的数据标红(excel筛选不及格门数用哪个函数)


excel怎么筛选出想要的数据标红(excel筛选不及格门数用哪个函数)

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


excel怎么筛选出想要的数据标红(excel筛选不及格门数用哪个函数)

很多同学会觉得 Excel 单个案例讲解有些碎片化,初学者未必能完全理解和掌握。不少同学都希望有一套完整的图文教学,从最基础的概念开始,一步步由简入繁、从入门到精通,系统化地讲解 Excel 的各个知识点。

现在终于有了,以下专栏,从最基础的操作和概念讲起,用生动、有趣的案例带大家逐一掌握 Excel 的操作技巧、快捷键大全、函数公式、数据透视表、图表、打印技巧等……学完全本,你也能成为 Excel 高手。

趣懂得网所有文章资讯、展示的图片素材等内容均为注册用户上传(部分报媒/平媒内容转载自网络合作媒体),仅供学习参考。用户通过本站上传、发布的任何内容的知识产权归属用户或原始著作权人所有。如有侵犯您的版权,请联系我们1875979182@qq.com 反馈 本站将在三个工作日内改正。

发表评论

您的电子邮箱地址不会被公开。