Excel裡的這個工具,做財務分析一定要學會

2020-12-06 騰訊網

有過編制預算經驗的朋友，應該會很熟悉，在預測技術裡，回歸分析應用非常廣泛，是用來進行定量數據分析的方法。線性回歸分析是一種統計方法，用於確定某個變量（或一組變量）對另一個變量的影響。

這個解釋其實有點拗口，舉個例子，我們知道，銷售推廣費用會影響到銷售量，那具體是怎樣的關係？如果要預測未來的數據，可以怎麼做？比較簡單的方法，就是基於歷史數據，將兩者之間的關係量化，表示為一個線性關係：Y=a + bX。

其中，X是自變量，Y是因變量，意思就是，Y會因為X的變化而變化；a是Y軸的截距，a的存在是為了讓表達式更準確，b是回歸係數，或者叫做斜率。具體到之前的例子，Y就是銷售量，X是銷售推廣費用。

回歸分析最關鍵的一步，就是要得到a和b的值。可以用二元一次方程的方式去求解，但那樣用到的數據太少，會影響到預測的準確性。在excel裡，提供了一個非常好的工具，可以基於一組數據來進行求解。只要有準備好的數據，用工具自動計算a和b的值，比手工計算的準確度要好，也更簡單。

1、準備工作。

excel中，默認可能沒有加載數據分析工具的，在準備使用前，需要先添加。在【excel選項】的【excel加載項】中，添加【分析工具庫】，之後，在excel工具欄的【數據】頁籤下，就會有【數據分析】的功能。

2、對於數據進行相關性分析。

相關性就是判斷兩組數據之間有沒有關係，以及關係的緊密程度。還是用前面的例子，我們認為銷售量和銷售推廣費之間是有線性關係的，那真實數據是不是這樣呢？就可以通過求這兩組數據之間的相關係數來確定。

在【數據分析】工具裡，有一個求相關係數的工具，只需要把兩組數據輸入，就能計算出來相關係數。相關係數的數值範圍是【-1，1】之間，負數表示負相關，一個增加另一個減少，0表示無關 ，而正數表示正相關，一個增加另一個也增加。典型的比如，同類產品組中的A和B兩個產品，A的銷量增加B會減少，那A和B之間的銷量就是負相關。相關係數的絕對值越大，越接近於1，表示相關係數越強，一般地，大於0.8表示強相關。

比如，我們把之前案例裡的上一年度數據，整理到EXCEL中，得到相關係數是0.947208，這表明，兩組數據之間存在強相關性。在實際處理中，輸入數據不能太少，一般至少不低於12組數據。如果數據中有某一個數據明顯異常，也會導致的相關係數變低，這時，不應該立刻否定相關性，而是應該優先去分析這個異常數據產生的原因，未來會不會重現，然後再進行調整。

3、回歸分析

在確定相關係數符合分析要求後，則可以藉助execl進行下一步的回歸分析。在【數據分析】裡，選擇回歸。

需要注意一下，Y值的輸入區域，對應的【銷售量】列的值，也就是因變量，而X值輸入區域，對應的是【推廣費用】一列的值，是自變量。選擇將結果輸出到新的表頁，在新表頁中，就包括了回歸分析相關的數據（如下圖）。

其中，有這樣幾個數據比較關鍵：

1、【回歸統計】中的Multiple R是相關係數，就是前面第二個步驟裡單獨計算的相關性，可以再次確認一下。

2、【回歸統計】中的R Square，也就是R平方，又叫做擬合優度或者決定係數，它表示因變量的變化，可以在多大程度上通過自變量的變化來進行解釋。R方的取值範圍是【0,1】,R平方值越大，表示模型擬合的越好。一般大於70%就算擬合的不錯，60%以下的就需要修正模型。

3、在【方差分析】中，df是自由度，SS是平方和，MS是均方，F是F統計量，Significance F是回歸方程總體的顯著性檢驗，其中我們主要關注F檢驗的結果，即Significance F值，F檢驗主要是檢驗因變量與自變量之間的線性關係是否顯著，用線性模型來描述他們之間的關係是否恰當，越小越顯著。這個案例裡F值很小，說明因變量與自變量之間顯著。（這一段是抄來的，我是真沒看懂）

4、 最後一張表的第一列，是回歸方程a和b的值。那示例中的數據，最後得到的方程式就是：Y=5720.946+0.879989X。以後就可以用這個表達式，來計算當輸入的X發生變化時，得到的Y是多少。比如預測下一年銷售推廣費用投入後，帶來的銷售量。

以上只是最基礎的部分，有興趣的朋友還可以更深入地挖掘一下。前陣子在做一個給小朋友玩的小道具，也用到了excel裡的數據分析，生成隨機數，還是蠻好用的，excel不愧是傳說中最好使的分析工具。

不過，在運用線性回歸進行分析的時候，有基本假設作為前提：

首先，自變量和因變量之間確實存在穩定的線性關係；

其次，則要求用線性回歸得到的估計值與真實值之間的差異，服從均值為0，標準差為常數的正態分布。

