返回目录:excel表格制作
定义一个动态名称例DATA,在数据透视表的数据区域框里填写DATA
1、打开需要设置数据透视表的Excel格式的数据文件,如下图所示:
2、点击最上方菜单栏中的“插入”,然后点击左上方的“表格”图标;在弹出的“创建表”窗口中,百选
择数据来源,小编示例文件的数据区域是A1:D15,选中即可,另外记得勾选“表包含标
题”,然后点击“确定”按钮;
3、再次点度击最上方菜单栏中的“插入”,然后点击点击菜单下方工具栏中的“数据透视表”或者“推知
荐的数据透视表”,参见下图;
4、弹出“选择数据源”窗道口,选择数据来源,小编示例文件的数据区域是A1:D15,因为已经在
步骤2中对这块数据区域做过命名,此处显示为表1;选中后点击“确定”按钮;
5、在弹出的“推荐的数据透视表”窗口中,点击“确定”按钮;
6、根据自己的实际需要,在下图右侧的“数据透视回表字段”中调整相应字段到“列、行或者“值”,
也可以把某个字段作为筛选器”;
7、回到初始数据文答件中,添加两行新行,比如产品类别AA和BB;
8、在数据透视表中,点击鼠标右键,在弹出的快捷菜单中点击“刷新”,见下图;
9、可以看到新加的两行AA和BB已经显示在数据透视表中;如图:
要制作动态的,可以进行计划与实际对比的甘特图,关键要解决两个问题:
1、计划时间与实际时间分成两行显示,这样才能够让甘特图的进度条也分成两行显示出来,实现计划与实际的对比;e799bee5baa6e58685e5aeb9365
2、使用数据透视表来查询,但是要解决透视表作为图表数据源时,如果发生变化,则图表也会错乱的问题。
下面是制作这种图表的步骤:
第一步、制作一个录入界面,同时用VBA将其保存到另一个作为数据库的表格中:
1、录入界面如下图所示:(注:录入界面中,计划时间与实际时间可以在同一行中。)
2、作为数据库的表格如下图所示:(注:本表需要做成可以方便地生成数据透视表的二维表格,并且,这一步是关键步骤,要在这一步通过VBA把录入界面的数据在此时把计划时间、实际时间分成两行显示。)
第二步、根据数据库生成几个数据透视表,其结构略有不同,自己根据需要进行调整(注:此处要使用OFFSET函数定义好上面数据库表中有效数据的范围。):
数据范围=OFFSET(项目进度数据库!$A$1,,,COUNTA(项目进度数据库!$A:$A),COUNTA(项目进度数据库!$1:$1))
1、单项目查询:
2、多项目查询:
3、各项目汇总查询:(只显示每个工程项目的总时间,不显示明细时间)
第三步、制作一个辅助表,用简单的公式把上述几个数据透视表对应起来,所谓简单,即本sheet中某一格=透视表中某一格,但要注意如果透视表中的空白格,可能会显示为0,所以用IF函数作些调整,如
IF(C1<>"空白",单项目查询!$A$1,""):
到了这一步,制作甘特图所需的数据表格已经基本完成了。接下来要准备好制作甘特图所需的数据源。
第四步、用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)
第五步、用普通制作甘特图的方法制作出甘特图:
首先,创建图表大概样式。
在图表向导中,选条形图当中的堆积条形图,并按步骤逐步操作,相关需要选定数据范围的地方,输入上面定义好的名称:
至此,进度图的大体样子已经完成如下,
接下来进行调整:
第1步,把坐标日期调整到所需月份,更改字体及数字格式:
右键点X轴坐标,选“坐标轴格式”,作如下操作:
右键点Y轴,选“坐标轴格式”,更改字体,如果勾选“分类次序反转”,可以把日期坐标调到顶部,否则是在底部。
修改后的图如下:
第2步,分别双击图中“计划开始时间”和“实际开始时间”的图形,在弹出来的对话框中,把【边框】和【内部】都选为无。
第3步,右键点击右边的图例说明,选“图例格式”,更改字体及放置位置:
再把多余的“计划开始时间”、“实际开始时间”删除。
第4步,在图表区空白地方右键点击,选“图表选项”,在网格线中,把X轴、Y轴的主要网格线都勾上。
至此,一个有计划与实际对比,并且可以根据需要由数据透视表进行筛选的动态的甘特图就完成了。
1、是使用示例数据制作图表。在这里使用柱形图作为示例。
2、是调用公式定义名称窗口。接下来,我们需要获得2个定义名称,因为我们的图形包含纵坐标的数据和横坐标的日期,它们都是动态变化的。
3、我们首先为“销售量”设置7a686964616fe78988e69d83339自定义名称。请记住在参考位置输入函数公式。该功能公司可以动态获取最新的销售数据。输入是“= OFFSET(动态图标!$ B $ 1,1 ,, COUNT(动态图标!$ B:$ B))”。
4、我们设置另一个水平轴“day”,以便他可以动态更改。重复第二步调出自定义名称界面,名称设置为日,输入为“= OFFSET(动态图标!$ A $ 1,1,COUNTA(动态图标!$ A:$ A)-1) “,公式有点不同哇,因为它们是文本数据。
5、设置2个名称后,我们将开始修改原始生成的柱形图并调出数据源的选择窗口。
6、我们首先修改销售量。单击编辑,您可以修改它。关键是在参考位置输入预定义的名称。请注意,这是一个相对参考,它将使用先前设置的公式动态更改。
7、在第七步中,我们再次修改横坐标“日”,参见第六步。
8、单击确认,您无法立即看到更改。但是你增加了一天的数据。例如,我输入数据5天。会动态添加到图表的5天图表。