204、Excel函数与公式在图表中的应用三--使用逻辑函数辅助创建图表3(制作动态甘特图)

作为项目进度跟进的甘特图，在很多地方都能找到相应的模板。如果觉得麻烦，甚至于可以直接使用excel自带的模板，如下图所示：

在这里不介绍使用自带模板创建甘特图，而是使用公式制作。

如下图所示，为某项目步骤及开始结束时间，根据该项目内容制作甘特图。

Step1、在A14：A17单元格创建相关信息，B14：B15单元格分别输入公式“=MIN(B3:C12)”，“=MAX(B3:C12)”，并将其格式调整为“常规”，如下图所示：

Step2、单击任意单元格，点击【开发工具】→【插入】选择“滚动条”命令，拖动鼠标创建一个“滚动条”，如下图所示：

在滚动条上右击，在弹出的快捷菜单中单击【设置控件格式】命令，打开【设置控件格式】对话框。切换到【控制】选项卡下，将【最小值】设置为1，【最大值】设置为61（也就是结束日期-开始日期的值），【步长】设置为1，【页步长】设置为7，【单元格链接】设置为B16单元格，然后点击【确定】完成设置。如下图所示：

Step3、在B17单元格输入公式“=B14+B16”。在D2单元格输入“步骤已消耗天数”，在D3单元格输入公式“=MAP(C3:C12,B3:B12,LAMBDA(x,y,IFS(B17>=x,x-y,B17>y,B17-y,TRUE,0)))”，公式表示判断“进度日期”B17单元格是否大于等于“计划结束时间”C列对应的值，如果是，则返回当前步骤的总天数，如果不是，则判断“进度日期”B17是否大于“计划开始时间”B列对应的值，如果是，则返回当前步骤所消耗的天数，如果前面的判断均为否，则返回0.

在E2单元格输入“距离步骤结束天数”，在E3单元格输入公式“=MAP(B3:B12,C3:C12,D3#,LAMBDA(x,y,z,y-x-z))”，公式使用“计划结束时间”-“计划开始时间”-“项目已消耗天数”，得到“距离步骤结束天数”，结果如下图所示：

Step4、选中A2：B12单元格区域，依次单击【插入】→【插入柱形图或条形图】→【二维条形图】→【堆积条形图】，创建一个堆积条形图。

选中D2：E12单元格区域，按<ctrl+C>件复制，单击图表，按<ctrl+V>将数据粘贴斤图表。结果如下图所示：

Step5、双击图表纵坐标轴，打开【设置坐标轴格式】选项窗格，在【坐标轴选项】选项卡中的【坐标轴位置】下选中【逆序类别】，使条形图的纵坐标轴按数据源顺序显示，如下图所示：

单击图表横坐标轴，在【设置坐标轴格式】选项窗格中切换到【坐标轴选项】选项卡，设置【边界】的【最小值】为46204（开始日期），【最大值】为46265（结束日期），设置【单位】【大】为7（一周）。单击【数字】选项，设置【类别】为自定义，【格式代码】输入m/d，即“月/日”形式，最后单击【添加】完成横坐标轴的设置，如下图所示：

Step6、单击图表数据系列，在【设置数据系列格式】选项窗格中切换到【系列选项】卡，设置【间隙宽度】为20%。

单击“计划开始时间”数据系列，在【设置数据系列格式】选项窗格中切换到【填充与线条】选项卡，设置【填充】→【无填充】。

单击“项目已消耗天数”数据系列，在【设置数据系列格式】选项窗格中切换到【填充与线条】选项卡，设置【填充】→【纯色填充】，在【主题颜色】面板中设置颜色为蓝色。

单击“距离步骤结束天数”数据系列，在【设置数据系列格式】选项窗格中切换到【填充与线条】选项卡，设置【填充】→【纯色填充】，在【主题颜色】面板中设置颜色为灰色。如下图所示：

Step7、添加分隔线

单击B17单元格，复制该单元格，单击图表区，在【开始】选项卡中单击【粘贴】下拉按钮，在下拉菜单中选择【选择性粘贴】命令调出【选择性粘贴】对话框，设置【添加单元格】为【新建系列】，【数值（Y）轴在】为【列】。最后单击【确定】完成设置，如下图所示：

单击刚添加的数据系列，在【插入】选项卡中单击【插入散点图（X、Y）或气泡图】命令，选择【散点图】，将系列图表类型更改为散点图。

右击图表绘图区，在快捷菜单中单据【选择数据】命令调出【选择数据】对话框，单击选中【系列4】再单击【编辑】按钮，打开【边界数据系列】对话框没咋【X轴系列值】中清除已有内容，设置单元格引用为B17，在【Y轴系列值】中清除已有内容，输入1，然后单击【确定】按钮完成设置。如下图所示：

Step8、单击图表中的次要纵坐标轴，在【设置坐标轴格式】选项卡中切换到【坐标轴选项】选项卡。设置【边界】的【最小值】为0，【最大值】为1（散点系列的【Y轴系列值】为1），单击【标签】选项卡，设置【标签位置】为【无】，将次要纵坐标轴隐藏。如下图所示：

Step9、单击散点系列，在【图表设计】选项卡下单击【添加图表元素】按钮，在下拉菜单中依次单击【误差线】→【标准误差】，如下图所示：

单击图表区，在【格式】选项卡中单击【图表元素】下拉按钮，在下列菜单中单击【系列4Y误差线】，选中误差线后按<ctrl+1>组合键调出【设置误差线格式】选项窗格。

切换到【误差线选项】选项卡，设置【垂直误差线】→【方向】→【负偏差】，【末端样式】→【无线端】，【误差值】→【固定值】，在文本框中输入数值1、

切换到【填充与线条】选项卡，设置【线条】→【实线】，在【主题颜色】中设置颜色为红色，【宽度】为2磅。如下图所示：

Step10、选中散点图系列后右击，在弹出的快捷菜单中单击【添加数据标签】命令，双击图表数据标签，打开【设置数据标签格式】选项窗格，切换到【标签选项】选项卡，设置【标签包括】→【X值】，【标签位置】→【居中】，设置【数字】→【类别】为【自定义】，在【格式代码】框中属兔“m/d”,单价【添加】按钮完成更改。

切换到【填充与线条】选项卡，设置【填充】→【纯色填充】，在【主题颜色】面板中设置颜色为红色。如下图所示：

Step11、调整图表格式，添加图表标题，将滚动条拖到图表下方调整大小位置与图表对齐，如下图所示：

这样创建好后，拖动滚动条就可以动态显示项目状态了，如下视频所示：

已关注

关注

重播分享赞

视频详情