又到年底冲业绩的时候了，各个公司的销售和总部管理人员都在寒风中加班加点跑业务。


于是就有了今天的求助问题：排班太多太密集，肉眼看不过来了，需要根据要求自动标识一下颜色。


案例：


下图 1 是某公司的业务员巡店排班表，需要按以下要求自动标识不同颜色：

  • 如果人名在表格中第一次出现，则整行字体自动显示为红色；
  • 如果人名在表格中最后一次出现，则整行字体自动显示为绿色。


效果如下图 2 所示。


解决方案：


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


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

=COUNTIF($B$2:$B2,$B2)=1


公式释义：

  • COUNTIF($B$2:$B2,$B2)：
    • 计算 $B2 单元格的值在区域 $B$2:$B2 中是第几次出现；
    • 参数中的第一个 $B$2 必须是绝对引用，因为这是统计区域的起始单元格，不会变；
    • 后面两个 $B2 的行值都必须是相对引用，这样才能活动地依次向下统计每一行名字是第几次出现，即从起始单元格到当前行区域内的出现次数
  • ...=1：如果上述值等于 1，即名字第一次出现，则满足触发条件


3. 在弹出的对话框中选择“字体”选项卡 --> 选择红色作为字体颜色 --> 点击“确定”


4. 点击“确定”


现在可以看到，表格中所有第一次出现的人名的整行字体都变成了红色。


接下来继续设置最后一次出现的人名颜色。


5. 再次选中 A2:C25 区域 --> 选择菜单栏的“开始”-->“条件格式”-->“新建规则”


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

=(COUNTIF($B:$B,$B2)=COUNTIF($B$2:$B2,$B2))*($B2<>"")


公式释义：

  • COUNTIF($B:$B,$B2)：
    • 计算 $B2 单元格的值在整个 B 列中出现的总次数；
    • $B2 的行值需要相对引用，这样才能依次计算每一行
  • COUNTIF($B$2:$B2,$B2)：前面已经解释过，统计 $B2 单元格的值在区域内是第几次出现
  • 上述两段公式相等，即表示某一次出现的次数，等于出现的总次数，那就是最后一次出现了
  • *($B2<>"")：表示如果姓名为空，则不考虑在内


7. 在弹出的对话框中选择“字体”选项卡 --> 选择绿色作为字体颜色 --> 点击“确定”


8. 点击“确定”


最终就完成了我们需要的设置。


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

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