📌 阶段五 · 报表可视化 · 第22讲 制作甘特图与动态甘特图 · 约 40 分钟

用堆积条形图给采购计划排出可视化时间表，拖动滚动条看进度

学习目标

  • 理解甘特图的原理：堆积条形图隐藏第一段、露出第二段
  • 会在 Excel 中制作静态甘特图并按日期设置横坐标轴
  • 会用「已完成/未完成」两根辅助列公式判断任务进度
  • 会用滚动条控件拨动「今天」的日期，让甘特图动起来
  • 为进销存系统新增采购计划排期表并完成可视化

知识点讲解

甘特图原理：会隐身的堆积条形图

甘特图（横道图）是项目管理里的排期图：每行一个任务，条形从开始日铺到结束日。Excel 里没有现成的「甘特图」按钮，它其实是「堆积条形图」的障眼法：

把「开始日期」和「持续天数」两列做成堆积条形图后，每个任务有两段——第一段从坐标原点延伸到开始日期，第二段是持续天数。把第一段的填充设为「无」，它就隐身了，但仍然占位；露出来的第二段，看起来正好是从开始日铺开的进度条。

另外要记住：日期在 Excel 里本质是序列数字（比如 2023/5/1 是 41760），所以横坐标轴的边界可以直接填数字来「裁剪」时间窗。

💡 提示:

  • 不知道日期的序列数？把单元格格式临时改成「常规」，看到的数字就是序列数

四步做出静态甘特图

以任务名称、开始日期、持续天数三列数据（A1:C9）为例：

第一步：选中 A1:C9，插入选项卡-插入堆积条形图。

第二步：点中图表里的「开始日期」系列条形（图上离原点最近的那段），右键-设置数据系列格式：填充选无填充、边框选无线条、阴影选无阴影——它隐身，甘特图的雏形出现了。

第三步：右键横坐标轴-设置坐标轴格式，把边界最小值、最大值设为计划首尾日期的序列数（如 41760 和 41790，对应 5/1 到 5/26），数字类别选日期、类型选只显示月和日——横轴变成真正的时间轴。

第四步美化：右键条形-设置数据系列格式，间隙宽度调到 10% 左右（条形更饱满）；右键纵坐标轴-设置坐标轴格式-勾选「逆序类别」，让第一个任务排到最上面；右键网格线-设置网格线格式-线型-短划线类型选虚线。一张清爽的甘特图完工。

💡 提示:

  • 隐身系列要「无填充＋无边框＋无阴影」三件套，缺一样都会露出马脚

动态甘特图：已完成/未完成辅助列

静态甘特图只排计划，动态版能看出「到今天为止干完了多少」。加两根辅助列（假设数据在第 2～9 行，B11 单元格放「今天」的日期）：

E 列「已完成」：=IF($B$11<B2,0,IF($B$11>B2+C2,C2,$B$11-B2))。逻辑分三层：今天还没到开始日，已完成 0 天；今天已超过「开始日＋持续天数」，说明整个工期干完了，已完成等于全部持续天数 C2；两者都不是，就是进行中，已完成＝今天减开始日。

F 列「未完成」：=C2-E2，总天数减已完成，直接下拉。

作图时选 A1:B9，按住 Ctrl 再选 E1:F9（跳过 C 列），插入堆积条形图——三个可见段变成「开始日期（隐身）＋已完成＋未完成」，颜色一深一浅，进度一目了然。

=IF($B$11<B2,0,IF($B$11>B2+C2,C2,$B$11-B2))

B11 放「今天」：未开始记 0；已完工记全部天数 C2；进行中记今天减开始日

=C2-E2

未完成天数＝持续天数－已完成天数

💡 提示:

  • 两根辅助列都从第 2 行下拉填充到第 9 行，与任务一一对应

滚动条拨动「今天」

演示时不想天天改日期，用滚动条当「时间旋钮」：开发工具选项卡（没有的话在文件-选项-自定义功能区里勾选）-插入-滚动条，在表里拉出合适长度；右键滚动条-设置控件格式-控制：最小值 0、最大值 30、单元格链接点选 C11（一个空白单元格）。

然后把 B11 的日期改成 =45047+C11——45047 是基准日 2023/5/1 的序列数，C11 在 0～30 间变化，B11 就在 5 月里逐日移动。拖动滚动条，已完成/未完成的分界线跟着日期走，这就是「拖动滚动条、条形也变动」的动态甘特图。

