Excel图表——甘特图(Gantt;基于条件格式)

发布时间:2024年01月18日

1.写在前面

????????Excel可以通过条件格式,设置出gantt图,并随着开始、结束日期的变换而变化,也可以通过完成度,让已完成和未完成部分通过不同的颜色区分进而显示出进度。

? ? ? ? 对您的要求为:

  • 明白Gantt图的用途
  • 需要您能运用Excel条件格式
  • 会使用基本的Excel函数(AND,OR,WEEKDAY,WORKDAY,SUMPRODUCT,IF,NETWORKDAYS等)
  • 会设置Excel单元格自定义格式

当然,不了解也没关系,不求甚解,先跟着后面的步骤一步步操作也可,慢慢的就理解了。

2.创建步骤

? ? ? ? 总体上,该gantt的创建步骤是通过条件格式的方式创建的(周末显示灰装,完成显示绿色,未完成显示浅蓝等都是通过条件格式实现)。图表上面的日期则是通过单元格间的相互依赖实现动态变化的。在此基础上为不同层级的任务类型(如一级任务,二级任务,三级任务等)设置一些特有的格式进行标识即可。

2.1设置日期坐标轴

2.1.1设定日期轴

????????将开始日期和项目开始日期对齐

????????在坐标轴的行(我的所示的是第9行)往后的每一个单元格为前一个单元格+1,形成关联关系。至于后面有多少个单元格需要+1,取决于您的日期跨度,可自行设置拖动即可。(其实不用设置的特别长,一般3个月即可,因为想要移动的看状态,可以通过更改D9单元格内的数值进行动态移动;也可以把K9单元格的内容更改为)

该行的所有单元格格式为日期格式,从gantt列开始往后的单元格调整一下列宽和显示格式。

2.1.2设置月份坐标轴

????????在2.1.1中,我们已经把日期的显示设置好了,针对月份的显示我们在日期的上一行中进行设置。 因为月份不需要每一个单元格都显示,我们此处只设置了每月的第一天,用于区分月份。?

????????在该行中,每一个单元中都输入公式【=IF(DAY(L9)=1,L9,"")】,然后往后拖动即可,这句话的意思是,如果是每个月的第一天则显示,否则为空白。然后再对该行单元格的日期格式进行设置。

????????就能看到如上图所示的效果,每个月仅显示第一天,用于区分月份。

2.1.3设置周坐标轴

????????同样的可以在月和日期行中间插入一行显示周,并且通过if语句仅在每周周一显示,起到简洁的作用。 每个单元格内的内容填写为【=IF(WEEKDAY(K10)=2,"W"&WEEKNUM(K10),"")】。

2.2设置周末标灰

Gantt 显示的区域我们都称之为图区,图区所有向右,向下的单元格(取决于你的表格需要有长和多山任务),如下图所示箭头方向。这个图区就是之后我们各类条件格式要应用的范围。

选中图区最左上角的第一个单元格>条件格式>新增条件格式

大家注意,我们现在选中的是K列11行。

我们的公式为

=OR(WEEKDAY(K$10)=1,WEEKDAY(K$10)=7)

在10前面有美元符号,K前面没有。 $符号是指相对引用和绝对引用,此处不展开讲解。

设置格式为灰色填充。

点击确定,设置规则应用范围。

如:=$K$11:$AC$24。注意此处的11行至24行,K列到AC列,我们图区如果很大,则对应得需要更改24这个值和AC这个值。大家灵活设置即可。

也可以通过点击右侧箭头的图标进行选择。

2.3已完成任务任务格式

选中图区最左上角的第一个单元格(K11)>条件格式>新增条件格式

=AND($E11>0,K$10>=$D11,K$10<=$F11,WORKDAY($D11,$E11*$G11,Holidays)>K$10)

公式说明:

  • $E11>0:? ?工期要大于零。
  • K10>D11:开始日期之后才能显示颜色,开始日期之前的不标识背景色。
  • K10<=F11:要小于结束日期之前的。 这个是防止完成度单元格内的值大于100%时的情况。 如果填写规范,这个条件不要也可以。
  • WORKDAY($D11,$E11*$G11,Holidays)>K$10 :计算每个任务的完成度,比如一个任务跨越了10天完成度为50%时,有5个单元格显示为绿色。
  • AND:进行各条件的组合,要均满足才执行条件格式。

函数说明:WORKDAY函数用于计算从给定的开始日期开始,指定数量的工作日之后的日期。它通常用于计算项目完成日期、任务截止日期等。

2.4设置未完成为浅蓝色

方式和前面一样,此处只共享条件格式的公式。

=AND($E11>0,K$10>=$D11,K$10<=$F11,WORKDAY($D11,$E11*$G11,Holidays)<=K$10)

2.5设置一级任务格式

????????如下图所示,不同层级的任务可以根据其规律设置条件格式,也可手动添加背景、加粗等操作。此处不做限制。

如此次模板中的,是根据每个一级任务上的单元格都是空白的,来进行判断。

条件格式公式为:

=ISBLANK($B10)

应用范围可为:

2.6设置父任务完成度公式

=SUMPRODUCT(G12:G14,E12:E14)/SUM(E12:E14)

2.7项目整体进度公式

=SUMPRODUCT(G11:G42,E11:E42)/SUM(E11:E42)

2.8设置今天条件格式

如今天是2024年1月17号,会显示一条竖线标识出时间线。如下图所示

设置条件格式公式

=K$10=TODAY()

设置格式为

3.附录:函数使用说明

函数用途参数
AND用于判断多个条件是否都为真参数是多个逻辑值或条件,如果所有参数都为真,则返回TRUE;如果任何一个参数为假,则返回FALSE。
OR用于判断多个条件是否至少有一个为真参数是多个逻辑值或条件,如果至少有一个参数为真,则返回TRUE;如果所有参数都为假,则返回FALSE。
WEEKDAY返回指定日期是星期几(1-7表示周一到周日)参数是日期值和可选的类型参数,用于指定星期的开始日期(1表示周日,2表示周一,默认为1)。返回值是一个介于1到7之间的数字,表示指定日期的星期几。
WORKDAY计算从指定日期开始的若干工作日后的日期参数包括开始日期、工作日数量和可选的节假日范围。返回值是开始日期后指定工作日数量的日期,排除了指定的节假日。
SUMPRODUCT对对应元素相乘后的结果求和参数是多个数组,对应位置的元素相乘后再求和。常用于加权求和或条件求和。
IF根据条件返回不同的值参数是一个条件,如果条件为真,则返回一个值,否则返回另一个值。可用于在数据集中进行条件判断并返回相应的结果。
NETWORKDAYS计算两个日期之间的工作日数量参数包括开始日期、结束日期和可选的节假日范围。返回值是开始日期和结束日期之间的工作日数量,排除了指定的节假日。
文章来源:https://blog.csdn.net/weixin_43957681/article/details/135631214
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。