ExcelHome技术论坛

 找回密码
 免费注册

QQ登录

只需一步,快速开始

快捷登录

搜索
EH技术汇-专业的职场技能充电站 妙哉!函数段子手趣味讲函数 Excel服务器-会Excel,做管理系统 Excel Home精品图文教程库
HR薪酬管理数字化实战 Excel 2021函数公式学习大典 Excel数据透视表实战秘技 打造核心竞争力的职场宝典
300集Office 2010微视频教程 数据工作者的案头书 免费直播课集锦 ExcelHome出品 - VBA代码宝免费下载
用ChatGPT与VBA一键搞定Excel WPS表格从入门到精通 Excel VBA经典代码实践指南
查看: 25013|回复: 10

[原创] 用数据透视表制作可以计划与实际对比的甘特图的步骤

[复制链接]

TA的精华主题

TA的得分主题

发表于 2012-6-19 17:26 | 显示全部楼层 |阅读模式
[广告] Excel易用宝 - 提升Excel的操作效率 · Excel / WPS表格插件       ★免费下载 ★       ★ 使用帮助
本帖最后由 gztraveller 于 2012-6-19 17:37 编辑

要制作动态的,可以进行计划与实际对比的甘特图,关键要解决两个问题:
1、计划时间与实际时间分成两行显示,这样才能够让甘特图的进度条也分成两行显示出来,实现计划与实际的对比;
2、使用数据透视表来查询,但是要解决透视表作为图表数据源时,如果发生变化,则图表也会错乱的问题。
下面是制作这种图表的步骤:

第一步、制作一个录入界面,同时用VBA将其保存到另一个作为数据库的表格中:
1、录入界面如下图所示:(注:录入界面中,计划时间与实际时间可以在同一行中。)
1.jpg

2、作为数据库的表格如下图所示:(注:本表需要做成可以方便地生成数据透视表的二维表格,并且,这一步是关键步骤,要在这一步通过VBA把录入界面的数据在此时把计划时间、实际时间分成两行显示。)

2.jpg


第二步、根据数据库生成几个数据透视表,其结构略有不同,自己根据需要进行调整(注:此处要使用OFFSET函数定义好上面数据库表中有效数据的范围。):

数据范围=OFFSET(项目进度数据库!$A$1,,,COUNTA(项目进度数据库!$A:$A),COUNTA(项目进度数据库!$1:$1))

1、单项目查询:

3.jpg


2、多项目查询:

4.jpg

3、各项目汇总查询:(只显示每个工程项目的总时间,不显示明细时间)

5.jpg

第三步、制作一个辅助表,用简单的公式把上述几个数据透视表对应起来,所谓简单,即本sheet中某一格=透视表中某一格,但要注意如果透视表中的空白格,可能会显示为0,所以用IF函数作些调整,如

IF(C1<>"空白",单项目查询!$A$1,""):

6.jpg

到了这一步,制作甘特图所需的数据表格已经基本完成了。接下来要准备好制作甘特图所需的数据源。

第四步、用OFFSET函数把甘特图所需的进度条1、2、3、4,Y座标轴名称(就是在图上显示的工作内容),甘特图的总数据范围定义好:

图表_进度条1        =OFFSET(制图辅助!$C$1,1,1,COUNTA(制图辅助!$C:$C)-COUNTIF(制图辅助!$C:$C,"空白")-1)
图表_进度条2        =OFFSET(制图辅助!$C$1,1,2,COUNTA(制图辅助!$C:$C)-COUNTIF(制图辅助!$C:$C,"空白")-1)
图表_进度条3        =OFFSET(制图辅助!$C$1,1,3,COUNTA(制图辅助!$C:$C)-COUNTIF(制图辅助!$C:$C,"空白")-1)
图表_进度条4        =OFFSET(制图辅助!$C$1,1,4,COUNTA(制图辅助!$C:$C)-COUNTIF(制图辅助!$C:$C,"空白")-1)
图表_进度图Y轴名称        =OFFSET(制图辅助!$C$1,1,-1,COUNTA(制图辅助!$C:$C)-COUNTIF(制图辅助!$C:$C,"空白")-1)
图表_制图辅助范围        =OFFSET(制图辅助!$B$1,,,COUNTA(制图辅助!$C:$C)-COUNTIF(制图辅助!$C:$C,"空白"),6)

第五步、用普通制作甘特图的方法制作出甘特图:

首先,创建图表大概样式。

在图表向导中,选条形图当中的堆积条形图,并按步骤逐步操作,相关需要选定数据范围的地方,输入上面定义好的名称:

7.jpg

8.jpg

9.jpg

10.jpg

至此,进度图的大体样子已经完成如下,
11.jpg

接下来进行调整:

第1步,把坐标日期调整到所需月份,更改字体及数字格式:

右键点X轴坐标,选“坐标轴格式”,作如下操作:

12.jpg

13.jpg

14.jpg

右键点Y轴,选“坐标轴格式”,更改字体,如果勾选“分类次序反转”,可以把日期坐标调到顶部,否则是在底部。

15.jpg


修改后的图如下:

16.jpg


第2步,分别双击图中“计划开始时间”和“实际开始时间”的图形,在弹出来的对话框中,把【边框】和【内部】都选为无。

17.jpg


第3步,右键点击右边的图例说明,选“图例格式”,更改字体及放置位置:

18.jpg


再把多余的“计划开始时间”、“实际开始时间”删除。

19.jpg

第4步,在图表区空白地方右键点击,选“图表选项”,在网格线中,把X轴、Y轴的主要网格线都勾上。

20.jpg

至此,一个有计划与实际对比,并且可以根据需要由数据透视表进行筛选的动态的甘特图就完成了。

21.jpg


该贴已经同步到 gztraveller的微博

该贴已经同步到 gztraveller的微博

评分

3

查看全部评分

TA的精华主题

TA的得分主题

发表于 2012-9-12 10:48 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
excel 2007编辑不了坐标轴格式的,是怎么回事?高手请指教!

TA的精华主题

TA的得分主题

发表于 2012-9-4 23:34 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2012-10-29 16:31 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
深奥啊,高人指点就好了

TA的精华主题

TA的得分主题

发表于 2013-5-14 22:14 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2013-5-31 20:13 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2013-10-12 13:20 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2014-11-5 11:07 | 显示全部楼层

TA的精华主题

TA的得分主题

发表于 2016-5-23 10:48 | 显示全部楼层
[广告] VBA代码宝 - VBA编程加强工具 · VBA代码随查随用  · 内置多项VBA编程加强工具       ★ 免费下载 ★      ★使用手册
我也想要呢

TA的精华主题

TA的得分主题

发表于 2016-5-25 14:17 | 显示全部楼层
您需要登录后才可以回帖 登录 | 免费注册

本版积分规则

手机版|关于我们|联系我们|ExcelHome

GMT+8, 2024-3-30 00:00 , Processed in 0.057554 second(s), 11 queries , Gzip On, Redis On.

Powered by Discuz! X3.4

© 1999-2023 Wooffice Inc.

沪公网安备 31011702000001号 沪ICP备11019229号-2

本论坛言论纯属发表者个人意见,任何违反国家相关法律的言论,本站将协助国家相关部门追究发言者责任!     本站特聘法律顾问:李志群律师

快速回复 返回顶部 返回列表