excel 進階版「滑珠圖」製作方法 平均數與標準差

瀏覽: 人次。

在公司裡面努力的自學圖表,也已經有三年多的時間了,一開始是很認真的把圖表達人李阿志上的課都好好記起來,然後也把圖表聖經「用圖表說故事Say it with charts」好好的k過一遍,不過,隨著公司裡面做數據分析量化的案子越來越多,單純的柱狀圖,已經不能滿足老闆了(泣)。

於是又上網爬了很多地方,買了幾本專做圖表的進階書,有「提高數據說服力的Excel圖表技法」(日本人寫得,還不錯),也從當當網海外購買了對岸最棒的圖表書「圖表之道」回家用力K,昨天研究出「滑珠圖」製作方法。還帶有平均數與標準差的,就在這邊分享給大家。(滑珠圖的由來)

在做滑珠圖之前,先解釋一下什麼是滑珠圖。

這是紐約時報The Pay at the Top用的,基本上右邊那一個區塊就叫他「滑珠圖」(用英文講的話不曉得是不是Slider Chart?)

也有這樣子的「小珠小珠落玉盤」,這是在比較田徑的時間,Fractions of a Second: An Olympic Musical ,紐約時報很厲害呢!這還是可以互動的喔!

這種比較接近我想做的,雖然他的珠珠沒有在中間,不太像是正統的滑珠圖,不過瞭解原理之後,也是能知道怎麼做這個圖。也是取自紐約時報These Days, Countries in Misery Have Lots of Company

EXCEL基本上是可以做「直」的折線圖,這很簡單,就是改用散布圖,然後把Y軸調成固定跳號的值,就噹噹完成了,圖表之道和提高數據力那本書裡面都有寫。不過如果還想要亮起固定範圍的「區間」那就比較麻煩了。底下就分享我的過程。

首先raw data大致長這樣子。(去創市際的網站上找的品項,數據自己瞎掰的)

然後我要把它整理成有平均值和標準差,可以變成滑珠圖的raw data,我需要加上七個欄位。並套用兩個函式

 

解釋一下各欄位

  • 上限就是平均值+標準差
  • 下限就是平均值-標準差
  • 透明1=下限
  • 灰底2=上限-下限
  • 散布Y是一個關鍵:他是一個自設跳號的值,依你的品項多寡自設,有人在圖表之道那邊留言說,品項超過10個的話就沒法做滑珠圖了,其實還是可以的,因為散布Y並沒有限制,只要知道怎麼調整跳號的值,還有軸標籤的最大最小值就好了。
  • 平均值 =TEXT(AVERAGE(儲存格:儲存格),"0.0%") ,也就是橫著選20歲以下那一欄,一直到60歲以上那一欄。
  • 標準差 =TEXT(STDEVP(儲存格:),"0.0%")

接著選取前4個資料,很快的產生堆疊橫條圖

再來是雙圖表類型的關鍵了

  1. 選取第一個藍色部份,也就是「上限」的值,在上面的左邊找第一個大按鈕「變更圖表類型」,選「散布圖」
  2. 看到點點出現之後,回到圖表上點右鍵,找到「選取資料」
  3. 進去點一下「上限」再按「編輯」
  4. 看到編輯數列的面板出現了,把X值改成上表上限的值,Y值為「散布Y」
  5. 接下來的下限也比照1~4的步驟辦理。

好了,已經很接近我們要的了。利用顏色和標記選項調整一下視覺:

  • 剩下來的綠色部份顏色改為「透明」
  • 紫色部份改為「灰色」
  • 藍色點點改為「深灰色」「圓形」
  • 紅色點點改為「淺灰色」「圓形」。
  • 然後選取左邊的品項軸,把他的類別次序設為「反轉」

「圓形」的功能怎麼做:選起點點,點滑鼠右鍵選單,找最後一個「資料數列格式」裡面的「標記選項」>內建,倒數第三個就是圓形了。

類別次序設為「反轉」怎麼做?選起左邊的類別軸項,點滑鼠右鍵選單,找最後一個「座標軸格式」,從上往下數來第五個就是了。

點點不是很整齊的對到灰底,要怎麼做?

  • 方式1:你可以手動修改「散布Y」的值(例如第一列想要點點稍微下來,那就不是5%而是2.5%)
  • 方式2:調整右編軸向的「最大」值(調整成37%之類的)。

我在這邊是使用第一招,我把散布Y的起跳值改為2.5%。

接下來就可以插入中間要的珠珠了:

  • 在圖表上點滑鼠右鍵選單,
  • 找到「選取資料」之後再選「新增」
  • 然後去選你要得目標,例如「20歲以下」
  • X軸就是他原來的值,Y軸就是「散布Y」
  • 珠珠出現之後,一樣也是把標記選項改成圓型,尺寸大個兩級,從預設的7變成9,就差不多要完工了!

接著就是美化的工作:

  • 選起圖例,點滑鼠右鍵選單。選最後一個「圖例格式」,設為「在上方顯示」
  • 「透明1」與「灰底2」兩個圖例單獨選取(要點兩下),按Delete鍵刪除
  • 把右邊的Y軸選取後刪除
  • 做一下框線的調整,插入圖表標題,就完成啦!

後記1:要同時有很多珠珠,也是可以,不過要注意珠珠可能會相疊在一起,所以如果品項和數值太多,建議分區做表,不然一整盤珠珠,只會在你眼前滾啊滑的,不能有效傳遞資料啊!

後記2:其實用帶珠珠的折線圖做,也可以的,不過就會變成「直立」的版本,這對於品項閱讀很不方便!而且中間的灰底雖然可以用「高低點連線」+「框線變粗」來克服,但就很難辦法表達「超出標準差」的珠珠了。