如何用函數製作【甘特圖】?顏色形狀隨意改,功能更多樣!

Last Updated on 2021 年 9 月 18 日 by 小幫手

How to create a Gantt chart with formulas in Google Sheets 封面圖片

「甘特圖」是一個比較特別的圖表類型,它可以說是「專案管理」當中相當重要的管理工具之一。而如果想要在ExcelGoogle試算表裡面繪製一張甘特圖,除了用外掛或長條圖來解決之外,其實也可以用函數來繪製它,具體怎麼操作?一起來看看吧!

如何用函數來製作甘特圖?

這裡會用到的函數主要有兩個,分別是「IF函數」「AND函數」,這兩個都是比較簡單的邏輯函數,搭配方式也不難,接下來看要怎麼用這兩個函數弄出甘特圖的樣子吧!

範例表格

為了方便說明,就用下面這個剪輯流程專案來當作範例表格吧。

甘特圖的範例表格


步驟一:填入甘特圖日期

從這張範例表格來看,甘特圖的日期可以從9月1號開始,因此我在表格的右邊用「自動填入」的方式來填上一系列的日期。

填入甘特圖的日期


步驟二:填入IF函數

接著要開始輸入函數了,這邊我在甘特圖空白處最左上角的地方輸入「IF函數」當作開頭。

填入IF函數


步驟三:填入AND函數

IF函數第一個引數的位置這邊,我們要填入【AND】的函式,所以整個公式會像下面這樣:

