對投資新手的簡單易懂教學-EXCEL的樞紐分析在財務分析的應用教學

前言

先前在做財務分析工作時，經常會需要處理大量資料，資料可能多達十幾萬筆，進行分析時就與大海撈針無異，這種時侯「樞紐分析」就是我們最好的朋友，原因在於可以用很簡單的操作方式(不需要懂函數、公式或VBA)，就能迅速地將資料以「表格」方式進行欄位分類與篩選，讓分析者清楚地只看到想看到的部分，確實非常方便。尤其是分析資料裡「項目」都會變動的時侯，如果用函數或公式要設想很多情況，此時直接用樞紐是最有效率的方式。

以下就是透過公開資訊觀測站的「採用IFRSs後每月營業收入彙總表」，以國內上市公司在111年4月(目前最新)的資料做範例，說明如何使用樞紐分析的教學。

樞紐分析是什麼?

樞紐分析表是一項強大的工具，可用來計算、建立摘要和分析資料，讓您比較資料和查看資料中的模式與趨勢。 視您用來執行 Excel 的平臺而定，樞紐分析表的運作方式稍有不同。(資料來源: Microsoft官網)

樞紐分析注意事項

個人經驗是有幾點要注意:

• 要分析的資料，從輸入「選取表格或範圍」的動作可知道，僅限「同一工作表的表格範圍」。
• 樞紐一開始的範圍建議設大一點，不然等到資料量超過預設範圍時，新的資料會抓不到，這是常發生的問題。
• 輸入資料的第一欄是表頭，所以一定要有資料。
• 資料最好是經標準化的表格，裡面盡量不要有錯誤(或空白)，不然像是樞紐範圍做自動調整，可能會有問題。
• 樞紐是將資料彙總。所以會將數字相加，但如果原始資料已是「比率」了，「比率」彼此的相加是無意義的，應該另設公式來整體來算，例如整體毛利率=整體毛利/整體營收。
• 這個樞紐結果有時是我們的分析目的，但很多時侯卻只是我們分析過程獲取資料一個手段而已，專門用來快速處理函數與公式難以處理的部分。
  

範例資料來源

公開資訊觀測站資料路徑: 彙總報表/營運概況/每月營收/採用IFRSs後每月營業收入彙總表

輸入想要查詢的「市場別」、「年度」、「月份」後，按查詢，並按下面的「請點選這裡」。

在本例中選「國內上市」、「111年」、「4月」。

接著會跳出新視窗，即為「上市公司111年4月份(累計與當月)營業收入統計表」，

為了整理方便，請直接如下面圖片，按「另存CSV」下載檔案。

步驟說明

下圖是用Excel打開「上市公司111年4月份(累計與當月)營業收入統計表」CSV檔的原始資料畫面。

然後為了選取樞紐的範圍，我們需要進行資料範圍的取選，這邊介紹一個小技巧!

增加Excel流暢的小技巧「凍結窗格」

• 從表頭開始算的第三列，也就是不含資頭的第二列資料，選點B的位置(如下圖)。
• 然後在工作列的「檢視」/凍結窗格/凍結窗格(F)按一下。

在凍結窗格執行後，我們在任一儲存格點一下，直接同時按「Ctrl鍵」加方向鍵「↓」，可發現我們一下就跳到資料範圍的最下面，

但同時我們資料範圍最上面的位置也能看到，所以可以很快地選取「所有」位置(不用再拖拉表格)，完成樞紐範圍的輸入(通常是選新增工作表)。

這個樞紐工作表，在本例中名稱為「工作表1」。

這個凍結窗格+樞紐分析的綜合技巧算是時常會用到，雖然有時只差幾秒，但卻可以有效增加使用Excel的流暢。

接著就是將預想的表格欄位丟到樞紐中，可以點選項目拖曳左邊表格列或欄的位置，也可往下方的欄位進行拖曳。

值欄位設定

但這樣數字的結果沒有千分位不好判讀，所以會在樞紐上按右鍵，點選「值欄位設定」，然後格式選「數值」，勾選千分位符號與想要的負數格式。

完成結果

最後下圖就是我們的完成結果。當然可以視使用者的需求把不同欄位放進去，以得到需要了解的分析結果。

有興趣的讀者可再參考下列文章

對投資新手的簡單易懂教學-EXCEL的樞紐分析在財務分析的應用教學-進階技巧-樞紐範圍自動調整