资料文库

基于Excel的项目进度管理——甘特图从理论与实操

office

理论基础

项目进度管理(Project Schedule Management)，又称为项目工期管理或项目时间管理，是为确保项目按时完工所进行的一系列管理过程。通过制定一个进度计划，加强进度控制，使之不偏离项目运行的轨道，使项目能够顺利交接，按时完成。进度管理包括计划编制、进度检测、进度监控。

项目进度管理需要基于项目范围基准、WBS 细节、项目相关成本、风险、质量、安全要求进行确定。传统施工进度管理方法主要有关键日期法、进度曲线法、横道图法、网络计划法、里程碑事件法、直方图、S型曲线、香蕉曲线等方法。

甘特图是一种非常常用的进度管理工具，又称横道图，由亨利·劳伦斯·甘特（Henry Laurence Gantt）在20世纪初开发，用于规划和控制项目。甘特图，在带有时间坐标的表格中，用一条横向线条表示一项工作，不同的横线表示不同阶段，横向线段起止位置对应的时间坐标表示该项工作的开始和介数时间，横向线段的长度表示该工作的持续时间。不同位置代表各工作的先后顺序，整个进度计划由一系列的横道线组成。甘特图可形象直观地展现不同工序之间的前后搭接关系，简单且易于编制，能够帮助项目经理和团队成员保持对项目进度的清晰视图，并确保项目按时完成。其缺点是无法反映关键线路。

下面我们来实现一个甘特图。其实现效果如下图所示：

图片

基础数据准备

基础信息如下所示：

层级 任务名 负责人 完成进度 持续时间 开始日期 结束日期
1项目启动
1.1需求收集 需求收集
1.1.1用户访谈 用户访谈 张伟 30% 1 天 2024/11/11 2024/11/12
1.1.2问卷调查 问卷调查 张伟 50% 1 天 2024/11/13 2024/11/14
1.1.3需求整理 需求整理 张伟 50% 1 天 2024/11/15 2024/11/16
1.2需求分析与规划 需求分析与规划
1.2.1需求优先级排序 需求优先级排序 赵敏 100% 1 天 2024/11/18 2024/11/19
1.2.2项目范围界定 项目范围界定 赵敏 50% 1 天 2024/11/20 2024/11/21
2设计阶段
2.1架构设计 架构设计
2.1.1系统组件定义 系统组件定义 陈强 100% 2 天 2024/11/22 2024/11/24
2.1.2技术选型 技术选型 陈强 50% 2 天 2024/11/25 2024/11/27
2.1.3架构评审 架构评审 陈强 50% 1 天 2024/11/28 2024/11/29
2.2用户界面设计 用户界面设计
2.1.1界面原型设计 原型设计 刘娟 100% 1 天 2024/12/2 2024/12/3
2.1.2界面原型优化 界面优化 刘娟 50% 1 天 2024/12/4 2024/12/5
2.1.3界面原型优化 界面细化 刘娟 10% 2 天 2024/12/6 2024/12/8
2.1.4界面原型出图 界面输出 刘娟 10% 1 天 2024/12/9 2024/12/10

其数据效果如下所示：

图片

增加日历-设置时间线

将当期日期输入到表格中，其中I4单元格，等于下方的2024/11/1。

图片

图片

下面对其格式进行设置。

首先将是上方的2024/11/1设置为短日期格式，快捷键为Ctrl+Shift+3。

然后将下方的一排日期设置为只有天数，从而更好展示效果。具体通过单元格的自定义格式，类型为d，即可日期。在Excel中，单元格时间格式自定义的基本规则是：

  • d代表日期格式中的单数的天（如5）
  • dd代表日期格式中双位的天（如05）
  • ddd代表的是星期（如Mon）
  • m代表单数的月（如1代表一月份）
  • mm代表双位的月（如01代表1月份）
  • mmm代表英文月（如Jan）
  • mmmm为英文全称月（January）
  • yyyy代表年（如2024），如果用一个yy代表年（如24代表2024）

图片

从而实现如图所示效果。

接下来在下方把星期加上，在excel中如果直接自定义格式，需要使用ddd来实现日期，但是英文日期，如果要实现中文日期，需要使用自定义格式字符串“[$-zh-CN]aaa;@”。另外因为格式问题，显示的是周三，对于星期而言，为了简化，我们省略周，只要三这个部分。对于这两部的要求，我们从两步进行处理。

  • 使用Text将日期输出为周三格式

    • 其中“[$-zh-CN]aaa;@”可以输出周一到周天的标签
    • 其中“[$-zh-CN]aaaa;@”可以输出星期一到星期天的标签

  • 第二步是使用Excel的文本函数，去周一的右边一位。

  • 完整公式是：=RIGHT(TEXT(J5,"[$-zh-CN]aaa;@"),1)

图片

最终效果如下所示，看起来是不是非常规整呢。

图片

为了让日期能够随起始日期而自动变动，我们将增加一个项目启动日期，并将所有的日期与前面的日期通过+1进行绑定。

图片

其公式设置为：

图片

最后，将日期区域选中，进行拖动，即可实现完整的日历效果。

  • 具体拖动方法是，直接拖动I到O列，进行拖动，会发现I5单元格因为引用的问题会偏移，一定要按列拖动，而不是按照单元格拖动，列拖动会直接把格式往后复制。

  • 手动修正即可

具体效果如下所示：