再配一个会说话的日期牌：插入选项卡-文本框-横排文本框，画在图表旁，选中文本框后在编辑栏输入 =$B$11——文本框直接显示当前模拟日期。

=45047+C11

基准序列数加滚动条偏移：C11 从 0 拨到 30，日期在 2023/5/1～5/31 间移动

💡 提示:

  • 文本框也能像单元格一样用编辑栏引用公式，输入 =$B$11 即可联动

实战演练

库存会预警了，进货也得有章法。本讲为系统新增「采购计划」表：列出一批物料在 6 月上旬的补货排期，先用堆积条形图排出静态甘特图，再用「已完成/未完成」辅助列升级成进度甘特图。在线部分负责把计划数据和辅助列公式搭对；图表与控件的画法按步骤末尾的「Excel 操作指引」在本地完成。

  1. 建采购计划表 — 新建「采购计划」表，A1:C1 写表头：采购物料、开始日期、持续天数。填 8 行数据，日期错落在 2024 年 6 月上旬，例如：复印纸 2024/6/1 持续 5 天、签字笔 6/2 持续 3 天、硒鼓 6/3 持续 4 天、文件夹 6/4 持续 2 天……物料名称可从商品档案里挑常用的 8 个。
  2. 查日期序列数 — 把任意一个日期单元格的数字格式临时改成「常规」，读出序列数：2024/6/1 是 45444、2024/6/17 是 45460。把首尾两个数记下来，第三步设置横轴边界要用；查完记得把格式改回日期。
  3. Excel 操作指引：静态甘特图 — 选中 A1:C9，插入-堆积条形图；点击「开始日期」系列，设置数据系列格式：无填充、无边框、无阴影；右键横坐标轴：边界最小值 45444、最大值 45460，数字类别选日期、只显示月日；最后美化：间隙宽度 10%、纵坐标勾逆序类别、网格线改虚线。8 个物料的排期条形图就位。
  4. 写进度辅助列公式 — E1 输入「已完成」、F1 输入「未完成」；B11 输入一个「模拟今天」的日期，先手填 2024/6/5。E2 输入 =IF($B$11<B2,0,IF($B$11>B2+C2,C2,$B$11-B2))，F2 输入 =C2-E2，双双下拉到第 9 行。检查：6/1 开工的物料已完成 4 天，还没开始的记 0。
  5. Excel 操作指引：进度甘特图 — 选中 A1:B9，按住 Ctrl 再选 E1:F9，插入堆积条形图；隐藏「开始日期」系列（无填充三件套），已完成段设深绿色、未完成段设浅灰色；横轴边界仍按 45444～45460 设置成日期；其余美化同静态版。现在图上每个任务都是「深绿已完成＋浅灰未完成」的两段尺。
  6. Excel 操作指引：滚动条拨日期 — 开发工具-插入-滚动条，右键-设置控件格式-控制：最小值 0、最大值 30、单元格链接选 C11；把 B11 的手填日期改成 =45444+C11。拖动滚动条：模拟今天从 6/1 滑到 7/1，甘特图的深绿段随之伸缩。再插入横排文本框，编辑栏输入 =$B$11，一个随滚动变化的日期牌就做好了。
  7. 验收与衔接 — 把滚动条拨到 6/8，逐行核对：开工早于 6/8 的物料，深绿段长度应等于 8 减开始日；6/9 之后才开工的应全灰。把这张「采购计划」表移到工作簿里「库存台账」之后，跟库存预警配合使用：看到「⚠补货」就查这张图安排下单。

在线练习

下面的表格可以直接编辑,照着演练步骤在本页练习;需要菜单操作的步骤请下载配套练习文件,在自己电脑的 Excel 中完成。

表格加载中…

配套练习文件:lecture-22.xlsx(见本页底部「附件下载」)。

验收清单

逐项自查,全部通过即完成本讲:

  • ☐ 能说出甘特图用的是堆积条形图，以及为什么第一个系列要设为无填充
  • ☐ 静态甘特图横坐标按日期显示，条形从各任务的开始日铺开
  • ☐ 「已完成」辅助列公式的三分支逻辑（没开始/已完工/进行中）口述正确
  • ☐ 拖动滚动条时，甘特条深浅分界与日期文本框同步移动
  • ☐ 采购计划表 8 个物料的排期和进度一眼可读，并能对照库存预警安排下单