Excel图表——甘特图（Gantt；基于条件格式）

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	计算两个日期之间的工作日数量	参数包括开始日期、结束日期和可选的节假日范围。返回值是开始日期和结束日期之间的工作日数量，排除了指定的节假日。