創業新聞

新創行銷人員看這!教你如何活用 Excel 圖表呈現數據讓報告更完美有力

shutterstock

本文摘自《用Excel取巧學統計:大數據的基本功,有力的圖表,就得這樣呈現。他人的數據誤用,立刻洞察》,大是文化出版

泡泡圖:三個以上的變數關聯

下列利用 Excel 製作的圖是針對各據點的銷售額、毛利和經常利潤進行統計,並以泡泡圖進行標示的圖表。

這裡省略了單位。札幌分店的銷售額是 252,毛利是 83,經常利潤則是 46。

這個時候,泡泡圖的橫軸數值資料代表資料的第 1 欄,縱軸代表第 2 欄,而第 3 欄則用圓的大小來進行表示。

橫軸和縱軸的位置將相當於圓的中央。如果把它看成在散佈圖中加上圓的大小要素,應該就會比較容易理解。

可是,圓的大小沒辦法表現 0 或負數值,所以製作表格的時候,第 3 欄的數值資料要避開包含 0 或負數值的值。

使用Excel製作泡泡圖的方法

點擊「插入」→「插入 XY 散佈圖或泡泡圖」→「泡泡圖」→「泡泡圖」。若是 Excel 2010 之前的版本,則點擊「其他圖表」→「泡泡圖」→「泡泡圖」。

製作泡泡圖的時候,千萬別忘記數值資料欄一定要有第 3 欄。另外,和散佈圖相同,圖表要盡可能調整成正方形。

此外,還必須從顯示出的泡泡圖(預設狀態)中,刪除圖表標題或圖例,同時增加座標軸標題。

不過,在這張圖表的資料中,據點部分有「札幌」、「東北」、「東京」、「中部」……都市名稱,但這些都市名稱並沒有反映在泡泡圖裡面。

顯示的方法將在步驟 4 之後說明。

步驟 4

增加泡泡圖的資料標籤。在泡泡圖的泡泡(圓形)部分點擊滑鼠右鍵,選擇「新增資料標籤(B)」→「新增資料標籤(B)」。

步驟 5

透過步驟 4 增加了資料標籤。這裡出現的數字(這裡是「毛利」)是縱軸的值。
接下來要利用這個數字,以手動方式覆寫資料標籤。

首先,在顯示「48」(縱軸「毛利」的值)的標籤進行覆寫。左鍵點擊顯示「48」的標籤兩下(如下頁圖 2-44 ),在選取狀態下,在資料編輯列輸入「=」後,選擇輸入「福岡」的儲存格(這裡是 A8 儲存格,如圖 2-45 )

選擇完成後,按下「Enter」鍵,確定輸入。指定的泡泡就會顯示出「福岡」。

利用相同要領,顯示出其他的資料標籤,如圖 2-46 所示。

直方圖怎麼讀?怎麼做?

圖 3-3 表示儲蓄額的家庭數分布圖稱為直方圖。這種直方圖用來作為基礎的資料,是一筆一筆的家庭別儲蓄額。

根據家庭別儲蓄額的調查結果,以固定金額加以區隔,表示該範圍內有多少人數的圖表就是直方圖。

只要把一筆一筆的家庭別儲蓄額加總起來,再用平均數進行除法,就可以簡單求出平均數。可是,就如同前面以平均分攤作為範例所做的說明,由於一部分偏多或偏少的情況,會拉開數值的差距,因此,未必可稱得上是整體的趨勢。

因此,為了掌握原始資料,把資料的分布情況視覺化,就要製作直方圖。

Excel 有製作直方圖的功能,所以為了從原始資料中找出分布,就先來了解直方圖的製作方法。

直方圖的製作有一個規定,直方體的粗細必須和數值(橫軸)區間的寬度一致。

只要仔細觀察圖 3-3 的圖表橫軸就會發現,在 2,000 萬日圓之前,區隔是 200 萬日圓,但超過 2,000 萬日圓之後,區隔則是 500 萬日圓。藉由直方體的粗細來反映這種差異,正是統計學中的直方圖規則。

因此,便可透過圖表得知,因為有儲蓄額達數千萬程度的家庭存在,而導致平均儲蓄額朝較多的方向發展。

也就是說,平均數未必是出現頻率較多的值,在這個平均儲蓄額的範例中,亦顯現出這種現象。然後,就算以平均數為界,也未必能夠把件數(這裡的家庭數)概略分成對半。

在依儲蓄額分類的家庭數的直方圖中,可以看出儲蓄額未滿 100 萬日圓的家庭居多。也可以發現儲蓄額增加越多,家庭數就變得越少。

使用Excel製作直方圖的方法

那麼,試著使用顧客資料的「年齡」項目,製作直方圖,調查依年齡層分類的人數分布程度。

在製作之前,先來看看之前的完成圖,掌握概略情況。

利用Excel的「資料分析」工具,就可以簡單製作出直方圖。

橫軸指年齡層,以「10」、「20」、「30」……進行配置。在利用 Excel 的資料分析功能製作的直方圖中,「10」解讀為10歲以下,「20」代表 11 歲以上 20 歲以下,「30」則是 21 歲以上 30 歲以下。

然後,符合組距(例:11歲 ~ 20歲)範圍內的筆數稱為次數(Frequency)。在Excel中則顯示為「頻率」。以年齡層的情況來說,就預先按照10歲來加以區隔後,再進行製作。

從圖 3-5 的圖表可發現,40年代(31歲以上 40 歲以下)的人數最多,其次是 50 年代的人數最多,不過,50年代的人數是 40 年代的一半以下。

