关于用EXCEL实现甘特图表的技巧总结

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

关于用EXCEL实现甘特图表的技巧总结

实现的功能：
1、可选择 周末节假日是否安排工作
2、可隐藏周六日显示
3、可以使用聚光灯效果（不妨碍复制粘贴功能）
4、可以切换 日 周 月 季度 显示
5、可以分别显示 计划 实际 完成 超计划 超完成 不同状态
6、实现WBS自动编号
7、实现周六日 突出颜色显示
8、实现完成后 变暗颜色显示
9、突出显示今天得日期
10,、突出显示周六日的日期

1、用到的函数

（1）WEEKDAY（date,type）

返回代表一周中第几天的数值，是一个1到7（或0到6）之间的整数；
type为确定返回值类型的数字，数字1 或省略则1 至7代表星期天到星期六，数字2 则1 至7 代表星期一到星期天，数字3则0至6代表星期一到星期日。

例如：

• 计算项目开始日期所在日那一周的第一天（周一）

  =C4-WEEKDAY(C4,1)+2+7*(I4-1)

• 根据weekday返回的星期几显示对应字母

  =CHOOSE(WEEKDAY(K6,1),"S","M","T","W","T","F","S")

• 计算项目开始日期所在周数

  ="Week "&(K6-($C$4-WEEKDAY($C$4,1)+2))/7+1

• 判断是否为周末

  =OR(WEEKDAY(K$8,3)=6,WEEKDAY(K$8,3)=5)

（2）NETWORKDAYS(start_date，end_date，holidays)和 WORKDAY()

NETWORKDAYS函数返回参数start-data和end-data之间完整的工作日（不包括周末和专门指定的假期）数值。

第三个参数数个区域，可以定义需要排除的休息日期。

例如： 计算 E9，F9 两个日期之间的工作日，排除周六日和$I2：$I20指定的休息日

=NETWORKDAYS（E9，F9，$I2：$I20）

（3）EDATE（date,number）

EDATA函数返回date日期之后的number指定个月的日期。

例如：当前日期是E9=2019年10月8日，EDATE（E9，1），返回2019年11月9日的日期

2、建立可切换周期的函数组合

=IF($H$4="周",$D$4+7*($H$3-1)+7*(Q7-1),IF($H$4="日",($D$4-WEEKDAY($D$4,2)+1)+($H$3-1)+Q7-1,IF($H$4="月",EDATE($D$4,($H$3-1)+Q7-1),EDATE($D$4,3*($H$3-1)+3*(Q7-1)))))

3、条件格式里公式的设定

计划= AND(M$5>$D9,L$5<=$E9)

实际= AND(M$5>$G9,L$5<=$H9)

超计划= AND(M$5>IF($E9<=$G9,$G9,$E9+1),L$5<=$H9)

完成= AND($J9>0,M$5>$G9,L$5<=($G9+INT(($H9-$G9)*$J9)))

超完成= AND(M$5>IF($E9<=$G9,$G9,$E9+1),$J9>0,L$5<=($G9+INT(($H9-$G9)*$J9)))

4、聚光灯用到的VBA代码

Private Sub Worksheet_Selection change(ByVal Target As Range)
On Error Resume Next
If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then Exit Sub
ActiveSheet.Calculate
End Sub

5、生成自动编号函数

=IF(A2="","",IF(A2>OFFSET(A2,-1,0,1,1),IF(OFFSET(B2,-1,0,1,1)="","1",OFFSET(B2,-1,0,1,1))&REPT(".1",A2-MAX(OFFSET(A2,-1,0,1,1),1)),IF(ISERROR(FIND(".",OFFSET(B2,-1,0,1,1))),REPT("1.",A2-1)&IFERROR(VALUE(OFFSET(B2,-1,0,1,1))+1,"1"),IF(A2=1,"",IFERROR(LEFT(OFFSET(B2,-1,0,1,1),FIND("^",SUBSTITUTE(OFFSET(B2,-1,0,1,1),".","^",A2-1))),""))&VALUE(TRIM(MID(SUBSTITUTE(OFFSET(B2,-1,0,1,1),".",REPT(" ",LEN(OFFSET(B2,-1,0,1,1)))),(A2-1)*LEN(OFFSET(B2,-1,0,1,1))+1,LEN(OFFSET(B2,-1,0,1,1)))))+1)))

详细讲解参见B站系列视频：

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏6 回帖

实现的功能：
1、可选择 周末节假日是否安排工作
2、可隐藏周六日显示
3、可以使用聚光灯效果（不妨碍复制粘贴功能）
4、可以切换 日 周 月 季度 显示
5、可以分别显示 计划 实际 完成 超计划 超完成 不同状态
6、实现WBS自动编号
7、实现周六日 突出颜色显示
8、实现完成后 变暗颜色显示
9、突出显示今天得日期
10,、突出显示周六日的日期

多谢分享经验！

最好上传附加，这些当做工具用比较好。

B站评论区 可以下载所有模版，搜 “名字叫麦兜的狗狗”

車樹森 发表于 2019-12-7 11:55
最好上传附加，这些当做工具用比较好。

B站 有模版下载

关于excel聚光灯效果，条件格式设置步骤完成之后，聚光灯效果已经实现了，但可能会出现无法将单个或多个单元格复制粘贴数据,解决办法就是:

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
On Error Resume Next
If Application.CutCopyMode = xlCopy Or Application.CutCopyMode = xlCut Then Exit Sub
ActiveSheet.Calculate
End Sub
纯VBA 实现方式：


Private Sub Worksheet_SelectionChange(ByVal Target As Range)
With Target
     .Parent.Cells.Interior.ColorIndex = xlNone
     .EntireRow.Interior.Color = RGB(230, 230, 250)
     .EntireColumn.Interior.Color = RGB(250, 235, 215)
End With
End Sub
节选自：https://symphonyh.github.io/cloudblog/2018/09/30/excel-option/

多谢分享经验！

计算某日所在那一周的周一 固定用法：
如果D5=12月13日(周五），D5-WEEKDAY(D5,2)+1返回结果12月9日（周一）