一目了然打造隨日期變化的動態甘特圖

平淡

○構建數據

比如圖1是某公司一個項目的進度計劃表，按圖示先在B2單元格中輸入任務的開始時間，在C2到C7單元格中依次輸入每個子項目的預計耗時。接著在B3單元格中輸入公式“=B2+C2”（表示下個項目的開始時間是“上個項目的開始時間+預計耗時”），

然后下拉填充到B7單元格，這樣便可以將每個子項目的開始時間自動地計算出來了（圖2）。

為了方便查看，我們先在A13單元格中隨意輸入一個日期，接著在D2單元格中輸入公式“=IFS（B2>$A$13，0，（B2+C2）<$A$13，C2，B2<=$A$13，$A$13-B2）”，然后下拉填充到D7單元格。再定位到E2單元格并輸入公式“=C2-D2”，下拉填充到E7單元格（圖3）。

公式解釋：

在D列通過I FS函數將B列中的日期（開始時間）和A13單元格中的日期進行對比并顯示對應的值。

1如果B列中的日期比A13單元格中的大，那么“已完成”就顯示為0，因為項目尚未開始。

2如果“B2+C2”比A13單元格中的日期小，那么“已完成”就顯示為C2單元格中的數值，即已過去的天數。

3如果B列中的日期小于等于A13單元格中的日期，則“已完成”就顯示為“A13-對應B列單元格”的數值，表示任務只完成其中的一部分。

最后在F2單元格中輸入公式“=SUM（$C$2：C2）”，然后下拉填充到F7單元格（圖4）。該公式表示從C2單元格開始對數值進行累加，目的是為了在下個任務的甘特圖的前方疊加上個任務已完成的數值占位符，這樣前后任務的甘特圖才能像圖1所示那樣無縫地銜接排列。

○制作甘特圖

構建完數據后，我們就可以制作甘特圖了。在H2單元格中輸入公式“=REPT（"█"，D2）&REPT（ " ? " ， E 2 ） ”，H 3 單元格中輸入公式“ = R E P T （ " " ， F 2 * 4 ）&REPT（ " █ "， D3）&REPT （ "?"，E3）”并下拉填充到H7單元格，同時將H2：H7區域的字體設置為“等線”、加粗顯示（圖5）。

公式解釋

使用REPT函數根據D列和E列中的數值，分別重復填充“█”和“?”字符作為“已完成”和“未完成”的圖標（這兩個字符通過點擊“插入→符號”進行選擇）。H2單元格作為初始單元格直接填充字符，從H3單元格開始添加函數“=REPT（" "，F2*4）”（使用空格填充）并和原來的公式進行連接，因為要達到圖1所示的前后銜接排列效果，在H3單元格中的字符圖標之前要增加H2單元格中數值的占位空格。比如在圖1中H 2單元格對應的數值是“5”，因此要增加“5*4=20”個空格字符（正好對應5個“█”字符，一個字符對應4個半角空格字符）。

接下來選中H2： H 7區域，依次點擊“開始→ 條件格式→ 新建規則→只為包含以下內容的單元格設置格式”，設置一個包含“█”字符的單元格，將其字體顏色設置為“綠色”（表示項目全部完成）。同上，設置一個包含“?”字符的單元格，字體顏色設置為“紅色”（表示項目未完成）。再設置一個包含“█”和“?”字符的單元格，字體顏色設置為“藍色”（表示項目正在進行中），這樣每個項目都可以通過對應的圖標和顏色進行區分了（圖6）。

然后定位到H1單元格并輸入公式“=IFS（B2>A13，"任務尚未開始"，B2<=A13，TEX T（A13，"y y y/mm/dd"）&"工作進度圖"）”，最后在G9：G12區域中添加圖標備注文字。

○實際使用

完成上述的操作后，我們只需更改A13單元格中的日期，那么甘特圖中的圖標就會自動發生變化了。比如在A1 3 單元格中輸入2021/4/27，因為項目尚未開始，此時甘特圖中的圖標全部顯示為“?”（表示項目均未完成），同時它的標題顯示為“任務尚未開始”。如果在A13單元格中輸入2021/5/18，這時已經超過所有任務的日期，此時甘特圖中的圖標全部顯示為綠色的“█”（表示項目已經全部完成），同時它的標題顯示為“2021/5/18工作進度圖”（圖7）。

當然，在實際使用時A13單元格是要和當天日期相對應的，在A 1 3 單元格中輸入公式“=TODAY（）”，再將不需要的D、E、F列隱藏。以后我們每天打開工作表后就可以看到甘特圖中的內容和標題都會發生不同的變化，這樣查看工作進度是不是更方便了（圖8）？