把交通红绿灯搬到Excel里，会有多牛X？

大家好，我是机智的秋小E~

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

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

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

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

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

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

01

自定义数字格式法

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

以下图为例，我们只需选中单元格后按【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 的整数，这些颜色如下：

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

02

条件格式法

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

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

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

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

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

03

特殊字体法

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

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

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

红灯公式：

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

黄灯公式：

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

绿灯公式：

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

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

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

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

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

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

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

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

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

每天学点Excel，工作效率up~up~