此外,製作這張直方圖的時候,為了概略掌握「年齡」的差異程度,要先求出年齡的最大值和最小值。最大值可以用 MAX 函數求出,最小值則要利用 MIN 函數求出。

在這裡,年齡的資料範圍是 J2 儲存格~ J3590 儲存格,所以 MAX 函數和 MIN 函數分別輸入如下。

從結果得知,年齡的最大值是84(歲),最小值是 17(歲)。

那麼,接下來說明根據顧客資料的「年齡」項目,使用 Excel 的資料分析工具,製作直方圖的方法。

步驟 1

首先,製作資料區間的欄。在M1儲存格輸入「資料區間」的標籤。接下來,從次列的M2儲存格開始,以「10」、「20」、「30」……「90」各 10 歲的間隔,依序往下配置。

此外,在Excel中,這裡的「10」是指「10歲以下」這樣的間隔上限,請記住這一點。

步驟 2

「資料區間」準備完成後,從「資料」標籤選擇「分析」群組的「資料分析」。

如果在「資料」標籤下找不到「分析」群組的「資料分析」的話,可以點擊「檔案」→「選項」開啟「Excel 選項」畫面,然後再點選「增益集」項目進行設定。

步驟 3

從顯示出的「資料分析」畫面選擇「直方圖」,點擊「確定」。

步驟 4

在「直方圖」畫面,進行下列設定。

  • 輸入範圍(I):
    選擇欲繪製直方圖的資料範圍。連同「年齡」的資料標籤一起設定選取範圍吧!這裡選擇 J1 儲存格~ J3590 儲存格。

  • 組界範圍(B):
    指定年齡的間隔方法。設定各個階級的上限。這裡採用 10 歲以下、11 歲~20 歲以下、21 歲~30 歲以下……這樣的間隔,所以就採用 10、20、30……這樣的配置。資料區間的最大間隔就設定成比最大值更大的數值。 年齡的最大值是 84 ,所以這裡的最大資料間隔就設定成 90 。此外,資料間隔的範圍選擇就包含輸入「資料區間」的 M1 儲存格在內,選擇 M1 儲存格 ~ M10 儲存格。

  • 標記(L):
    因為選擇的範圍包含 J1 儲存格的「年齡」、M1 儲存格的「資料區間」,所以這裡就勾選「標記(L)」。

  • 輸出範圍(O):
    預定把圖表輸出至資料所在的相同工作表時,就要指定一個開始輸出圖表的儲存格。

  • 新工作表(P):
    自動新增新的工作表,從 A1 儲存格開始輸出。

  • 自動新增新新活頁簿(W):
    自動建立新的 Excel 檔案,新增「Sheet1」工作表,從 A1 儲存格開始輸出。

  • 柏拉圖(A):
    按照各項目、各分類(這裡指的是年齡層別的階級),以降冪排列,顯示各階級的資料筆數,同時用折線圖表示累積百分比的複合圖表,就稱為柏拉圖。希望同時輸出柏拉圖的時候,就勾選這個項目。這裡不需要依照人數較多的年齡層順序進行排序,所以便不做勾選。

  • 累積百分率(M):
    只要勾選這個項目,就可以一併輸出各階級的累積構成比例。這裡則不做勾選。

  • 圖表輸出(C):
    因為結果要輸出直方圖,所以要勾選這個項目。

設定完成後,點擊「確定」。輸入的初期狀態就如圖3-11。

顯示在 O 欄~ P 欄的表格稱為次數分配表。顯示出各階級分別含有幾筆資料。也就是說,這裡所表示的是各年齡層的人數。

因為 Excel 的規則緣故,接下來必須針對下列 3 點調整格式:

  • 刪除圖例和修正資料標籤。
  • 隱藏「其他」項目。
  • 填滿直方體的間隔。

1.刪除圖例和修正資料標籤:

首先,刪除圖例。因為圖表中只有顯示年齡層類別的資料,所以不需要標示圖例。用滑鼠分別選取「直方圖」和「頻率」,再按下「Delete」鍵,即可刪除圖例。
接著,修改座標軸標題。在預設狀態下,橫軸顯示「資料區間」,縱軸則顯示「頻率」。這裡的橫軸代表年齡層,所以把座標軸標題修改成「年代」,縱軸則修改成「人數」。

分別修改縱橫軸的標題

  • 「頻率」→「人數」
  • 「資料區間」→「年代」

2.隱藏「其他」項目:

直方圖中有個「其他」項目,超出「資料區間」最大值(相當於範例中的 90 )的筆數,就包含在「其他」裡面。

可是,因為「資料區間」的指定採用了比資料最大值更大的值,所以 Excel 的直方圖功能並不使用「其他」。

若不想讓「其他」這個項目反映在直方圖裡面,首先要選擇圖表。這時會顯示出圖表的參照來源範圍(如圖 3-13 )。利用滑鼠拖曳的方式,把「其他」項目排除在圈選範圍之外即可,如圖 3-14 所示。

3.填滿直方體的間隔:

欲填滿直方體的間隔時,就用滑鼠右鍵點擊直方圖的直方體部分,然後選擇「資料數列格式(F)」。

在「資料數列格式」畫面,把「類別間距(W)」的數值變更成「0%」。

Excel 沒有在直條圖的外框部分,加上不同於直方體的顏色,所以填滿直方體的間隔之後,還要另外加上外框,讓直方體的區隔更容易辨識。

用滑鼠右鍵點擊直方體的部分。再點擊顯示出的「外框」,就可以選擇任意顏色。

如此,Excel 的直方圖便完成了。

了解更多關於創業小聚的資訊,歡迎透過以下服務
粉絲交流每月小聚Line@互動訂電子報新創影音發表專欄及新創資料庫