=IF(AND($D2-E$1>=0,E$1-$C2>=0)

這個AND裡面的運算式子看起來好像有點複雜,其實不難,因為它只是用來判斷兩個式子有沒有成立。

第一個邏輯運算式$D2-E$1>=0,是用來判斷「結束日期」-「當天日期」是不是大於等於零;

第二個邏輯運算式E$1-$C2>=0,是用來判斷「當天日期」-「開始日期」是不是大於等於零。

而加上「$」的符號是要讓公式複製到其他儲存格的時候,可以固定在某個資料欄或資料列上。

填入AND函數的公式


步驟四:繼續完成IF公式

最後再把【IF公式】最後的部分完成,也就是TRUE跟FLASE分別要回傳什麼。

  • 如果AND函數回傳的結果是TRUE,也就是當AND函式裡面的2個條件都成立的時候,就回傳「1」;
  • 如果AND函數回傳的結果是FALSE,也就是AND函式裡面其中1個或2個條件不成立的時候,回傳空值「””」就好。
完成IF函數的公式


步驟五:自動填入其他儲存格

公式完成之後,利用右下角的藍色小方塊,來複製公式到其他的儲存格裡面。

從下面這張圖就可看出,只要「當天日期」小於開始日或大於結束日,就不符合AND函數裡面的運算式,只要AND函數的結果是FAlSE,IF函數最後回傳的結果就是空值,儲存格就會保持空白的狀態。

但只要AND函數的結果是TRUE,IF函數就會回傳「1」。

自動填入公式到其他儲存格


步驟六:打開條件式格式設定

再來打開功能選單的格式條件式格式設定

打開條件式格式設定


步驟七:調整長條顏色

  • 「套用範圍」選擇套甘特圖的長條區域範圍。
  • 「格式規則」可以選非空白,或是可以調整為「值等於1」
  • 「顏色」的部分則依個人喜好調整想要的長條顏色,如果把文字顏色填滿顏色都調成同一種顏色的話,就會變成單純的長條顏色。
格式設定樣式的顏色調整


完成甘特圖

最後再把「凍結窗格」線拉一下,基本的甘特圖就大功告成囉!

拖曳凍結窗格的線,讓甘特圖方便捲動


測試甘特圖

完成甘特圖之後,還要測試看看它能不能運作,你可以用旁邊的「日期選擇器」來更改開始日或結束日,看看旁邊的甘特圖會不會跟著變動,同時看一下日期有沒有正確。

測試甘特圖日期跟專案日期的連動性


aifian APP推薦活動
[ 這 是 APP 推 廣 活 動 廣 告 ]


為甘特圖新增交替條紋色

如果覺得這張甘特圖看起來很單調的話,其實還可以替他加點效果,像是Google試算表「替代顏色」就可以用來搭配一下。

先把你要填色的範圍框選起來之後,點擊功能選單的格式替代顏色,它就會迅速幫這個範圍填上條紋的交替顏色。

為甘特圖加上「替代顏色」效果


這樣甘特圖看起來會比較條列分明,而且也不會影響到「條件式格式」的顏色效果。

甘特圖有替代顏色的效果


改變甘特圖的長條外觀

如果不喜歡這樣的長條,覺得太過單調的話

1.你可以先把文字顏色條淺一些。

調整文字顏色淺一點


2.然後更改【IF函式】第二個引數的部分,把它換成別的文字或特殊符號。

修改IF公式的第二個引數,調整為其他文字或特殊符號。


3.這樣甘特圖裡面的長條,就可以擁有特製的圖案或文字。

將帶有特殊符號的IF函式,複製到甘特圖的長條區域裡面


4.甚至你也可以關掉條件式格式的「填滿顏色」效果,只保留「文字顏色」,這樣甘特圖的長條外型就可以隨意設定了。

調整甘特圖的文字顏色,並且隱藏填滿顏色的效果


5.還有一個做法,就是先在旁邊新增一個資料欄(輔助欄),然後每一個資料列都給它不同的「文字」或「特殊符號」。

在專案旁邊新增一個資料輔助欄,然後填入相關的文字或特殊符號


6.再把【IF函數】的第二個引數指向新資料欄的儲存格,就像下面這張圖一樣,然後記得要把A欄給固定下來,也就是改成$A2這樣。

修改完IF公式之後,把它複製到甘特圖其他的儲存格裡面,這樣甘特圖的長條就能擁有不同的顯示方式了,不再是一般的長條圖形。

把IF函式的第二個引數指定為輔助欄位的儲存格,這樣甘特圖的長條就可以有不同的外觀


aifian APP推薦活動
[ 這 是 APP 推 廣 活 動 廣 告 ]


不同長條套用不同的顏色

如果覺得甘特圖的顏色太單調的話,也可以到「條件式格式設定」去調整每一列的顏色,格式規則可以參考下面這張圖,然後再進一步設定不同的「文字顏色」或「填滿顏色」,就會有下面這兩張甘特圖的效果了,每一條長條的顏色都是不一樣的。

為甘特圖的每個資料列,都設定不同的條件式格式規則,每個長條就能有不同的顏色
每個甘特圖長條都有不同的顏色


為甘特圖新增警示功能

有時候我們可能會把開始日或結束日的日期打錯,這時候也不用擔心旁邊的甘特圖會出錯喔,因為它有公式可以幫忙確認,只要日期不正確,像是開始日比結束日還要晚,旁邊的甘特圖長條就不會顯示出來。

甘特圖日期輸入錯誤,不會顯示長條出來


如果可以的話,也可以加一點警示的效果,看是要用「資料驗證」的方式,還是要用「條件式格式設定」的方式,設定的條件可以運用【AND】這個函數,可以像我下面這張圖的設定一樣:

  • 「條件式格式設定」的部分,設定自訂公式=AND(D2<C2),並且套用綠色背景跟刪除線。
  • 「資料驗證」的部分,也是設定自訂公式=AND(D2>=C2),然後顯示警告。
設定警示用的「條件式格式規則」跟「驗證資料」


條件式格式設定跟資料驗證都設定完之後,來測試一下。

像下面這樣把結束日改得比開始日早,那結束日就會顯示警示標誌與綠色背景,可以提醒我們這格的日期要重新設定。

日期輸入錯誤會顯示警示訊息


aifian APP推薦活動
[ 這 是 APP 推 廣 活 動 廣 告 ]


結語

看完這麼多甘特圖的設定之後,可以感受到使用函數的功能還是比較多的,因為之前有提到「簡易版的甘特圖」,都沒有用到任何一個函數,所以功能其實非常有限!

其實用「函數」製作甘特圖也不止這種作法,本文所示範的內容只是其中一種方式而已,重點是可以學習函數的運用方式,以及甘特圖想呈現什麼樣的效果。

如果不想使用函數,用「圖表工具」或是「外掛」也都可以做出甘特圖喔!

Reference:文件編輯器說明


延伸閱讀

【Google試算表】全部文章總整理

Google試算表如何【插入圖表】?自動生成圖表的方法?

Google試算表如何繪製【圓餅圖】?像披薩一樣的分離效果怎做?

Google試算表|用SPARKLINE把【進度條】放進儲存格裡!

Google試算表|用REPT製作另類的【進度條】,形狀隨你選!


aifian APP推薦活動


最後
§ 很感謝你的閱讀 §
如果你喜歡這篇文章的話

→ 歡迎 留言分享 喔!

→ 歡迎點下面這張圖來【贊助】支持我!讓我繼續為你創作更多內容!

贊助我一杯咖啡


發表迴響