一個是搭建模型之前要用數據確認的，一個是事後要再回測，也就是說模型不是用一次就完了，還要再用實際數進行驗證，並反覆修正。

註：是最近正好看了關於預測技術的內容，就結合起來簡單整理一下，這部分並沒有實際做過，歡迎有實戰經驗的朋友來拍磚。另，測試了兩天廣告，感謝大家的容忍。

相關焦點

  • 勤哲Excel伺服器做財務分析管理系統
    包括：財務信息化軟體類型不能滿足企業具體發展的需求、部門間信息數據共享程度低、財務軟體的適用性和安全性問題等等方面，這些都嚴重製約了現代企業財務信息化進程。那麼，如何解決現代企業遇到的財務信息化管理難題呢？某企業負責人推薦了勤哲Excel伺服器。該企業負責人稱，中小企業財務分析核心原則很簡單，就是一句話：一個起點，三大運作，二個結果。打個比方，做個培訓課程，就講這句話，半天時間。
  • 堪比python,強於Excel的可視化,我拿這個工具做出來了
    傍晚，一盞孤燈，你獨坐在辦公室裡繼續做著明天的報表，企業管理會議多，月報周報少不了，你「犧牲」在做表的途中，卻還有千千萬萬的表哥還在路上，這是月薪5K的人。實際情況可能是，你辛辛苦苦做出來的，也只是密密麻麻的數字與表格，你看不到結論，談何決策？這時候，你應該好好考慮數據可視化這個問題了。數據可視化能力已經越來越成為各崗位的基礎技能。
  • 人人都能學會的Excel數據分析方法
    文|花隨花心無論是剛入門的數據分析小白，但是工作多年的數據工程師，學會用Excel做數據分析總是第一門課。其實excel不只是一個數據統計工具，它的數據分析能力十分強大，除了基本的數據計算之外，還可以進行數據清洗、數據可視化等等，財務人、業務人等都很喜歡用excel做報表。
  • 值得學習的excel技巧,格式工具欄的使用
    我們在實際工作中，當我們需要處理數據的時候，我們首先會使用excel表格對數據進行處理，原因是excel表格具有眾多的數據處理工具，有些工具還特別實用，我們在日常工作中都經常使用到，今天我們要分享的是有關excel表格格式工具欄的使用，我們清楚excel裡的數據可以根據我們的需要設置不同的格式
  • 使用簡單而強大的Excel來進行數據分析
    這款軟體不僅能夠進行基本的數據計算，還可以使用它來進行數據分析。它被廣泛用於許多的領域內，包括財務建模和業務規劃等。對於數據分析領域的新手來說，Excel它可以成為一個很好的跳板。甚至來說在學習R或Python之前，最好先了解一下Excel。將Excel添加到你的技能庫中沒有什麼壞處。
  • 財務小白如何快速上手報表分析(內含公式圖)
    對於初出茅廬的財務小白來講，掌握財務報表分析方法並運用好工具，是一條可複製的捷徑之路。今天，小億將開啟財務報表小講堂，為大家詳細講講這中間的門道。　　來~上乾貨，一張圖將最常用的計算公式羅列出來，從企業償債能力、營運能力和盈利能力等三大方面入手分析。希望對大家有所幫助，小夥伴們趕緊收藏啦！
  • 如何用excel製作年會抽獎滾動工具,原來一個公式就搞定了
    抽獎是年會中的重要一環，那麼可以滾動的抽獎工具該怎麼做呢？抽獎工具，還是可以滾動的，看似很複雜，其實在excel裡用一個公式就搞定了，想要在年會上秀一手嘛，那就趕快來學習吧~一個公式搞定抽獎工具其實，=index（A：A，between（2,8））這個公式是兩個函數的疊加，index1、RANDBETWEEN(2,8)：函數為隨機返回2-8中的任意一個數值，因為姓名當中人數在第2-8行，所以數值範圍為1-10；2、INDEX(A：A,RANDBETWEEN(2,8))：index
  • 數據分析軟體工具有哪些?
    Excel Excel作為入門級的工具，是最基礎也是最主要的數據分析工具，它可以進行各種數據的處理、統計分析和輔助決策操作，數據透視圖是Excel中最重要的工具，如果不考慮性能和數據量，它可以處理絕大部分的分析工作。正所謂初級學圖表，中級學函數透視表，高級學習VBA。
  • 零一數據 [21天小白學成大師]第五天 學會用EXCEL做預測
    原創：有點瘦的胖子零一需要預測的場景太多這裡就不一一贅述了，在師傅的指導下，我對excel的認知水平又提升了一大截，學會了用excel做多元回歸分析。這個預測方法不僅適用絕大部分行業，並且也適用沒有業務基礎的小白操作。附上師父的一句教誨：相信相信的力量。
  • 多年來Excel填報數據的各種不如意,終於現在用這個神器全部都解決了
    首先,excel對數據收集的使用場景就受到限制,只能通過PC來完成數據收集。另外,要想共享數據,就需要將文件通過微信、釘釘等軟體或者U盤、移動硬碟等硬體傳來傳去,這個過程不僅繁瑣,而且數據的一致性和實時更新都不能保證。還有,Excel無法滿足企業中對於權限管控的需求。並且,Excel不是採用基於資料庫選擇的方式,大家輸入數據的時候隨意粘貼複製,有時候多一個字,有時候少一個字,數據質量就很差。
  • excel函數技巧:如何用三角函數製作環形氣泡圖
    例如「財務函數」、「數學函數」、「分析函數」等等。學習更多技巧，請收藏部落窩教育excel圖文教程。相信即便是一些「認為自己函數玩的不錯」的同學，看到上圖中的函數也是「懵」的。存在即合理，既然微軟有這些函數，就一定是多年使用EXCEL的客戶，對於使用體驗反饋中所涉及的，例如財務函數，在財務工作中大部分的計算都是「加減乘除」，可是對於真正的財務分析師來說，單純的四則運算是遠遠不夠的。 為什麼學習EXCEL，為什麼學習EXCEL函數、VBA？是因為，我們在為自己的工作尋求可以提高效率的方式。
  • 財務分析是什麼?財務分析的目的與基本分析方法
    因此可以這樣下定義：財務分析的本質是搜集與決策有關的財務信息，並加以分析和解釋的一項技術。財務分析的方法與分析工具很多，應根據分析者的目的而具體選擇。最經常用到的是圍繞財務指標進行單指標、多指標綜合分析、再加上借用一些參照值，運用一些分析方法（比率、趨勢、結構、因素等）進行分析，然後通過直觀、人性化的格式展現給用戶，比如報表和圖文報告等。
  • 數據分析工具太多該怎麼選擇合適的軟體?
    一般在日常生活中大家用到的數據分析工具多數是excel。但是其實在數據分析工具中，excel只是基礎。對於那些專門做數據分析的人來說，有更為簡便、專業的數據分析工具。
  • 早做完,不加班,財務會計工作中必須會的Excel函數公式!
    點擊播放 GIF 0.0M2，在財務工作中，也會經常遇到條件匯總的問題，尤其是再加上隔列分類匯總，解決的方法可以用到SUMIF：首先，選定目標單元格。點擊播放 GIF 0.0M3，在財會的工作中多條件匯總求和也是非常的常見，學會SUMIFS也是很輕鬆的解決問題：在目標單元格中分別輸入公式：=SUMIFS
  • 兩個excel表格核對的6種方法
    excel表格之間的核對，是每個excel用戶都要面對的工作難題，今天ostar帶大家一起盤點一下表格核對的方法，一共6種，以後再也不用加班勾數據了。一、使用合併計算核對excel中有一個大家不常用的功能：合併計算。利用它我們可以快速對比出兩個表的差異。例：如下圖所示有兩個表格要對比，一個是庫存表，一個是財務軟體導出的表。
  • 華為財務崗裁員1100人!任正非痛斥:鼠目寸光!他們將來是要做接班人!
    讓財務到項目中去，讓業務人員到財務裡來。 不止是在華為，不論哪個公司，都應該有業財融合的意識，做業務的要去了解財務，財務人也要去了解業務！ 4財務人員如何懂業務？
  • 做機械的為什麼一定要下車間?
    在你的印象中，似乎再也找不到比這裡更差的工作環境了，來看看都覺得難以接受，更比說要在這種環境裡工作幾年，運氣不好可能是工作一輩子，那是怎樣的一種自我懲罰啊，有一種懷疑人生的念頭在腦袋裡不停的閃爍：上帝，我到底做錯了什麼？
  • 面試數據分析崗,怎麼提升一倍成功率?讓過來人給你支支招
    說到excel，這裡我還要提一下，很多人在簡歷裡都會寫自己能熟練使用Excel，不僅僅是求職數據分析師的簡歷，幾乎大多數的人都會這樣寫，想體現自己對辦公常用軟體的使用能力，而對我們想找數據分析師工作的朋友來說，這「熟練」二字萬不能隨意寫上，除非你把VBA、函數公式、數據透視表等等都學會了，不然就謙虛點。
  • 泛癌全基因數據分析工具推薦:UCSC XENA
    前兩天我們介紹了一下剛剛發表的泛癌的全基因組在線數據工具匯總的文章。同時也介紹了一下關於ICGC的使用，在那個文章裡面提到了五個在線分析PCAWG的工具，今天就來介紹另外一個：UCSC XENA。　　UCSC XENA簡介　　之前我們在很多帖子裡面都提到了。
  • Excel中製作「熱力地圖」竟如此簡單,財務分析又多一種圖表
    做財務的，經常會涉及到各種各樣的財務分析，所以製作分析報表是常有的事情，但是有的時候公司會有分公司，所以會涉及到一些不同地區的數據分析，分析起來會比較難。比如各省份，某省份的各個市縣相關，可以從地緣角度去分析數據。本期內容主要講解基於地緣信息進行分析常用的一種圖表——熱力地圖。