Excel不同大小散佈圖~泡泡圖

Excel泡泡圖

泡泡圖的好處?

一般繪製散佈圖時,會出現一種情況,就是相同數值的點發生重疊。
以下的散佈圖各方法都是量測10筆數據,但是卻沒有顯示出10個點,方法B甚至只出現3個點。
而相同數值的點疊在一起,會使得我們沒辦法看出數值到底集中在哪個區塊,如下圖大多數值是落於16.0還是15.0,以一般散佈圖無法得知。

這時,可以使用另外一種圖表類型~「泡泡圖」。
藉由泡泡的大小變化來表示出幾筆數值重疊,可以很快看出方法B較多筆尺寸為15 cm,其次是16 cm。
「泡泡圖」可解決「散佈圖」重疊數據的問題,並且更有效突顯出數據的集中性。

泡泡圖製作教學

依先前散佈圖的製作方法,只要再進行幾個小步驟修改,即可轉換成泡泡圖。

圖表架構

我們先來看看泡泡圖的圖表架構。
點選圖表,「插入」-「建議圖表」-「所有圖表」-「XY散佈圖」-「立體泡泡圖」。
點選右鍵-「選取資料」-「編輯數列」,可以看到有「數列名稱」、「數列X值」、「數列Y值」、「數列泡泡大小」,相較於散佈圖,多了「數列泡泡大小」。

「數列泡泡大小」輸入數值大或小,泡泡即產生大小變化。
我們就可以利用泡泡的大小代表多少個相同數值,例如:僅1個數值的泡泡大小即為1;2個相同數值的泡泡大小即為2,以此類推。

計算相同數值

那麼要如何計算相同數值共有幾個呢?
Ans:有個很實用的函數 – 「『countif』函數:計算一範圍內符合指定條件儲存格的數目。」
(1)建立個輔助列,可以先複製原本的數據進行修改。
(2)框選數據,取得最小值(13)及最大值(19)。
(3)建立從最小值到最大值之數列(如13~19)。

(4)將原本數值刪除,插入Countif函數。
方法A第一格數據,Range:B3:B12 (選取方法A的10筆數據);Criteria:G3(選取最小值13) “即計算方法A有幾個13”
方法A第二格數據,Range:B3:B12 (選取方法A的10筆數據);Criteria:G3(選取第二個數值14) “即計算方法A有幾個14”
方法B第一格數據,Range:C3:C12 (選取方法B的10筆數據);Criteria:G3(選取最小值13) “即計算方法B有幾個13”
以此類推計算出方法A~方法B的相同數值共有幾個?

※上面感覺好複雜,一個一個設定,這邊有個小技巧可以一次完成,利用「$」鎖定工作列符號。
H3儲存格輸入「COUNTIF(B$3:B$12,$G3)」,即可向右和向下拖曳,將公式一次完成。
如果不放心的話,可以稍微點幾個儲存格來確認公式範圍是否有選擇正確。

拖曳完成即計算出有多少個相同數值。

插入圖表

點選圖表,「插入」-「建議圖表」-「所有圖表」-「XY散佈圖」-「立體泡泡圖」。
點選右鍵-「選取資料」-「編輯數列」,首先是方法A。
數列名稱:B2 (方法A)
數列X值:B14:B23 (選取10個數字1)
上面兩項同散佈圖作法,不需進行修改。
數列Y值:G3:G9 (選取最小值到最大值之數列,即13~19)
數列泡泡大小:H3:H9 (即選取13~19數值的個數)

以同方式完成方法B~方法D的設定。

泡泡大小調整

當泡泡大小太大,泡泡都黏到一起時,可以左鍵點選泡泡兩下。
「數列選項」-「調整泡泡的大小為」,數值由100自行往下調整。

調整完成後,不同大小散佈圖~泡泡圖即完成啦!
由泡泡圖能立即看出數值都集中在哪一點上,可解決「散佈圖」重疊數據的問題。

泡泡大小代表幾筆資料?

對於泡泡圖會有個疑問,氣泡大跟小分別是代表幾個相同數值呢?
Ans:我們可以建立氣泡大小示意圖,做出分別有1、2、3、4、5個相同數值的氣泡大小(甚至6~10個)。
分別打上1、2、3、4、5,並空個間隔以避免氣泡黏在一起,接著將框線去除。

最後,複製貼上圖表,並裁剪留下不同大小之氣泡圖。

其他文章分享

EXCEL繪製不同條件之散佈圖

Excel文字方塊參照/連結至儲存格

Excel手機圖繪製~讓你的圖表更吸睛

Excel人形圖繪製~讓你的圖表更吸睛

Excel求解未知數 Excel求解方程式


發佈留言

發佈留言必須填寫的電子郵件地址不會公開。