excel中使用函数构建辅助列与控件 [滚动条] 组合制作甘特图的方法详解

下图为数据源，利用函数构建辅助列与控件[滚动条]组合来制作甘特图，可直观查看各项目的进展情况。

完成后效果如下图所示。

以下是对使用函数构建辅助列与控件 [滚动条] 组合制作甘特图的详细说明：

一、确定日期范围并插入滚动条

1. 在 B2:C10 单元格中找出 [开始日期]（最小值）与 [结束日期]（最大值），将日期分别写入 B12 和 B13 单元格，对应的 C12 和 C13 单元格中同样输入 “开始日期” 与 “结束日期”，并将 C12 和 C13 单元格格式设置为 [常规]。这一步确定了整个项目的时间范围。
2. 插入滚动条并设置：
   • 在 [开发工具] 选项卡中选择 [插入]→[滚动条（窗体控件）] 选项，在工作表中插入一个滚动条。
   • 在滚动条上右击，选择 [设置控件格式] 命令，打开 [设置控件格式] 对话框。
   • 切换到 [控制] 选项卡，设置 [最小值] 为 0，[最大值] 为 51（使用结束日期 - 开始日期得到的天数），设置 [步长] 为 1，[页步长] 为 7，将 [单元格链接] 设置为 B14 单元格。这样设置后，滚动条的变化将与 B14 单元格的值相关联，用于控制进度条的日期。

二、构建数据

1. 在 A15 单元格中输入文字 “进度日期”，在 B15 单元格中输入公式=B12 B14得到进度条的日期。这个公式根据开始日期和滚动条链接的单元格值来动态确定进度日期。
2. 在 D1 单元格中输入文字 “步骤已消耗天数”，在 D2 单元格中输入公式并向下复制到 D10 单元格。公式=IF($B$15>=C2,C2-B2，IF($B$15>B2，$B$15-B2，0))用于判断当前进度条日期与当前步骤的计划开始日期和计划结束日期的关系，从而确定步骤已消耗的天数。
3. 在 E1 单元格中输入文字 “距步骤结束天数”，在 E2 单元格中输入公式并向下复制到 E10 单元格。公式=C2-B2-D2根据计划结束日期、计划开始日期和步骤已消耗天数计算出距离步骤结束的天数。

二、构建数据

1. 在 A15 单元格中输入文字 “进度日期”，在 B15 单元格中输入公式=B12 B14得到进度条的日期。这个公式根据开始日期和滚动条链接的单元格值来动态确定进度日期。
2. 在 D1 单元格中输入文字 “步骤已消耗天数”，在 D2 单元格中输入公式并向下复制到 D10 单元格。公式=IF($B$15>=C2,C2-B2，IF($B$15>B2，$B$15-B2，0))用于判断当前进度条日期与当前步骤的计划开始日期和计划结束日期的关系，从而确定步骤已消耗的天数。
3. 在 E1 单元格中输入文字 “距步骤结束天数”，在 E2 单元格中输入公式并向下复制到 E10 单元格。公式=C2-B2-D2根据计划结束日期、计划开始日期和步骤已消耗天数计算出距离步骤结束的天数。

三、生成堆积条形图并调整格式

1. 选中 A1:B10 单元格区域，依次选择 [插入]→[插入柱形图或条形图]→[堆积条形图] 选项，生成一个堆积条形图。
2. 选中 D1:E10 单元格区域，按 组合键复制，单击图表，按 < Ctrl V > 组合键将数据粘贴到图表中，增加数据系列。
3. 调整纵坐标轴：
   • 双击图表纵坐标轴，打开 [设置坐标轴格式] 对话框，在 [坐标轴选项] 选项卡中的 [坐标轴位置] 选中 [逆序类别] 复选框，使纵坐标轴的顺序与数据表中的顺序一致。
4. 调整横坐标轴：
   • 单击图表横坐标轴，在 [设置坐标轴格式] 对话框中切换到 [坐标轴选项] 选项卡。
   • 设置 [边界] 的 [最小值] 为开始日期数值，[最大值] 为结束日期数值，也可以直接输入日期。
   • 设置 [单位] 为 7（一周）。
   • 在 [数字] 选项区域设置 [类别] 为 [自定义]，[格式代码] 为 [m/d]，设置横坐标轴的数字格式。
   • 单击图表数据系列，在 [设置数据系列格式] 对话框中切换到 [系列选项] 选项卡设置 [间隙宽度] 为 18%。
   • 分别设置不同数据系列的填充颜色，如 [计划开始日期] 数据系列设置为 [无填充]，[步骤已消耗天数] 数据系列设置为蓝色填充，[距步骤结束天数] 数据系列设置为黄色填充。

四、添加分割线

1. 复制 B15 单元格，单击图表区，在 [开始] 选项卡中选择 [选择性粘贴] 命令，将其作为新系列添加到图表中。
2. 将新系列类型更改为散点图。
3. 编辑新系列的数据：
   • 右击图表绘图区，选择 [选择数据] 命令，弹出 [选择数据源] 对话框。
   • 选中 [系列 4] 复选框后单击 [编辑] 按钮，打开 [编辑数据系列] 对话框。
   • 在 [X 轴系列值] 中输入 “=sheet1!$B$15”，在 [Y 轴系列值] 中输入 “1”。
4. 添加误差线并设置格式：
   • 单击散点系列，在 [图表工具] 中单击 [设计] 选项卡中单击 [添加图表元素] 按钮，选择 [误差线]→[标准误差] 选项。
   • 单击图表次要纵坐标轴，在 [设置坐标轴格式] 对话框中设置边界和标签位置。
   • 选择误差线后，设置垂直误差线的方向、末端样式、误差量等格式。
   • 切换到 [填充与线条] 选项卡，设置线条颜色和宽度。
   • 为散点系列添加 [数据标签]，设置标签包括 X 值，标签位置为居中，并设置数字格式。

五、完善图表

给图表添加标题，并将滚动条与图表排版对齐至单元格。当用户单击滚动条时，数据与图表进行相应的变化，从而实现通过滚动条动态查看各项目进展情况的甘特图效果。

最终效果如上图。