Excel带项目进度预警功能的甘特图

文前推荐

编按：

分享一份能实时反应项目进度状态，并提供进度预警功能的甘特图。

甘特图又叫横道图，可以显示项目进度、开始以及完成时间，常用于项目管理、生成管理中。可以用甘特图展示项目计划；也可以用甘特图展示项目的计划与实际进展对比。

计划甘特图

计划与实际对比甘特图

上面的计划与实际对比甘特图可以了解截止到当前日期项目开展到哪个阶段。譬如上图就表示当前在进行的任务是“设计”。但是，项目完成了多少、是否能按时完工，都无法了解。

今天小窝分享一份能反应实时进度，并对是否能按时完成进行预警的甘特图。如下图，截止到当前“设计”任务完成75.5%，比计划慢了6.3%，有逾期的风险：

原始数据如下：

要做到预警，必然需要用实际完成比例与相同时间的计划完成比例进行比较，必然要根据比较结果给出相应进度状态。这就需要建立两个辅助列：一个用于计算计划完成比例，一个用于得到进度状态。除此外，为了让进度状态文字显示于进度条右侧，还得增加一个进度标记辅助列。

如图：

说明：

进度标记主要用来显示进度状态文字，因此其数字是固定的某个正数。

相同用时的计划比例通过公式得到，按住Ctrl键从下往上依次选中I13、I11、I9、I7、I5后，输入公式“=IFS(D5="","",1,1/E4*F5)”，并按Ctrl+Enter完成输入。

进度状态也通过公式得到。因为只关注已开始但未完成的任务，所以将忽略未开始的和100%完成的。公式=IFS(D5="","",G5=100%,"",G5>I5*1.05,"快"&TEXT(G5-I5,"0.0%")&"，可能提前",G5>=I5*0.95,"进度正常",G5<i5*0.95,"< span="">慢"&TEXT(I5-G5,"0.0%")&"，可能逾期")</i5*0.95,"<>

此处正常、提前、逾期的判断根据实际项目具体规则进行。当前的规则：实际比例>1.05*计划比例，则可能提前；>=0.95*计划比例，则正常；< 0.95*计划比例则可能逾期。

下面开始正式做图。

Step 01 选中A3:F13插入“堆积条形图”，结果如下。

查看Y轴文字，可以看出当前任务顺序是反着的，因此需要纠正过来。

Step 02 单击Y轴坐标文字，在右侧的“坐标轴选项”中勾选“逆序类别”复选框，纠正顺序。

Step 03 单击进度条，在“系列选项”中设置“间隙宽度”为30%。（可以更小）

现在X坐标轴上的日期显得太拥挤，且不是从第一个开始日期开始的，因此，需要调整。

Step 04 单击上方的X坐标，设置最小值为“2023-12-5”，最大值为“2024-1-10”（大于等于最后一项任务的结束时间）；设置数量类型为“3/14”，不显示年份。

接下来修改填充颜色。

Step 05 选中蓝色条，取消填充；选中灰色条，改为浅绿色；选中橙色条，改为灰色。结果如下图。

添加实际完成比例数据。

Step 06 选中绿色条，先为其添加“数据标签”图表元素。然后选中添加的数据标签，在“标签选项”中设置标签包括“单元格中的值”，选择“G4:G13”单元格；取消“值”的勾选；设置标签位置为“数据标签内”。

接着添加当前日期指示线。

Step 07 在图表上右击选择“选择数据”命令，在弹出的“选择数据源”对话框中单击“添加”按钮，系列名称拾取A2，系列值拾取B2。

结果如图：

下面修改当前日期的图表类型为散点图，并生成指示线。

Step 08 选中黄色条右击选择“更改系列图表类型”。将“当前日期”的图表类型改为“散点图”。

改后，图表中看不到当前日期了。不用慌！修改系列值后就可以看到。

Step 09 在图表上右击选择“选择数据”，选中“当前日期”系列，单击“编辑”按钮，设置X轴值为B2，Y轴为1。

确定后就可以看到一个黄色的点。

Step 10 选中这个黄色点，首先添加“标准误差”图表元素。然后设置“Y”误差线的方向为“负偏差”，末端样式为“无线端”，误差量改为“固定值”1。

选中右侧的次坐标轴，设置最大值为1。修改后，黄色点移动到图表上方。

当前效果如下。

下面添加进度状态。进度状态要标记在进度条右侧。当前的进度条上已经添加了完成比例，所以需增加一个系列。

Step 11 右击图表选择“选择数据”，点击“添加”，增加一个系列。系列名，拾取H3，Y轴值拾取H4:H13。X轴值空着。（当前要添加的系列是条形图，所以用不着X轴值。）

右击图表，选择“更改图表类型”，将“进度标记辅助”改为“堆积条形图”。

效果如下：

Step 12 选择蓝色进度条，设置为“无填充”；然后添加“数据标签”。设置“数据标签”，标签内容包括“单元格中的值”，单元格选择“J4:J13”，并取消“值”的勾选；标签位置设置为“轴内侧”。

当前效果如下。

最后，删除不需要的次坐标轴、开始日期图例、进度标记辅助图例，添加图表标题，当前日期线的数据标签，按自己喜欢的风格修改各元素颜色，得到最终效果。

效果1

效果2

本站仅提供存储服务，所有内容均由用户发布，如发现有害或侵权内容，请点击举报。