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

Last Updated on 2021 年 11 月 21 日 by 小幫手

How to create Progress bar with SPARKLINE function in Google sheets 封面圖片

我們在使用 ExcelGoogle 試算表來做一些項目管理時,有一部分的項目想做進度追蹤,會用到「進度條」這類型的圖表資料,但如果想把它插進儲存格裡面,就需要一些技巧了,其中一種方法就是用 SPARKLINE 來製作,它的進度條有很不錯的視覺效果!

SPARKLINE 的用途

先前有介紹過「SPARKLINE」這個函數的用法,它其實就是一個可以在儲存格裡建立「迷你圖表」的函數。

SPARKLINE 可以建立的圖表類型有這4種:

而接下來要介紹的「進度條」,就是要用「堆疊長條圖」的方式來製作,堆疊長條圖的公式語法基本上是下面這樣:

=SPARKLINE(data,{"charttype","bar"})

後面的大括號裡面,除了「Charttype」的參數之外,還可以再設定其他類型的參數來調整「進度條的外觀」,要注意每一個參數要用分號 ; 來分開。詳細的參數跟公式設定可以參考這篇文章:「Google 試算表【SPARKLINE】直接在儲存格建立「迷你圖表」!

至於進度條的做法就繼續往下看吧!


SPARKLINE 怎麼製作進度條?

進度條的選取範圍

想用 SPARKLINE 製作「進度條」,首先範圍的選取很重要,因為如果沒選好的話,進度條就會失真而沒有意義。

以下圖的表格為例,這是一張閱讀進度的表格跟進度追蹤,如果第一個引數的範圍是框選「總頁數~已讀頁數」,也就是像下面這條公式的範圍一樣:

=SPARKLINE(B3:C3,{"charttype","bar"})

這樣 SPARKLINE 回傳的進度條就不是我想要的,因為 SPARKLINE 的堆疊長條圖是按比例分配的,如果總頁數已讀頁數都是 1000 頁,那進度條就會分成一半,進度永遠達不到終點。

SPARKLINE選取到錯誤的儲存格範圍


正確的範圍

但如果把範圍框選為「已讀頁數~未讀頁數」,只要已讀頁數越多,未讀的頁數就會減少,這樣的進度條才能達到終點

=SPARKLINE(C3:D3,{"charttype","bar"})

另外,如果沒有在公式裡設定顏色的話,堆疊長條圖會有它的預設顏色,就像下圖一樣是「橘色」與「藍色」。

SPARKLINE選取到正確的儲存格範圍


進度條加入 max 參數

不過,其實也不一定需要未讀頁數,如果第一個引數只放「已讀頁數」,那後面的大括號裡面只要再加入 max 的參數就好,「max」是用來設定進度條上限的,所以「max」的值就可以設定為總頁數的儲存格「B3」,公式如下:

=SPARKLINE(C3,{"charttype","bar";"max",B3})

這樣 SPARKLINE 回傳的進度條也是可以 OK 的。

SPARKLINE函式裡面加入max參數


而且用這種方法設定的進度條,它的另一邊就會是透明的,這樣當你調整格子的 背景顏色 時,就不會被「進度條」擋到。

SPARKLINE有max的進度條不會擋到背景顏色


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


進度條的顏色設定

除了使用進度條原本的顏色設定之外,你也可以在公式後面的大括號裡面加入 color1color2 的參數,來設定自己喜歡的顏色。

顏色可以使用英文單字像是「red」或「blue」之類的,也可以用「HEX 的顏色代碼」。

像是下面這張任務進度追蹤表,表格旁邊的進度條就是使用我設定的顏色代碼,具體的公式如下:

=SPARKLINE(C56:D56,{"charttype","bar";"color1","#1C95C7";"color2","#D6EAF8"})
SPARKLINE函式裡面加入顏色的兩個參數


超過上限的進度條

前面圖片裡所提到的例子,都不會有「超過上限」的問題,但如果像是「預算」這一類的資料,它就很有機會超過進度的上限。

我們就以下面這張圖為例子,框選的範圍如果是「C68:D68」,只要預算超過,D68 就會變成負的,然後進度條也會失真。所以像這一類型的資料最好加入一個 max 參數,來控制進度條的上限。

SPARKLINE的進度條加入max參數可以防止進度超過上限


進度條達標變色

此外,如果想要在進度條達標的時候有一個「變色效果」,可以使用「IF函數」來達成它。我們只要在參數 color1 的後面添加「IF」的公式進去,然後設定「達標前」跟「達標後」的顏色代碼,就可以做到進度條達標變色的效果。

以下圖為例,只要「已花費」的金額大於「預算」,也就是 C 欄大於 B 欄的時候,進度條就會達標變成紅色,其中設定的細節可以參考下面這條公式:

=SPARKLINE(C68,{"charttype","bar";"max",B68;"color1",IF(C68>B68,"red","#25B413")})
SPARKLINE函式裡面加入IF函式


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


結語

資料視覺化」越來越流行,但一般在 Google 試算表插入的圖表都是在儲存格上方。如果要在儲存格中插入圖表,通常會用到「SPARKLINE」函數,SPARKLINE 的公式並不算難,但如果想要設計比較理想或是好看的「進度條」,就要加入比較多的參數,所以有些公式可能會變得一大串。

把圖表插進儲存格裡還有一個好處,就是圖表就可以跟著儲存格移動,這樣在資料管理上也是滿方便的!

而儲存格進度條的另一種做法就是使用「REPT」函數,實際的做法可以參考這篇文章:「Google 試算表|用 REPT 製作另類的【進度條】,形狀隨你選!

Reference:infoinspired.com文件編輯器說明


延伸閱讀

【Google試算表】文章總列表

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

Google 試算表【SPARKLINE】直接在儲存格建立「迷你圖表」!

Google 試算表插入圖片可以用【IMAGE函數】4種模式任你選!

Google 試算表|如何插入公式?公式 12 個【小技巧】都學起來!


aifian APP推薦活動


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

→ 歡迎 留言分享

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

贊助我一杯咖啡


留言區