点击蓝字【秋叶 Excel】

发送【礼包】

免费领办公神器、Office 模板和免商字体！

本文作者：小花

本文审核：玛奇鹅

本文编辑：竺兰

大家好，我是 Excel 护体光速下班的效率达人，小花。

近日，小花意外淘到一种仿真交通灯，颇为新奇，自觉有趣得紧，便忍不住要与诸位分享。

交通灯通常用来表示某一指标的完成状态、重要与否或紧急程度，它在运营管理中运用广泛。

传统的交通灯有两种常用的设置方法， 一是通过自定义数字格式来完成， 另一种则是使用条件格式。

它们都是在单元格里显示不同颜色的圆形图案来表示不同的状态。

然而，上图中的交通灯却别出心裁， 使用图片+文本框+特殊字体法来设置， 虽然略显复杂，却颜值大涨，想必也会颇受欢迎。

这三种交通灯的设置方法具体该如何操作呢？不急，且看小花逐一拆解。

更多图表相关的 Excel 小技巧：如何选择图表、如何实现想要的图表效果……都在《秋叶 Excel 3 天集训营》，原价 99 元，今天免费加入！

报名就送100 套职场必备 Excel模板，参加直播课还有机会领取价值 156.9 元的学习福利~

自定义数字格式法

通过自定义数字格式，可以直接对单元格内的数值进行判断，根据不同的条件显示不同颜色的圆形。

以下图为例，我们只需选中单元格后按【Ctrl+1】即可快速打开自定义单元格【对话框】，随后在【类型】一栏中输入如下内容，即可完成交通灯设置。

[红色][<0]●;[黄色][<30]●;[绿色]●;[颜色 5]@

它表示，逾期未完成事项亮红灯、30 天以内需完成事项亮黄灯，30 天以后需完成事项亮绿灯，文本颜色使用调色板第五种颜色，即蓝色。

使用这种方法，考验的是小伙伴们对自定义数字格式的知识储备，主要是以下两点：

❶自定义带条件的数字格式

数字格式有四个区段，其中前三个是数值的格式，最后一个是文本的格式。

我们可以通过数值条件判断，设置不同的数值格式，即将数字格式设置成如下格式：

[条件 1]格式 1：[条件 2]格式 2;格式 3;文本格式

它的含义是，满足条件 1，则将数值设置为格式 1： 满足条件 2，则将数值设置为格式 2： 都不满足，则设置为格式 3。

文本适用第四种格式。

不难看出，常规的「正数的格式;负数的格式;零值的格式;文本格式」是条件被省略的特殊自定义条件数字格式。

❷自定义带颜色的数字格式

自定义数字格式中的颜色可以用两种方式来表示：

一是 RGB 颜色模式下、RGB 的强度值分别为极小值 0 或极大值 255 对应的 8 种颜色，直接使用「[颜色名称]」即可识别，这八种颜色如下：

二是调用调色板的 56 种颜色，使用「[颜色 N]」表示，N 为 1-56 的整数，这些颜色如下：

详细表格见配套文件，文末可获取~

需要注意的是，颜色和条件两个要素均需要使用方括号「[]」引导才能被正确识别， 颜色需在条件之前，且颜色不可自定义， 小伙伴们一定不要在此处栽跟头哦！

条件格式法

作为 Excel 数图结合和颜色管理的技术担当，条件格式自然不会在交通灯这种小 Case 上掉链子。

选择需要设置交通灯的单元格区域，点击【开始】-【条件格式】-【图标集】，选择其中的<三色交通灯>，即可快速设置三等分的交通灯。

即将条件格式区域所有值的值域，按百分比均分为三等份，再对每一部分分别设置不同颜色的交通灯。

如果我们需要根据特定的数值来设定不同的灯色，我们可以再次点击条件格式中的管理规则，修改其中的条件规则即可。

举个例子，我们需要把达成率高于80%及以上的设置为绿灯，60%-80%设置为黄灯，60%以下的设置为红灯，我们只需要这样操作：

特殊字体法

除了上述两种方法，我们还可以使用特殊字体 Wingdings 来设置文章开头提到的仿真交通灯。

操作也是十分简单，只需一个公式，一个交通灯背景板和三个文本框即可。

首先，添加三个辅助单元格，分别代表红绿蓝三个状态，设置如下公式：

红灯公式：

=IF(B2<50%,"l","")

黄灯公式：

=IF(AND(B2>=50%,B2<80%),"l","")

绿灯公式：

=IF(B2>=80%,"l","")

其中"l"为小写字母 L。

然后，插入绘制好的交通灯背景板，插入分别链接三个辅助单元格，并将文本框字体颜色分别设置为红黄绿三色。

只需两步，一个仿真交通灯就完成了。

总结一下三种交通灯的设置方法：

❶ 自定义带颜色和条件的数字格式交通灯；

❷ 使用条件格式图表集设置三色交通灯；

❸ 使用特殊字体+文本框设置仿真交通灯。

以上设置交通灯的三种方法，你学会了吗？ 你还知道其他设置交通灯的方法吗？

如果你想学习更多关于表格排版、数据处理、数据分析、汇报表达的知识，想提高 Excel 技能、升职加薪，那么今天就是一个很好的机会：

小编为大家申请了专属福利，原价99元的《秋叶 Excel 3 天集训营》，现在报名，只需 0 元！

秋叶 Excel 3 天集训营

0 基础也能学

报名额外赠送

100 套职场必备图表模板

35 个常用函数说明

学到就是赚到

名额有限，快来免费加入

关注公众号秋叶 Excel

回复关键词【0105】

即可下载本文 Excel 案例文件

以上内容包含广告