Excel条件格式制作甘特图

我们知道Excel条件格式可以做很多预警、热力图等相关数据可视化,因此在预警的一个特殊领域——项目管理中,条件格式也可以发挥其作用,可以利用条件格式制作一个功能简便的甘特图。

结果如下:

操作方法:

1、根据项目需要编制项目时间表如下:

2、首先填充计划时间的进度条,选中H3:AK11区域,点击开始→条件格式→新建规则→使用公式确定要设置格式的单元格。

3、在”为符合此公式的值设置格式”中,输入:=AND(H$2>=$C3,H$2<=$D3),设置格式填充为淡绿色,以确定计划时间段

4、重复2和3的步骤,在”为符合此公式的值设置格式”中,输入:=AND(H$2>=$E3,H$2<=$F3),设置格式填充为较深的绿色

以上即为最终的效果,但我们发现一个问题:项目3中,实际结束时间超过了计划时间,这种情况下我们希望将超过的时间突出展示,可以执行以下操作:

5、新建规则,公式输入:=AND($F3>$D3,H$2>$D3,H$2<=$F3),设定填充为橘红色。即可突出展示出6月26和27日超过计划时间

百度网盘,文件下载:

地址:https://pan.baidu.com/s/1gG6s2P_WAmmolQhpSXaN0w

提取码:itxx

Excel基于历史月份数据的条件格式预警

在市场研究或产品监测中,通常会遇到对同一个数据连续多期的表现的预警监测。如,连续两期低于某个值、连续三期低于某个值等等。

仍以”单一目标值的条件格式预警“中的区域和产品为例。假设我们连续监测了8个区域5个产品在1-3月的销售情况,对于各个产品,如果某区域的连续2个月销量低于各区域当月平均值则用黄色预警,如果连续3个月均低于当月平均值则用红色预警。最终结果如下:

操作方法:

1、选中3月的数据区域C21:G26,点击开始→条件格式→新建规则→使用公式确定要设置格式的单元格

2、在”为符合此公式的值设置格式”中,输入:=SUM(IF(C3<C$9,1,0),IF(C12<C$18,5,0),IF(C21<C$27,10,0))=15,点击格式,设置填充为黄色。用以设置在2月和3月连续两个月均低于平均值的数据预警

3、上述步骤确定完成后,再重复步骤1,在设置公式时输入:=SUM(IF(C3<C$9,1,0),IF(C12<C$18,5,0),IF(C21<C$27,10,0))=16,点击格式,设置填充为红色。用以设置在1-3月均低于平均值的数据预警

至此我们可以观察验证公式设置是否正确。以上问题的解决思路主要为:

我们判断某个月的,某区域的产品销售量是否小于所有区域的均值,如果小于则返回一个特定值,否则返回0。我们通过对不同月份的判断结果返回不同的值,然后对这些值相加即可知道某个区域是在哪几个月份销售额小于均值。

如我们设置某区域一月销售额小于均值则返回1,二月小于均值则返回5,三月则返回10,将三个月返回值相加,得到最终结果。通过对最终结果的判断,我们知道15则表示二三月份同时低于均值,16则表示一二三月份同时低于均值。

文件下载,百度网盘:https://pan.baidu.com/s/1NGSqr663kRUCvOUuCb8JtQ 提取码:umk6

Excel单一目标值的条件格式预警

Excel的条件格式可根据设定条件突出显示单元格数据,因此是制作预警表格的优良工具。

比如,有一系列数据如果小于某个值就设置为预警数据突出显示。在生产环境中,结果如下:

操作方法:

1、选定要预警的数据区域C3:G8,点击开始→条件格式→新建规则→使用公式确定要设置格式的单元格

2、在“为符合此公式的值设置格式”中,输入: =c3<c$9

3、点开右下角的格式,设置填充为你想要的颜色。一路确定即可。

条件设置过程中主要难点在第二步的公式设置,如果对公式设置不太理解,请参考相对引用和绝对引用。以下做简单解释:

文中c3<c$9,表示将第9行固定,所有行的数据与第9行的数据比较。即C3和C9比较,C4/C5….C8都与C9比较。如果不固定第9行,那么比较对象就会变为C3对C9、C4对C10,依次类推。如果目标值只有一个而不是一行,那么设置为c3<$c$9即可。