EXCEL VBA 独家秘籍: 线表升级版,终极解决方案

上一篇文章讲了如何根据日期生成甘特图，这是最基本的用法，日期是基于自然天显示的，每个日期都能找到对应的日期列，但是如果后面的线表日期是按照月或年，这个方法就失效了！如下表所示：部分日期找不到对应的列而出现错误值！

解决方案：

1、对模板进行升级改造，增加两列偏移量，以便知道日期天数对应的单元格位置比例，如15日对应单元格的一半位置，10日对应单元格三分之一的位置。

2、对开始和结束日期对应的列进行函数升级，按照模糊匹配进行查找，注意match的最后一个参数是1，不是0。1是模糊匹配，0是精确匹配。

E2=MATCH(C2,$A$1:$P$1,1)

F2=MATCH(D2,$A$1:$P$1,1)

3、计算偏移比例，DAY(日期）：获得日期中的“日”，EOMONTH(日期,0)获得当月最后一天的日期，DAY(EOMONTH(日期,0))：获得当月有多少天，如5月有31天，4月有30天。用天数除以当月总天数就可以换算成日期所在单元格的位置比列，

注意：G2和H2稍有不同，因为开始日期对应当天开始，结束日期对应当天结束，中间有一天的差异。如5-1为开始日期对应的比例为0/31，如果为结束日期对应的1/31

G2=(DAY(C2)-1)/DAY(EOMONTH(C2,0))

H2=DAY(D2)/DAY(EOMONTH(D2,0))

分别下列公式到最后一列，得到下表：

4、按ALT+F11，插入模块，复制代码如下：

Sub 画甘特图按月()

Dim SHP As Shape

ActiveSheet.Shapes.SelectAll '选中当前工作表所有图形

Selection.Delete '删除所有选中图形

T = Cells(Rows.Count, 1).End(xlUp).Row '最后一行行号

For I = 2 To T

S = Cells(I, 5) '开始日期所在列

F = Cells(I, 6) '结束日期所在列

SP = Cells(I, 7) '开始日期偏移量

FP = Cells(I, 8) '结束日期偏移量

X0 = Cells(I, S).Left + Cells(I, S).Width * SP '开始点横坐标

Y0 = Cells(I, S).Top + Cells(I, S).Height / 2 '开始点纵坐标

X1 = Cells(I, F).Left + Cells(I, F).Width * FP '终点横坐标

Y1 = Y0 '终点纵坐标

Set SHP = ActiveSheet.Shapes.AddLine(X0, Y0, X1, Y1) '画甘特图

If Cells(I, 2) = "关键" Then

SHP.Line.ForeColor.RGB = rgbRed ' 关键任务为红色

Else

SHP.Line.ForeColor.RGB = rgbBlue ' 其它任务为蓝色

End If

SHP.Line.Weight = 2 '设置线宽

End Sub

5、将鼠标放在代码内，点击运行或F5，线表自动生成，如下图所示：

6、选中表头日期，自定义数字格式为“MM月”

最后，线表如下图所示：