Excel_使用条件格式制作甘特图

 

之前想用Excel做一个可以反映项目进度计划的自动甘特图，网上查了一些教程，但都只能反映计划，不能反映项目进度情况。然后发现Excel的联机模板里面就有一个比较好的甘特图模板，于是参考其原理仿做了一个简单的Excel甘特图。

 

原资料分析

 

参考的Excel联机模板

 

这个甘特图模板有几个特点：

 

• 可以反映出计划周期与实际周期的关系
• 日期高亮
• 百分比换算表示实际进度
• 配色和排版都漂亮

 

分析了一下这个模板的甘特图实现原理，其实就是利用Excel的条件格式功能进行单元格填充，那做起来还是比较简单的。

 

制作前的思考

 

为了方便实际项目使用，我想要的甘特图还需要做一些调整：

 

• 原模板是使用单位时间计划任务的，需要将其调整为使用日期计算
• 除了原本的日期高亮，还需要实现节点日高亮
• 添加周末高亮
• 原模板实际工期不输入的话甘特图显示是错误的，那只有等到计划任务全部完成后这个甘特图才能显示正确，所以这个完成百分比就显得很鸡肋了，干脆去掉百分比的相关显示
• 我好像做不出这么好的配色...钢铁直男无所谓了，好用就行

 

成品演示

 

 

制作解析

 

首先讲一下原理，Excel有一个叫条件格式的功能，这个功能允许通过公式判定单元格内的数据，如果满足条件，则按照设定修改单元格格式。所实现的这个甘特图就是使用了日期进行判定运算，然后对甘特图内的单元格进行颜色填充或者边框线调整。

 

下面进行步骤解析

 

首先在Excel中调整一个格式，后面涉及到公式部分都基于下图格式

 

 

---

 

先实现周末高亮，逻辑很简单，通过Excel的weekday函数判断一下就可以了：

 

1. 光标放在H12，在开始选项卡里点击 条件格式 下拉按钮
2. 点击 新建规则
3. 点击 使用公式确定要设置格式的单元格
4. 公式输入 "=WEEKDAY(H$12,2)>5"
5. 设定格式
6. 调整条件格式应用范围

 

公式："=WEEKDAY(H$12,2)>5"

 

使用WEEKDAY(serial_number,return_type)这个Excel函数可以返回输入数值在一周内是第几天，输入H12里面的数值的话返回值应为6，再判断这个返回值是否大于5就可以知道是否为周末了。

 

 

 

---

 

然后实现计划周期显示，逻辑是判断单元格内日期是否在计划开始日期与计划结束日期之间：

 

1. 光标放在H13，其他步骤参考上一小节
2. 公式输入 "=AND(H$12>=$C13,H$12<=MAX($C13,$D13),$C13>0)"
3. 注意调整条件格式应用范围

 

公式："=AND(H$12>=$C13,H$12<=MAX($C13,$D13),$C13>0)"

 

使用Excel函数AND(logical1,logical2,...)可以判断多个条件，当条件全部满足时则返回真值，这里输入了三个条件:

 

H$12>=$C13——判断H12单元格内的值是否大于或等于C13单元格内的值；

 

H$12<=MAX($C13,$D13)——首先判断C13的值大还是D13的值大，再判读H12单元格内的值是否小于或等于这个值。这样做是考虑如果没有输入结束日期或者结束日期比开始日期还早（输入错误）的时候，就会用开始日期当作结束日期，保证甘特图显示不会反逻辑；

 

$C13>0——当C13单元格大于0，即开始日期需要有输入，甘特图才显示相关数据。

 

 

---

 

计划周期内的实际工作时间显示，逻辑是判断出在计划周期内，实际启动日期到计划结束日期的单元格。

 

1. 光标放在H13，其他步骤参考第一小节
2. 公式输入 "=AND(H$12>=MAX($C13,$E13),H$12<=MIN(MAX($E13,$F13),$D13),$E13>0)"
3. 注意调整条件格式应用范围

 

公式："=AND(H$12>=MAX($C13,$E13),H$12<=MIN(MAX($E13,$F13),$D13),$E13>0)"

 

实现方法与前一节相仿，条件稍微调一下：

 