图片

修正效果如下

图片

其动态效果如下所示：

图片

优化甘特图的展示效果

第一步，隐藏Excel的网格线，效果如下。【视图→网格线取消】

图片

第二步，增加背景颜色。在启动日期、日程和表格标题区域设为背景色，可以根据自己需要设置。可以使用格式刷来刷格式。

图片

第三步，增加单元格边框，可以根据自己要求设定边框。总体效果如下：

图片

其中边框的设定方法是首先选择单元格，然后点击单元格。

图片

边框设置的基本方法是，首先选择直线样式，然后选择颜色，接着选择边框。边框三根线边缘两根边线和中间一根线，根据需要设置即可。

图片

设置甘特图的进度条（关键）

要自动实现甘特图的自动进度条，需要使用到条件格式。条件格式顾名思义，根据条件来显示不同的格式。

图片

如果日期单元格刚好在开始日期和结束日期之间，将其显示为设定颜色。按照这种思想，我们可以设定单元格格式颜色公式为：=AND(I$5>=$G9,I$5<=$H9)。其中日期一行确定为行不动，而开始日期和结束日期设置为列不动。所以单元格公式如下所示。如果为True，说明要标注为需要的颜色。

图片

首先选择日历空白区，然后设置条件格式，公式为=AND(I$5>=$G9,I$5<=$H9)，效果如下：

图片

接着甘特图的的展示效果如下，这里说明下，为了展示结果，项目启动日期，我略微调整了下。是不是看起来非常完美呢？

图片

需要注意的是框选区域，要从第一个子任务正式开始进行处理。框选区域如下：

图片

调整甘特图的参数

  • 将日期调整为从本星期周一开始，更好地展现进度，设置效果如下，具体公式为=G4-WEEKDAY(G4,3)。

Excel 中的 WEEKDAY 函数用于返回给定日期对应的星期值。其中参数为2：返回值范围是1（周一）到7（周日）。参数为3：返回值范围是0（周一）到6（周日）。

通过向前调整，实现了从周一开始的要求。让日历更加规整。

  • 展示项目工作在第几周，方便进行查询，并方便进行操作

在此将单元格进行进行调整，进行增加7天处理，如果是第一周，所以不用调整，所以减1，然后再乘以7。

图片

在项目周数输入窗口中，添加一个滚动条，实现日期的快速调整。首先通过【开发工具→滚动条工具】，进行滚动条拖动。设置内容如下所示。然后退出界面，即可执行。

图片

其动态调整效果如下所示：

图片

  • 高亮显示今天

选择单元格区域，从25日一直到最后，需要注意的是I$5设置为相对引用格式，并设置红色边框效果。设置方法如下：

图片

  • 把周末展示出来，方便不进行996

在这里我们继续使用条件格式展示周末的效果，具体我们采用NETWORKDAYS。NETWORKDAYS 函数在Excel中用于计算两个日期之间的工作日天数，排除周末和指定的节假日，如果为0，即为非工作日。

图片

  • 展示项目的进度情况

在完成进度区域，选中设置进行渐变填充，渐变填充效果如下。设置方法为【开始-条件格式-条件格式-数据条】

图片

  • 在甘特图上显示进度情况

在此我们以完成进度情况为基础，设置进度区域条件格式，其设置公式为：

=1*AND(I$5>=taskStart,I$5<=taskStart+taskProgress*taskDays)

其中taskStart为开始日期，taskStart加上进度的完成情况。最后乘以1将其转变为数值。在这里，日期单元格是列固定的，而项目起始日期是纵向固定的。

图片

  • 展示项目的进度情况

根据需要设置工作日的日期，此时可以继续使用WORKDAY进行处理，从而保障不进行996.

总结

经过上述一系列操作后，我们可以得到一个动态的甘特图，甘特图通过条形图的形式展示项目中各个任务的开始和结束时间，帮助管理者直观地了解项目进度，从而为大家的项目管理提供支持。

本项目过程内容比较多，是练习条件格式非常好的材料，希望为大家日常管理提供支持。

动态效果展示

图片

知识点

  1. 条件格式是实现Excel动态格式的关键
  2. 使用Ctrl+Shift+3快捷键将选中的单元格区域格式化为“短日期”格式
  3. 单元格格式自定义，d代表日期格式中的天，ddd代表的是星期，m代表月，yyyy代表年
  4. TEXT(K2,“ddd”)可以实现单元格格式自定义功能，从而在Excel中进行输出
  5. Excel文本函数right截取字符串
  6. Ctrl+Shift+=，增加行
  7. Ctrl+1查看和编辑单元格格式
  8. [$-zh-CN]aaa;@，输出中文日期，实现周一到周日的效果
  9. yyyy"年"m"月"d"日"[$-zh-CN]aaaa;@ ，实现2024年11月4日星期一效果
  10. 使用基于公式的条件格式，如果单元格在区间中就自动进行变色处理，避免了手动扒拉的缺点
  11. Ctrl+D,设置单元格格式和上面的单元格格式相同。
  12. NETWORKDAYS可以用于确定今天是否为工作日
  13. WEEKDAY使用，实现周一工作效果调节
  14. 条件格式的今天使用today函数进行实现
  15. 使用开发工具，可以减少输入，加强操作效果，开发工具滚动条是一个非常方便的工具实现快捷输入
  16. 视图网格
  17. 边框设置