(H$12>=MAX($C13,$E13)——先判断C13与E13的最大值，可获得计划周期内的实际启动日期，再判断H12单元格是否大于或等于这个日期；

 

H$12<=MIN(MAX($E13,$F13),$D13)——先用MAX处理，避免出现结束日期未输入或输入错误的情况，再用MIN比较计划结束日期与实际结束日期较哪个更小，最后判断H12里的值是否小于等于这个日期；

 

$E13>0——当实际启动日期有输入才在甘特图中显示相关数据。

 

 

注意检查条件格式的顺序，越上面的条件格式会将下方的条件格式覆盖

 

---

 

计划周期前的实际工作时间显示，逻辑是判断出实际启动日期及实际结束日期在计划启动日期之前的单元格。

 

1. 光标放在H13，其他步骤参考第一小节
2. 公式输入 "=AND(H$12>=$E13,H$12<=MIN($C13-1,MAX($E13,$F13)),$E13>0)"
3. 注意调整条件格式应用范围

 

公式："=AND(H$12>=$E13,H$12<=MIN($C13-1,MAX($E13,$F13)),$E13>0)"

 

同样是三个条件：

 

H$12>=$E13——判断单元格是否大于及等于实际启动日期；

 

H$12<=MIN($C13-1,MAX($E13,$F13))——MAX的使用原因同前面小节，然后通过MIN判断实际结束日期是否早于计划开始日期，最后判断单元格是否小于或等于这个日期；

 

$E13>0——同上一小节。

 

 

---

 

计划周期后的实际工作时间显示，体现项目延期的这个部分我用了两个条件格式，分别用来判定两种情况，一种是实际启动日期早于计划结束日期，一种是实际启动日期比计划结束日期还晚。公式分别如下：

 

"=AND(H$12>=MAX($D13+1,$E13),H$12<=MAX($E13,$F13),$E13>0)"

 

"=AND(H$12>$D13,H$12<$E13)"

 

公式："=AND(H$12>=MAX($D13+1,$E13),H$12<=MAX($E13,$F13),$E13>0)"

 

老套路：

 

H$12>=MAX($D13+1,$E13)——MAX函数判断实际启动日期晚还是计划结束日期晚，单元格值需要等于或者大于这个日期；

 

H$12<=MAX($E13,$F13)——此次MAX函数也是为了解决结束日期输入的问题，然后判断单元格值是否小于等于实际结束日期；

 

$E13>0——同样用于判断是否有开始日期的输入。

 

公式："=AND(H$12>$D13,H$12<$E13)"

 

这次只需要两个条件：

 

H$12>$D13——单元格值需大于计划结束日期；

 

H$12<$E13——单元格值需小于实际启动日期；

 

 

---

 

接下来实现日期高亮，日期高亮也是由两个条件格式完成，但都使用一条公式，只是公式的应用范围及替换的格式不一样。

 

第一个条件格式光标先定在H10，公式输入"=H$12=$S$7"

 

第二格条件格式光标定在H11，公式输入"=H$12=$S$7"

 

公式："=H$12=$S$7"

 

 

这个判断非常简单，直接判断甘特图上的日期与设定日期是否一致就可以了。

 

 

单元格使用了一个公式赋值

 

---

 

节点日期高亮实现方法与上一节大致一样，也是两个条件格式。

 

第一个条件格式光标先定在H11，公式输入"=H$11="节点"

 

第二格条件格式光标定在H12，公式输入"=H$11="节点"

 

公式："=H$11="节点"

 

这个公式就是直接判断H11这行是否有输入"节点"这两个字，有的话替换相关范围的单元格格式。

 

 

---

 

至此，甘特图的核心功能就全部做好了，后面还可以做一些其他的美化或者格式调整。

 

其实在我的观点中，每个领域通常都有这个领域的专业软件，包括项目管理上我相信也有软件比Excel做得更好，功能更全。但我相信在一般的办公室，没有任何一款软件比Excel更普及，更容易让人接受。一些时候只是简单有了个想法，想要一些简单的功能，果然还是强大的Excel更直接。

 

当然了，最主要的是我好久没折腾软件了，有时候折腾也是一种快乐呀~